Client 3 - Processing Queries

   

Client 3 ”Processing Queries

The next client executes a hard-coded query, intercepts any errors, and prints the result set. I've factored most of the code into separate methods to make it easier to follow. Listing 13.5 shows client3.java .

Listing 13.5 client3.java (Part 1)
 1 //  2 //  File: client3.java  3 //  4  5 import java.sql.*;  6  7 public class client3  8 {  9   public static void main( String args[] ) 10   { 11     Class driverClass = loadDriver( "org.postgresql.Driver" ); 12 13     if( driverClass == null ) 14       return; 15 16     if( args.length != 1 ) 17     { 18       System.err.println( "usage: java client3 <url>" ); 19       return; 20     } 21 22     Connection con = connectURL( args[0] ); 23 24     if( con != null ) 25     { 26       ResultSet result = execQuery( con, "SELECT * FROM tapes;" ); 27 28       if( result != null ) 29         printResults( result ); 30     } 31   } 

The main() method for client3 should be much easier to read now that the details have been factored out (see Listing 13.6). Start by loading the Driver class. If that fails, the call to loadDriver() will print an error message and you exit. Next, verify that the user provided a URL on the command line, and connect to the database using that URL. If the connection succeeds, execute a hard-coded query and print the result set.

Listing 13.6 client3.java (Part 2)
 33   static Class loadDriver( String driverName ) 34   { 35     try 36     { 37       return( Class.forName( driverName )); 38     } 39     catch( ClassNotFoundException e ) 40     { 41       System.err.println( "Can't load driver - " + e.getMessage()); 42       return( null ); 43     } 44   } 45 46   static Connection connectURL( String URL ) 47   { 48     try 49     { 50       return( DriverManager.getConnection( URL )); 51     } 52     catch( SQLException e ) 53     { 54       System.err.println( "Can't connect - " + e.getMessage()); 55       return( null ); 56     } 57   } 

You should be familiar with most of the code in the loadDriver() and connectURL() methods [6] .

[6] These methods show a personal design preference. I try to intercept exceptions as early as possible rather than throwing them back up the call stack. I find the resulting mainline code a little easier to read without the try/catch blocks.

In loadDriver() , you use Class.forName() to load the named Driver into your VM. If the load is successful, you return the Class object for the Driver ; otherwise , you print an error message and return null to inform the caller that something went wrong.

The connectURL() method is similar in structure. It attempts to connect to the requested URL, returning a Connection object or null if the connection attempt fails (see Listing 13.7).

Listing 13.7 client3.java (Part 3)
 59   static ResultSet execQuery( Connection con, String query ) 60   { 61     try 62     { 63       Statement stmt = con.createStatement(); 64 65       System.out.println( query ); 66 67       return( stmt.executeQuery( query )); 68     } 69     catch( SQLException e ) 70     { 71       System.err.println( "Query failed - " + e.getMessage()); 72       return( null ); 73     } 74 } 

execQuery() shows how to execute a query using JDBC. When this method is invoked, the caller gives you a Connection . Before you can execute a query, you have to create a Statement object. A Statement object gives you a way to send a command to the server. After the command has been sent to the server, you can ask the Statement for a ResultSet . Some database servers (PostgreSQL included) support multiple Statement objects for each Connection . This means that you can execute multiple commands and process the results concurrently.

The Statement.executeQuery() method throws a SQLException if something (a syntax error, for example) goes wrong.

If the call to executeQuery() succeeds, you return the ResultSet to the caller, which passes it to printResults() to be displayed to the user.

The final method (see Listing 13.8) in this application is printResults() .

Listing 13.8 client3.java (Part 4)
 76   static void printResults( ResultSet res ) 77   { 78     System.out.println( " tape_id  title" ); 79     System.out.println( "---------+--------------------------" ); 80 81     try 82     { 83       while( res.next()) 84       { 85         System.out.print( res.getString( 1 )); 86         System.out.print( "  "); 87         System.out.print( res.getString( 2 )); 88         System.out.println( "" ); 89       } 90     } 91     catch( SQLException e ) 92     { 93       System.err.println( "Fetch failed: " + e.getMessage()); 94     } 95   } 96 } 

The printResults() method fetches every row in the given ResultSet and prints each column. Lines 78 and 79 print the column headings for the result set. Because you are working with a hard-coded query in this client, you can take a few shortcuts ”if you didn't know the shape of the result set, you would have to interrogate the metadata for this ResultSet to find the column headings. You'll do that in the next client ( client4.java ).

