Batch heterogeneous updates with parameters

With pureQuery, you can batch INSERT, UPDATE, and DELETE statements that refer to different tables. These heterogeneous batch updates allow all associated tables to be updated in one network round trip to the server. With this means of heterogeneous batch updates, you call a method to indicate to pureQuery that you are starting a batch update.
Restriction: To use this feature, your application must run SQL statements against a DB2® database that is running on Linux, UNIX, Windows, or z/OS®. The application must connect to that database by using the IBM® Data Server Driver for JDBC and SQLJ. See the system requirements to find out which version of this driver is required for the version of pureQuery that you are using.

All subsequent SQL statements that your application requests to run on the indicated connection are queued in memory. pureQuery returns dummy results to your application to indicate that each statement ran successfully, even though they did not in fact run yet. You call a method that indicates to pureQuery that you are ending a batch update. At that point, pureQuery runs all of the queued SQL statements.

Use question marks ("?") in your SQL statements to indicate where your methods pass values to those statements. You can also use :name or ?#.name. name must refer to a property within a java.util.Map<String> object or a bean. For more information about parameter markers, see Parameter markers in SQL statements.

The batch process

  1. Call the startBatch() method to indicate the start of the batch of SQL statements:
    data.startBatch(HeterogeneousBatchKind.heterogeneousModify_);
    After you call this method, pureQuery queues all SQL statements that your application requests to run against the same implementation of the Data interface. pureQuery continues to queue INSERT, UPDATE, and DELETE statements until the application calls the endBatch() method. As pureQuery queues the statements, it returns dummy results to your application to indicate that the statements ran successfully.

    Your application can request to run only INSERT, UPDATE, and DELETE statements. pureQuery rejects all other types of SQL statement and those statements are not run against your connection. If your application submits another type of SQL statement, pureQuery throws an exception and clears all of the statements that is has queued for the batch. Also, pureQuery does not run the statement that caused the exception.

    If your application calls the startBatch() method again before it calls the endBatch() method, pureQuery stops the batch process, erases the statements that are queued, and throws a RuntimeException.

  2. Call the endBatch() method to indicate the end of the batch of SQL statements and to cause pureQuery to run the statements that are queued.
    int[][] endBatch();
    pureQuery runs the queued statements in one network trip. The method returns a two-dimensional integer array of the update counts that the update() and updateMany() methods would have returned. The size of the first dimension is equal to the number of requests that your application submitted to run SQL statements. The size of the second dimension is either:
    1
    Indicates that an update() method was batched.
    n
    If an updateMany() method was batched, n is the number of rows of data in the updateMany() method.
If your application needs to find out whether the startBatch() was submitted but not yet the endBatch() method, your application can use the getBatchKind() method.
HeterogeneousBatchKind getBatchKind();
If a batch is in progress, the method returns heterogeneousModify_. If no batch is in progress, the method returns hetergeneousNone_.

Your application can call the updateMany() method between calling the startBatch() and endBatch() methods. The updateMany() method can run a single SQL INSERT, UPDATE, or DELETE statement multiple times against a single database object. For more information about this method, see Batch updates against single database objects by means of the updateMany() method of the Data interface.

Requirements

All inline methods and annotated methods that run the SQL statements to be part of the batch update must share the same Data object. This is done slightly differently, depending on the mix of pureQuery methods the user application uses.

Only inline methods
Create an instance of Data that you can use for the batch update, as in this example:
Data data = DataFactory.getData(jdbcConnection);
data.startBatch(...);
// other Data methods that use the Data instance "data"
data.endBatch();
After the application calls the endBatch() method, the application can use the Data instance for different purposes, if you want it to.
Inline methods and annotated methods
Create a Data instance and then pass that Data instance to instantiate the interfaces that declare your annotated methods, as in this example:
Data data = DataFactory.getData (jdbcConnection);
// now use "data" to instantiate the interfaces that will be used
EmployeeInterface emp = DataFactory.getData (EmployeeInterface.class, data);
DepartmentInterface dept = DataFactory.getData (DepartmentInterface.class, data);
data.startBatch(...);
// other Data and Annotated (EmployeeInterface and DepartmentInterface) methods
data.endbatch();
Only annotated methods
Instantiate the first interface and then use that interface to instantiate all the other interfaces. Implementations that DataFactory returns extend an implementation of the Data interface. To call the Data methods in the implementation, you cast the implementation to the Data type.
EmployeeInterface emp = DataFactory.getData (EmployeeInterface.class, jdbcConnection);
DepartmentInterface dept = DataFactory.getData (DepartmentInterface.class, (Data) emp);
Data data = (Data) emp;
data.startBatch(...);
// other Annotated (EmployeeInterface and DepartmentInterface) methods
data.endbatch();
Attention: Data objects are not thread safe. Do not share them between multiple threads. Use a Data object only in the same thread that it was created in.

Exceptions

To the database, all of the SQL statements between startBatch() and endBatch() represent one transaction. Calling commit() or rollback() after startBatch() and before calling endBatch() causes the batch not to run and the queued requests are lost. Finally, pureQuery throws a RuntimeException.

If one or more SQLExceptions are returned from the JDBC driver, they are wrapped within an UpdateManyException.

If the endBatch() method causes an UpdateManyException, your application can call the method int[][] getHeterogeneousUpdateCounts(), which returns the update count as a two-dimensional integer array, as would be returned if the batch completed normally.


Feedback