3

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!!

Community
  • 1
  • 1
laloumen
  • 1,229
  • 2
  • 14
  • 15

3 Answers3

1

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

Neil Stockton
  • 11,383
  • 3
  • 34
  • 29
  • Thanks, but it doesn't give very much detail. What happens when the result sets contain different columns? What should the class hierarchy look like? How should the mappings be defined to map the first result set to one class and the second to a different class? I can't get Hibernate to work at all - I mean, it returns different result sets, just as your docs say, but the mappings are wrong and the values are wrong. I don't think people have much investigated this issue so far. – laloumen Jul 14 '14 at 13:52
  • different columns ? then you get different columns in the List. What mappings are you talking about? This is a native query, so there is no mapping to an entity. – Neil Stockton Jul 14 '14 at 14:18
  • I don't know about DataNucleus, but with Hibernate the first result set returns correctly, as a native query result but calling spq.hasMoreResults() throws: com.microsoft.sqlserver.jdbc.SQLServerException: "The column name xyz is not valid" even when there is no reference to a POJO for mapping. It is trying to reuse the same object. If I skip calling hasMoreResults() then the rows from the second result set are identical to those of the first one, even though they shouldn't be. And, if the Entity annotation is used a mapping will certainly be attempted to the annot. class. – laloumen Jul 14 '14 at 16:40
  • I tried DataNucleus (their nightly build, since I need it for some other feature) and all works fine for me for a stored proc that returns 2 result sets with differing numbers of columns - I get both back with the different columns. Suggest you raise a bug on Hibernate – Neil Stockton Jul 15 '14 at 07:42
  • The link doesn't work. – kaushalpranav Sep 30 '21 at 03:38
0

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.

kazi
  • 11
  • 2
0

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.

Maurice
  • 6,698
  • 9
  • 47
  • 104