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:

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:

• Column numbers start at 1, not zero.
• There are mappings between SQL and Java data types. For example, a SQL INT is mapped to a Java int; SQL VARCHARs are mapped to Java Strings. We call a get() method on the ResultSet that is appropriate to the type we want to retrieve. (As discussed on the next page, it turns out that these methods luckily can often perform conversion if we don't choose the exact type.)
• The ResultSet and/or Statement needs to be closed. Whilst in emergencies they will be closed on finalization, it's preferable to close them explicitly as soon as they are no longer needed. Unfortunately, there are three different types of JDBC resource, all of which have a close() method! In a moment, we'll discuss when to close JDBC resources.

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.