DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
  1. DZone
  2. Data Engineering
  3. Databases
  4. Running Batch DB Updates in Java Persistence Technologies

Running Batch DB Updates in Java Persistence Technologies

it is a good idea to use the batch update facilities built on top of JDBC in order to submit multiple SQL commands as a single request to the underlying database.

Mahmoud Anouti user avatar by
Mahmoud Anouti
·
Jan. 04, 19 · Tutorial
Like (2)
Save
Tweet
Share
21.17K Views

Join the DZone community and get the full member experience.

Join For Free

Relational data access often needs to insert or update multiple rows in a database as part of a single operation. In such scenarios, it is a good idea to use the batch update facilities built on top of JDBC in order to submit multiple SQL commands as a single request to the underlying database. This reduces the number of roundtrips to the database, hence improving the result time of the operation.

JDBC Batched Updates

The Statement interface and its subinterfaces, PreparedStatement and CallableStatement support executing multiple SQL statements as a batch, by maintaining a collection of these statements that the application can add to using the method Statement.addBatch(sql). When the batch of statements is ready to be executed, the method Statement.executeBatch() can be called to execute them in one unit. To clear the current batch, the application can call the method Statement.clearBatch(). Only statements that return an update count are eligible for batch execution; select statements will throw a BatchUpdateException.

Example

The following code uses a Statement's batch to add a student to a course:

try(Connection connection = dataSource.getConnection()) {
    connection.setAutoCommit(false);

    try(Statement statement = connection.createStatement()) {
        statement.addBatch("insert into student values (14, 'John Doe')");
        statement.addBatch("insert into course values (3, 'Biology')");
        statement.addBatch("insert into student_courses values (14, 3)");

        int[] updateCounts = statement.executeBatch();
        connection.commit();

    } catch(BatchUpdateException ex) {
        connection.rollback();
        ... // do something with exception
    }
}

Another example using a PreparedStatement. Given a customer table, we want to import a list of customers. Notice that the method addBatch does not take an SQL string here, instead it adds the specified parameters to the prepared statement's batch of commands.

connection.setAutoCommit(false);
try(PreparedStatement statement = connection.prepareStatement("insert into customer values (?, ?)")) {
    int n = 0;
    for(Customer customer : customers) {
        statement.setInt(1, ++n);
        statement.setString(2, customer.getName());
        statement.addBatch();
    }
    int[] updateCounts = statement.executeBatch();
    connection.commit();
} catch(BatchUpdateException ex) {
    connection.rollback();
    ... // do something with exception
}

Switching Off Auto-Commit

