Client 4 - An Interactive Query Processor

Client 4 ”An Interactive Query Processor

Now, let's move on to the final JDBC client. As in previous chapters, we'll wrap up by looking at an application that processes arbitrary commands entered by the user .

Listing 13.9 shows the client4.main() method.

Listing 13.9 client4.java (Part 1)

 1 //  2 //  File: client4.java  3 //  4  5 import java.sql.*;  6 import java.io.*;  7  8 public class client4  9 { 10   static String blanks = "                                   "; 11   static String dashes = "-----------------------------------"; 12 13   public static void main( String args[] ) 14     throws SQLException 15   { 16     Class driverClass = loadDriver( "org.postgresql.Driver" ); 17 18     if( driverClass == null ) 19       return; 20 21     if( args.length != 1 ) 22     { 23       System.err.println( "usage: java client4 <url>" ); 24       return; 25     } 26 27     Connection con = connectURL( args[0] ); 28 29     if( con != null ) 30     { 31       DatabaseMetaData dbmd = con.getMetaData(); 32 33       System.out.print( "Connected to " ); 34       System.out.print( dbmd.getDatabaseProductName()); 35       System.out.println( " " + dbmd.getDatabaseProductVersion()); 36 37       processCommands( con ); 38 39       con.close(); 40     } 41   } 

client4.main() is similar to client3.main() ; you load the PostgreSQL driver and then connect to the database using the URL provided by the user. At line 31, you obtain a DatabaseMetaData object, so you can print a welcome message that includes the product name and version.

main() finishes by calling processCommands() . Now, let's look at the processCommands() method (Listing 13.10).

Listing 13.10 client4.java (Part 2)

 43   static void processCommands( Connection con ) 44   { 45     try 46     { 47       Statement      stmt = con.createStatement(); 48       String         cmd  = ""; 49       BufferedReader in; 50 51       in = new BufferedReader( new InputStreamReader( System.in )); 52 53       while( true ) 54       { 55         System.out.print( "--> "); 56 57         cmd = in.readLine(); 58 59         if( cmd == null ) 60             break; 61 62         if( cmd.equalsIgnoreCase( "quit" )) 63           break; 64 65         processCommand( stmt, cmd ); 66 67       } 68 69       System.out.println( "bye" ); 70 71     } 72     catch( Exception e ) 73     { 74         System.err.println( e ); 75     } 76   } 

The processCommands() method prompts the user for a command and then executes that command. Because this is not a graphical application, you need a way to read input from the user. Java's BufferedReader class lets you read user input one line at a time, so you create a new BufferedReader object at line 51.

Lines 53 through 67 comprise the main processing loop in this application. At the top of the loop, you print a prompt string and then read the user's response using BufferedReader 's readline() method.

Three things can cause you to break out of this loop. First, one of the methods that you call can throw an exception. You catch exceptions at line 72 and simply print any error message contained in the exception. Next , the user can close the input stream (usually by pressing Ctrl+D). In that case, readline() returns a null String reference and you break out of the loop at line 60. Finally, you break out of this loop if the user enters the string quit .

When you reach line 65, you call the processCommand() method to execute a single command. Listing 13.11 shows the processCommand() method.

Listing 13.11 client4.java (Part 3)

 78   static void processCommand( Statement stmt, String cmd ) 79     throws SQLException 80   { 81 82     if( stmt.execute( cmd )) 83         printResultSet( stmt.getResultSet()); 84     else 85     { 86       int count = stmt.getUpdateCount(); 87 88       if( count == -1 ) 89         System.out.println( "No results returned" ); 90       else 91         System.out.println( "(" + count + " rows)" ); 92     } 93   } 

The processCommand() method is a little difficult to understand at first. Here's some background information that might help.

