题目:【JDBC】PreparedStatement实现批量插入数据
前言:
PreparedStatement除了解决Statement的拼串、sql注入问题之外,还可以实现以下操作
- PreparedStatement操作Blob的数据,而Statement做不到
- PreparedStatement可以实现更高效的批量操作
- update、delete本身就具有批量操作的效果(通过过滤条件)
- 此时的批量操作主要指的是批量插入
目标:
(1).使用PreparedStatement实现更高效的批量插入
迭代过程:
方式一:使用Statement
(1).数据表如下
# 向goods表中插入20000条数据
CREATE TABLE goods{
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(25)
};
(2).使用Statement
// (封装好的工具类)实现数据库连接
Connection conn = JDBCUtils.getConnection();
// 获取Statement实例
Statement st = conn.createStatement();
// 使用Statement执行SQL语句
for(int i = 1;i <= 20000;i++){
// 拼接字符串,容易SQL注入攻击
String sql = "insert into goods(name)values('name_" + i + "')";
st.execute(sql);
}
方式二:
(1).使用Statement
// 方式二:使用PreparedStatement
public class InsertTest {
@Test
// idea Ctrl + Alt + t 快捷键抛出异常
public void testInsert1() {
Connection conn = null;
PreparedStatement ps = null;
try {
// 测试用时
long start = System.currentTimeMillis();
// 连接数据库
conn = JDBCUtils.getConnection();
// 通过Connection实例获取PreparedStatement, 预编译SQL语句
String sql = "insert into goods(name)values(?)";
ps = conn.prepareStatement(sql);
// 批量插入数据
for(int i = 1; i <= 20000; i++){
ps.setObject(1, "name_" + i);
ps.execute();
}
// 测试用时
long end = System.currentTimeMillis();
// 花费事件
System.out.println("花费的时间为:" + (end - start));//20000:83065
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn, ps);
}
}
}
(2).PreparedStatement 能最大可能提高性能原因:
- DBServer会对预编译语句提供性能优化。因为预编译语句有可能被重复调用,所以语句在被DBServer的编译器编译后的执行代码被缓存下来,那么下次调用时只要是相同的预编译语句就不需要编译,只要将参数直接传入编译过的语句执行代码中就会得到执行。
- 在statement语句中,即使是相同操作但因为数据内容不一样,所以整个语句本身不能匹配,没有缓存语句的意义.事实是没有数据库会对普通语句编译后的执行代码缓存。这样每执行一次都要对传入的语句编译一次。
- (语法检查,语义检查,翻译成二进制命令,缓存)
(3).与Statement对比:
- 字符串拼接使得每次执行都要编译一次
方式三:
(1).攒够一定数量SQL,一次执行
@Test
// 批量插入方式三:攒够一定数量,一次执行 //318ms
/*
1. addBatch()、executeBatch()、clearBatch()
2. mysql服务器默认是关闭批处理的,我们需要通过一个参数,让mysql开启批处理的支持。
?rewriteBatchedStatements=true 写在配置文件的url后面
3.使用更新的mysql 驱动:mysql-connector-java-5.1.37-bin.jar
*/
public void testInsert3() {
Connection conn = null;
PreparedStatement ps = null;
try {
// 测试用时
long start = System.currentTimeMillis();
// 连接数据库
conn = JDBCUtils.getConnection();
// 通过Connection实例获取PreparedStatement, 预编译SQL语句
String sql = "insert into goods(name)values(?)";
ps = conn.prepareStatement(sql);
// 批量插入数据
for(int i = 1; i <= 20000; i++){
ps.setObject(1, "name_" + i);
// 1.攒SQL
ps.addBatch();
// 攒够500条
if(i % 500 == 0 || i == 20000){
// 2.执行batch
ps.executeBatch();
// 3.清空batch
ps.clearBatch();
}
}
// 测试用时
long end = System.currentTimeMillis();
// 花费事件
System.out.println("花费的时间为:" + (end - start));
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn, ps);
}
}
方式四:
(1)设置连接不允许自动提交数据
@Test
// 批量插入方式四:攒够一定数量,一次执行 //297ms
/*
1. 关闭自动提交数据,完成后统一提交
*/
public void testInsert4() {
Connection conn = null;
PreparedStatement ps = null;
try {
// 测试用时
long start = System.currentTimeMillis();
// 连接数据库
conn = JDBCUtils.getConnection();
// 设置不允许自动提交数据
conn.setAutoCommit(false);
// 通过Connection实例获取PreparedStatement, 预编译SQL语句
String sql = "insert into goods(name)values(?)";
ps = conn.prepareStatement(sql);
// 批量插入数据
for(int i = 1; i <= 20000; i++){
ps.setObject(1, "name_" + i);
// 1.攒SQL
ps.addBatch();
// 攒够500条
if(i % 500 == 0 || i == 20000){
// 2.执行batch
ps.executeBatch();
// 3.清空batch
ps.clearBatch();
}
}
// 同一提交数据
conn.commit();
// 测试用时
long end = System.currentTimeMillis();
// 花费事件
System.out.println("花费的时间为:" + (end - start));//1583ms
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn, ps);
}
}
使用小结:
(1).JDBC程序编写步骤
(2).【面试】PreparedStatement 和 Statement 区别
-
PreparedStatement 是Statement 的子接口
-
代码的可读性和可维护性
-
PreparedStatement 能最大可能提高性能:
- DBServer会对预编译语句提供性能优化。因为预编译语句有可能被重复调用,所以语句在被DBServer的编译器编译后的执行代码被缓存下来,那么下次调用时只要是相同的预编译语句就不需要编译,只要将参数直接传入编译过的语句执行代码中就会得到执行。
- 在statement语句中,即使是相同操作但因为数据内容不一样,所以整个语句本身不能匹配,没有缓存语句的意义.事实是没有数据库会对普通语句编译后的执行代码缓存。这样每执行一次都要对传入的语句编译一次。
- (语法检查,语义检查,翻译成二进制命令,缓存)
-
PreparedStatement 可以防止 SQL 注入