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
}
Above stored procedure can be used to add an employee record in 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.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;
}
Complete Example
Now let’s look at all the files involved into 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());
}
}
Output of above program is below printed in 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 procedure using above example source code.
Happy Learning !!
Anne Racel
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
I am looking for the same.. Have you got the solution..?