Reading data from a ResultSet

Having obtained a database connection and executed a query via a Statement object, the next stage is to pull out the data from the ResultSet. A result set consists of zero or more rows. Let's suppose that our query on a Users table returns rows in the following format:

Id (INT)UserName (VARCHAR)FirstName (VARCHAR)Surname (VARCHAR)TimeRegistered (TIMESTAMP)
3dsmithDavidSmith2008-04-02 18:16:22
4ntrobertsNigelRoberts2008-05-01 20:11:01
6bgsmithersBillSmithers2008-05-02 02:43:52

Now we can pull out the details of successive users from the result as follows:

ResultSet rs = st.getResultSet();
try {
  while (rs.next()) {
    int id = rs.getInt(1);
    String userName = rs.getString(2);
    String firstName = rs.getString(3);
    String surname = rs.getString(4);
    Timestamp timeReg = rs.getTimestamp(5);
    // ... do something with these variables ...
  }
} finally {
  rs.close();
}

Notice the repeated call to next(), which returns true if there is another row of data, and at the same time gets the ResultSet ready to start reading data from that next row.

The code above illustrates the following points and topics:

Prepared statements

In this example, we passed a piece of "raw" SQL to the executeQuery() method. In real life, you should generally never execute "raw" SQL in this way if you can possibly avoid it. As we'll see in a moment, prepared statements are generally a preferable way of executing SQL. Prepared statements separate out the SQL parsing stage from the executing stage. They're preferable from both a performance and a security point of view.


1. This turns out to be a slightly buggy design, because it breaks the contract for the equals() method: date.equals(timestamp) will return true if the two have identical fields except for the timestamp's nanosecond value, but not vice versa. You should therefore not mix Date and Timestamp objects as keys to a map.