Unable to get multiple Table entities through Stored procedure using hibernate

Question

Here is my stored Procedure

Create PROCEDURE  [dbo].getUserAndEnum

AS

BEGIN

select * from user_master where id =1

select * from enum_master where id = 1

End

With hibernate i written

Session session = HibernateFactory.getSessionFactory().openSession();

Transaction tr = session.beginTransaction();

SQLQuery qr=session.createSQLQuery("getUserAndEnum");

List list = qr.list();

In list i am getting only the user object ..what about my enum_master row with id 1

P.S enum_master row with id 1 is there in DB

Thanks.

Answer 1

'Rules/limitations for using stored procedures' in hibernate documentation states that

"The procedure must return a result set. Note that since these servers can return multiple result sets and update counts, Hibernate will iterate the results and take the first result that is a result set as its return value. Everything else will be discarded." (reference : http://docs.jboss.org/hibernate/orm/3.3/reference/en/html/querysql.html#sp_query)

As stated, the second resultset in your case is being ignored.

You would need to use jdbc for getting both result sets. Either you can make separate classes for doing so, or alternatively, hibernate offers you methods for performing traditional jdbc operations via its session's 'doWork' and 'doReturningWork' methods...

A simple example could be:

List<Object> res = session.doReturningWork(new ReturningWork<List<Object> /*objectType returned*/>() {
            @Override
            /* or object type you need to return to process*/
            public List<Object> execute(Connection conn) throws SQLException 
            {
                CallableStatement cstmt = conn.prepareCall("CALL YOUR_PROCEDURE");
                //Result list that would return ALL rows of ALL result sets
                List<Object> result = new ArrayList<Object>();
                try
                {
                    cstmt.execute();                        

                    ResultSet rs = cstmt.getResultSet(); // First resultset
                    while (rs.next()) {//Read items/rows of first resultset
                        // .
                        // Process rows of first resultset

                        result.add(obj); // add items of resultset 1 to the returning list object
                    }

                    cstmt.getMoreResults(); // Moves to this Statement object's next result, returns true if it is a ResultSet object

                    rs = cstmt.getResultSet(); // Second resultset
                    while (rs.next()) {
                        // .
                        // Process rows of second resultset

                        result.add(obj); // add items of resultset 2 to the returning list object
                    }
                    rs.close();                           
                }
                finally
                {cstmt.close();}

                return result; // this should contain All rows or objects you need for further processing
            }
        });