SpringBoot2 | 第五篇：整合JdbcTemplate

Posted on | In

Spring Framework对数据库的操作在JDBC上面做了深层次的封装，通过依赖注入功能，可以将 DataSource 注册到JdbcTemplate之中，使我们可以轻易的完成对象关系映射，并有助于规避常见的错误；==SpringBoot 默认会自动配置DataSource==，在SpringBoot中我们可以很轻松的使用它。

特点

  • 速度快，对比其它的ORM框架而言，JDBC的方式无异于是最快的
  • 配置简单，Spring自家出品，几乎没有额外配置
  • 学习成本低，毕竟JDBC是基础知识，JdbcTemplate更像是一个DBUtils

[TOC]

环境/版本一览：

  • 开发工具：Intellij IDEA 2018.2.2
  • springboot： 2.0.5.RELEASE
  • jdk：1.8.0_171
  • maven：3.3.9

1、搭建

1538279053874

2、pom.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
<!-- jdbc 依赖携带 HikariCP -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
    <scope>test</scope>
</dependency>
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.16.22</version>
</dependency>

3、application.yml

数据库如果没有引入指定连接池依赖，则默认使用HikariCP

1
2
3
4
5
6
7
spring:
  datasource:
    driver-class-name: com.mysql.jdbc.Driver
    # 基本属性
    url: jdbc:mysql://localhost:3306/chapter5?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&serverTimezone=UTC&useSSL=false
    username: root
    password: 123456

4、sql

创建一张 user 的表

1
2
3
4
5
6
CREATE TABLE `user` (
  `id` int(8) NOT NULL AUTO_INCREMENT COMMENT '主键自增',
  `username` varchar(50) NOT NULL COMMENT '用户名',
  `password` varchar(50) NOT NULL COMMENT '密码',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表';

5、entity

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
package com.fatal.entity;

import lombok.Data;
import lombok.experimental.Accessors;

/**
 * User 实体
 * @author: Fatal
 * @date: 2018/9/30 0030 11:55
 */
@Data
@Accessors(chain = true)
public class User {

    private Long id;
    private String username;
    private String password;

}

6、Dao

IUserDao

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
package com.fatal.dao;

import com.fatal.entity.User;

import java.util.List;

/**
 * @author: Fatal
 * @date: 2018/9/30 0030 14:20
 */
public interface IUserDao {

    public List<User> queryUsers();
    public User getUser(Long id);
    public int delUser(Long id);
    public int addUser( User user);
    public int editUser( Long id, User user);

}

UserDaoImpl

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
package com.fatal.dao.impl;

import com.fatal.dao.IUserDao;
import com.fatal.entity.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * @author: Fatal
 * @date: 2018/9/30 0030 14:24
 */
@Repository
public class UserDaoImpl implements IUserDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public List<User> queryUsers() {
        // 查询所有用户
        String sql = "SELECT * FROM user";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(User.class));
    }

    @Override
    public User getUser(Long id) {
        // 根据主键ID查询
        String sql = "SELECT * FROM user WHERE id = ?";
        return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(User.class), id);
    }

    @Override
    public int delUser(Long id) {
        // 根据主键ID删除用户信息
        String sql = "DELETE FROM user WHERE id = ?";
        return jdbcTemplate.update(sql, id);
    }

    @Override
    public int addUser(User user) {
        // 添加用户
        String sql = "INSERT user(username, password) values(?, ?)";
        return jdbcTemplate.update(sql, user.getUsername(), user.getPassword());
    }

    @Override
    public int editUser(Long id, User user) {
        // 根据主键ID修改用户信息
        String sql = "UPDATE user SET username = ? ,password = ? WHERE id = ?";
        return jdbcTemplate.update(sql, user.getUsername(), user.getPassword(), id);
    }
}

7、Service

IUserService

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
package com.fatal.service;

import com.fatal.entity.User;

import java.util.List;

/**
 * @author: Fatal
 * @date: 2018/9/30 0030 14:26
 */
public interface IUserService {

    public List<User> queryUsers();
    public User getUser(Long id);
    public int delUser(Long id);
    public int addUser( User user);
    public int editUser( Long id, User user);

}

UserServiceImpl

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
package com.fatal.service.impl;

