JAVA Oracle插入大量数据优化

手动提交方式

   public static void  batchInsert() throws SQLException {
        Connection conn = null;
        PreparedStatement  stmt = null;

        try {
            long startTime = System.currentTimeMillis();
            Class.forName(DRIVER).newInstance();
            conn = DriverManager.getConnection(DBURL, USER, PSWD);
            conn.setAutoCommit(false);
            stmt= conn.prepareStatement("INSERT INTO TEST(ID,USERNAME,AGE) VALUES (?,?,?)");
            int num = 0;
            for (int i = 0; i < 1000000; i++) {
                num++;
                stmt.setString(1, String.valueOf(i));
                stmt.setString(2, "第"+i+"个");
                stmt.setString(3, String.valueOf(i));
                stmt.addBatch();
                if(num > 50000){
                    stmt.executeBatch();
                    conn.commit();
                    num = 0;
                }
            }
            stmt.executeBatch();
            conn.commit();
            long endTime = System.currentTimeMillis();
            System.out.println("============"+ (endTime - startTime) / 1000);
            String sqls = "select count(*) as cnt from test";
            ResultSet rs = stmt.executeQuery(sqls);
            while (rs.next()) {
                String cnt = rs.getString("cnt");
                System.out.println("当前记录数：" + cnt);
            }

        } catch (Exception e) {
            conn.rollback();
            e.printStackTrace();
            System.out.print(e.getMessage());
        } finally {
            try {
                stmt.close();
                conn.close();
            } catch (SQLException e) {
                System.out.print("Can't close stmt/conn because of " + e.getMessage());
            }
        }
    }

关键代码在于

 conn.setAutoCommit(false);
 conn.commit();

在插入数据之前，先把自动提交关闭，之后把数据全放进缓存之后，手动一次提交。
在数据字段少的情况下，插入速度很快
在对于字段多达两百个的就会越来越慢，看下面的代码

OraclePreparedStatement 批量提交

查看源代码之后，看到，在使用OraclePreparedStatement 之前，需要先代理出oracle这个类。之后会设置数组长度，本质上意思是，在使用这个方式时，需要初始化数组长度。在对于很多字段方面，效率也有明显的提升

  public static void  batchOraclePreparedStatement() throws SQLException {
        Connection conn = null;
        PreparedStatement  stmt = null;
        OraclePreparedStatement oraclePreparedStatement = null;
        try {
            long startTime = System.currentTimeMillis();
            Class.forName(DRIVER).newInstance();
            conn = DriverManager.getConnection(DBURL, USER, PSWD);
            stmt= conn.prepareStatement("INSERT INTO TEST(ID,USERNAME,AGE) VALUES (?,?,?)");
            oraclePreparedStatement = stmt.unwrap(OraclePreparedStatement.class);
            oraclePreparedStatement.setExecuteBatch(100);
            int num = 0;
            for (int i = 0; i < 100; i++) {
                num =  num++;
                stmt.setString(1, String.valueOf(i));
                stmt.setString(2, "第"+i+"个");
                stmt.setString(3, String.valueOf(i));
                int i1 = oraclePreparedStatement.executeUpdate();
                System.out.println("==============="+i1);
                System.out.println("成功添加"+i);
            }
            long endTime = System.currentTimeMillis();
            System.out.println("============"+ (endTime - startTime) / 1000);

        } catch (Exception e) {
            e.printStackTrace();
            System.out.print(e.getMessage());
        } finally {
            try {
                stmt.close();
                conn.close();
            } catch (SQLException e) {
                System.out.print("Can't close stmt/conn because of " + e.getMessage());
            }
        }
    }