HowToDoInJava

  • Python
  • Java
  • Spring Boot
  • Dark Mode
Home / Hibernate / @NamedStoredProcedureQuery Example

Hibernate – @NamedStoredProcedureQuery

In this tutorial, I am giving an example of executing Stored Procedures in with the help of @NamedStoredProcedureQuery annotation. I am using HQLDB (in-memory database) for backend DB in this example. I will first create a stored procedure during start of application, then will run it using EntityManager.createStoredProcedureQuery() method.

Table of Contents

Maven Configuration
Declaring Stored Procedure using @NamedStoredProcedureQuery
Executing Stored Procedure
Complete Example

Maven Configuration

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

<dependency>
	<groupId>org.hibernate.javax.persistence</groupId>
	<artifactId>hibernate-jpa-2.1-api</artifactId>
	<version>1.0.0.Final</version>
</dependency>
<dependency>
	<groupId>org.hibernate</groupId>
	<artifactId>hibernate-entitymanager</artifactId>
	<version>4.3.10.Final</version>
</dependency>

Declaring Stored Procedure using @NamedStoredProcedureQuery

To declare the stored procedure, you 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
}

The above stored procedure can be used to add an employee record in the database.

Executing Stored Procedure

To Execute above declared stored procedure, use EntityManager.createStoredProcedureQuery() in 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")
				.registerStoredProcedureParameter(0 , String.class , ParameterMode.IN)
				.registerStoredProcedureParameter(1 , String.class, ParameterMode.IN)
				.registerStoredProcedureParameter(2 , String.class, ParameterMode.IN)
				.registerStoredProcedureParameter(3 , Integer.class, ParameterMode.IN);
		
		storedProcedure	.setParameter(0, firstName)
						.setParameter(1, lastName)
						.setParameter(2, email)
						.setParameter(3, departmentId);
		
		storedProcedure.execute();
	} 
	catch (Exception e) {
		e.printStackTrace();
		return false;
	}
	return true;
}

Complete Example

Now let’s look at all the files involved in running this example.

tempDB.sql

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);
END$

application-context.xml

<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:context="http://www.springframework.org/schema/context"
    xmlns:tx="http://www.springframework.org/schema/tx"
    xmlns:mvc="http://www.springframework.org/schema/mvc"
    xmlns:jdbc="http://www.springframework.org/schema/jdbc"
    xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
    http://www.springframework.org/schema/context/ http://www.springframework.org/schema/context/spring-context-3.0.xsd
    http://www.springframework.org/schema/tx/ http://www.springframework.org/schema/tx/spring-tx-3.2.xsd
    http://www.springframework.org/schema/mvc/ http://www.springframework.org/schema/mvc/spring-mvc-3.2.xsd
    http://www.springframework.org/schema/jdbc/ http://www.springframework.org/schema/jdbc/spring-jdbc-3.2.xsd;

	<context:component-scan base-package="com.howtodoinjava.jpa.demo" />

	<bean id="entityManagerFactoryBean"	class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
	
		<property name="dataSource" ref="dataSource" />

		<property name="persistenceUnitName" value="demoJPAUnit" />
		
		<property name="packagesToScan">
			<list>
				<value>com.howtodoinjava.jpa.demo.entity</value>
			</list>
		</property>

		<property name="jpaVendorAdapter">
			<bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter" />
		</property>
		
		<property name="jpaProperties">
			<props>
				<prop key="hibernate.archive.autodetection">class,hbm</prop>
				<prop key="hibernate.hbm2ddl.auto">create</prop>
				<prop key="hibernate.show_sql">true</prop>
				<prop key="hibernate.dialect">org.hibernate.dialect.HSQLDialect</prop>
			</props>
		</property>
	</bean>

	<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
	    <property name="driverClassName" value="org.hsqldb.jdbcDriver" />
	    <property name="url" value="jdbc:hsqldb:mem:howtodoinjava" />
	    <property name="username" value="sa" />
	    <property name="password" value="" />
	</bean>
    
    <jdbc:initialize-database data-source="dataSource" ignore-failures="DROPS">
	    <jdbc:script location="classpath:scripts/tempDB.sql" separator="$"/>
	</jdbc:initialize-database>

	<bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager">
		<property name="entityManagerFactory" ref="entityManagerFactoryBean" />
	</bean>

	<tx:annotation-driven />

