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:

@Repository
public class LegacyDataAccessRepositoryImpl implements LegacyDataAccessRepository {
	@PersistenceContext
	private EntityManager entityManager;

	@Override
	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 storedProcedureResults.stream().map(result -> new MyObject(
	         (Integer) result[0],
	         (String) result[1]
	   )).collect(Collectors.toList());

	}
}

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.