JDBC - 儲存過程

上一頁
下一頁

在討論JDBC - 語句章節時，我們學習瞭如何在JDBC中使用儲存過程。本章節與該部分內容類似，但會提供關於JDBC SQL轉義語法的更多資訊。

正如Connection物件建立Statement和PreparedStatement物件一樣，它也建立CallableStatement物件，該物件用於執行對資料庫儲存過程的呼叫。

建立CallableStatement物件

假設，您需要執行以下Oracle儲存過程：

CREATE OR REPLACE PROCEDURE getEmpName 
   (EMP_ID IN NUMBER, EMP_FIRST OUT VARCHAR) AS
BEGIN
   SELECT first INTO EMP_FIRST
   FROM Employees
   WHERE ID = EMP_ID;
END;

注意 - 以上儲存過程是為Oracle編寫的，但我們使用的是MySQL資料庫，因此讓我們編寫以下MySQL儲存過程，以便在EMP資料庫中建立它。

DELIMITER $$

DROP PROCEDURE IF EXISTS `EMP`.`getEmpName` $$
CREATE PROCEDURE `EMP`.`getEmpName` 
   (IN EMP_ID INT, OUT EMP_FIRST VARCHAR(255))
BEGIN
   SELECT first INTO EMP_FIRST
   FROM Employees
   WHERE ID = EMP_ID;
END $$

DELIMITER ;

存在三種類型的引數：IN、OUT和INOUT。PreparedStatement物件僅使用IN引數。CallableStatement物件可以使用所有三種引數。

以下是每種引數的定義：

引數 描述 示例
IN 在建立SQL語句時值未知的引數。您可以使用setXXX()方法將值繫結到IN引數。 僅包含IN引數的儲存過程
OUT 由SQL語句返回其值的引數。您可以使用getXXX()方法從OUT引數檢索值。 僅包含OUT引數的儲存過程
INOUT 同時提供輸入和輸出值的引數。您可以使用setXXX()方法繫結變數，並使用getXXX()方法檢索值。 同時包含IN和OUT引數的儲存過程

以下程式碼片段顯示瞭如何使用Connection.prepareCall()方法基於前面的儲存過程例項化CallableStatement物件：

CallableStatement cstmt = null;
try {
   String SQL = "{call getEmpName (?, ?)}";
   cstmt = conn.prepareCall (SQL);
   . . .
}
catch (SQLException e) {
   . . .
}
finally {
   . . .
}

字串變數SQL表示儲存過程，其中包含引數佔位符。

使用CallableStatement物件與使用PreparedStatement物件非常相似。您必須在執行語句之前將值繫結到所有引數，否則將收到SQLException。

如果您有IN引數，只需遵循適用於PreparedStatement物件的相同規則和技術；使用與您正在繫結的Java資料型別對應的setXXX()方法。

當您使用OUT和INOUT引數時，必須使用附加的CallableStatement方法registerOutParameter()。registerOutParameter()方法將JDBC資料型別繫結到儲存過程預期返回的資料型別。

呼叫儲存過程後，您可以使用適當的getXXX()方法從OUT引數檢索值。此方法將檢索到的SQL型別的值轉換為Java資料型別。

關閉CallableStatement物件

正如您關閉其他Statement物件一樣，出於同樣的原因，您也應該關閉CallableStatement物件。

只需呼叫close()方法即可。如果您首先關閉Connection物件，它也會關閉CallableStatement物件。但是，您應該始終顯式關閉CallableStatement物件以確保正確的清理。

CallableStatement cstmt = null;
try {
   String SQL = "{call getEmpName (?, ?)}";
   cstmt = conn.prepareCall (SQL);
   . . .
}
catch (SQLException e) {
   . . .
}
finally {
   cstmt.close();
}

我們在Callable - 示例程式碼中學習了更多細節。

JDBC SQL轉義語法

轉義語法使您可以靈活地使用透過使用標準JDBC方法和屬性無法使用的資料庫特定功能。

一般的SQL轉義語法格式如下：

{keyword 'parameters'}

以下是一些轉義序列，您在執行JDBC程式設計時會發現它們非常有用：

d、t、ts關鍵字

它們有助於標識日期、時間和時間戳文字。眾所周知，沒有兩個DBMS以相同的方式表示時間和日期。此轉義語法告訴驅動程式以目標資料庫的格式呈現日期或時間。例如：

>
{d 'yyyy-mm-dd'}

其中yyyy=年，mm=月；dd=日。使用此語法{d '2009-09-03'}是2009年3月9日。

這是一個簡單的示例，顯示如何將日期插入表中：

//Create a Statement object
stmt = conn.createStatement();
//Insert data ==> ID, First Name, Last Name, DOB
String sql="INSERT INTO STUDENTS VALUES" +
             "(100,'Zara','Ali', {d '2001-12-16'})";

stmt.executeUpdate(sql);

類似地，您可以使用以下兩種語法之一，即t或ts：

{t 'hh:mm:ss'}

其中hh=小時；mm=分鐘；ss=秒。使用此語法{t '13:30:29'}是下午1:30:29。

{ts 'yyyy-mm-dd hh:mm:ss'}

這是上面兩種語法'd'和't'的組合語法，用於表示時間戳。

escape關鍵字

此關鍵字標識在LIKE子句中使用的跳脫字元。在使用SQL萬用字元%（匹配零個或多個字元）時很有用。例如：

