JDBC Driver for ORACLE

    我们知道SUN定义的JDBC Driver有四种类型:Type1 JDBC-ODBC桥、Type2 JAVA to Native API、Type3 Java to net 、Type4 JAVA to native dababase；对于我们开发人员来说，了解他们的异同是非常必要的，尤其是在做纯后台高性能项目时(注：受最近纯后台项目的折磨而感想，不得不考虑JAVA如何满足高并发、高性能)。下面是一些关于JDBC Driver for ORACLE的随笔记录：

》JDBC四种类型描述：

》》简约型描述（摘录自http://www.cmsjzw.com/wangluobiancheng/JSP/9152.html）

Type1 JDBC-ODBC桥

作为JDK1.1后的一部分，是sun.jdbc.odbc包的一部分

ApplicationJDBC-ODBC Bridge(Type1 jdbc driver)-JDBC-ODBC LibraryODBC DriverDatabase

适用于快速的原型系统，没有提供JDBC驱动的数据库如Access  

Type2 JAVA to Native API

利用开发商提供的本地库来直接与数据库通信。

Application-JDBC Driver(Type2 jdbc driver)-Native Database library-Database

比Type1性能略好。  

Type3 Java to net

Application-Jdbc Driver(Type3 jdbc driver)-java middleware-JDBC Driver-Database

具有最大的灵活性，通常由那些非数据库厂商提供，是四种类型中最小的。 

Type4 JAVA to native dababase

Application-Jdbc driver(type4 jdbc driver)-database engine-database

最高的性能，通过自己的本地协议直接与数据库引擎通信，具备在Internet装配的能力。

 

》》详细型描述（摘录自http://blog.csdn.net/kj021320/archive/2006/08/06/1028291.aspx）：

1、JDBC-ODBC bridge plus ODBC driver:        JDBC-ODBC桥驱动程序，将JDBC调用转换为ODBC的调用。( This combination provides JDBC access via ODBC drivers. ODBC binary code--and in many cases, database client code-- must be loaded on each client machine that uses a JDBC-ODBC Bridge. Sun provides a JDBC-ODBC Bridge driver, which is appropriate for experimental use and for situations in which no other driver is available.)注意， 必须将ODBC二进制代码（许多情况下还包括数据库客户机代码）加载到使用该驱动程序的每个客户机上。这种类型的驱动程序最适合于企业网（这种网络上客户机的安装不是主要问题），或者是用Java编写的三层结构的应用程序服5、 务器代码。

2、Native-API partly-Java driver:  将JDBC调用转换为对数据库客户端API的调用。(A native-API partly Java technology-enabled driver: This type of driver converts JDBC calls into calls on the client API for Oracle, Sybase, Informix, DB2, or other DBMS. Note that, like the bridge driver, this style of driver requires that some binary code be loaded on each client machine.)这种类型的驱动程序把客户机API上的JDBC调用转换为Oracle、Sybase、Informix、DB2或其它DBMS的调用。注意，象桥驱动程序一样，这种类型的驱动程序要求将某些二进制代码加载到每台客户机上。
3、 Pure Java Driver for Database Middleware: 先将JDBC调用转换为DBMS-independent网络协议，然后由服务器端的中间件转换为具体数据库服务器可以接收的网络协议。(net-protocol fully Java technology-enabled driver #This style of driver translates JDBC calls into the middleware vendor's protocol, which is then translated to a DBMS protocol by a middleware server. The middleware provides connectivity to many different databases.)这种网络服务器中间件能够将它的纯Java客户机连接到多种不同的数据库上。所用的具体协议取决于提供者。通常，这是最为灵活的JDBC驱动程序。有可能所有这种解决方案的提供者都提供适合于Intranet用的产品。为了使这些产品也支持Internet访问，它们必须处理Web所提出的安全性、通过防火墙的访问等方面的额外要求。几家提供者正将JDBC驱动程序加到他们现有的数据库中间件产品中。

4、Direct-to-Database Pure Java Driver:    将JDBC调用直接转换为具体数据库服务器可以接收的网络协议。(native-protocol fully Java technology-enabled driver #This style of driver converts JDBC calls into the network protocol used directly by DBMSs, allowing a direct call from the client machine to the DBMS server and providing a practical solution for intranet access.)这将允许从客户机机器上直接调用DBMS服务器，是Intranet访问的一个很实用的解决方法。由于许多这样的协议都是专用的，因此数据库提供者自己将是主要来源，有几家提供者已在着手做这件事了。

 

　　 第3、4类驱动程序将成为从JDBC访问数据库的首选方法。第1、2类驱动程序在直接的纯Java驱动程序还没有上市前将会作为过渡方案来使用。对第1、2类驱动程序可能会有一些变种（下表中未列出），这些变种要求有连接器，但通常这些是更加不可取的解决方案。第3、4类驱动程序提供了Java的所有优点，包括自动安装（例如，通过使用JDBC驱动程序的applet applet来下载该驱动程序）。

 

》》对于oracle的链接，我们通常采用thin及oci模式，还有特殊的kprb模式专为oracle的存储过程而准备，详细信息如下（摘录自：http://www.orafaq.com/wiki/JDBC）

[edit] THIN driver

Oracle's JDBC Thin driver uses Java sockets to connect directly to Oracle. It provides its own TCP/IP version of Oracle's SQL*Net protocol. Because it is 100% Java, this driver is platform independent and can also run from a Web Browser (applets).

Sample connect string:

String url = "jdbc:oracle:thin:@myhost:1521:orcl";

The connection string for Oracle XE (version 10g) is:

String url = "jdbc:oracle:thin:@myhost:1521:xe";

You can find the SID name in your tnsnames.ora file:

XE =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521))
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = XE)
   )
 )

