Hibernate JPA，继承和存储过程返回多个结果集 [英] Hibernate JPA, inheritance and Stored Procedure returning multiple result sets

问题描述

我尝试使用Hibernate 4.3.5.Final（JPA 2.1）从存储过程中使用多个结果集 - 并且我无法获取它工作。我使用的是Sql Server 2008.

存储的proc结果集具有不同的列，但有一些共同点，但不足以将它们组合到一个结果集中。 Java的通用性表现为继承层次结构。即使存储过程结果集中确实没有显式表，我仍然使用TABLE_PER_CLASS的InheritanceType策略。尽管如此，我仍然需要做一些事情来让Hibernate为一个结果集提供一个类X1的对象，为另一个提供X2。

我的简化Java层次结构如下所示：

  @Entity 
 @Inheritance（strategy = InheritanceType.TABLE_PER_CLASS）
 @DiscriminatorColumn（
 name =clazz_，
 discriminatorType = DiscriminatorType.INTEGER 
）
 @ DiscriminatorValue value =0）
 public class XBase {
 @Column（name =ProductTypeID）
 protected Integer productTypeId; 
} 
  

和

<$ p $ b $ @Entity
@DiscriminatorValue（value =1）
public class X1 extends XBase {
@Column（name =UUID）
保护字符串uuid;
}


和

<$ p $ b $ @Entity
@DiscriminatorValue（value =2）
public class X2 extends XBase {
@Column（name =geo_id）
私人整数geoId;
}


使用@NamedStoredProcedureQuery，

  @NamedStoredProcedureQuery（
 name =XInfoSProc，
 resultClasses = {
 com.xyz.search.jpa.XBase.class，
 com.xyz.search.jpa.X1.class，
 com.xyz.search.jpa.X2.class 
}，
 procedureName =spXInfo，
参数= {
 @StoredProcedureParameter（mode = ParameterMode.IN，name =XMatchID，type = String.class）
} 
）
  

我构建StoredProcedureQuery并执行它，

  / /为这个持久化单元创建一个EntityManagerFactory 
 EntityManagerFactory factory = Persistence.createEntityManagerFactory（XPU）; 
 EntityManager em = factory.createEntityManager（）; 
 StoredProcedureQuery spq = em.createNamedStoredProcedureQuery（XInfoSProc）; 
 spq.setParameter（XMatchID，10002916403）; 
尝试{
 spq.execute（）; 
} catch（Exception ex）{
 System.err.println（Exception：+ ex.getMessage（））; 
} 
  

Hibernate抛出一个WrongClassException异常，

异常：org.hibernate.WrongClassException：Object [id = 512565]不是指定的子类[com.xyz.search.jpa.X2]：加载的对象是错误的类com.xyz.search。 jpa.X1

查看从hibernate生成的DEBUG语句，似乎我的@DiscriminatorValue（）注释没有正确处理。尽管我为X1指定了@DiscriminatorValue（value =1），但hibernate固执地为X1生成了2（2作为X1的clazz_），这可能是问题的原因，或者不是，我不确定

有什么方法可以使用Hibernate / JPA和存储过程来返回多个结果集吗？

我是否做错了？

预先感谢！

（如果有人需要我的测试代码中的额外信息，

附录（已编辑）：

遵循zxcf的建议，我修改了@NamedStoredProcedureQuery ：

  @NamedStoredProcedureQuery（
 name =XInfoSProc，
 resultSetMappings = {
XInfoSProcMapping1，
XInfoSProcMapping2，
XInfoSProcMapping3，
XInfoSProcMapping7
}，
 procedureName =spXInfo，
 parameters = {
 @StoredProcedureParameter（mode = ParameterMode.IN，name =SearchID，type = String.class）
} 
）
  

