How to get multiple result lists from store procedure to spring boot side

user_5598882 0
Viewed 1464

I am trying to fetch multiple results from a store procedure to spring side but somehow I am getting this error:

org.hibernate.exception.SQLGrammarException: Error extracting results from CallableStatement
    at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:106)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111)
    at org.hibernate.result.internal.OutputsImpl.convert(OutputsImpl.java:79)
    at org.hibernate.result.internal.OutputsImpl.extractResults(OutputsImpl.java:139)
    at org.hibernate.result.internal.OutputsImpl.extractCurrentResults(OutputsImpl.java:127)

My store procedure is like, Example:

DELIMITER ;;

CREATE PROCEDURE `USER_LIST`()
BEGIN

   SET @sqlSelect = 'SELECT * from profile WHERE is_email_verified = 0';

   PREPARE stmt FROM @sqlSelect;
   EXECUTE stmt;

   SET @sqlSelect = 'SELECT * from user WHERE is_phone_verified = 0';

   PREPARE stmt FROM @sqlSelect;
   EXECUTE stmt;


END;;
DELIMITER ;

In Spring side:

StoredProcedureQuery storedProcedure = entityManager.createStoredProcedureQuery("USER_LIST");

        // Execute stored procedure
        storedProcedure.execute();

        List<Object[]> storedProcedureResults = storedProcedure.getResultList();

        if(storedProcedure.hasMoreResults())
        {
            System.out.println("Print");
        }

I am getting that error in if condition where I write storedProcedure.hasMoreResults().

Is there any possible way to get both the result list at spring side?

Please help me out to solve this out. Thanks in advance.

jpa stored-procedures hibernate spring java
1 Answers

Hibernate only returns the first result set returned by the procedure call. That means you'll be able to retrieve only the result set of Profile table if you map it correctly to its corresponding entity at Spring side. However if you need the result of both tables, join the result set of both tables using union. And then you need to map it to an entity which must have all the columns of both tables at spring side.

user_89982600
Related