The Java TM Tutorial

| Start of Tutorial > Start of Trail > Start of Lesson | Search

Trail: JDBC Database Access
Lesson: JDBC Basics

## 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 cursor 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

> `` | 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.

| Start of Tutorial > Start of Trail > Start of Lesson | Search