Java, MySQL, Software Development, Spring Boot

Spring Boot JPA – Stored Procedure With Select Statement

Sometimes we need to reuse stored procedures when moving to Spring Boot JPA/ORM because they still serve their purpose. We wouldn’t want to reinvent things but reuse them instead. Although stored procedures traditionally do not return data, we can use a SELECT statement to produce rows of records with the help of Spring Data @Procedure annotation. This post shows how to call a stored procedure that returns rows from a SELECT statement in Spring Boot JPA.

The items we use include Java 17, Spring Boot (Spring Data JPA with Hibernate), and MySQL 8.

Reuse An Existing Stored Procedure in Spring Boot

Let’s say we have the following stored procedure that we need to reuse. Its purpose is to generate a list of rows representing a Graph dependency list (akin to the dependency matrix concept in Computer Science) for a couple of nodes.

When we manually run the stored procedure, we would get a similar result below.

Spring Boot Stored Procedure with SELECT statement

How do we run this stored procedure with a SELECT statement in a Spring Boot JPA application?

Things We Need To Capture Rows From a Stored Procedure

First, we need to use the SqlResultSetMapping annotation, and we can place it atop an Entity class closely related to our SELECT results. For example, we could use the annotation shown below because the SQL SELECT results represent all relationships between two nodes.

Also, we are using a DTO class – NodeHierarchyRelationship – which mirrors the SELECT projection the stored procedure returns. Therefore, we will a list of NodeHierarchyRelationship with data the stored procedure returned.

Then, we create a service method that calls the stored procedure.

How does this tie back to the SqlResultSetMapping in the Entity class Relationship? Aside from the stored procedure name, we also use the “node-hierarchy-custom-dto-mapping” name, which we specified for the SqlResultSetMapping annotation.

Please note that we need to use an instance of EntityManager, which we can easily auto-wire to our service class and invoke the stored procedure as a “native query.”

Loading

Got comments or suggestions? We disabled the comments on this site to fight off spammers, but you can still contact us via our Facebook page!.


You Might Also Like