Client 2—Adding Error Checking

In the previous section, I mentioned that the DriverManager.getConnection() method will throw an exception whenever it fails. Listing 13.3 shows the second JDBC client. This version is nearly identical to client1.java, except that client1, ignored any exceptions and in client2, you will intercept them and produce friendlier error messages.

Listing 13.3. client2.java

 1 //
 2 // File: client2.java
 3 //
 4
 5 import java.sql.*;
 6
 7 public class client2
 8 {
 9   public static void main( String args[] )
10   {
11     String  driver = "org.postgresql.Driver";
12     String  url    = "jdbc:postgresql:movies";
13     String  user   = "korry";
14     String  pwd    = "cows";
15
16     try 
17     {
18       Class.forName( driver );
19     }
20     catch( ClassNotFoundException e )
21     {
22       System.err.println( "Can't load driver" + e.getMessage());
23       System.exit( 1 );
24     }
25
26     try 
27     {
28       Connection con = DriverManager.getConnection(url, user, pwd);
29
30       System.out.println( "Connection attempt successful" );
31
32       con.close();
33
34     }
35     catch( Exception e )
36     {
37       System.err.println( "Connection attempt failed" );
38       System.err.println( e.getMessage());
39     }
40   }
41 }

The first difference between client1 and client2 appears at line 10. In the client1 version, you had to declare that main() could throw ClassNotFoundException and SQLException. You'll be intercepting those exceptions now, so main() should not throw any exceptions.

At lines 17 through 24, client2 wraps the call to Class.forName() in a try/catch block. Remember that forName() dynamically loads the implementation of a class into your VM—it is entirely possible that forName() may not be able to load the class file that you need. You may have misspelled the class name, or the class file might not be in your $CLASSPATH search path. You may also find that you don't have the permissions required to load the class file, or you could even find that the class file has been corrupted. If client2 catches an exception, it prints a suitable error message and exits.

After the Driver class has been loaded into your VM, you can attempt to make a connection. Wrap the connection attempt in a try/catch block so that you can intercept any exceptions. The call to DriverManager.getConnection() throws a SQLException if something goes wrong. Let's compile this application and give it a try:

$ make client2.class
javac -g client2.java
$ java client2
Connection attempt failed
Connection refused. Check that the hostname and port is correct, 
and that the postmaster is running with the -i flag, which 
enables TCP/IP networking.
$

I haven't restarted the postmaster yet, so I encounter the same error as before, but this time the error message is less intimidating.

DriverManager.getConnection() can throw two kinds of exceptions: SQLException and PSQLException (PSQLException is derived from SQLException). PSQLExceptions are specific to the PostgreSQL driver; SQLExceptions indicate errors that might be common to many drivers. Let's modify client2.java so that you can see which type of exception you catch. The new client is shown in Listing 13.4.

Listing 13.4. client2a.java

 1 //
 2 // File: client2a.java
 3 //
 4
 5 import java.sql.*;
 6 import org.postgresql.util.PSQLException;
 7
 8 public class client2a
 9 {
10   public static void main( String args[] )
11   {
12     String  driver = "org.postgresql.Driver";
13
14     try 
15     {
16       Class.forName( driver );
17     }
18     catch( ClassNotFoundException e )
19     {
20       System.err.println( "Can't load driver " + e.getMessage());
21       System.exit( 1 );
22     }
23     catch( Exception e )
24     {
25       System.err.println( "Can't load driver " + e.toString());
26       System.exit( 1 );
27     }
28
29     try 
30     {
31       Connection con = DriverManager.getConnection( args[0] );
32
33       System.out.println( "Connection attempt successful" );
34
35       con.close();
36
37     }
38     catch( PSQLException e)
39     {
40       System.err.println( "Connection failed(PSQLException)" );
41       System.err.println( e.getMessage());
42     }
43     catch( SQLException e )
44     {
45       System.err.println( "Connection failed(SQLException)" );
46       System.err.println( e.getMessage());
47     }
48   }
49 }

I've made a few minor changes in client2a.java. First off, you want to distinguish between SQLException and PSQLException, so at line 6, import the appropriate package. I've also removed most of the String variables used in the previous version. In this version, you supply a URL on the command line rather than hard-coding the connection parameters. At line 31, client2a calls a different flavor of the getConnection() method; this one expects a single argument (the URL to which you want to connect). Notice that I have removed the hard-coded URL from this client. When you invoke client2a, you provide a connection URL on the command line (see the next example). Finally, client2a catches PSQLException explicitly.

Compile this client and reproduce the same error that you saw earlier:

