​精通 Spring Boot 系列文（10）

Spring Boot 使用 JdbcTemplate

JdbcTemplate 主要为数据访问提供了模板方案，将数据库存取的工作进行简化。

案例：访问数据

1）编辑 pom.xml 文件

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0modelVersion>

    <groupId>com.nxgroupId>
    <artifactId>springbootdataartifactId>
    <version>1.0-SNAPSHOTversion>

    <parent>
        <groupId>org.springframework.bootgroupId>
        <artifactId>spring-boot-starter-parentartifactId>
        <version>2.2.6.RELEASEversion>
        <relativePath/>
    parent>

    <properties>
        <project.build.sourceEncoding>UTF-8project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8project.reporting.outputEncoding>
        <java.version>1.8java.version>
    properties>

    <dependencies>

        
        <dependency>
            <groupId>org.springframework.bootgroupId>
            <artifactId>spring-boot-starter-webartifactId>
        dependency>

        
        <dependency>
            <groupId>org.springframework.bootgroupId>
            <artifactId>spring-boot-starter-thymeleafartifactId>
        dependency>

        
        <dependency>
            <groupId>mysqlgroupId>
            <artifactId>mysql-connector-javaartifactId>
        dependency>

        
        <dependency>
            <groupId>org.springframework.bootgroupId>
            <artifactId>spring-boot-starter-jdbcartifactId>
        dependency>

        <dependency>
            <groupId>junitgroupId>
            <artifactId>junitartifactId>
            <scope>testscope>
        dependency>

    dependencies>
project>

2）编辑 application.properties 文件

####################
### 数据源信息配置 ###
####################
# 数据库地址
spring.datasource.url=jdbc:mysql://localhost:3306/springbootdata?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true
# 用户名
spring.datasource.username=root
# 密码
spring.datasource.password=1234
# 数据库驱动
spring.datasource.driverClassName=com.mysql.cj.jdbc.Driver
# 指定连接池中最大的活跃连接数.
spring.datasource.max-active=20
# 指定连接池最大的空闲连接数量.
spring.datasource.max-idle=8
# 指定必须保持连接的最小值
spring.datasource.min-idle=8
# 指定启动连接池时，初始建立的连接数量
spring.datasource.initial-size=10

3）创建 User 持久类

public class User implements Serializable{

    private static final long serialVersionUID = 1L;

    private int id ;
    private String loginName ;
    private String username ;
    private String password;

    // setXxx 和 getXxx 方法
}

4）创建 UserRepository 数据访问接口

@Repository
public class UserRepository {

    // 注入JdbcTemplate模板对象
    @Resource
    private JdbcTemplate jdbcTemplate;

    // 插入数据
    public int insertUser(){
        String sql = "insert into tb_user(login_name ,username ,passWord) "
                + "values (?,?,?),(?,?,?),(?,?,?)";
        Object[] args = new Object[]{"s1","小黄","123456","s2","小绿","123456"
                ,"s3","小红","123456"};
        // 参数1：插入数据的sql语句 
        // 参数2: 对应sql语句中占位符?的参数
        return jdbcTemplate.update(sql, args);
    }

    // 根据userName查询数据
    public User selectByUsername(String username) {
        String sql = "select * from tb_user where username = ?";
        RowMapper rowMapper = new BeanPropertyRowMapper<>(User.class);
        User user = jdbcTemplate.queryForObject(sql, new Object[] { username }, rowMapper);
        return user;
    }

    // 根据id查询数据
    public User findUserById(int id) {
        String sql = "select * from tb_user where id=?";
        RowMapper rowMapper = new BeanPropertyRowMapper<>(User.class);
        return jdbcTemplate.queryForObject(sql, new Object[] { id }, rowMapper);
    }

    // 查询所有数据
    public List findAll() {
        String sql = "select * from tb_user";
        // 申明结果集的映射rowMapper，将结果集的数据映射成User对象数据
        RowMapper rowMapper = new BeanPropertyRowMapper<>(User.class);
        return jdbcTemplate.query(sql, rowMapper);
    }

    // 根据id删除数据
    public void delete(final Integer id) {
        String sql = "delete from tb_user where id=?";
        jdbcTemplate.update(sql, new Object[] { id });
    }

    // 修改数据
    public void update(final User user) {
        String sql = "update tb_user set username=?, login_name=? where id=?";
        jdbcTemplate.update(sql,
            new Object[] { user.getUsername(), user.getLoginName(), user.getId()});
    }

    // 插入数据获取被插入数据的主键
    public User insertGetKey(User user) {
        String sql = "insert into tb_user(username,login_name,password) values(?,?,?)";
        // 定义插入数据后获取主键的对象
        KeyHolder holder = new GeneratedKeyHolder();
        jdbcTemplate.update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                // 插入数据后，将被插入数据的主键返回回来
                PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                ps.setString(1, user.getUsername());
                ps.setString(2, user.getLoginName());
                ps.setString(3, user.getPassword());
                return ps;
            }
        }, holder);
        // 获取被插入数据库的主键 然后注入到user对象中去
        int newUserId = holder.getKey().intValue();
        user.setId(newUserId);
        return user;
    }
}

5）创建 UserService 业务层类

@Service
public class UserService {

    // 注入UserRepository
    @Resource
    private UserRepository userRepository;

    public int insertUser(){
        return userRepository.insertUser();
    }

    public User selectByUsername(String username){
        return userRepository.selectByUsername(username);
    }

    public List findAll(){
        return userRepository.findAll();
    }

    public User insertGetKey(User user) {
        return userRepository.insertGetKey(user);
    }

    public void update(User user) {
        userRepository.update(user);
    }

    public void delete(Integer id) {
        userRepository.delete(id);
    }
}

6）定义 UserController 控制器类

@RestController
@RequestMapping("/user")
public class UserController {
    @Resource
    private UserService userService;

    @RequestMapping("/insertUser")
    public String insertUser(){
        return "插入数据["+userService.insertUser()+"]条";
    }

    @RequestMapping("/insertGetKey")
    public User insertGetKey(User user) {
        return userService.insertGetKey(user);
    }

    @RequestMapping("/selectByUsername")
    public User selectByUserNm(String username){
        return userService.selectByUsername(username);
    }

    @RequestMapping("/findAll")
    public List findAll(){
        return userService.findAll();
    }

    @RequestMapping("/update")
    public void update(User user) {
        userService.update(user);
    }

    @RequestMapping("/delete")
    public void delete(Integer id) {
        userService.delete(id);
    }
}

7）测试

http://localhost:8080/user/insertUser