Client 4 ”An Interactive Query ProcessorNow, 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).
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] .
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. |