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 !!

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 !!

5 thoughts on “Hibernate @NamedStoredProcedureQuery”

  1. On EmployeeDAOImpl.java you are using createStoredProcedureQuery instead of createNamedStoredProcedureQuery, so you are not really creating a named sp query

    Reply
  2. I am getting error like :-

    ***************************
    APPLICATION FAILED TO START
    ***************************

    Description:

    Field in required a bean named ‘entityManagerFactory’ that could not be found.

    Action:

    Consider defining a bean named ‘entityManagerFactory’ in your configuration.

    Reply
  3. Is there any other setup required for the EntityManager? I am using your code as an example for my own and getting a ‘NullPointerException’ for my EntityManager instance.

    Reply

Leave a Comment

HowToDoInJava

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