SpringBoot is a product that was born to simplify the creation, operation, debugging, and deployment of Spring applications. The automatic assembly feature allows us to better focus on the business itself rather than external XML configuration. We only need to follow the specifications and introduce relevant dependencies to easily build a WEB project.
Spring Framework's database operations are deeply encapsulated in JDBC. Through the dependency injection function, DataSource can be registered in JdbcTemplate, allowing us to easily complete object-relational mapping and help avoid common errors. In SpringBoot, we can easily use it.
Features
Import dependencies
Add dependency on JdbcTemplate in pom.xml
<!-- Spring JDBC dependency package, using spring-boot-starter-jdbc or spring-boot-starter-data-jpa will automatically obtain HikariCP dependency--><dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jdbc</artifactId></dependency><!-- MYSQL package--><dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId></dependency><!-- Tomcat is embedded by default Container, if you need to replace the container, it is also very simple --><dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId></dependency>
Connect to the database
Add the following configuration in application.properties. It is worth noting that SpringBoot will automatically configure DataSource by default, which will prioritize the use of HikariCP connection pool. If there is no dependency, select tomcat-jdbc. If neither of the first two is available, select Commons DBCP2. The spring.datasource.type property can specify other types of connection pools
spring.datasource.url=jdbc:mysql://localhost:3306/chapter4?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&useSSL=falsespring.datasource.password=rootspring.datasource.username=root#spring.datasource.type#More subtle configurations can be adjusted by the following prefix#spring.datasource.hikari#spring.datasource.tomcat#spring.datasource.dbcp2
Start the project, through the log, you can see that by default, the HikariDataSource is injected.
2018-05-07 10:33:54.021 INFO 9640 --- [ main] osjeaAnnotationMBeanExporter : Bean with name 'dataSource' has been autodetected for JMX exposure2018-05-07 10:33:54.026 INFO 9640 --- [ main] osjeaAnnotationMBeanExporter : Location MBean 'dataSource': registering with JMX server as MBean [com.zaxxer.hikari:name=dataSource,type=HikariDataSource]2018-05-07 10:33:54.071 INFO 9640 --- [ main] osbwembedded.tomcat.TomcatWebServer : Tomcat started on port(s): 8080 (http) with context path ''2018-05-07 10:33:54.075 INFO 9640 --- [ main] com.battcn.Chapter4Application : Started Chapter4Application in 3.402 seconds (JVM running) for 3.93)
Specific code
After completing the basic configuration, perform specific encoding operations. In order to reduce the amount of code, I will not write interfaces such as UserDao and UserService. I will use JdbcTemplate directly in the Controller to access the database. This is not standardized. Don’t learn from me...
Table structure
Create a table with t_user
CREATE TABLE `t_user` ( `id` int(8) NOT NULL AUTO_INCREMENT COMMENT 'primary key increment', `username` varchar(50) NOT NULL COMMENT 'username', `password` varchar(50) NOT NULL COMMENT 'password', PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='user table';
Entity Class
package com.battcn.entity;/** * @author Levin * @since 2018/5/7 0007 */public class User { private Long id; private String username; private String password; // TODO omit get set}restful style interface
package com.battcn.controller;import com.battcn.entity.User;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.jdbc.core.BeanPropertyRowMapper;import org.springframework.jdbc.core.JdbcTemplate;import org.springframework.web.bind.annotation.*;import java.util.List;/** * @author Levin * @since 2018/4/23 0023 */@RestController@RequestMapping("/users")public class SpringJdbcController { private final JdbcTemplate jdbcTemplate; @Autowired public SpringJdbcController(JdbcTemplate jdbcTemplate) { this.jdbcTemplate = jdbcTemplate; } @GetMapping public List<User> queryUsers() { // Query all users String sql = "select * from t_user"; return jdbcTemplate.query(sql, new Object[]{}, new BeanPropertyRowMapper<>(User.class)); } @GetMapping("/{id}") public User getUser(@PathVariable Long id) { // Query String sql = "select * from t_user where id = ?"; return jdbcTemplate.queryForObject(sql, new Object[]{id}, new BeanPropertyRowMapper<>(User.class)); } @DeleteMapping("/{id}") public int delUser(@PathVariable Long id) { // Delete user information according to the primary key ID String sql = "DELETE FROM t_user WHERE id = ?"; return jdbcTemplate.update(sql, id); } @PostMapping public int addUser(@RequestBody User user) { // Add user String sql = "insert into t_user(username, password) values(?, ?)"; return jdbcTemplate.update(sql, user.getUsername(), user.getPassword()); } @PutMapping("/{id}") public int editUser(@PathVariable Long id, @RequestBody User user) { // Modify user information according to the primary key ID String sql = "UPDATE t_user SET username = ? ,password = ? WHERE id = ?"; return jdbcTemplate.update(sql, user.getUsername(), user.getPassword(), id); }}test
Since the above interface is a restful-style interface, addition and modification cannot be completed through the browser, we need to write junit ourselves or use tools such as postman.
Create unit test Chapter4ApplicationTests and simulate request operations such as GET, POST, PUT, DELETE, etc. through TestRestTemplate
package com.battcn;import com.battcn.entity.User;import org.junit.Test;import org.junit.runner.RunWith;import org.slf4j.Logger;import org.slf4j.LoggerFactory;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.boot.test.context.SpringBootTest;import org.springframework.boot.test.web.client.TestRestTemplate;import org.springframework.boot.web.server.LocalServerPort;import org.springframework.core.ParameterizedTypeReference;import org.springframework.http.HttpMethod;import org.springframework.http.ResponseEntity;import org.springframework.test.context.junit4.SpringRunner;import java.util.List;/** * @author Levin */@RunWith(SpringRunner.class)@SpringBootTest(classes = Chapter4Application.class, webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT)public class Chapter4ApplicationTests { private static final Logger log = LoggerFactory.getLogger(Chapter4ApplicationTests.class); @Autowired private TestRestTemplate template; @LocalServerPort private int port; @Test public void test1() throws Exception { template.postForEntity("http://localhost:" + port + "/users", new User("user1", "pass1"), Integer.class); log.info("[add user success]/n"); // TODO If it is the returned collection, use exchange instead of getForEntity, which needs to force the type ResponseEntity<List<User>> response2 = template.exchange("http://localhost:" + port + "/users", HttpMethod.GET, null, new ParameterizedTypeReference<List<User>>() { }); final List<User> body = response2.getBody(); log.info("[Query all] - [{}]/n", body); Long userId = body.get(0).getId(); ResponseEntity<User> response3 = template.getForEntity("http://localhost:" + port + "/users/{id}", User.class, userId); log.info("[Primary key query] - [{}]/n", response3.getBody()); template.put("http://localhost:" + port + "/users/{id}", new User("user11", "pass11"), userId); log.info("[modify user successfully]/n"); template.delete("http://localhost:" + port + "/users/{id}", userId); log.info("[delete user successfully]"); }}Summarize
This chapter introduces several commonly used operations of JdbcTemplate. For details, please refer to the JdbcTemplate API documentation.
Many bigwigs have written tutorials about SpringBoot. If there are any similarities, please forgive me. This tutorial is based on the latest spring-boot-starter-parent: 2.0.1.RELEASE, and the features of the new version will be introduced together...