Call stored procedure with Repository

Question

I'm trying to call database stored procedure through CrudRepository method (spring-jpa).

This is the code of my repository

public interface TestRepo extends JpaRepository<NewsContainer, Long> {

@Procedure(procedureName = "ncMaxVisualisations")
public Long getMaxNumberOfVisualisations(); 
}

This is the code of class test

public class RankingTest {

 private Logger logger = Logger.getLogger(RankingTest.class);

 @Autowired
 TestRepo repo;

 @Autowired
 EntityManager em;

 @Test
 public void testWithEM(){
    StoredProcedureQuery store = em.createStoredProcedureQuery("ncMaxVisualisations");
    int visualisations = (int) store.getSingleResult();
    System.out.println(visualisations);
 }


 @Test
 public void testWithRepository(){
    System.out.println(repo.getMaxNumberOfVisualisations());
 }  
}

The output of test is

testWithRepository() 
Hibernate: {call ncMaxVisualisations(?)}
  2014-10-24 17:47:28 WARN  SqlExceptionHelper:144 - SQL Error: 0, SQLState: S1009
  2014-10-24 17:47:28 ERROR SqlExceptionHelper:146 - Parameter number 1 is not an OUT    parameter
testWithEM()
Hibernate: {call ncMaxVisualisations()}
  50

If I call stored procedure with repository it waits parameter, while if I call it using EntityManager it works fine. Why?

Thanks for your support.

Regards. Mauro

Hell, 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:48

score 2 · Accepted Answer · edited Oct 23 '15 at 17:38

2

AFAIK, Spring expects a parameter of type OUT where it'll read your output value.

I did a test for this:

Procedure:

create or replace PROCEDURE TESTPROCEDURE 
(
  TEST1 OUT NUMBER
) AS 
BEGIN
  TEST1 := 50;

END TESTPROCEDURE;

Mapping:

@Procedure(procedureName = "TESTPROCEDURE", outputParameterName = "TEST1")
public Long callTestProcedure();

Hope it helps :)

edited Oct 23 '15 at 17:38

Nicolas C

1,584
2
17
33

answered Apr 17 '15 at 14:14

Alex

311
2
17

Call stored procedure with Repository

1 Answers1