目录
JdbcTemplate 概述 与 环境准备
1、Spring 对数据库的操作在 jdbc 上面做了简单的封装(类似 DBUtils)，使用 spring 的注入功能，可以把 DataSource 注册到 JdbcTemplate 之中。
2、org.springframework.jdbc.core.JdbcTemplate 位于 spring-jdbc-xxx.RELEASE.jar 包中，同时还需要依赖事务和异常控制包 spring-tx-xxx.RELEASE.jar。官网 Using JdbcTemplate。
3、JdbcTemplate 主要提供以下几类方法：
| execute 方法 | 能执行任何 SQL 语句，一般用于执行 DDL 语句；以及 建表、删表等等 SQL. |
| update、batchUpdate 方法 | update 方法用于执行新增、修改、删除等语句；batchUpdate 方法用于执行批处理相关语句； |
| queryForObject、queryForList、query 方法 | 用于执行查询相关语句；queryForObject查询的结果只能是1条，多余或少于都会抛异常；\ queryForList 与 query 查询结果为空时，返回的 list 大小为0，不会引发空指针异常。 |
| call 方法 | 用于执行存储过程、函数相关语句。 |
4、本文是介绍 SpringBoot 使用 JdbcTemplate 操作 Mysql 数据库，学习 JdbcTemplate 的常用 API。
环境 Mysql 5.6.11 + Java JDK 1.8 + Spring Boot 2.1.5 + mysql 驱动 "mysql-connector-java-8.0.16.jar" + IDEA 2018.
5、新建一个 spring boot 的 web 应用，spring boot 只需要导入了 spring-boot-starter-jdbc 就包含了 JdbcTemplate 以及默认的 HikariDataSource 数据源，然后就可以操作数据库了，其 pom.xml 文件依赖如下：
<!--web应用，方便从浏览器发起请求进行测试-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!-- 引入Spring封装的jdbc。包含了 JdbcTemplate 以及默认的数据源 HikariDataSource -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<!-- 引入mysql数据库连接驱动-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
<version>8.0.16</version>
</dependency>
pom.xml · 汪少棠/jdbc_template_app - Gitee.com
6、配置文件配置数据源如下：
spring:
datasource:
# jdbc 连接基础配置
username: root
password: root
#使用的 mysql 版本为：Server version: 5.6.11 MySQL Community Server (GPL)
#mysql 驱动版本：mysql-connector-java-8.0.16.jar
#高版本 Mysql 驱动时，配置的 driver-class-name 不再是 com.mysql.jdbc.Driver，url 后面必须设置时区 serverTimezone
url: jdbc:mysql://127.0.0.1:3306/test?characterEncoding=UTF-8&serverTimezone=UTC
driver-class-name: com.mysql.cj.jdbc.Driver
#hikari数据源特性配置
hikari:
maximum-pool-size: 100 #最大连接数,默认值10.
minimum-idle: 20 #最小空闲连接，默认值10.
connection-timeout: 60000 #连接超时时间(毫秒),默认值30秒.
#空闲连接超时时间，默认值600000(10分钟),只有空闲连接数大于最大连接数且空闲时间超过该值，才会被释放
#如果大于等于 max-lifetime 且 max-lifetime>0,则会被重置为0.
idle-timeout: 600000
max-lifetime: 3000000 #连接最大存活时间,默认值30分钟.设置应该比mysql设置的超时时间短
connection-test-query: select 1 #连接测试查询src/main/resources/application.yml · 汪少棠/jdbc_template_app - Gitee.com
7、如上面配置文件所示使用的是 mysql 默认的 test 数据库，为了保证数据源配置正确，以及能正确获取连接，可以在测试类中先进行测试：
/**
* Spring Boot 默认已经配置好了数据源，程序员可以直接 DI 注入然后使用即可
*/
@Resource
DataSource dataSource;
@Test
public void contextLoads() throws SQLException {
Connection connection = dataSource.getConnection();
DatabaseMetaData metaData = connection.getMetaData();
//数据源>>>>>>class com.zaxxer.hikari.HikariDataSource
System.out.println("数据源>>>>>>" + dataSource.getClass());
System.out.println("连接>>>>>>>>" + connection);
System.out.println("连接地址>>>>" + connection.getMetaData().getURL());
System.out.println("驱动名称>>>>" + metaData.getDriverName());
System.out.println("驱动版本>>>>" + metaData.getDriverVersion());
System.out.println("数据库名称>>" + metaData.getDatabaseProductName());
System.out.println("数据库版本>>" + metaData.getDatabaseProductVersion());
System.out.println("连接用户名称>" + metaData.getUserName());
connection.close();
// 数据源>>>>>>class com.zaxxer.hikari.HikariDataSource
// 连接>>>>>>>>HikariProxyConnection @554510956 wrapping com.mysql.cj.jdbc.ConnectionImpl@3bec5821
// 连接地址>>>>jdbc:mysql://127.0.0.1:3306/test?characterEncoding=UTF-8&serverTimezone=UTC
// 驱动名称>>>>MySQL Connector/J
// 驱动版本>>>>mysql-connector-java-8.0.16 (Revision: 34cbc6bc61f72836e26327537a432d6db7c77de6)
// 数据库名称>>MySQL
// 数据库版本>>8.0.26
// 连接用户名称>root@localhost
}
src/test/java/com/wmx/jdbc_template_app/DataSourceTest.java · 汪少棠/jdbc_template_app - Gitee.com
8、代码中新建 Person 实体：src/main/java/com/wmx/jdbc_template_app/pojo/Person.java · 汪少棠/jdbc_template_app - Gitee.com
Sql 脚本：src/main/resources/data/person.sql · 汪少棠/jdbc_template_app - Gitee.com
JdbcTemplate 常用 CRUD
1、新建一个 PersonController 类，页面发起请求，直接在此控制层操作 mysql 数据库，然后将结果返回页面，省去业务层与 dao 层。
2、Spring Boot 默认已经配置好了 JdbcTemplate 放在了容器中，程序员只需自己注入（@Autowired、@Resource）即可使用。
3、spring boot 对 JdbcTemplate 的自动配置原理在 org.springframework.boot.autoconfigure.jdbc 包下的 org.springframework.boot.autoconfigure.jdbc.JdbcTemplateAutoConfiguration 类中。
JdbcTemplate 增删改、删表、建表
1、常用方法如下，因为查询通常是最复杂的，提供的方法也最多，所以后面再单独分一节：
- 1.1、public int update(String sql, @Nullable Object... args)：sql 是增删改语句，args 是 sql 中的占位符(?)参数值
- 1.2、public int update(final String sql) ：sql 是增删改语句
- 1.3、public int[] batchUpdate(final String... sql)：批量处理增删改 sql
- 1.4、public int[] batchUpdate(String sql, List<Object[]> batchArgs) ：批量处理增删改 sql，注意 sql 是同一条，根据 batchArgs（参数）不同来区分
- 1.5、public void execute(final String sql)：因为没有返回值，所以不适宜做查询，除此之外可用于执行任意 SQL 语句，
- 如增加、删除、修改、TRUNCATE、drop 删除表，create 建表等等。
2、控制层代码如下，测试时需要先调用 person/createTable 接口进行建表：
@RestController
public class PersonController {
Logger logger = Logger.getAnonymousLogger();
/**
* JdbcTemplate 用于简化 JDBC 操作，还能避免一些常见的错误，如忘记关闭数据库连接
* Spring Boot 默认提供了数据源与 org.springframework.jdbc.core.JdbcTemplate
* spring boot 默认对 JdbcTemplate 的配置，已经注入了数据源创建好了实例，添加到了容器中，程序员直接获取使用即可
*/
@Autowired
private JdbcTemplate jdbcTemplate;
/**
* 创建 person 表：http://localhost:8080/person/createTable
*
* @return
*/
@GetMapping("person/createTable")
public String createTable() {
String sql = "create table if not EXISTS person(" +
"pId int primary key auto_increment," +
"pName varchar(18) not null," +
"birthday date not null," +
"salary float(10,2)," +
"summary varchar(256)" +
")";
//execute 可以执行任意 sql(不宜做查询)
jdbcTemplate.execute(sql);
return sql;
}
/**
* 删除 person 表：http://localhost:8080/person/dropTable
*
* @return
*/
@GetMapping("person/dropTable")
public String dropTable() {
String sql = "DROP TABLE if EXISTS PERSON";
jdbcTemplate.execute(sql);
return sql;
}
/**
* 保存用户
* 为了方便使用的是 get 请求：
* http://localhost:8080/person/save?pName=admin&summary=重要人物&salary=9999.00
*
* @param person
* @return
*/
@GetMapping("person/save")
public String savePerson(Person person) {
String message = "保存用户：" + person;
logger.info(message);
person.setpName(person.getpName() == null ? "scott" : person.getpName());
person.setSummary(person.getSummary() == null ? "" : person.getSummary().trim());
String sql = "INSERT INTO PERSON(pName,birthday,salary,summary) VALUES (?,?,?,?)";
Object[] params = new Object[4];
params[0] = person.getpName();
params[1] = new Date();
params[2] = person.getSalary();
params[3] = person.getSummary();
//update 方法用于执行新增、修改、删除等语句
jdbcTemplate.update(sql, params);
return sql;
}
/**
* 修改用户描述
* 为了方便使用的是 get 请求：http://localhost:8080/person/update?summary=大咖&pId=1
*
* @param person
* @return
*/
@GetMapping("person/update")
public String updatePerson(Person person) {
String message = "修改用户描述：" + person;
logger.info(message);
person.setSummary(person.getSummary() == null ? "" : person.getSummary().trim());
person.setpId(person.getpId() == null ? 0 : person.getpId());
StringBuffer sqlBuff = new StringBuffer("UPDATE PERSON SET ");
//sql 中的字符串必须加单引号
sqlBuff.append(" SUMMARY='" + person.getSummary() + "' ");
sqlBuff.append(" WHERE pId=" + person.getpId());
logger.info("SQL 确认：" + sqlBuff.toString());
//update 方法用于执行新增、修改、删除等语句
jdbcTemplate.update(sqlBuff.toString());
return sqlBuff.toString();
}
/**
* 根据 id 删除一个或者多条数据，多个 id 时用 "," 隔开
* http://localhost:8080/person/delete?ids=2,3,4
*
* @param ids
* @return
*/
@GetMapping("person/delete")
public String deletePerson(String ids) {
String message = "删除用户：" + ids;
logger.info(message);
if (ids == null || "".equals(ids)) {
return message;
}
String[] id_arr = ids.split(",");
String[] sql_arr = new String[id_arr.length];
for (int i = 0; i < id_arr.length; i++) {
sql_arr[i] = "DELETE FROM PERSON WHERE pId = " + id_arr[i];
}
logger.info("SQL 确认：" + Arrays.asList(sql_arr));
//batchUpdate 方法用于执行批处理增加删除、修改等 sql
jdbcTemplate.batchUpdate(sql_arr);
return Arrays.asList(sql_arr).toString();
}
/**
* 根据单个 id 删除单条数据：http://localhost:8080/person/deleteById?pId=4
*
* @param pId
* @return
*/
@GetMapping("person/deleteById")
public String deletePersonById(Integer pId) {
String message = "根据 pId 删除：" + pId;
logger.info(message);
if (pId == null) {
return message;
}
String sql = "DELETE FROM PERSON WHERE pId = " + pId;
//execute 同样可以执行任意 DDL 语句
jdbcTemplate.execute(sql);
return sql;
}
/**
* 删除整表数据：http://localhost:8080/person/deletesAll
*
* @return
*/
@GetMapping("person/deletesAll")
public String deleteAll() {
String sql = "TRUNCATE TABLE PERSON";
jdbcTemplate.execute(sql);
return sql;
}
}
批量执行、批处理
@PostMapping("person/batchSave6")
public ResultData batchSave6(@RequestBody List<Person> persons) {
String sql = "INSERT INTO PERSON(pName,birthday,salary,summary) VALUES (?,?,?,?)";
List<Object[]> batchArgs = new ArrayList<>();
for (Person person : persons) {
List<Object> args = new ArrayList<>();
args.add(person.getpName());
args.add(person.getBirthday());
args.add(person.getSalary());
args.add(person.getSummary());
batchArgs.add(args.toArray());
}
int[] ints = jdbcTemplate.batchUpdate(sql, batchArgs);
return ResultData.success(ints);
}JdbcTemplate 查询 与 分页
1、常用的查询方法如下：
| //queryForObject 查询结果为单个对象。注意结果只能是一条/个，结果条数 ==0 或者 >1 都会抛异常，只能 ==1 条。requiredType 表示返回的结果类型 public <T> T queryForObject(String sql, Class<T> requiredType)： |
| //如下几个重载方法中的 RowMapper 是行映射接口，可以将结果直接通过反射转为 Java Bean 对象。
|
| 1. 当 queryForObject 结果为空时，抛出 EmptyResultDataAccessException 异常 2. 当 queryForObject 结果 size 大于 1 时，抛 IncorrectResultSizeDataAccessException 异常 3. Spring 这样做的目的是为了防止程序员不对空值进行判断，保证了程序的健壮性。解决办法是自己捕获异常处理，或者使用下面的 queryForList、query |
| //queryForList 查询结果为单条或者多条，有个好处是如果没有数据，则list大小为0，不会出现空指针异常 //所有 queryForList 方法中的泛型 T 都只能是基本类型，如 Integer.class,String.class等等，如果是 Map 或者 Java Bean 则报错 List<T> queryForList(String sql, Class<T> elementType) |
| //query 是查询的底层方法，上面的 queryForObject、queryForList 底层都是调用 query 方法。查询结果为 0条、1条、或者 多条 //需要 Java Bean 元素类型时，使用 {@link BeanPropertyRowMapper}， public <T> List<T> query(String sql, RowMapper<T> rowMapper) |
2、控制层代码如下：
/**
* @author wangmaoxiong
*/
@RestController
public class PersonController2 {
Logger logger = Logger.getAnonymousLogger();
/**
* JdbcTemplate 用于简化 JDBC 操作，还能避免一些常见的错误，如忘记关闭数据库连接
* Spring Boot 默认提供了数据源与 org.springframework.jdbc.core.JdbcTemplate
* spring boot 默认对 JdbcTemplate 的配置，已经注入了数据源创建好了实例，程序员直接获取使用即可
*/
@Autowired
private JdbcTemplate jdbcTemplate;
/**
* 查询表中总记录数：http://localhost:8080/person/findCount
*
* @return
*/
@GetMapping("person/findCount")
public String findCount() {
String sql = "SELECT COUNT(1) FROM PERSON";
//注意结果只能是一条/个，结果条数 ==0 或者 >1 都会抛异常，只能 ==1
Integer count = jdbcTemplate.queryForObject(sql, Integer.class);
return sql + " ===> " + count;
}
/**
* 根据 pId 查询姓名：http://localhost:8080/person/findNameById?pId=1
*
* @param pId
* @return
*/
@GetMapping("person/findNameById")
public String findNameById(Integer pId) {
pId = pId == null ? 1 : pId;
String sql = "SELECT pName FROM PERSON WHERE pId = ?";
Object[] param = new Object[]{pId};
//一定要注意 queryForObject 结果必须是 1 条，多余 1 条或者没有1条，都会报错
String name = null;
try {
name = jdbcTemplate.queryForObject(sql, param, String.class);
} catch (Exception e) {
logger.warning("查询的 pId 不存在：" + pId);
}
return sql + " ===> " + name;
}
/**
* 根据 id 查询实体对象：http://localhost:8080/person/findById?pId=1
*
* @param pId
* @return
*/
@GetMapping("person/findById")
public String findByPid(Integer pId) {
String sql = "SELECT * FROM PERSON WHERE pId = ?";
Object[] params = new Object[]{pId};
Person person = new Person();
try {
//queryForObject 结果只能是 1 条，小于或者大于1条都会报错
person = jdbcTemplate.queryForObject(sql, params, new BeanPropertyRowMapper<>(Person.class));
} catch (Exception e) {
logger.info("pId " + pId + " 不存在.");
}
JsonObject jsonObject = new JsonObject();
JsonParser jsonParser = new JsonParser();
jsonObject.addProperty("sql", sql);
jsonObject.add("person", jsonParser.parse(new Gson().toJson(person)));
return jsonObject.toString();
}
/**
* 查询所有：http://localhost:8080/person/findAll
*
* @return
*/
@GetMapping("person/findAll")
public String findAll() {
String sql = "SELECT * FROM PERSON";
//如果没有数据，则 list 大小为 0，不会为 null 出现空指针异常
List<Map<String, Object>> mapList = jdbcTemplate.queryForList(sql);
String message = new Gson().toJson(mapList);
return message;
}
/**
* 查询所有用户名：http://localhost:8080/person/findAllName/2
* queryForList 方法返回的元素类型是 Map 类型，因为它底层使用的是 {@link ColumnMapRowMapper}
*
* @param size
* @return
*/
@GetMapping("person/findAllName/{size}")
public String findAllName(@PathVariable Integer size) {
String sql = "SELECT DISTINCT t.pName FROM PERSON t limit ?";
//如果没有数据，则 list 大小为 0，不会为 null 出现空指针异常
List<String> mapList = jdbcTemplate.queryForList(sql, String.class, size);
String message = new Gson().toJson(mapList);
return message;
}
/**
* 模糊查询：http://localhost:8080/person/vagueFind?vagueValue=管理员
*
* @param vagueValue
* @return
*/
@GetMapping("person/vagueFind")
public String vagueFind(String vagueValue) {
String sql = "SELECT * FROM PERSON ";
if (vagueValue != null && !"".equals(vagueValue)) {
sql += " WHERE pName LIKE '%" + vagueValue + "%' ";
sql += " OR summary LIKE '%" + vagueValue + "%' ";
}
//BeanPropertyRowMapper 要求 sql 查询出来的列和实体属性一一对应，否则应该在 sql 语句中用 as 设置别名
List<Person> personList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Person.class));
return personList.toString();
}
/**
* 分页查询：
* http://localhost:8080/person/pagingFind
* http://localhost:8080/person/pagingFind?pageNo=1&rows=3
*
* @param pageNo :当前查询的页码，从1开始
* @param rows ：每页显示的记录条数
* @return
*/
@GetMapping("person/pagingFind")
public String pagingFind(Integer pageNo, Integer rows) {
//mysql 的 limit 分页，第一个参数为起始索引，从0开始，第二个参数为查询的条数
String sql = "SELECT * FROM PERSON LIMIT ?,?";
pageNo = pageNo == null ? 1 : pageNo;
rows = rows == null ? 2 : rows;
Integer startIndex = (pageNo - 1) * rows;
List<Person> personList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Person.class), startIndex, rows);
return new Gson().toJson(personList);
}
/**
* http://localhost:8080/person/loadAll/3
* 需要 Java Bean 元素类型时，使用 {@link BeanPropertyRowMapper}，
* 需要 Map 类型时，使用 {@link ColumnMapRowMapper}
*
* @return
*/
@GetMapping("person/loadAll/{size}")
public List<Map<String, Object>> loadAll(@PathVariable Integer size) {
String sql = "SELECT * FROM PERSON limit ?,?";
//如果没有数据，则 list 大小为 0，不会为 null 出现空指针异常
Object[] args = {0, size};
List<Map<String, Object>> mapList = jdbcTemplate.query(sql, new ColumnMapRowMapper(), args);
return mapList;
}
}
491
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