并添加了一个SqlResultSetMapping，如下所示：

  @SqlResultSetMappings（
 value = {
 @SqlResultSetMapping（
 name =XInfoSProcMapping1，
 entities = {
 @EntityResult（entityClass = X1.class，
 discriminatorColumn =clazz_，
 fields = {
 @ FieldResult（name =id，column =XID），
 @FieldResult（name =typeId，column =XTypeID），
 @FieldResult（name =productTypeId XProductTypeID），
 @FieldResult（name =natsId，column =NatsId）
} 
）
} 
），
 @ SqlResultSetMapping（
 name =XInfoSProcMapping2，
 entities = {
 @EntityResult（entityClass = X2.class，
 discriminatorColumn =clazz_，
 fields = {
 @FieldResult（name =id，column =XID），
 @FieldResult（name =typeId，column =XTypeID），
 @FieldResult name =productTypeId，column =XProductTypeID），
 @FieldResult（name =phoneNumber，column =PhoneNumber）
} 
）
} 
 
 @SqlResultSetMapping（
 name =XInfoSProcMapping3，
 entities = {
 @EntityResult（entityClass = X3.class，
 discriminatorColumn =clazz_， 
 fields = {
 @FieldResult（name =id，column =XID），
 @FieldResult（name =typeId，column =XTypeID），
 @FieldResult（name =productTypeId，column =XProductTypeID）
} 
）
} 
），
 @SqlResultSetMapping（
 name = XInfoSProcMapping7，
 entitie s = {
 @EntityResult（entityClass = X7.class，
 discriminatorColumn =clazz_，
 fields = {
 @FieldResult（name =id，column =XID ），
 @FieldResult（name =typeId，column =XTypeID），
 @FieldResult（name =productTypeId，column =XProductTypeID），
 @FieldResult name =geoId，column =geo_id）
} 
）
} 
）
} 
）
  

通过此修改，我得到了一些非常奇怪的行为。依次使用List x = spq.getResultList（）处理每个结果集显示x实际上是一个Object []，其中结果集中的每一行都已映射到每个类 - 即结果集1的第1行，它具有映射到X1，X2，X3和X7。这并不是我所期望的 - 我认为resultSets将被逐一映射，即第一个resultSet映射到X1，第二个到X2等，但这不是发生的事情。

Update 7/10/2014 -

在XBase.java中，

<$ p $
name =XInfoSProcMapping，
entities = {
@EntityResult（entityClass = XBase.class，
discriminatorColumn =dc ，
fields = {
@FieldResult（name =id，column =XID），
@FieldResult（name =typeId，column =XTypeID），
@FieldResult（name =natsId，column =NatsId），
@FieldResult（name =xUUID）$ b @FieldResult（name =productTypeId，column =XProductTypeID ，column =XUUID），
@FieldResult（name =phoneNumber，column =PhoneNumber），
@FieldResult（name =xAddress1，column =XAddress1），
@FieldResult（name =couponURL，column =CouponURL ），
@FieldResult（name =geoId，column =geo_id），
}
）
}
）
@NamedStoredProcedureQuery（
name =XInfoSProc，
resultSetMappings = {
XInfoSProcMapping
}，
procedureName =spXInfo，
parameters = {
@StoredProcedureParameter（mode = ParameterMode.IN，name =XMatchID，type = String.class）
}
）
@Entity
@Inheritance（strategy = javax.persistence .InheritanceType.SINGLE_TABLE）
public abstract class XBase {
@Id protected Long id;

$ / code>

在X1.java中，

  @Entity 
 @DiscriminatorValue（value =1）
 public class X1 extends XBase {
 / * ... * / 
} 
  

在X2.java中，

  @Entity 
 @DiscriminatorValue（value =2）
 public class X2 extends XBase {
 / * ... * / 
 
 
 
 $ b第一个结果集（其中 '1 'as dc  ），但是在尝试处理第二个结果集时，它将 '2'作为dc  为所有行，我得到一个ClassCastException。 
 
 
 
