CallableStatement в Java используется для вызова хранимой процедуры из программы на Java. Хранимые процедуры – это группа операторов, которые мы компилируем в базе данных для выполнения определенной задачи. Хранимые процедуры полезны, когда мы имеем дело с несколькими таблицами в сложных сценариях и вместо отправки нескольких запросов в базу данных, мы можем отправить необходимые данные в хранимую процедуру и выполнить логику непосредственно на сервере базы данных.
CallableStatement
API JDBC поддерживает выполнение хранимых процедур через интерфейс CallableStatement. Хранимые процедуры должны быть написаны на специфичном для базы данных синтаксисе, и для моего учебного пособия я буду использовать базу данных Oracle. Мы рассмотрим стандартные функции CallableStatement с входными и выходными параметрами. Позже мы рассмотрим примеры с использованием специфичных для Oracle типов STRUCT и Cursor. Давайте сначала создадим таблицу для наших примеров с использованием CallableStatement с помощью следующего SQL-запроса. create_employee.sql
-- Для базы данных Oracle
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. Убедитесь, что файл Oracle OJDBC находится в пути сборки проекта. 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 {
// загрузка класса драйвера
Class.forName(DB_DRIVER_CLASS);
// создание подключения
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. Если оператор вставки работает нормально, возвращается 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;
// Чтение пользовательского ввода
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);
// Регистрация выходного параметра перед вызовом хранимой процедуры
stmt.registerOutParameter(6, java.sql.Types.VARCHAR);
stmt.executeUpdate();
// Чтение выходного параметра сейчас
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 в этом случае.
Пример CallableStatement – OUT-параметры хранимой процедуры
Теперь давайте напишем хранимую процедуру для получения данных сотрудника по идентификатору. Пользователь введет идентификатор сотрудника, и программа отобразит информацию о сотруднике. 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.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;
//Чтение пользовательского ввода
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);
//Регистрация OUT-параметра перед вызовом хранимой процедуры
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();
//Чтение OUT-параметра
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
Пример CallableStatement – Хранимая процедура Oracle CURSOR
Поскольку мы читаем информацию о сотруднике по идентификатору, мы получаем один результат, и 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;
//Чтение пользовательского ввода
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);
//Регистрация OUT-параметра перед вызовом хранимой процедуры
stmt.registerOutParameter(2, OracleTypes.CURSOR);
stmt.execute();
//Чтение OUT-параметра
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.
Пример CallableStatement – объект Oracle DB и STRUCT
Если вы посмотрите на хранимые процедуры insertEmployee и getEmployee, то увидите, что у меня есть все параметры таблицы Employee в процедуре. Когда количество столбцов растет, это может привести к путанице и увеличению количества ошибок. Oracle-база данных предоставляет возможность создания объекта базы данных, и мы можем использовать Oracle STRUCT для работы с ними. Давайте сначала определим объект Oracle DB для столбцов таблицы 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)
);
Теперь давайте перепишем хранимую процедуру insertEmployee с использованием 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;
Давайте посмотрим, как мы можем вызвать хранимую процедуру insertEmployeeObject в программе на языке 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;
//Создаем массив объектов для вызова хранимой процедуры
Object[] empObjArray = new Object[5];
//Читаем ввод пользователя
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);
//Регистрируем выходной параметр перед вызовом хранимой процедуры
stmt.registerOutParameter(2, java.sql.Types.VARCHAR);
stmt.executeUpdate();
//Читаем выходной параметр сейчас
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 мы устанавливаем его в качестве входного параметра для хранимой процедуры, регистрируем выходной параметр и выполняем ее. Этот код тесно связан с API OJDBC и не будет работать с другими базами данных. Вот результат выполнения этой программы.
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
Мы также можем использовать объект базы данных в качестве выходного параметра и считывать его, чтобы получить значения из базы данных. Вот и все для примера использования CallableStatement в Java для выполнения хранимых процедур. Надеюсь, вы что-то усвоили из этого.
Source:
https://www.digitalocean.com/community/tutorials/callablestatement-in-java-example