整合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);
}
}
控制台打印日志:
数据库表更新: