CODEWITHSUNDEEP
hibernatejpastored-proceduresmultiple-resultsets
laloumen
laloumen

Reputation: 1369

Using JPA with stored procedures that return multiple result sets

Is it possible to handle multiple result sets having different columns from a stored procedure in SQL Server using JPA? What is a way (or the best way) to do this?

I am currently working with Hibernate 4.3.5 Final and JPA 2.1, SqlServer 2008.

The saga of my current approach can be found here: Hibernate JPA, inheritance and Stored Procedure returning multiple result sets

but it apparently not the right approach. If anyone knows how to make this work, please let me know. Thanks!!

Upvotes: 3

Views: 11813

Answers (3)

Maurice
Maurice

Reputation: 7401

I have the same issue as you have and reported it here. https://dba.stackexchange.com/questions/265890/can-a-mysql-stored-procedure-return-multiple-resultsets-each-containing-differen?noredirect=1#comment523515_265890

It seems to be a design flaw and therefore I have also created a bug issue about the problem here https://hibernate.atlassian.net/browse/HHH-13983

Currently Hibernate does not seem to support retrieving multiple resultsets with different column sets at the same time. But there is a work around for this and it goes like this:

  1. In the stored procedure create a temporary table that consists of all unique columns that are present in every select query that you make and return in the stored procedure. So if in select query #1 you are retrieving the columns name, address,user_id and in the second select query you are retrieving product, color, dateCreated. Then your temporary table should have the columns name, address,user_id,product, color, dateCreated defined in that order.

  2. Select the data of name, address,user_id into the temporary table that you've created. Following that select * from temp_table. Now a resultset of 6 columns is returned as the first resultset. On the applicationside you can retrieve name, address and user_id from the ObjectField array elements 0, 1, and 2.

  3. Empty the temp_table with the command TRUNCATE TABLE temp_table.

  4. repeat step 2 only now you insert select the values for product, color, dateCreated into the table temp_table.

  5. Select the results from temp_table, you can get the data from Object[] elements 3 4 and 5. Lastly drop the temp_table to release the memory resources.

Upvotes: 0

kazi
kazi

Reputation: 11

when each result set returns a different column, you can create different DTOs to collect the results and make all DTOs to implement an interface or extend an abstract class (the abstract class should contain columns common to all result sets). Then return type should be the implemented interface/abstract class. This works in my experience.

Upvotes: 0

Neil Stockton
Neil Stockton

Reputation: 11531

Using the docs for the JPA implementation that I use, you can see the JPA standard way of retrieving multiple result sets on this page. Page down to "Generalised execution, for multiple result sets"

http://www.datanucleus.org/products/accessplatform_4_0/jpa/stored_procedures.html

Upvotes: 1

Related Questions