Download the JDK
Search the Tutorials
Hide the TOC
Trail: JDBC(TM) Database Access
Lesson: JDBC Basics
JDBC Basics
Getting Started
Setting Up a Database
Establishing a Connection
Setting Up Tables
Retrieving Values from Result Sets
Updating Tables
Milestone: The Basics of JDBC
Using Prepared Statements
Using Joins
Using Transactions
Stored Procedures
SQL Statements for Creating a Stored Procedure
Creating Complete JDBC Applications
Running the Sample Applications
Creating an Applet from an Application
Home Page > JDBC(TM) Database Access > JDBC Basics
« Previous • Trail • Next »
Retrieving Values from Result Sets

> In the previous lesson, `ResultSet` was briefly mentioned. Now, you will learn the details of the `ResultSet` interface. The `ResultSet` interface provides methods for retrieving and manipulting the results of executed queries, and `ResultSet` objects can have different functionality and characteristics. These characteristics are result set type, result set concurrency, and cursor holdability. A table of data representing a database result set is usually generated by executing a statement that queries the database.

> The type of a `ResultSet` object determines the level of its functionality in two areas: the ways in which the cursor can be manipulated, and how concurrent changes made to the underlying data source are reflected by the `ResultSet` object.

> The sensitivity of the `ResultSet` object is determined by one of three different `ResultSet` types:

> 1. `TYPE_FORWARD_ONLY` — The result set is not scrollable; its cursor moves forward only, from before the first row to after the last row. The rows contained in the result set depend on how the underlying database materializes the results. That is, it contains the rows that satisfy the query at either the time the query is executed or as the rows are retrieved.
> 2. `TYPE_SCROLL_INSENSITIVE` — The result set is scrollable; its cursor can move both forward and backward relative to the current position, and it can move to an absolute position.
> 3. `TYPE_SCROLL_SENSITIVE` — The result set is scrollable; its cursor can move both forward and backward relative to the current position, and it can move to an absolute position.

> Now, you'll see how to 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. In addition, the `Statement` methods `executeQuery` and `getResultSet` both return a `ResultSet` object, as do various `DatabaseMetaData` methods. The following code demonstrates declaring the `ResultSet` object `rs` and assigning the results of our earlier query to it by using the `executeQuery` method.

> Before you can take advantage of these features, however, you need to create a scrollable `ResultSet` object. The following line of code illustrates one way to create a scrollable `ResultSet` object:

> >     Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
> >                                          ResultSet.CONCUR_READ_ONLY);
> >     ResultSet srs = stmt.executeQuery("SELECT COF_NAME, PRICE FROM COFFEES");

> This code is similar to what you have used earlier, except that it adds two arguments to the `createStatement` method. The first argument is one of three constants added to the `ResultSet` API to indicate the type of a `ResultSet` object: `TYPE_FORWARD_ONLY`, `TYPE_SCROLL_INSENSITIVE`, and `TYPE_SCROLL_SENSITIVE`. The second argument is one of two `ResultSet` constants for specifying whether a result set is read-only or updatable: `CONCUR_READ_ONLY` and `CONCUR_UPDATABLE` . The point to remember here is that if you specify a type, you must also specify whether it is read-only or updatable. Also, you must specify the type first, and because both parameters are of type `int`, the compiler will not complain if you switch the order.

> Specifying the constant `TYPE_FORWARD_ONLY` creates a nonscrollable result set, that is, one in which the cursor moves only forward. If you do not specify any constants for the type and updatability of a `ResultSet` object, you will automatically get one that is `TYPE_FORWARD_ONLY` and `CONCUR_READ_ONLY`.

> ### Using the ResultSet Methods

> The variable `srs`, 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. A `ResultSet` object maintains a cursor, which points to its current row of data.

> When a `ResultSet` object is first created, the cursor is positioned before the first row. To move the cursor, you can use the following methods:

> - `next()` - moves the cursor forward one row. Returns true if the cursor is now positioned on a row and false if the cursor is positioned after the last row.
> - `previous()` - moves the cursor backwards one row. Returns true if the cursor is now positioned on a row and false if the cursor is positioned before the first row.
> - `first()` - moves the cursor to the first row in the `ResultSet` object. Returns true if the cursor is now positioned on the first row and false if the `ResultSet` object does not contain any rows.
> - `last()` - moves the cursor to the last row in the `ResultSet` object. Returns true if the cursor is now positioned on the last row and false if the `ResultSet` object does not contain any rows.
> - `beforeFirst()` - positions the cursor at the start of the `ResultSet` object, before the first row. If the `ResultSet` object does not contain any rows, this method has no effect.
> - `afterLast()` - positions the cursor at the end of the `ResultSet` object, after the last row. If the `ResultSet` object does not contain any rows, this method has no effect.
> - `relative(int rows)` - moves the cursor relative to its current position.
> - `absolute(int row)` - positions the cursor on the row-th row of the `ResultSet` object.

> Once you have a scrollable `ResultSet` object, `srs` in the previous example, you can use it to move the cursor around in the result set. 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.

> ### Using the getXXX Methods

