Spring Boot整合JdbcTemplate

前期回顾：
创建Spring Boot项目
Spring Boot项目基础配置
Spring Boot上传文件
Spring Boot全局异常处理、全局数据、参数预处理
Spring Boot自定义拦截器
Spring Boot整合Servlet、Filter和Listener
Spring Boot整合AOP

JdbcTemplate是Spring提供的JDBC的一套模板框架，目的是利用AOP技术减少了直接使用JDBC的大量重复代码。Spring Boot对JdbcTemplate提供了默认的装载配置类JdbcTemplateAutoConfiguration，部分源码为：

@Configuration
@ConditionalOnClass({DataSource.class, JdbcTemplate.class})
@ConditionalOnSingleCandidate(DataSource.class)
@AutoConfigureAfter({DataSourceAutoConfiguration.class})
@EnableConfigurationProperties({JdbcProperties.class})
public class JdbcTemplateAutoConfiguration {
    public JdbcTemplateAutoConfiguration() {
    }

    @Configuration
    @Import({JdbcTemplateAutoConfiguration.JdbcTemplateConfiguration.class})
    static class NamedParameterJdbcTemplateConfiguration {
        NamedParameterJdbcTemplateConfiguration() {
        }

        @Bean
        @Primary
        @ConditionalOnSingleCandidate(JdbcTemplate.class)
        @ConditionalOnMissingBean({NamedParameterJdbcOperations.class})
        public NamedParameterJdbcTemplate namedParameterJdbcTemplate(JdbcTemplate jdbcTemplate) {
            return new NamedParameterJdbcTemplate(jdbcTemplate);
        }
    }

    @Configuration
    static class JdbcTemplateConfiguration {
        private final DataSource dataSource;
        private final JdbcProperties properties;

        JdbcTemplateConfiguration(DataSource dataSource, JdbcProperties properties) {
            this.dataSource = dataSource;
            this.properties = properties;
        }

        @Bean
        @Primary
        @ConditionalOnMissingBean({JdbcOperations.class})
        public JdbcTemplate jdbcTemplate() {
            JdbcTemplate jdbcTemplate = new JdbcTemplate(this.dataSource);
            Template template = this.properties.getTemplate();
            jdbcTemplate.setFetchSize(template.getFetchSize());
            jdbcTemplate.setMaxRows(template.getMaxRows());
            if (template.getQueryTimeout() != null) {
                jdbcTemplate.setQueryTimeout((int)template.getQueryTimeout().getSeconds());
            }

            return jdbcTemplate;
        }
    }
}

源码解释：通过@ConditionalOnClass({DataSource.class, JdbcTemplate.class})可以看到加载此配置需要DataSource和JdbcTemplate类，并且在@ConditionalOnSingleCandidate(DataSource.class)看到DataSource需为单例，若开发者没提供JdbcOperations，Spring Boot会自动注入JdbcTemplate，由此可以发现，如果项目中要使用JdbcTemplate，只需要提供JdbcTemplate和DataSource的依赖就行。
接下来就通过一个小Demo来演示下JdbcTemplate的使用：

• maven依赖：

<!--jdbc的Spring Boot依赖-->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<!--mysql驱动-->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>
<!--数据库连接池-->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.1.20</version>
</dependency>

• 配置文件

spring:
  datasource:
    #指定数据库连接池
    type: com.alibaba.druid.pool.DruidDataSource
    #连接的url，SpringBoot指的是数据名
    url: jdbc:mysql://127.0.0.1:3306/SpringBoot
    #用户名
    username: root
    #密码
    password: rootroot

• 建库建表

create database SpringBoot default charset utf8mb4;

use SpringBoot;

drop table if exists springboot_student;

create table springboot_student(
  id bigint(20) primary key auto_increment,
  name varchar(50) not null comment '姓名',
  sex varchar(2) not null comment '性别',
  age int(2) not null comment '年龄'
)engine = innodb default charset utf8mb4 comment '学生表';

• 定义实体类

import lombok.Getter;
import lombok.Setter;

@Getter
@Setter
public class Student {

    private Long id;

    private String name;

    private String sex;

    private Integer age;
}

• Dao层

import java.util.List;

import com.springboot.data.dto.Student;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

@Repository("studentDao")
public class StudentDao {

    @Autowired
    JdbcTemplate jdbcTemplate;

    public boolean add(Student student) {
        return jdbcTemplate.update("insert into springboot_student (name, sex, age) values(?,?,?)", student.getName()
                , student.getSex(), student.getAge()) > 0;
    }

    public boolean update(Student student) {
        return jdbcTemplate.update("update springboot_student set name = ?,sex = ?,age = ? where id = ?",
                student.getName(), student.getSex(), student.getAge()) > 0;
    }

    public boolean delete(Long id) {
        return jdbcTemplate.update("delete from springboot_student where id = ?", id) > 0;
    }

    public List<Student> find() {
        return jdbcTemplate.query("select * from springboot_student", new BeanPropertyRowMapper<>(Student.class));
    }
}

• Service

import java.util.List;

import com.springboot.data.dto.Student;

public interface StudentService {

    List<Student> add(Student student);

    List<Student> update(Student student);
}

import java.util.List;

import com.springboot.data.dao.StudentDao;
import com.springboot.data.dto.Student;
import com.springboot.data.service.StudentService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

@Service("studentService")
public class StudentServiceImpl implements StudentService {

    @Autowired
    StudentDao studentDao;

    @Transactional(propagation = Propagation.REQUIRED,rollbackFor = RuntimeException.class)
    @Override
    public List<Student> add(Student student) {
        studentDao.add(student);
        return studentDao.find();
    }

    @Transactional(propagation = Propagation.REQUIRED,rollbackFor = RuntimeException.class)
    @Override
    public List<Student> update(Student student) {
        studentDao.update(student);
        return studentDao.find();
    }
}

• Controller

import java.util.List;

import com.springboot.data.dto.Student;
import com.springboot.data.service.StudentService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController
@RequestMapping("student")
public class StudentController {

    @Autowired
    StudentService studentService;

    @PostMapping("/add")
    public List<Student> add(@RequestBody Student student) {
        return studentService.add(student);
    }

    @PostMapping("/update")
    public List<Student> update(@RequestBody Student student) {
        return studentService.update(student);
    }
}

演示代码中就只写了个新增和修改的API了，可以通过Postman或其它工具调用测试下。

如有不到之处，欢迎各位留言指正，不慎感激。
更多文章：
点击跳转CSDN博客
点击跳转简书博客
微信公众号：代码小搬运