CODEWITHSUNDEEP
javahibernatestored-proceduresspring-dataspring-data-jpa
hello_world_infinity
hello_world_infinity

Reputation: 4830

Spring Data JPA NamedStoredProcedureQuery Multiple Out Parameters

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)

Upvotes: 32

Views: 32288

Answers (5)

Matus Babinsky
Matus Babinsky

Reputation: 315

Hi guys based on Jeff Sheets anwer got my problem solved so I'd like to also help a bit

here is my solution

CREATE PROCEDURE `cardById`(IN id int, out cardNumber varchar(16), out personId bigint)
BEGIN
    SELECT card_number, person_id into cardNumber, personId 
    FROM cards 
    WHERE card_number = id
    LIMIT 1;
END

in entity class

@NamedStoredProcedureQuery(name = "Card.cardById",
    procedureName = "cardById", parameters = {
    @StoredProcedureParameter(mode = ParameterMode.IN, name = "id", type = String.class),
    @StoredProcedureParameter(mode = ParameterMode.OUT, name = "cardNumber", type = String.class),
    @StoredProcedureParameter(mode = ParameterMode.OUT, name = "personId", type = Long.class)

In repository

@Procedure(name = "Card.cardById")
Map<String, Object> cardById(String id);

happy coding!

Upvotes: 2

Vikky
Vikky

Reputation: 1223

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.

User.java

UserRepository.java

Upvotes: 2

Jeff Sheets
Jeff Sheets

Reputation: 1209

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);
}

Upvotes: 7

Tezuka
Tezuka

Reputation: 21

Spring doesn't support multiple out params just yet. There is a JIRA for this.

Upvotes: 2

Julien
Julien

Reputation: 1097

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");
...

Upvotes: 9

Related Questions