 java.lang.ClassCastException：com.xyz.search.jpa.X1不能转换为com.xyz.search.jpa.X2 
 
 
 
我尝试将从第二个getResultList（）返回的对象设置为X2，但显然休眠JPA即使对于具有dc ='2'的行保湿X1s  - 显然没有注意到鉴别器列来确定要实例化的内容。
 
 
 
存储过程结果集1： 
 
 
  XID XTypeID XProductTypeID XUUID NatsID XPriority dc 
 512565 2 2001 AD6AB5A8-3A75-449D-8742-76C2425BA164 1809025090 10 1 
  
存储过程结果集2： 
 
 
  XID XTypeID名称PhoneNumber dc 
 512565 2 ABC DEF 8152597378 2 
  
上述sp结果具有代表性 - 为了清晰起见，我还清除了许多其他列。还有5个额外的结果集，每个结果集都有一组不同的列和一个不同的dc值：1,2,3,4,5,6,7 
 
 
 <一些（可能是最终的）想法：
 
 
 
我越深入研究这一点，Hibernate 4.3.5 Final的设计就没有充分处理多重结果集来自单个存储过程。通常，不能保证来自给定存储过程的两个结果集将具有任何共同点，甚至可能不是相同的主键。从存储过程生成多个结果集的决定可能是由效率驱动的 - 例如，在SQL方面可能需要相同的预处理步骤（例如临时表生成）来生成多个不同的结果集。然而，JPA中唯一用于每个SQL行实例化不同类的工具是一个鉴别器字段，鉴别器只能用于继承，这至少有一些共同点。如果没有公共标识符，主键，那么Java类层次结构就无法工作。
 
 
 
即使可以识别通用的@Id字段，来自不同结果的行即使该行的其余部分完全不同，那么对于Id字段具有相同值的集合将被水合到现有对象中。如果已经存在一个缓存中有Id的对象，Hibernate显然会忽略鉴别符字段。
 
 
 
即使是MappedSuperclass方法也需要一个公共的Id字段。此外，还无法为子类指定表（name =???），因为结果集不是可以进行引用的命名表。
将会工作。
  
 
 
我会将您的 @NamedStoredProcedureQuery 更改为包含 resultSetMappings  
  @NamedStoredProcedureQuery（
 name =XInfoSProc，
 resultSetMappings = {
XInfoSProcMapping
}，
 procedureName =spXInfo，
 parameters = {
 @StoredProcedureParameter（mode = ParameterMode.IN，name =XMatchID，type = String.class）
} 
 ）
  
并添加 SqlResultSetMapping 定义
  @SqlResultSetMapping（
 name =XInfoSProcMapping，
 entities = 
 @EntityResult（
实体Class = XBase.class，
 discriminatorColumn =clazz_，
 fields = {
 @FieldResult（name =productTypeId，column =ProductTypeID），
 @FieldResult name =uuid，column =UUID），
 @FieldResult（name =geoId，column =geo_id）
} 
）
）
  
正如您所看到的，我假设您的过程至少返回四列 clazz _ ， ProductTypeID ， UUID 和 geo_id  。
 
 
 更新 
 
 
 
我想你误会了我。我仍然不知道你的存储过程返回的是什么，但是在一行中返回多个实例的情况并不常见。
 
 
 
如果声明
  resultClasses = {
 com.xyz.search.jpa.XBase.class，
 com.xyz.search.jpa.X1。 class，
 com.xyz.search.jpa.X2.class 
} 
  
然后你对JPA说每个行包含三个类实例，并让它映射它自己。
 
 
 
如果声明
  resultSetMappings = {
XInfoSProcMapping1，
XInfoSProcMapping2，
XInfoSProcMapping3，
XInfoSProcMapping7
} 
  
然后你对JPA说每一个 row 至少包含四个由这些映射映射的事物。
 
 
在我看来，您应该声明单个resultSetMapping，让它命名为 XInfoSProcMapping 。因此， NamedStoredProcedureQuery 应该如下所示： 
 
 
  @NamedStoredProcedureQuery（
 name =XInfoSProc，
 resultSetMappings = {
XInfoSProcMapping
}，
 procedureName =spXInfo，
 parameters = {
 @ StoredProcedureParameter（mode = ParameterMode.IN，name =SearchID，type = String.class）
} 
）
  
和 SqlResultSetMapping 应如下所示： 
 
 
  @SqlResultSetMapping（
 name =XInfoSProcMapping1，
 entities = {
 @EntityResult（entityClass = XBase.class，
 discriminatorColumn =clazz_，
 fields = {
 @FieldResult（name =id，column =XID），
 @FieldResult（name =typeId，column =XTypeID），
 @FieldResult name =productTypeId，column =XProductTypeID），
 @FieldResult（name =natsId，column =NatsId），
 @FieldResult（name =phoneNumber，column =PhoneNumber），
 @FieldResult（name =geoId，column =geo_id）
 
} 
） 
 
 
 code 
 $ b $ p $重要的是 EntityResult->字段列表应适合存储过程查询返回的所有列。继承和具体对象的实现将由JPA provider完成。
 
 
 
希望它对你有所帮助。  
I am attempting to consume multiple result sets from a stored procedure using Hibernate 4.3.5.Final (JPA 2.1) -- and I have not been able to get it to work. I am using Sql Server 2008.

The stored proc result sets have different columns with some commonality but not enough to combine them into a single result set.  The commonality is expressed in Java with an inheritance hierarchy.  I've been using the InheritanceType strategy of TABLE_PER_CLASS even though there really aren't explicit tables in the stored procedure result sets.  Still, I need to do something to get Hibernate to hydrate an object of class X1 for one result set and X2 for the other.  

My simplified Java hierarchy is as follows:
@Entity
@Inheritance(strategy=InheritanceType.TABLE_PER_CLASS)
@DiscriminatorColumn(
   name="clazz_",
   discriminatorType=DiscriminatorType.INTEGER
)
@DiscriminatorValue(value="0")
public class XBase {
   @Column(name = "ProductTypeID")
   protected Integer productTypeId;
}
and
@Entity
@DiscriminatorValue(value="1")
public class X1 extends XBase {
   @Column(name = "UUID")
   protected String uuid;
}
and
@Entity
@DiscriminatorValue(value="2")
public class X2 extends XBase {
   @Column(name = "geo_id")
   private Integer geoId;
}
Using a @NamedStoredProcedureQuery, 
@NamedStoredProcedureQuery (
   name = "XInfoSProc",
   resultClasses = {
        com.xyz.search.jpa.XBase.class,
        com.xyz.search.jpa.X1.class,
        com.xyz.search.jpa.X2.class
  },
  procedureName = "spXInfo",
  parameters = { 
     @StoredProcedureParameter(mode = ParameterMode.IN, name = "XMatchID", type = String.class) 
  }
)
I construct the StoredProcedureQuery and execute it,
// Create an EntityManagerFactory for this Persistence Unit
EntityManagerFactory factory = Persistence.createEntityManagerFactory("XPU");
EntityManager em = factory.createEntityManager();
StoredProcedureQuery spq = em.createNamedStoredProcedureQuery("XInfoSProc");
spq.setParameter("XMatchID", "10002916403");
try {
   spq.execute();
} catch(Exception ex) {
   System.err.println("Exception: " + ex.getMessage());
}
Hibernate throws a WrongClassException exception,

Exception: org.hibernate.WrongClassException: Object [id=512565] was not of the specified subclass [com.xyz.search.jpa.X2] : loaded object was of wrong class class com.xyz.search.jpa.X1

Looking at the DEBUG statements generated from hibernate, it seems that my @DiscriminatorValue() annotations aren't being properly handled.  Even though I specified @DiscriminatorValue(value="1") for X1, hibernate obstinately is generating SQL with 2 for X1 (2 as clazz_ from X1)  This may be the cause of the issue, or maybe not, I'm not sure yet.

Is there any way to use Hibernate / JPA with stored procs returning multiple result sets?

What am I doing wrong?

Thanks in advance!

(If anyone needs additional information from my test code, plmk. :)

Addendum (edited) :

Following the advice of zxcf, I modified the @NamedStoredProcedureQuery to be:
@NamedStoredProcedureQuery (
   name = "XInfoSProc",
   resultSetMappings = {
      "XInfoSProcMapping1",
      "XInfoSProcMapping2",
      "XInfoSProcMapping3",
      "XInfoSProcMapping7"
   },
   procedureName = "spXInfo",
   parameters = { 
      @StoredProcedureParameter(mode = ParameterMode.IN, name = "SearchID", type = String.class) 
   }
)
and added a SqlResultSetMapping as follows:
@SqlResultSetMappings(
   value = {
      @SqlResultSetMapping (
         name="XInfoSProcMapping1",
         entities= {
            @EntityResult(entityClass=X1.class,
               discriminatorColumn="clazz_",
               fields={
                  @FieldResult(name="id", column="XID"),
                  @FieldResult(name="typeId", column="XTypeID"),
                  @FieldResult(name="productTypeId", column="XProductTypeID"),
                  @FieldResult(name="natsId", column="NatsId")
               }
            )
         }
      ),
      @SqlResultSetMapping (
         name="XInfoSProcMapping2",
         entities= {
            @EntityResult(entityClass=X2.class,
               discriminatorColumn="clazz_",
               fields={
                  @FieldResult(name="id", column="XID"),
                  @FieldResult(name="typeId", column="XTypeID"),
                  @FieldResult(name="productTypeId", column="XProductTypeID"),
                  @FieldResult(name="phoneNumber", column="PhoneNumber")
               }
            )
         }
      ),
      @SqlResultSetMapping (
         name="XInfoSProcMapping3",
         entities= {
            @EntityResult(entityClass=X3.class,
               discriminatorColumn="clazz_",
               fields={
                  @FieldResult(name="id", column="XID"),
                  @FieldResult(name="typeId", column="XTypeID"),
                  @FieldResult(name="productTypeId", column="XProductTypeID")
               }
            )
         }
      ),
      @SqlResultSetMapping (
         name="XInfoSProcMapping7",
         entities= {
            @EntityResult(entityClass=X7.class,
               discriminatorColumn="clazz_",
               fields={
                  @FieldResult(name="id", column="XID"),
                  @FieldResult(name="typeId", column="XTypeID"),
                  @FieldResult(name="productTypeId", column="XProductTypeID"),
                  @FieldResult(name="geoId", column="geo_id")
               }
            )
         }
      )
   }
)
With this modification I am getting some very strange behavior.  Processing each result set in turn using List x = spq.getResultList() shows that x is actually an Object[] where each row in the result set has been mapped to each class - that is, row 1 of result set 1, has a mapping to X1, X2, X3 and X7.  This is not all what I expected at all - I thought that resultSets would be mapped one by one, i.e. first resultSet mapped to X1, 2nd to X2, etc.  but that's not what is happening.

Update 7/10/2014 --

In XBase.java,
@SqlResultSetMapping (
   name="XInfoSProcMapping",
   entities= {
      @EntityResult(entityClass=XBase.class,
         discriminatorColumn="dc",
         fields={
            @FieldResult(name="id", column="XID"),
            @FieldResult(name="typeId", column="XTypeID"),
            @FieldResult(name="productTypeId", column="XProductTypeID"),
            @FieldResult(name="natsId", column="NatsId"),
            @FieldResult(name="xUUID", column="XUUID"),
            @FieldResult(name="phoneNumber", column="PhoneNumber"),
            @FieldResult(name="xAddress1", column="XAddress1"),
            @FieldResult(name="couponURL", column="CouponURL"),
            @FieldResult(name="geoId", column="geo_id"),
         }
      )
   }
)
@NamedStoredProcedureQuery (
   name = "XInfoSProc",
   resultSetMappings = {
      "XInfoSProcMapping"
   },
   procedureName = "spXInfo",
   parameters = { 
      @StoredProcedureParameter(mode = ParameterMode.IN, name = "XMatchID", type = String.class) 
   }
)
@Entity
@Inheritance(strategy=javax.persistence.InheritanceType.SINGLE_TABLE)
public abstract class XBase {
   @Id protected Long id;
}
In X1.java,
@Entity
@DiscriminatorValue(value="1")
public class X1 extends XBase {
    /* ... */
}
In X2.java,
@Entity
@DiscriminatorValue(value="2")
public class X2 extends XBase {
    /* ... */
}
The first result set (which has '1' as dc for all rows) is processed correctly but, when attempting to process the second result set, which has '2' as dc for all rows, I am getting a ClassCastException.  

java.lang.ClassCastException: com.xyz.search.jpa.X1 cannot be cast to com.xyz.search.jpa.X2

I am attempting to case the objects returned from the second getResultList() to X2 but apparently hibernate JPA is hydrating X1s even for rows which have dc='2' - apparently not paying any attention to the discriminator column to determine what to instantiate.

Stored procedure result set 1:
XID XTypeID XProductTypeID  XUUID   NatsID  XPriority   dc
512565  2   2001    AD6AB5A8-3A75-449D-8742-76C2425BA164    1809025090  10  1
Stored procedure result set 2:
XID XTypeID Name    PhoneNumber dc
512565  2   ABC DEF 8152597378  2
The above sp results are representative - there are lots of other columns that I stripped out for clarity.  There are also 5 additional result sets, each of which has a different set of columns and a distinct value for dc: 1,2,3,4,5,6,7

Some (possibly final) thoughts:

The more I dig into this, the more it becomes clear that Hibernate 4.3.5 Final isn't designed to adequately handle multiple result sets from a single stored procedure.  In general, there is no guarantee that two result sets from a given stored procedure will have anything in common, maybe not even the same primary key.  The decision to generate multiple result sets from a stored procedure might be driven by efficiency - for instance, the same pre-processing steps (e.g., temp table generation) might be needed on the SQL side to generate several disparate result sets.  

However, the only facility in JPA for the instantiation of different classes per SQL row is a discriminator field, and discriminators only work with inheritance, which presupposes at least some commonality.  If there is no common identifier, primary key, then a Java class hierarchy cannot work.

And, even if a common @Id field can be identified, rows from different result sets which have the same value for the Id field will be hydrated into the existing object, even though the remainder of the row is completely different.  Hibernate evidently ignores the discriminator field if there is already an object in cache with that Id.

Even the MappedSuperclass approach requires a common Id field.  And, in addition, there is no way to specify a Table(name="???") for subclasses because a result set is not a named table to which reference can be made.
解决方案
Result-classes would work if you returned different entities in single row.

I would change your @NamedStoredProcedureQuery to include resultSetMappings
@NamedStoredProcedureQuery (
   name = "XInfoSProc",
   resultSetMappings = {
        "XInfoSProcMapping"
  },
  procedureName = "spXInfo",
  parameters = { 
     @StoredProcedureParameter(mode = ParameterMode.IN, name = "XMatchID", type = String.class) 
  }
)
and add SqlResultSetMapping definition
@SqlResultSetMapping(
        name="XInfoSProcMapping",
        entities=
        @EntityResult(
                entityClass=XBase.class,
                discriminatorColumn="clazz_",
                fields={
                        @FieldResult(name="productTypeId", column="ProductTypeID"),
                        @FieldResult(name="uuid", column="UUID"),
                        @FieldResult(name="geoId", column="geo_id")
                }
        )
)
As you can see, I assume that your procedure returns at least four columns clazz_, ProductTypeID, UUID and geo_id.

Update

I think you misunderstood me. I still don't know what your stored procedure returns, but it's quite uncommon to return multiple instances in a single row.

If you declare
resultClasses = {
    com.xyz.search.jpa.XBase.class,
    com.xyz.search.jpa.X1.class,
    com.xyz.search.jpa.X2.class
}
then you are saying to JPA that every single row contains three classes instances and you let it map itself.

If you declare
resultSetMappings = {
  "XInfoSProcMapping1",
  "XInfoSProcMapping2",
  "XInfoSProcMapping3",
  "XInfoSProcMapping7"
}
Then you are saying to JPA that every single row contains at least four "things" which are mapped by these mappings.

In my opinion, you should declare single resultSetMapping, lets name it XInfoSProcMapping. So, the NamedStoredProcedureQuery should look like:
@NamedStoredProcedureQuery (
   name = "XInfoSProc",
   resultSetMappings = {
      "XInfoSProcMapping"
   },
   procedureName = "spXInfo",
   parameters = { 
      @StoredProcedureParameter(mode = ParameterMode.IN, name = "SearchID", type = String.class) 
   }
)
And SqlResultSetMapping should look as following:
@SqlResultSetMapping (
    name="XInfoSProcMapping1",
    entities= {
        @EntityResult(entityClass=XBase.class,
           discriminatorColumn="clazz_",
           fields={
              @FieldResult(name="id", column="XID"),
              @FieldResult(name="typeId", column="XTypeID"),
              @FieldResult(name="productTypeId", column="XProductTypeID"),
              @FieldResult(name="natsId", column="NatsId"),
              @FieldResult(name="phoneNumber", column="PhoneNumber"),
              @FieldResult(name="geoId", column="geo_id")

           }
        )
    }
)
The important thing is that EntityResult->fields list should fit to all columns returned by your stored procedure query. The inheritance and concrete object instantation will be done by JPA provider.

Hope it helps you. 

                        
这篇关于Hibernate JPA，继承和存储过程返回多个结果集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！