Jdbc Series VIII: batch processing

  when records need to be inserted or updated in batches. The batch update mechanism of Java can be used, which allows multiple statements to be submitted to the database for batch processing at one time. In general, it is more efficient than submission alone.
The batch processing statements of JDBC include the following two methods:
          add batch (string): add SQL statements or parameters that need to be processed in batch;
   executeBatch(): execute batch processing statement;
Generally, we will encounter two situations of executing SQL statements in batches:
Batch processing of multiple SQL statements;

Batch transfer parameters of an SQL statement;


Test:
  insert 100000 records into Oracle's customers data table. Test how to insert in the shortest time.
1. Statement, 39567ms

@Test
public void testBatchWithStatement(){
    Connection connection = null;
    Statement statement = null;
    String sql = null;

    try {
        connection = JDBCTools.getConnection();
        JDBCTools.beginTx(connection);

        statement = connection.createStatement();

        long begin = System.currentTimeMillis();
        for(int i = 0; i < 100000; i++){
            sql = "INSERT INTO customers VALUES(" + (i + 1)
                    + ", 'name_" + i + "', '29-6 month -13')";
            statement.addBatch(sql);
        }
        long end = System.currentTimeMillis();

        System.out.println("Time: " + (end - begin)); //39567

        JDBCTools.commit(connection);
    } catch (Exception e) {
        e.printStackTrace();
        JDBCTools.rollback(connection);
    } finally{
        JDBCTools.releaseDB(null, statement, connection);
    }
}

2. Use PreparedStatement. Time 9819ms

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

    try {
        connection = JDBCTools.getConnection();
        JDBCTools.beginTx(connection);
        sql = "INSERT INTO customers VALUES(?,?,?)";
        preparedStatement = connection.prepareStatement(sql);
        Date date = new Date(new java.util.Date().getTime());

        long begin = System.currentTimeMillis();
        for(int i = 0; i < 100000; i++){
            preparedStatement.setInt(1, i + 1);
            preparedStatement.setString(2, "name_" + i);
            preparedStatement.setDate(3, date);

            preparedStatement.executeUpdate();
        }
        long end = System.currentTimeMillis();

        System.out.println("Time: " + (end - begin)); //9819

        JDBCTools.commit(connection);
    } catch (Exception e) {
        e.printStackTrace();
        JDBCTools.rollback(connection);
    } finally{
        JDBCTools.releaseDB(null, preparedStatement, connection);
    }
}

3. Batch operation, 569ms

@Test
public void testBatch(){
	Connection connection = null;
	PreparedStatement preparedStatement = null;
	String sql = null;
	
	try {
		connection = JDBCTools.getConnection();
		JDBCTools.beginTx(connection);
		sql = "INSERT INTO customers VALUES(?,?,?)";
		preparedStatement = connection.prepareStatement(sql);
		Date date = new Date(new java.util.Date().getTime());
		
		long begin = System.currentTimeMillis();
		for(int i = 0; i < 100000; i++){
			preparedStatement.setInt(1, i + 1);
			preparedStatement.setString(2, "name_" + i);
			preparedStatement.setDate(3, date);
			
			//"Accumulate" SQL 
			preparedStatement.addBatch();
			
			//When the "accumulation" reaches a certain level, it will be executed once in a unified way, and the previously "accumulated" SQL will be cleared
			if((i + 1) % 300 == 0){
				preparedStatement.executeBatch();
				preparedStatement.clearBatch();
			}
		}
		
		//If the total number is not an integral multiple of the batch number, an additional execution is required 
		if(100000 % 300 != 0){
			preparedStatement.executeBatch();
			preparedStatement.clearBatch();
		}
		
		long end = System.currentTimeMillis();
		
		System.out.println("Time: " + (end - begin)); //569
		
		JDBCTools.commit(connection);
	} catch (Exception e) {
		e.printStackTrace();
		JDBCTools.rollback(connection);
	} finally{
		JDBCTools.releaseDB(null, preparedStatement, connection);
	}
}

Tags: SQL Java Database JDBC

Posted on Mon, 04 Nov 2019 14:48:38 -0500 by ThunderLee