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对接口进行调试，由于篇幅有限，这里只列出一个接口的测试结果。
-
查询所有书籍接口测试结果
网友评论