无法使用休眠通过存储过程获取多个表实体 [英] Unable to get multiple Table entities through Stored procedure using hibernate

问题描述

这是我的存储过程

Create PROCEDURE  [dbo].getUserAndEnum

AS

BEGIN

select * from user_master where id =1

select * from enum_master where id = 1

End

我用hibernate写的

With hibernate i written

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

Transaction tr = session.beginTransaction();

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

List list = qr.list();

在列表中我只得到用户对象..id 为 1 的 enum_master 行怎么样

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

P.S enum_master id 为 1 的行在数据库中

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

谢谢.

推荐答案

hibernate 文档中的使用存储过程的规则/限制"指出

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

"过程必须返回一个结果集.请注意，由于这些服务器可以返回多个结果集和更新计数，Hibernate 将迭代结果并将作为结果集的第一个结果作为其返回值.一切否则将被丢弃."(参考: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.

您需要使用 jdbc 来获取两个结果集.您可以为此创建单独的类，或者，hibernate 通过其会话的doWork"和doReturningWork"方法为您提供执行传统 jdbc 操作的方法...

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
            }
        });

这篇关于无法使用休眠通过存储过程获取多个表实体的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！