The Connection
object can generate various kinds
of statements; the simplest is a Statement
created
by createStatement( )
and used to send your SQL
query as an arbitrary string:
Statement stmt = conn.createStatement( ); stmt.executeQuery("select * from myTable");
The result of the query is returned as a
ResultSet
object. The ResultSet
works like an iterator in
that it lets you access all the rows of the result that match the
query. This process is shown in Figure 20-1.
Typically, you use it like this:
while (rs.next( )) { int i = rs.getInt(1); // or getInt("UserID");
As the comment suggests, you can retrieve elements from the
ResultSet
either by their
column index (which starts at one, unlike
most Java things, which typically start at zero) or column name. In
JDBC 1, you must retrieve the values in increasing order by the order
of the SELECT (or by their column order in the database if the query
is SELECT *). In JDBC 2, you can retrieve them in any order (and in
fact, many JDBC 1 drivers don’t enforce the retrieving of
values in certain orders). If you want to learn the column names (a
sort of introspection), you can use a
ResultSet
’s getResultSetMetaData( )
method, described in Section 20.11. There
are many types of
data in SQL, and there are methods to
get them from a ResultSet
; the common ones are
shown in Table 20-3.
Table 20-3. Data type mappings between SQL and JDBC
JDBC method |
SQL type |
Java type |
---|---|---|
|
BIT |
|
|
TINYINT |
|
|
SMALLINT |
|
|
INTEGER |
|
|
BIGINT |
|
|
REAL |
|
|
DOUBLE |
|
|
CHAR |
|
|
VARCHAR |
|
|
LONGVARCHAR |
|
|
DATE |
|
|
TIME |
|
|
BLOB |
|
Assuming that we have a relational database containing the
User
data, we can retrieve it as demonstrated in
Example 20-7. This program retrieves any or all
entries that have a username of ian
and prints the
ResultSet
s in a loop. It prints lines like:
User ian is named Ian Darwin
The source code is shown in Example 20-7.
Example 20-7. UserQuery.java
import jabadot.*; import java.sql.*; import java.io.*; import java.util.*; /** Look up one use from the relational database using JDBC. */ public class UserQuery { public static void main(String[] fn) throws ClassNotFoundException, SQLException, IOException { // Load the database driver Class.forName(JDConstants.getProperty("jabadot.userdb.driver")); System.out.println("Getting Connection"); Connection conn = DriverManager.getConnection( JDConstants.getProperty("jabadot.dburl")); Statement stmt = conn.createStatement( ); ResultSet rs = stmt.executeQuery( "SELECT * from userdb where name='ian'"); // Now retrieve (all) the rows that matched the query while (rs.next( )) { // Field 1 is login name String name = rs.getString(1); // Password is field 2 - do not display. // Column 3 is fullname String fullName = rs.getString(3); System.out.println("User " + name + " is named " + fullName); } rs.close( ); // All done with that resultset stmt.close( ); // All done with that statement conn.close( ); // All done with that DB connection System.exit(0); // All done with this program. } }
Note that a ResultSet
is tied to its
Connection
object; if the
Connection
is closed, the
ResultSet
becomes invalid. You should either
extract the data from the
ResultSet
before closing it, or cache it in a
CachingRowSet
, an experimental
RowSet
subclass currently available from
Sun’s Java
web site (RowSet
is
a JDBC 2 subclass of ResultSet
).
18.119.28.108