Connecting to the Database

The hardest thing about using JDBC is usually getting the JDBC drivers to connect to the database in the first place. The principle difficulty is because we need to get three different things working together: our Oracle database, our Java development environment, and the JDBC drivers to connect the Oracle database and our Java programs.

There are numerous options and alternatives for connecting, many of which we'll explore in the next chapter, but for now we'll start with the basics. These are the steps required to connect to the database:

• Setting up the Oracle, Java, and JDBC environment

• Importing the JDBC classes we need

• Loading and registering the JDBC driver

• Getting a Connection object from the driver

Setting up the Oracle, Java, and JDBC Environment

Before trying to get JDBC working, you should make sure that you have an Oracle database running and basic connectivity working. You should be able to start SQL*Plus, log into a database, and do a simple query. See Chapter 2 (or your database administrator) for more information if necessary.

You should also make sure that you have installed the Java software development kit. The latest version, Java 2 Platform, Standard Edition Version 1.4, is recommended. You should be able to create a Java source file, compile it, and run it.

Finally, you should set your CLASSPATH environment variable to include the Oracle JDBC driver. This is provided by Oracle in the [ORACLE_HOME]\jdbc\lib directory. If you are using Java 1.1, you should use classes11.zip. If you are using Java 1.2 or above (including 1.4 as recommended), you should use classes12.zip. For the sake of simplicity, we'll assume that you are using classes12.zip.

If you are using Windows 98, you can set your CLASSPATH by including the following line in the C:\AUTOEXEC.BAT file:

 SET CLASSPATH=.;[ORACLE_HOME]\jdbc\lib\classes12.zip. 

Notice that we have actually added two entries to the CLASSPATH variables, [ORACLE_HOME]\jdbc\lib\classes12.zip and a single period, separated by a semicolon. A single period, you may already know, indicates the current directory in the Unix and Windows operating system. If no CLASSPATH is defined, by default it will look in the current directory and the Java Virtual Machine (JVM) directory for classes. If CLASSPATH is defined, it will look only in the directories specified in CLASSPATH and the JVM directory. It will no longer look in the current directory unless the current directory is explicitly included in the CLASSPATH.

If you are using Windows NT/2000/XP, you can set CLASSPATH using the System applet in the Control Panel. Details differ slightly for NT and XP, but in Windows 2000, select the Advanced tab in the System applet then click the Environment Variables button.

If a CLASSPATH variable does not already exist, start by choosing New. Type in CLASSPATH as the variable name and .;\[ORACLE_HOME] \jdbc\lib\classes12.zip as the variable value.

If a CLASSPATH has already been defined, highlight it and select Edit. If there is already an entry for classes12.zip or classes11.zip, make a note of it and remove it if it's different than the path and file you plan to use. Otherwise, add \[ORACLE_HOME]\jdbc\lib\classes12.zip to what is already there. In either case, make sure it starts with a period and semicolon (.;).

Importing the JDBC Classes

The Java compiler needs to import the JDBC classes we will be using. At the top of our Java source file, we need to include the following import statements:

 import java.sql.*; import oracle.jdbc.driver.*; 

If the CLASSPATH hasn't been set correctly, the second import above will generate a complaint from the compiler, claiming that the package oracle.jdbc.driver does not exist.

Loading and Registering the JDBC Driver

To load the JDBC driver requires two steps: loading the class and registering it with the DriverManager. As it turns out, though, we need to do only one of these, and the other will happen automatically. If we load the driver by name, using the static Class.forName() method, the driver, as part of its initialization, will register itself with the DriverManager. We'll take the alternate approach, however, and register it explicitly with this line of code:

 DriverManager.registerDriver(new oracle.jdbc.OracleDriver()); 

The DriverManager will automatically load the driver for us.

Getting a Connection

The DriverManager is responsible for connecting us to resources. When we want to use a resource, such as a database, we construct a Uniform Resource Locater (URL) and use it to request a Connection object from the DriverManager. The DriverManager will search the registered drivers for one that can accept our URL.

Oracle provides two JDBC drivers, an OCI driver and a pure-Java thin driver; each of these accepts several different types of URLs. We will use the thin driver with a URL of the following format:

 "jdbc:oracle:thin:@<host>:<port>:<sid>" 

My database server is named noizmaker, I use the default port 1521, and my database's system identifier (SID) is osiris, so my URL is:

 "jdbc:oracle:thin:@noizmaker:1521:osiris" 

DriverManager has a number of different getConnection() static methods available, but we will use one that lets us specify username and password as separate parameters. The call looks like this:

 Connection conn = DriverManager.getConnection(                   "jdbc:oracle:thin:@noizmaker:1521:osiris"                              "scott", "tiger"); 

Factory Methods

The getConnection() method is one of many static methods in the Java API that are often referred to as factory methods. A factory method is a method that creates objects. It typically belongs to the abstract class or interface and returns an instance of a concrete class that implements an abstract class or interface. Because all we really care about is that it implements the Connection interface, this allows the implementation to hide irrelevant details from us, such as the precise type of the concrete class and the way it is initialized. This increases the portability and flexibility of the implementation because the factory can then have the freedom to give us different concrete classes with different features initialized in different ways, depending on the situation or environment.

Sample Connection Program

The following sample program puts all the above together:

 // TestConnection.java   Load JDBC and connect to database import java.sql.*; import oracle.jdbc.driver.*; public class TestConnection { public static void main(String [] vars) {   Connection conn;   try   { DriverManager.registerDriver(new   oracle.jdbc.OracleDriver()); // The following needs to be edited with // your database specifics:        conn = DriverManager.getConnection(     "jdbc:oracle:thin:@noizmaker:1521:osiris",         "scott", "tiger");  }  catch (SQLException e)  {    System.out.println("Caught: " + e);    System.exit(1);  } } } 

After editing the URL to include the proper parameters for your database, you should be able to compile and run this program. If the program runs without printing an error message, you can assume it connected to the database correctly. We'll expand this example to provide output in the next section when we add code to execute an SQL statement.