1. Introduction
In this quick tutorial, we’ll explore the use of Stored Procedures within the Java Persistence API (JPA).
2. Project Setup
2.1. Maven Setup
We first need to define the following dependencies in our pom.xml:
- a JPA API implementation – in this example, we will use Hibernate via hibernate-core, but EclipseLink would be an OK alternative as well
- jakarta.xml.bind-api
- javax.annotation-api
- a MySQL Database connector – mysql-connector-java
<properties>
<mysql.version>8.0.33</mysql.version>
<hibernate.version>6.2.0.Final</hibernate.version>
<jakarta.xml.bind-api.version>4.0.0</jakarta.xml.bind-api.version>
<javax.annotation.version>1.3.2</javax.annotation.version>
</properties>
<dependencies>
<dependency>
<groupId>org.hibernate.orm</groupId>
<artifactId>hibernate-core</artifactId>
<version>${hibernate.version}</version>
<exclusions>
<exclusion>
<groupId>jakarta.xml.bind</groupId>
<artifactId>jakarta.xml.bind-api</artifactId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>jakarta.xml.bind</groupId>
<artifactId>jakarta.xml.bind-api</artifactId>
<version>${jakarta.xml.bind-api.version}</version>
</dependency>
<dependency>
<groupId>javax.annotation</groupId>
<artifactId>javax.annotation-api</artifactId>
<version>${javax.annotation.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="https://jakarta.ee/xml/ns/persistence"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" version="3.0"
xsi:schemaLocation="https://jakarta.ee/xml/ns/persistence https://jakarta.ee/xml/ns/persistence/persistence_3_0.xsd">
<persistence-unit name="jpa-db">
<provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>
<class>com.baeldung.jpa.model.Car</class>
<properties>
<property name="jakarta.persistence.jdbc.driver" value="com.mysql.jdbc.Driver" />
<property name="jakarta.persistence.jdbc.url" value="jdbc:mysql://127.0.0.1:3306/baeldung" />
<property name="jakarta.persistence.jdbc.user" value="baeldung" />
<property name="jakarta.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
Let’s now create a Table ( CAR ) – with three attributes: ID, MODEL and YEAR:
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
Below the Car entity that well be mapped to the CAR database table by the Entity Manager.
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
In this tutorial we discussed using Stored Procedure with the Java Persistence API.
The example used in this article is available as a sample project in GitHub.