Related concepts
“JDBC interfaces for executing SQL” on page 24
Related tasks
“Retrieving multiple result sets from a stored procedure in a JDBC application”
on page 46
Related reference
“Driver support for JDBC APIs” on page 252
Making batch queries in JDBC applications
The IBM Data Server Driver for JDBC and SQLJ provides a IBM Data Server
Driver for JDBC and SQLJ-only DB2PreparedStatement interface that lets you
perform batch queries on a homogeneous batch.
To make batch queries using a single statement with several sets of input
parameters, follow these basic steps:
1. Invoke the prepareStatement method to create a PreparedStatement object for
the SQL statement with input parameters.
2. For each set of input parameter values:
a. Execute PreparedStatement.setXXX methods to assign values to the input
parameters.
b. Invoke the PreparedStatement.addBatch method to add the set of input
parameters to the batch.
3. Cast the PreparedStatement object to a DB2PreparedStatement object, and
invoke the DB2PreparedStatement.executeBatch method to execute the
statement with all sets of parameters.
4. In a loop, retrieve each ResultSet object that is associated with the
PreparedStatement object.
a. For each ResultSet object, retrieve all rows.
Example: In the following code fragment, two sets of parameters are batched. A
SELECT statement that takes one input parameter is then executed twice, once
with each parameter value. The numbers to the right of selected statements
correspond to the previously described steps.
String empnum, phonenum;
Connection con;
PreparedStatement pstmt;
ResultSet rs;
...
pstmt = con.prepareStatement(
"SELECT EMPNO, PHONENO FROM EMPLOYEE WHERE EMPNO=?");
// Create a PreparedStatement object 1
pstmt.setString(1,"000010"); // Assign value to input parameter 2
rs = pstmt.executeQuery(); // Get the result table from the query 3
while (rs.next()) { // Position the cursor 4
empnum = rs.getString(1); // Retrieve the first column value
phonenum = rs.getString(2); // Retrieve the first column value
System.out.println("Employee number="+empnum +
"Phone number="+phonenum);
// Print the column values
}
rs.close(); // Close the ResultSet 5
pstmt.close(); // Close the PreparedStatement 6
Figure 13. Example of using PreparedStatement.executeQuery
34 Application Programming Guide and Reference for Java
™
To Next Page
Loading...
Need help?
General