JDBC get auto primary key, batch operation

1: Getting database auto increment primary key by jdbc
Usage scenario: after inserting a record, you need to directly use the record for other operations. For example, after inserting an order, you need to insert an order item record into the order detail table, and you need to use the primary key as the foreign key of the order item table (provided that the primary key of the order table is an auto increment type)

    //example
@Test
public void test(){
    Connection connection=null;
    PreparedStatement preparedStatement=null;
    ResultSet resultSet=null;
    String sql="insert into order(otime,total) values (?,?);";
    try {
        //Get connection
        connection=DBUtils.getConnection();
        //Get the preparedstatement that can get the auto increment primary key
        preparedStatement=connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        preparedStatement.setDate(1, new Date(new java.util.Date().getTime()));
        preparedStatement.setDouble(2, 200.5);
        preparedStatement.executeUpdate();
        //Get auto increment primary key
        resultSet=preparedStatement.getGeneratedKeys();
        if(resultSet.next()){
            System.out.println(resultSet.getObject(1));
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        DBUtils.close(connection, preparedStatement, resultSet);
    }
}

2: Batch operation
If a batch of data is operated at one time, the cost of using cycle by cycle operation is too high, and batch operation can greatly improve performance

@Test
public void test(){
    //Example: insert 100000 pieces of data
    Connection connection=null;
    PreparedStatement preparedStatement=null;
    String sql="insert into person values (?,?,?);";
    try {
        //Involving the operation of things
        connection=DBUtils.getConnection();
        DBUtils.startTransacton(connection);

        long start=System.currentTimeMillis();
        for(int i=1;i<=100000;i++){
            preparedStatement=connection.prepareStatement(sql);
            preparedStatement.setInt(1, i);
            preparedStatement.setString(2, "name"+i);
            preparedStatement.setDate(3, new Date(new java.util.Date().getTime()));
            //Batch processing
            preparedStatement.addBatch();
            //Perform batch processing every 500 data operations and empty the batch
            if((i%500==0)){
                preparedStatement.executeBatch();
                preparedStatement.clearBatch();
            }
        }
        //Process the last remaining data operations
        preparedStatement.executeBatch();
        preparedStatement.clearBatch();
        long end=System.currentTimeMillis();
        DBUtils.commit(connection);
        System.out.println("Time to add 100000 pieces of data in batch method:"+(end-start)+"millisecond");
    } catch (Exception e) {
        e.printStackTrace();
        DBUtils.rollback(connection);
    } finally {
        DBUtils.close(connection, preparedStatement, null);
    }
}

3: Getting database auto primary key in Apache dbutils

    @Test
    public void test(){
        QueryRunner qr=new QueryRunner(DBUtils.getDataSource());
        String sql="insert into department values(null,'java')";
        try {
            Long keys=qr.insert(sql, new ScalarHandler<Long>());
            System.out.println(keys);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

4: Apache dbutils bulk operations

    @Test
    public void testBatch(){
        String sql="delete from department where id=?";
        QueryRunner qr=new QueryRunner(DBUtils.getDataSource());
        int[] is={4,7,8};
        //Define parameter array
        //The number of lines indicates the number of records to be operated, and there are several lines to operate several lines of data
        Object[][] params=new Object[is.length][];
        //The data of each row is the parameter to be filled
        for (int i = 0; i < params.length; i++) {
            params[i]=new Object[]{is[i]};
        }
        try {
            //The returned result is 0 / 1 of the operation result of each set of data. The array length is the number of operation records (rows)
            int[] batch = qr.batch(sql, params);
            for (int i : batch) {
                System.out.println(i);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

Tags: SQL Java Database Apache

Posted on Thu, 04 Jun 2020 14:26:03 -0400 by starrieyed