|
|
Start of Tutorial > Start of Trail > Start of Lesson |
Search
Feedback Form |
We now show how you send the aboveSELECTstatements from a program written in the Java programming language and how you get the results we showed.JDBC returns results in a
ResultSetobject, so we need to declare an instance of the classResultSetto hold our results. The following code demonstrates declaring theResultSetobjectrsand 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 methodnextmoves 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 aResultSetobject, the first call to the methodnextmoves the cursor to the first row and makes it the current row. Successive invocations of the methodnextmove 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
getXXXmethod of the appropriate type to retrieve the value in each column. For example, the first column in each row ofrsisCOF_NAME, which stores a value of SQL typeVARCHAR. The method for retrieving a value of SQL typeVARCHARisgetString. 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 ofrsand prints a line with the name followed by three spaces and the price. Each time the methodnextis 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
getXXXmethods work by examining the twogetXXXstatements in this code. First let's examinegetString.String s = rs.getString("COF_NAME");The method
getStringis invoked on theResultSetobjectrs, sogetStringwill retrieve (get) the value stored in the columnCOF_NAMEin the current row ofrs. The value thatgetStringretrieves has been converted from an SQLVARCHARto aStringin the Java programming language, and it is assigned to theStringobjects. Note that we used the variablesin theprintlnexpression shown above, that is,println(s + " " + n).The situation is similar with the method
getFloatexcept that it retrieves the value stored in the columnPRICE, which is an SQLFLOAT, and converts it to a Javafloatbefore assigning it to the variablen.JDBC offers two ways to identify the column from which a
getXXXmethod 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), with1signifying 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 JavaStringobject, 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
getXXXmethod. 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
getXXXmethods you can use to retrieve the different SQL types. For example, the methodgetIntcan 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 methodgetIntis recommended for retrieving only SQLINTEGERtypes, 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
getStringis recommended for retrieving the SQL typesCHARandVARCHAR, 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 withgetStringcan be very useful, but it also has its limitations. For instance, if it is used to retrieve a numeric type,getStringwill convert the numeric value to a JavaStringobject, 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 thegetStringmethod.Use of ResultSet.getXXX Methods to Retrieve JDBC Types
Note: If you have trouble reading this table, see Use of ResultSet.getXXX: Table-Free Versionsfor alternate views of the same information.
getByte
getShort
getInt
getLong
getFloat
getDouble
getBigDecimal
getBoolean
getString
getBytes
getDate
getTime
getTimestamp
getAsciiStream
getUnicodeStream
getBinaryStream
getObjectAn "x" indicates that the
getXXXmethod may legally be used to retrieve the given JDBC type.An " X " indicates that the
getXXXmethod is recommended for retrieving the given JDBC type.![]()
![]()
![]()
Start of Tutorial > Start of Trail > Start of Lesson Search
Feedback FormCopyright 1995-2004 Sun Microsystems, Inc. All rights reserved.