Spring 数据访问 - JdbcTemplate 入门
一、项目准备
（一）使用 IDEA 开发工具新建 Maven 工程.
（二）修改 pom.xml, 添加所需的依赖
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-devtools</artifactId>
<optional>true</optional>
</dependency>
（三）新建数据库及表
drop database study_jdbctemplate;
create database study_jdbctemplate;
use study_jdbctemplate;
drop table if exists t_user;
create table t_user
(
id int primary key auto_increment,
username varchar(100),
password varchar(200),
email varchar(200)
);
insert into t_user (id, username, password, email) values (1, 'zs', 'zs', 'zs@abc.com');
insert into t_user (id, username, password, email) values (2, 'ls', 'ls', 'ls@abc.com');
insert into t_user (id, username, password, email) values (3, 'a', 'a', 'a@abc.com');
insert into t_user (id, username, password, email) values (4, 'b', 'b', 'b@abc.com');
insert into t_user (id, username, password, email) values (5, 'c', 'c', 'c@abc.com');
insert into t_user (id, username, password, email) values (6, 'd', 'd', 'd@abc.com');
insert into t_user (id, username, password, email) values (7, 'e', 'e', 'e@abc.com');
insert into t_user (id, username, password, email) values (8, 'f', 'f', 'f@abc.com');
insert into t_user (id, username, password, email) values (9, 'g', 'g', 'g@abc.com');
insert into t_user (id, username, password, email) values (10, 'h', 'h', 'h@abc.com');
insert into t_user (id, username, password, email) values (11, 'i', 'i', 'i@abc.com');
insert into t_user (id, username, password, email) values (12, 'j', 'j', 'j@abc.com');
insert into t_user (id, username, password, email) values (13, 'k', 'k', 'k@abc.com');
insert into t_user (id, username, password, email) values (14, 'l', 'l', 'l@abc.com');
（四）配置数据库连接
# resources/application.properties
spring.datasource.name=mysql
spring.datasource.type=com.zaxxer.hikari.HikariDataSource
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/study_jdbctemplate?useUnicode=true&characterEncoding=utf8&useSSL=false&allowPublicKeyRetrieval=true
spring.datasource.username=root
spring.datasource.password=root
（五）配置日志
<?xml version="1.0" encoding="UTF-8"?>
<configuration debug="false">
<include resource="org/springframework/boot/logging/logback/base.xml"/>
<statusListener class="ch.qos.logback.core.status.NopStatusListener"/>
<appender name="console" class="ch.qos.logback.core.ConsoleAppender">
<encoder>
<pattern>%date{HH:mm:ss.SSS} [%thread] %-5level %logger{36} - %msg%n</pattern>
</encoder>
</appender>
<appender name="STDOUT" class="ch.qos.logback.core.ConsoleAppender">
<encoder>
<pattern>%date{HH:mm:ss.SSS} [%thread] %-5level %logger{36} - %msg%n</pattern>
</encoder>
</appender>
<logger name="org.springframework.jdbc.core" additivity="true" level="DEBUG">
<appender-ref ref="STDOUT"/>
</logger>
<logger name="com.demo.jdbctemplate" level="debug"/>
<logger name="org.springframework.test" level="error"/>
<logger name="org.springframework.jdbc.support" level="error"/>
<logger name="org.springframework" level="error"/>
<logger name="com.zaxxer" level="error"/>
<logger name="ch.qos.logback" level="error"/>
</configuration>
（六）新建实体类User
package com.demo.jdbctemplate.entity;
public class User {
private Integer id;
private String username;
private String password;
private String email;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", password='" + password + '\'' +
", email='" + email + '\'' +
'}';
}
}
二、增删改查
（一）新增一条记录
public Integer insertUser(User user) {
String sql = "insert into t_user(id, username, password, email) values(null, ?, ?, ?)";
return jdbcTemplate.update(sql, user.getUsername(), user.getPassword(), user.getEmail());
}
（二）删除指定记录
public Integer deleteUserById(Integer id) {
String sql = "delete from t_user where id = ?";
return jdbcTemplate.update(sql, id);
}
（三）修改指定记录
public Integer updateUser(User user) {
String sql = "update t_user set username = ?, password = ?, email = ? where id = ?";
return jdbcTemplate.update(sql, user.getUsername(), user.getPassword(), user.getEmail(), user.getId());
}
（四）查找所有记录
public List<User> findUsers() {
String sql = "select id, username, password, email from t_user";
return jdbcTemplate.query(sql, userRowMapper());
}
（五）查找一条记录
public User getUserById(Integer id) {
String sql = "select id, username, password, email from t_user where id = ?";
return jdbcTemplate.queryForObject(sql, userRowMapper(), id);
}
其中，查询记录中需要的 userRowMapper() 方法定义如下：
private RowMapper<User> userRowMapper() {
return (resultSet, i) -> {
User user = new User();
user.setId(resultSet.getInt("id"));
user.setUsername(resultSet.getString("username"));
user.setPassword(resultSet.getString("password"));
user.setEmail(resultSet.getString("email"));
return user;
};
}
（六）查找记录总数量
public Integer count() {
String sql = "select count(id) from t_user";
return jdbcTemplate.queryForObject(sql, Integer.class);
}
（七）返回新增记录的 ID
插入一条新纪录时，如果需要获得新记录的 id，可以使用下面的方式:
// 方式 1：
public Integer insertUser(User user) {
String sql = "insert into t_user(id, username, password, email) values(null, ?, ?, ?)";
KeyHolder keyHolder = new GeneratedKeyHolder();
int resRow = jdbcTemplate.update(connection -> {
PreparedStatement ps = connection.prepareStatement(sql, new String[]{"id"});
ps.setString(1, user.getUsername());
ps.setString(2, user.getPassword());
ps.setString(3, user.getEmail());
return ps;
}, keyHolder);
return Integer.parseInt(Objects.requireNonNull(keyHolder.getKey()).toString());
}
// 方式 2：
public Integer insertUser(User user) {
String sql = "insert into t_user(id, username, password, email) values(null, ?, ?, ?)";
KeyHolder keyHolder = new GeneratedKeyHolder();
int resRow = jdbcTemplate.update(connection -> {
PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
ps.setString(1, user.getUsername());
ps.setString(2, user.getPassword());
ps.setString(3, user.getEmail());
return ps;
}, keyHolder);
return Integer.parseInt(Objects.requireNonNull(keyHolder.getKey()).toString());
}
