CODEWITHSUNDEEP
javaspring-bootjpastored-proceduresspring-data-jpa
Alexander Stepchkov
Alexander Stepchkov

Reputation: 755

Spring Boot Data JPA receive Multiple Out Parameters from stored procedure

I'm trying to call stored procedure with multiple out parameters via Spring Boot Data JPA (v2.2.6), but receive an error:

DEBUG [http-nio-8080-exec-1] org.hibernate.engine.jdbc.spi.SqlStatementLogger: {call TEST_SP(?,?,?)}
RACE [http-nio-8080-exec-1] org.hibernate.type.descriptor.sql.BasicBinder: binding parameter [DOC_NAME] as [VARCHAR] - [ololo]
ERROR [http-nio-8080-exec-1] org.apache.juli.logging.DirectJDKLog: Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessApiUsageException: OUT/INOUT parameter not available: DOC_ID; nested exception is java.lang.IllegalArgumentException: OUT/INOUT parameter not available: DOC_ID] with root cause
java.lang.IllegalArgumentException: OUT/INOUT parameter not available: DOC_ID

Stored procedure in MS SQL Server 2012:

CREATE PROCEDURE [dbo].[TEST_SP]
    @DOC_ID bigint output,
    @DOC_GUID nvarchar(255) output,
    @DOC_NAME nvarchar(255)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT @DOC_ID = 6666, @DOC_GUID = @DOC_NAME
END

Here my entity (it is not related to SP):

@Data
@Entity
@NamedStoredProcedureQuery(name = "SomeEntity.test", procedureName = "TEST_SP", parameters = {
        @StoredProcedureParameter(mode = ParameterMode.OUT, name = "DOC_ID", type = Long.class),
        @StoredProcedureParameter(mode = ParameterMode.OUT, name = "DOC_GUID", type = String.class),
        @StoredProcedureParameter(mode = ParameterMode.IN, name = "DOC_NAME", type = String.class)
})
@Table(name = "DOCUMENT_DATA")
public class SomeEntity {
    // some variables
}

Here my repository:

public interface SomeRepository extends JpaRepository<SomeEntity, Long> {

    @Procedure(name = "SomeEntity.test")
    Map<String, Object> testSp(@Param("DOC_NAME") String docName);
}

Here how I call it:

@RestController
public class Controller {

    @Autowired
    SomeRepository repository;

    @GetMapping("test")
    String test() {
        return repository.testSp("ololo").toString();
    }
}

As example I used these links: https://github.com/spring-projects/spring-data-jpa/blob/master/src/test/java/org/springframework/data/jpa/domain/sample/User.java#L77 https://github.com/spring-projects/spring-data-jpa/blob/e27933455efa6d1821dea23abd2bbe109b5d59a7/src/test/java/org/springframework/data/jpa/repository/sample/UserRepository.java#L362

Upd: I've tried with Oracle 11g

CREATE OR REPLACE PROCEDURE test_sp(x OUT INTEGER, y OUT INTEGER) AS
BEGIN
  x := 17; y := 93;
END;


@NamedStoredProcedureQuery(name = "EstatementsDataEntity.test", procedureName = "test_sp", parameters = {
        @StoredProcedureParameter(mode = ParameterMode.OUT, name = "x", type = Integer.class),
        @StoredProcedureParameter(mode = ParameterMode.OUT, name = "y", type = Integer.class)
})

But receive almost identical error:

java.lang.IllegalArgumentException: OUT/INOUT parameter not available: 1

Upvotes: 6

Views: 10918

Answers (2)

Parawata
Parawata

Reputation: 61

FYI for anyone still using older versions of Spring Boot. This error was caused by calling a procedure without enclosing it in a transaction. Error message was a bit of a red herring but the fix was to add @Transaction to the caller.

In the example above I'd include a service layer class between Controller and SomeRepository and annotate my service layer method as transactional.

i.e.

@Service
public class SomeService{
    
    @Autowired
    SomeRepository someRepo;

    @Transactional
    public Map<String, Object> testSp(String docName){
        return someRepo.testSp(docName);
    }
}

Upvotes: 5

Alexander Stepchkov
Alexander Stepchkov

Reputation: 755

I could not find working solution with annotations and submitted a bug DATAJPA-1722

But I could managed to solve issue using EntityManager:

@SpringBootTest
class DemoApplicationTests {

    @PersistenceContext
    private EntityManager em;

    @Test
    void callStoredProcedureUsingEntitiManager() {
        StoredProcedureQuery proc = em.createStoredProcedureQuery("test_sp");
        proc.registerStoredProcedureParameter("x", Integer.class, ParameterMode.OUT);
        proc.registerStoredProcedureParameter("y", Integer.class, ParameterMode.OUT);
        proc.execute();
        assertThat(proc.getOutputParameterValue("x")).isEqualTo(17);
        assertThat(proc.getOutputParameterValue("y")).isEqualTo(93);
    }

}

Upvotes: 4

Related Questions