Java示例中的CallableStatement
Java中的CallableStatement用于从Java程序调用存储过程。
存储过程是我们在数据库中为某些任务编译的一组语句。
当我们处理具有复杂场景的多个表时,存储过程是有益的,而不是向数据库发送多个查询,我们可以将所需数据发送到存储过程,并在数据库服务器本身中执行逻辑。
CallableStatement
JDBC API支持通过CallableStatement接口执行存储过程。
存储过程需要使用数据库特定的语法编写,对于本教程,我将使用Oracle数据库。
我们将研究带有IN和OUT参数的CallableStatement的标准功能。
稍后,我们将研究Oracle特定的STRUCT和Cursor示例。
首先,使用下面的SQL查询为我们的CallableStatement示例程序创建一个表。
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.theitroad.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示例
让我们编写一个简单的存储过程,将数据插入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表中。
如果insert语句式运作正常,则传回TRUE,如有任何例外情形,传回FALSE。
让我们看看如何使用CallableStatement执行insertEmployee存储过程以插入员工数据。
JDBCStoredProcedureWrite.java
package com.theitroad.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
唯一不同的是,通过""{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: San Franceco 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.theitroad.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=San Franceco
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.theitroad.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=San Franceco
您的输出可能会有所不同,具体取决于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.theitroad.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数据库对象相同的Object数组。
然后,我们根据EMPLOYEE_OBJ对象变量设置值。
这非常重要,否则数据将被插入错误的列中。
然后,我们在oracle.sql.StructDescriptor和Object数组的帮助下创建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参数,并读取它以从数据库获取值。