The loop at lines 83 through 89 iterates through each row in the result set. Each ResultSet maintains a pointer [7] to the current row. ResultSet offers a number of methods for navigating through a result set. The ResultSet.next() method moves you forward through the result set. Table 13.1 lists all the navigation methods.

[7] The JDBC documentation refers to this pointer as a cursor ; to avoid confusion with database cursors (a similar concept), I'll use the term pointer .

Table 13.1. ResultSet Navigation Methods

Navigation Method

Related Accessor Method

Description

absolute( n )

getRow()

Moves to the n th row in the result set if n is positive or to the last n row if n is negative

afterLast()

isAfterLast()

Moves past the last row in the result set

beforeFirst()

isBeforeFirst()

Moves to just before the first row

first()

isFirst()

Moves to the first row

last()

isLast()

Moves to the last row

next()

getRow()

Moves to the next row

previous()

getRow()

Moves to the previous row

relative( n )

getRow()

Moves forward n rows if n is positive or back n rows if n is negative

The first column in Table 13.1 lists the methods you can call to move through the result set. Each entry in the second column shows the related accessor method. The isAfterLast() , isBeforeLast() , isFirst() , and isLast() methods return true or false to indicate whether you are pointed to the named position within the result set. The getRow() function returns the current row number with the result set.

first() differs from beforeFirst() in that you can retrieve column values if you are positioned on the first row, but not if you are positioned before the first row. Similarly, you can retrieve column values if you are positioned on the last row, but not if you are positioned after the last row.

You use the ResultSet.getString() method to retrieve a column from the current row. When you call getString() , you provide an integer argument that specifies which column you are interested in; column numbers start at 1.

After printing the two column values, you continue looping until res.next() returns false (meaning that there are no more rows in the result set).

This example shows that it's easy to process a query and a result set using JDBC. Now, let's go back and fill in a few of the details that I avoided.

Statement Classes

In client3.java, you used the Statement.executeQuery() method to execute a query. Statement is one of three interfaces that you can use to execute a SQL command. Statement is the most general interface and can be used to execute any SQL command. Let's look at the other Statement interfaces.

PreparedStatement

The PreparedStatement interface provides a way to precompile a command and execute it later. PreparedStatement inherits from (extends) Statement , so anything that you can do with a Statement , you can also do with a PreparedStatement . If you read the previous chapter, you may recognize PreparedStatement as the JDBC implementation of the ODBC Prepare/Execute execution model. When you use a PreparedStatement , you can parameterize your SQL commands. Let's say you are writing an application that repeatedly queries the tapes table, providing a different tape_id for each query. Rather than constructing a new command for each query, you can create a PreparedStatement like this:

 ... PreparedStatement stmt; stmt = con.prepareStatment( "SELECT * FROM tapes WHERE tape_id = ?" ); ... 

Notice that the text of this query doesn't specify an actual tape_id in the WHERE clause; instead, you include a parameter marker ( ? ). Using a parameter marker, you can substitute different values each time you execute the PreparedStatement . You can include as many parameter markers as you like [8] .

[8] The JDBC documentation suggests that you can include a parameter marker anywhere within a SQL command. For example, the following command is allowed SELECT ? FROM customers , implying that you could substitute a list of column names at runtime. I recommend that you only use parameter markers where values are expected (and use one marker for each value). The PostgreSQL driver and many other drivers will not function correctly if you try to use a parameter marker in a context in which a value is not allowed.

The PreparedStatement object returned by prepareStatement() can be executed many times. Each time you execute the query, you can provide a different substitution value for each parameter marker. For example, to substitute a tape_id value in the previous query:

 ... PreparedStatement stmt; stmt = con.prepareStatment( "SELECT * FROM tapes WHERE tape_id = ?" ); stmt.setString( 1, "AA-55281" ); ResultString result = stmt.executeQuery(); ... 

The call to setString() substitutes the value "AA-55281" in place of the first parameter marker (parameter markers are numbered starting with 1 ). The net effect is that executeQuery() executes the string "SELECT * FROM tapes WHERE tape_id = 'AA-55281'" . Notice that setString() automatically includes the single quotes required around a string literal, so you don't have to include them in the string.

