How is JDBC Batch Update helpful?

Question

Everybody says batch updates reduce the number of JDBC calls. Could someone explain what "JDBC call" means and how increased number of such calls are costlier compared to carrying entire load in one JDBC call.

Answer 1

Colloquially, when developer's use the phrase JDBC call they're talking about sending information over the wire to the database. The batch feature of the JDBC API allows you to submit multiple discrete operations in a single network call, as opposed to a call for each SQL statement. From the JDBC spec:

The batch update facility allows a Statement object to submit a set of heterogeneous SQL statements together as a single unit, or batch, to the underlying data source.

In the case of a PreparedStatement, the added benefit of only having to create a single statement exists. This allows you to execute the same query with multiple sets of bind parameters without adding the statement itself to the batch multiple times. The end result is less network traffic and its associated overhead.

An example from the spec:

PreparedStatement stmt = con.prepareStatement(
"INSERT INTO employees VALUES (?, ?)");

stmt.setInt(1, 2000);
stmt.setString(2, "Kelly Kaufmann");
stmt.addBatch();

stmt.setInt(1, 3000);
stmt.setString(2, "Bill Barnes");
stmt.addBatch();

// submit the batch for execution
int[] updateCounts = stmt.executeBatch();

Answer 2

As described in IBM's documentation:

The JDBC drivers that support JDBC 2.0 and above support batch updates. With batch updates, instead of updating rows of a DB2(R) table one at a time, you can direct JDBC to execute a group of updates at the same time. Statements that can be included in the same batch of updates are known as batchable statements.

If a statement has input parameters or host expressions, you can include that statement only in a batch that has other instances of the same statement. This type of batch is known as a homogeneous batch. If a statement has no input parameters, you can include that statement in a batch only if the other statements in the batch have no input parameters or host expressions. This type of batch is known as a heterogeneous batch. Two statements that can be included in the same batch are known as batch compatible.

Answer 3

On calling JDBC update, the java program connects to the database server and executes the query. It means for each update, the java program contacts the database server and executes the query. Now, if we use JDBC batch-update, the java application needs to contact the database server only once and executes all the queries at the database server and returns to the java application.

In brief, it will reduce the round trip between java application and database server. Normally both will be on a different server, so it will reduce a lot of network resources. Hence, it achieves better performance.