SpringBoot基础教程(三) | JdbcTemplate

JdbcTemplate介绍

Spring 对数据库的JDBC操作做了深层次的封装，可以将DataSource注册到JdbcTemplate之中，使我们可以轻易的完成对象关系映射，简化数据库操作，在SpringBoot中我们也可以很轻松的使用它。

导入Maven依赖

    <dependencies>
        <!--jdbc-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>

        <!--web-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <!--mysql驱动-->
        <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>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
    </dependencies>

配置文件编写

我们需要在SpringBoot的配置文件中写明一些数据库的基本信息，这里我使用的.properties文件。

# jdbc相关配置
spring.datasource.username=root
spring.datasource.password=yzhroot
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&serverTimezone=CTT

数据库初始化操作

既然是持久层的操作，肯定要对数据库的库表结构进行初始化，这里我创建的是一个book表，SQL语句如下：

CREATE DATABASE test DEFAULT character set = 'utf8';

use test;

create table book(
    `id` int(10) PRIMARY KEY auto_increment,
    `book_name` VARCHAR(100) not null DEFAULT '',
    `book_author` VARCHAR(100) not null DEFAULT ''
)DEFAULT CHARSET= utf8 ;

insert into book(book_name,book_author) values('Java编程规范','James Gosling');
insert into book(book_name,book_author) values('Java核心技术：卷I基础知识','James Gosling');
insert into book(book_name,book_author) values('Java数据结构和算法','James Gosling');
insert into book(book_name,book_author) values('Java与模式','James Gosling');

编码实现

这里我是针对book表进行了一系列增删改查操作。

• Book实体类

package traning.springboot.jdbctemplate.pojo;

/**
 * 书籍实体类
 */
public class Book {

    private Integer id;

    private String bookName;

    private String bookAuthor;

    public Book() {
    }

    public Integer getId() {
        return id;
    }

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

    public String getBookName() {
        return bookName;
    }

    public void setBookName(String bookName) {
        this.bookName = bookName;
    }

    public String getBookAuthor() {
        return bookAuthor;
    }

    public void setBookAuthor(String bookAuthor) {
        this.bookAuthor = bookAuthor;
    }
}

• BookDao接口

package traning.springboot.jdbctemplate.dao;

import traning.springboot.jdbctemplate.pojo.Book;

import java.util.List;

public interface BookDao {

    //查询所有书籍
    List<Book> queryAllBooks();

    //根据ID查询书籍
    Book queryBookByBookId(Integer id);

    //根据ID更新书籍
    boolean updateBookInfoByBookId(Book book,Integer id);

    //根据ID删除书籍
    boolean deleteBookByBookId(Integer id);

    //增加一本书籍
    boolean insertBook(Book book);
}

• BookDao的实现类

package traning.springboot.jdbctemplate.dao;

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 traning.springboot.jdbctemplate.pojo.Book;

import java.util.List;

@Repository
public class BookDaoImp1 implements BookDao{

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public List<Book> queryAllBooks() {
        String sql = "select * from book";
        return jdbcTemplate.query(sql,new Object[]{},new BeanPropertyRowMapper<>(Book.class));
    }

    @Override
    public Book queryBookByBookId(Integer id) {
        String sql = "select * from book where id = ?";
        return jdbcTemplate.queryForObject(sql,new Object[]{id},new BeanPropertyRowMapper<>(Book.class));
    }

    @Override
    public boolean updateBookInfoByBookId(Book book, Integer id) {
        String sql = "update book set book_name = ? and book_author = ? where id = ?";
        return jdbcTemplate.update(sql,book.getBookName(),book.getBookAuthor())>0;
    }

    @Override
    public boolean deleteBookByBookId(Integer id) {
        String sql = "delete from book where id = ?";
        return jdbcTemplate.update(sql,id)>0;
    }

    @Override
    public boolean insertBook(Book book) {
        String sql = "insert into book(book_name,book_author) values(?,?)";
        return jdbcTemplate.update(sql,book.getBookName(),book.getBookAuthor())>0;
    }
}

• BookService实现

package traning.springboot.jdbctemplate.service;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.stereotype.Service;
import traning.springboot.jdbctemplate.dao.BookDao;
import traning.springboot.jdbctemplate.pojo.Book;

import java.util.List;

@Service
public class BookService {

    @Autowired
    private BookDao bookDao;

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

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

    public boolean updateBookInfoByBookId(Book book, Integer id) {
        return bookDao.updateBookInfoByBookId(book,id);
    }

    public boolean deleteBookByBookId(Integer id) {
        return bookDao.deleteBookByBookId(id);
    }

    public boolean insertBook(Book book) {
        return bookDao.insertBook(book);
    }
}

• BookController实现

package traning.springboot.jdbctemplate.controller;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import traning.springboot.jdbctemplate.pojo.Book;
import traning.springboot.jdbctemplate.service.BookService;

import java.util.List;

@RestController
public class BookController {

    @Autowired
    private BookService bookService;

    @RequestMapping("/queryAllBooks")
    public List<Book> queryAllBooks() {
        return bookService.queryAllBooks();
    }

    @RequestMapping("/queryBookByBookId")
    public Book queryBookByBookId(Integer id) {
        return bookService.queryBookByBookId(id);
    }

    @RequestMapping("/updateBookInfoByBookId")
    public boolean updateBookInfoByBookId(Book book, Integer id) {
        return bookService.updateBookInfoByBookId(book,id);
    }

    @RequestMapping("/deleteBookByBookId")
    public boolean deleteBookByBookId(Integer id) {
        return bookService.deleteBookByBookId(id);
    }

    @RequestMapping("/insertBook")
    public boolean insertBook(Book book) {
        return bookService.insertBook(book);
    }

}

启动项目测试

我们启动项目后，可以使用PostMan对接口进行调试，由于篇幅有限，这里只列出一个接口的测试结果。

• 查询所有书籍接口测试结果

  
  

完整的工程代码链接：https://github.com/youzhihua/springboot-training