> The `ResultSet` interface declares getter methods (`getBoolean`, `getLong`, and so on) for retrieving column values from the current row. Your application can retrieve values using either the index number of the column or the name of the column. The column index is usually more efficient. Columns are numbered from 1. For maximum portability, result set columns within each row should be read in left-to-right order, and each column should be read only once.

> Column names used as input to getter methods are case insensitive. When a getter method is called with a column name and several columns have the same name, the value of the first matching column will be returned. The column name option is designed to be used when column names are used in the SQL query that generated the result set. For columns that are NOT explicitly named in the query, it is best to use column numbers. If column names are used, the programmer should take care to guarantee that they uniquely refer to the intended columns, which can be assured with the SQL AS clause.

> The `getXXX` method of the appropriate type retrieves the value in each column. For example, the first column in each row of `srs` 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 `srs` 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`.

> The method `getString` is invoked on the `ResultSet` object `srs`, so `getString` retrieves (gets) the value stored in the column `COF_NAME` in the current row of `srs` . 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 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` converts the numeric value to a Java `String` object, and the value has to be converted back to a numeric type before it can be operated on as a number. In cases where the value is 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.

> >     Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
> >                                          ResultSet.CONCUR_READ_ONLY);
> >     ResultSet srs = stmt.executeQuery(
> >         "SELECT COF_NAME, PRICE FROM COFFEES");
> >     while (srs.next()) {
> >             String name = srs.getString("COF_NAME");
> >             float price = srs.getFloat("PRICE");
> >             System.out.println(name + "     " + price);
> >     }

> 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

> You can process all of the rows is srs going backward, but to do this, the cursor must start out located after the last row. You can move the cursor explicitly to the position after the last row with the method `afterLast.` Then the method `previous()` moves the cursor from the position after the last row to the last row, and then to the previous row with each iteration through the while loop. The loop ends when the cursor reaches the position before the first row, where the method `previous()` returns false .

> >     Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
> >                                              ResultSet.CONCUR_READ_ONLY);
> >     ResultSet srs = stmt.executeQuery("SELECT COF_NAME, PRICE FROM COFFEES");
> >         srs.afterLast();
> >         while (srs.previous()) {
> >             String name = srs.getString("COF_NAME");
> >             float price = srs.getFloat("PRICE");
> >             System.out.println(name + "     " + price);
> >         }

> The printout will look similar to this:

> >     French_Roast_Decaf     9.99
> >     Colombian_Decaf     8.99
> >     Espresso     9.99
> >     French_Roast     8.99
> >     Colombian     7.99

> As you can see, the printout for each has the same values, but the rows are in the opposite order.

> 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 = srs.getString(1);
> >     float n = srs.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.

> You can move the cursor to a particular row in a `ResultSet` object. The methods `first`, `last`, `beforeFirst`, and `afterLast` move the cursor to the row indicated in their names. The method `absolute` will move the cursor to the row number indicated in the argument passed to it. If the number is positive, the cursor moves the given number from the beginning, so calling `absolute(1)` puts the cursor on the first row. If the number is negative, the cursor moves the given number from the end, so calling `absolute(-1)` puts the cursor on the last row. The following line of code moves the cursor to the fourth row of `srs`:

>     srs.absolute(4);

> If `srs` has 500 rows, the following line of code moves the cursor to row 497:

>     srs.absolute(-4);

> Three methods move the cursor to a position relative to its current position. As you have seen, the method `next` moves the cursor forward one row, and the method `previous` moves the cursor backward one row. With the method `relative`, you can specify how many rows to move from the current row and also the direction in which to move. A positive number moves the cursor forward the given number of rows; a negative number moves the cursor backward the given number of rows. For example, in the following code fragment, the cursor moves to the fourth row, then to the first row, and finally to the third row:

>     srs.absolute(4); // cursor is on the fourth row
>     . . .
>     srs.relative(-3); // cursor is on the first row
>     . . .
>     srs.relative(2); // cursor is on the third row

> The method `getRow` lets you check the number of the row where the cursor is positioned. For example, you can use `getRow` to verify the current position of the cursor in the previous example as follows:

>     srs.absolute(4);
>     int rowNum = srs.getRow(); // rowNum should be 4
>     srs.relative(-3);
>     int rowNum = srs.getRow(); // rowNum should be 1
>     srs.relative(2);
>     int rowNum = srs.getRow(); // rowNum should be 3

> Four additional methods let you verify whether the cursor is at a particular position. The position is stated in their names: `isFirst`, `isLast`, `isBeforeFirst`, `isAfterLast`. These methods all return a `boolean` and can therefore be used in a conditional statement. For example, the following code fragment tests to see whether the cursor is after the last row before invoking the method `previous` in a `while` loop. If the method `isAfterLast` returns `false`, the cursor is not after the last row, so the method `afterLast` is invoked. This guarantees that the cursor will be after the last row and that using the method `previous` in the `while` loop will cover every row in `srs`.

>     if (srs.isAfterLast() == false) {
>             srs.afterLast();
>     }
>     while (srs.previous()) {
>             String name = srs.getString("COF_NAME");
>             float price = srs.getFloat("PRICE");
>             System.out.println(name + "     " + price);
>     }

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

« Previous • Trail • Next »
Previous page: Setting Up Tables
Next page: Updating Tables