5.批量插入

5.1批量插入数据1

image.png

• 预编译，逻辑缓存下来了，是需要传入占位符参数。而statement没有缓存语句，每次执行编译一次

@Test
    public void test(){
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            long start = System.currentTimeMillis();
            conn = JDBCUtils.getConnection();
            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);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, ps);
        }

• 这种方法虽然比statement好，但也不是特别快，2w条数据花费790686ms

5.2批量插入数据

• 方式3：Batch执行：1740ms

  
  
  方式3.png

Connection conn = null;
        PreparedStatement ps = null;
        try {
            long start = System.currentTimeMillis();
            conn = JDBCUtils.getConnection();
            String sql = "insert into goods(name)values(?)";
            ps = conn.prepareStatement(sql);
            for (int i = 1; i <= 2000000; i++) {
                ps.setObject(1, "name_" + i);
                ps.addBatch();
                if (i % 500 == 0) {
                    //执行和清空batch
                    ps.executeBatch();
                    ps.clearBatch();
                }

            }
            long end = System.currentTimeMillis();
            System.out.println(end - start);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, ps);
        }

• 方式4 commit统一提交

  
  
  image.png

public void test2(){
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            long start = System.currentTimeMillis();
            conn = JDBCUtils.getConnection();
            //不用序自动提交
            conn.setAutoCommit(false);

            String sql = "insert into goods(name)values(?)";
            ps = conn.prepareStatement(sql);
            for (int i = 1; i <= 1000000; i++) {
                ps.setObject(1, "name_" + i);
                ps.addBatch();
                if (i % 500 == 0) {
                    //执行和清空batch
                    ps.executeBatch();
                    ps.clearBatch();
                }
            }
            //统一提交
            conn.commit();
            long end = System.currentTimeMillis();
            System.out.println(end - start);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtils.closeResource(conn, ps);
        }
    }