Java中的CallableStatement示例

教學

Java

在Java中，CallableStatement用于从Java程序中调用存储过程。存储过程是一组我们在数据库中编译的语句，用于执行某些任务。当我们需要处理涉及多个表的复杂场景时，存储过程非常有用。与将多个查询发送到数据库不同，我们可以将所需数据发送到存储过程，并在数据库服务器中执行逻辑。

CallableStatement

JDBC API提供了通过CallableStatement接口执行存储过程的支持。存储过程需要使用特定于数据库的语法编写，在本教程中，我将使用Oracle数据库。我们将研究CallableStatement的标准功能，包括IN和OUT参数。稍后，我们将查看Oracle特定的STRUCT和Cursor示例。首先，让我们使用以下SQL查询语句为我们的CallableStatement示例程序创建一个表。create_employee.sql

-- 对于Oracle数据库
CREATE TABLE EMPLOYEE
  (
    "EMPID"   NUMBER NOT NULL ENABLE,
    "NAME"    VARCHAR2(10 BYTE) DEFAULT NULL,
    "ROLE"    VARCHAR2(10 BYTE) DEFAULT NULL,
    "CITY"    VARCHAR2(10 BYTE) DEFAULT NULL,
    "COUNTRY" VARCHAR2(10 BYTE) DEFAULT NULL,
    PRIMARY KEY ("EMPID")
  );

讓我們首先創建一個實用類來獲取 Oracle 數據庫連接對象。確保 Oracle OJDBC jar 已添加到項目的構建路徑中。 DBConnection.java

