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)
Jason
  • 3,943
  • 12
  • 64
  • 104
  • 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. – M. Deinum Aug 01 '22 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. – Jason Aug 01 '22 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. – M. Deinum Aug 01 '22 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? – Jason Aug 01 '22 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. – M. Deinum Aug 01 '22 at 14:51
  • That honestly doesn't surprise me, but I was hoping someone could say otherwise. This wouldn't be the first application that is heavily dependent on stored procedures and functions, which is why Spring Data JPA didn't make much sense. I was hoping the JDBC flavor would do so. – Jason Aug 01 '22 at 14:55
  • Well adding an `@Query` which calls a function doesn't add much imho over a `SimpleJdbcCall`. As you still need to configure the output and the mapping either way. – M. Deinum Aug 01 '22 at 14:56

1 Answers1

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.

Jens Schauder
  • 77,657
  • 34
  • 181
  • 348
  • Thank you. I was trying to use Spring Data to reduce the amount of boilerplate code, shame it isn't supported. – Jason Aug 02 '22 at 10:03