JDBC Install

Example

At it's simplest, you can then create a .java file containing your Java program (e.g. JDBCtest.java)...

import java.sql.*;

class JDBCtest {

  public static void main (String args []) throws SQLException

  {

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

    Connection conn = DriverManager.getConnection

      ("jdbc:oracle:thin:@machineName:1521/SERVICE", "username", "password"); // @machineName:port:SID, userid, password

    Statement stmt = conn.createStatement();

    ResultSet rset = stmt.executeQuery("select BANNER from SYS.V_$VERSION");

    while (rset.next())

      System.out.println (rset.getString(1));   // Print col 1

    stmt.close();

  }

}

Compile it...

javac JDBCtest.java

Run it..

java JDBCtest

Long form connection string

jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(HOST=myhost)(PORT=1521)(PROTOCOL=tcp))(CONNECT_DATA=(SERVICE_NAME=myservice))) 

Mainframe

On a mainframe you may need to force compatible encoding otherwise you will see strange characters in the listener.log...

To force it to use UTF8 ASCII use...

java -Dfile.encoding=UTF8 JDBCtest

JDBC and Multi-Tenant Architecture

When connecting to a PDB via JDBC you must connect via the SERVICE not the SID, hence the first example below is invalid, the second is valid...

jdbc:oracle:thin:@myserver:1521:mypdb

jdbc:oracle:thin:@myserver:1521/mypdb

JDBC and Oracle Wallet

In addition to adding ojdbcx.jar to the CLASSPATH (as described above) you should also add these additional items to the CLASSPATH...

$ORACLE_HOME/jlib/oraclepki.jar

$ORACLE_HOME/jlib/osdt_cert.jar

$ORACLE_HOME/jlib/osdt_core.jar

You can then connect using a command similar to this in your Java code...

Connection conn = DriverManager.getConnection ("jdbc:oracle:thin:/@TNSALIAS");

To run the code you should define two system properties on the command line to specify the tnsnames.ora and wallet location...

-Doracle.net.tns_admin=/u01/app/oracle/product/12.1.0.2/network/admin 

-Doracle.net.wallet_location=/home/oracle/wallet

For example...

/u01/app/oracle/product/12.1.0.2/jdk/bin/java -cp /u01/app/oracle/product/12.1.0.2/jdbc/lib/ojdbc6.jar:/u01/dba/java:/u01/app/oracle/product/12.1.0.2/jlib/oraclepki.jar:/u01/app/oracle/product/12.1.0.2/jlib/osdt_cert.jar:/u01/app/oracle/product/12.1.0.2/jlib/osdt_core.jar -Doracle.net.tns_admin=/u01/app/oracle/product/12.1.0.2/network/admin -Doracle.net.wallet_location=/home/oracle/wallet myclass

Bibliography