Hibernate provides support for executing the stored procedures and capturing their outputs using StoredProcedureQuery and ProcedureCall APIs. We can programmatically configure the procedure names and parameters, or we can use the @NamedStoredProcedureQuery annotation to provide stored procedure details and later refer it to other places in the application.
Note that, under the hood, Hibernate executes the JDBC CallableStatement for fetching the procedure outputs. By default, the CallableStatement is closed upon ending the currently running database transaction, either via calling commit or rollback.
1. Setup
Stored procedures are like named functions that are stored in the database and used to execute native SQL statements to increase the reusability and take advantage of database-specific syntaxes. Stored procedures can accept input parameters and return output after executing the queries.
We are creating two stored procedures in the MySQL database and we will see multiple ways to execute the procedures.
get_employee_by_id: It accepts IN parameter ‘employeeId‘ and returns the employee details using OUT parameters.
DELIMITER //
CREATE PROCEDURE get_employee_by_id(
IN employeeId INT,
OUT email VARCHAR(100),
OUT firstName VARCHAR(100),
OUT lastName VARCHAR(100))
BEGIN
SELECT e.email, e.firstName, e.lastName
INTO email, firstName, lastName
FROM Employee e
WHERE e.ID = employeeId;
END // get_employee_details_by_id: It accepts IN parameter ‘employeeId‘ and returns the employee details as Object[] directly without an OUT parameter type.
DELIMITER //
CREATE PROCEDURE get_employee_details_by_id(IN employeeId INT)
BEGIN
SELECT *
FROM Employee e
WHERE e.ID = employeeId;
END // Also, we are inserting dummy data into the database using the session.persist() API.
LongStream.range(1, 5).forEach(id -> {
EmployeeEntity employee = new EmployeeEntity();
employee.setFirstName("FNAME_" + id);
employee.setLastName("LNAME_" + id);
employee.setEmail("NAME_" + id + "@email.com");
session.persist(employee);
});2. Using ProcedureCall
The ProcedureCall interface defines support for executing database stored procedures and functions. We will see to execute above both procedures and capture the output.
2.1. With OUT Parameter Types
Start with creating an instance of ProcedureCallImpl with Session.createStoredProcedureCall(). Then we register the IN and OUT parameters with the call using the method registerParameter().
The value we set in the IN parameter is used in the WHERE clause.
Finally, we use call.getOutputs() method that executes the procedure in the database and returns the output values. We can call getOutputs() multiple times, returning the same ProcedureOutputs instance each time.
public void testOutParameterTypes() {
try (Session session = sessionFactory.openSession()) {
session.getTransaction().begin();
ProcedureCall call = session.createStoredProcedureCall("get_employee_by_id");
ProcedureParameter<Long> in_parameter = call
.registerParameter(1,Long.class, ParameterMode.IN);
call.setParameter(in_parameter, 1L);
call.registerParameter(2, String.class, ParameterMode.OUT);
call.registerParameter(3, String.class, ParameterMode.OUT);
call.registerParameter(4, String.class, ParameterMode.OUT);
ProcedureOutputs outputs = call.getOutputs();
int updateCount = ((UpdateCountOutput) outputs.getCurrent()).getUpdateCount();
Assertions.assertEquals(1, updateCount);
Assertions.assertEquals("[email protected]", outputs.getOutputParameterValue(2));
Assertions.assertEquals("FNAME_1", outputs.getOutputParameterValue(3));
Assertions.assertEquals("LNAME_1", outputs.getOutputParameterValue(4));
session.getTransaction().commit();
}
}We can verify the procedure call in the logs.
Hibernate: {call get_employee_by_id(?,?,?,?)}2.2. With Output as Object[]
We cannot use OUT parameters if we have to fetch a lot of information after the execution of stored procedures. It will create problems in code maintenance. So, we can only map the IN parameters because they are generally limited to 1 or 2 values. And we can get the output information in form of Object[].
Note that we need to create StoredProcedureQuery using createStoredProcedureQuery() method. Registering the IN ProcedureParameter is same as the first example. This time we need to execute the procedure with getResultList() method.
The ‘SELECT *‘ clause selects all four columns from the table so we have an Object[] of size 4. This will vary based on the number of columns and the SELECT clause.
Also, the size of the List will depend on the number of rows returned after the execution of the stored procedure.
public void fetchRowsDirectly() {
try (Session session = sessionFactory.openSession()) {
session.getTransaction().begin();
ProcedureCall call = session
.createStoredProcedureQuery("get_employee_details_by_id");
ProcedureParameter<Long> parameter = call
.registerParameter(1, Long.class, ParameterMode.IN);
call.setParameter(parameter, 1L);
List<Object[]> listOfResults = call.getResultList();
Object[] resultArray = listOfResults.get(0);
Assertions.assertEquals(1, resultArray[0]);
Assertions.assertEquals("[email protected]", resultArray[1]);
Assertions.assertEquals("FNAME_1", resultArray[2]);
Assertions.assertEquals("LNAME_1", resultArray[3]);
session.getTransaction().commit();
}
}We can verify the procedure call in the logs
Hibernate: {call get_employee_details_by_id(?)}3. Using @NamedStoredProcedureQuery
The @NamedStoredProcedureQuery annotation is used to specify a stored procedure query that can be retrieved later by its name. This annotation can be applied to an Entity or mapped superclass.
@NamedStoredProcedureQuery specifies and names a stored procedure, its parameters, and its result type. It is important to note that all parameters must be specified in the order in which they occur in the parameter list of the stored procedure in the database.
As a great benefit, we can directly map a class to the procedure results.
@NamedStoredProcedureQuery(
name = "getEmployeeByIdProcedure",
procedureName = "get_employee_details_by_id",
resultClasses = {EmployeeEntity.class},
parameters = {
@StoredProcedureParameter(name = "employeeId",
type = Integer.class,
mode = ParameterMode.IN)
}
)
public class EmployeeEntity implements Serializable {
//...
}Finally, create the StoredProcedureQuery instance and get the outputs using the method getResultList().
public void testNamedStoredProcedure() {
try (Session session = sessionFactory.openSession()) {
session.getTransaction().begin();
StoredProcedureQuery call = session
.createNamedStoredProcedureQuery("getEmployeeByIdProcedure");
List<EmployeeEntity> list = call.setParameter("employeeId", 1)
.getResultList();
EmployeeEntity employee = list.get(0);
Assertions.assertEquals(1, employee.getEmployeeId());
Assertions.assertEquals("[email protected]", employee.getEmail());
Assertions.assertEquals("FNAME_1", employee.getFirstName());
Assertions.assertEquals("LNAME_1", employee.getLastName());
session.getTransaction().commit();
}
}
}We can verify the procedure call in the logs.
Hibernate: {call get_employee_details_by_id(?)}4. Conclusion
In this hibernate tutorial, we learned to execute the SQL stored procedures using the hibernate ProcedureCall and named StoredProcedureQuery interfaces. We learned to fetch the procedure outputs with and without the PUT parameter types.
Happy Learning !!
Comments