Recipe 20.5 Connecting to a JDBC Database

Problem

You need to connect to the database.

Solution

Use DriverManager.getConnection( ).

Discussion

The static method DriverManager.getConnection( ) lets you connect to the database using a URL-like syntax for the database name (for example, jdbc:dbmsnetproto://server:4567/mydatabase) and a login name and password. The "dbURL" that you give must begin with jdbc:. The rest of it can be in whatever form the driver vendor's documentation requires and is checked by the driver. The DriverManager asks each driver you have loaded (if you've loaded any) to see if it can handle a URL of the form you provided. The first one that responds in the affirmative gets to handle the connection, and its connect( ) method is called for you (by DriverManager.getConnection( )).

Four types of drivers are defined by Sun (not in the JDBC specification but in their less formal documentation); these are shown in Table 20-1.

Table 20-1. JDBC driver types
Type	Name	Notes
1	JDBC-ODBC bridge	Provides JDBC API access.
2	Java and Native driver	Java code calls Native DB driver.
3	Java and Middleware	Java contacts Middleware server.
4	Pure Java	Java contacts (possibly remote) DB directly.

Table 20-2 shows some interesting drivers. I'll use the ODBC bridge driver and InstantDB in examples for this chapter. Some drivers work only locally (like the JDBC-ODBC bridge), while others work across a network. For details on different types of drivers, please refer to the books listed at the end of this chapter. Most of these drivers are commercial products. InstantDB is a clever freeware^[4] product; the driver and the entire database management system reside inside the same Java Virtual Machine as the client (the database is stored on disk like any other, of course). This eliminates the interprocess communication overhead of some databases. However, you can't have multiple JVM processes updating the same database at the same time.

^[4] At this writing, it is also a freeware product in flux; use Google to see if you can find it.

Table 20-2. Some JDBC drivers
Driver class	Start of dbURL	Database
`sun.jdbc.odbc.JdbcOdbcDriver`	`jdbc:odbc:`	Bridge to Microsoft ODBC (included with JDK)
`jdbc.idbDriver`	`jdbc:idb:`	Instant Database (IDB)
`oracle.jdbc.Driver.OracleDriver`	`jdbc:oracle:thin:@server:port#:dbname`	Oracle
`postgresql.Driver`	`jdbc:postgres://host/database`	PostGreSQL (freeware database; see http://www.postgresql.org)
`org.gjt.mm.mysql.Driver`	`jdbc:mysql://host/database`	MySql (freeware database; see http://www.mysql.com)

Example 20-7 is a sample application that connects to a database. Note that we now have to catch the checked exception SQLException since we're using the JDBC API. (The Class.forName( ) method is in java.lang, and so it is part of the standard Java API, not part of JDBC.)

Example 20-7. Connect.java

import java.awt.*; import java.sql.*; /** Load a driver and connect to a database.  */ public class Connect {     public static void main(String[] av) {         String dbURL = "jdbc:odbc:Companies";         try {             // Load the jdbc-odbc bridge driver             Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");             // Enable logging             DriverManager.setLogStream(System.err);             System.out.println("Getting Connection");             Connection conn =                  DriverManager.getConnection(dbURL, "ian", "");    // user, passwd             // If a SQLWarning object is available, print its             // warning(s).  There may be multiple warnings chained.             SQLWarning warn = conn.getWarnings( );             while (warn != null) {                 System.out.println("SQLState: " + warn.getSQLState( ));                 System.out.println("Message:  " + warn.getMessage( ));                 System.out.println("Vendor:   " + warn.getErrorCode( ));                 System.out.println("");                 warn = warn.getNextWarning( );             }             // Process the connection here...             conn.close( );    // All done with that DB connection         } catch (ClassNotFoundException e) {             System.out.println("Can't load driver " + e);         } catch (SQLException e) {             System.out.println("Database access failed " + e);         }     } }

I've enabled two verbosity options in this example. The use of DriverManager.setLogStream( ) causes any logging to be done to the standard error, and the Connection object's getWarnings( ) prints any additional warnings that come up.

When I run it on a system that doesn't have ODBC installed, I get the following outputs. They are all from the setLogStream( ) except for the last one, which is a fatal error:

Getting Connection JDBC to ODBC Bridge: Checking security *Driver.connect (jdbc:odbc:Companies) JDBC to ODBC Bridge: Checking security JDBC to ODBC Bridge 1.2001 Current Date/Time: Fri Jun 16 16:18:45 GMT-5:00 2000 Loading JdbcOdbc library Unable to load JdbcOdbc library Unable to load JdbcOdbc library Unable to allocate environment Database access failed java.sql.SQLException: driver not found: jdbc:odbc:Companies

On a system with JDBC installed, the connection goes further and verifies that the named database exists and can be opened.

Problem

Solution

Discussion

Table 20-1. JDBC driver types

Table 20-2. Some JDBC drivers

Example 20-7. Connect.java

See Also