Stored Procedures with Hibernate

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("NAME_1@email.com", 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("NAME_1@email.com", 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("NAME_1@email.com", 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 !!

Sourcecode on Github

Was this post helpful?

Join 7000+ Awesome Developers

Get the latest updates from industry, awesome resources, blog updates and much more.

* We do not spam !!

Leave a Comment

HowToDoInJava

A blog about Java and related technologies, the best practices, algorithms, and interview questions.