Working example program (Conn.java):

import java.sql.*;
class Conn {
  public static void main (String args []) throws SQLException
  {
        DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());

        Connection conn = DriverManager.getConnection
            ("jdbc:oracle:thin:@localhost:1521:orcl", "scott", "tiger");
                            // @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();
  }
}

[edit] OCI driver

Oracle's JDBC OCI drivers uses Oracle OCI (Oracle Call Interface) to interact with an Oracle database. You must use a JDBC OCI driver appropriate to your Oracle client installation. The OCI driver works through SQL*Net.

The JDBC OCI drivers allow you to call the OCI directly from Java, thereby providing a high degree of compatibility with a specific version of Oracle. Because they use native methods, they are platform specific.

String url = "jdbc:oracle:oci:@myhost:1521:orcl";

Here is an example connect class:

import java.sql.*;
class dbAccess {
  public static void main (String args []) throws SQLException
  {
        try {
              Class.forName ("oracle.jdbc.driver.OracleDriver");
        } catch (ClassNotFoundException e) {
              e.printStackTrace();
        }

        Connection conn = DriverManager.getConnection
             ("jdbc:oracle:oci8:@hostname_orcl", "scott", "tiger");
                     // or oci7 @TNSNames_Entry,    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();
  }
}

[edit] KPRB driver

Oracle's JDBC KPRB driver is mainly used for writing Java stored procedures, triggers and database JSPs. It uses the default/ current database session and thus requires no additional database username, password or URL.

One can obtain a handle to the default or current connection (KPRB driver) by calling the OracleDriver.defaultConenction() method. Please note that you do not need to specify a database URL, username or password as you are already connected to a database session. Remember not to close the default Connection. Closing the default connection might throw an exception in future releases of Oracle.

import java.sql.*;
class dbAccess {
  public static void main (String args []) throws SQLException
  {
        Connection conn = (new oracle.jdbc.driver.OracleDriver()).defaultConnection();

        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();
  }
}

 

 

》》关于THIN和OCI的性能问题，据传OCI性能优于THIN，并无见官方说明，但经本人粗略测试并没有明显的优势，有时OCI反而低于THIN（但为了支持oracle的RAC模式，官方称需OCI），粗略测试环境及数据如下：

 

测试环境：主机 222_idep（HP-UX rp3440 B.11.23 U 9000/800 (td)） 数据库 222_ora9i2
插入速度测试数据
模式	驱动模式	数据量	2w	2w	10w	10w
原始jdbc模式	thin	时间(速率)	17秒(1176条/秒)	18秒(1111条/秒)	1.26分(1162条/秒)	1.26分(1162条/秒)
oci	18秒(1111条/秒)	18秒(1111条/秒)	1.25分(1176条/秒)	1.28分(1136条/秒)
JdbcTemplate模式	thin	24秒(833条/秒)	24秒(833条/秒)	1.59分(840条/秒)	1.54分(877条/秒)
oci	29秒(689条/秒)	29秒(689条/秒)	2.33分(653条/秒)	2.21分(709条/秒)

    接下来是关于thin及oci两种驱动模式的详细比较（摘录自http://www.51testing.com/?uid-127689-action-viewspace-itemid-185254）

引用

Oracle provides four different types of JDBC drivers, for use in different deployment scenarios. The 10.1.0 drivers can access Oracle 8.1.7 and higher. While all Oracle JDBC drivers are similar, some features apply only to JDBC OCI drivers and some apply only to the JDBC Thin driver. 

JDBC OCI client-side driver: This is a JDBC Type 2 driver that uses Java native methods to call entrypoints in an underlying C library. That C library, called OCI (Oracle Call Interface), interacts with an Oracle database. The JDBC OCI driver requires an Oracle client installation of the same version as the driver. 

The use of native methods makes the JDBC OCI driver platform. specific. Oracle supports Solaris, Windows, and many other platforms. This means that the Oracle JDBC OCI driver is not appropriate for Java applets, because it depends on a C library. 

