Hi,

I need to use an Oracle index hint (can't avoid it) and so therefore need to use Native SQL rather than HQL. However, Hibernate does not seem to be able to figure out which subclass to instantiate based on the results.

Hibernate version: 3.2-rc5

What I have is abase HBM file 'Event', which 'XEvent' and 'YEvent' extend using a table per subclass hierarchy (<joined-subclass>).

Here is a dumbed down version of the mapping files. Please note that these persisted entities don't have POJOs, they're returned as Maps by Hibernate ('Dynamic Models', see http://www.hibernate.org/hib_docs/refer ... amicmodels but I don't think this really plays a factor to this problem).

Mapping documents:

Event:
<hibernate-mapping>
<class entity-name="Event" table="EVENT">
<id name="id" type="java.lang.String" access="field">
<column name="EVENT_ID" length="36"/>
<generator class="assigned"/>
</id>

<version column="VERSION" name="version" type="java.lang.Long" access="field" unsaved-value="null"/>

<property name="eventType" type="java.lang.String">
<column name="EVENTTYPE" length="60" not-null="true"/>
</property>
.... SNIP ...
</class>
</hibernate-mapping>

XEvent:
<hibernate-mapping>
<joined-subclass entity-name="XEvent" table="XEVENT" extends="Event">
<key column="EVENT_ID"/>
</joined-subclass>
</hibernate-mapping>

YEvent:
<hibernate-mapping>
<joined-subclass entity-name="YEvent" table="YEVENT" extends="Event">
<key column="EVENT_ID"/>
<property access="field" name="someOtherColumnProperty" type="java.lang.String" not-null="false">
<column name="SOME_OTHER_COLUMN"/>
</property>
</joined-subclass>
</hibernate-mapping>


So, my native SQL query should be something like something like:



And I do this to my SQLQuery: addEntity("Event"), which, according the the Native SQL chapter, is all that is required (declare the "root" entity). There's very little on how inheritance should be handled.

What happens when hibernate executes that? Well, it executes the query, and gets a result set, then tries to retrieve a "clazz_" column from this result set, which doesn't exist, and so an exception is thrown. Why does hibernate expect this to be in the result set when it knows it's using custom SQL?

I've looked into this clazz_ column and found that hibernate uses it as an inheritance discriminator so it knows what subclass to instantiate (i.e., XEvent or Yevent?).

If I was to do the following, it works fine:



As you can see, clazz_ is 0,1 or 2 based on the the entity type. I figured this out from looking at the SQL generated when using normal HQL. Why can't I just use this? Because the 0, 1 and 2 keys refer to Event, XEvent and YEvent, respectively, in some map, and there is no guarantee that some other time 1 will refer to XEvent and 2 refer to Yevent (could easily be the other way around).

Any advice on this issue? Is this a limitation or am I going about it the wrong way?

Thanks in advance,
Cormac

I don't see what's wrong with the 'case' statement. The numeric values, as you said, are arbitrary, and if you use native SQL, it is up to you to map these values to correct subclasses. Build the map based on the numbers you decide for each subclass and that's it, no?

"Build the map based on the numbers you decide for each subclass and that's it, no?" - What do you mean? I'm not building any map ;)

I don't build the map which stores the discriminators, hibernate does, and it depends on the order it reads in the HBM files. If it reads XEvent first then 1 -> XEvent, etc. I can't figure this mapping out from outside of hibernate.

It's strange that it seems there's no mention of handling this stuff in the documents.

IF you are using native sql, don't it mean that you expect to process the sql result sets youself, i.e. decide which class to instantiate without hibernate's maps?

Nope :)

If that were the case it would be simple...I'm not trying to bypass Hibernate. I found a workaround to this problem, but the issue is still there. I think Hibernate should document and support Native SQL better, and support a way to add index hinting via HQL.

There was some talk of a Query.addSQLHint(), but no joy.

Cremond,

Can you paste your work-around? We are facing the similar error. In our case we just have single sub-class.

Thanks,
Brahma.

Unfortunately my workaround will not help you...We found that in the end all we needed was the Event map, not XEvent or Yevent, so we didn't care that Hibernate couldn't handle the inheritance correctly.

However, including the clazz_ parts in the SQL caused Hibernate to instantiate to the correct subclass.

For example, if 0 then XEvent, if 1 then YEvent. My problem was we couldn't guarantee that 0 will always means XEvent; maybe tomorrow it might mean YEvent and 1 might mean XEvent.


Then I found that this clazz_ number that it is directly related to the order the mapping files are read in, so you can now guarantee that 0 will always point to XEvent and 1 to YEvent, as long as you do not re-order your mapping files.


It's not nice though. If your problem is that you need to use custom SQL to inclusde an index hint, then the problem should be solved at a database level by collecting proper statistics, or creating a column histogram, etc.

Is this your problem?

I figured out a solution to this problem, based on information provided by user credmond in this conversation. Now, I assume that this issue is fixed in newer versions of Hibernate, but there may be some people out there using an older version, so I'm posting my solution in case anyone else ever runs into this problem, as I've run into it a number of times and had never solved it until now.

The problem: I have a class (Certification) and a joined-subclass (Certification_SC), and I want to write a native SQL query against the root class (Certification) without having to handle the instance of a Certification_SC. The simplest case for a Certification query: This query works with any Certification that does not have a joined-subclass component. But if I query and get a resultset that contains the version of Certification with a joined-subclass component, Hibernate throws an Oracle Exception (which in my opinion is a very bad move, because this error IS a Hibernate error) I wanted to write a query general enough so I didn't have to have a specific version for the joined-subclass Object. It turns out the solution is mind-numbingly simple, based on credmond's research above.
Hibernate appears to ignore the extra column when populating the root class objects, and the 0 indicates populating the root class RATHER than the full joined-subclass when getting a joined-subclass result. Now, I can run the above query, and when the resultset contains only Certification objects, I get Certification objects, but when the resultset contains Certification_SC objects, I only get the Certification component of it.

Hope this helps somebody somewhere...