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:
- Wrong database user, or database user did not get the roles "connect" and "resource".
- The Oracle listener is not running, therefore SQLnet connections fail.
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);
...