OracleBlog

Last December I wrote about how to create a simple Perl application that connected to your Oracle database, it is high time that I showed you how to do the same in Java.

What You'll Need

1. An Oracle database. Make sure you can tnsping your ORACLE_SID. Better yet, make sure you can connect with sqlplus.

2. Java. It's a free download from Sun. I'm using 1.5, but I've tested this on several versions.

3. You don't need the client installed because we're using the thin client. If you want to use OCI but don't want to install the full Oracle client, download the instant client.

Set your PATH

As part of your Java installation, you must have set the path to your jdk\bin. Make sure that's there and add it if not.

Set your CLASSPATH

If you're using Java 1.4 or greater, then ojdbc14.jar contains the classes you need. You'll find it in ORACLE_HOME/jdbc/lib.

And more information on this? You can unzip the jar file and look at the files inside with some Java tools, search the Internet or, best yet, check out ORACLE_HOME/jdbc/doc/javadoc.tar. That's a complete API: all the classes and their parameters.

If you're using an older version, you'll want something like classes12.jar instead. Follow the installation instructions. But do NOT include this if you're on 1.4 or greater. You want one or the other, not both.

There is also one thing that seems to always get me when I start a java project. I always forget to add the local directory to the CLASSPATH. Save yourself the 20 wasted minutes and put it first.

C:\temp>echo %CLASSPATH%
.;c:\oracle\product\10.1.0\db\jdbc\lib\ojdbc14.jar

Include Your Classes

Though you may want others, here are the main classes you'll need to import. The former should be part of your Java SDK, the latter is in the aforementioned ojdbc14.jar.

import java.sql.*;
import oracle.jdbc.pool.OracleDataSource;

Create an OracleDataSource

Once you've created an OracleDataSource, you'll be free to write your SQL calls. My example below shows the syntax for a default installation, you'll need to modify that second line as appropriate.

OracleDataSource ods = new OracleDataSource();
ods.setURL("jdbc:oracle:thin:scott/tiger@localhost:1521:ORCL");
Connection conn = ods.getConnection();

Check the documentation, there are other ways of doing this.

Query Away!

There are many ways to use your connection to query your database. Here is one quick sample I found.

Statement stmt = conn.createStatement();
ResultSet rset = stmt.executeQuery("select 'Hello World' from dual");
while (rset.next())
System.out.println(rset.getString(1));

Compile and Execute

You'll need to compile your application before you execute.

javac JdbcVersion.java
java JdbcVersion

If you get this error:

Exception in thread "main" java.lang.NoClassDefFoundError

Then check your CLASSPATH very carefully. You can run java with the -verbose option to see if you get more information about what's missing.

That's it!

For more information, review the documentation to which the README file points you:

1. The new technical white paper

2. The online JDBC doc for the most updated information

3. The revised JDBC FAQ

4. The JDBC Developer's Guide and Reference