One important thing to notice in the above code snippets is the call to connection.setAutoCommit(false), which allows the application to control when to commit the transaction. In the previous code, we only commit the transaction when all statements are executed successfully. In case of a BatchUpdateException thrown because of a failed statement, we roll back the transaction so that no effect happens on the database. We could have decided to examine the BatchUpdateException (as we'll see shortly) to see which statement(s) failed and still decide to commit the statements that were processed successfully.

Disabling auto-commit mode should always be done when executing a batch of updates. Otherwise, the result of the updates depends on the behavior of the JDBC driver: it may or may not commit the successful statements.

Update Counts and BatchUpdateException

The method Statement.executeBatch() returns an array of integers where each value is the number of affected rows by the corresponding statement. The order of values matches the order in which statements are added to the batch. Specifically, each element of the array is:

  1. an integer >= 0, reflecting the affected row count by the update statement,
  2. or the constant Statement.SUCCESS_NO_INFO, indicating that the statement was successful but the affected row count is unknown.

In case one of the statements failed, or was not a valid update statement, the method executeBatch() throws a BatchUpdateException. The exception can be examined by calling BatchUpdateException.getUpdateCounts(), which returns an array of integers. There are two possible scenarios:

  1. If the JDBC driver allows continuing the processing of remaining statements upon a failed one, then the result of BatchUpdateException.getUpdateCounts() is an array containing as many integers as there were statements in the batch, where the integers correspond to the affected row count for successful statements, except for the failed ones where the corresponding array element will be the constant Statement.EXECUTE_FAILED.
  2. If the JDBC driver does not continue upon a failed statement, then the result of BatchUpdateException.getUpdateCounts() is an array containing the affected row count for all successful statements until the first failed one.

Batch Updates Using Spring's JdbcTemplate

Spring offers a convenient class as part of its support for JDBC. It reduces the amount of boilerplate code required when using plain JDBC such as processing result sets and closing resources. It also makes batch updates easier, as shown in the following example:

List<Customer> customers = ...;

jdbcTemplate.batchUpdate("insert into customer values (?, ?)",
             new BatchPreparedStatementSetter() {

    @Override
    public void setValues(PreparedStatement ps, int i) throws SQLException {
        ps.setLong(1, customers.get(i).getId());
        ps.setString(2, customers.get(i).getName());
    }

    @Override
    public int getBatchSize() {
        return customers.size();
    }
});

Batch Updates Using Hibernate

Hibernate can also make use of JDBC's batching facility when generating the statements corresponding to its persistence operations. The main configuration property is hibernate.jdbc.batch_size which specifies the maximum batch size. This setting can be overriden for a specific session using the method Session.setJdbcBatchSize(). Hibernate will use the value specified in the method on the current session, and if not set it uses the value in the global session factory-level setting hibernate.jdbc.batch_size.

The earlier example that stores a list of customers would use the persistence methods in the Session instance:

Transaction transaction = null;
try (Session session = sessionFactory.openSession()) {
    transaction = session.getTransaction();
    transaction.begin();

    for (Customer customer : customers) {
        session.persist(customer);
    }

    transaction.commit();
} catch (RuntimeException ex) {
    if (transaction != null) {
        transaction.rollback();
    }
    throw ex;
}

When the transaction.commit() is invoked, Hibernate will send the SQL statements that insert the customer rows. If batching is enabled as described earlier (either via hibernate.jdbc.batch_size or by calling Session.setJdbcBatchSize(batchSize)), then all the generated statements will be sent as a single request. Otherwise, each statement is sent as a single request.

When employing batched updates in Hibernate for a large number of entity objects, it is a good practice to flush the session and clear its cache periodically as opposed to flushing the session at the end of the transaction. This reduces memory usage by the session cache because it holds entities that are in persistent state:

Transaction transaction = null;
try (Session session = sessionFactory.openSession()) {
    transaction = session.getTransaction();
    transaction.begin();

    int n = 0;
    for (Customer customer : customers) {
        if (++n % batchSize == 0) {
            // Flush and clear the cache every batch
            session.flush();
            session.clear();
        }
        session.persist(customer);
    }

    transaction.commit();
} catch (RuntimeException ex) {
    if (transaction != null) {
        transaction.rollback();
    }
    throw ex;
}

One important thing to know is that batch insert (not update or delete) doesn't work with entities using identity columns (i.e. whose generation strategy is GenerationType.IDENTITY, because Hibernate needs to generate the identifier when persisting the entity and in this case the value can only be generated by sending the insert statement.

It should be noted that the above applies equally if the application uses an EntityManager instead of directly using a Session.

Batch Updates Using jOOQ

jOOQ also supports batch updates easily. Here's an example that follows the earlier examples:

DSLContext create = ...;
BatchBindStep batch = create.batch(create.insertInto(CUSTOMER, ID, NAME)
                                         .values((Integer) null, null));
int n = 0;
for (Customer customer : customers) {
    batch.bind(++n, customer.getName());
}
int[] updateCounts = batch.execute();

Summary

All major Java persistence technologies support batch mode updates to relational databases leveraging the JDBC API. Such a mode can improve performance for applications involving heavy workloads by reducing the number of network roundtrips to the database server.

Relational database Database sql Persistence (computer science) Java (programming language) application Java Database Connectivity Session (web analytics) Hibernate

Published at DZone with permission of Mahmoud Anouti, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • DevSecOps Benefits and Challenges
  • The Real Democratization of AI, and Why It Has to Be Closely Monitored
  • A Simple Union Between .NET Core and Python
  • Implementing Infinite Scroll in jOOQ

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends: