db2批处理，不用添加rewriteBatchedStatements

喜欢ヅ旅行 2022-05-16 00:40 117阅读 0赞

1、db2的批量处理 添加rewriteBatchedStatements，因添加此字段时，报

/Library/Java/JavaVirtualMachines/jdk1.8.0\_74.jdk/Contents/Home/bin/java -ea -Didea.launcher.port=7533 "-Didea.launcher.bin.path=/Applications/IntelliJ IDEA 15.app/Contents/bin" -Didea.junit.sm\_runner -Dfile.encoding=UTF-8 -classpath "/Applications/IntelliJ IDEA 15.app/Contents/lib/idea\_rt.jar:/Applications/IntelliJ IDEA 15.app/Contents/plugins/junit/lib/junit-rt.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0\_74.jdk/Contents/Home/jre/lib/charsets.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0\_74.jdk/Contents/Home/jre/lib/deploy.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0\_74.jdk/Contents/Home/jre/lib/ext/cldrdata.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0\_74.jdk/Contents/Home/jre/lib/ext/dnsns.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0\_74.jdk/Contents/Home/jre/lib/ext/jaccess.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0\_74.jdk/Contents/Home/jre/lib/ext/jfxrt.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0\_74.jdk/Contents/Home/jre/lib/ext/localedata.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0\_74.jdk/Contents/Home/jre/lib/ext/nashorn.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0\_74.jdk/Contents/Home/jre/lib/ext/sunec.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0\_74.jdk/Contents/Home/jre/lib/ext/sunjce\_provider.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0\_74.jdk/Contents/Home/jre/lib/ext/sunpkcs11.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0\_74.jdk/Contents/Home/jre/lib/ext/zipfs.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0\_74.jdk/Contents/Home/jre/lib/javaws.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0\_74.jdk/Contents/Home/jre/lib/jce.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0\_74.jdk/Contents/Home/jre/lib/jfr.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0\_74.jdk/Contents/Home/jre/lib/jfxswt.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0\_74.jdk/Contents/Home/jre/lib/jsse.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0\_74.jdk/Contents/Home/jre/lib/management-agent.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0\_74.jdk/Contents/Home/jre/lib/plugin.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0\_74.jdk/Contents/Home/jre/lib/resources.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0\_74.jdk/Contents/Home/jre/lib/rt.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0\_74.jdk/Contents/Home/lib/ant-javafx.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0\_74.jdk/Contents/Home/lib/dt.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0\_74.jdk/Contents/Home/lib/javafx-mx.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0\_74.jdk/Contents/Home/lib/jconsole.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0\_74.jdk/Contents/Home/lib/packager.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0\_74.jdk/Contents/Home/lib/sa-jdi.jar:/Library/Java/JavaVirtualMachines/jdk1.8.0\_74.jdk/Contents/Home/lib/tools.jar:/Users/yp-tc-m-2935/Documents/mavenProject/servletTest/target/classes:/Users/yp-tc-m-2935/.m2/repository/junit/junit/4.11/junit-4.11.jar:/Users/yp-tc-m-2935/.m2/repository/org/hamcrest/hamcrest-core/1.3/hamcrest-core-1.3.jar:/Users/yp-tc-m-2935/.m2/repository/commons-io/commons-io/2.5/commons-io-2.5.jar:/Users/yp-tc-m-2935/.m2/repository/apache-taglibs/jstl/1.1.2/jstl-1.1.2.jar:/Users/yp-tc-m-2935/.m2/repository/dom4j/dom4j/1.6.1/dom4j-1.6.1.jar:/Users/yp-tc-m-2935/.m2/repository/xml-apis/xml-apis/1.0.b2/xml-apis-1.0.b2.jar:/Users/yp-tc-m-2935/.m2/repository/jaxen/jaxen/1.1.1/jaxen-1.1.1.jar:/Users/yp-tc-m-2935/.m2/repository/jdom/jdom/1.0/jdom-1.0.jar:/Users/yp-tc-m-2935/.m2/repository/xerces/xercesImpl/2.6.2/xercesImpl-2.6.2.jar:/Users/yp-tc-m-2935/.m2/repository/xom/xom/1.0/xom-1.0.jar:/Users/yp-tc-m-2935/.m2/repository/xerces/xmlParserAPIs/2.6.2/xmlParserAPIs-2.6.2.jar:/Users/yp-tc-m-2935/.m2/repository/xalan/xalan/2.6.0/xalan-2.6.0.jar:/Users/yp-tc-m-2935/.m2/repository/com/ibm/icu/icu4j/2.6.1/icu4j-2.6.1.jar:/Users/yp-tc-m-2935/.m2/repository/com/ibm/db2/db2jcc4/1.6.0\_9.7/db2jcc4-1.6.0\_9.7.jar:/Users/yp-tc-m-2935/.m2/repository/com/ibm/db2/db2jcc\_license\_cu/1.4.2\_9.7/db2jcc\_license\_cu-1.4.2\_9.7.jar:/Users/yp-tc-m-2935/.m2/repository/commons-dbcp/commons-dbcp/1.4/commons-dbcp-1.4.jar:/Users/yp-tc-m-2935/.m2/repository/commons-pool/commons-pool/1.6/commons-pool-1.6.jar:/Users/yp-tc-m-2935/.m2/repository/commons-collections/commons-collections/3.2/commons-collections-3.2.jar:/Users/yp-tc-m-2935/.m2/repository/commons-logging/commons-logging/1.2/commons-logging-1.2.jar:/Users/yp-tc-m-2935/.m2/repository/commons-beanutils/commons-beanutils/1.9.2/commons-beanutils-1.9.2.jar:/Users/yp-tc-m-2935/.m2/repository/org/mybatis/mybatis/3.2.3/mybatis-3.2.3.jar:/Users/yp-tc-m-2935/.m2/repository/org/mybatis/mybatis-spring/1.3.0/mybatis-spring-1.3.0.jar:/Users/yp-tc-m-2935/.m2/repository/apache-taglibs/standard/1.1.2/standard-1.1.2.jar:/Users/yp-tc-m-2935/.m2/repository/jackson-core-asl/jackson-core-asl/1.9.4/jackson-core-asl-1.9.4.jar:/Users/yp-tc-m-2935/.m2/repository/jackson-mapper-asl/jackson-mapper-asl/1.9.4/jackson-mapper-asl-1.9.4.jar:/Users/yp-tc-m-2935/.m2/repository/javax/servlet/javax.servlet-api/3.1.0/javax.servlet-api-3.1.0.jar:/Users/yp-tc-m-2935/.m2/repository/javax/servlet/jsp/jsp-api/2.1/jsp-api-2.1.jar:/Users/yp-tc-m-2935/.m2/repository/org/springframework/spring-core/3.2.9.RELEASE/spring-core-3.2.9.RELEASE.jar:/Users/yp-tc-m-2935/.m2/repository/org/springframework/spring-beans/3.2.9.RELEASE/spring-beans-3.2.9.RELEASE.jar:/Users/yp-tc-m-2935/.m2/repository/org/springframework/spring-context/3.2.9.RELEASE/spring-context-3.2.9.RELEASE.jar:/Users/yp-tc-m-2935/.m2/repository/org/springframework/spring-aop/3.2.9.RELEASE/spring-aop-3.2.9.RELEASE.jar:/Users/yp-tc-m-2935/.m2/repository/aopalliance/aopalliance/1.0/aopalliance-1.0.jar:/Users/yp-tc-m-2935/.m2/repository/org/springframework/spring-expression/3.2.9.RELEASE/spring-expression-3.2.9.RELEASE.jar:/Users/yp-tc-m-2935/.m2/repository/org/springframework/spring-jdbc/3.2.9.RELEASE/spring-jdbc-3.2.9.RELEASE.jar:/Users/yp-tc-m-2935/.m2/repository/org/springframework/spring-tx/3.2.9.RELEASE/spring-tx-3.2.9.RELEASE.jar:/Users/yp-tc-m-2935/.m2/repository/org/springframework/spring-web/3.2.9.RELEASE/spring-web-3.2.9.RELEASE.jar:/Users/yp-tc-m-2935/.m2/repository/org/springframework/spring-webmvc/3.2.9.RELEASE/spring-webmvc-3.2.9.RELEASE.jar:/Users/yp-tc-m-2935/.m2/repository/org/springframework/spring-test/3.2.9.RELEASE/spring-test-3.2.9.RELEASE.jar:/Users/yp-tc-m-2935/.m2/repository/log4j/log4j/1.2.16/log4j-1.2.16.jar" com.intellij.rt.execution.application.AppMain com.intellij.rt.execution.junit.JUnitStarter -ideVersion5 com.jdbcTest.Batch,addBatch  
Method1: com.ibm.db2.jcc.DB2Driver  
com.ibm.db2.jcc.am.DisconnectNonTransientConnectionException: \[jcc\]\[t4\]\[2030\]\[11211\]\[4.11.77\] 在对连接的底层 socket、socket input stream 或 socket output stream 执行操作期间  
发生通信错误。错误位置：Reply.fill() - socketInputStream.read (-1)。消息：Connection reset。 ERRORCODE=-4499, SQLSTATE=08001  
    at com.ibm.db2.jcc.am.gd.a(gd.java:319)  
    at com.ibm.db2.jcc.t4.a.a(a.java:461)  
    at com.ibm.db2.jcc.t4.a.a(a.java:456)  
    at com.ibm.db2.jcc.t4.z.b(z.java:206)  
    at com.ibm.db2.jcc.t4.z.c(z.java:259)  
    at com.ibm.db2.jcc.t4.z.c(z.java:372)  
    at com.ibm.db2.jcc.t4.z.v(z.java:1147)  
    at com.ibm.db2.jcc.t4.ab.a(ab.java:45)  
    at com.ibm.db2.jcc.t4.b.m(b.java:1241)  
    at com.ibm.db2.jcc.t4.b.b(b.java:1113)  
    at com.ibm.db2.jcc.t4.b.d(b.java:696)  
    at com.ibm.db2.jcc.t4.b.c(b.java:682)  
    at com.ibm.db2.jcc.t4.b.a(b.java:367)  
    at com.ibm.db2.jcc.t4.b.<init>(b.java:307)  
    at com.ibm.db2.jcc.DB2SimpleDataSource.getConnection(DB2SimpleDataSource.java:214)  
    at com.ibm.db2.jcc.DB2Driver.connect(DB2Driver.java:460)  
    at java.sql.DriverManager.getConnection(DriverManager.java:664)  
    at java.sql.DriverManager.getConnection(DriverManager.java:247)  
    at com.jdbcTest.utils.getConnection(utils.java:48)  
    at com.jdbcTest.Batch.addBatch(Batch.java:16)  
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)  
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)  
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)  
    at java.lang.reflect.Method.invoke(Method.java:498)  
    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:47)  
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)  
    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:44)  
    at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)  
    at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:271)  
    at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:70)  
    at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:50)  
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:238)  
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:63)  
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:236)  
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:53)  
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:229)  
    at org.junit.runners.ParentRunner.run(ParentRunner.java:309)  
    at org.junit.runner.JUnitCore.run(JUnitCore.java:160)  
    at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:69)  
    at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:234)  
    at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:74)  
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)  
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)  
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)  
    at java.lang.reflect.Method.invoke(Method.java:498)  
    at com.intellij.rt.execution.application.AppMain.main(AppMain.java:144)  
