JDBC驱动oci和thin区别

thin和oci和oracle数据库系统的架构图

JDBC Thin Driver

The JDBC Thin driver is a pure Java, Type IV driver that can be used in applications and applets. It is platform-independent and does not require any additional Oracle software on the client-side. The JDBC Thin driver communicates with the server using SQL*Net to access Oracle Database.

The JDBC Thin driver allows a direct connection to the database by providing an implementation of SQL*Net on top of Java sockets. The driver supports the TCP/IP protocol and requires a TNS listener on the TCP/IP sockets on the database server.

JDBC OCI Driver

The JDBC OCI driver is a Type II driver used with Java applications. It requires an Oracle client installation and, therefore, is Oracle platform-specific. It supports all installed Oracle Net adapters, including interprocess communication (IPC), named pipes, TCP/IP, and Internetwork Packet Exchange/Sequenced Packet Exchange (IPX/SPX).

The JDBC OCI driver, written in a combination of Java and C, converts JDBC invocations to calls to OCI, using native methods to call C-entry points. These calls communicate with the database using SQL*Net.

The JDBC OCI driver uses the OCI libraries, C-entry points, Oracle Net, core libraries, and other necessary files on the client computer where it is installed.

从官网上的描述可以看出来thin和oci的区别

1. thin 底层是通过tcp/ip协议实现的。oci是通过调用oci客户端c动态库实现的。
2. oci jdbc使用之前必须要安装oci 的客户端，所以我们通常会选择thin驱动来连接oracle数据库。

3. 理论上oci jdbc驱动要优与thin的驱动。

   下面写一个简单的例子来测试一下thin导出文本文件和oci导出文本文件的效率。

import java.io.BufferedWriter;import java.io.File;import java.io.FileNotFoundException;import java.io.FileOutputStream;import java.io.IOException;import java.io.OutputStreamWriter;import java.io.UnsupportedEncodingException;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.text.DateFormat;import java.util.ArrayList;import java.util.Date;import java.util.List;public class OracleThin {    private static BufferedWriter bw = null;       public static void  main(String[] args) throws SQLException {        long begin = 0;        int index = 0;        long count = 0;         String  sql = "select * from subs";        Connection conn = connect();          open();        System.out.println("start time:" + DateFormat.getDateTimeInstance(DateFormat.LONG,DateFormat.LONG).format(new Date()));        while(true) {            begin = index * 100000;            String sqlTmp = getQueryPageSql(sql, begin, 100000);            count = readPageDataAndWrite(conn, sqlTmp);            index++;            if (count < 100000) {                break;            }        }        close();        System.out.println("end time:" + DateFormat.getDateTimeInstance(DateFormat.LONG,DateFormat.LONG).format(new Date()));    }    public static void open() {        File txtFile = new File("D:\\test\\sub.csv");        try {            bw = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(txtFile, true), "utf-8"));        }        catch (UnsupportedEncodingException e) {            // TODO Auto-generated catch block            e.printStackTrace();        }        catch (FileNotFoundException e) {            // TODO Auto-generated catch block            e.printStackTrace();        }    }    public static void close() {        try {            bw.close();        }        catch (IOException e) {            // TODO Auto-generated catch block            e.printStackTrace();        }    }    public static Connection connect() {        Connection conn = null;        try {            Class.forName("oracle.jdbc.driver.OracleDriver");            conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1522:cc","test","test");        }        catch (SQLException e1) {            // TODO Auto-generated catch block            e1.printStackTrace();        }        catch (ClassNotFoundException e) {            // TODO Auto-generated catch block            e.printStackTrace();        }        return conn;    }    public static String getQueryPageSql(final String querySql, final long begin, final long pagesize) {        // order by        long tmpnum = begin + pagesize;        StringBuilder sb = new StringBuilder();        sb.append("SELECT * FROM (");                sb.append("\n\tSELECT T.*,ROWNUM RN FROM (\n\t\t");        sb.append(querySql);        sb.append("\n\t\t) T");        sb.append("\n\tWHERE ROWNUM <=").append(tmpnum);        sb.append("\n) WHERE RN>").append(begin);        return sb.toString();    }    private static long readPageDataAndWrite(Connection conn, String sql) throws SQLException {        ResultSet rst = null;        PreparedStatement pst = null;        long count = 0;        try {            pst = conn.prepareStatement(sql);            rst = pst.executeQuery();            List<String> metaDataType = new ArrayList<String>();            // 增加字段名            if (rst != null) {                StringBuilder sbuilder = new StringBuilder();                for (int i = 0; i < rst.getMetaData().getColumnCount(); i++) {                    sbuilder.append(rst.getMetaData().getColumnName(i + 1));                    sbuilder.append("|");                    metaDataType.add(rst.getMetaData().getColumnClassName(i + 1));                }                sbuilder.delete(sbuilder.length() - 1, sbuilder.length());                sbuilder.append("\r\n");                bw.write(sbuilder.toString());                while (rst.next()) {                    StringBuffer sb = new StringBuffer();                    for (int i = 0; i < rst.getMetaData().getColumnCount(); i++) {                        String value = rst.getString(i+1);                        if (StringUtil.isEmpty(value)) {                            // 查询结果为NULL，转换为""空字符串                            sb.append("");                        }                        else {                            if (String.class.getName().equals(metaDataType.get(i))) {                                sb.append("\"").append(value).append("\"");                            }                             else {                                sb.append(value);                            }                        }                        sb.append("|");                    }                    sb.delete(sb.length() - 1, sb.length());                    sb.append("\r\n");                    count++;                    bw.write(sb.toString());                }            }                  }        catch (SQLException e) {            // TODO Auto-generated catch block            e.printStackTrace();        }        catch (IOException e) {            // TODO Auto-generated catch block            e.printStackTrace();        }        finally {            if (rst != null) {                rst.close();                rst = null;            }            if (pst != null) {                pst.close();                pst = null;             }        }        return count;    }}

    // oci方式只需要修改连接的方式    public static Connection connect() {        Connection conn = null;        try {            Class.forName("oracle.jdbc.driver.OracleDriver");            conn = DriverManager.getConnection("jdbc:oracle:oci:@10.45.4.170:1522:cc","test","test");        }        catch (SQLException e1) {            // TODO Auto-generated catch block            e1.printStackTrace();        }        catch (ClassNotFoundException e) {            // TODO Auto-generated catch block            e.printStackTrace();        }        return conn;    }