As we saw in the previous chapter, the SELECT statement extracts data from a database. Here's an example which should be prefaced with the warning that columns are numbered starting with 1, not zero. That is an SQL convention that really had to be respected by Java. If we run this Java code fragment,
ResultSet result;
result = statement.executeQuery( " SELECT Person.name, Person.age "
+ "FROM Person "
+ "WHERE Person.age = 24 " );
while (result.next()) {
String p = result.getString(1);
int a = result.getInt(2);
System.out.println( p + " is " + a + " years");
}
we'll get output like this:
Robert Bellamy is 24 years
Timothy French is 24 years
Elizabeth Kramer is 24 years
Relating that output to the code fragment shows how the ResultSet object can hold multiple values. I like to think of ResultSet as being similar to a 2D array. Instead of incrementing the most significant index variable, you call the result method next(). Each time you call next(), you are moved on to the next record in the result set. You need to call next() before you can see the first result record, and it returns false when there are no more result records, so it is convenient for controlling a while loop. That does make it different from an Iterator, however, so be alert to that difference. As a reminder, the Iterator next() method returns the next object, not a true/false value. A true/false value can be returned for a result set next() because there is another group of methods for actually getting the data. Read on to find out what!
You get individual values from a column within a record by calling one of the many methods whose signature looks like this:
SomeType getSomeType( colNumberOrName );
The argument can be the name of the attribute, or the column number (which starts at 1, remember). Thus, the class ResultSet has methods getBlob(), getBigDecimal(), getDate(), getBytes(), getInt(), getLong(), getString(), getObject(), and so on, for all the Java types that represent SQL types and for a column name and column number argument. The getObject() is interesting. If the database supports it, you can put a Java object into the database! You can then retrieve it later, and invoke methods on it. So your database may be able to store and catalog serialized Java objects as well as data.
Column numbers should be used for columns that are not explicitly named in the query, such as when you do a “select '*'”. Column names can be unreliable in this case, but otherwise they document the intent of your program better. Another advantage of using column names versus column index for the ResultSet 'get' methods is that your code doesn't break when your query changes to include more columns. Access by name might run into limitations of JDBC drivers, though. Some drivers allow access to the result set columns only in the order of the index. If you use by name and try to access columns out of order, you will get an exception.You can see all of the get-methods if you review the javadoc HTML pages for java.sql.ResultSet.
Good programming practice says that you should close Statement objects explicitly when they are no longer needed, with a statement like this:
myStmt.close();
Closing a statement when you are finished with it is important because it frees up resources (like locks and caches) on both the server and the client.
A default ResultSet object is not updatable and has a cursor that moves forward only. With this type of Result Set you can only go through the result records once, and only from the first row to the last row in order. That's not very convenient, so JDBC 2.0 brought in some new methods that let you specify (when you create a statement) that you want something better than the default. In this code example,
Statement stmt = conn.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE );
all result sets created by that statement will:
-
Be scrollable. You can move backwards and forwards among the records of the result set. A cursor indicates the current position in the result set.
-
Not sense updates by others that occur after your result set was constructed. That is, despite possible updates to the database from elsewhere, your result set will not change. This may or may not be what you want.
-
Be updatable. If a result set is updatable, it means you can call a method to change its value, and then another method to put that same change back in the database too. This is very handy when the query results are being reviewed by a person online. They can type a new value for some field, and your program can move that to the result set and get it to update the database without formulating a whole new SQL query.
If a database cannot support the result set configuration you have requested, it will carry on processing and return a result set that it can complete. It will also add a warning to your connection object. So it is a good idea to check for warnings before and after creating a customized result set. The method getWarnings() of the Connection class will do this check. If you try to do something that is not supported on your result set, it will raise an SQLException. See the fields of the ResultSet class for other options.