使用PrepareStatement實現批量插入操作

使用PrepareStatement實現批量插入操作

方式一：

@Test
    public void testQueryManyRecords(){
        Connection connection = null;
        PreparedStatement ps = null;
        try {
            long start = System.currentTimeMillis();
            connection = JDBCUtils.getConnection();
            String sql="insert into goods(name) values(?)";
            ps = connection.prepareStatement(sql);
            for(int i=1;i<=10000;i++){
                ps.setObject(1,"admin"+i);
                ps.executeUpdate();
            }
            long end=System.currentTimeMillis();
            System.out.println("總花費的時間："+(end-start)+"毫秒");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(connection,ps);
        }
    }

執行結果：

資料庫體現：

方式二：

@Test
    public void testQueryManyRecords2(){
        Connection connection = null;
        PreparedStatement ps = null;
        try {
            long start = System.currentTimeMillis();
            connection = JDBCUtils.getConnection();
            String sql="insert into goods(name) values(?)";
            ps = connection.prepareStatement(sql);
            for(int i=1;i<=10000;i++){
                ps.setObject(1,"admin"+i);
                ps.addBatch();
                if(i%500==0){
                    ps.executeBatch();
                    ps.clearBatch();
                }
            }
            long end=System.currentTimeMillis();
            System.out.println("總花費的時間："+(end-start)+"毫秒");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(connection,ps);
        }
    }

方式三：

 public void testQueryManyRecords3(){
        Connection connection = null;
        PreparedStatement ps = null;
        try {
            long start = System.currentTimeMillis();
            connection = JDBCUtils.getConnection();
            String sql="insert into goods(name) values(?)";
            ps = connection.prepareStatement(sql);
            connection.setAutoCommit(false);
            for(int i=1;i<=1000;i++){
                ps.setObject(1,"admin"+i);
                ps.addBatch();
                if(i%500==0){
                    ps.executeBatch();
                    ps.clearBatch();
                }
            }
            connection.commit();
            long end=System.currentTimeMillis();
            System.out.println("總花費的時間："+(end-start)+"毫秒");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(connection,ps);
        }
    }