$ make client2a.class
javac -g client2a.java

$ java client2a "jdbc:postgresql:movies?user=korry&password=cows"
Connection failed(PSQLException)
Connection refused. Check that the hostname and port is correct, 
and that the postmaster is running with the -i flag, which 
enables TCP/IP networking.
$

Okay, that message comes from a PSQLException. Now, let's restart the postmaster and try connecting with an invalid password:

$ pg_ctl start -l /tmp/pg.log -o -i
postmaster successfully started
$ java client2a "jdbc:postgresql:movies?user=korry&password=oxen"
Connection failed(PSQLException)
Something unusual has occurred to cause the driver to fail.
Please report this exception:
Exception: java.sql.SQLException:
  FATAL 1:  Password authentication failed for user "korry"

Stack Trace:

java.sql.SQLException: FATAL 1:  Password authentication failed 
for user "korry"

        at org.postgresql.Connection.openConnection(Unknown Source)
        at org.postgresql.Driver.connect(Unknown Source)
        at java.sql.DriverManager.getConnection(DriverManager.java:517)
        at java.sql.DriverManager.getConnection(DriverManager.java:199)
        at client2a.main(client2a.java:31)
End of Stack Trace

We're back to the intimidating error messages again. This is still a PSQLException, but the PostgreSQL JDBC Driver feels that an invalid password is unusual enough to justify this kind of error. You can see the importance of catching exceptions—you may want to translate this sort of message into something a little less enthusiastic rather than attacking your users with the raw error message text, as we've done here.

It's a little harder to generate a SQLException when the only thing you are doing is connecting and disconnecting. If you try hard enough, you can break just about anything:

$ java client2a "jdbc:postgres:movies?user=korry&password=cows" 
Connection failed(SQLException)
No suitable driver

In this example, I've misspelled the subprotocol portion of the connection URL (postgres should be postgresql).

JNDI and the DataSource Class

So far, all of the JDBC client applications that you've seen in this chapter connect to a PostgreSQL server using a URL. Every time you connect to the server, you have to craft a URL that contains all of the connection properties required by the server. Creating a URL by hand doesn't seem too onerous if you're writing one or two client applications that interact with a single database, but it can become quite burdensome as you add more clients and more databases.

Fortunately, Java provides a better way to scale JDBC applications: the DataSource. If you've read through Chapter 12, “Using PostgreSQL from an ODBC Client Application,” you're already familiar with the concept of a DataSource. A DataSource is a named collection of connection properties (and you get to choose the name). In an ODBC application, each DataSource is stored in the Windows registry (or in /etc/odbc.ini for Linux/Unix hosts). In a JDBC application, a DataSource is typically stored in a JNDI (Java Naming and Directory Interface ) repository. The JNDI package is an interface that maps a name into an object. Using JNDI, you can store DataSource objects in an LDAP directory or a Java RMI registry (or any JNDI-enabled repository).

When you connect to a PostgreSQL server using a DataSource (as opposed to a URL), you typically ask the JNDI (the Java Naming and Directory Interface) to create the DataSource object for you. The JNDI searches through its repository to find a DataSource object whose name matches the name that you provide. If the JNDI finds such an object, it creates a Java DataSource object and returns the object to you. The DataSource object contains all of the properties required to connect to a PostgreSQL server. In fact, the DataSource object can connect to the server on your behalf and return a reference to a Connection object. For example, to connect to a DataSource named accounting, you might write code similar to the following:

...
    DataSource acctg = (DataSource) new InitialContext().lookup("accounting");
    Connection conn  = acctg.getConnection();
 ...

When you invoke InitialContext().lookup("accounting"), the JNDI starts searching its repository (which may be an LDAP directory, an RMI registry, or some other JNDI service provider) for an object with the name accounting. If the JNDI can't find such an object, it throws a NameNotFound exception. If the lookup() method does find an object with that name, it returns a copy of that object. The call to acctg.getConnection() tries to connect to the PostgreSQL server using the connection properties defined in the DataSource.

In between the call to lookup() and the call to getConnection(), you may want to modify the properties in your copy of the DataSource object. For example, you may not want to store a plain-text password in an insecure repository. Instead, you can create an incomplete DataSource in the repository and set the password property in your copy. You can modify any of the DataSource properties shown in Table 13.2.

Now that you know how to connect to a PostgreSQL database (using a URL or a DataSource), I'll show you how to execute a PostgreSQL command from a JDBC application. I'll also explain how to retrieve data from a ResultSet and how to find the metadata exposed by the server.