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 getXXXX() methods will return if the value is NULL. The answer depends on which getXXXX() 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 0 if the value is NULL or if the value is 0. 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.