PreparedStatement supports a number of parameter-substitution methods. We've used the setString() method in this example, but there are also methods for setting Boolean values ( setBoolean() ), numeric values ( setInt() , setFloat() , setDouble() , setLong() , setBigDecimal() ), temporal values ( setDate() , setTime() , setTimestamp() ), large objects ( setBlob() , setClob() ), and generic objects ( setObject() ). Each of these methods expect a parameter number and a value of the appropriate type. You can use the setNull() method to substitute a null value.

Each time you execute a PreparedStatement , you can substitute new values for some or all the parameter markers. If you don't supply a new value for a given marker, the previous value is retained.

Why would you want to use a PreparedStatement instead of a Statement ? The Prepare/Execute model makes it easy to factor the code required to generate a command into a separate method. You may also experience a performance boost by preparing a command and then reusing it many times. The current version of the PostgreSQL JDBC driver will not show increased performance using the Prepare/Execute model, but other drivers (for other databases) will. It is also possible that a future release of PostgreSQL will provide complete support for this execution model.

CallableStatement

The CallableStatement interface inherits from PreparedStatement , so anything that you can do with a PreparedStatement , you can also do with a CallableStatement . The CallableStatement provides a way to call a function or stored-procedure using a database-independent syntax.

The following code fragment illustrates CallableStatement :

 ... CallableStatement stmt; boolean          result; stmt = con.prepareCall( "{?= call has_table_privilege(?,?)}"); stmt.registerOutParameter( 1, Types.BIT ); stmt.setString( 2, "customers" ); stmt.setString( 3, "UPDATE" ); stmt.execute(); result = stmt.getBoolean( 1 ); ... 

This example calls PostgreSQL's has_table_privilege() function. has_table_ privilege() expects two parameters: a table name and an access type. It returns a Boolean value that indicates whether the current user holds the given privilege on the named table. The query string contains three parameter markers. The first marker tells JDBC that the function that you want to call will return a value. The second and third markers specify the IN parameters. Each function parameter can be an input value ( IN ), a return value ( OUT ), or both ( IN/OUT ).

Before you can execute the CallableStatement , you use the setString() method (inherited from PreparedStatement ) to substitute the two input parameters. You also have to tell JDBC about the type of all OUT parameters; the call to registerOutParameter() does that for you. After executing the statement, you can retrieve the result using getBoolean() .

Metadata

Metadata is another issue that I glossed over in describing client3. There are two types of metadata that you can retrieve using JDBC: database metadata and result set metadata.

The DatabaseMetaData interface provides information about the database at the other end of a Connection . To access a DatabaseMetaData object, you call the Connection.getMetaData() method. Here is a snippet that shows how to retrieve the JDBC driver name and version information:

 ... Connection       con  = DriverManager.getConnection( args[0] ); DatabaseMetaData dbmd = con.getMetaData(); System.out.println( "Driver name:    " + dbmd.getDriverName()); System.out.println( "Driver version: " + dbmd.getDriverVersion()); ... 

At last count, DatabaseMetaData exposes more than 120 items of database information. The sample source code for this chapter (http://www.conjectrix.com/pgbook/jdbc) includes an application ( printMetaData ) that displays most of the metadata exposed by DatabaseMetaData .

In most applications, you will probably be more interested in the other type of metadata. The ResultSetMetaData interface exposes information about the data contained within a result set. You obtain a ResultSetMetaData object by calling the ResultSet.getMetaData() method. For example:

 ... ResultSet          rs   = stmt.executeQuery(); ResultSetMetaData  rsmd = rs.getMetaData(); ... 

After you have a ResultSetMetaData object, you can query it for all sorts of information. The getcolumnCount() method returns the number of columns in the result set. Because all ResultSetMetaData methods (except getColumnCount() ) return information about a given column, you will probably want to process meta data in a loop:

 ... int   colCount = rsmd.getColumnCount(); for( int column = 1; column <= colCount; column++ ) {   System.out.println( "Column #" + column );   System.out.println( "  Name: " + rsmd.getColumnName( column ));   System.out.println( "  Type: " + rsmd.getTypeName( column )); } ... 

This code snippet uses getColumnName() to retrieve the name of each column and getTypeName() to retrieve the type of each column.

   


PostgreSQL
PostgreSQL (2nd Edition)
ISBN: 0672327562
EAN: 2147483647
Year: 2005
Pages: 220
Authors: Korry Douglas

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