8 phút để đọc
CallableStatement trong Java được sử dụng để gọi thủ tục được lưu trữ (stored procedure) từ chương trình Java.
Thủ tục lưu trữ là một tập hợp các câu lệnh SQL được biên dịch và lưu trữ trực tiếp trên cơ sở dữ liệu. Chúng giúp chúng ta đóng gói logic nghiệp vụ phức tạp, giảm thiểu lưu lượng mạng, tăng cường bảo mật và cải thiện hiệu năng đáng kể bằng cách thực thi logic trực tiếp trên máy chủ cơ sở dữ liệu.
Vậy làm thế nào để ứng dụng Java của chúng ta có thể gọi và tương tác với những thủ tục mạnh mẽ này? Câu trả lời nằm ở interface CallableStatement trong JDBC API.
Trong bài viết này, chúng ta sẽ cùng nhau khám phá sâu hơn về CallableStatement.
CallableStatement
JDBC API cung cấp giao diện CallableStatement để hỗ trợ thực thi các Stored Procedure. Các Stored Procedure cần được viết bằng cú pháp đặc thù của từng hệ quản trị cơ sở dữ liệu. Trong bài hướng dẫn này, tôi sẽ sử dụng cơ sở dữ liệu Oracle.
Chúng ta sẽ xem xét các tính năng tiêu chuẩn của CallableStatement với các tham số IN và OUT. Sau đó, chúng ta sẽ đi sâu vào các ví dụ cụ thể của Oracle như STRUCT và CURSOR.
Đầu tiên, hãy tạo một bảng EMPLOYEE cho các chương trình ví dụ CallableStatement của chúng ta với câu truy vấn SQL dưới đây: 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")
);
Tiếp theo, chúng ta tạo một lớp tiện ích để lấy đối tượng Connection đến cơ sở dữ liệu Oracle. Đảm bảo rằng file JAR của Oracle OJDBC đã được thêm vào build path của dự án của bạn. 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;
}
}
Ví dụ CallableStatement
Hãy viết một stored procedure đơn giản để chèn dữ liệu vào bảng 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;
Như bạn thấy, thủ tục insertEmployee mong đợi các giá trị đầu vào từ chương trình gọi để chèn vào bảng EMPLOYEE. Nếu câu lệnh INSERT thành công, nó trả về TRUE; ngược lại, nếu có bất kỳ ngoại lệ nào, nó trả về FALSE.
Bây giờ, hãy cùng xem cách chúng ta sử dụng CallableStatement để thực thi thủ tục insertEmployee và chèn dữ liệu nhân viên. 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();
}
}
}
}
Chúng ta đọc dữ liệu đầu vào từ người dùng để lưu vào bảng EMPLOYEE. Điểm khác biệt duy nhất so với PreparedStatement là cách tạo CallableStatement thông qua cú pháp "{call insertEmployee(?,?,?,?,?,?)}", và thiết lập tham số OUT bằng phương thức registerOutParameter() của CallableStatement. Chúng ta cần đăng ký tham số OUT trước khi gọi stored procedure. Sau khi stored procedure được thực thi, chúng ta có thể sử dụng phương thức getXXX() của CallableStatement để lấy dữ liệu của đối tượng OUT. Lưu ý rằng khi đăng ký tham số OUT, chúng ta cần chỉ định kiểu dữ liệu của nó thông qua java.sql.Types.
Mã này có tính chất chung, vì vậy nếu chúng ta có cùng stored procedure trong các cơ sở dữ liệu quan hệ khác như MySQL, chúng ta cũng có thể thực thi chúng bằng chương trình này. Dưới đây là kết quả khi chúng ta thực thi chương trình ví dụ CallableStatement này nhiều lần.
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
Lưu ý rằng lần thực thi thứ hai đã thất bại vì tên được truyền vào lớn hơn kích thước cột cho phép. Chúng ta đã xử lý ngoại lệ trong stored procedure và trả về FALSE trong trường hợp này.
Ví dụ CallableStatement – Stored Procedure với nhiều tham số OUT
Bây giờ, hãy viết một stored procedure để lấy dữ liệu nhân viên theo ID. Người dùng sẽ nhập ID nhân viên và chương trình sẽ hiển thị thông tin nhân viên. 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;
Chương trình ví dụ CallableStatement trong Java sử dụng stored procedure getEmployee để đọc dữ liệu nhân viên là: 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();
}
}
}
}
Một lần nữa, chương trình này có tính chất chung và hoạt động cho bất kỳ cơ sở dữ liệu nào có cùng stored procedure. Hãy xem kết quả khi chúng ta thực thi chương trình ví dụ CallableStatement trên.
Enter Employee ID (int):
1
Employee Name=Pankaj,Role=Developer,City=Bangalore,Country=India
Ví dụ CallableStatement – Stored Procedure với Oracle CURSOR
Vì chúng ta đang đọc thông tin nhân viên thông qua ID, chúng ta chỉ nhận được một kết quả và các tham số OUT hoạt động tốt để đọc dữ liệu. Nhưng nếu chúng ta tìm kiếm theo vai trò hoặc quốc gia, chúng ta có thể nhận được nhiều hàng, và trong trường hợp đó, chúng ta có thể sử dụng Oracle CURSOR để đọc chúng giống như một ResultSet. 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();
}
}
}
}
Chương trình này sử dụng các lớp cụ thể của Oracle OJDBC và sẽ không hoạt động với các cơ sở dữ liệu khác. Chúng ta đặt kiểu tham số OUT là OracleTypes.CURSOR và sau đó ép kiểu nó thành đối tượng ResultSet. Phần còn lại của mã là lập trình JDBC đơn giản. Khi chúng ta thực thi chương trình ví dụ CallableStatement trên, chúng ta nhận được kết quả dưới đây.
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
Kết quả của bạn có thể khác nhau tùy thuộc vào dữ liệu trong bảng EMPLOYEE của bạn.
Ví dụ CallableStatement – Oracle DB Object và STRUCT
Nếu bạn nhìn vào các stored procedure insertEmployee và getEmployee, tôi đang có tất cả các tham số của bảng EMPLOYEE trong thủ tục. Khi số lượng cột tăng lên, điều này có thể dẫn đến sự nhầm lẫn và dễ gây lỗi hơn. Cơ sở dữ liệu Oracle cung cấp tùy chọn tạo đối tượng cơ sở dữ liệu (Database Object) và chúng ta có thể sử dụng Oracle STRUCT để làm việc với chúng.
Đầu tiên, hãy định nghĩa đối tượng EMPLOYEE_OBJ của Oracle DB cho các cột trong bảng EMPLOYEE. 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)
);
Bây giờ, hãy viết lại stored procedure insertEmployee sử dụng EMPLOYEE_OBJ. 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;
Hãy xem cách chúng ta có thể gọi stored procedure insertEmployeeObject trong chương trình Java. 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();
}
}
}
}
Đầu tiên, chúng ta tạo một mảng Object có cùng độ dài với đối tượng cơ sở dữ liệu EMPLOYEE_OBJ. Sau đó, chúng ta thiết lập các giá trị tương ứng với các biến đối tượng của EMPLOYEE_OBJ. Điều này rất quan trọng nếu không dữ liệu sẽ bị chèn vào các cột sai. Tiếp theo, chúng ta tạo đối tượng oracle.sql.STRUCT với sự trợ giúp của oracle.sql.StructDescriptor và mảng Object của chúng ta. Sau khi đối tượng STRUCT được tạo, chúng ta thiết lập nó làm tham số IN cho stored procedure, đăng ký tham số OUT và thực thi nó.
Mã này gắn chặt với API OJDBC của Oracle và sẽ không hoạt động cho các cơ sở dữ liệu khác. Đây là kết quả khi chúng ta thực thi chương trình này.
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
Chúng ta cũng có thể sử dụng đối tượng Database Object làm tham số OUT và đọc nó để lấy các giá trị từ cơ sở dữ liệu.
Đó là tất cả cho ví dụ về CallableStatement trong Java để thực thi Stored Procedures. Tôi hy vọng bạn đã học được điều gì đó từ bài viết này.
Kết luận
Qua bài viết này, chúng ta đã cùng nhau khám phá sức mạnh và tính linh hoạt của CallableStatement trong Java JDBC API để tương tác với Stored Procedures.
Hãy tự mình thực hành và thử nghiệm với các loại tham số khác nhau, hoặc áp dụng kiến thức này vào các dự án thực tế của bạn. Khả năng tương tác mạnh mẽ giữa Java và cơ sở dữ liệu sẽ mở ra nhiều cơ hội để bạn xây dựng những ứng dụng mạnh mẽ và hiệu quả hơn.
