Java中的CallableStatement示例

教程

Java

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

CallableStatement

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

-- 对于Oracle DB
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();
			}
		}
	}

}

我们正在读取用户输入以存储在 Employee 表中。与 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游标

由于我们通过ID读取员工信息，因此我们得到单个结果，并且OUT参数可以很好地用于读取数据。但是，如果我们按角色或国家搜索，可能会得到多行数据，在这种情况下，我们可以使用Oracle游标来像结果集一样读取它们。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数据库对象和STRUCT

如果您看一下insertEmployee和getEmployee存储过程，我在过程中拥有Employee表的所有参数。当列数增加时，这可能会导致混淆并更容易出错。Oracle数据库提供了创建数据库对象的选项，我们可以使用Oracle STRUCT与它们一起工作。让我们首先为Employee表列定义Oracle数据库对象。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

我们还可以将Database对象用作OUT参数，然后读取它以从数据库获取值。关于在Java中执行存储过程的CallableStatement示例，就是这些。希望你从中学到了一些东西。

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