无法通过使用hibernate的存储过程获取多个表实体 [英] 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

  Session session = HibernateFactory.getSessionFactory（）。openSession（）; 
 
 Transaction tr = session.beginTransaction（）; 
 
 SQLQuery qr = session.createSQLQuery（getUserAndEnum）; 
 
 List list = qr.list（）; 
  

在列表中，我仅获取用户对象..关于我的enum_master行（id为1） p>

PS enum_master row with id 1 is there in DB

Thanks。

过程必须返回一个结果集，请注意，由于这些服务器可以返回多个结果集并更新计数，所以Hibernate会迭代结果并将第一个结果集作为结果集的返回值，其他所有内容都将被丢弃。
（参考： http：/ /docs.jboss.org/hibernate/orm/3.3/reference/en/html/querysql.html#sp_query ）

如上所述，第二个结果集在你的情况下被忽略。

您需要使用jdbc来获取苏斯集。您可以为此做单独的类，或者，hibernate为您提供通过会话的doWork和doReturningWork方法执行传统jdbc操作的方法...

一个简单的例子可以是：

  List< Object> res = session.doReturningWork（new ReturningWork< List< Object> / * objectType returned * />（）{
 @Override 
 / *或您需要返回到进程的对象类型* / 
 public List< Object> execute（Connection conn）throws SQLException 
 {
 CallableStatement cstmt = conn.prepareCall（CALL YOUR_PROCEDURE）; 
 //将返回ALL结果集
 List< Object> result = new ArrayList< Object>（）; 
 try 
 {
 cstmt.execute（）; 
 
 ResultSet rs = cstmt.getResultSet（）; //第一个结果集
 while（rs.next（））{//读取项目/第一个结果集的行
 //。
 //处理第一行结果集
 
 result.add（obj）; //添加项目resultset 1返回返回列表对象
} 
 
 cstmt.getMoreResults（）; //移动到此Statement对象的下一个结果，如果它是ResultSet对象，则返回true 
 
 rs = cstmt.getResultSet（）; //第二个结果集
 while（rs.next（））{
 //。 
 //处理第二个结果集的行
 
 result.add（obj）; //将结果集2的项添加到返回的列表对象
} 
 rs.close（）; 
} 
 finally 
 {cstmt.close（）;} 
 
返回结果; //这应该包含所有需要进一步处理的行或对象
} 
}）; 
  

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.

解决方案

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

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