Connecting to the Database

To access a database, a connection must be initiated. The connection is a Java object containing methods that access the database. The connection class also holds information on the state of connections. Various connection parameters are necessary, for example, to locate the database, to specify drivers and protocols, and to specify a user account and password in the DBMS. The format of these parameters and how to set them is discussed in the following section, “JDBC’s database naming scheme.”

As shown in Figure 6-1, the first step is the establishment of a connection. The last step will, of course, be the termination (“closing”) of the connection. Opening and closing the connection usually creates and releases user resources within the database management system and driver.

Figure 6-1: An overview of the connection.

To create an instance of a connection, it is necessary to understand the JDBC naming scheme. The next subsection explains this in detail.

JDBC’s database naming scheme

JDBC uses a particular syntax to name a database. The designers wanted to use a well-known and supported convention: the uniform resource locator (URL) syntax. In this case, the JDBC URL has the following form:

jdbc:<subprotocol>:<subname>

In this form, jdbc means that the protocol is JDBC, the subprotocol field is the name of the JDBC driver to be used, and the subname is a parameter string that is dependent on the subprotocol. Figure 6-2 illustrates the JDBC URL naming mechanism.

Figure 6-2: JDBC mechanism to name a data source.

The following examples show some of the uses of database URLs:

jdbc:odbc:sampledb

A JDBC-Open Database Connectivity (ODBC) bridge will be used, and the ODBC Data Source Name (DSN) is sampledb.

jdbc:odbc:sampledb;UID=javauser;PWD=hotjava

This URL is the same as the previous one, but adds a user ID and password. Other attributes can also be added. Note that the format of JDBC URLs is a little bit arbitrary. JDBC driver vendors may choose to use their own proprietary format. For example, such a format might be the following:

jdbc:mydbdrv://www.mydomain.net:8192/mydb 

In this case, the subprotocol is called mydbdrv. The database engine is running on the www.mydomain.net host (the subname field), the Transmission Control Protocol/Internet Protocol (TCP/IP) port that should be used is 8192, and mydb is the name of the database to be accessed. The significance of these parameters is somewhat arbitrary. For example, if the subprotocol (the driver) always uses the same port number, it is unnecessary to provide it in the URL. In this example, mydb, called a sub-subname, refers to a specific database instance. Other types of JDBC drivers may interpret the sub-subname as something else other than a specific database instance name.

jdbc:dcenaming:employees

This URL suggests that a local Distributed Computing Environment (DCE) naming service should be used to locate the database named employees. This service resolves employees into a particular name more appropriate to locate the database engine. Another type of network naming protocol can be used — for example, Network Information System (NIS).

Note that the ODBC subprotocol URL should always conform to the following syntax:

jdbc:odbc:<dsn>[;<attribute-name>=<attribute-value>]*

The JDBC URL syntax is flexible enough to allow specific drivers to interpret their own syntax.

Table 6-1 contains a few JDBC URLs recognized by commercial database products. Please check the documentation for more details and options that are specific to each database.

Table 6-1: SAMPLE JDBC URLS

Database	JDBC URL
DB2	jdbc:db2://host:4100/mydb;
Cloudscape	jdbc:cloudscape:HelloWorldDB;create=true
Connect SW	jdbc:sybase://host.domain.com:8192
Hypersonic	jdbc:HypersonicSQL:testdb
Informix	jdbc:informix-sqli://host:1526/mydb:INFORMIXSERVER= server_ol;
MiniSQL	jdbc:msql://athens.imaginary.com:4333/Testdb
ODBC Bridge	jdbc:odbc:data-source-name
Oracle	jdbc:oracle://host:1521/ORACLE_SID;
Oracle Lite	jdbc:polite:data_sourcename
Sybase	jdbc:sybase:Tds:host.domain.com:8192

The format of URLs is not defined by the JDBC specification. It is up to the vendors to determine a format; you should therefore refer to a driver’s documentation for specific details about URLs.

