Retrieving data from tables using the Statement.executeQuery method

To retrieve data from a table using a SELECT statement with no parameter markers, you can use the Statement.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 Statement.executeQuery method to retrieve a result set from a stored procedure call, if that stored procedure returns only one result set. If the stored procedure returns multiple result sets, you need to use the Statement.execute method.

This topic discusses the simplest kind of ResultSet, which is a read-only ResultSet in which you can only move forward, one row at a time. The IBM Data Server Driver for JDBC and SQLJ also supports updatable and scrollable ResultSets.

Procedure

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

  1. Invoke the Connection.createStatement method to create a Statement object.
  2. Invoke the Statement.executeQuery method to obtain the result table from the SELECT statement in a ResultSet object.
  3. In a loop, position the cursor using the next method, and retrieve data from each column of the current row of the ResultSet object using getXXX methods. XXX represents a data type.
  4. Invoke the ResultSet.close method to close the ResultSet object.
  5. Invoke the Statement.close method to close the Statement object when you have finished using that object.

Example

The following code demonstrates how to retrieve all rows from the employee table. The numbers to the right of selected statements correspond to the previously-described steps.
Figure 1. Using Statement.executeQuery
String empNo;
Connection con;
Statement stmt;
ResultSet rs;
…
stmt = con.createStatement();     // Create a Statement object           1 
rs = stmt.executeQuery("SELECT EMPNO FROM EMPLOYEE");                    2 
                                  // Get the result table from the query
while (rs.next()) {               // Position the cursor                 3 
 empNo = rs.getString(1);             // Retrieve only the first column value
 System.out.println("Employee number = " + empNo);
                                  // Print the column value
}
rs.close();                       // Close the ResultSet                 4 
stmt.close();                     // Close the Statement                 5