</beans>

pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd;
	<modelVersion>4.0.0</modelVersion>
	<groupId>com.howtodoinjava.jpa.demo</groupId>
	<artifactId>JPAExamples</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>jar</packaging>
	<build>
		<sourceDirectory>src</sourceDirectory>
		<plugins>
			<plugin>
				<artifactId>maven-compiler-plugin</artifactId>
				<version>2.3.2</version>
				<configuration>
					<source>1.7</source>
					<target>1.7</target>
				</configuration>
			</plugin>
		</plugins>
	</build>
	<dependencies>

		<dependency>
			<groupId>junit</groupId>
			<artifactId>junit</artifactId>
			<version>4.12</version>
			<scope>test</scope>
		</dependency>

		<!-- Spring Support -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-core</artifactId>
			<version>4.1.4.RELEASE</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-context</artifactId>
			<version>4.1.4.RELEASE</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-orm</artifactId>
			<version>4.1.4.RELEASE</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-test</artifactId>
			<version>4.1.4.RELEASE</version>
			<scope>test</scope>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-jdbc</artifactId>
			<version>4.1.4.RELEASE</version>
		</dependency>
		<dependency>
			<groupId>org.hibernate.javax.persistence</groupId>
			<artifactId>hibernate-jpa-2.1-api</artifactId>
			<version>1.0.0.Final</version>
		</dependency>
		<dependency>
			<groupId>org.hibernate</groupId>
			<artifactId>hibernate-entitymanager</artifactId>
			<version>4.3.10.Final</version>
		</dependency>
		<dependency>
			<groupId>javax.validation</groupId>
			<artifactId>validation-api</artifactId>
			<version>1.0.0.GA</version>
		</dependency>
		<dependency>
			<groupId>org.slf4j</groupId>
			<artifactId>slf4j-api</artifactId>
			<version>1.7.5</version>
		</dependency>
		<dependency>
			<groupId>org.slf4j</groupId>
			<artifactId>slf4j-log4j12</artifactId>
			<version>1.5.6</version>
		</dependency>
		<dependency>
			<groupId>org.jboss.logging</groupId>
			<artifactId>jboss-logging</artifactId>
			<version>3.1.0.CR2</version>
		</dependency>

		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>5.1.10</version>
		</dependency>

		<dependency>
			<groupId>commons-dbcp</groupId>
			<artifactId>commons-dbcp</artifactId>
			<version>1.4</version>
		</dependency>

		<dependency>
			<groupId>org.hsqldb</groupId>
			<artifactId>hsqldb</artifactId>
			<version>2.2.8</version>
		</dependency>
		<dependency>
			<groupId>antlr</groupId>
			<artifactId>antlr</artifactId>
			<version>2.7.6</version>
		</dependency>
		<dependency>
			<groupId>commons-collections</groupId>
			<artifactId>commons-collections</artifactId>
			<version>3.1</version>
		</dependency>
		<dependency>
			<groupId>dom4j</groupId>
			<artifactId>dom4j</artifactId>
			<version>1.6.1</version>
		</dependency>
		<dependency>
			<groupId>javassist</groupId>
			<artifactId>javassist</artifactId>
			<version>3.4.GA</version>
		</dependency>
		<dependency>
			<groupId>javax.transaction</groupId>
			<artifactId>jta</artifactId>
			<version>1.1</version>
		</dependency>
		<dependency>
			<groupId>org.slf4j</groupId>
			<artifactId>slf4j-api</artifactId>
			<version>1.5.6</version>
		</dependency>

	</dependencies>
</project>

EmployeeEntity.java

