A Guide to Stored Procedures with JPA

1. Introduction

In this quick tutorial, we’ll explore the use of Stored Procedures within the Jakarta 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, which implements Jakarta Persistence 3.1
• jakarta.xml.bind-api
• jakarta.annotation-api
• a MySQL Database connector – mysql-connector-java

<properties>
    <mysql.version>8.4.0</mysql.version>
    <hibernate.version>6.5.2.Final</hibernate.version>
    <jakarta.xml.bind-api.version>4.0.0</jakarta.xml.bind-api.version>
    <jakarta.annotation.version>3.0.0</jakarta.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>jakarta.annotation</groupId>
        <artifactId>jakarta.annotation-api</artifactId>
        <version>${jakarta.annotation.version}</version>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-j</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" />
            <property name="hibernate.proc.param_null_passing" 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 were already in place.

2.4. Stored Procedure Creation on DB

The very last step before jumping 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 is the Car entity that will 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 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 no longer use the stored procedure defined on the entity. Instead, we 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. Passing Null Params to Stored Procedures

When we call a stored procedure, Hibernate does not pass null values to the database by default. The objective of not passing null parameters to stored procedures is to allow any default argument values to be applied. However, we can set the hibernate.proc.param_null_passing property to true (the default is false) in the src/main/resources/META-INF/persistence.xml file to pass null values to stored procedures, as we did in this article.

We can verify using a JUnit 5 assertion that we can set params to null:

@Test
public void givenStoredProc_whenNullParamPassed_thenNoExceptionThrown() {
    final StoredProcedureQuery storedProcedure = 
      entityManager.createStoredProcedureQuery("FIND_CAR_BY_YEAR", Car.class)
        .registerStoredProcedureParameter(1, Integer.class, ParameterMode.IN);
             
    assertDoesNotThrow(() -> {
      storedProcedure.setParameter(1, null);
        });
    }

Further, when we set the hibernate.proc.param_null_passing property to true in the persistence.xml file, it is applied globally. Alternatively, we can enable passing null param on a per stored procedure call basis:

ProcedureCall procedureCall = getSession()
  .createStoredProcedureCall("findByYearProcedure");

procedureCall
  .registerParameter("nullable_param", String.class, ParameterMode.IN)
  .enablePassingNulls(true);

5. Conclusion

In this tutorial, we discussed using Stored Procedure with the Java Persistence API.