Preface
Spring's JDBC Templet is a basic encapsulation that Spring uses for JDBC. It mainly helps programmers to manage database connections, and the rest of the usage methods are no big difference from using JDBC directly.
Business Requirements
Everyone is familiar with the use of JDBC. This is mainly to demonstrate the steps to use Spring JDBC Templet in SpringBoot, so we designed a simple requirement. A CURD operation of a user object. An object has two properties, one is id and the other is name. Stored in the auth_user table in MySQL.
Create new projects and add dependencies
Create an empty SpringBoot project in Intellij IDEA. Specific steps reference
Intellij IDEA's graphic tutorial for creating spring-boot projects. According to the requirements of this example, we need to add the following three dependencies
<dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId></dependency><dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jdbc</artifactId></dependency><dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>6.0.6</version></dependency>
Because we want to publish the Http Rest service, we add the spring-boot-starter-web dependency. Here we want to use the JDBC Tempet method to access the database, so we add the spring-boot-starter-jdbc dependency to access the MySQL database, so we add the latest version of the JDBC driver of MySQL.
Prepare the database environment
Assume that MySQL 5.7 is already installed on the Linux operating system. The following operations are executed on the operating system command line, logged in to the command line client of MySQL through the root user.
Build database and table
create database springboot_jdbc;create table auth_user (uuid bigint not null,name varchar(32), primary key (uuid)) default charset=utf8mb4;
Set user permissions
grant all privileges on springboot_jdbc.* to 'springboot'@'%' identified by 'springboot';flush privileges;
Configure data source (connection pool)
SpringBoot's data source is automatically configured. In SpringBoot 2.0, there are several data source configurations available, and they choose which data source to actually use in the last order of HikariCP -> Tomcat pooling -> Commons DBCP2.
When the project adds spring-boot-starter-jdbc dependency, the HikariCP data source dependency is already included, so the HikariCP connection pool data source is automatically configured here.
Add the following configuration in appplications.properties
#General data source configuration spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driverspring.datasource.url=jdbc:mysql://10.110.2.5:3306/spring-boot-jdbc?charset=utf8mb4&useSSL=falsspring.datasource.username=springbootspring.datasource.password=springboot# Hikari Data source specific configuration spring.datasource.hikari.maximum-pool-size=20spring.datasource.hikari.minimum-idle=5
Among them, most of the configurations of the Hikari data source are shown in the figure below. You can check the meaning of each configuration
Program Development
User database entity
According to the requirements, the corresponding user data entity has two attributes, one is id and the other is name. This is a pure POJO object.
package com.yanggaochao.springboot.learn.springbootjdbclearn.domain.dao;/** * User entity object* * @author Yang Gaochao* @since 2018-03-09 */public class UserDO { private Long id; private String name; public Long getId() { return id; } public void setId(Long id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; }} General Http Rest Return Object
Usually in the Http Rest interface, we not only want to directly return the content of the business object, but also return some common information, such as the result of the interface call, the custom text message returned when the call fails, etc. Then we need to establish two common rest return objects, in addition to returning common interface call results and text messages, one includes a separate business content and one contains a collection that holds multiple business content. The specific definition is as follows
Return object for separate business content
package com.yanggaochao.springboot.learn.springbootjdbclearn.domain.bo;/** * Single object returns result* * @author Yang Gaochao* @since 2018-03-09 */public class RestItemResult<T> { private String result; private String message; private T item; public String getResult() { return result; } public void setResult(String result) { this.result = result; } public String getMessage() { return message; } public void setMessage(String message) { this.message = message; } public T getItem() { return item; } public void setItem(T item) { this.item = item; }} Collection of business content returns object
package com.yanggaochao.springboot.learn.springbootjdbclearn.domain.bo;import java.util.Collection;/** * Collection object returns result* * @author Yang Gaochao* @since 2018-03-09 */public class RestCollectionResult<T> { private String result; private String message; private Collection<T> items; public String getResult() { return result; } public void setResult(String result) { this.result = result; } public String getMessage() { return message; } public void setMessage(String message) { this.message = message; } public Collection<T> getItems() { return items; } public void setItems(Collection<T> items) { this.items = items; }} Data persistence layer development
User data persistence layer interface definition
package com.yanggaochao.springboot.learn.springbootjdbclearn.dao;import com.yanggaochao.springboot.learn.springbootjdbclearn.domain.dao.UserDO;import java.util.List;/** * User data layer interface* * @author Yang Gaochao* @since 2018-03-09 */public interface UserDao { /** * Save a new user to the database* * @param user object to save* @return Whether muscle gain is successful*/ Boolean add(UserDO user); /** * Update a user in the database* * @param user user user to update * @return Whether the update is successful*/ Boolean update(UserDO user); /** * Delete a specified user* * @param id The identity of the user to delete* @return Whether the delete is successful*/ boolean delete(Long id); /** * Exact query of a specified user* * @param id The identity of the user to query* @return If it can be queried, return user information, otherwise return null */ UserDO location(Long id); /** * Query the user by name* * @param name The name to be fuzzy* @return List of users to query*/ List<UserDO> matchName(String name);} User data persistence layer implementation
package com.yanggaochao.springboot.learn.springbootjdbclearn.dao.impl;import com.yanggaochao.springboot.learn.springbootjdbclearn.dao.UserDao;import com.yanggaochao.springboot.learn.springbootjdbclearn.domain.dao.UserDO;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.jdbc.core.JdbcTemplate;import org.springframework.jdbc.support.rowset.SqlRowSet;import org.springframework.stereotype.Repository;import java.util.ArrayList;import java.util.List;/** * User object database access implementation class* * @author Yang Gaochao* @since 2018-03-09 */@Repositorypublic class UserDaoJDBCTempletImpl implements UserDao { private final JdbcTemplate jdbcTemplate; @Autowired public UserDaoJDBCTempletImpl(JdbcTemplate jdbcTemplate) { this.jdbcTemplate = jdbcTemplate; } @Override public Boolean add(UserDO user) { String sql = "INSERT INTO AUTH_USER(UUID,NAME) VALUES(?,?)"; return jdbcTemplate.update(sql, user.getId(), user.getName()) > 0; } @Override public Boolean update(UserDO user) { String sql = "UPDATE AUTH_USER SET NAME = ? WHERE UUID = ?"; return jdbcTemplate.update(sql, user.getName(), user.getId()) > 0; } @Override public boolean delete(Long id) { String sql = "DELETE FROM AUTH_USER WHERE UUID = ?"; return jdbcTemplate.update(sql, id) > 0; } @Override public UserDO locate(Long id) { String sql = "SELECT * FROM AUTH_USER WHERE UUID=?"; SqlRowSet rs = jdbcTemplate.queryForRowSet(sql, id); if (rs.next()) { return generateEntity(rs); } return null; } @Override public List<UserDO> matchName(String name) { String sql = "SELECT * FROM AUTH_USER WHERE NAME LIKE ?"; SqlRowSet rs = jdbcTemplate.queryForRowSet(sql, "%" + name + "%"); List<UserDO> users = new ArrayList<>(); while (rs.next()) { users.add(generateEntity(rs)); } return users; } private UserDO generateEntity(SqlRowSet rs) { UserDO weChatPay = new UserDO(); weChatPay.setId(rs.getLong("UUID")); weChatPay.setName(rs.getString("NAME")); return weChatPay; }} Here we first use annotation @Repository to indicate that this is a class of the data persistence layer, and SpringBoot will automatically instantiate this class. Then add a @Autowired to the constructor. When SpringBoot instantiates this class, it will automatically inject the JDBCTemplet instance into this class. Here, the JDBCTemplet instance is automatically configured by SpringBoot based on the data source-related configuration in applications.properties. According to SpringBoot's algorithm to automatically configure data sources, the data source to be configured here is HikariCP.
The rest are just like ordinary Spring JDBCTemplet development. By manually converting between objects and database SQL by programmers, users can be added, modified, deleted, fuzzy matching, precise query and other functions.
Data business layer development
Data service layer interface definition
package com.yanggaochao.springboot.learn.springbootjdbclearn.service;import com.yanggaochao.springboot.learn.springbootjdbclearn.domain.dao.UserDO;import java.util.List;/** * User service layer interface* * @author Yang Gaochao* @since 2018-03-09 */public interface UserService { UserDO add(UserDO user); UserDO update(UserDO user); boolean delete(Long id); UserDO locate(Long id); List<UserDO> matchName(String name);} Data service layer implementation
package com.yanggaochao.springboot.learn.springbootjdbclearn.service.impl;import com.yanggaochao.springboot.learn.springbootjdbclearn.dao.UserDao;import com.yanggaochao.springboot.learn.springbootjdbclearn.domain.dao.UserDO;import com.yanggaochao.springboot.learn.springbootjdbclearn.service.UserService;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Service;import java.util.Date;import java.util.List;/** * User business layer implementation class* * @author Yang Gaochao* @since 2018-03-09 */@Servicepublic class UserServiceImpl implements UserService { private final UserDao userDao; @Autowired public UserServiceImpl(UserDao userDao) { this.userDao = userDao; } @Override public UserDO add(UserDO user) { user.setId(new Date().getTime()); if (userDao.add(user)) { return user; } return null; } @Override public UserDO update(UserDO user) { if (userDao.update(user)) { return location(user.getId()); } return null; } @Override public boolean delete(Long id) { return userDao.delete(id); } @Override public UserDO location(Long id) { return userDao.locate(id); } @Override public List<UserDO> matchName(String name) { return userDao.matchName(name); }} Here, this implementation class is declared as a business-level class through a @Service annotation. UserDao of the persistence layer allows SpringBoot to instantiate this business layer class through @Autowired, and automatically inject the corresponding persistence layer class into this business class.
Here, when adding user objects, when setting the identification for the user, a millisecond number of current time is simply used as the identification. During the actual development process, this place needs to use a globally unique mechanism to ensure that this logo cannot be repeated.
External service layer development
package com.yanggaochao.springboot.learn.springbootjdbclearn.web;import com.yanggaochao.springboot.learn.springbootjdbclearn.domain.bo.RestCollectionResult;import com.yanggaochao.springboot.learn.springbootjdbclearn.domain.dao.UserDO;import com.yanggaochao.springboot.learn.springbootjdbclearn.service.UserService;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.web.bind.annotation.*;import java.util.List;/** * User Http Rest interface* * @author Yang Gaochao* @since 2018-03-09 */@RestController@RequestMapping("api/v1/user")public class UserApi { @Autowired private UserService userService; @RequestMapping(value = "/add", method = RequestMethod.POST) public RestItemResult<UserDO> add(@RequestBody UserDO user) { RestItemResult<UserDO> result = new RestItemResult<>(); user = userService.add(user); if (user != null) { result.setItem(user); result.setResult("success"); } else { result.setMessage("New user failed"); result.setResult("failure"); } return result; } @RequestMapping(value = "/update", method = RequestMethod.POST) public RestItemResult<UserDO> update(@RequestBody UserDO user) { RestItemResult<UserDO> result = new RestItemResult<>(); user = userService.update(user); if (user != null) { result.setItem(user); result.setResult("success"); } else { result.setMessage("UserDO failed to modify user"); result.setResult("failure"); } return result; } @RequestMapping(value = "/delete/{uuid}", method = RequestMethod.GET) public RestItemResult<UserDO> delete(@PathVariable Long uuid) { RestItemResult<UserDO> result = new RestItemResult<>(); if (userService.delete(uuid)) { result.setResult("success"); } else { result.setMessage("Delete user failed"); result.setResult("failure"); } return result; } @RequestMapping(value = "/locate/{uuid}", method = RequestMethod.GET) public RestItemResult<UserDO> locate(@PathVariable Long uuid) { RestItemResult<UserDO> result = new RestItemResult<>(); UserDO user = userService.locate(uuid); if (user != null) { result.setItem(user); result.setResult("success"); } else { result.setMessage("query user failed"); result.setResult("failure"); } return result; } @RequestMapping(value = "/match/{name}", method = RequestMethod.GET) public RestCollectionResult<UserDO> match(@PathVariable String name) { RestCollectionResult<UserDO> result = new RestCollectionResult<>(); List<UserDO> users = userService.matchName(name); result.setItems(users); result.setResult("success"); return result; }} Here @RestController is used to declare that this is an Http Rest interface class. The call route for each interface is formed by combining @RequestMapping on the class and @RequestMapping on the method. The method property in @RequestMapping on the method declares the method called by http. @RequestBody annotation automatically converts the json object in the post data into a POJO object. @PathVariable Automatically converts data in the http url path into parameters of the service method.
Http Rest interface test
Test the Http Rest service is called through the HttpClient of Apache commons.
Http Resst calls auxiliary classes
package com.yanggaochao.springboot.learn.springbootjdbclearn;import org.apache.commons.httpclient.DefaultHttpMethodRetryHandler;import org.apache.commons.httpclient.HttpClient;import org.apache.commons.httpclient.methods.GetMethod;import org.apache.commons.httpclient.methods.GetMethod;import org.apache.commons.httpclient.methods.StringRequestEntity;import org.apache.commons.httpclient.params.HttpMethodParams;import java.io.BufferedReader;import java.io.InputStreamReader;import java.io.InputStreamReader;import java.io.Reader;import java.util.Map;/** * @author Yang Gaochao* @since 2018-03-09 */public class HttpClientHelper { /** * Use the get method to initiate an http request* * @param url The url of http to access * @return Access http The response text obtained after */ public String httpGetRequest(String url, Map<String, String> headers) { try { HttpClient httppclient = new HttpClient(); GetMethod method = new GetMethod(url); method.setRequestHeader("Content-Type", "application/json; charset=utf-8"); method.getParams().setParameter(HttpMethodParams.RETRY_HANDLER, new DefaultHttpMethodRetryHandler(3, false)); if (headers != null) { for (String key : headers.keySet()) { method.setRequestHeader(key, headers.get(key)); } } int statusCode = httpclient.executeMethod(method); if (statusCode == 200) { return parseInputStream(method.getResponseBodyAsStream()); } else { System.out.println(url + " status = " + statusCode); } } catch (Exception e) { e.printStackTrace(); } return null; } /** * Use the post method to initiate an http request* * @param url The url of http to be accessed * @param data data in the request* @return The response text obtained after accessing http*/ public String httpPostRequest(String url, String data, Map<String, String> headers) { try { HttpClient httppclient = new HttpClient(); PostMethod method = new PostMethod(url); method.setRequestHeader("Content-Type", "application/json;charset=UTF-8"); method.setRequestHeader("User-Agent", "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/34.0.1847.131 Safari/537.36"); if (headers != null) { for (String key : headers.keySet()) { method.setRequestHeader(key, headers.get(key)); } } method.setRequestEntity(new StringRequestEntity(data, "json", "utf-8")); int statusCode = httpclient.executeMethod(method); if (statusCode == 200) { return parseInputStream(method.getResponseBodyAsStream()); } else { System.out.println(url + " status = " + statusCode + parseInputStream(method.getResponseBodyAsStream())); } } catch (Exception e) { e.printStackTrace(); } return null; } /** * Parsing text data from java.io.Reader* * @param rd java.io.Reader object* @throws Exception throws an error when an error occurs*/ private String parseReader(Reader rd) throws Exception { BufferedReader brd = new BufferedReader(rd); String line; StringBuilder respondseContext = new StringBuilder(); while ((line = brd.readLine()) != null) { responseseContext.append(line).append("/n"); } //rd.close(); if (responseContext.length() > 0) { responseseContext.deleteCharAt(responseContext.length() - 1); } return respondseContext.toString(); } /** * Parsing text data from the input stream* * @param is input stream* @throws Exception throws an exception when an error occurs*/ private String parseInputStream(InputStream is) throws Exception { return parseReader(new BufferedReader(new InputStreamReader(is))); }} Here we mainly implement the method of calling the Http Rest service using GET and POST methods.
Test cases
Use JUnit to execute test cases. To implement the test, we added the following maven dependency
<dependency> <groupId>commons-httpclient</groupId> <artifactId>commons-httpclient</artifactId> <version>3.1</version> <scope>test</scope></dependency><dependency> <groupId>org.codehaus.jettison</groupId> <artifactId>jettison</artifactId> <version>1.3.3</version> <scope>test</scope></dependency>
package com.yanggaochao.springboot.learn.springbootjdbclearn;import org.codehaus.jettison.json.JSONObject;import org.junit.After;import org.junit.Before;import org.junit.Test;import java.net.URLEncoder;import java.util.ArrayList;import java.util.List;/** * Description: * * @author Yang Gaochao* @since 2018-03-09 */public class UserApiTest { private String userAddUrl = "http://localhost:3030/security/api/v1/user/add"; private String userLocateUrl = "http://localhost:3030/security/api/v1/user/locate/"; private String userDeleteUrl = "http://localhost:3030/security/api/v1/user/delete/"; private String userUpdateUrl = "http://localhost:3030/security/api/v1/user/update"; private String userMatchUrl = "http://localhost:3030/security/api/v1/user/match/"; JSONObject addUser = new JSONObject(); Long addUserId = null; List<Long> userIds = new ArrayList<>(); @Before public void before() throws Exception { addUser.put("name", "Beautiful Sheep"); JSONObject addResultJson = new JSONObject(new HttpClientHelper().httpPostRequest(userAddUrl, addUser.toString(), null)); assert ("success".equals(addResultJson.getString("result"))); addUserId = addResultJson.getJSONObject("item").getLong("id"); JSONObject user = new JSONObject(); user.put("name", "Pleasant Goat"); addResultJson = new JSONObject(new HttpClientHelper().httpPostRequest(userAddUrl, user.toString(), null)); assert ("success".equals(addResultJson.getString("result"))); userIds.add(addResultJson.getJSONObject("item").getLong("id")); user.put("name", "Grey Wolf"); addResultJson = new JSONObject(new HttpClientHelper().httpPostRequest(userAddUrl, user.toString(), null)); assert ("success".equals(addResultJson.getString("result"))); userIds.add(addResultJson.getJSONObject("item").getLong("id")); } @Test public void testUpdateUser() throws Exception { JSONObject user = new JSONObject(); user.put("name", "Smad Sheep"); user.put("id", addUserId); new HttpClientHelper().httpPostRequest(userUpdateUrl, user.toString(), null); JSONObject locateResultJson = new JSONObject(new HttpClientHelper().httpGetRequest(userLocateUrl + addUserId, null)); assert (user.getString("name").equals(locateResultJson.getJSONObject("item").getString("name"))); } @Test public void testMatchUser() throws Exception { JSONObject matchResultJson = new JSONObject(new HttpClientHelper().httpGetRequest(userMatchUrl + URLEncoder.encode("Sheep","UTF-8"), null)); assert (matchResultJson.has("items") && matchResultJson.getJSONArray("items").length() == 2); matchResultJson = new JSONObject(new HttpClientHelper().httpGetRequest(userMatchUrl + URLEncoder.encode("Wolf","UTF-8"), null)); assert (matchResultJson.has("items") && matchResultJson.getJSONArray("items").length() == 1); } @After public void after() throws Exception { if (addUserId != null) { JSONObject deleteResultJson = new JSONObject(new HttpClientHelper().httpGetRequest(userDeleteUrl + addUserId, null)); assert ("success".equals(deleteResultJson.getString("result"))); } for (Long userId : userIds) { JSONObject deleteResultJson = new JSONObject(new HttpClientHelper().httpGetRequest(userDeleteUrl + userId, null)); assert ("success".equals(deleteResultJson.getString("result"))); } }} Here, two test cases are declared in @Test, one tests the user modification function and the other tests the user fuzzy query function. @Before declares the preparations to be done before executing each test case. Here we first insert three pieces of data into the database, and at the same time, we also test the function of adding data and precise query. @After declares the cleanup after each test case is executed. Here we mainly delete the previously inserted data. The function of user deletion is tested synchronously here.
postscript
Here is a complete example of SpringBoot using JDBC Templet. If you have experience using JDBC Templet under Spring, then the main purpose of reducing a lot of configuration work in Spring.
The code involved in this article has been uploaded to GitHUB, and you can also download it locally
Summarize
The above is the entire content of this article. I hope that the content of this article has certain reference value for everyone's study or work. If you have any questions, you can leave a message to communicate. Thank you for your support to Wulin.com.