Specifying a JDBC driver

A specific database is usually reachable through one or more drivers. The driver manager and driver objects provide methods to load a driver and handle driver properties. JDBC must have some knowledge about the available drivers. This knowledge comes from the jdbc.drivers system property. It can be set via the Java interpreter command line or via a property file.

Here’s how to specify the jdbc.drivers system property via the command line (which can be included in a shell script or batch file for greater convenience when invoking standalone programs):

% java -Djdbc.drivers=vendor1.driver1 example

Here’s how to specify the jdbc.drivers system property via a file — for example, when using the applet viewer or Sun’s HotJava browser:

# On Unix, this file is ~/.hotjava/properties jdbc.drivers=vendor1.driver1 

Although the database URL specifies the database and protocol to be used, it is sometimes preferable to let the JDBC driver manager choose between two or more drivers. In this case, it is possible to specify a driver list in the property called jdbc.drivers. The list of driver class names should be colon separated — for example:

‘vendor1.dbdrv:vendor2.sql.foodriver:vendor3.db.connectdrv’

The driver manager tries to use each of the drivers listed in jdbc.drivers until it finds one that can successfully connect to the given URL. Drivers that aren’t trusted code are skipped. The driver registers itself with the driver manager to allow connections to be made.

In case the jdbc.driver system property is unavailable, there is a way to force a particular driver to be loaded. For example, the following line loads a JDBC-ODBC Bridge driver:

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

Another method is to use the following statements, which register the bridge driver class with the driver manager:

Driver myDriver = new sun.jdbc.odbc.JdbcOdbcDriver(); java.sql.DriverManager.registerDriver(myDriver);

Table 6-2 contains a few examples of JDBC driver class names for commercial database products. Please check the documentation, as the package names may have changed.

Table 6-2: SAMPLE JDBC DRIVERS

Database	JDBC Driver Class Name
DB2	com.ibm.DB2.JdbcDriver
Cloudscape	COM.cloudscape.core.JDBCDriver
Connect SW	connect.sybase.SybaseDriver
Hypersonic	org.hsql.jdbcDriver
Informix	com.informix.jdbc.IfxDriver
MiniSQL	COM.imaginary.sql.msql.MsqlDriver
ODBC Bridge	sun.jdbc.odbc.JdbcOdbcDriver
Oracle	com.oracle.jdbc.OracleDriver
Oracle Lite	oracle.pol.poljdbc.POLJDBCDriver
Sybase	com.sybase.jdbc.SybDriver

The exact JDBC driver class names are not standardized; I therefore recommend that you check the driver’s documentation to know the latest correct class name for it.

Creating a connection

Before creating a connection, it is necessary to declare it. Declaring a connection is quite simple; just say that you need a connection object and name it:

Connection myConnection;

In this example, the connection object is named myConnection.

To connect to the data source, the connection object must be made. The method that provides this functionality is as follows:

java.sql.DriverManager.getConnection();

The following statement creates a connection object that sends statements to the database. The URL naming convention is used with the getConnection() method and is the way to specify the data source that is targeted.

String url = "jdbc:odbc:mysource"; Connection myConnection = DriverManager.getConnection(url, "javauser", "hotjava");

This example shows how to pass the URL string to the driver manager plus specific arguments to the driver itself. In this case:

• The protocol used is JDBC.

• The driver is a JDBC-ODBC Bridge.

• The ODBC DSN is mysource.

• A username is provided: javauser.

• A password is provided: hotjava.

In some cases, the ODBC data source doesn’t need a login and password. In such cases, the getConnection(String url) method is sufficient.

The driver manager tries to find a registered JDBC driver that is allowed to reach the data source that is specified in the URL.

The following are other methods that enable the user to establish a connection to the database. They have the same name but different parameters.

DriverManager’s getConnection Methods

Connection getConnection(String url); Connection getConnection(String url, String user, String password); Connection getConnection(String url, java.util.Properties info);

