Поиск по сайту:

CallableStatement в примере Java


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

CallableStatement

-- 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")
  );

Давайте сначала создадим служебный класс для получения объекта Connection базы данных Oracle. Убедитесь, что jar 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 {
			// 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. Если оператор вставки работает нормально, он возвращает 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;
		
		//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();
			}
		}
	}

}

Мы читаем пользовательский ввод, который будет сохранен в таблице сотрудников. Единственное отличие от 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

Теперь давайте напишем хранимую процедуру для получения данных сотрудника по 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.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

Пример 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;
		
		//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=India

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

Пример 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;
		
		//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 и нашего массива объектов. После создания объекта 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

Мы также можем использовать объект базы данных в качестве параметра OUT и прочитать его, чтобы получить значения из базы данных. Это все, что касается CallableStatement в примере Java для выполнения хранимых процедур, надеюсь, вы чему-то научились.