Java示例中的CallableStatement-白红宇

Java示例中的CallableStatement

阅读量：2534 次

发布时间：2019-05-11

本文共 17233 字，大约阅读时间需要 57 分钟。

CallableStatement in java is used to call stored procedure from java program. Stored Procedures are group of statements that we compile in the database for some task. Stored procedures are beneficial when we are dealing with multiple tables with complex scenario and rather than sending multiple queries to the database, we can send required data to the stored procedure and have the logic executed in the database server itself.

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

CallableStatement (CallableStatement)

provides support to execute Stored Procedures through

CallableStatement interface.

支持通过CallableStatement接口执行存储过程。

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.

存储过程需要使用数据库特定的语法编写，对于本教程，我将使用Oracle数据库。我们将研究带有IN和OUT参数的CallableStatement的标准功能。

Later on we will look into Oracle specific STRUCT and Cursor examples.

稍后，我们将研究Oracle特定的STRUCT和Cursor示例。

Let’s first create a table for our CallableStatement example programs with below SQL query.

首先，使用下面SQL查询为我们的CallableStatement示例程序创建一个表。

create_employee.sql

-- For Oracle DBCREATE 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")  );

Let’s first create a utility class to get the Oracle database Connection object. Make sure Oracle OJDBC jar is in the build path of the project.

首先，我们创建一个实用程序类来获取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;	}}

CallableStatement示例 (CallableStatement Example)

Let’s write a simple stored procedure to insert data into Employee table.

让我们编写一个简单的存储过程，将数据插入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)ASBEGIN  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;

As you can see that insertEmployee procedure is expecting inputs from the caller that will be inserted into the Employee table.

如您所见，insertEmployee过程期望来自调用者的输入将被插入到Employee表中。

If insert statement works fine, it’s returning TRUE and incase of any exception it’s returning FALSE.

如果insert语句工作正常，则返回TRUE，如果有任何异常，则返回FALSE。

Let’s see how we can use CallableStatement to execute insertEmployee stored procedure to insert employee data.

让我们看看如何使用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();			}		}	}}

We are reading user input to be stored in Employee table. The only thing different from PreparedStatement is the creation of CallableStatement through “{call insertEmployee(?,?,?,?,?,?)}” and setting OUT parameter with CallableStatement registerOutParameter() method.

我们正在读取要存储在Employee表中的用户输入。与PreparedStatement唯一不同的是，通过“ {call insertEmployee(?,?,?,?,?,?)} ”来创建CallableStatement registerOutParameter()并使用CallableStatement registerOutParameter()方法设置OUT参数。

We have to register the OUT parameter before executing the stored procedure. Once the stored procedure is executed, we can use CallableStatement getXXX() method to get the OUT object data. Notice that while registering the OUT parameter, we need to specify the type of OUT parameter through java.sql.Types.

在执行存储过程之前，我们必须注册OUT参数。一旦执行了存储过程，就可以使用CallableStatement getXXX()方法获取OUT对象数据。请注意，在注册OUT参数时，我们需要通过java.sql.Types指定OUT参数的类型。

The code is generic in nature, so if we have same stored procedure in other relational database like MySQL, we can execute them with this program too.

该代码本质上是通用的，因此，如果我们在其他关系数据库（如MySQL）中具有相同的存储过程，我们也可以使用此程序执行它们。

Below is the output when we are executing above CallableStatement example program multiple times.

下面是我们多次执行上述CallableStatement示例程序时的输出。

Enter Employee ID (int):1Enter Employee Name:PankajEnter Employee Role:DeveloperEnter Employee City:BangaloreEnter Employee Country:IndiaEmployee Record Save Success::TRUE-----Enter Employee ID (int):2Enter Employee Name:Pankaj KumarEnter Employee Role:CEOEnter Employee City:San JoseEnter Employee Country:USAEmployee Record Save Success::FALSE

Notice that second execution failed because name passed is bigger than the column size. We are consuming the exception in the stored procedure and returning false in this case.

请注意，第二次执行失败，因为传递的名称大于列的大小。我们在存储过程中使用了异常，在这种情况下返回false。

CallableStatement示例–存储过程OUT参数 (CallableStatement Example – Stored Procedure OUT Parameters)

Now let’s write a stored procedure to get the employee data by id. User will enter the employee id and program will display the employee information.

现在，让我们编写一个存储过程以按id获取员工数据。用户将输入员工ID，程序将显示员工信息。

getEmployee.sql

create or replacePROCEDURE 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 )ASBEGIN  SELECT NAME, ROLE, CITY, COUNTRY   INTO out_name, out_role, out_city, out_country  FROM EMPLOYEE  WHERE EMPID = in_id;  END;

