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.
This is cool!
With thanks! Valuable information!
very good submit, i definitely love this website, carry on it
With thanks! Valuable information!
Am trying to implement similar stored procedures via spring boot. So are you suggesting we dont map the db table to an entity via orm? do you have a sample project on github?
Hi Masibonge, thanks for commenting.
When working with legacy data, it is very hard to utilize the ORM. If you have 100 tables, it doesn’t make sense to make 100 entity tables for one stored procedure. The ‘MyObject’ class is not an ORM entity. It is a simple Java class. This is also a more simple approach.
Learn more about the ORM vs Stored Procedure debate here: https://stevenhorvatin.com/2016/09/22/database-query-orm-vs-sql/
Cheers!