Jdbc Oracle Drivermanager

January 15, 2012 by Admin No comments

JDBC Thin Driver

communicate directly with the data source using a standard Java socket
is written 100% in Java and hence is platform independent
does not require any additional Oracle software on the client
driver supports only TCP/IP-based communication

Connect using Oracle Think Driver (via OracleDataSource)

String connectURL = "jdbc:oracle:thin:scott/tiger@//192.168.72.129:1521/orcl.mydom.com"; OracleDataSource ds = new OracleDataSource(); ds.setURL(connectURL); Connection conn = ds.getConnection();

Connect using Oracle Think Driver (via DriverManager)

Class.forName("oracle.jdbc.OracleDriver"); String connectURL = "jdbc:oracle:thin:scott/tiger@192.168.72.129:1521:orcl"; Connection conn = DriverManager.getConnection(connectURL);

JDBC OCI Driver

OCI stands for Oracle Call Interface
called a thick driver
allows access Oracle database using a 3GL-Language such as C/C++
requires the OCI C libraries, Oracle Net libraries
until 9i Release 2 required the entire Oracle client installation
since 10g optional reqired just Oracle Instant Client

Connect using Oracle OCI Driver

String connectURL = "jdbc:oracle:oci:@myorcl"; Connection conn = DriverManager.getConnection (connectURL, "scott", "tiger" );

use OCI Driver when:

connect to Oracle other than TCP/IP
use OCI connection pooling
use TAF – Transparent Application Failover
when code run in middle tier, application server
OCI is faster before 9i, performance is no relevant since 9i verus thin driver

use Thin Driver when:

too many clients
portability

Install

Oracle JDBC Driver kann download from:

10g: http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-10201-088211.html

11g: http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-112010-090769.html

or you can find it direct in Oracle installation $ORACLE_HOME/jdbc/lib

exm: ojdbc5.jar is nessesary for connetion to Oracle Database 11g and must be in the LIBPATH

- for connection using OCI Driver, Oracle Client or minimal Oracle Instant Client must installed

Test Code

package test; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import oracle.jdbc.pool.OracleDataSource; public class TestOJDBCConnect { static void doShowEmp(Connection conn) throws SQLException { ResultSet rset = null; Statement stmt = null; stmt = conn.createStatement(); rset = stmt.executeQuery("select empno, ename, job from emp"); final int COL_EMPNO = 1; final int COL_ENAME = 2; final int COL_JOB = 3; while (rset.next()) { int empNo = rset.getInt(COL_EMPNO); String empName = rset.getString(COL_ENAME); String empJob = rset.getString(COL_JOB); System.out.println(empNo + " " + empName + " " + empJob); } rset.close(); stmt.close(); } static void doSelect(Connection conn, String sql) throws SQLException{ Statement stmt = conn.createStatement(); ResultSet rset = stmt.executeQuery(sql); if (rset.next()) System.out.println(rset.getString(1)); rset.close(); stmt.close(); } public static Connection getConnectionByDataSource() throws SQLException { String connectURL = "jdbc:oracle:thin:scott/tiger@//192.168.72.129:1521/orcl.mydom.com"; OracleDataSource ds = new OracleDataSource(); ds.setURL(connectURL); Connection conn = ds.getConnection(); doSelect(conn, "select 'Connecting via DataSource..User is '|| initcap(USER) from dual"); return conn; } public static Connection getConnectionByDriverManager() throws SQLException, ClassNotFoundException { Class.forName("oracle.jdbc.OracleDriver"); String connectURL = "jdbc:oracle:thin:scott/tiger@192.168.72.129:1521:orcl"; Connection conn = DriverManager.getConnection(connectURL); doSelect(conn, "select 'Connecting via DriverManager..User is '|| initcap(USER) from dual"); return conn; } public static Connection getConnectionOCI() throws SQLException { String connectURL = "jdbc:oracle:oci:@myorcl"; Connection conn = DriverManager.getConnection (connectURL, "scott", "tiger" ); doSelect(conn, "select 'Connecting via OCI..User is '|| initcap(USER) from dual"); return conn; } public static void main(String args[]) throws SQLException, ClassNotFoundException { Connection conn = null; conn = getConnectionByDataSource(); conn.close(); conn = getConnectionByDriverManager(); conn.close(); conn = getConnectionOCI(); doShowEmp(conn); conn.close(); } }

jdbc:oracle:thin: is the prefix when connect using Thin driver
jdbc:oracle:oci: is the prefix when connect using OCI driver
scott is username
tiger is password
192.168.72.129 is the ip of the database server (or hostname)
1521 ist the port number
orcl.mydom.com is the service name
orcl is the SID
myorcl is the net service name from tnsnames.ora

.. the output in Eclipse console is:

Connecting via DataSource..User is Scott Connecting via DriverManager..User is Scott Connecting via OCI..User is Scott 7369 SMITH CLERK 7499 ALLEN SALESMAN 7521 WARD SALESMAN 7566 JONES MANAGER 7654 MARTIN SALESMAN 7698 BLAKE MANAGER 7782 CLARK MANAGER 7788 SCOTT ANALYST 7839 KING PRESIDENT 7844 TURNER SALESMAN 7876 ADAMS CLERK 7900 JAMES CLERK 7902 FORD ANALYST 7934 MILLER CLERK