Previous Table of Contents Next


Fetching Data

Selecting data from a single table is done by using the Statement method executeQuery(). The steps for fetching data are as follows:

1.  Create a statement from a connection.
2.  Execute the query.
3.  Fetch a result set from the Statement object.
For example, the following call returns a list of all inventory items: 
...
 Connection con = DriverManager.getConnection(“jdbc:odbc:Inventory”,
                                             “”,“”);
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(“SELECT * FROM Inventory”);
...

A database query returns a result set. If no values are returned, ResultSet will not contain values.

Working with a Result Set

Once you have fetched a result set from a database call, you will want to process it. ResultSet is organized into logical rows and columns of data. It maintains a cursor to the current row. You can instruct ResultSet to move to the next row by calling next().

NOTE:  JDBC 2.0 contains methods for traversing to the next, previous, first, and last row of ResultSet. It has methods for deleting current row, jumping to the insert row, and so on. In general, ResultSet is much more feature rich. However, with some work, most of these features can be duplicated in JDBC 1.0.

The columns of ResultSet can be accessed in any order and as often as needed. Columns can be accessed by their positions in the column list or by name. For instance, if you had the following SQL: 

SELECT name, address, city FROM Customers

you could access the address attribute by name, address, or by index, 2. To get values from ResultSet, use the getXXX methods included in the ResultSet object. Table 3.10 lists these methods.

Table 3.10 ResultSet getXXX Methods
INDEX GETXXX METHOD NAME GETXXX
METHOD 		USE
InputStream getAsciiStream(int columnIndex) InputStream getAsciiStream(String columnName) Get a column value as a stream of ASCII characters.
BigDecimal getBigDecimal(int columnIndex, int scale) BigDecimal getBigDecimal(String columnName, int scale) Get the value of a column as a java.lang.BigDecimal object.
InputStream getBinaryStream(int columnIndex) InputStream getBinaryStream(String columnName) Get a column value as a stream of bytes.
Boolean getBoolean(int columnIndex) boolean getBoolean(String columnName) Get the value of a column as a Boolean integer.
byte getByte(int columnIndex) byte getByte(String columnName) Get the value of a column as a byte.
byte[] getBytes(int columnIndex) byte[] getBytes(String columnName) Get the value of a column as an array of bytes.
Date getDate(int columnIndex) Date getDate(String columnName) Get the value of a column as a java.sql.Date object. Date is a thin wrapper around java.util.Date.
double getDouble(int columnIndex) double getDouble(String columnName) Get the value of a column as a double.
FloatgetFloat(int columnIndex) float getFloat(String columnName) Get the value of a column as a float.
int getInt(int columnIndex) int getInt(String columnName) Get the value of a column as an int.
Object getObject(int columnIndex) Object getObject(String columnName) Get the value of a column as an Object. The mapping is based on the JDBC spec and is listed in Appendix A.
short getShort(int columnIndex) short getShort(String columnName) Get the value of a column as a short.
String getString(int columnIndex) String getString(String columnName) Get the value of a column as a String.
Time getTime(int columnIndex) Time getTime(String columnName) Get the value of a column as a java.sql.Time object. Time is a wrapper around java.util.Date that adds formatting and parsing operations to support the JDBC escape syntax for time values.
Timestamp getTimestamp(int columnIndex) Timestamp getTimestamp(String columnName) Get the value of a column as a java.sql.Timestamp object. Timestamp is a wrapper around java.util.Date that adds the ability to hold the SQL TIMESTAMP nanos value and provides formatting andparsing operations to support the JDBC escape syntax for timestamp values.

NOTE:  If two columns have the same name, the first one matching your column name will be returned. For this reason, it is recommended that you use the position or index to reference the column. This way, programmers can guarantee they are returning the correct column.

The next example fetches a list of all suppliers and the beverage products they sell from the ProductSuppliersView view in the INV database. Once the list is loaded, the program prints these values. To get data from the database, this example uses the executeQuery method, which returns a ResultSet. Because the names of the returned data are known, the actual column names are used to retrieve the values. Initially, the ResultSet cursor is positioned before the first row of results. Each time rs.next() is called, the ResultSet places its cursor on the next row and returns true until it reaches the end of the results. Then it returns false. Figure 3.11 illustrates what your results will look like.


Previous Table of Contents Next