Recipe 20.6 Sending a JDBC Query and Getting Results

Problem

You're getting tired of all this setup and want to see results.

Solution

Get a Statement and use it to execute a query. You'll get a set of results, a ResultSet object.

Discussion

The Connection object can generate various kinds of statements. The simplest is a Statement created by createStatement( ) , which is 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.

Figure 20-1. ResultSet illustrated

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 Recipe 20.12. SQL handles many types of data, and JDBC offers corresponding 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
getBit( )	BIT	boolean
getByte( )	TINYINT	byte
getShort( )	SMALLINT	short
getInt( )	INTEGER	int
getLong( )	BIGINT	long
getFloat( )	REAL	float
getDouble( )	DOUBLE	double
getString( )	CHAR	String
getString( )	VARCHAR	String
getString( )	LONGVARCHAR	String
getDate( )	DATE	java.sql.Date
getTimeStamp( )	TIME	java.sql.Date
getObject( )	BLOB	Object

Assuming that we have a relational database containing the User data, we can retrieve it as demonstrated in Example 20-8. This program retrieves any or all entries that have a username of ian and prints the ResultSets in a loop. It prints lines like:

User ian is named Ian Darwin

The source code is shown in Example 20-8.

Example 20-8. 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 CachedRowSet (for more on RowSets, see Recipe 20.10).