String sql = "SELECT symbol FROM MathSymbols
              WHERE symbol LIKE '\%' {escape '\'}";
stmt.execute(sql);

如果您使用反斜槓字元(\)作為跳脫字元，則還必須在Java字串文字中使用兩個反斜槓字元，因為反斜槓也是Java跳脫字元。

fn關鍵字

此關鍵字表示在DBMS中使用的標量函式。例如，您可以使用SQL函式length來獲取字串的長度：

{fn length('Hello World')}

這將返回11，即字元字串'Hello World'的長度。

call關鍵字

此關鍵字用於呼叫儲存過程。例如，對於需要IN引數的儲存過程，請使用以下語法：

{call my_procedure(?)};

對於需要IN引數並返回OUT引數的儲存過程，請使用以下語法：

{? = call my_procedure(?)};

oj關鍵字

此關鍵字用於表示外部連線。語法如下：

{oj outer-join}

其中outer-join = 表{LEFT|RIGHT|FULL} OUTERJOIN{表|outer-join}on search-condition。例如：

String sql = "SELECT Employees 
              FROM {oj ThisTable RIGHT
              OUTER JOIN ThatTable on id = '100'}";
stmt.execute(sql);

在SQL命令示例中使用like語句

在這個例子中，我們有四個靜態字串，包含資料庫連線URL、使用者名稱、密碼和帶有LIKE運算子和特殊字元的SELECT查詢，用於選擇多個條目。現在，使用DriverManager.getConnection()方法，我們準備了一個數據庫連線。連線準備就緒後，我們使用connection.createStatement()方法建立了一個Statement物件。然後使用statement.executeQuery()執行SELECT查詢，並將結果儲存在結果集中。然後在迭代結果集時列印所有記錄。

將以下示例複製並貼上到ResultSetExample.java中，編譯並執行如下：

import java.sql.*;

// This class demonstrates the use of mySQL 'like'
public class MySQLFunctionExample {

    static final String DB_URL = "jdbc:mysql:///TUTORIALSPOINT";
    static final String USER = "guest";
    static final String PASS = "guest123";
    //Select addresses which contain ','
    static final String QUERY = "select * from students where address like '%,%'";
    
    public static void main(String args[]) {
        Connection conn = null;
        Statement stmt = null;        
        try{            
             conn = DriverManager.getConnection(DB_URL,USER,PASS);
             System.out.println("Connection to db  established.");
             
             stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(QUERY);
             
             System.out.println("Successfully executed query.");
             System.out.println("----------------------------");
             
             while(rs.next()){
                System.out.print("StudentID: " + rs.getInt(1));
                System.out.print(", FirstName: " + rs.getString(2));
                System.out.print(", LastName: " + rs.getString(3) );
                System.out.print(", Address: " + rs.getString(4));
             }
      
             rs.close();
             stmt.close();
            conn.close();
        }catch( SQLException e){
            e.printStackTrace();
        }
    }
}

輸出

現在讓我們按如下方式編譯以上示例：

C:\>javac MySQLFunctionExample.java
C:\>

執行MySQLFunctionExample時，它會產生以下結果：

C:\>java MySQLFunctionExample
Connection to db  established.
Successfully executed query.
----------------------------
StudentID: 1005, FirstName: Kumar, LastName: Kishore, Address: 49 Gorakhpur Rd., Agra
StudentID: 1006, FirstName: Khan, LastName: Ganesh, Address: 34 Digha Rd., Digha
  
C:\>

在SQL命令示例中使用函式Length()

在這個例子中，我們有四個靜態字串，包含資料庫連線URL、使用者名稱、密碼和一個SELECT查詢，其中Length()函式計算姓氏的長度。現在，使用DriverManager.getConnection()方法，我們準備了一個數據庫連線。連線準備就緒後，我們使用connection.createStatement()方法建立了一個Statement物件。然後使用statement.executeQuery()執行SELECT查詢，並將結果儲存在結果集中。然後在迭代結果集時列印所有記錄。

將以下示例複製並貼上到ResultSetExample.java中，編譯並執行如下：

import java.sql.*;

// This class demonstrates the use of mySQL 'like'
public class MySQLFunctionExample {

    static final String DB_URL = "jdbc:mysql:///TUTORIALSPOINT";
    static final String USER = "guest";
    static final String PASS = "guest123";
    //Select addresses which contain ','
    static final String QUERY = "select StudentID, LastName, length(LastName) AS LengthOfLastName from students";
    
    public static void main(String args[]) {
        Connection conn = null;
        Statement stmt = null;        
        try{            
             conn = DriverManager.getConnection(DB_URL,USER,PASS);
             System.out.println("Connection to db  established.");
             
             stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(QUERY);
             
             System.out.println("Successfully executed query.");
             System.out.println("----------------------------");
             
             while(rs.next()){
                System.out.print("StudentID: " + rs.getInt(1));
                System.out.print(", LastName: " + rs.getString(2));
                System.out.println(", LengthOfLastName: " + rs.getInt(3) );
             }
      
             rs.close();
             stmt.close();
            conn.close();
        }catch( SQLException e){
            e.printStackTrace();
        }
    }
}

輸出

現在讓我們按如下方式編譯以上示例：

C:\>javac MySQLFunctionExample.java
C:\>

執行MySQLFunctionExample時，它會產生以下結果：

C:\>java MySQLFunctionExample
Connection to db  established.
Successfully executed query.
----------------------------
StudentID: 1000, LastName: Agarwal, LengthOfLastName: 7
StudentID: 1001, LastName: Pandey, LengthOfLastName: 6
StudentID: 1002, LastName: Kumar, LengthOfLastName: 5
StudentID: 1004, LastName: Ali, LengthOfLastName: 3
StudentID: 1005, LastName: Kumar, LengthOfLastName: 5
StudentID: 1006, LastName: Khan, LengthOfLastName: 4
  
C:\>
列印頁面
上一頁
下一頁
廣告