Batch Processing in JDBC and HIBERNATE

Hello!

In this article, I will briefly talk about batch processing of SQL (DML) statements: INSERT, UPDATE, DELETE, as one of the ways to achieve increased performance.

Benefits

Unlike sequential execution of each SQL query, batch processing makes it possible to send a whole set of queries (packet) in one call, thereby reducing the number of required network connections and allowing the database to execute a certain number of queries in parallel, which can significantly increase the speed of execution. Immediately make a reservation that a noticeable effect can be seen when inserting, updating or deleting large amounts of data into a database table.

DB table

As an example, we will use the book table with the id and title fields.

1. JDBC - batch processing

Before moving on to implementation examples, it is necessary to highlight several important points:

1. JDBC , supportsBatchUpdates() DatabaseMetaData, , . true, .
2. , setAutoCommit(false). , commit() rollback(). rollback() SQL .
3. — , JDBC Statement, PreparedStatement CallableStatement, .

, Statement, PreparedStatement CallableStatement . , BATCH_SIZE. , . , , Hibernate 10 50.

, SQL INSERT. UPDATE, DELETE .

1.1. Statement

Statement book.

connection.setAutoCommit(false);
try (Statement stmt = connection.createStatement()) { //(1)
    for (int i = 1; i <= SIZE; i++) {
        stmt.addBatch("INSERT INTO book (title) VALUES ('" + "JDBC Insert Example: " + i + "')"); //(2)
        if (i % BATCH_SIZE == 0 || i == SIZE) {
            try {
                int[] result = stmt.executeBatch(); //(3)
                connection.commit();
            } catch (BatchUpdateException ex) {
                Log(ex);
                connection.rollback();
            }
        }
    }
}

1. C Statement;
2. C void addBatch( String SQL );
3. executeBatch(). executeBatch() .

Statement SQL INSERT, UPDATE, DELETE.

SQL , .

1.2. PreparedStatement

PreparedStatement book.

connection.setAutoCommit(false);
try (PreparedStatement pstmt = connection.prepareStatement("INSERT INTO book (title) VALUES (?)")) { //(1)
    for (int i = 1; i <= SIZE; i++) {
        pstmt.setString(1, "JDBC Insert Example: " + i); //(2)
        pstmt.addBatch(); //(3)
        if (i % BATCH_SIZE == 0 || i == SIZE) {
            try {
                int[] result = pstmt.executeBatch(); //(4)
                connection.commit();
            } catch (BatchUpdateException ex) {
                Log(ex);
                connection.rollback();
            }
        }
    }
}

1. C PreparedStatement SQL ;
2. , ;
3. void addBatch();
4. executeBatch().

3) 4) , Statement, — addBatch() .

SQL , , . , .

PreparedStatement SQL (INSERT, UPDATE, DELETE) Statement, - .

1.3. CallableStatement

CallableStatement .

, OUT INOUT.

CallableStatement book.

connection.setAutoCommit(false);
try (CallableStatement cstmt = connection.prepareCall("call insert_book(?)")) { //(1)
    for (int i = 1; i <= SIZE; i++) {
        cstmt.setString(1, "JDBC Insert Example: " + i); //(2)
        cstmt.addBatch(); //(3)
        if (i % BATCH_SIZE == 0 || i == SIZE) {
            try {
                int[] result = cstmt.executeBatch(); //(4)
                connection.commit();
            } catch (BatchUpdateException ex) {
                Log(ex);
                connection.rollback();
            }
        }
    }
}

1. C CallableStatement ;
2. , ;
3. void addBatch();
4. executeBatch().

, PreparedStatement.

, , .

- , .

CallableStatement , - .

1.4. BatchUpdateException

BatchUpdateException, . BatchUpdateException , SQL , - , ResultSet. BatchUpdateException ( getUpdateCounts()), , executeBatch. , SQL . , c (Statement.EXECUTE_FAILED) .

:

...
} catch (BatchUpdateException ex) {
    int[] updateCount = ex.getUpdateCounts();
    int count = 1;
    for (int i : updateCount) {
        if (i == Statement.EXECUTE_FAILED) {
            System.out.println("Request " + count + ": Execute failed");
        } else {
            System.out.println("Request " + count + ": OK");
        }
        count++;
    }
}
...

. , , , , . BatchUpdateException SQL , . .

2. Hibernate —

2.1.

, , hibernate.jdbc.batch_size Hibernate.cfg.xml . Hibernate SQL INSERT, UPDATE, DELETE JDBC . SQL JDBC hibernate.order_inserts, hibernate.order_updates true, Hibernate SQL . SQL , JDBC Hibernate addBatch() PreparedStatement, SQL .

Hibernate.cfg.xml

...
<property name="hibernate.jdbc.batch_size">50</property>
<property name="hibernate.order_inserts">true</property>
<property name="hibernate.order_updates">true</property>
...

2.2.

. , . , Hibernate , OutOfMemoryException. 2 :

1. flush() clear() .

   :

   
   

   try (Session session = HibernateUtil.getSessionFactory().getCurrentSession()) {
       Transaction transaction = session.getTransaction();
       transaction.begin();
       for (int i = 1; i <= SIZE; i++) {
           session.persist(new Book("Hibernate Insert Example: " + i));
           if (i % BATCH_SIZE == 0) {
               // Flush and clear the cache every batch
               session.flush();
               session.clear();
           }
       }
       transaction.commit();
   }
   

   
   
2. StatelessSession. StatelessSession . , , , (aliasing) . , Hibernate. Hibernate.
   

   :

   
   

   try (StatelessSession session = HibernateUtil.getSessionFactory().openStatelessSession()) {
       Transaction transaction = session.getTransaction();
       transaction.begin();
       for (int i = 1; i <= SIZE; i++) {
           session.persist(new Book("Hibernate Insert Example: " + i));
       }
       transaction.commit();
   }
   

   
   

Batch execution of SQL queries is one of the well-known ways to improve performance that you should pay attention to. Reducing the number of network connections to the database and increasing the speed of query execution is a significant plus in favor of using batch processing.

Code examples can be found on GitHub .