java中的CallableStatement用于从java程序中调用存储过程。存储过程是我们在数据库中为某些任务编译的一组语句。当我们处理复杂场景的多个表时，存储过程很有用，我们可以将所需的数据发送到存储过程，并在数据库服务器本身中执行逻辑，而不是向数据库发送多个查询。
JDBC API provides support to execute Stored Procedures through CallableStatement interface. Stored Procedures requires to be written in the database specific syntax and for my tutorial, I will use Oracle database. We will look into standard features of CallableStatement with IN and OUT parameters. Later on we will look into Oracle specific STRUCT and Cursor examples. Let’s first create a table for our CallableStatement example programs with below SQL query. create_employee.sql
-- For 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 数据库 Connection 对象。确保 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 {
// load the Driver Class
Class.forName(DB_DRIVER_CLASS);
// create the connection now
con = DriverManager.getConnection(DB_URL,DB_USERNAME,DB_PASSWORD);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return con;
}
}
让我们编写一个简单的存储过程来将数据插入到 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;
//Read User Inputs
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);
//register the OUT parameter before calling the stored procedure
stmt.registerOutParameter(6, java.sql.Types.VARCHAR);
stmt.executeUpdate();
//read the OUT parameter now
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是通过“创建CallableStatement{call insertEmployee(?,?,?,?,?,?)}”并设置 OUT 参数CallableStatement registerOutParameter()方法。我们必须在执行存储过程之前注册 OUT 参数。执行存储过程后，我们可以使用CallableStatement getXXX()方法获取 OUT 对象数据。请注意，在注册 OUT 参数时，我们需要通过以下方式指定 OUT 参数的类型：java.sql.Types。该代码本质上是通用的，因此如果我们在其他关系数据库（例如 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。
现在让我们编写一个存储过程来通过 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;
使用 getEmployee 存储过程读取员工数据的 Java CallableStatement 示例程序是；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;
//Read User Inputs
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);
//register the OUT parameter before calling the stored procedure
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();
//read the OUT parameter now
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
由于我们通过 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;
//Read User Inputs
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);
//register the OUT parameter before calling the stored procedure
stmt.registerOutParameter(2, OracleTypes.CURSOR);
stmt.execute();
//read the OUT parameter now
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 表中的数据。
如果你看一下insertEmployee and 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;
让我们看看如何调用insertEmployeeObjectjava程序中的存储过程。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;
//Create Object Array for Stored Procedure call
Object[] empObjArray = new Object[5];
//Read User Inputs
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);
//register the OUT parameter before calling the stored procedure
stmt.registerOutParameter(2, java.sql.Types.VARCHAR);
stmt.executeUpdate();
//read the OUT parameter now
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.STRUCT对象的帮助下oracle.sql.StructDescriptor和我们的对象数组。创建 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执行存储过程的例子，希望你能从中有所收获。