Client 1—Connecting to the Server

Before you can connect to a database, you have to tell JDBC to which server you want to connect. JDBC uses a paradigm that you are undoubtedly already familiar with: A database is identified using a URL (Uniform Resource Locator). Every time you use your web browser, you use URLs.

A URL is composed of at least two parts, sometimes more. For example, the URL http://www.postgresql.org has two components. The http part specifies the protocol to use (in this case, hypertext transport protocol). Everything following the colon is used by the protocol to find the resource you want.

JDBC URLs

A JDBC URL is composed of three parts. We'll be using the URL jdbc:postgresql:movies in many of the examples for this chapter.

The protocol component for a JDBC URL is always jdbc. Following the protocol (and the : delimiter), is the subprotocol. The subprotocol is usually the name of a JDBC driver, but it can also identify a naming service that will provide a specific name, given an alias^[2]. In the case of PostgreSQL, the subprotocol is postgresql. Finally, you can include a string that identifies a specific data source that the driver should use (Sun's JDBC documentation calls this the subname). In our example, the subname is movies. The format of the subname string is determined by the author of the JDBC driver. In the case of the PostgreSQL JDBC driver, the URL can take any of the following forms:

^[2] See the JDBC documentation for more information about naming services.

jdbc:postgresql:database
jdbc:postgresql://host/database
jdbc:postgresql://host:port/database
jdbc:org.postgresql://host:port/database?param1=val1&...

If you don't provide a port, the standard PostgreSQL port (5432) is assumed. Notice that in all cases, you must provide the database name. Unlike the other PostgreSQL APIs, JDBC will not look for any environment variables when you omit required connection parameters, so you must include the database name in the URL. In the last form, you can include other connection parameters. For example:

jdbc:org:postgresql?user=korry&password=cows

You can include any of the parameters shown in Table 13.1 after the question mark in the URL:

user=user-name
password=password
loglevel={0|1|2}

Listing 13.2 shows a simple JDBC client application. This application connects to a database (using a URL), prints a completion message, disconnects, and then exits.

Listing 13.2. client1.java

 1 //
 2 // File: client1.java
 3 //
 4
 5 import java.sql.*;
 6
 7 public class client1
 8 {
 9   public static void main( String args[] )
10     throws ClassNotFoundException, SQLException
11   {
12     String  driver = "org.postgresql.Driver";
13     String  url    = "jdbc:postgresql:movies";
14     String  user   = "korry";
15     String  pwd    = "cows";
16
17     Class.forName( driver );
18
19     Connection con = DriverManager.getConnection( url, user, pwd );
20
21     System.err.println( "Connection complete" );
22
23     con.close();
24
25   }
26 }

At line 5, client1.java imports the java.sql package. Most of the JDBC interface is defined in this package, with a few extensions residing in the javax.sql package^[3]. I won't do any error checking in this client, so you have to declare that the main() method can throw two exceptions (at line 10). In the next client application (client2.java), I'll show you how to intercept these exceptions and handle them a bit more gracefully.

^[3] The javax.sql package was an optional feature introduced in the JDBC 2.0 specification. In the JDBC 3.0 specification, javax.sql has been moved from the JDBC 2.0 Optional Package (included in the J2EE) into J2SE.

Lines 12 through 15 define a few String objects that should make the code more descriptive. The driver string tells the JVM the fully qualified name of the driver class. The JDBC driver distributed with PostgreSQL is named org.postgresql.Driver^[4]. The url string specifies the URL to which you want to connect.

^[4] If you use a JDBC driver obtained from another source, the driver name will be different. For example, the PostgreSQL driver from the jxDBCon project is named org.sourceforge.jxdbcon.JXDBConDriver.

The user and pwd (password) strings will be passed to the DriverManager and then to the Driver when you actually get around to making a connection attempt.

Line 17 loads the PostgreSQL Driver class. A lot of things happen with this simple method call. First, the Class.forName()^[5] method locates and loads the object file that implements the org.postgresql.Driver class. Normally, a reference to another class is compiled into your class. Using Class.forName(), you can dynamically load classes into your VM at runtime. This is roughly equivalent to

^[5] In some versions of Java, you may need to call Class.forName().newInstance() to load the driver correctly. If you have trouble with Class.forName(), append .newInstance() to the end of the string.

org.postgresql.Driver Driver = new org.postgresql.Driver();

The important difference between this method (creating an instance of an org.postgresql.Driver object) and using Class.forName() is that you can use the latter method to select the driver that you want at runtime, rather than at compile time. If you arrange the code properly, you can load different drivers based on an external value, such as a command-line parameter or an environment variable. That might not be important if you simply want code that can talk only to PostgreSQL, but JDBC was designed to provide database portability. After Class.forName() loads the Driver class into your VM, the Driver's static initializer is invoked to register the driver with the JDBC DriverManager class.

After the DriverManager knows about the PostgreSQL JDBC driver, you can ask it to create a Connection object for you.

There are three DriverManager.getConnection() methods:

getConnection( String url, String user, String password );
getConnection( String url, Properties props );
getConnection( String url );

Each form uses a different strategy for getting the username and password to the driver. In the first form, the username and password are passed as extra parameters. In the second form, the username and password are expected to be in the props property list. In the last form, the URL should contain the username and password as separate properties.

In the following code fragment, the three calls to getConnection() are equivalent:

...
Properties    connectionProps;
String        url = "jdbc:postgresql:movies";

connectionProps.put( "user", "korry" );
connectionProps.put( "password", "cows" );

DriverManager.getConnection( url, "korry", "cows" );
DriverManager.getConnection( url, connectionProps );
DriverManager.getConnection( url + "?user=korry&password=cows" );
...

Looking back at client1.java, you see the first form of getConnection(). If getConnection() returns successfully, client1 prints a message, closes the connection (at line 23), and runs to completion. If getConnection() fails to connect to the database, it will throw an exception. You'll see how to intercept errors in the next section.

Let's compile and run this client:

$ make client1.class
javac -g client1.java
$ java client1
Connection complete
$

Sorry, that's not very exciting is it? Shut down the postmaster just so you know what an error might look like:

$ pg_ctl stop
waiting for postmaster to shut down......done
postmaster successfully shut down

$ java client1 
Exception in thread "main" 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.
        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:177)
        at client1.main(client1.java:19)
$

You can almost feel the heat as client1 crashes and burns. That error message isn't very friendly. Let's move on to client2, in which we will try to intercept the failure and provide a little insulation to the end users.