6月 26, 2013

透過 JDBC 連線 Oracle 資料庫

要透過 Java JDBC 連線至 Oracle 資料庫,有幾個要注意的地方:

首先搞清楚連線對象是 "SID" 或 "Service Name" 因為兩者使用語法並不相同,其中:

1. SID (nique name of the INSTANCE)
jdbc:oracle:thin:[USER/PASSWORD]@[HOST][:PORT]:SID
2. Service Name (Alias to an INSTANCE)
jdbc:oracle:thin:[USER/PASSWORD]@//[HOST][:PORT]/SERVICE
實際例子:
Connection conn = null;
conn = DriverManager.getConnection
       ("jdbc:oracle:thin:ID/PASS//HOST_IP:1521/SERVICE_NAME"); 
再來,包裝你想送出的查詢
String StrX = "SELECT * FROM FOO";
StrX = StrX + "WHERE BAR=15";

Statement myQuery = conn.createStatement();
ResultSet Output = myQuery.executeQuery(StrX);

while( Output.next() ) /* 透過迴圈取得資料 */
{
  System.out.println ( 
    Output.getString(1) + "," + Output.getString(2)+ "," +
    Output.getString(3) + "," + Output.getString(4)
  );
}

Output.close();
myQuery.close();
conn.close();
最後必須有一段程式碼,來處理例外狀況 (Java編譯時會偵測)
catch (ClassNotFoundException e) 
{ 
  System.err.println(e.getMessage()); 
}
catch (SQLException e) 
{ 
  System.err.println(e.getMessage()); 
} 
finally 
{ 
  try 
  { 
    if(conn != null) 
      conn.close(); 
  } 
  catch(SQLException e) 
  { 
    System.err.println(e); 
  } 
} 

沒有留言:

張貼留言