import com.fatal.dao.IUserDao;
import com.fatal.entity.User;
import com.fatal.service.IUserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * @author: Fatal
 * @date: 2018/9/30 0030 14:27
 */
@Service
public class UserServiceImpl implements IUserService {

    @Autowired
    private IUserDao userDao;

    @Override
    public List<User> queryUsers() {
        return userDao.queryUsers();
    }

    @Override
    public User getUser(Long id) {
        return userDao.getUser(id);
    }

    @Override
    public int delUser(Long id) {
        return userDao.delUser(id);
    }

    @Override
    public int addUser(User user) {
        return userDao.addUser(user);
    }

    @Override
    public int editUser(Long id, User user) {
        return userDao.editUser(id, user);
    }
}

8、Controller

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
package com.fatal.controller;

import com.fatal.entity.User;
import com.fatal.service.IUserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.List;

/**
 * User 控制器
 * @author: Fatal
 * @date: 2018/9/30 0030 12:00
 */
@RestController
@RequestMapping("/users")
public class UserController {

    @Autowired
    private IUserService userService;

    @GetMapping
    public List<User> queryUsers() {
        return userService.queryUsers();
    }

    @GetMapping("/{id}")
    public User getUser(@PathVariable Long id) {
        return userService.getUser(id);
    }

    @DeleteMapping("/{id}")
    public int delUser(@PathVariable Long id) {
        return userService.delUser(id);
    }

    @PostMapping
    public int addUser(@RequestBody User user) {
        return userService.addUser(user);
    }

    @PutMapping("/{id}")
    public int editUser(@PathVariable Long id, @RequestBody User user) {
        return userService.editUser(id, user);
    }

}

7、Test

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
package com.fatal;

import com.fatal.entity.User;
import lombok.extern.slf4j.Slf4j;
import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
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.net.URL;
import java.util.List;

@RunWith(SpringRunner.class)
@SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT)
@Slf4j
public class Chapter5ApplicationTests {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @LocalServerPort
    private int port;

    private String url;

    private String urlWithId;

    @Autowired
    private TestRestTemplate template;

    @Before
    public void before() throws Exception{
        this.url = "http://localhost:" + port + "/users";
        this.urlWithId = "http://localhost:" + port + "/users/{id}";
    }

    /**
     *  postForEntity(String url, Object request, Class<T> responseType, Object... urlVariables)
     *  String url ：请求路径
     *  Object request ：参数
     *  Class<T> responseType ：返回值类型
     *  Object... urlVariables ：条件参数，对应请求路径上的占位符
     */
    @Test
    public void addUser() {
        User user = new User().setUsername("米彩").setPassword("20");
        template.postForEntity(url, user, Integer.class);
        log.info("[添加用户成功]\n");
    }

    @Test
    public void exchange() {
        ResponseEntity<List<User>> responseEntity = template.exchange(url, HttpMethod.GET, null,
                new ParameterizedTypeReference<List<User>>() {
                });
        log.info("[查询所有] - [{}]\n", responseEntity.getBody());
    }

    @Test
    public void getForEntity() {
        ResponseEntity<User> responseEntity = template.getForEntity(urlWithId, User.class, 1);
        log.info("[主键查询] - [{}]\n", responseEntity.getBody());
    }

    @Test
    public void put() {
        User user = new User().setUsername("米琪").setPassword("20");
        template.put(urlWithId, user, 1);
        log.info("[修改用户成功]\n");
    }

    @Test
    public void delete() {
        template.delete(urlWithId, 2);
        log.info("[删除用户成功]");
    }
    
    @Test
    public void dataSourceTest() {
        DataSource dataSource = jdbcTemplate.getDataSource();
        Assert.assertTrue(dataSource instanceof HikariDataSource);
    }
}

总结

本章介绍了JdbcTemplate常用的几种操作，详细请参考JdbcTemplate API文档

SpringBoot的知识已经有前辈在我们之前探索了。比较喜欢的博主有：唐亚峰 | Battcn、方志朋的专栏、程序猿DD、纯洁的微笑。对这门技术感兴趣的可以去他们的博客逛逛。谢谢他们的分享~~

以上文章是我用来学习的Demo，都是基于 SpringBoot2.x 版本。

源码地址: https://github.com/ynfatal/springboot2-learning/tree/master/chapter5

学习 唐亚峰 前辈的经验