Retrieving data from tables using the PreparedStatement.executeQuery method

To retrieve data from a table using a SELECT statement with parameter markers, you use the PreparedStatement.executeQuery method.

About this task

This method returns a result table in a ResultSet object. After you obtain the result table, you need to use ResultSet methods to move through the result table and obtain the individual column values from each row.

With the IBM® Data Server Driver for JDBC and SQLJ, you can also use the PreparedStatement.executeQuery method to retrieve a result set from a stored procedure call, if that stored procedure returns only one result set and has only input parameters. If the stored procedure returns multiple result sets, you need to use the PreparedStatement.execute method.

You can also use the PreparedStatement.executeQuery method for statements that have no parameter markers. When you execute a query many times, you can get better performance by creating the SQL statement as a PreparedStatement.

Procedure

To retrieve rows from a table using a SELECT statement with parameter markers, you need to perform these steps:

  1. Invoke the Connection.prepareStatement method to create a PreparedStatement object.
  2. Invoke PreparedStatement.setXXX methods to pass values to the input parameters.
  3. Invoke the PreparedStatement.executeQuery method to obtain the result table from the SELECT statement in a ResultSet object.
    Restriction: For a PreparedStatement that contains an IN predicate, the expression that is the argument of the IN predicate cannot have more than 32767 parameters if the target data server is a Db2® on Linux®, UNIX, and Windows systems system. Otherwise, the IBM Data Server Driver for JDBC and SQLJ throws an SQLException with error code -4499.
  4. In a loop, position the cursor using the ResultSet.next method, and retrieve data from each column of the current row of the ResultSet object using getXXX methods.
  5. Invoke the ResultSet.close method to close the ResultSet object.
  6. Invoke the PreparedStatement.close method to close the PreparedStatement object when you have finished using that object.

Example

The following code demonstrates how to retrieve rows from the employee table for a specific employee. The numbers to the right of selected statements correspond to the previously-described steps.
Figure 1. Example of using PreparedStatement.executeQuery
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