Using the batch feature of PreparedStatement

When you update a large amount of data, prepare an INSERT statement multiple times, causing many times of network connectivity. To reduce the number of JDBC calls to improve performance, you can use PreparedStatement's Addbatch () method to send multiple queries to the database at once. For example, let's compare the following example.

Example 1: Multiple executions of prepared Statement

PreparedStatement PS = conn.preparestatement (
"INSERT into employees values (?,?,?)");

for (n = 0; n <; n++) {

Ps.setstring (Name[n]);
Ps.setlong (Id[n]);
Ps.setint (Salary[n]);
Ps.executeupdate ();
}

Example 2: Using Batch

PreparedStatement PS = conn.preparestatement (
"INSERT into employees values (?,?,?)");

for (n = 0; n <; n++) {

Ps.setstring (Name[n]);
Ps.setlong (Id[n]);
Ps.setint (Salary[n]);
Ps.addbatch ();
}
Ps.executebatch ();

In Example 1, PreparedStatement is used to execute the INSERT statement multiple times. Here, 100 insert operations were performed, with a total of 101 network round trips. Of these, 1 roundtrip is the pre-stored statement, and the other 100 round trips perform each iteration. In Example 2, when you use the Addbatch () method in 100 insert operations, only two network round trips are used. 1 round trips are pre-stored statement, and another is the Execute batch command. Although the batch command uses more CPU cycles for the database, performance is improved by reducing network round trips. Remember, the greatest improvement in JDBC performance is to reduce the network traffic between the JDBC driver and the database.