Spring JdbcTemplate 模板剖析之常用增删改查

爱被打了一巴掌 2021-12-04 07:13 193阅读 0赞

**目录**

JdbcTemplate 概述 与 环境准备

JdbcTemplate 常用 CRUD

JdbcTemplate 增删改、删表、建表

JdbcTemplate 查询 与 分页

--------------------

# 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。][Using JdbcTemplate]

3、JdbcTemplate 主要提供以下几类方法：

<table> 
 <tbody> 
 <tr> 
 <td>execute 方法</td> 
 <td>能执行任何 SQL 语句，一般用于执行 DDL 语句；以及 建表、删表等等 SQL.</td> 
 </tr> 
 <tr> 
 <td>update、batchUpdate 方法</td> 
 <td>update 方法用于执行新增、修改、删除等语句；batchUpdate 方法用于执行批处理相关语句；</td> 
 </tr> 
 <tr> 
 <td>queryForObject、queryForList、query 方法</td> 
 <td> 用于执行查询相关语句；queryForObject查询的结果只能是1条，多余或少于都会抛异常；\ queryForList 与 query 查询结果为空时，返回的 list 大小为0，不会引发空指针异常。 </td> 
 </tr> 
 <tr> 
 <td>call 方法</td> 
 <td>用于执行存储过程、函数相关语句。</td> 
 </tr> 
 </tbody> 
</table>

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 文件依赖如下：

<parent>
 <groupId>org.springframework.boot</groupId>
 <artifactId>spring-boot-starter-parent</artifactId>
 <version>2.1.5.RELEASE</version>
 <relativePath/> 
 </parent>
 //...
 <dependencies>
 
 <dependency>
 <groupId>org.springframework.boot</groupId>
 <artifactId>spring-boot-starter-web</artifactId>
 </dependency>
 
 
 <dependency>
 <groupId>org.springframework.boot</groupId>
 <artifactId>spring-boot-starter-jdbc</artifactId>
 </dependency>
 
 
 <dependency>
 <groupId>mysql</groupId>
 <artifactId>mysql-connector-java</artifactId>
 <scope>runtime</scope>
 <version>8.0.16</version>
 </dependency>
 
 
 
 <dependency>
 <groupId>com.google.code.gson</groupId>
 <artifactId>gson</artifactId>
 <version>2.8.5</version>
 </dependency>
 </dependencies>

6、配置文件配置数据源如下：

spring:
      datasource:
        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

7、如上面配置文件所示使用的是 mysql 默认的 test 数据库，为了保证数据源配置正确，以及能正确获取连接，可以在测试类中先进行测试：

import org.junit.Test;
    import org.junit.runner.RunWith;
    import org.springframework.boot.test.context.SpringBootTest;
    import org.springframework.test.context.junit4.SpringRunner;
    import javax.annotation.Resource;
    import javax.sql.DataSource;
    import java.sql.Connection;
    import java.sql.SQLException;
    
    @RunWith(SpringRunner.class)
    @SpringBootTest
    public class JdbcTemplateAppApplicationTests {
        /**
         * Spring Boot 默认已经配置好了数据源，程序员可以直接 DI 注入然后使用即可
         */
        @Resource
        DataSource dataSource;
        @Test
        public void contextLoads() throws SQLException {
            Connection connection = dataSource.getConnection();
            System.out.println("数据源>>>>>>" + dataSource.getClass());
            System.out.println("连接>>>>>>>>>" + connection);
            System.out.println("连接地址>>>>>" + connection.getMetaData().getURL());
            connection.close();//关闭连接
        }
    }

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdteDE5OTMzMjg_size_16_color_FFFFFF_t_70][]

8、代码中新建 Person 实体如下：

import java.io.Serializable;
    import java.util.Date;
    /**
     * @author wangmaoxiong
     */
    public class Person implements Serializable {
        private Integer pId;
        private String pName;
        private Float salary;
        private Date birthday;
        private String summary;
    
       //省略了 getter、setter、toString 方法没有粘贴
    }

# 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 接口进行建表：

import com.wmx.jdbc_template_app.pojo.Person;
 import org.springframework.beans.factory.annotation.Autowired;
 import org.springframework.jdbc.core.JdbcTemplate;
 import org.springframework.web.bind.annotation.GetMapping;
 import org.springframework.web.bind.annotation.RestController;
 import java.util.Arrays;
 import java.util.Date;
 import java.util.logging.Logger;
 /**
 * @author wangmaoxoing
 */
 @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;
 }
 }

![20200327193517840.gif][]

# JdbcTemplate 查询 与 分页 #

1、常用的查询方法如下：

