透過JDBC操作oracle資料庫

本文講解如何實作存取oracle資料庫。

Import librarie(s)

import java.sql.*;

1	import java.sql.*;

Class

設定Connection為連線後產生之連線物件。

public class oracleDB {
    public Connection conn = null;
}

public class oracleDB {

public Connection conn = null;

}

Connect function

實例化oracleDB物件後，透過此function設定Connection。


//Connect func
public boolean CONNECT_ORACLE() {
    // Load the JDBC driver
    String driverName = "oracle.jdbc.driver.OracleDriver";
    try {
        Class.forName(driverName);
        // Create a connection to the database
        String serverName = "xx.xx.xx.xx";
        String portNumber = "1521"; // fill in your database port (ex:1521)
        String sid = "mysid";
        String url = "jdbc:oracle:thin:@" + serverName + ":" + portNumber + ":" + sid;
        String username = "myUserName";
        String password = "myPassword";
        // set the connection object
        conn = DriverManager.getConnection(url, username, password);
        System.out.println("Database connected.");
        return true;
    } catch (ClassNotFoundException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
        return false;
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
        return false;
    }
}

//Connect func

public boolean CONNECT_ORACLE() {

// Load the JDBC driver

String driverName = "oracle.jdbc.driver.OracleDriver";

try {

Class.forName(driverName);

// Create a connection to the database

String serverName = "xx.xx.xx.xx";

String portNumber = "1521"; // fill in your database port (ex:1521)

String sid = "mysid";

String url = "jdbc:oracle:thin:@" + serverName + ":" + portNumber + ":" + sid;

String username = "myUserName";

String password = "myPassword";

// set the connection object

conn = DriverManager.getConnection(url, username, password);

System.out.println("Database connected.");

return true;

} catch (ClassNotFoundException e) {

// TODO Auto-generated catch block

e.printStackTrace();

return false;

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

return false;

}

Implement the DML in Java

實作DML，這邊使用SELECT簡易示範。
透過上方connect function連線成功後，將connection參數傳入設計的指令function。
這邊示範獲取table欄位後回傳ArrayList<String[]>。
範例資料庫table設計如下：

COL1	COL2	COL3
test1	aaa	bbb
test2	ccc	ddd


public ArrayList<String[]> SELECT_TABLE(Connection conn, String TABLENAME) throws SQLException{
    Statement stmt = conn.createStatement();
    String SELECT_STAT = "SELECT * FROM " + TABLENAME;
    ResultSet rows = stmt.executeQuery(SELECT_STAT);
    ArrayList<String[]> rows_db = new ArrayList<String[]>();
    int count=0;
    while (rows.next()) {
        // Assume we have 7 columns in this table
        String[] dataArr = new String[7];
        String colStr = "";
        count+=1;
        for(int i=0;i<3;i++){
            colStr = "COL";
            colStr += Integer.toString(i+1);
            dataArr[i] = rows.getString(colStr);
        }
        rows_db.add(dataArr);
    }
    // release the statement resource
    stmt.close();
    if(count==0){
        System.out.println("No data.\n");
        return null;
    }else{
        return rows_db;
    }
}

public ArrayList<String[]> SELECT_TABLE(Connection conn, String TABLENAME) throws SQLException{

Statement stmt = conn.createStatement();

String SELECT_STAT = "SELECT * FROM " + TABLENAME;

ResultSet rows = stmt.executeQuery(SELECT_STAT);

ArrayList<String[]> rows_db = new ArrayList<String[]>();

int count=0;

while (rows.next()) {

// Assume we have 7 columns in this table

String[] dataArr = new String[7];

String colStr = "";

count+=1;

for(int i=0;i<3;i++){

colStr = "COL";

colStr += Integer.toString(i+1);

dataArr[i] = rows.getString(colStr);

}

rows_db.add(dataArr);

}

// release the statement resource

stmt.close();

if(count==0){

System.out.println("No data.\n");

return null;

}else{

return rows_db;

}

使用Statement宣告將傳送出的指令容器。
設計DML指令字串。
利用Statement中的executeQuery方法，傳入指令字串，取得回傳之ResultSet。
解析ResultSet，利用ResultSet.next()逐行解析。

Advanced Statement

Statement若較為複雜，可利用下面這種方法設計。


String INSERT_STAT="INSERT INTO TABLE VALUES (?,?,?)";
PreparedStatement pstmt = conn.prepareStatement(INSERT_STAT); 
pstmt.setString(1,  data[0]);
pstmt.setString(2,  data[1]);
pstmt.setString(3,  data[2]);
try{
    pstmt.executeUpdate();
    pstmt.close();
    return true;
}catch(SQLException e){
    System.err.println(e);
    pstmt.close();
    return false;
}

String INSERT_STAT="INSERT INTO TABLE VALUES (?,?,?)";

PreparedStatement pstmt = conn.prepareStatement(INSERT_STAT);

pstmt.setString(1, data[0]);

pstmt.setString(2, data[1]);

pstmt.setString(3, data[2]);

try{

pstmt.executeUpdate();

pstmt.close();

return true;

}catch(SQLException e){

System.err.println(e);

pstmt.close();

return false;

}

PreparedStatement可以先將statement架構先建好，等參數準備好後再將其填入。此種方法常用於製作動態指令。

Close your resources

不管是Connection或是Statement操作完畢，務必將資源釋放。


statement.close();
connection.close();

statement.close();

connection.close();

[Java] 透過JDBC操作oracle資料庫

Import librarie(s)

Class

Connect function

Implement the DML in Java

Advanced Statement

Close your resources

留言

撰寫回覆或留言取消回覆

[Java] 透過JDBC操作oracle資料庫

Import librarie(s)

Class

Connect function

Implement the DML in Java

Advanced Statement

Close your resources

留言

撰寫回覆或留言 取消回覆

撰寫回覆或留言取消回覆