@Entity(name="EmployeeEntity")
@Table (name="employee")
@NamedNativeQueries({
	@NamedNativeQuery(
			name	=	"getAllEmployees",
			query	=	"SELECT id, firstName, lastName, email, department_id " +
						"FROM employee",
						resultClass = EmployeeEntity.class
	)
})
@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
{
    private static final long serialVersionUID = 1L;
 
    @Id
    @GeneratedValue
    private Integer id;
    private String firstName;
    private String lastName;
    private String email;
     
    @ManyToOne
    private DepartmentEntity department;
     
    public EmployeeEntity() {}
  
    //Setters and Getters
    
    @Override
	public String toString() {
		return "EmployeeEntity [id=" + id + ", firstName=" + firstName
				+ ", lastName=" + lastName + ", email=" + email
				+ ", department=" + department + "]";
	}
}

DepartmentEntity.java

@Entity(name="DepartmentEntity")
@Table (name="department")
public class DepartmentEntity implements Serializable {
 
    private static final long serialVersionUID = 1L;
     
    @Id
    @GeneratedValue
    private Integer id;
    private String name;
     
    public DepartmentEntity(){
    }
 
    public DepartmentEntity(String name) {
        super();
        this.name = name;
    }
    
    @OneToMany(mappedBy="department",cascade=CascadeType.PERSIST)
    private List<EmployeeEntity> employees = new ArrayList<EmployeeEntity>();
     
    //Setters and Getters
   
	 @Override
	    public String toString() {
	        return "DepartmentVO [id=" + id + ", name=" + name + "]";
	    }
}

EmployeeDAO.java

public interface EmployeeDAO 
{
	public List<EmployeeEntity> getAllEmployees();

	public boolean addEmployee(String firstname, String lastName, String email, Integer departmentId);
}

EmployeeDAOImpl.java

@Repository
@Transactional
public class EmployeeDAOImpl implements EmployeeDAO 
{

	@PersistenceContext
	private EntityManager manager;
	
	@Override
	public boolean addEmployee(String firstName, String lastName, String email, Integer departmentId) {
		try 
		{
			StoredProcedureQuery storedProcedure = manager.createStoredProcedureQuery("ADD_EMPLOYEE_PROCEDURE")
					.registerStoredProcedureParameter(0 , String.class , ParameterMode.IN)
					.registerStoredProcedureParameter(1 , String.class, ParameterMode.IN)
					.registerStoredProcedureParameter(2 , String.class, ParameterMode.IN)
					.registerStoredProcedureParameter(3 , Integer.class, ParameterMode.IN);
			
			storedProcedure	.setParameter(0, firstName)
							.setParameter(1, lastName)
							.setParameter(2, email)
							.setParameter(3, departmentId);
			
			storedProcedure.execute();
		} 
		catch (Exception e) {
			e.printStackTrace();
			return false;
		}
		return true;
	}

	@Override
	public List<EmployeeEntity> getAllEmployees() {
		List<EmployeeEntity> employees = manager.createNamedQuery("getAllEmployees", EmployeeEntity.class).getResultList();
		return employees;
	}
}

TestEmployeeDAO.java

@ContextConfiguration(locations = "classpath:application-context.xml")
@RunWith(SpringJUnit4ClassRunner.class)
public class TestEmployeeDAO 
{
	@Autowired
	private EmployeeDAO employeeDAO;
	
	@Autowired
	private DepartmentDAO departmentDAO;
	
	@Test
	@Transactional
	@Rollback(true)
	public void testUpdateEmployee()
	{
		DepartmentEntity dept = new DepartmentEntity();
		dept.setName("IT");
		departmentDAO.addDepartment(dept);
		
		employeeDAO.addEmployee("Lokesh", "Gupta", "howtodoinjava@gmail.com", 1);
				
		Assert.assertEquals(1, employeeDAO.getAllEmployees().size());
	}
}

The output of the above program is below printed in the console.

