Statement
PreparedStatement
CallableStatement PreparedStatement物件允許在SQL語句中為變數嵌入佔位符，與SQLJ中使用宿主變數類似，但是它沒有直接在SQL語句中指定JAVA宿主變數，而是用問號（?）表示每個變數，然後通過索引呼叫設定方法來設定每個變數的數值，這稱為引數化SQL語句． PreparedStatement物件只能用來執行惟一一條SQL語句
Statement比較簡單，效率較高，但是引數所書寫過於繁索．
CallableStatement與PreparedStatement相似，只是它只用在呼叫函式和儲存過程． Oracle有兩種語法實現CallableStatement有兩種實現方法：PL/SQL塊語法和標準的SQL-92語法．其中的SQL-92語法裡的call只能是小寫的！ import java.sql.*;
import oracle.jdbc.driver.*; public class BB {
private static Connection conn;
public BB() {
} public static void main(String[] args){
Statement stmt;
ResultSet rst;
PreparedStatement pstmt,pstmt1;
CallableStatement cstmt,cstmt1,cstmt2,cstmt3;
/*
new oracle.jdbc.OracleDriver();
jdbc:oracle:thin:@hostname:port:database
*/ try{
DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
conn = DriverManager.getConnection("jdbc:oracle:thin:@ruang:1521:orcl","xling","fp51gfa"); stmt = conn.createStatement();
rst = stmt.executeQuery("SELECT COUNT(*) FROM XLING_APP_GUID");
if(rst.next())
System.out.println(rst.getInt(1)); rst.close(); pstmt = conn.prepareStatement("UPDATE XLING_USER SET ID = ? WHERE ID = ?");
pstmt.setString(1,"xling");
pstmt.setString(2,"AAA");
pstmt.executeUpdate(); stmt.execute("DELETE FROM XLING_USER WHERE ID = 'fairy'");
pstmt1 = conn.prepareStatement("INSERT INTO XLING_USER (ID,DESP,PWD) VALUES (?,?,?)");
pstmt1.setString(1,"fairy");
pstmt1.setString(2,"王濤");
pstmt1.setString(3,"werewi");
pstmt1.execute();
/*
PL/SQL塊語法：注意，要完全符合PL/SQL語法，Oracle的VARCHAR2不是標準的SQL型別，所以使用Types.CHAR
*/
cstmt = conn.prepareCall("begin ? := xling_pkg_tools.rc4(?); end;");
cstmt.registerOutParameter(1,Types.CHAR);
cstmt.setString(2,"fp51gfa");
cstmt.execute();
System.out.println(cstmt.getString(1));
/*
SQL-92語法，呼叫Oracle裡的函式,注意：call只能小寫
*/
cstmt1 = conn.prepareCall("{? = call XLING_PKG_TOOLS.RC4(?)}");
cstmt1.registerOutParameter(1,Types.CHAR);
cstmt1.setString(2,"xling");
cstmt1.execute();
System.out.println(cstmt1.getString(1));
/*
用SQL-92格式呼叫Oracle裡的過程，第二個引數為OUT型別的引數
CREATE OR REPLACE PROCEDURE PRO_TEST(I_DATE_STRING IN VARCHAR2,O_DAYS OUT NUMBER) AS
BEGIN
SELECT SYSDATE - TO_DATE(I_DATE_STRING,'YYYYMMDD') INTO O_DAYS FROM DUAL;
END;
*/
cstmt2 = conn.prepareCall("{call PRO_TEST(?,?)}");
cstmt2.setString(1,"20060901");
cstmt2.registerOutParameter(2,Types.INTEGER);
cstmt2.execute();
System.out.println(cstmt2.getInt(2));
/*
用PL/SQL格式呼叫Oracle裡的過程
*/
cstmt3 = conn.prepareCall("begin PRO_TEST(?,?); END;");
cstmt3.setString(1,"20060101");
cstmt3.registerOutParameter(2,Types.INTEGER);
cstmt3.execute();
System.out.println(cstmt3.getInt(2));
cstmt3.close();
cstmt2.close();
cstmt1.close();
cstmt.close();
pstmt.close();
pstmt1.close();
stmt.close();
conn.close();
System.out.println("here");
}catch(SQLException e){
//e.printStackTrace();
System.out.println("ErrorCode:" + e.getErrorCode());//錯誤號
System.out.println("Message:" + e.getMessage());
StackTraceElement[] el = e.getStackTrace();
for(int i = 0;i System.out.println("錯誤行號：" + el[i].getLineNumber());
System.out.println("所在方法：" + el[i].getMethodName());
System.out.println("所在類：" + el[i].getClassName());
System.out.println("所在檔案：" + el[i].getFileName());
System.out.println();
}
}
}
} |