Spring Data JPA NamedStoredProcedureQuery Multiple Out Parameters
Solution 1
It looks like @Procedure
expects only one OUT parameter which is binded directly to the method return type...
To handle multiple OUT params you can use the JPA API directly:
StoredProcedureQuery proc = em.createNamedStoredProcedureQuery("plus1");
proc.setParameter("arg", 1);
proc.execute();
Integer res1 = (Integer) proc.getOutputParameterValue("res1");
Integer res2 = (Integer) proc.getOutputParameterValue("res2");
...
Solution 2
You can specify to return one of the multiple out params with the outputParameterName
param in the @Procedure
annotation like this:
@Repository
public interface AdjudConverDateSPRepository extends JpaRepository<AdjudConverDateSP, Long> {
@Procedure(name = "plus1", outputParameterName = "res2")
Integer plus1(@Param("arg") Integer arg);
}
UPDATE 2019-06-24:
Multiple out parameters is now supported in Spring Data JPA 2.2-RC1 https://spring.io/blog/2019/06/17/spring-data-moore-rc1-and-lovelace-sr9-released
https://jira.spring.io/browse/DATAJPA-707
The interface method just needs to have a Map return type so each out param can be accessed by key name:
@Repository
public interface AdjudConverDateSPRepository extends JpaRepository<AdjudConverDateSP, Long> {
@Procedure(name = "plus1")
Map<String, Object> plus1(@Param("arg") Integer arg);
}
Solution 3
Spring Data JPA support multiple output parameters. Return type of Method must be a Map. I spent a lot of time on this. Below link exactly gives example of that, Search for User.plus1IO2.
Solution 4
Spring doesn't support multiple out params just yet. There is a JIRA for this.
hello_world_infinity
Updated on July 09, 2022Comments
-
hello_world_infinity almost 2 years
I have a simple stored procedure I'm using to test out Spring Data JPA Stored Procedure feature.
create or replace procedure plus1inout (arg in int,res1 out int,res2 out int) is BEGIN res1 := arg + 1; res2 := res1 + 1; END;
My code is:
@Repository public interface AdjudConverDateSPRepository extends JpaRepository<AdjudConverDateSP, Long> { @Procedure(name = "plus1") Object[] plus1(@Param("arg") Integer arg); } @Entity @NamedStoredProcedureQuery(name = "plus1", procedureName = "ADJUD.PLUS1INOUT", parameters = { @StoredProcedureParameter(mode = ParameterMode.IN, name = "arg", type = Integer.class), @StoredProcedureParameter(mode = ParameterMode.OUT, name = "res1", type = Integer.class), @StoredProcedureParameter(mode = ParameterMode.OUT, name = "res2", type = Integer.class) }) public class AdjudConverDateSP implements Serializable { //stub to satisfy hibernate identifier requirement @Id @GeneratedValue private Long id; }
Everything works fine when I have a single OUT parameter. But once I add a second OUT parameter I get an exception saying it can't find the procedure in the entity.
Caused by: org.springframework.data.mapping.PropertyReferenceException: No property plus1 found for type AdjudConverDateSP! at org.springframework.data.mapping.PropertyPath.<init>(PropertyPath.java:75) at org.springframework.data.mapping.PropertyPath.create(PropertyPath.java:327) at org.springframework.data.mapping.PropertyPath.create(PropertyPath.java:307) at org.springframework.data.mapping.PropertyPath.from(PropertyPath.java:270) at org.springframework.data.mapping.PropertyPath.from(PropertyPath.java:241) at org.springframework.data.repository.query.parser.Part.<init>(Part.java:76) at org.springframework.data.repository.query.parser.PartTree$OrPart.<init>(PartTree.java:235) at org.springframework.data.repository.query.parser.PartTree$Predicate.buildTree(PartTree.java:373) at org.springframework.data.repository.query.parser.PartTree$Predicate.<init>(PartTree.java:353)