Connect Oracle database via JDBC
November 29th, 2011 . by adminJDBC 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