Frank4DD, @2008-2010

Introduction

JDBC is a set of classes and interfaces written in Java that allows Java programs to access a database. Oracle provides different types of JDBC drivers, this how-to is refering to the Oracle thin driver.

Download driver package

Around 2006, the distributed driver file was called classes12.zip. The content of the driver file could be accessed with: 

jar -tf classes.zip
oracle/sql/...

Today, the package is called ojdbc5.jar for Java 1.5, or ojdbc6.jar for version 1.6. Here is an example link I used at the time of testing: Latest Release 11.2.0.1.0

Install the driver package

In order to work with the driver, Java must be able to find it when called. We can achieve this by adding the drivers file location to the Java classpath, or by simply placing the driver file into the Java standard directory for extensions, for JAVA JRE this is $JAVA_HOME/jre/lib/ext (i.e. /usr/lib64/jvm/jre-1.6.0-ibm/lib/ext).

Use the driver to access Oracle through Java

The following example code JdbcTestOracle.java can be used to quickly access and test the JDBC connection. 

vi JdbcTestOracle.java
//JdbcTestOracle.java
import java.sql.Connection ;
import java.sql.DriverManager ;
import java.sql.ResultSet ;
import java.sql.Statement ;

class JdbcTestOracle {
  public static void main (String args[]) {
      try {
        Class.forName("oracle.jdbc.driver.OracleDriver");
       }
        catch (ClassNotFoundException e) {
            System.err.println (e) ;
            System.exit (-1) ;
        }
       try {
            // open connection to database
           Connection connection = DriverManager.getConnection(
             "jdbc:oracle:thin:@localhost:1521:XE",
             "system",  // ## fill in User here
             "test" // ## fill in Password here
            );

            // build query
            String query = "SELECT * From DUAL" ;

            // execute query
            Statement statement = connection.createStatement () ;
            ResultSet rs = statement.executeQuery (query) ;

            // show query results
            while ( rs.next () )
                System.out.println ("Oracle Query: " + rs.getString (1)) ;
            connection.close () ;
        }
        catch (java.sql.SQLException e) {
            System.err.println (e) ;
            System.exit (-1) ;
        }
    }
}

Compile and run the test program

fm@susie:~> javac JdbcTestOracle.java 
fm@susie:~> java JdbcTestOracle
Oracle Query: X

Should this test fail, typical reasons are:

Connecting with a special role: using SYSOPER or SYSDBA

It is possible to connect as SYSOPER or SYSDBA within JDBC. In order to do that, we need to set a property value called "internal login". The following code example shows how to set it up: 

vi JdbcTestOracle.java
//JdbcTestOracle.java
import java.util.Properties ;
import java.sql.Connection ;
import java.sql.DriverManager ;
import java.sql.ResultSet ;
import java.sql.Statement ;

class JdbcTestOracle {
  public static void main (String args[]) {
      try {
        Class.forName("oracle.jdbc.driver.OracleDriver");
       }
        catch (ClassNotFoundException e) {
            System.err.println (e) ;
            System.exit (-1) ;
        }
       try {
            Properties db_credentials = new Properties(); 
            db_credentials.put("user", "sys"); 
            db_credentials.put("password", "testpass"); 
            db_credentials.put("internal_logon", "sysdba");

            // open connection to database
           Connection connection = DriverManager.getConnection(
             "jdbc:oracle:thin:@localhost:1521:XE", db_credentials);
...