package com.journaldev.jdbc.storedproc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DBConnection {

	private static final String DB_DRIVER_CLASS = "oracle.jdbc.driver.OracleDriver";
	private static final String DB_URL = "jdbc:oracle:thin:@localhost:1521:orcl";
	private static final String DB_USERNAME = "HR";
	private static final String DB_PASSWORD = "oracle";
	
	public static Connection getConnection() {
		Connection con = null;
		try {
			// 加載驅動類
			Class.forName(DB_DRIVER_CLASS);

			// 創建連接
			con = DriverManager.getConnection(DB_URL,DB_USERNAME,DB_PASSWORD);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return con;
	}
}

CallableStatement 示例

讓我們編寫一個簡單的存儲過程，將數據插入到 Employee 表中。 insertEmployee.sql

CREATE OR REPLACE PROCEDURE insertEmployee
(in_id IN EMPLOYEE.EMPID%TYPE,
 in_name IN EMPLOYEE.NAME%TYPE,
 in_role IN EMPLOYEE.ROLE%TYPE,
 in_city IN EMPLOYEE.CITY%TYPE,
 in_country IN EMPLOYEE.COUNTRY%TYPE,
 out_result OUT VARCHAR2)
AS
BEGIN
  INSERT INTO EMPLOYEE (EMPID, NAME, ROLE, CITY, COUNTRY) 
  values (in_id,in_name,in_role,in_city,in_country);
  commit;
  
  out_result := 'TRUE';
  
EXCEPTION
  WHEN OTHERS THEN 
  out_result := 'FALSE';
  ROLLBACK;
END;

如您所見，insertEmployee 過程期望來自調用者的輸入，這些輸入將被插入到 Employee 表中。如果插入語句運行正常，它將返回 TRUE，如果出現任何異常，則返回 FALSE。讓我們看看如何使用 CallableStatement 執行 insertEmployee 存儲過程以插入員工數據。 JDBCStoredProcedureWrite.java

package com.journaldev.jdbc.storedproc;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Scanner;

public class JDBCStoredProcedureWrite {

	public static void main(String[] args) {
		Connection con = null;
		CallableStatement stmt = null;
		
		//讀取用戶輸入
		Scanner input = new Scanner(System.in);
		System.out.println("Enter Employee ID (int):");
		int id = Integer.parseInt(input.nextLine());
		System.out.println("Enter Employee Name:");
		String name = input.nextLine();
		System.out.println("Enter Employee Role:");
		String role = input.nextLine();
		System.out.println("Enter Employee City:");
		String city = input.nextLine();
		System.out.println("Enter Employee Country:");
		String country = input.nextLine();
		
		try{
			con = DBConnection.getConnection();
			stmt = con.prepareCall("{call insertEmployee(?,?,?,?,?,?)}");
			stmt.setInt(1, id);
			stmt.setString(2, name);
			stmt.setString(3, role);
			stmt.setString(4, city);
			stmt.setString(5, country);
			
			//在調用存儲過程之前註冊 OUT 參數
			stmt.registerOutParameter(6, java.sql.Types.VARCHAR);
			
			stmt.executeUpdate();
			
			//現在讀取 OUT 參數
			String result = stmt.getString(6);
			
			System.out.println("Employee Record Save Success::"+result);
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try {
				stmt.close();
				con.close();
				input.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

}

我們正在讀取用戶輸入以存儲在員工表中。與 PreparedStatement 唯一不同的是通過 “{call insertEmployee(?,?,?,?,?,?)}” 創建 CallableStatement 並使用 CallableStatement registerOutParameter() 方法設置 OUT 參數。在執行存儲過程之前，我們必須註冊 OUT 參數。一旦執行了存儲過程，我們可以使用 CallableStatement getXXX() 方法來獲取 OUT 物件數據。請注意，在註冊 OUT 參數時，我們需要通過 java.sql.Types 指定 OUT 參數的類型。代碼具有通用性，因此如果在其他關係數據庫中有相同的存儲過程，比如 MySQL，我們也可以使用這個程序來執行它們。以下是在多次執行上述 CallableStatement 示例程序時的輸出。

Enter Employee ID (int):
1
Enter Employee Name:
Pankaj
Enter Employee Role:
Developer
Enter Employee City:
Bangalore
Enter Employee Country:
India
Employee Record Save Success::TRUE

-----
Enter Employee ID (int):
2
Enter Employee Name:
Pankaj Kumar
Enter Employee Role:
CEO
Enter Employee City:
San Jose
Enter Employee Country:
USA
Employee Record Save Success::FALSE

請注意，第二次執行失敗，因為傳遞的名稱大於列大小。我們在存儲過程中捕獲了異常，在這種情況下返回 false。

CallableStatement 示例 – 存儲過程 OUT 參數

現在讓我們編寫一個存儲過程，通過 id 獲取員工數據。用戶將輸入員工 id，程序將顯示員工信息。 getEmployee.sql

create or replace
PROCEDURE getEmployee
(in_id IN EMPLOYEE.EMPID%TYPE,
 out_name OUT EMPLOYEE.NAME%TYPE,
 out_role OUT EMPLOYEE.ROLE%TYPE,
 out_city OUT EMPLOYEE.CITY%TYPE,
 out_country OUT EMPLOYEE.COUNTRY%TYPE
 )
AS
BEGIN
  SELECT NAME, ROLE, CITY, COUNTRY 
  INTO out_name, out_role, out_city, out_country
  FROM EMPLOYEE
  WHERE EMPID = in_id;
  
END;

Java CallableStatement 範例程式，使用 getEmployee 儲存過程來讀取員工資料是；JDBCStoredProcedureRead.java

package com.journaldev.jdbc.storedproc;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Scanner;

public class JDBCStoredProcedureRead {

	public static void main(String[] args) {
		Connection con = null;
		CallableStatement stmt = null;
		
		// 讀取使用者輸入
		Scanner input = new Scanner(System.in);
		System.out.println("Enter Employee ID (int):");
		int id = Integer.parseInt(input.nextLine());
		
		try{
			con = DBConnection.getConnection();
			stmt = con.prepareCall("{call getEmployee(?,?,?,?,?)}");
			stmt.setInt(1, id);
			
			// 在呼叫儲存過程之前註冊 OUT 參數
			stmt.registerOutParameter(2, java.sql.Types.VARCHAR);
			stmt.registerOutParameter(3, java.sql.Types.VARCHAR);
			stmt.registerOutParameter(4, java.sql.Types.VARCHAR);
			stmt.registerOutParameter(5, java.sql.Types.VARCHAR);
			
			stmt.execute();
			
			// 現在讀取 OUT 參數
			String name = stmt.getString(2);
			String role = stmt.getString(3);
			String city = stmt.getString(4);
			String country = stmt.getString(5);
			
			if(name !=null){
			System.out.println("Employee Name="+name+",Role="+role+",City="+city+",Country="+country);
			}else{
				System.out.println("Employee Not Found with ID"+id);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try {
				stmt.close();
				con.close();
				input.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

}

再次強調，該程式是通用的，適用於具有相同儲存過程的任何資料庫。讓我們看看當執行上述 CallableStatement 範例程式時的輸出。

Enter Employee ID (int):
1
Employee Name=Pankaj,Role=Developer,City=Bangalore,Country=India

CallableStatement 範例 – 儲存過程 Oracle CURSOR

由於我們是通過 ID 讀取員工資訊，因此我們得到單一結果，OUT 參數可以很好地用於讀取資料。但如果我們按角色或國家搜索，可能會得到多行結果，在這種情況下，我們可以使用 Oracle CURSOR 來讀取它們，就像結果集一樣。getEmployeeByRole.sql

create or replace
PROCEDURE getEmployeeByRole
(in_role IN EMPLOYEE.ROLE%TYPE,
 out_cursor_emps OUT SYS_REFCURSOR
 )
AS
BEGIN
  OPEN out_cursor_emps FOR
  SELECT EMPID, NAME, CITY, COUNTRY 
  FROM EMPLOYEE
  WHERE ROLE = in_role;
  
END;

JDBCStoredProcedureCursor.java

package com.journaldev.jdbc.storedproc;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;

import oracle.jdbc.OracleTypes;

public class JDBCStoredProcedureCursor {

	public static void main(String[] args) {

		Connection con = null;
		CallableStatement stmt = null;
		ResultSet rs = null;
		
		// 讀取使用者輸入
		Scanner input = new Scanner(System.in);
		System.out.println("Enter Employee Role:");
		String role = input.nextLine();
		
		try{
			con = DBConnection.getConnection();
			stmt = con.prepareCall("{call getEmployeeByRole(?,?)}");
			stmt.setString(1, role);
			
			// 在呼叫儲存過程之前註冊 OUT 參數
			stmt.registerOutParameter(2, OracleTypes.CURSOR);
			
			stmt.execute();
			
			// 現在讀取 OUT 參數
			rs = (ResultSet) stmt.getObject(2);
			
			while(rs.next()){
				System.out.println("Employee ID="+rs.getInt("empId")+",Name="+rs.getString("name")+
						",Role="+role+",City="+rs.getString("city")+
						",Country="+rs.getString("country"));
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try {
				rs.close();
				stmt.close();
				con.close();
				input.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

}

這個程式使用了 Oracle OJDBC 專用類，並且無法與其他數據庫一起使用。我們將 OUT 參數類型設置為 OracleTypes.CURSOR，然後將其轉換為 ResultSet 對象。代碼的其他部分是簡單的 JDBC 編程。當我們執行上面的 CallableStatement 示例程序時，我們會得到以下輸出。

Enter Employee Role:
Developer
Employee ID=5,Name=Kumar,Role=Developer,City=San Jose,Country=USA
Employee ID=1,Name=Pankaj,Role=Developer,City=Bangalore,Country=India

您的輸出可能會根據 Employee 表中的數據而變化。

CallableStatement 示例 – Oracle DB 對象和 STRUCT

如果您查看 insertEmployee 和 getEmployee 存儲過程，我在存儲過程中擁有 Employee 表的所有參數。當列數增加時，這可能會導致混淆和更多錯誤。Oracle 數據庫提供了創建數據庫對象的選項，我們可以使用 Oracle STRUCT 來處理它們。讓我們首先為 Employee 表列定義 Oracle DB 對象。EMPLOYEE_OBJ.sql

create or replace TYPE EMPLOYEE_OBJ AS OBJECT
(
  EMPID NUMBER,
  NAME VARCHAR2(10),
  ROLE VARCHAR2(10),
  CITY  VARCHAR2(10),
  COUNTRY  VARCHAR2(10)
  
  );

現在讓我們使用 EMPLOYEE_OBJ 重寫 insertEmployee 存儲過程。insertEmployeeObject.sql

CREATE OR REPLACE PROCEDURE insertEmployeeObject
(IN_EMPLOYEE_OBJ IN EMPLOYEE_OBJ,
 out_result OUT VARCHAR2)
AS
BEGIN
  INSERT INTO EMPLOYEE (EMPID, NAME, ROLE, CITY, COUNTRY) values 
  (IN_EMPLOYEE_OBJ.EMPID, IN_EMPLOYEE_OBJ.NAME, IN_EMPLOYEE_OBJ.ROLE, IN_EMPLOYEE_OBJ.CITY, IN_EMPLOYEE_OBJ.COUNTRY);
  commit;
  
  out_result := 'TRUE';
  
EXCEPTION
  WHEN OTHERS THEN 
  out_result := 'FALSE';
  ROLLBACK;
END;

讓我們看看如何在 java 程式中調用 insertEmployeeObject 存儲過程。JDBCStoredProcedureOracleStruct.java

package com.journaldev.jdbc.storedproc;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.Scanner;

import oracle.jdbc.OracleCallableStatement;
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;

public class JDBCStoredProcedureOracleStruct {

	public static void main(String[] args) {
		Connection con = null;
		OracleCallableStatement stmt = null;
		
		//建立物件陣列以供呼叫儲存過程使用
		Object[] empObjArray = new Object[5];
		//讀取使用者輸入
		Scanner input = new Scanner(System.in);
		System.out.println("Enter Employee ID (int):");
		empObjArray[0] = Integer.parseInt(input.nextLine());
		System.out.println("Enter Employee Name:");
		empObjArray[1] = input.nextLine();
		System.out.println("Enter Employee Role:");
		empObjArray[2] = input.nextLine();
		System.out.println("Enter Employee City:");
		empObjArray[3] = input.nextLine();
		System.out.println("Enter Employee Country:");
		empObjArray[4] = input.nextLine();
		
		try{
			con = DBConnection.getConnection();
			
			StructDescriptor empStructDesc = StructDescriptor.createDescriptor("EMPLOYEE_OBJ", con);
			STRUCT empStruct = new STRUCT(empStructDesc, con, empObjArray);
			stmt = (OracleCallableStatement) con.prepareCall("{call insertEmployeeObject(?,?)}");
			
			stmt.setSTRUCT(1, empStruct);
			
			//在呼叫儲存過程之前註冊OUT參數
			stmt.registerOutParameter(2, java.sql.Types.VARCHAR);
			
			stmt.executeUpdate();
			
			//現在讀取OUT參數
			String result = stmt.getString(2);
			
			System.out.println("Employee Record Save Success::"+result);
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try {
				stmt.close();
				con.close();
				input.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

}

首先，我們建立了與EMPLOYEE_OBJ資料庫物件相同長度的物件陣列。然後，我們根據EMPLOYEE_OBJ物件變數設置值。這一點非常重要，否則資料將插入到錯誤的欄位中。然後，我們使用oracle.sql.StructDescriptor和我們的物件陣列來創建oracle.sql.STRUCT物件。一旦建立了STRUCT物件，我們將其設置為儲存過程的IN參數，註冊OUT參數並執行它。此代碼與OJDBC API緊密耦合，不適用於其他資料庫。當我們執行此程式時，這是輸出。

Enter Employee ID (int):
5
Enter Employee Name:
Kumar
Enter Employee Role:
Developer
Enter Employee City:
San Jose
Enter Employee Country:
USA
Employee Record Save Success::TRUE

我們也可以將資料庫物件用作OUT參數，並讀取它以從資料庫中獲取值。這就是有關於在Java中執行儲存過程的CallableStatement的示例，希望你從中學到了一些東西。

Source:
https://www.digitalocean.com/community/tutorials/callablestatement-in-java-example