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 !!
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.
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.
I am looking for the same.. Have you got the solution..?