Connect to Oracle with Java

Java 提供 JDBC API 來連接資料庫，但要連接到各式各樣的資料庫就要透過各廠商提供的JDBC DRIVER來處理了。

這篇是介紹Java如何連接到Oracle。相關的Oracle JDBC DRIVER請至Oracle下載。


Oracle提供了三種JDBC DRIVER：
1. JDBC Thin Driver (no local SQL*Net installation required/ handy for applets)
2. JDBC OCI for writing stand-alone Java applications
3. JDBC KPRB driver (default connection) for Java Stored Procedures and Database JSPs.
三種都提供相同的syntax and APIs。唯一要注意的是要找對應你的JDK版本來下載，不然會連不上。

Thin Driver
Thin Driver是百分百純JAVA打造，透過SOCKET(TCP/IP)的方式來連接資料庫。
有兩種URL syntax
舊的，只對SID有用：
jdbc:oracle:thin:@[HOST][:PORT]:SID
新的，則多了SERVICE NAME
jdbc:oracle:thin:[USER/PASSWORD]@[HOST][:PORT]:SID
jdbc:oracle:thin:[USER/PASSWORD]@//[HOST][:PORT]/SERVICE

例：
String url = "jdbc:oracle:thin:@//yourhost:yourport/orcl";
or
String url = "jdbc:oracle:thin:@yourhost:yourport:orcl";

如果不知道SID或SERVICE NAME，可以在tnsnames.ora檔案中找到它們。
XE =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521))
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = XE)
  )
 )

ORCL =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521))
   (CONNECT_DATA =
     (SID = ORCL)
   )
 )


範例(ConnThin.java)：
import java.sql.*;

class ConnThin {
  public static void main (String args []) {
    Connection conn = null;

    try {
      // Load the JDBC driver
      String driverName = "oracle.jdbc.driver.OracleDriver";
      Class.forName(driverName);

      // Create a connection to the database
      String serverName = "yourhost";
      String portNumber = "yourport";
      String sid = "yourdatabase";
      String url = "jdbc:oracle:thin:@" + serverName + ":" + portNumber + ":" + sid;
      String username = "username";
      String password = "password";
      conn = DriverManager.getConnection(url, username, password);
      System.out.println("Database connected.");

    } catch (ClassNotFoundException e) {
      // Could not find the database driver
      System.err.println(e.getMessage());
    } catch (SQLException e) {
      // Could not connect to the database
      System.err.println(e.getMessage());
    }
    finally
    {
      try
      {
        if(conn != null)
          conn.close();
      }
      catch(SQLException e)
      {
        // connection close failed.
        System.err.println(e);
      }
    }
  }
}


OCI Driver
OCI Driver是透過Oracle Call Interface來連接Oracle。
底下為Oracle提供的範例：
import java.sql.*;
class dbAccess {
  public static void main (String args []) throws Exception
  {
    Class.forName ("oracle.jdbc.OracleDriver");

    Connection conn = DriverManager.getConnection
      ("jdbc:oracle:oci8:@hostname_orcl", "scott", "tiger");
         // or oci7 @TNSNames_Entry, userid, password
    try {
      Statement stmt = conn.createStatement();
    try {
      ResultSet rset = stmt.executeQuery("select BANNER from SYS.V_$VERSION");
    try {
      while (rset.next())
        System.out.println (rset.getString(1)); // Print col 1
    } finally {
      try { rset.close(); } catch (Exception ignore) {}
    }
    } finally {
      try { stmt.close(); } catch (Exception ignore) {}
    }
    } finally {
      try { conn.close(); } catch (Exception ignore) {}
    }
  }
}

KPRB driver
KPRB driver
KPRB Driver是透過資料庫現有或預設的SESSION來連線，不需要額外的URL、USERNAME and PASSWORD。

底下為Oracle提供的範例：
import java.sql.*;
class dbAccess {
  public static void main (String args []) throws SQLException
  {
    Connection conn = (new oracle.jdbc.OracleDriver()).defaultConnection();

    try {
      Statement stmt = conn.createStatement();
    try {
      ResultSet rset = stmt.executeQuery("select BANNER from SYS.V_$VERSION");
    try {
      while (rset.next())
        System.out.println (rset.getString(1)); // Print col 1
    } finally {
      try { rset.close(); } catch (Exception ignore) {}
    }
    } finally {
      try { stmt.close(); } catch (Exception ignore) {}
    }
    } finally {
      try { conn.close(); } catch (Exception ignore) {}
    }
  }
}

雖然Oracle提供了三種JDBC Driver，但最常用還是以Thin Driver為主，準確的提供資料庫連接資訊就可以透過SOCKET來連接，簡單明瞭。