JDBC批量处理

JDBC批量处理

批量处理JDBC语句提高处理速度

~addBatch(String):添加需要批量处理的SQL语句或是参数；

~executeBatch():执行批量处理语句；

    /**
     * 向Oracle的customers数据表中插入10万条记录
     * 测试如何插入，用时最短
     * 1.使用Statement
     * 2.使用PreparedStatement
     * 3.使用Batch
     */
    @Test
    public void testBatchWithStatement(){
        Connection conn = null;
        Statement statement = null;
        String sql = null;

        try {
            conn = Methods.getConnection();
            Methods.beginTx(conn);
            statement = conn.createStatement();

            long begin = System.currentTimeMillis();

            for (int i = 0; i <10000 ; i++) {
                sql="insert into account values("+(i+1) + ",name_"+i+")";
                statement.executeUpdate(sql);
            }
            long end = System.currentTimeMillis();

            System.out.println(end-begin);//39567
            Methods.commit(conn);
        } catch (Exception e) {
            e.printStackTrace();
            Methods.rollback(conn);
        }finally {
            Methods.release(statement,conn,null);
        }
    }

    @Test
    public void testBatchWithPreparedStatement(){
        Connection conn = null;
        PreparedStatement preparedStatement= null;
        String sql = null;

        try {
            conn = Methods.getConnection();
            Methods.beginTx(conn);
            sql="insert into account(name,password) values(?,?)";
            preparedStatement = conn.prepareStatement(sql);

            long begin = System.currentTimeMillis();

            for (int i = 0; i <10000 ; i++) {
                preparedStatement.setString(1,"name_"+i);
                preparedStatement.setString(2,"i");
                preparedStatement.executeUpdate();
            }

            long end = System.currentTimeMillis();


            System.out.println(end-begin);//9819
            Methods.commit(conn);
        } catch (Exception e) {
            e.printStackTrace();
            Methods.rollback(conn);
        }finally {
            Methods.release(preparedStatement,conn,null);
        }
    }

    @Test
    public void testBatch(){
        Connection conn = null;
        PreparedStatement preparedStatement= null;
        String sql = null;

        try {
            conn = Methods.getConnection();
            Methods.beginTx(conn);
            sql="insert into account(name,password) values(?,?)";
            preparedStatement = conn.prepareStatement(sql);

            long begin = System.currentTimeMillis();

            for (int i = 0; i <10000 ; i++) {
                preparedStatement.setString(1,"name_"+i);
                preparedStatement.setString(2,"i");

                //“积攒”SQL
                preparedStatement.addBatch();

                //当积攒到一定程度，就统一的执行一次，并且清空先前"积攒"的SQL
                if((i+1)%300==0){
                    preparedStatement.executeBatch();
                    preparedStatement.clearBatch();
                }
            }

            //若总条数不是批量数值的整数倍，则还需要再额外的执行一次
            if(100000%300!=0){
                preparedStatement.executeBatch();
                preparedStatement.clearBatch();
            }

            long end = System.currentTimeMillis();


            System.out.println(end-begin);//569
            Methods.commit(conn);
        } catch (Exception e) {
            e.printStackTrace();
            Methods.rollback(conn);
        }finally {
            Methods.release(preparedStatement,conn,null);
        }
    }

　　

从util.Date转为sql.Date

Date date = new Date(new java.util.Date().getTime());