The getConnection(String url) method doesn’t use specific parameters to provide the username and password. If necessary, and if allowed by the specific driver, these values may be passed within the URL string as shown in this example:

String url = "jdbc:odbc:mysource;UID=javauser;PWD=hotjava"; Connection myConnection = DriverManager.getConnection(url);

The getConnection(String url, String user, String password) method sends the second and third parameters to the driver, and the driver usually interprets them as the username and password to connect to the data source.

In the case of the getConnection(String url, java.util.Properties info) method, the second parameter is a list of arbitrary string pairs such as user and its value and password and its value. These two connection arguments should be included in the list. The list of such properties should be included in the driver’s documentation. However, it is preferable to pass as much information as possible within the database URL.

What you have learned so far enables you to begin your first Java standalone application. Listing 6-2 shows how to open a connection.

Listing 6-2: Opening a Connection

// opening a connection import java.sql.*; class SimpleExample {          public static void main(String args[]) {                   try {                           String url = "jdbc:odbc:mysource";                           Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");                           Connection myConnection =                           DriverManager.getConnection(url,                           "javauser", "hotjava");                           // ...                   } catch (java.lang.Exception ex) {                           ex.printStackTrace();                   }          } }

Closing a connection

Closing connections is as important as opening connections. If connections are never closed, the database keeps unnecessary connection context in memory and eventually runs out of connections. The following shows how to proceed to close connections:

Connection Closing Methods

void close(); boolean isClosed(); void setAutoClose(boolean autoclose); boolean getAutoClose();

These methods all apply to a connection instance.

The close() method in the first line simply closes the current connection. Normally, a connection closes automatically when it is garbage-collected or when certain fatal errors occur. It is a good programming practice to close connections when they’re no longer needed.

isClosed returns true if the connection is currently closed and false if it is open.

A connection is normally in autoclose mode by default. Because other objects may depend on specific connections (for example, statements and result set objects, which are discussed later in the “Sending statements” sections), it may be necessary to keep a connection open after a transaction has been committed or rolled back (canceled). setAutoClose() enables you to disable autoclosing.

The getAutoClose() method returns true in case the connection is in autoclose state and false in the opposite case.

Listing 6-3 shows how to close a connection.

Listing 6-3: Closing a Connection

// closing a connection import java.sql.*; class SimpleExample {          public static void main(String args[]) {                   try {                           // ...                           String url = "jdbc:odbc:mysource";                           Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");                           Connection myConnection =                           DriverManager.getConnection(url,                           "javauser", "hotjava");                           // ...                           if (!myConnection.isClosed())                                     myConnection.close();                           // ...                   } catch (java.lang.Exception ex) {                            ex.printStackTrace();                   }          } }

Adjusting properties

It is possible to set and query connection properties that affect the general behavior of commands to be performed within the connection.

The methods available to set and query the connection object are listed in the following:

Connection Behavior

void setReadOnly(boolean readonly); boolean isReadOnly(); void setCatalog(String catalog); String getCatalog();

It may be necessary to put a connection in read-only mode, as in the first line of the preceding. By default, a connection isn’t set as read-only. Setting it in read-only mode may sometimes be practical and may enable database optimizations where the connection won’t be used for database updates.

For the second method, boolean isReadOnly();, the return is true if the connection has been set in read-only mode. Use isReadOnly to test the connection mode.

A catalog is a database subspace containing the database objects affected by the operations performed within the connection. Some DBMSs manage multiple databases at the same time. It is possible to restrict the subspace to one or another database using the setCatalog() method, as in the third method of the preceding. In cases where the DBMS or the driver associated with the connection doesn’t support catalogs, this method silently ignores all calls to it.

The getCatalog() method in the last line gives the catalog name that is currently in use or a null value.

Note 

The notion of database catalogs is database independent. Some databases support it, and others don’t. In general, a catalog means a logical subspace of a database server instance.