Hibernate: drop table department if exists
Hibernate: drop table employee if exists
Hibernate: create table department (id integer generated by default as identity (start with 1), name varchar(255), primary key (id))
Hibernate: create table employee (id integer generated by default as identity (start with 1), email varchar(255), firstName varchar(255), lastName varchar(255), department_id integer, primary key (id))
Hibernate: alter table employee add constraint FK_hr5ovw667hkx0jl5cmyo66wb8 foreign key (department_id) references department
HHH000230: Schema export complete
Hibernate: insert into department (id, name) values (default, ?)
binding parameter [1] as [VARCHAR] - [IT]
Hibernate: {call ADD_EMPLOYEE_PROCEDURE(?,?,?,?)}
binding parameter [1] as [VARCHAR] - [Lokesh]
binding parameter [2] as [VARCHAR] - [Gupta]
binding parameter [3] as [VARCHAR] - [howtodoinjava@gmail.com]
binding parameter [4] as [INTEGER] - [1]
Hibernate: SELECT id, firstName, lastName, email, department_id FROM employee
extracted value ([id] : [INTEGER]) - [1]
extracted value ([department_id] : [INTEGER]) - [1]
extracted value ([email] : [VARCHAR]) - [howtodoinjava@gmail.com]
extracted value ([firstName] : [VARCHAR]) - [Lokesh]
extracted value ([lastName] : [VARCHAR]) - [Gupta]

Let me know if you face any problem in running stored procedures using the above example code.

Happy Learning !!

Share this:

  • Twitter
  • Facebook
  • LinkedIn
  • Reddit

About Lokesh Gupta

A family guy with fun loving nature. Love computers, programming and solving everyday problems. Find me on Facebook and Twitter.

Feedback, Discussion and Comments

  1. Marlon Castro

    July 20, 2020

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

    • Lokesh Gupta

      July 20, 2020

      Thanks for pointing out. Fixed it.

  2. maaya

    February 19, 2018

    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.

  3. Anne Racel

    July 26, 2016

    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.

    • Nik

      March 9, 2017

      I am looking for the same.. Have you got the solution..?

Comments are closed on this article!

Search Tutorials

Hibernate Tutorial

  • Hibernate – Introduction
  • Hibernate – Hello World
  • Hibernate – Get/Fetch
  • Hibernate – Persist
  • Hibernate – Merge & Refresh
  • Hibernate – Get Entity Reference
  • Hibernate – BLOB
  • Hibernate – Save Update
  • Hibernate – Persistence LifeCycle
  • Hibernate – SessionFactory
  • Hibernate – Entities Equality
  • Hibernate – Cascade Types
  • Hibernate – Lazy Loading
  • Hibernate – Criteria Queries
  • Hibernate – HQL
  • Hibernate – Named Query
  • Hibernate – Mappings
  • Hibernate – First Level Cache
  • Hibernate – Second Level Cache
  • Hibernate – EhCache Configuration
  • Hibernate – OSCache Configuration
  • Hibernate – C3P0 Connection Pool
  • Hibernate – In memory Database
  • Hibernate – Bean Validation
  • Hibernate – Validator CDI
  • UnexpectedTypeException

Hibernate Annotations

  • Hibernate – JPA 2 Annotations
  • Annotations Vs Mappings
  • Hibernate – @Immutable
  • Hibernate – @NaturalId
  • Hibernate – @OneToMany
  • Hibernate – @ManyToMany
  • Hibernate – @OneToOne

Meta Links

  • About Me
  • Contact Us
  • Privacy policy
  • Advertise
  • Guest and Sponsored Posts

Recommended Reading

  • 10 Life Lessons
  • Secure Hash Algorithms
  • How Web Servers work?
  • How Java I/O Works Internally?
  • Best Way to Learn Java
  • Java Best Practices Guide
  • Microservices Tutorial
  • REST API Tutorial
  • How to Start New Blog

Copyright © 2020 · HowToDoInjava.com · All Rights Reserved. | Sitemap

  • Java 15 New Features
  • Sealed Classes and Interfaces
  • EdDSA (Ed25519 / Ed448)