Java中的CallableStatement用于从Java程序中调用存储过程。存储过程 是我们在数据库中为某个任务编译的一组语句。当我们处理具有复杂场景的多个表时，存储过程很有用，我们可以将所需的数据发送到存储过程，并在数据库服务器本身中执行逻辑，而不是向数据库发送多个查询。
CallableStatement
[JDBC API]（/community/tutorials/jdbc-example-mysql-oracle)支持通过CallableStatement接口执行存储过程。存储过程需要在数据库的具体语法和我的教程写，我将使用Oracle数据库。我们将研究一下带有IN和OUT参数的 CallableStatement 的标准特性。稍后，我们将研究Oracle特定的** 游标 ** 和** 游标 ** 示例。让我们首先用下面的SQL查询为CallableStatement示例程序创建一个表。create_employee.sql
1-- For Oracle DB
2CREATE TABLE EMPLOYEE
3 (
4 "EMPID" NUMBER NOT NULL ENABLE,
5 "NAME" VARCHAR2(10 BYTE) DEFAULT NULL,
6 "ROLE" VARCHAR2(10 BYTE) DEFAULT NULL,
7 "CITY" VARCHAR2(10 BYTE) DEFAULT NULL,
8 "COUNTRY" VARCHAR2(10 BYTE) DEFAULT NULL,
9 PRIMARY KEY ("EMPID")
10 );
让我们首先创建一个实用程序类来获取Oracle数据库Connection对象。确保Oracle OJDBC jar位于项目的构建路径中。DBConnection.java
1package com.journaldev.jdbc.storedproc;
2
3import java.sql.Connection;
4import java.sql.DriverManager;
5import java.sql.SQLException;
6
7public class DBConnection {
8
9 private static final String DB_DRIVER_CLASS = "oracle.jdbc.driver.OracleDriver";
10 private static final String DB_URL = "jdbc:oracle:thin:@localhost:1521:orcl";
11 private static final String DB_USERNAME = "HR";
12 private static final String DB_PASSWORD = "oracle";
13
14 public static Connection getConnection() {
15 Connection con = null;
16 try {
17 // load the Driver Class
18 Class.forName(DB_DRIVER_CLASS);
19
20 // create the connection now
21 con = DriverManager.getConnection(DB_URL,DB_USERNAME,DB_PASSWORD);
22 } catch (ClassNotFoundException e) {
23 e.printStackTrace();
24 } catch (SQLException e) {
25 e.printStackTrace();
26 }
27 return con;
28 }
29}
CallableStatement示例
让我们编写一个简单的存储过程来将数据插入Employee表。intertEmployee.sql
1CREATE OR REPLACE PROCEDURE insertEmployee
2(in_id IN EMPLOYEE.EMPID%TYPE,
3 in_name IN EMPLOYEE.NAME%TYPE,
4 in_role IN EMPLOYEE.ROLE%TYPE,
5 in_city IN EMPLOYEE.CITY%TYPE,
6 in_country IN EMPLOYEE.COUNTRY%TYPE,
7 out_result OUT VARCHAR2)
8AS
9BEGIN
10 INSERT INTO EMPLOYEE (EMPID, NAME, ROLE, CITY, COUNTRY)
11 values (in_id,in_name,in_role,in_city,in_country);
12 commit;
13
14 out_result := 'TRUE';
15
16EXCEPTION
17 WHEN OTHERS THEN
18 out_result := 'FALSE';
19 ROLLBACK;
20END;
如您所见，intertEmployee过程期望来自调用者的输入将被插入到Employee表中。如果INSERT语句运行良好，则返回TRUE，如果出现任何异常，则返回FALSE。让我们来看看如何使用CallableStatement执行intertEmployee存储过程来插入员工数据。JDBCStoredProcedureWrite.java
1package com.journaldev.jdbc.storedproc;
2
3import java.sql.CallableStatement;
4import java.sql.Connection;
5import java.sql.SQLException;
6import java.util.Scanner;
7
8public class JDBCStoredProcedureWrite {
9
10 public static void main(String[] args) {
11 Connection con = null;
12 CallableStatement stmt = null;
13
14 //Read User Inputs
15 Scanner input = new Scanner(System.in);
16 System.out.println("Enter Employee ID (int):");
17 int id = Integer.parseInt(input.nextLine());
18 System.out.println("Enter Employee Name:");
19 String name = input.nextLine();
20 System.out.println("Enter Employee Role:");
21 String role = input.nextLine();
22 System.out.println("Enter Employee City:");
23 String city = input.nextLine();
24 System.out.println("Enter Employee Country:");
25 String country = input.nextLine();
26
27 try{
28 con = DBConnection.getConnection();
29 stmt = con.prepareCall("{call insertEmployee(?,?,?,?,?,?)}");
30 stmt.setInt(1, id);
31 stmt.setString(2, name);
32 stmt.setString(3, role);
33 stmt.setString(4, city);
34 stmt.setString(5, country);
35
36 //register the OUT parameter before calling the stored procedure
37 stmt.registerOutParameter(6, java.sql.Types.VARCHAR);
38
39 stmt.executeUpdate();
40
41 //read the OUT parameter now
42 String result = stmt.getString(6);
43
44 System.out.println("Employee Record Save Success::"+result);
45 }catch(Exception e){
46 e.printStackTrace();
47 }finally{
48 try {
49 stmt.close();
50 con.close();
51 input.close();
52 } catch (SQLException e) {
53 e.printStackTrace();
54 }
55 }
56 }
57
58}
我们正在读取要存储在Employee表中的用户输入。与PreparedStatement唯一不同的是，通过)方法设置参数。我们必须在执行存储过程之前注册OUT参数。一旦存储过程执行完毕，我们就可以使用CallableStatement getXXX()方法来获取out对象数据。请注意，在注册out参数时，我们需要通过java.sql.Types`指定out参数的类型。代码本质上是泛型的，所以如果我们在其他关系数据库中有相同的存储过程，如MySQL，我们也可以用这个程序执行它们。下面是我们多次执行上面的CallableStatement示例程序时的输出。
1Enter Employee ID (int):
21
3Enter Employee Name:
4Pankaj
5Enter Employee Role:
6Developer
7Enter Employee City:
8Bangalore
9Enter Employee Country:
10India
11Employee Record Save Success::TRUE
12
13-----
14Enter Employee ID (int):
152
16Enter Employee Name:
17Pankaj Kumar
18Enter Employee Role:
19CEO
20Enter Employee City:
21San Jose
22Enter Employee Country:
23USA
24Employee Record Save Success::FALSE
请注意，第二次执行失败，因为传递的名称大于列大小。我们使用存储过程中的异常，并在本例中返回FALSE。
CallableStatement示例-存储过程输出参数
现在，让我们编写一个存储过程来按id获取员工数据。用户将输入员工ID，程序将显示员工信息。getEmployee.sql
1create or replace
2PROCEDURE getEmployee
3(in_id IN EMPLOYEE.EMPID%TYPE,
4 out_name OUT EMPLOYEE.NAME%TYPE,
5 out_role OUT EMPLOYEE.ROLE%TYPE,
6 out_city OUT EMPLOYEE.CITY%TYPE,
7 out_country OUT EMPLOYEE.COUNTRY%TYPE
8 )
9AS
10BEGIN
11 SELECT NAME, ROLE, CITY, COUNTRY
12 INTO out_name, out_role, out_city, out_country
13 FROM EMPLOYEE
14 WHERE EMPID = in_id;
15
16END;
Java CallableStatement使用getEmployee存储过程读取员工数据的示例程序是; JDBCStoredProcedureRead.java
1package com.journaldev.jdbc.storedproc;
2
3import java.sql.CallableStatement;
4import java.sql.Connection;
5import java.sql.SQLException;
6import java.util.Scanner;
7
8public class JDBCStoredProcedureRead {
9
10 public static void main(String[] args) {
11 Connection con = null;
12 CallableStatement stmt = null;
13
14 //Read User Inputs
15 Scanner input = new Scanner(System.in);
16 System.out.println("Enter Employee ID (int):");
17 int id = Integer.parseInt(input.nextLine());
18
19 try{
20 con = DBConnection.getConnection();
21 stmt = con.prepareCall("{call getEmployee(?,?,?,?,?)}");
22 stmt.setInt(1, id);
23
24 //register the OUT parameter before calling the stored procedure
25 stmt.registerOutParameter(2, java.sql.Types.VARCHAR);
26 stmt.registerOutParameter(3, java.sql.Types.VARCHAR);
27 stmt.registerOutParameter(4, java.sql.Types.VARCHAR);
28 stmt.registerOutParameter(5, java.sql.Types.VARCHAR);
29
30 stmt.execute();
31
32 //read the OUT parameter now
33 String name = stmt.getString(2);
34 String role = stmt.getString(3);
35 String city = stmt.getString(4);
36 String country = stmt.getString(5);
37
38 if(name !=null){
39 System.out.println("Employee Name="+name+",Role="+role+",City="+city+",Country="+country);
40 }else{
41 System.out.println("Employee Not Found with ID"+id);
42 }
43 }catch(Exception e){
44 e.printStackTrace();
45 }finally{
46 try {
47 stmt.close();
48 con.close();
49 input.close();
50 } catch (SQLException e) {
51 e.printStackTrace();
52 }
53 }
54 }
55
56}
同样，该程序是通用的，适用于任何具有相同存储过程的数据库。让我们来看看执行上面的CallableStatement示例程序时的输出是什么。
1Enter Employee ID (int):
21
3Employee Name=Pankaj,Role=Developer,City=Bangalore,Country=India
CallableStatement示例-存储过程Oracle游标
因为我们通过ID读取员工信息，所以我们得到了单一的结果，Out参数可以很好地读取数据。但如果我们按角色或国家/地区进行搜索，我们可能会得到多个行，在这种情况下，我们可以使用Oracle游标来读取它们，就像读取结果集一样。getEmployeeByRole.sql
1create or replace
2PROCEDURE getEmployeeByRole
3(in_role IN EMPLOYEE.ROLE%TYPE,
4 out_cursor_emps OUT SYS_REFCURSOR
5 )
6AS
7BEGIN
8 OPEN out_cursor_emps FOR
9 SELECT EMPID, NAME, CITY, COUNTRY
10 FROM EMPLOYEE
11 WHERE ROLE = in_role;
12
13END;
JDBCStoredCursor.java
1package com.journaldev.jdbc.storedproc;
2
3import java.sql.CallableStatement;
4import java.sql.Connection;
5import java.sql.ResultSet;
6import java.sql.SQLException;
7import java.util.Scanner;
8
9import oracle.jdbc.OracleTypes;
10
11public class JDBCStoredProcedureCursor {
12
13 public static void main(String[] args) {
14
15 Connection con = null;
16 CallableStatement stmt = null;
17 ResultSet rs = null;
18
19 //Read User Inputs
20 Scanner input = new Scanner(System.in);
21 System.out.println("Enter Employee Role:");
22 String role = input.nextLine();
23
24 try{
25 con = DBConnection.getConnection();
26 stmt = con.prepareCall("{call getEmployeeByRole(?,?)}");
27 stmt.setString(1, role);
28
29 //register the OUT parameter before calling the stored procedure
30 stmt.registerOutParameter(2, OracleTypes.CURSOR);
31
32 stmt.execute();
33
34 //read the OUT parameter now
35 rs = (ResultSet) stmt.getObject(2);
36
37 while(rs.next()){
38 System.out.println("Employee ID="+rs.getInt("empId")+",Name="+rs.getString("name")+
39 ",Role="+role+",City="+rs.getString("city")+
40 ",Country="+rs.getString("country"));
41 }
42 }catch(Exception e){
43 e.printStackTrace();
44 }finally{
45 try {
46 rs.close();
47 stmt.close();
48 con.close();
49 input.close();
50 } catch (SQLException e) {
51 e.printStackTrace();
52 }
53 }
54 }
55
56}
该程序正在使用Oracle OJDBC特定的类，不能与其他数据库一起工作。我们将参数类型设置为OracleTypes.CURSOR，然后将其强制转换为ResultSet对象。代码的其他部分是简单的JDBC编程。当我们执行上面的CallableStatement示例程序时，我们得到的输出低于。
1Enter Employee Role:
2Developer
3Employee ID=5,Name=Kumar,Role=Developer,City=San Jose,Country=USA
4Employee ID=1,Name=Pankaj,Role=Developer,City=Bangalore,Country=India
您的输出可能会因Employee表中的数据而异。
CallableStatement示例-Oracle数据库对象和STRUCT
如果您查看intertEmployee和getEmployee存储过程，就会发现该过程中包含Employee表的所有参数。当列数增加时，这可能会导致混淆和更容易出错。Oracle数据库提供了创建数据库对象的选项，我们可以使用Oracle STRUCT来处理它们。让我们首先为Employee表列定义Oracle DB对象。EMPLOYEE_OBJ.sql
1create or replace TYPE EMPLOYEE_OBJ AS OBJECT
2(
3 EMPID NUMBER,
4 NAME VARCHAR2(10),
5 ROLE VARCHAR2(10),
6 CITY VARCHAR2(10),
7 COUNTRY VARCHAR2(10)
8
9 );
现在，让我们使用Employee_OBJ重写intertEmployee存储过程。intertEmployeeObt.sql
1CREATE OR REPLACE PROCEDURE insertEmployeeObject
2(IN_EMPLOYEE_OBJ IN EMPLOYEE_OBJ,
3 out_result OUT VARCHAR2)
4AS
5BEGIN
6 INSERT INTO EMPLOYEE (EMPID, NAME, ROLE, CITY, COUNTRY) values
7 (IN_EMPLOYEE_OBJ.EMPID, IN_EMPLOYEE_OBJ.NAME, IN_EMPLOYEE_OBJ.ROLE, IN_EMPLOYEE_OBJ.CITY, IN_EMPLOYEE_OBJ.COUNTRY);
8 commit;
9
10 out_result := 'TRUE';
11
12EXCEPTION
13 WHEN OTHERS THEN
14 out_result := 'FALSE';
15 ROLLBACK;
16END;
让我们来看看如何在Java程序中调用intertEmployeeObject存储过程。JDBCStoredProcedureOracleStruct.java
1package com.journaldev.jdbc.storedproc;
2
3import java.sql.Connection;
4import java.sql.SQLException;
5import java.util.Scanner;
6
7import oracle.jdbc.OracleCallableStatement;
8import oracle.sql.STRUCT;
9import oracle.sql.StructDescriptor;
10
11public class JDBCStoredProcedureOracleStruct {
12
13 public static void main(String[] args) {
14 Connection con = null;
15 OracleCallableStatement stmt = null;
16
17 //Create Object Array for Stored Procedure call
18 Object[] empObjArray = new Object[5];
19 //Read User Inputs
20 Scanner input = new Scanner(System.in);
21 System.out.println("Enter Employee ID (int):");
22 empObjArray[0] = Integer.parseInt(input.nextLine());
23 System.out.println("Enter Employee Name:");
24 empObjArray[1] = input.nextLine();
25 System.out.println("Enter Employee Role:");
26 empObjArray[2] = input.nextLine();
27 System.out.println("Enter Employee City:");
28 empObjArray[3] = input.nextLine();
29 System.out.println("Enter Employee Country:");
30 empObjArray[4] = input.nextLine();
31
32 try{
33 con = DBConnection.getConnection();
34
35 StructDescriptor empStructDesc = StructDescriptor.createDescriptor("EMPLOYEE_OBJ", con);
36 STRUCT empStruct = new STRUCT(empStructDesc, con, empObjArray);
37 stmt = (OracleCallableStatement) con.prepareCall("{call insertEmployeeObject(?,?)}");
38
39 stmt.setSTRUCT(1, empStruct);
40
41 //register the OUT parameter before calling the stored procedure
42 stmt.registerOutParameter(2, java.sql.Types.VARCHAR);
43
44 stmt.executeUpdate();
45
46 //read the OUT parameter now
47 String result = stmt.getString(2);
48
49 System.out.println("Employee Record Save Success::"+result);
50 }catch(Exception e){
51 e.printStackTrace();
52 }finally{
53 try {
54 stmt.close();
55 con.close();
56 input.close();
57 } catch (SQLException e) {
58 e.printStackTrace();
59 }
60 }
61 }
62
63}
首先，我们创建一个长度与EMPLOYEE_OBJ数据库对象相同的Object数组。然后我们根据EMPLOYEE_OBJ对象变量设置值。这一点非常重要，否则数据将被插入错误的列。然后，我们在oracle.sql.StructDescriptor和Object数组的帮助下创建oracle.sql. SQLACT对象。一旦创建了RECT对象，我们就将其设置为存储过程的IN参数，注册OUT参数并执行它。这段代码与OJDBC API紧密耦合，不适用于其他数据库。这是我们执行这个程序时的输出。
1Enter Employee ID (int):
25
3Enter Employee Name:
4Kumar
5Enter Employee Role:
6Developer
7Enter Employee City:
8San Jose
9Enter Employee Country:
10USA
11Employee Record Save Success::TRUE
我们也可以使用数据库对象作为输出参数，并读取它以从数据库中获取值。这就是Java示例中的CallableStatement执行存储过程的全部内容，希望您从中学到一些东西。