JdbcTemplate的用法
Spring Boot JdbcTemplate 入门
在实际项目中，在对数据库访问层对数据库进行操作时，大部分时候我们都用的mybatis，但是偶尔会有用到使用jdbc的时候，一般使用jdbc的话要么就自己封装一个jdbc连接池进行使用，要么就是使用Apache Common 提供的 DbUtils 工具类，还有个就是Spring JDBC ，提供的JdbcTemplate 工具类
因为我的工作中基本上都是使用的spring boot，所以就学习了一下JdbcTemplate 工具类的使用
JdbcTemplate是Spring对JDBC的封装，目的是使JDBC更加易于使用。JdbcTemplate是Spring的一部分。JdbcTemplate处理了资源的建立和释放。他帮助我们避免一些常见的错误，比如忘了总要关闭连接。他运行核心的JDBC工作流，如Statement的建立和执行，而我们只需要提供SQL语句和提取结果。
在JdbcTemplate中执行SQL语句的方法大致分为3类：
execute：可以执行所有SQL语句，一般用于执行DDL语句。update：用于执行INSERT、UPDATE、DELETE等DML语句。queryXxx：用于DQL数据查询语句。call：用于执行存储过程、函数相关语句。
应用场景以及技术选型
首先先说一下JdbcTemplate 的应用场景，其他的应用场景我说不准，现在java项目中大多数的数据访问层用的都是mybatis，但是在项目中总会遇到那种数据的批量插入，或者批量删除等操作，在这样的场景下，总要考虑到一个性能的问题，所以在做之前总要先行做好技术选型，所以这里就拿Mybatis和传统的Jdbc做了一下比较。
这里我先用JdbcTemplate 和mybatis分别做一下批量数据插入的测试：
首先环境准备，测试是在相同变量的情况下进行的，所以我就使用同一个mysql5.7的数据库进行测试。
先建表，建表可看DDL操作那里,这里先根据表结构，创建一个实体类：
TestRecord.java
public class TestRecord {
private String aa;
private String bb;
private String cc;
private String dd;
private String ee;
private String ff;
private String gg;
private String hh;
private Integer ii;
private Integer jj;
//getter and setter
}
JdbcTemplate
代码写法后面讲解，
案例代码：
@RunWith(SpringRunner.class)
@SpringBootTest(classes = TestApplication.class)
public class JdbcDMLTest {
@Autowired
private JdbcTemplate jdbcTemplate;
String sql="insert into test_record(a,b, c, d, e, f, g, h, i,j) " +
" values(?,?,?,?,?,?,?,?,?,?) ";
//预编译的批量插入
@Test
public void batchUpdate(){
//获取参数
List<TestRecord> recordList=new ArrayList<>();
for(int i=0;i<1000;i++){
TestRecord testRecord=new TestRecord();
testRecord.setAa("aa"+i);
testRecord.setBb("bb"+i);
testRecord.setCc("cc"+i);
testRecord.setDd("dd"+i);
testRecord.setEe("ee"+i);
testRecord.setFf("ff"+i);
testRecord.setGg("gg"+i);
testRecord.setHh("hh"+i);
testRecord.setIi(i);
testRecord.setJj(i);
recordList.add(testRecord);
}
Long start=System.currentTimeMillis();
int[] count = jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps, int i)
throws SQLException {
//注入参数值
ps.setString(1, recordList.get(i).getAa());
ps.setString(2 ,recordList.get(i).getBb());
ps.setString(3, recordList.get(i).getCc());
ps.setString(4, recordList.get(i).getDd());
ps.setString(5, recordList.get(i).getEe());
ps.setString(6, recordList.get(i).getFf());
ps.setString(7, recordList.get(i).getGg());
ps.setString(8, recordList.get(i).getHh());
ps.setInt(9,recordList.get(i).getIi() );
ps.setInt(10, recordList.get(i).getJj());
}
@Override
public int getBatchSize() {
//批量执行的数量
return recordList.size();
}
});
System.out.println("插入数量："+count.length+"，用时："+(System.currentTimeMillis()-start));
}
}
在执行代码之前需要注意一个点，JDBC做批量插入的时候需要在mysql的驱动配置信息里加一个属性配置，这个是开启批量执行的关键
rewriteBatchedStatements=true //加在配置文件数据库url参数中
执行程序：
可以看到1000条数据是549毫秒，也就是0.5秒完成插入。下来看一下mybatis的处理效率
Mybatis
案例代码：
MybatisBatchDML.java
package com.zkk.test.javaTest;
import com.zkk.test.TestApplication;
import com.zkk.test.domain.TestRecord;
import com.zkk.test.mapper.BatchInertTestMapper;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* description
*
* @author Zaker 2020/06/04 17:21
*/
@RunWith(SpringRunner.class)
@SpringBootTest(classes = TestApplication.class)
public class MybatisBatchDML {
@Autowired
private BatchInertTestMapper batchInertTestMapper;
@Test
public void mybatisBatchInsert(){
List<TestRecord> recordList=new ArrayList<>();
for(int i=0;i<1000;i++){
TestRecord testRecord=new TestRecord();
testRecord.setAa("aa"+i);
testRecord.setBb("bb"+i);
testRecord.setCc("cc"+i);
testRecord.setDd("dd"+i);
testRecord.setEe("ee"+i);
testRecord.setFf("ff"+i);
testRecord.setGg("gg"+i);
testRecord.setHh("hh"+i);
testRecord.setIi(i);
testRecord.setJj(i);
recordList.add(testRecord);
}
Map<String,Object> param =new HashMap();
param.put("recordList", recordList);
Long start=System.currentTimeMillis();
int count=batchInertTestMapper.batchInsert(param);
System.out.println("插入数量："+count+"，用时："+(System.currentTimeMillis()-start));
}
}
BatchInertTestMapper.java
package com.zkk.test.mapper;
import com.zkk.test.domain.TestRecord;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Repository;
import java.util.List;
import java.util.Map;
/**
* description
*
* @author Zaker 2020/06/04 17:32
*/
@Repository
@Mapper
public interface BatchInertTestMapper {
Integer batchInsert(Map<String,Object> param);
}
BatchInertTestMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.zkk.test.mapper.BatchInertTestMapper">
<insert id="batchInsert" parameterType="java.util.Map">
insert into test_record
(a,b,c,d,e,f,g,h,i,j)
values
<foreach collection="recordList" item="record" open="" close=""
separator=",">
(
#{record.aa},
#{record.bb},
#{record.cc},
#{record.dd},
#{record.ee},
#{record.ff},
#{record.gg},
#{record.hh},
#{record.ii},
#{record.jj}
)
</foreach>
;
</insert>
</mapper>
以上就是一套很常见的mybatis使用foreach处理的批量数据插入的写法
注：这里面mybatis使用的都是拼接字符串的形式然后再执行，所必须注意mybatis中拼接的字符串的长度的限制，如果数据量过大，需要分批进行执行。
执行结果：
可以看到使用mybatis插入1000条数据使用了1764毫秒，就是1.7秒，和JDBC的差距已经展现出来了。
mybatis也使用的批量执行，那如果就单纯的for循环逐条执行插入呢？这里我就不尝试了，随便想想就能想到只会更慢，更别说更大批量的数据了。
对比结果
下来两种方式我做了一个对比测试，这种测试不一定每次结果都是一样的，所以就做5次测试，选择中间的一个值做对比（懒得算平均值）
| 记录数 | JDBC耗时/ms | Mybatis耗时/ms |
|---|---|---|
| 100 | 432 | 727 |
| 1000 | 538 | 1340 |
| 10000 | 1036 | 3816 |
| 100000 | 5368 | 14937 |
可以看到这里的执行效果差，明显JDBC在处理这种大批量的数据的时候表现是更加优秀的，所以在有这种处理大批量数据的场景的时候，可以考虑选择JDBC来进行实现。
详细介绍
JdbcTemplate类对可变部分采用回调接口方式实现，如ConnectionCallback通过回调接口返回给用户一个连接，从而可以使用该连接做任何事情、StatementCallback通过回调接口返回给用户一个Statement，从而可以使用该Statement做任何事情等等，还有其他一些回调接口
DDL操作
主要使用execute：用于执行DDL语句，这里尝试一下使用JdbcTemplate 做DDL操作，新建一张表
案例代码：
package com.zkk.test.javaTest;
import com.zkk.test.TestApplication;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.test.context.junit4.SpringRunner;
/**
* description
*
* @author Zaker 2020/06/04 14:26
*/
@RunWith(SpringRunner.class)
@SpringBootTest(classes = TestApplication.class)
public class JdbcDDL {
@Autowired
private JdbcTemplate jdbcTemplate;
@Test
public void caret() {
// 创建表的SQL语句
String sql = "CREATE TABLE test_record("
+ "a VARCHAR(20),"
+ "b VARCHAR(20),"
+ "c VARCHAR(20),"
+ "d VARCHAR(20),"
+ "e VARCHAR(20),"
+ "f VARCHAR(20),"
+ "g VARCHAR(20),"
+ "h VARCHAR(20),"
+ "i int,"
+ "j int"
+ ");";
jdbcTemplate.execute(sql);
}
}
执行测试用例，执行完成没有报错，查看数据库可以看到创建成功
这样一个简单的建表操作就完成了。
具体的其他的一些删除以及更改等操作就不做练习了，大概会用就行。
DML操作
单条数据操作
一般都是使用update方法，主要看一下update有哪些方法
-
int update(String sql)
用来执行固定的sql，没有参数注入，直接执行，因为没有预编译，所以应该是效率会慢。
-
int update(String sql, Object... args)
执行参数注入的sql，Object... args可变参数组里面放入的是要注入的参数值
-
int update(PreparedStatementCreator psc)
PreparedStatementCreator ，通过回调获取JdbcTemplate提供的Connection，由用户使用该Conncetion创建相关的preparedStatement预编译对象，使用原始jdbc方式给预编译sql注入参数
-
int update(String sql, PreparedStatementSetter pss)
可以进行参数注入，PreparedStatementSetter 方法就是用来获取预编译语句对象，并设置相应参数值。
-
int update(String sql, Object... args,int[] argTypes)
执行参数注入的sql，Object... args可变参数组里面放入的是要注入的参数值，argTypes里面是需要注入的sql参数的JDBC类型（java.sql.Types中来获取类型的常量）
-
int update(PreparedStatementCreator psc,KeyHolder generatedKeyHolder)
如果需要返回插入的主键，只能用此方法，增加KeyHolder参数
案例1：int update(String sql)
@Test
public void insertOne1(){
String sql="insert into test_record(a,b, c, d, e, f, g, h, i,j) " +
"values(a,b,c,d,e,f,g,h,1,2)";
//返回的是更新的行数
int count = jdbcTemplate.update(sql);
System.out.println("影响的行数："+count);
}
案例2：int update(String sql, Object... args)
@Test
public void insertOne3(){
String sql="insert into test_record(a,b, c, d, e, f, g, h, i,j) " +
" values(?,?,?,?,?,?,?,?,?,?) ";
//返回的是更新的行数
int count = jdbcTemplate.update(sql,new Object[]{"a","b","c","d","e","f","g","h",1,1});
System.out.println("影响的行数："+count);
}
案例3
增加、更新、删除（一条sql语句）（sql需要参数注入）
int update(String sql, PreparedStatementSetter pss)
String sql="insert into test_record(a,b, c, d, e, f, g, h, i,j) " +
" values(?,?,?,?,?,?,?,?,?,?) ";
@Test
public void insertOne(){
//返回的是更新的行数
int count = jdbcTemplate.update(sql, new PreparedStatementSetter(){
@Override
public void setValues(PreparedStatement pstmt)
throws SQLException {
pstmt.setObject(1, "aa");
pstmt.setObject(2 ,"bb");
pstmt.setObject(3, "cc");
pstmt.setObject(4, "dd");
pstmt.setObject(5, "ee");
pstmt.setObject(6, "ff");
pstmt.setObject(7, "gg");
pstmt.setObject(8, "hh");
pstmt.setObject(9, 1);
pstmt.setObject(10, 1);
}
});
System.out.println("影响的行数："+count);
}
执行结果：成功
查看数据库：成功
批量数据操作
案例1：批量插入（需要预编译的）
批量操作的话使用的是batchUpdate这个方法，这个方法有很多不同的实现，可以看一下
第一个方法int[] batchUpdate(String sql, BatchPreparedStatementSetter pss)就是前面应用场景案例那里的方法，可以看一下大概的用法。
值得注意的是BatchPreparedStatementSetter 这个方法类似于PreparedStatementSetter，但用于批处理，需要指定批处理大小。
下面再练习一下
案例2: 批量插入（需要预编译的）
int[] batchUpdate(String sql,List<Object[]> batchArgs)
//预编译的批量插入2
@Test
public void batchUpdate2(){
List<Object[]> batchArgs=new ArrayList<Object[]>();
//获取参数
List<TestRecord> recordList=new ArrayList<>();
for(int i=0;i<10000;i++){
TestRecord testRecord=new TestRecord();
testRecord.setAa("aa"+i);
testRecord.setBb("bb"+i);
testRecord.setCc("cc"+i);
testRecord.setDd("dd"+i);
testRecord.setEe("ee"+i);
testRecord.setFf("ff"+i);
testRecord.setGg("gg"+i);
testRecord.setHh("hh"+i);
testRecord.setIi(i);
testRecord.setJj(i);
recordList.add(testRecord);
}
for(TestRecord record:recordList){
batchArgs.add(new Object[]{record.getAa(),
record.getBb(),
record.getCc(),
record.getDd(),
record.getEe(),
record.getFf(),
record.getGg(),
record.getHh(),
record.getIi(),
record.getJj()});
}
Long start=System.currentTimeMillis();
int[] count = jdbcTemplate.batchUpdate(sql,batchArgs);
System.out.println("插入数量："+count.length+"，用时："+(System.currentTimeMillis()-start));
}
这里就是把预编译用的参数以数组的形式放入，比上一种方式操作起来更好理解。
查看执行结果：
一万条据也只用了1429毫秒，也就是1.4秒。
这里只做两个常用方法的练习。
DQL操作
查询语句的话可以做一个了解，就不做demo练习了
public int queryForInt(String sql) 执行查询语句，返回一个int类型的值。
public long queryForLong(String sql) 执行查询语句，返回一个Long类型的数据。
public
public Map<String, Object> queryForMap(String sql) 执行查询语句，将一条记录放到一个Map中。
public List<Map<String, Object>> queryForList(String sql) 执行查询语句，返回一个List集合，List中存放的是Map类型的数据。
public
public
