SpringBoot-整合JdbcTemplate

整合JdbcTemplate

1.源码解释

Jdbc Template 是Spring 提供的一套JDBC模板，SpringBoot中对Jdbc Template的使用提供了自动化配置类JdbcTemplateAutoConfiguration，部分源码如下：

@AutoConfiguration(after = DataSourceAutoConfiguration.class)
@ConditionalOnClass({ DataSource.class, JdbcTemplate.class })
@ConditionalOnSingleCandidate(DataSource.class)
@EnableConfigurationProperties(JdbcProperties.class)
@Import({ DatabaseInitializationDependencyConfigurer.class, JdbcTemplateConfiguration.class,
		NamedParameterJdbcTemplateConfiguration.class })
public class JdbcTemplateAutoConfiguration {

}

从源码可以看出，当classpath下存在DataSource和JdbcTemplate并且DataSource只有一个实例时，自动配置才会生效。若没有提JdbcOperations，则SpringBoot会自动向容器注入一个JdbcTemplate。要想使用JdbcTemplate，只需要提供JdbcTemplate依赖和DataSource，操作步骤如下：

2. 创建数据库和表

CREATE DATABASE `chapter05` DEFAULT CHARACTER SET utf8;

USE `chapter05`

CREATE TABLE `book`(
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(128) DEFAULT NULL,
`author` VARCHAR(64) DEFAULT NULL,
PRIMARY KEY(`id`)			
) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO `book`(`id`,`name`,`author`) VALUES (1,'刻在你心底的名字','DiFer'),(2,'活着','余华');

3. 创建项目

创建Spring Boot 项目，添加依赖

<!--jdbc-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
<!-- 数据库-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.22</version>
        </dependency>
<!--        druid-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.2.8</version>
        </dependency>

4. 数据库配置

在application.yml 中配置数据库基本连接信息：

spring:
  datasource:
    username: root
    password: root
    url: jdbc:mysql://localhost:3306/chapter05?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf-8
    driver-class-name: com.mysql.cj.jdbc.Driver
    type: com.alibaba.druid.pool.DruidDataSource
    #druid 数据源专有配置
    druid:
      filters: stat,wall
      aop-patterns: com.chen.*
      stat-view-servlet:
        enabled: true
        login-username: admin
        login-password: 123456
        reset-enable: false

      web-stat-filter:
        enabled: true
        url-pattern: /*
        exclusions: '*.js,*.gif,*jpg,*.png,*.ico,/druid/*'

      filter:
        stat:
          slow-sql-millis: 1000
          log-slow-sql: true
          enabled: true
        wall:
          enabled: true

注意：msql8.0版本以上要有时区的配置 serverTimezone=GMT%2B8

5. 创建实体类

创建Book实体类，代码如下：

public class Book {
    private Integer id;
    private String name;
    private String author;

    @Override
    public String toString() {
        return "Book{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", author='" + author + '\'' +
                '}';
    }

    public Book() {
    }

    public Book(Integer id, String name, String author) {
        this.id = id;
        this.name = name;
        this.author = author;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getAuthor() {
        return author;
    }

    public void setAuthor(String author) {
        this.author = author;
    }
}

6. 创建数据库访问层

创建BookDao，代码如下：

@Repository
public class BookDao {

   @Autowired
    JdbcTemplate jdbcTemplate;

   public int addBook(Book book){
       return jdbcTemplate.update("insert into book(name,author) values (?,?)",book.getName(),book.getAuthor());
   }

   public int updateBook(Book book){
       return jdbcTemplate.update("update book set name=?,author=? where id=?",book.getName(),book.getAuthor(),book.getId());
   }

    public int deleteBookById (Integer id) {
        return jdbcTemplate.update("delete from book where id=?",id);
    }
    
    public Book getBookById(Integer id){
       return jdbcTemplate.queryForObject("select * from book where id=?",new BeanPropertyRowMapper<>(Book.class),id);
    }
    
    public List<Book> getAllBooks(){
       return jdbcTemplate.query("select * from book",new BeanPropertyRowMapper<>(Book.class));
    }

7.创建Service和Controller

创建BookService和BookController，代码如下：

@Service
public class BookService {
 @Autowired
 BookDao bookDao;

 public int addBook(Book book){
     return bookDao.addBook(book);
 }

 public int updateBook(Book book){
     return bookDao.updateBook(book);
 }

 public int deleteBookById (Integer id) {
     return bookDao.deleteBookById(id);
 }

 public Book getBookById(Integer id){
     return bookDao.getBookById(id);
 }

 public List<Book> getAllBooks(){
     return bookDao.getAllBooks();
 }
}

@RestController
public class BookController {

    @Autowired
    BookService bookService;

    @GetMapping("/bookOps")
    public void bookOps(){
        Book b1 = new Book();
        b1.setName("SpringBoot");
        b1.setAuthor("Jack");
        int i = bookService.addBook(b1);
        System.out.println("addBook>>>"+i);

        Book b2 = new Book();
        b2.setId(2);
        b2.setName("兄弟");
        int updateBook = bookService.updateBook(b2);
        System.out.println("update>>>"+updateBook);

        Book b3 = bookService.getBookById(1);
        System.out.println("getBookById>>>"+b3);

        List<Book> allBooks = bookService.getAllBooks();
        System.out.println("getAllBooks>>>"+allBooks);
    }
}

控制台打印日志：

数据库表更新：