Listing 6-4 shows how to adjust connection properties.

Listing 6-4: Adjusting Connection Properties

// adjusting connection properties import java.sql.*; class SimpleExample {          public static void main(String args[]) {                   try {                           // ...                           String url = "jdbc:odbc:mysource";                           Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");                           Connection myConnection =                           DriverManager.getConnection(url,                           "javauser", "hotjava");                           if (myConnection.isReadOnly()) {                                System.out.println("Connection is read only");                           } else {                                myConnection.setReadOnly(true);                           }                           System.out.println("Default catalog: " +                           myConnection.getCatalog());                           // use the pubs2 database                           myConnection.setCatalog("pubs2");                           // ...                           if (!myConnection.isClosed())                                myConnection.close();                           // ...                   } catch (java.lang.Exception ex) {                            ex.printStackTrace();                   }          } }

Putting it all together

The essential steps in every Java project that uses JDBC to obtain and terminate a connection to a DBMS are the following:

• Import the java.sql.* package classes.

• Build a JDBC URL.

• Load one or more specific JDBC driver with Class.forName() or DriverManager.registerDriver().

• If necessary, adjust the connection properties.

• Open a connection with getConnection().

• Terminate the connection with close().

The example in Listing 6-5 does nothing but open a connection and close it. The JDBC log stream is set to the standard output to trace everything that happens. Note that this is a deprecated method; therefore, it must be used with caution.

Listing 6-5: A Do-Nothing Client

// a do-nothing client import java.sql.*; class SimpleExample {          public static void main(String args[]) {                   String url = "jdbc:odbc:mysource";                   try {                           Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");                           // set the log stream (deprecated)                           DriverManager.setLogStream(java.lang.System.out);                           Connection myConnection =                           DriverManager.getConnection(url,                           "javauser", "hotjava");                           myConnection.close();                   } catch(java.lang.Exception ex) {                           ex.printStackTrace();                   }          } }

Note that code to catch java.lang.Exception is explicitly included. I point out what this means later in this chapter, in the section "Managing Errors and Warnings." For the moment, just note that the compiler will complain if you don’t catch this exception:

2 errors compiling: ex.java ex.java(13): Exception java.lang.ClassNotFoundException must be caught, or it 
must be declared in the throws clause of this method. Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); ^ ex.java(15): Exception java.sql.SQLException must be caught, or it must be declared in the throws clause of this method. DriverManager.getConnection(url, ^ 

What happens? The lines shown in Listing 6-6 are printed to the JDBC log stream during the execution of this small Java program.

Listing 6-6: The JDBC Log Stream

DriverManager.getConnection("jdbc:odbc:mysource") trying driver[className=sun.jdbc.odbc.JdbcOdbcDriver,context=null, jdbc.odbc.JdbcOdbcDriver@1393878] *Driver.connect (jdbc:odbc:mysource) JDBC to ODBC Bridge: Checking security No SecurityManager present, assuming trusted application/applet JDBC to ODBC Bridge 1.0 Current Date/Time: Wed Aug 07 19:42:19 1996 Loading JdbcOdbc library Allocating Environment handle (SQLAllocEnv) hEnv=5308508 Allocating Connection handle (SQLAllocConnect) hDbc=5310680 Connecting (SQLDriverConnect), hDbc=5310680,  szConnStrIn=DSN=mysource;UID=javauser;PWD=hotjava RETCODE = 1 getConnection returning driver[className=sun.jdbc.odbc.JdbcOdbcDriver,context=null, sun.jdbc.odbc.JdbcOdbcDriver@1393878] *Connection.close Disconnecting (SQLDisconnect), hDbc=5310680 Closing connection (SQLFreeConnect), hDbc=5310680 Closing environment (SQLFreeEnv), hEnv=5308508

The JDBC log is quite long, and you actually did nothing but open and close a connection. It is interesting to see all the silently performed actions.