jpa-stored-procedures
A Guide to Stored Procedures with JPA
1. Introduction
2. Project Setup
-
javax.javaee-api – as it includes the JPA API
-
a JPA API implementation – in this example we will use Hibernate, but EclipseLink would be an OK alternative as well
-
a MySQL Database
<properties>
<jee.version>7.0</jee.version>
<mysql.version>11.2.0.4</mysql.version>
<hibernate.version>5.1.38</hibernate.version>
</properties>
<dependencies>
<dependency>
<groupId>javax</groupId>
<artifactId>javaee-api</artifactId>
<version>${jee.version}</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-entitymanager</artifactId>
<version>${hibernate.version}</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>${mysql.version}</version>
</dependency>
</dependencies>
2.2. Persistence Unit Definition
The second step is the creation of src/main/resources/META-INF/persistence.xml file – which contains the persistence unit definitions:
<?xml version="1.0" encoding="UTF-8"?>
<persistence xmlns="http://java.sun.com/xml/ns/persistence"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/persistence
http://java.sun.com/xml/ns/persistence/persistence_2_1.xsd"
version="2.1">
<persistence-unit name="jpa-db">
<provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>
<class>com.baeldung.jpa.model.Car</class>
<properties>
<property name="javax.persistence.jdbc.driver"
value="com.mysql.jdbc.Driver" />
<property name="javax.persistence.jdbc.url"
value="jdbc:mysql://127.0.0.1:3306/baeldung" />
<property name="javax.persistence.jdbc.user"
value="baeldung" />
<property name="javax.persistence.jdbc.password"
value="YourPassword" />
<property name="hibernate.dialect"
value="org.hibernate.dialect.MySQLDialect" />
<property name="hibernate.show_sql" value="true" />
</properties>
</persistence-unit>
</persistence>
All Hibernate properties defined are not needed if you refer to a JNDI DataSource (JEE environments):
<jta-data-source>java:jboss/datasources/JpaStoredProcedure</jta-data-source>
2.3. Table Creation Script
CREATE TABLE `car` (
`ID` int(10) NOT NULL AUTO_INCREMENT,
`MODEL` varchar(50) NOT NULL,
`YEAR` int(4) NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
The assumption was, of course, that the DB schema and permissions are already in place.
2.4. Stored Procedure Creation on DB
The very last step before jump to the java code is the stored procedure creation in our MySQL Database:
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `FIND_CAR_BY_YEAR`(in p_year int)
begin
SELECT ID, MODEL, YEAR
FROM CAR
WHERE YEAR = p_year;
end
$$
DELIMITER ;
3. The JPA Stored Procedure
We are now ready to use JPA to communicate with the database and execute the stored procedure we defined.
Once we do that, we’ll also be able to iterate over the results as a List of Car.
3.1. The Car Entity
Notice that we’re also defining our stored procedure directly on the entity by using the @NamedStoredProcedureQueries annotation:
@Entity
@Table(name = "CAR")
@NamedStoredProcedureQueries({
@NamedStoredProcedureQuery(
name = "findByYearProcedure",
procedureName = "FIND_CAR_BY_YEAR",
resultClasses = { Car.class },
parameters = {
@StoredProcedureParameter(
name = "p_year",
type = Integer.class,
mode = ParameterMode.IN) })
})
public class Car {
private long id;
private String model;
private Integer year;
public Car(String model, Integer year) {
this.model = model;
this.year = year;
}
public Car() {
}
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "ID", unique = true, nullable = false, scale = 0)
public long getId() {
return id;
}
@Column(name = "MODEL")
public String getModel() {
return model;
}
@Column(name = "YEAR")
public Integer getYear() {
return year;
}
// standard setter methods
}
3.2. Accessing the Database
Now, with everything defined and in place, let’s write a couple of tests actually using JPA to execute the procedure.
We are going to retrieve all Cars in a given year:_
_
public class StoredProcedureTest {
private static EntityManagerFactory factory = null;
private static EntityManager entityManager = null;
@BeforeClass
public static void init() {
factory = Persistence.createEntityManagerFactory("jpa-db");
entityManager = factory.createEntityManager();
}
@Test
public void findCarsByYearWithNamedStored() {
StoredProcedureQuery findByYearProcedure =
entityManager.createNamedStoredProcedureQuery("findByYearProcedure");
StoredProcedureQuery storedProcedure =
findByYearProcedure.setParameter("p_year", 2015);
storedProcedure.getResultList()
.forEach(c -> Assert.assertEquals(new Integer(2015), ((Car) c).getYear()));
}
@Test
public void findCarsByYearNoNamedStored() {
StoredProcedureQuery storedProcedure =
entityManager
.createStoredProcedureQuery("FIND_CAR_BY_YEAR",Car.class)
.registerStoredProcedureParameter(1, Integer.class, ParameterMode.IN)
.setParameter(1, 2015);
storedProcedure.getResultList()
.forEach(c -> Assert.assertEquals(new Integer(2015), ((Car) c).getYear()));
}
}
Notice that in the second test, we’re no longer using the stored procedure we defined on the entity. Instead, we’re are defining the procedure from scratch.
That can be very useful when you need to use stored procedures but you don’t have the option to modify your entities and recompile them.
4. Conclusion
The example used in this article is available as a sample project in GitHub.