Hibernate @NamedStoredProcedureQuery

Learn to execute SQL stored procedures with the help of @NamedStoredProcedureQuery annotation from Jakarta persistence API that helps in specifying the name of a stored procedure, its parameters, and its result type.

1. Project Setup

The support for executing stored procedures using @NamedStoredProcedureQuery has been added in JPA 2.1. So we will need to add maven dependencies for JPA pr Jakarta persistence 2.1 and it’s compatible hibernate entity manager implementation to run this example.

Hibernate 6 internally uses Jakarta persistence so we can directly use its latest version from Maven.

<dependency>
   <groupId>org.hibernate.orm</groupId>
   <artifactId>hibernate-core</artifactId>
   <version>6.0.0.Final</version>
</dependency>   

2. Declaring Named Stored Procedure

For demo purposes, we are creating a stored procedure with name ADD_EMPLOYEE_PROCEDURE that takes multiple inputs and executes an INSERT query with those inputs. Then it executes a SELECT query to return the employee data.

CREATE PROCEDURE ADD_EMPLOYEE_PROCEDURE (IN firstName VARCHAR(50), IN lastName VARCHAR(50), IN email VARCHAR(100), IN departmentId INTEGER)
     MODIFIES SQL DATA
BEGIN ATOMIC
     INSERT INTO employee (id, email, firstName, lastName, department_id ) VALUES (DEFAULT,  email, firstName, lastName, departmentId);
SELECT id, email, firstName, lastName, department_id FROM employee where id = id;
END$

To use the above the stored procedure, we will need to use @NamedStoredProcedureQuery and @StoredProcedureParameter annotations.

@NamedStoredProcedureQuery(
	    name="addEmployeeProcedure",
	    procedureName="ADD_EMPLOYEE_PROCEDURE",
	    resultClasses = { EmployeeEntity.class },
    	parameters={
            @StoredProcedureParameter(name="firstName", type=String.class, mode=ParameterMode.IN),
            @StoredProcedureParameter(name="lastName", type=String.class, mode=ParameterMode.IN),
            @StoredProcedureParameter(name="email", type=String.class, mode=ParameterMode.IN),
            @StoredProcedureParameter(name="departmentId", type=Integer.class, mode=ParameterMode.IN)
        }
)
public class EmployeeEntity implements Serializable
{
	//More code
}

3. Executing Stored Procedure

To execute the above declared stored procedure, use EntityManager.createStoredProcedureQuery() in the below manner.

@PersistenceContext
private EntityManager manager;

@Override
public boolean addEmployee(String firstName, String lastName, String email, Integer departmentId) {
	try
	{
		StoredProcedureQuery storedProcedure = manager
                                        .createNamedStoredProcedureQuery("ADD_EMPLOYEE_PROCEDURE");

		storedProcedure.setParameter(0, firstName)
					.setParameter(1, lastName)
					.setParameter(2, email)
					.setParameter(3, departmentId);

		storedProcedure.execute();
	}
	catch (Exception e) {
		e.printStackTrace();
		return false;
	}
	return true;
}

If the stored procedure returns any result, we can get the required result as the return value of execute() method.

EmployeeEntity employee = storedProcedure.execute();

4. Conclusion

In this hibernate tutorial, we learned to execute the stored procedures using the Jakarta persistence API’s @NamedStoredProcedureQuery annotation and EntityManager interface.

Happy Learning !!