Hibernate JPA, inheritance and Stored Procedure returning multiple result sets
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.
Answer
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.