Calling stored procedure using Spring JPA

Over the last week, I have had a chance to develop a spring boot microservice, which fetches data from three data sources. All three data sources were exposed as stored procedures.

Spring Data JPA provides a handy set of annotations for storing procedures. I thought it might help some other fellow developers if I shared how to do it nice and clean without falling into common pitfalls.

I organised the steps to follow in this article. I used an AWS MariaDB instance for the sample in this article.

1. Create the table and populate the data using following SQL.

2. Create a stored procedure as named “get_all_employees” using mysql work bench.

i. Right mouse click on the Routines and choose “Create Procedure…” menu item.

Create procedure on mysql workbench

ii. Create the procedure logic content.

Write the select all query as the procedure logic

iii. Apply the SQL script to the database.

iv. Call the stored procedure in the bench to test it.

Test the procedure.

3. All set from the database side. Let’s get the code done. Create a spring boot project using https://start.spring.io/ with spring-boot-starter-data-jpa and spring-boot-starter-web dependencies (You can find the pom.xml ) and create the usual spring package structure with controller, domain, and repository.

4. Create the domain object Employees as shown below.

The key points to note in the above class are can be described as follows.

@NamedStoredProcedureQueries

Annotation do the magic for you it is the tool which use to call the stored procedures.

@NamedStoredProcedureQuery(name = "getAllEmployees",
                            procedureName = "get_all_employees",
resultClasses = Employees.class)

The @NamedStoredProcedureQuery points to the stored procedure by “procedureName” attribute.

And the “name” attribute creates the application-scoped reference for the named stored procedure query. You can use this call the stored procedure. “resultClasses” attribute is the bean which holds the result.

5. The next tricky bit is how to call this NamedStoredProcedureQuery using the JPA repository. The spring documentation advice to use @ Procedure annotation to call it.

@Procedure(name = "getAllEmployees")
List<Employees> getAllEmployees();

I have encountered various errors when I tried to use @Procedure annotation. And further reading of the issue ledes me to another way to use the NamedStoredProcedure via the usual JPA repository.

Create a custom repository interface

public interface EmployeeRepositoryCustom {

    List<Employees> getAllEmployees();
}

Create repository which extends the CrudRepository<> and EmployeeRepositoryCustom. This will allow you to have all CrudRepositoy features and extend it with your custom repository.

public interface EmployeeRepository extends CrudRepository<Employees, Long>, EmployeeRepositoryCustom {

}

Finally, create the EmployeeRepositoryImpl which extends EmployeeRepositoryCustom.

Note the use of @PersistenceContext to inject the entityManager to access the NamedStoredProcedure by given name (getAllEmployees).

public class EmployeeRepositoryImpl implements EmployeeRepositoryCustom {

    @PersistenceContext
    private EntityManager em;


    @Override
    public List<Employees> getAllEmployees() {
        StoredProcedureQuery findByYearProcedure =
              em.createNamedStoredProcedureQuery("getAllEmployees");
        return findByYearProcedure.getResultList();
    }
}

Now you can inject the EmployeeRepository to your service layer as usual.

Hope this helps to another fellow developer. :). The full source code is available at GitHub.