How to Hold multiple results arrived from a procedure in hibernate

Question

I am new to Hibernate
Here my problem is I know how to call stored procedures of mssql using hibernate, so here I used native queries and I am calling this

@NamedNativeQueries({
@NamedNativeQuery(
name = "callProc",
query = "EXEC SimpleProc :var,:var1,:var2,
resultClass = resultbean.class
)})

Here I have a procedure named let it be MultiResultProc like

Create Procedure MultiResultProc
.....
begin
select * from resulttable1;
select * from resulttable2;
select * from resulttable3;
end

So How to sepeciy these multple resultclass, when I google in some where I found that Hibernate will not handle Multiple Result Sets of Sybase and MSSQL db's. But I need that type of work... here my project have that type of multiple procedures and we can't design again.
**So Please Help me **

All tables have same fields? Do you have access to change anything in `StoredProcedure` — Vicky Thakor, Oct 30 '14 at 18:09
Thanks Vicky for your reply and All my three tables will have different columns, means table1 holds 5 columns table2 with 16 columns and table3 with some 9 columns... — RamBen, Oct 30 '14 at 18:15

score 0 · Accepted Answer · answered Oct 30 '14 at 18:18

0

What should be taken care while executing stored procedure in Hibernate?

If you have single Insert, Update or Delete operation in your stored procedure then you have to beginTransactin() and commit() it in order to take effect.
Hibernate will only select first result of stored procedure. e.g: If you write two select statement then first result will be mapped for hibernate in case of bean and without bean it'll only return first result as list of object.

Answer is no you can't get all three table records in hibernate.

answered Oct 30 '14 at 18:18

Vicky Thakor

3,847
7
42
67

Ok Vicky, I read same from https://docs.jboss.org/hibernate/orm/3.6/reference/en-US/html/querysql.html#d0e17495, and I am asking in which manner can I overcome that problem, means is any way to hold that multiple result sets – RamBen Oct 30 '14 at 18:23
If all tables have same fields then you can use `union` between select statement. I haven't tried but it may work – Vicky Thakor Oct 30 '14 at 18:35
Union is not possible ,when I do union I will get projection based result and it is unusable – RamBen Oct 30 '14 at 18:48

score 0 · Answer 2 · answered Oct 31 '14 at 05:24

I think you need to work with cursors when using stored procedure in hibernate. Here's what you should be doing.

Open cursor for select * from table1
select * from table2 join to existing cursor.
Similarly select from table n and join with existing result set cursor.
Map the returned cursor from hibernate to appropriate class which would contain all fields returned from all tables.
Spilt the result as per your need from the class.

This would save a lot of time and labor. For precise example visit this link.

How to Hold multiple results arrived from a procedure in hibernate

2 Answers2