![]() ![]() ![]() |
Start of Tutorial > Start of Trail > Start of Lesson | Search |
We now show how you send the aboveSELECT
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 classResultSet
to hold our results. The following code demonstrates declaring theResultSet
objectrs
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 ofResultSet
, 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 methodnext
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 aResultSet
object, the first call to the methodnext
moves the cursor to the first row and makes it the current row. Successive invocations of the methodnext
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 ofrs
isCOF_NAME
, which stores a value of SQL typeVARCHAR
. The method for retrieving a value of SQL typeVARCHAR
isgetString
. The second column in each row stores a value of SQL typeFLOAT
, and the method for retrieving values of that type isgetFloat
. The following code accesses the values stored in the current row ofrs
and prints a line with the name followed by three spaces and the price. Each time the methodnext
is invoked, the next row becomes the current row, and the loop continues until there are no more rows inrs
.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.99Note 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 twogetXXX
statements in this code. First let's examinegetString
.String s = rs.getString("COF_NAME");The method
getString
is invoked on theResultSet
objectrs
, sogetString
will retrieve (get) the value stored in the columnCOF_NAME
in the current row ofrs
. The value thatgetString
retrieves has been converted from an SQLVARCHAR
to aString
in the Java programming language, and it is assigned to theString
objects
. Note that we used the variables
in theprintln
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 columnPRICE
, which is an SQLFLOAT
, and converts it to a Javafloat
before assigning it to the variablen
.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), with1
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
(columnCOF_NAME
), converts it to a JavaString
object, and assigns it tos
. The second line of code gets the value stored in the second column of the current row ofrs
, converts it to a Javafloat
, and assigns it ton
. 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 methodgetInt
can be used to retrieve any of the numeric or character types. The data it retrieves will be converted to anint
; that is, if the SQL type isVARCHAR
, JDBC will attempt to parse an integer out of theVARCHAR
. The methodgetInt
is recommended for retrieving only SQLINTEGER
types, however, and it cannot be used for the SQL typesBINARY
,VARBINARY
,LONGVARBINARY
,DATE
,TIME
, orTIMESTAMP
.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 typesCHAR
andVARCHAR
, 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 withgetString
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 JavaString
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 thegetString
method.Use of ResultSet.getXXX Methods to Retrieve JDBC Types
getByte
getShort
getInt
getLong
getFloat
getDouble
getBigDecimal
getBoolean
getString
getBytes
getDate
getTime
getTimestamp
getAsciiStream
getUnicodeStream
getBinaryStream
getObject
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