能不能在FOR循环中执行SQL？
JDBC最基础的For循环处理SQL的方式 以及执行时间
package javaee.net.cn.jdbc; import java.sql.*; public class TestTransaction { public static void main(String[] args) { Long startTime = System.currentTimeMillis(); Connection conn = null; PreparedStatement stmt = null; try { Class.forName("com.mysql.jdbc.Driver"); conn = DriverManager .getConnection("jdbc:mysql://192.168.1.105:3306/sunkun?user=root&password=********"); String sql = "insert into user (name) values (?)"; stmt = conn.prepareStatement(sql); for(int i=0;i<1000;i++){ stmt.setString(1,"test"+i); stmt.execute(); } stmt.executeBatch(); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch(SQLException e) { e.printStackTrace(); try { if(conn != null) { conn.rollback(); conn.setAutoCommit(true); } } catch (SQLException e1) { e1.printStackTrace(); } }finally { try { if(stmt != null) stmt.close(); if(conn != null) conn.close(); } catch (SQLException e) { e.printStackTrace(); } } Long endTime = System.currentTimeMillis(); Long time = endTime-startTime; System.err.println("一千条数据所需要的时间"+time);//一千条数据所需要的时间48262 } }
for循环一千条SQL时间是48262ms
设置setAutoCommit(false) FOR循环执行
package javaee.net.cn.jdbc; import java.sql.*; public class TestTransaction { public static void main(String[] args) { Long startTime = System.currentTimeMillis(); Connection conn = null; PreparedStatement stmt = null; try { Class.forName("com.mysql.jdbc.Driver"); conn = DriverManager .getConnection("jdbc:mysql://192.168.1.105:3306/sunkun?user=root&password=*******"); String sql = "insert into user (name) values (?)"; stmt = conn.prepareStatement(sql); conn.setAutoCommit(false); for(int i=0;i<1000;i++){ stmt.setString(1,"test"+i); stmt.execute(); } conn.commit(); conn.setAutoCommit(true); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch(SQLException e) { e.printStackTrace(); try { if(conn != null) { conn.rollback(); conn.setAutoCommit(true); } } catch (SQLException e1) { e1.printStackTrace(); } }finally { try { if(stmt != null) stmt.close(); if(conn != null) conn.close(); } catch (SQLException e) { e.printStackTrace(); } } Long endTime = System.currentTimeMillis(); Long time = endTime-startTime; System.err.println("一千条数据所需要的时间"+time);//一千条数据所需要的时间578 } }
578ms 厉害吧 同样是for循环执行SQL 效率提高了百倍
setAutoCommit介绍
void setAutoCommit(boolean autoCommit)
throws SQLException
- 将此连接的自动提交模式设置为给定状态。如果连接处于自动提交模式下，则它的所有 SQL 语句将被执行并作为单个事务提交。否则，它的 SQL 语句将聚集到事务中，直到调用
commit方法或rollback方法为止。默认情况下，新连接处于自动提交模式。提交发生在语句完成时。语句完成的时间取决于 SQL 语句的类型：
- 对于 DML 语句（比如 Insert、Update 或 Delete）和 DDL 语句，语句在执行完毕时完成。
- 对于 Select 语句，语句在关联结果集关闭时完成。
- 对于
CallableStatement对象或者返回多个结果的语句，语句在所有关联结果集关闭并且已获得所有更新计数和输出参数时完成。
注：如果在事务和自动提交模式更改期间调用此方法，则提交该事务。如果调用
setAutoCommit而自动提交模式未更改，则该调用无操作（no-op）。 - 参数：
autoCommit- 为true表示启用自动提交模式；为false表示禁用自动提交模式- 抛出：
SQLException- 如果发生数据库访问错误，在参与分布式事务的同时调用 setAutoCommit(true)，或者在关闭的连接上调用此方法- 另请参见：
getAutoCommit()
测试批处理一千条数据的时间
package javaee.net.cn.jdbc; import java.sql.*; public class TestTransaction { public static void main(String[] args) { Long startTime = System.currentTimeMillis(); Connection conn = null; PreparedStatement stmt = null; try { Class.forName("com.mysql.jdbc.Driver"); conn = DriverManager .getConnection("jdbc:mysql://192.168.1.105:3306/sunkun?user=root&password=******"); String sql = "insert into user (name) values (?)"; stmt = conn.prepareStatement(sql); conn.setAutoCommit(false); for(int i=0;i<1000;i++){ stmt.setString(1,"test"+i); stmt.addBatch(); } stmt.executeBatch(); conn.commit(); conn.setAutoCommit(true); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch(SQLException e) { e.printStackTrace(); try { if(conn != null) { conn.rollback(); conn.setAutoCommit(true); } } catch (SQLException e1) { e1.printStackTrace(); } }finally { try { if(stmt != null) stmt.close(); if(conn != null) conn.close(); } catch (SQLException e) { e.printStackTrace(); } } Long endTime = System.currentTimeMillis(); Long time = endTime-startTime; System.err.println("一千条数据所需要的时间"+time);//一千条数据所需要的时间502 } }
看到如果设置了批处理502 比for循环执行一千条SQL578快了一点(和网络延迟 GC有关 其实只要不让事物自动提交 for循环处理SQL和批处理的效率差不多)
实验Mybatis
不管什么dao框架 底层都是jdbc
工作中用的最多的是Mybatis
下面对Mybatis做一下For循环处理SQL的实验
@Test public void testForDao(){ //获取配置在Spring中的SqlSessionFactory实例 SqlSessionFactory sqlSessionFactory =SpringContextHolder.getBean(SqlSessionFactory.class); // SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH,false); Long startTime = System.currentTimeMillis(); for(int i=0;i<1000;i++){ ClientClassify classify = new ClientClassify(); String name = "testFor_"+i; classify.setClientClassify(name); classify.setOwnerId(2154L); classify.setCreateBy("kk6"); classify.setLastUpdateBy("kk6"); classify.setCreateDate(new Date()); classify.setLastUpdateDate(new Date()); classify.setClientType(ClientType.customer); classify.setIsDel(false); classify.setVersion(1); classify.setDelDate(new Date()); sqlSession.insert("com.ydcfo.common.model.crm.ClientClassifyMapper.insert",classify); } sqlSession.flushStatements(); sqlSession.commit(); sqlSessionFactory.openSession(true); Long endTime = System.currentTimeMillis(); System.err.println("1000条数据时间"+(endTime-startTime)); }
下面直接说结论
sqlSessionFactory.openSession(ExecutorType.BATCH,false); 时间：47010
sqlSessionFactory.openSession(ExecutorType.SIMPLE,false); 时间：50357
sqlSessionFactory.openSession(ExecutorType.REUSE,false); 时间：50398
sqlSessionFactory.openSession(ExecutorType.BATCH,true); 时间：49178
在Mybatis中，我试图和JDBC一样 通过设置 不让事物自动提交，发现效率并没有提高
/**
* @author Clinton Begin
*/
public enum ExecutorType {
SIMPLE, REUSE, BATCH
}用 Mybatis BATCH的ExecutorType执行器，FOR循环执行SQL的效率一样很低
总结，在原始的JDBC中，我们可以通过设置AutoCommit来提高FOR循环中执行SQL的效率
但是在Mybatis中 这样行不通，至于原因，还未知(我估计是Mybatis内部优化不好)。
所以在工作中 Mybatis不能FOR循环执行SQL 一定要拼装成一个SQL(通过字符串拼接SQL)然后在执行(也就是Mybatis的批处理)
这是一篇介绍事物的文章 我觉得核心的一句话是 where条件中用到的num 会收到其他未commit事物的影响 这也是乐观锁为什么可以防止并发的原因
补充:
经过测试发现， SqlSession openSession(ExecutorType execType, boolean autoCommit);
当第一个参数是ExecutorType.BATCH的时候 第二个参数是true还是false 每循环一次都不会落地到数据库
当第一个参数不是ExecutorType.BATCH的时候 第二个参数是true还是false 每循环一次都会落地到数据库 即使 还没有 sqlSession.commit() 我个人认为这是Mybatis的bug
不过这个细节不会影响我们日常的开发，因为事物都交给了Spring去处理。
