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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | DELIMITER // DROP PROCEDURE IF EXISTS getNodeHierarchy // CREATE PROCEDURE `getNodeHierarchy`(IN param_node1Id int, IN param_node2Id int, IN param_tenantId int) BEGIN WITH RECURSIVE nodematrix AS (SELECT a.id, a.tenant_id, a.bidirectional, a.weight, a.v1_id, a.name, a.v2_id, 0 AS hierarchy_level FROM relationships a WHERE a.v1_id = param_node1Id and a.tenant_id = param_tenantId UNION SELECT b.id, b.tenant_id, b.bidirectional, b.weight, b.v1_id, b.name, b.v2_id, hierarchy_level + 1 FROM relationships b JOIN nodematrix ON b.v1_id = nodematrix.v2_id WHERE (b.v1_id = param_node2Id or b.v2_id = param_node2Id) and b.tenant_id = param_tenantId) SELECT * FROM nodematrix ; END // DELIMITER ; |
When we manually run the stored procedure, we would get a similar result below.
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | @SqlResultSetMapping(name= "node-hierarchy-custom-dto-mapping", classes = { @ConstructorResult(targetClass = NodeHierarchyRelationship.class, columns = { @ColumnResult(name="id",type = Long.class), @ColumnResult(name="tenant_id",type = Long.class), @ColumnResult(name="bidirectional",type = Boolean.class), @ColumnResult(name="weight",type = Double.class), @ColumnResult(name="v1_id",type = Long.class), @ColumnResult(name="name",type = String.class), @ColumnResult(name="v2_id",type = Long.class), @ColumnResult(name="hierarchy_level",type = Integer.class), } ) }) @Entity @Table(name = "relationships", indexes = { @Index(name="relationshipsIndex1", columnList = "bidirectional, name, tenant_id, v1_id, v2_id") }) public class Relationship { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Long id; //... } |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | public RelationshipServiceResponse getRelationshipsBetweenNodes(String node1ReferenceId, String node2ReferenceId, String tenantReferenceId) { Tenant tenant = getTenant(tenantReferenceId); Node node1 = getNode(node1ReferenceId, tenant); Node node2 = getNode(node2ReferenceId, tenant); List<NodeHierarchyRelationship> result = entityManager .createNativeQuery("call getNodeHierarchy(:param_node1Id,:param_node2Id, :param_tenantId)", "node-hierarchy-custom-dto-mapping") .setParameter("param_node1Id", node1.getId()) .setParameter("param_node2Id", node2.getId()) .setParameter("param_tenantId", tenant.getId()) .getResultList(); //... } |
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.”
