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