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); } }