There are three ^[9] ways to execute a command using a Statement object. I've used the executeQuery() method in most of the examples in this chapter. Calling executeQuery() is only appropriate if you know that you are executing a SELECT command. executeQuery() returns a ResultSet . If you know that you are executing some other type of command (such as CREATE TABLE , INSERT , or UPDATE ), you should use the executeUpdate() method instead of executeQuery() . executeUpdate() returns the number of rows affected by the command (or 0 for DDL commands).

^[9] Actually, there is a fourth way to execute a SQL command. You can call the addBatch() method repeatedly to build up a batch of commands, and then execute the whole batch using executeBatch() .

If you don't know whether you are executing a query or a command, which is the case in this client, you can call the execute() method. execute() returns a Boolean value: true means that the command returned a result set; false means that the command returned the number of rows affected by the command (or 0 for DDL commands) ^[10] .

^[10] This is not entirely accurate. Some JDBC drivers (but not the PostgreSQL driver) can execute multiple commands in a single call to execute() . In that case, the return code from execute() indicates the type of the first result. To get subsequent results, you call the getMoreResults() method. See the JDBC documentation for more information.

Because you don't know what kind of command the user entered, you use execute() . If the command returns a result set (that is, if execute() returns true ), you call printResultSet() to display the results. If the command does not return a result set, you have to call getUpdateCount() to determine whether the command modified any rows. Note that the 7.2 version of the PostgreSQL JDBC driver seems to contain a small bug: the getUpdateCount() method returns 1, even for commands such as CREATE TABLE , GRANT , and CREATE INDEX .

Now let's look at the methods that display result sets to the user. The first one is pad() , shown in Listing 13.12.

Listing 13.12 client4.java (Part 4)

 95   static String pad( String in, int len, String fill )  96   {  97       String result = in;  98  99       len -= in.length(); 100 101       while( len > 0  ) 102       { 103           int l; 104 105           if( len > fill.length()) 106               l = fill.length(); 107           else 108               l = len; 109 110         result = result + fill.substring( 0, l ); 111 112         len -= l; 113       } 114 115       return( result ); 116   } 

The pad() method is a helper method used by printResultSet() . It returns a string padded with fill characters to the given length.

Next, let's look at the printResultSet() method, shown in Listing 13.13.