Starting from 10.1.0, the JDBC OCI driver is available for install with the OCI Instant Client feature, which does not require a complete Oracle client-installation. Please refer to Oracle Call Interface for more information. 

JDBC Thin client-side driver: This is a JDBC Type 4 driver that uses Java to connect directly to Oracle. It implements Oracle's SQL*Net Net8 and TTC adapters using its own TCP/IP based Java socket implementation. The JDBC Thin driver does not require Oracle client software to be installed, but does require the server to be configured with a TCP/IP listener. 

Because it is written entirely in Java, this driver is platform-independent. The JDBC Thin driver can be downloaded into any browser as part of a Java application. (Note that if running in a client browser, that browser must allow the applet to open a Java socket connection back to the server.) 

JDBC Thin server-side driver: This is another JDBC Type 4 driver that uses Java to connect directly to Oracle. This driver is used internally within the Oracle database. This driver offers the same functionality as the client-side JDBC Thin driver (above), but runs inside an Oracle database and is used to access remote databases. 

Because it is written entirely in Java, this driver is platform-independent. There is no difference in your code between using the Thin driver from a client application or from inside a server. 

连接方式有以下几种： 

Oralce provides four types of JDBC driver. 

Thin Driver, a 100% Java driver for client-side use without an Oracle installation, particularly with applets. The Thin driver type is thin. To connect user scott with password tiger to a database with SID (system identifier) orcl through port 1521 of host myhost, using the Thin driver, you would write : 
Connection conn = DriverManager.getConnection 
("jdbc:oracle:thin:@myhost:1521:orcl", "scott", "tiger"); 

OCI Driver for client-side use with an Oracle client installation. The OCI driver type is oci. To connect user scott with password tiger to a database with SID (system identifier) orcl through port 1521 of host myhost, using the OCI driver, you would write : 
Connection conn = DriverManager.getConnection 
("jdbc:oracle:oci:@myhost:1521:orcl", "scott", "tiger"); 

Note that you can also specify the database by a TNSNAMES entry. You can find the available TNSNAMES entries listed in the file tnsnames.ora on the client computer from which you are connecting. For example, if you want to connect to the database on host myhost as user scott with password tiger that has a TNSNAMES entry of MyHostString, enter: 
Connection conn = DriverManager.getConnection 
("jdbc:oracle:oci8:@MyHostString","scott","tiger"); 

If your JDBC client and Oracle server are running on the same machine, the OCI driver can use IPC (InterProcess Communication) to connect to the database instead of a network connection. An IPC connection is much faster than a network connection. 
Connection conn = DriverManager.getConnection 
("jdbc:oracle:oci8:@","scott","tiger"); 

Server-Side Thin Driver, which is functionally the same as the client-side Thin driver, but is for code that runs inside an Oracle server and needs to access a remote server, including middle-tier scenarios. The Server-Side Thin driver type is thin and there is no difference in your code between using the Thin driver from a client application or from inside a server. 
Server-Side Internal Driver for code that runs inside the target server, that is, inside the Oracle server that it must access. The Server-Side Internal driver type is kprb and it actually runs within a default session. You are already "connected". Therefore the connection should never be closed. 
To access the default connection, write: 
DriverManager.getConnection("jdbc:oracle:kprb:"); 
or: 
DriverManager.getConnection("jdbc:default:connection:"); 

You can also use the Oracle-specific defaultConnection() method of the OracleDriver class which is generally recommended: 
OracleDriver ra = new OracleDriver(); 
Connection conn = ora.defaultConnection(); 

Note: You are no longer required to register the OracleDriver class for connecting with the Server-Side Internal driver, although there is no harm in doing so. This is true whether you are using getConnection() or defaultConnection() to make the connection. 
Any user name or password you include in the URL string is ignored in connecting to the server default connection. The DriverManager.getConnection() method returns a new Java Connection object every time you call it. Note that although the method is not creating a new physical connection (only a single implicit connection is used), it is returning a new object. 
Again, when JDBC code is running inside the target server, the connection is an implicit data channel, not an explicit connection instance as from a client. It should never be closed. 
这下基本明白了 
1）从使用上来说，oci必须在客户机上安装oracle客户端或才能连接，而thin就不需要，因此从使用上来讲thin还是更加方便，这也是thin比较常见的原因。 
2）原理上来看，thin是纯java实现tcp/ip的c/s通讯；而oci方式,客户端通过native java method调用c library访问服务端，而这个c library就是oci(oracle called interface)，因此这个oci总是需要随着oracle客户端安装（从oracle10.1.0开始，单独提供OCI Instant Client，不用再完整的安装client） 
3）它们分别是不同的驱动类别，oci是二类驱动， thin是四类驱动，但它们在功能上并无差异。 
4）虽然很多人说oci的速度快于thin，但找了半天没有找到相关的测试报告。 

 

 所谓智者见智，仁者见仁，以上只是随笔~~