Caused by: java.net.SocketException: Connection reset  
    at java.net.SocketInputStream.read(SocketInputStream.java:209)  
    at java.net.SocketInputStream.read(SocketInputStream.java:141)  
    at com.ibm.db2.jcc.t4.z.b(z.java:199)  
    ... 42 more

java.lang.NullPointerException  
    at com.jdbcTest.Batch.addBatch(Batch.java:20)  
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)  
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)  
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)  
    at java.lang.reflect.Method.invoke(Method.java:498)  
    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:47)  
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)  
    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:44)  
    at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)  
    at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:271)  
    at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:70)  
    at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:50)  
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:238)  
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:63)  
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:236)  
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:53)  
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:229)  
    at org.junit.runners.ParentRunner.run(ParentRunner.java:309)  
    at org.junit.runner.JUnitCore.run(JUnitCore.java:160)  
    at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:69)  
    at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:234)  
    at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:74)  
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)  
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)  
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)  
    at java.lang.reflect.Method.invoke(Method.java:498)  
    at com.intellij.rt.execution.application.AppMain.main(AppMain.java:144)

Process finished with exit code 255

2、不添加rewriteBatchedStatements 时，使用批处理,执行正确，且用时为402毫秒。

Connection connection = utils.getConnection();
        PreparedStatement ps = null;
        String sql = " INSERT INTO DB2INST.TBL_USER (USERNAME,PASSWORD,AGE,SEX) VALUES (?,?,?,?)";
        try {
            ps = connection.prepareStatement(sql);
            for(int i = 0;i <10000;i++){
                try {
                    ps.setString(1,"用户名"+i);
                    ps.setInt(2,i);
                    ps.setInt(3,i);
                    ps.setString(4,i%2==0?"男":"女");
                    ps.addBatch();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            long start = System.currentTimeMillis();
            ps.executeBatch();
            long end = System.currentTimeMillis();
            System.out.println("差值:"+(end-start));
        } catch (SQLException e) {
            e.printStackTrace();
        }
    
        finally {
            if(ps != null){
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(connection !=null){
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    
    }

3、不使用批处理，用时：104448 毫秒

![70][]

[70]: /images/20220516/7143ee610c2a4d2399c4dcad0e664f2a.png