Retrieving Values from Result Sets

We now show how you send the above


SELECT

statements from a program written in the Java programming language and how you get the results we showed.

JDBC returns results in a ResultSet object, so we need to declare an instance of the class ResultSet to hold our results. The following code demonstrates declaring the ResultSet object rs and assigning the results of our earlier query to it:

ResultSet rs = stmt.executeQuery(
    "SELECT COF_NAME, PRICE FROM COFFEES");

Using the Method next

The variable rs , which is an instance of ResultSet , contains the rows of coffees and prices shown in the result set example above. In order to access the names and prices, we will go to each row and retrieve the values according to their types. The method next moves what is called a cursor to the next row and makes that row (called the current row) the one upon which we can operate. Since the cursor is initially positioned just above the first row of a ResultSet object, the first call to the method next moves the cursor to the first row and makes it the current row. Successive invocations of the method next move the cursor down one row at a time from top to bottom. Note that with the JDBC 2.0 API, covered in the next section, you can move the cursor backwards, to specific positions, and to positions relative to the current row in addition to moving the curs or forward.

Using the getXXX Methods

We use the getXXX method of the appropriate type to retrieve the value in each column. For example, the first column in each row of rs is COF_NAME , which stores a value of SQL type VARCHAR . The method for retrieving a value of SQL type VARCHAR is getString . The second column in each row stores a value of SQL type FLOAT , and the method for retrieving values of that type is getFloat . The following code accesses the values stored in the current row of rs and prints a line with the name followed by three spaces and the price. Each time the method next is invoked, the next row becomes the current row, and the loop continues until there are no more rows in rs .

String query = "SELECT COF_NAME, PRICE FROM COFFEES";
ResultSet rs = stmt.executeQuery(query);
while (rs.next()) {
    String s = rs.getString("COF_NAME");
    float n = rs.getFloat("PRICE");
    System.out.println(s + "   " + n);
}

The output will look something like this:

Colombian   7.99
French_Roast   8.99
Espresso   9.99
Colombian_Decaf   8.99
French_Roast_Decaf   9.99

Note that we use a curved arrow to identify output from JDBC code; it is not part of the output. The arrow is not used for results in a result set, so its use distinguishes between what is contained in a result set and what is printed as the output of an application.

Let's look a little more closely at how the getXXX methods work by examining the two getXXX statements in this code. First let's examine getString .

String s = rs.getString("COF_NAME");

The method getString is invoked on the ResultSet object rs , so getString will retrieve (get) the value stored in the column COF_NAME in the current row of rs . The value that getString retrieves has been converted from an SQL VARCHAR to a String in the Java programming language, and it is assigned to the String object s . Note that we used the variable s in the println expression shown above, that is, println(s + " " + n) .

The situation is similar with the method getFloat except that it retrieves the value stored in the column PRICE , which is an SQL FLOAT , and converts it to a Java float before assigning it to the variable n .

JDBC offers two ways to identify the column from which a getXXX method gets a value. One way is to give the column name, as was done in the example above. The second way is to give the column index (number of the column), with 1 signifying the first column, 2 , the second, and so on. Using the column number instead of the column name looks like this:

String s = rs.getString(1);
float n = rs.getFloat(2);

The first line of code gets the value in the first column of the current row of rs (column COF_NAME ), converts it to a Java String object, and assigns it to s . The second line of code gets the value stored in the second column of the current row of rs , converts it to a Java float , and assigns it to n . Note that the column number refers to the column number in the result set, not in the original table.

In summary, JDBC allows you to use either the column name or the column number as the argument to a getXXX method. Using the column number is slightly more efficient, and there are some cases where the column number is required. In general, though, supplying the column name is essentially equivalent to supplying the column number.

JDBC allows a lot of latitude as far as which getXXX methods you can use to retrieve the different SQL types. For example, the method getInt can be used to retrieve any of the numeric or character types. The data it retrieves will be converted to an int ; that is, if the SQL type is VARCHAR , JDBC will attempt to parse an integer out of the VARCHAR . The method getInt is recommended for retrieving only SQL INTEGER types, however, and it cannot be used for the SQL types BINARY , VARBINARY , LONGVARBINARY , DATE , TIME , or TIMESTAMP .

Table 24, Methods for Retrieving SQL Types shows which methods can legally be used to retrieve SQL types and, more important, which methods are recommended for retrieving the various SQL types. Note that this table uses the term "JDBC type" in place of "SQL type." Both terms refer to the generic SQL types defined in java.sql.Types , and they are interchangeable.

Using the Method getString

Although the method getString is recommended for retrieving the SQL types CHAR and VARCHAR , it is possible to retrieve any of the basic SQL types with it. (You cannot, however, retrieve the new SQL3 datatypes with it. We will discuss SQL3 types later in this tutorial.) Getting all values with getString can be very useful, but it also has its limitations. For instance, if it is used to retrieve a numeric type, getString will convert the numeric value to a Java String object, and the value will have to be converted back to a numeric type before it can be operated on as a number. In cases where the value will be treated as a string anyway, there is no drawback. Further, if you want an application to retrieve values of any standard SQL type other than SQL3 types, use the getString method.

Use of ResultSet.getXXX Methods to Retrieve JDBC Types

Note: If you have trouble reading this table, see Use of ResultSet.getXXX: Table-Free Versions for alternate views of the same information.

	T I N Y I N T	S M A L L I N T	I N T E G E R	B I G I N T	R E A L	F L O A T	D O U B L E	D E C I M A L	N U M E R I C	B I T	C H A R	V A R C H A R	L O N G V A R C H A R	B I N A R Y	V A R B I N A R Y	L O N G V A R B I N A R Y	D A T E	T I M E	T I M E S T A M P
`getByte`	X	x	x	x	x	x	x	x	x	x	x	x	x
`getShort`	x	X	x	x	x	x	x	x	x	x	x	x	x
`getInt`	x	x	X	x	x	x	x	x	x	x	x	x	x
`getLong`	x	x	x	X	x	x	x	x	x	x	x	x	x
`getFloat`	x	x	x	x	X	x	x	x	x	x	x	x	x
`getDouble`	x	x	x	x	x	X	X	x	x	x	x	x	x
`getBigDecimal`	x	x	x	x	x	x	x	X	X	x	x	x	x
`getBoolean`	x	x	x	x	x	x	x	x	x	X	x	x	x
`getString`	x	x	x	x	x	x	x	x	x	x	X	X	x	x	x	x	x	x	x
`getBytes`														X	X	x
`getDate`											x	x	x				X		x
`getTime`											x	x	x					X	x
`getTimestamp`											x	x	x				x	x	X
`getAsciiStream`											x	x	X	x	x	x
`getUnicodeStream`											x	x	X	x	x	x
`getBinaryStream`														x	x	X
`getObject`	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x	x

An "x" indicates that the getXXX method may legally be used to retrieve the given JDBC type.

An " X " indicates that the getXXX method is recommended for retrieving the given JDBC type.