<table> 
 <tbody> 
 <tr> 
 <td> //queryForObject 查询结果为单个对象。注意结果只能是一条/个，结果条数 ==0 或者 &gt;1 都会抛异常，只能 ==1 条。requiredType 表示返回的结果类型 //常用于获取某个单个结果记录，如 count、avg、sum 等函数返回唯一值 &nbsp; public &lt;T&gt; T queryForObject(String sql, Class&lt;T&gt; requiredType)： public &lt;T&gt; T queryForObject(String sql, Class&lt;T&gt; requiredType, @Nullable Object... args)：在上面的基础上为 sql 加了占位符，可以通过args设置参数值 public &lt;T&gt; T queryForObject(String sql, Object[] args, Class&lt;T&gt; requiredType)：和上面一样，重载的方法而已。 </td> 
 </tr> 
 <tr> 
 <td> //如下几个重载方法中的 RowMapper 是行映射接口，可以将结果直接通过反射转为 Java Bean 对象。 //RowMapper接口常用实现类有 BeanPropertyRowMapper、ColumnMapRowMapper、SingleColumnRowMapper //BeanPropertyRowMapper 要求 sql 查询出来的列和实体属性一一对应，否则应该在 sql 语句中用 as 设置别名 public &lt;T&gt; T queryForObject(String sql, RowMapper&lt;T&gt; rowMapper) public &lt;T&gt; T queryForObject(String sql, @Nullable Object[] args, RowMapper&lt;T&gt; rowMapper) public &lt;T&gt; T queryForObject(String sql, RowMapper&lt;T&gt; rowMapper, @Nullable Object... args) </td> 
 </tr> 
 <tr> 
 <td>1.&nbsp; 当 queryForObject 结果为空时，抛出 EmptyResultDataAccessException 异常 2.&nbsp; 当 queryForObject 结果 size 大于 1 时，抛 IncorrectResultSizeDataAccessException 异常 3.&nbsp; Spring 这样做的目的是为了防止程序员不对空值进行判断，保证了程序的健壮性。解决办法是自己捕获异常处理，或者使用下面的 queryForList、query</td> 
 </tr> 
 <tr> 
 <td> //queryForList 查询结果为单条或者多条，有个好处是如果没有数据，则list大小为0，不会出现空指针异常 public List&lt;Map&lt;String, Object&gt;&gt; queryForList(String sql)//每一个 map 表示表中的一行记录,map 的key为字段名，map 的value 为字段值 public List&lt;Map&lt;String, Object&gt;&gt; queryForList(String sql, @Nullable Object... args)：可以通过 sql 占位符设置参数值 </td> 
 </tr> 
 <tr> 
 <td> //query 是查询的底层方法，上面的 queryForObject、queryForList 底层都是调用 query 方法。查询结果为 0条、1条、或者 多条 //没有数据时，list 大小为 0，不会出现控制层异常，rowMapper 行映射，直接将结果反射为 Java Bean 对象。 &nbsp; public &lt;T&gt; List&lt;T&gt; query(String sql, RowMapper&lt;T&gt; rowMapper) public &lt;T&gt; List&lt;T&gt; query(String sql, RowMapper&lt;T&gt; rowMapper, @Nullable Object... args) public &lt;T&gt; List&lt;T&gt; query(String sql, @Nullable Object[] args, RowMapper&lt;T&gt; rowMapper) </td> 
 </tr> 
 </tbody> 
</table>

2、控制层代码如下：

import com.google.gson.Gson;
 import com.google.gson.JsonObject;
 import com.google.gson.JsonParser;
 import com.wmx.jdbc_template_app.pojo.Person;
 import org.springframework.beans.factory.annotation.Autowired;
 import org.springframework.jdbc.core.BeanPropertyRowMapper;
 import org.springframework.jdbc.core.JdbcTemplate;
 import org.springframework.web.bind.annotation.GetMapping;
 import org.springframework.web.bind.annotation.RestController;
 import java.util.List;
 import java.util.Map;
 import java.util.logging.Logger;
 /**
 * @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/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);
 }
 }

项目源码：[https://github.com/wangmaoxiong/jdbc\_template\_app][https_github.com_wangmaoxiong_jdbc_template_app]

[Using JdbcTemplate]: https://docs.spring.io/spring-boot/docs/2.1.6.RELEASE/reference/htmlsingle/#boot-features-using-jdbc-template
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dhbmdteDE5OTMzMjg_size_16_color_FFFFFF_t_70]: /images/20211204/215f9dd1e3284968b6c7fedb52a88520.png
[20200327193517840.gif]: /images/20211204/62312d1a1eaa45b2acf1b738cfdfef93.png
[https_github.com_wangmaoxiong_jdbc_template_app]: https://github.com/wangmaoxiong/jdbc_template_app