Java CallableStatement example program using getEmployee stored procedure to read the employee data is;

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;				//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();			}		}	}}

Again the program is generic and works for any database having same stored procedure. Let’s see what is the output when we execute the above CallableStatement example program.

同样，该程序是通用的，适用于具有相同存储过程的任何数据库。让我们看看执行上述CallableStatement示例程序时的输出是什么。

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

CallableStatement示例–存储过程Oracle CURSOR (CallableStatement Example – Stored Procedure Oracle CURSOR)

Since we are reading the employee information through ID, we are getting single result and OUT parameters works well to read the data. But if we search by role or country, we might get multiple rows and in that case we can use Oracle CURSOR to read them like result set.

由于我们通过ID读取员工信息，因此得到的是单个结果，并且OUT参数可以很好地读取数据。但是，如果我们按角色或国家/地区搜索，则可能会得到多行，在这种情况下，我们可以使用Oracle CURSOR像结果集一样读取它们。

getEmployeeByRole.sql

create or replacePROCEDURE getEmployeeByRole(in_role IN EMPLOYEE.ROLE%TYPE, out_cursor_emps OUT SYS_REFCURSOR )ASBEGIN  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();			}		}	}}

This program is using Oracle OJDBC specific classes and won’t work with other database. We are setting OUT parameter type as OracleTypes.CURSOR and then casting it to ResultSet object. Other part of the code is simple JDBC programming.

该程序使用的是Oracle OJDBC特定的类，不适用于其他数据库。我们将OUT参数类型设置为OracleTypes.CURSOR ，然后将其强制转换为ResultSet对象。代码的其他部分是简单的JDBC编程。

When we execute above CallableStatement example program, we get below output.

当我们在CallableStatement示例程序上方执行时，将得到以下输出。

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

Your output may vary depending on the data in your Employee table.

您的输出可能会有所不同，具体取决于Employee表中的数据。

CallableStatement示例– Oracle DB对象和STRUCT (CallableStatement Example – Oracle DB Object and STRUCT)

If you look at the insertEmployee and getEmployee stored procedures, I am having all the parameters of the Employee table in the procedure. When number of column grows, this can lead to confusion and more error prone. Oracle database provides option to create database Object and we can use Oracle STRUCT to work with them.

如果您查看insertEmployee和getEmployee存储过程，则该过程中包含Employee表的所有参数。当列数增加时，这可能导致混乱并更容易出错。 Oracle数据库提供了创建数据库对象的选项，我们可以使用Oracle STRUCT来处理它们。

Let’s first define Oracle DB object for Employee table columns.

首先，为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)    );

Now let’s rewrite the insertEmployee stored procedure using EMPLOYEE_OBJ.

现在，让我们使用EMPLOYEE_OBJ重写insertEmployee存储过程。

insertEmployeeObject.sql

CREATE OR REPLACE PROCEDURE insertEmployeeObject(IN_EMPLOYEE_OBJ IN EMPLOYEE_OBJ, out_result OUT VARCHAR2)ASBEGIN  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;

Let’s see how we can call insertEmployeeObject stored procedure in java program.

让我们看看如何在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;				//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();			}		}	}}

First of all we are creating an Object array of same length as the EMPLOYEE_OBJ database object. Then we are setting values according to the EMPLOYEE_OBJ object variables. This is very important otherwise the data will get inserted into wrong columns.

首先，我们创建一个与EMPLOYEE_OBJ数据库对象长度相同的Object数组。然后，我们根据EMPLOYEE_OBJ对象变量设置值。这非常重要，否则数据将被插入错误的列中。

Then we are creating oracle.sql.STRUCT object with the help of oracle.sql.StructDescriptor and our Object array. Once the STRUCT object is created, we are setting it as IN parameter for the stored procedure, register the OUT parameter and executing it. This code is tightly couple with OJDBC API and will not work for other databases.

然后，我们将在oracle.sql.StructDescriptor和Object数组的帮助下创建oracle.sql.STRUCT对象。创建STRUCT对象后，我们将其设置为存储过程的IN参数，注册OUT参数并执行它。该代码与OJDBC API紧密结合在一起，不适用于其他数据库。

Here is the output when we are executing this program.

这是我们执行此程序时的输出。

Enter Employee ID (int):5Enter Employee Name:KumarEnter Employee Role:DeveloperEnter Employee City:San JoseEnter Employee Country:USAEmployee Record Save Success::TRUE

We can use the Database object as OUT parameter also and read it to get the values from database.

我们也可以将Database对象用作OUT参数，并读取它以从数据库获取值。

That’s all for CallableStatement in java example to execute Stored Procedures, I hope you learned something from it.

Java示例中的CallableStatement就足以执行存储过程了，我希望您从中学到了一些东西。