1

I am attempting to call an Oracle stored function via Spring Data JDBC (NOT JPA but JDBC) with the @Query annotation:

@Repository
public interface UserRepository extends CrudRepository<User, String>{

    
    @Query(value="? = call PKG.get_app_props()")
    Object getLoginProperty();
}

Unfortunately, I'm getting an InvalidDataAccessApiUsageException saying "given one prop, but expected 0". I understand just fine that the "? = " means it's expecting something, but I need to get the result of the function call.

However, I'm having a miserable time finding a) an example of using a @Query annotation for a stored function (even procedures are pretty iffy), and b) finding examples for Spring Data JDBC instead of JPA.

I'll be implementing a RowMapper or ResultsetExtractor once I can get SOMETHING back from the function.

Can anyone help?

Exception per request:

org.springframework.dao.InvalidDataAccessApiUsageException: SQL [? = call PKG.get_app_props()]: given 1 parameters but expected 0
    at org.springframework.jdbc.core.PreparedStatementCreatorFactory$PreparedStatementCreatorImpl.<init>(PreparedStatementCreatorFactory.java:218)
    at org.springframework.jdbc.core.PreparedStatementCreatorFactory$PreparedStatementCreatorImpl.<init>(PreparedStatementCreatorFactory.java:197)
    at org.springframework.jdbc.core.PreparedStatementCreatorFactory.newPreparedStatementCreator(PreparedStatementCreatorFactory.java:171)
    at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.getPreparedStatementCreator(NamedParameterJdbcTemplate.java:426)
    at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.getPreparedStatementCreator(NamedParameterJdbcTemplate.java:399)
    at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.queryForObject(NamedParameterJdbcTemplate.java:243)
    at org.springframework.data.jdbc.repository.query.AbstractJdbcQuery.lambda$singleObjectQuery$1(AbstractJdbcQuery.java:120)
Improve this question
7
  • 1
    Why would you need a RowMapper or ResultsetExtractor once you retrieve something? If you receive something you don't need to convert anything. Please add the full error to the question. All being said you are probably better of using a SimpleJdbcCall instead of an @Query here. Commented Aug 1, 2022 at 14:33
  • I plan to implement those to convert whatever raw data into a useable result, whether that be a hashmap or actual entity. My point was that first I want to get something back, then change it into whatever format I eventually need. The reason for using a @Query is to try to take advantage of the reduced boilerplate code offered via Spring Data. Commented Aug 1, 2022 at 14:37
  • 1
    If you are going to use a RowMapper (which isn't actually possible) you aren't making it any better and are adding boilerplate again yourself. So you actually loose what you gain with what you are trying. Which is why I suggested a SimpleJdbcCall in the first place. Which would take around the same amount of code you have now. Commented Aug 1, 2022 at 14:39
  • Maybe it's just me trying to justify using Spring Data at all then? I'm looking at the arguments to @Query and it allows specifying a row mapper class or resultsetexractor. Am I misunderstanding the arguments to @Query? Commented Aug 1, 2022 at 14:44
  • 1
    I'm not fully up to speed with Spring Data JDBC but looking at your error what you are trying isn't going to work. It should use the JdbcTemplate.call method and not the NamedParameterJebcTemplate.queryForObject. As one needs to register in and out parameters for the function (only OUT as there is no input). So it looks like what you want with a Spring Data JDBC Repository is currently not possible. Commented Aug 1, 2022 at 14:51

1 Answer 1

Reset to default
2

In general I agree with M. Deinum. Spring Data JDBC doesn't have any special support for stored procedures yet and therefore it won't do much for you. In most cases it is therefore best to just get a JdbcTemplateinjected or use a SimpleJdbcCall.

That said there are some limited cases where you can fairly easily call stored procedures using a @Query annotation.

No return value

If you don't have a return value, you can call the stored procedure using the @Modifiying annotation. Consider for example the following stored procedure.

CREATE OR REPLACE PROCEDURE NO_IN_NO_OUT_NO_RETURN
AS BEGIN
    INSERT INTO SOME_ENTITY (NAME) VALUES ('NO_IN_NO_OUT_NO_RETURN');
END;

You may declare the following method in your repository in order to call it:

    @Modifying
    @Query("{call no_in_no_out_no_return()}")
    void noInNoOutNoReturn();

callable in select

When a function adheres to certain constraints it might get called from within a select statement.

Consider this function.

CREATE OR REPLACE FUNCTION SIMPLE_VALUE_OUT
RETURN NUMBER
AS BEGIN
    RETURN 23;
END;

It can be called using this repository method

    @Query("select simple_value_out() from dual")
    int simpleValueOut();

Using the same idea it should be possible to call functions that return a REF CURSOR. Since as far as Spring Data JDBC is concerned these are just queries returning a ResultSet you may apply a RowMapper or similar.

Limitations

IN parameters should be as trivial to pass. OUT parameters on the other hand are not supported at all.

Example code

There is a sample project demonstrating stored procedure calls on GitHub.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thank you. I was trying to use Spring Data to reduce the amount of boilerplate code, shame it isn't supported.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.