Notes 0016

Using Oracle from Java

Databases are not very useful on their own; they are mostly only useful when accessed from other applications.

Also, users (almost) never type in SQL queries to extract some data. These low level tasks are accomplished by application software, that basically allows for convenient and user friendly way to access database functionality.

Instead of typing in an INSERT statement, one would fill out an online form, and instead of using COMMIT they would simply click "OK" or something similar.

In these (and the following) notes we shall learn how we can use the Oracle database remotely, from within a Java program. We will learn how to connect to the database, get database information, execute queries, run procedures, etc.

Getting the Driver

Before we can get our Java programs to connect to Oracle, we need to get the Oracle JDBC drivers.

There are two basic types of drivers that we can use: the thin driver and the OCI driver. Both are found in the same place, but work quite differently (explained a bit later).

If you installed oracle on your machine, then you can get the Driver in your C:\Oracle\jdbc\lib (depending on where you installed it) directory [it will be in the \jdbc\lib]. The file you need is classes12.zip (or newer versions when those are available).

What you need to do now is point your CLASSPATH to point to this file (you can also move it to other directory and point to that). So, essentially, your CLASSPATH should at least look like:

CLASSPATH=.;C:\Oracle\jdbc\lib\classes12.zip;

This may be followed (or preceded) by other libraries. To set the path manually at command line (before you compile and run your code from the same DOS box), you can do this:

set CLASSPATH=.;C:\Oracle\jdbc\lib\classes12.zip;%CLASSPATH%

Note that if you do that instead of the more permanent CLASSPATH, then the environment (including CLASSPATH disappears when you close the DOS box (you will need to redo this every time you compile and run your code).

thin vs. OCI

When writing our applications, we need to decide in which environment it will run, how much memory it is allowed to use, and what performance is required (and what tradeoffs are we willing to make to get it to run quickly).

Depending on those decisions and conditions, we may choose one type of driver over the other.

thin

The thin driver is not exactly thin, but it is definitely more light weight than the OCI driver. It is a Java only driver (meaning you can run it on any platform without modification). It also does not require any outside libraries or environment.

You give this driver the hostname, port, and SID, and it will simply connect.

It offers fair performance, and is usually the driver of choice for Java applications. This is the one we will use (mostly).

OCI

The OCI driver is the Oracle Client Interface driver. What that means is that the driver, instead of connecting directly to the database, actually connects to the native implementation of the client driver which has to be (required to be) installed on the local machine.

Given that the client installation could use up over 200MB of disk space, and requires a separate installation (Oracle Client), this is not always an option (especially if your program is intended to run in an unfamiliar environment - not on an Intranet where you may be able to ensure proper Oracle Client installation).

For all the limitations, this driver performs better than the pure Java client, since it is free to use system optimized code to gain some advantage over the general Java version.

© 2006, Particle