Stored procedure call with Spring JPA

Question

I need to invoke a stored procedure using the JPA. The stored procedure operates on multiple tables and return some of the columns from these tables.

Tried with the @Procedure it doesn't seem to work, always the stored procedure is not found in this case.

Directly calling the procedure using native query was successful, but with this approach, I am need to map the result returned to List of an Object.

My implementation in the repository looks like below,

@Query(value = "EXECUTE dbs.multitable_Test :inputObj", nativeQuery = true)
List<sp> multitable_Test(@Param("inputObj")String inputObj);

The result returned from the stored procedure needs to be mapped to the sp class.

How can this be achieved while we have multiple tables response in the single result set?

Already tried with the attributeConvert from this link, still getting the below exception.

org.springframework.core.convert.ConverterNotFoundException: No converter found capable of converting from type

Any help with this is appreciated.

Not that I know of. JPA is an layer on top of relational databases that allows to pretend that you're dealing only with objects. Stored procedures are not a good fit in that model. You're already using Spring. Just use Spring JDBC template to manage it. — duffymo, Dec 06 '20 at 17:30
You can have a look [here](https://stackoverflow.com/questions/3572626/how-to-call-a-stored-procedure-from-java-and-jpa/70788256#70788256), I hope it helps : [How to call a stored procedure from Java and JPA](https://stackoverflow.com/questions/3572626/how-to-call-a-stored-procedure-from-java-and-jpa/70788256#70788256) — RED-ONE, Jun 05 '23 at 22:44

score 2 · Answer 1 · answered Dec 07 '20 at 15:58

Firstly, this is not really the use case for procedure. Procedure is meant to modify data on the database without any return value, then you could use:

@Procedure(procedureName = "procedure_name")
void procedure(); // notice void

You should rather use a function using create function syntax. Function can modify data and return the result.

Secondly if you want to map it to some class, I see two solutions (using EntityManager):

Using ResultTransformer:

entityManager.createNativeQuery(
    "select * from function_name(:parameter)"
)
    .setParameter("parameter", parameter)
    .unwrap(org.hibernate.query.NativeQuery.class)
    .setResultTransformer(new ResultTransformer() {
        @Override
        public Object transformTuple(Object[] tuple, String[] aliases) {
            return new Sp(tuple[0]);
        }

        @Override
        public List transformList(List collection) {
            return collection;
        }

    })
    .getResultList();

Note that ResultTransformer is deprecated, but is so powerful, it will not be removed until there is a sensible replacement, see the note from hibernate developer.

Using ResultSetMapping. Place the proper annotation over an entity:

@SqlResultSetMapping(
    name = "sp_mapping",
    classes = @ConstructorResult(
        targetClass = Sp.class,
        columns = {
            @ColumnResult(name = "attribute", type = Long.class)
        })
)

And invoke the function using the mapping as parameter:

entityManager.createNativeQuery(
    "select * " +
    "from function_name(:parameter);",
    "sp_mapping"
)
    .setParameter("parameter", parameter)
    .getResultList();

score 0 · Answer 2 · answered Mar 31 '23 at 19:32

In case someone else runs into this, according to baeldung document, it looks like you just need to replace EXECUTE with CALL like

@Query(value = "CALL dbs.multitable_Test(:inputObj);", nativeQuery = true)
List<sp> multitable_Test(@Param("inputObj")String inputObj);

Source: https://www.baeldung.com/spring-data-jpa-stored-procedures#query-annotation

Stored procedure call with Spring JPA

2 Answers2