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
figs/jcb2_2001.gif


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).



Java Cookbook
Java Cookbook, Second Edition
ISBN: 0596007019
EAN: 2147483647
Year: 2003
Pages: 409
Authors: Ian F Darwin

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net