How to Call a Stored Procedure with Spring Boot Using JPA/Hibernate

Working with legacy data is hard. Using an ORM with legacy data is even harder. I was recently creating a REST API in the context of a legacy database. I originally tried to generate Hibernate entities but came to the conclusion that the legacy data was too messy for the ORM to be effective. Many other tutorials demonstrate on how to call stored procedures. The main issue I ran into is that these tutorials show you how to call stored procedures and “auto-map” them to Hibernate entities. Automatically mapping them to hibernate objects didn’t work for my application. This may have been because my database schema was very old.

First create your repository interface:

public interface LegacyDataAccessRepository {
	List<MyObject> getSomeLegacyData(String firstParameter);

Now create the implementation of the repository:

public class LegacyDataAccessRepositoryImpl implements LegacyDataAccessRepository {
	private EntityManager entityManager;

	pubilc List<MyObject> getSomeLegacyData(String firstParameter){
	   StoredProcedureQuery storedProcedure = entityManager.createStoredProcedureQuery("StoredProcName");
	   // Set the parameters of the stored procedure.
	   String firstParam = "firstParam";
	   storedProcedure.registerStoredProcedureParameter(firstParam, String.class, ParameterMode.IN);
	   storedProcedure.setParameter(firstParam, firstParameter);

	   // Call the stored procedure. 
	   List<Object[]> storedProcedureResults = storedProcedure.getResultList();

	   // Use Java 8's cool new functional programming paradigm to map the objects from the stored procedure results
	   return -> new MyObject(
	         (Integer) result[0],
	         (String) result[1]


As we can see, calling stored procedures in Spring Boot is easy. This implementation is not specific to Spring Boot or Hibernate. This will work with any framework which implements the JPA specification. The only thing that would change would be obtaining the EntityManager object.

Database Query: ORM vs SQL

Object relational mappers (ORM) are utilized to abstract database access into your preferred object oriented programming language. On the other hand, raw SQL statements are a more direct way of communicating with a database. Usually raw SQL statements are either embedded into your database access layer or stored in your database as a stored procedure. Each approach has its advantages and disadvantages.

  • ORM
    • Advantages
      • Switching and upgrading databases are easier.
      • Great for simple CRUD.
      • In theory, one object can represent an entire entity and be passed through an entire application.
      • SQL injection is usually taken care of ORM frameworks.
      • Database tables can be generated from objects.
      • Objects can be generated from database tables.
      • Easy to store in source control.
    • Disadvantages
      • Slower than SQL.
      • More memory usage than SQL.
      • Have to learn quirky bugs for the specific ORM one is utilizing.
  • SQL Embedded Into DAL
    • Advantages
      • Faster and more expressive than ORM.
      • Easy to store in source control.
    • Disadvantages
      • Slower than stored procedure due to not being compiled and having to send the raw SQL over the network.
      • Manual intervention to prevent SQL injection is required.
  • Stored Procedure
    • Advantages
      • Faster and more expressive than ORM.
      • Faster than SQL embedded into the database access layer.
      • Creates a distinct separation of database logic and application logic.
    • Disadvantages
      • Requires extra steps to put in source control.
      • When stepping through code, debugging requires more work than other database access methods because one has to navigate to the stored procedure in order to see the database logic.

As one can see, each database access method has its advantages and disadvantages. Usually, the most important consideration when choosing a database access method is the complexity of the query. For simple CRUD queries,  the ORM tends to be best. For very complex queries, which usually involve data manipulation and calculation, SQL tends to be the way to go.  Most large applications tend to utilize both the ORM and SQL for database access.

Which method do you prefer? Feel free to comment below!