Listing 13.13 client4.java (Part 5)

 118   static void printResultSet( ResultSet rs ) 119     throws SQLException 120   { 121     int[]             sizes; 122     ResultSetMetaData rsmd     = rs.getMetaData(); 123     int               colCount = rsmd.getColumnCount(); 124     int               rowCount = 0; 125 126     sizes = new int[colCount+1]; 127 128     // 129     // Compute column widths 130     // 131     while( rs.next()) 132     { 133       rowCount++; 134 135       for( int i = 1; i <= colCount; i++ ) 136       { 137         String val = rs.getString(i); 138 139         if(( rs.wasNull() == false ) && ( val.length() > sizes[i] )) 140           sizes[i] = val.length(); 141       } 142     } 143 144     // 145     // Print column headers 146     // 147     for( int i = 1; i <= colCount; i++ ) 148     { 149       if( rsmd.getColumnLabel(i).length() > sizes[i] ) 150         sizes[i] = rsmd.getColumnLabel(i).length(); 151 152       System.out.print( pad( rsmd.getColumnLabel( i ), 153                              sizes[i], 154                              blanks )); 155 156       if( i < colCount ) 157         System.out.print( "  " ); 158       else 159         System.out.println(); 160     } 161 162     for( int i = 1; i <= colCount; i++ ) 163     { 164       if( i < colCount ) 165         System.out.print( pad( "", sizes[i], dashes ) + "-+-" ); 166       else 167         System.out.println( pad( "", sizes[i], dashes )); 168     } 169 170     // 171     //  Rewind the result set and print the contents 172     // 173     rs.beforeFirst(); 174 175     while( rs.next()) 176     { 177       for( int i = 1; i <= colCount; i++ ) 178       { 179         String val = rs.getString(i); 180 181         if( rs.wasNull()) 182           val = ""; 183 184         if( i < colCount ) 185           System.out.print( pad( val, sizes[i], blanks ) + "  " ); 186         else 187           System.out.println( pad( val, sizes[i], blanks )); 188       } 189     } 190   } 

The printResultSet() method is easily the most complex method in this application.

Start by computing the width of each column header. Each column is as wide as the widest value in that column. You have to read through the entire result set to find the widest value. At lines 147 through 168, print the column headers. If getColumnLabel() returns a string longer than the widest value in the column, adjust the width to accommodate the label.

After you have printed the column headers, you have to rewind the result set so that you are positioned just before the first row. Remember, you processed the entire result set earlier when you were computing column widths.

The loop covering lines 175 through 189 processes every row in the result set. For each column in the result set, you retrieve the value in String form. Line 181 shows an oddity in the JDBC package: There is no way to determine whether a value is NULL without first retrieving that value. So, first call rs.getString() to retrieve a column from the current row and then call rs.wasNull() to detect NULL values. You may be wondering what the get XXXX () methods will return if the value is NULL . The answer depends on which get XXXX () method you call. In this chapter, you have retrieved most result values in the form of a Java String , but you can also ask for values to be returned in other data types. getString() returns a null reference if the column value is NULL . getBoolean() will return false if the column value is NULL . Of course, getBoolean() will also return false if the column value is false . Likewise, getInt() returns if the value is NULL or if the value is . You must call wasNull() to detect NULL values.

After you have detected NULL values, print the result, padded to the width of the column.

The last two methods in client4.java are identical to those included in client3.java . loadDriver() is shown in Listing 13.14.

Listing 13.14 client4.java (Part 6)

 192   static Class loadDriver( String driverName ) 193   { 194     try 195     { 196       return( Class.forName( driverName )); 197     } 198     catch( ClassNotFoundException e ) 199     { 200       System.err.println( "Can't load driver - " + e.getMessage()); 201       return( null ); 202     } 203   } 204 205   static Connection connectURL( String URL ) 206   { 207     try 208     { 209       return( DriverManager.getConnection( URL )); 210     } 211     catch( SQLException e ) 212     { 213       System.err.println( "Can't connect - " + e.getMessage()); 214       return( null ); 215     } 216   } 217 } 

The loadDriver() method tries to load the named JDBC driver, and connectURL() attempts to connect to the given JDBC URL.

Now, let's compile and run this application:

 $ make client4.class javac -g client4.java $ java client4 "jdbc:postgresql:movies?user=korry&password=cows" Connected to PostgreSQL 7.2.1 --> SELECT * FROM tapes tape_id   title ---------+-------------- AB-12345  The Godfather AB-67472  The Godfather MC-68873  Casablanca OW-41221  Citizen Kane AH-54706  Rear Window --> SELECT * FROM customers id  customer_name         phone     birth_date ---+----------------------+----------+----------- 1   Jones, Henry          555-1212  1970-10-10 2   Rubin, William        555-2211  1972-07-10 3   Panky, Henry          555-1221  1968-01-21 4   Wonderland, Alice N.  555-1122  1969-03-05 5   Funkmaster, Freddy    555-FUNK  7   Gull, Jonathan LC     555-1111  1984-02-05 8   Grumby, Jonas         555-2222  1984-02-21 

Now, I'd like to show you a problem with this application:

 --> SELECT * FROM tapes; SELECT * FROM customers Cannot handle multiple result groups. 

In this example, I tried to execute two SQL commands on one line. As the message suggests, the PostgreSQL JDBC driver cannot handle multiple result groups (this message comes from an exception thrown by the PostgreSQL driver). Note that this is not a limitation of the JDBC package, but of this particular driver. The PostgreSQL source distribution includes an example application ( src/interfaces/jdbc/example/psql.java ) that gets around this problem by parsing user input into individual commands.