Вызываемое утверждение в примере Java

Автор оригинала: Pankaj Kumar.

CallableStatement в java используется для вызова хранимой процедуры из программы java. Хранимые процедуры – это группа инструкций, которые мы компилируем в базе данных для какой-либо задачи. Хранимые процедуры полезны, когда мы имеем дело с несколькими таблицами со сложным сценарием, и вместо того, чтобы отправлять несколько запросов в базу данных, мы можем отправлять необходимые данные в хранимую процедуру и выполнять логику на самом сервере базы данных.

Вызываемое утверждение

API JDBC обеспечивает поддержку выполнения хранимых процедур через интерфейс CallableStatement .

Хранимые процедуры должны быть написаны в определенном синтаксисе базы данных, и для моего урока я буду использовать базу данных Oracle. Мы рассмотрим стандартные функции CallableStatement с ВХОДНЫМИ и выходными параметрами.

Позже мы рассмотрим конкретные примеры STRUCT и Курсора для Oracle.

Давайте сначала создадим таблицу для наших примеров программ CallableStatement с приведенным ниже 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. Убедитесь, что Oracle JDBC jar находится в пути сборки проекта.

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;
	}
}

Пример вызываемого утверждения

Давайте напишем простую хранимую процедуру для вставки данных в таблицу сотрудников.

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;

Как вы можете видеть, процедура вставки сотрудника ожидает ввода данных от вызывающего абонента, которые будут вставлены в таблицу сотрудников.

Если оператор insert работает нормально, он возвращает значение TRUE, а в случае любого исключения возвращает значение FALSE.

Давайте посмотрим, как мы можем использовать CallableStatement для выполнения вставки сотрудника хранимой процедуры для вставки данных сотрудника.

Давайте посмотрим, как мы можем использовать || CallableStatement || для выполнения || вставки сотрудника || хранимой процедуры для вставки данных сотрудника.

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

}

Мы считываем данные пользователя, которые будут сохранены в таблице сотрудников. Единственное, что отличается от Подготовленного заявления , – это создание CallableStatement через ” {вызов insertEmployee(?,?,?,?,?,?)} ” и задание параметра с помощью метода 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 – Параметры вывода хранимой процедуры

Теперь давайте напишем хранимую процедуру для получения данных сотрудника по идентификатору. Пользователь введет идентификатор сотрудника, и программа отобразит информацию о сотруднике.

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 для чтения данных о сотрудниках, является;

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

Пример вызываемого утверждения – КУРСОР Oracle Хранимой процедуры

Поскольку мы считываем информацию о сотруднике с помощью идентификатора, мы получаем единый результат, и параметры OUT хорошо работают для считывания данных. Но если мы будем искать по роли или стране, мы можем получить несколько строк, и в этом случае мы можем использовать курсор Oracle, чтобы прочитать их как результирующий набор.

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;

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 ODBC и не будет работать с другими базами данных. Мы задаем тип параметра как OracleTypes.КУРСОР , а затем приведение его к результирующему набору объекту. Другая часть кода-простое программирование на 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

Ваши выходные данные могут отличаться в зависимости от данных в таблице сотрудников.

Пример вызываемого утверждения – Объект и СТРУКТУРА БД Oracle

Если вы посмотрите на хранимые процедуры insertEmployee и getEmployee , у меня есть все параметры таблицы сотрудников в процедуре. Когда количество столбцов увеличивается, это может привести к путанице и большей вероятности ошибок. База данных Oracle предоставляет возможность создания объекта базы данных, и мы можем использовать Oracle STRUCT для работы с ними.

Давайте сначала определим объект Oracle DB для столбцов таблицы сотрудников.

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.

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-программе.

Давайте посмотрим, как мы можем вызвать || вставить объект сотрудника || хранимую процедуру в 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 и наш массив объектов. Как только объект СТРУКТУРЫ создан, мы устанавливаем его как параметр IN для хранимой процедуры, регистрируем параметр OUT и выполняем его. Этот код тесно связан с API JDBC и не будет работать для других баз данных.

Вот результат, когда мы выполняем эту программу.

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 и прочитать его, чтобы получить значения из базы данных.

Это все для CallableStatement в примере java для выполнения хранимых процедур, надеюсь, вы чему-то научились из него.