Querying Extension Tables Using Hibernate
I have a query problem in Hibernate. I have a table "test" with existing data. I have a requirement where I cannot change the schema of the test table, so I created another table "testExtension" whose primary key is a foreign key to the primary key of Test. The data in testExtension is a subset of the data in the test. those. the "testExtension" will have fewer lines than the "test".
I have defined this relationship in the config file as follows:
<class name="Test" table="test">
<id name="testId" column="test_id">
<generator class="sequence">
<param name="sequence">test_id_seq</param>
</generator>
</id>
<property name="name"/>
<joined-subclass name="TestExtension" table="testExtension">
<key column="test_id"/>
<property name="summary" />
<property name="homepage"/>
</joined-subclass>
With this setup, I can create a TestExtension object in my Java program, populate it with data, "save" it with Hibernate, and commit a transaction. And it saves data correctly in both Test and TestExtension.
My problem occurs when I try to query data from these tables. Right now, if I ask for a specific test_id using TestExtension.class for a QBE, it will only return a string if that ID exists in both Test and TestExtension. If I use Test.class for the QBE it will return a string, but I won't have access to any of the data stored in the TestExtension.
My question is, how can I query these tables so that the results are based on the "left outer join" of both Test and TestExtension? Any solution is appreciated whether it asks for an example, HQL or whatever (although not raw SQL is preferred).
Thanks!
a source to share
HQL is probably the easiest way to do this. The docs are here:
http://docs.jboss.org/hibernate/stable/core/reference/en/html/queryhql-joins.html
It sounds like what you might want to do is reassign your relationship so that Test and TestExtension use a one-to-one relationship instead of inheritance. Then you can query Test and TestExtension using left outer join one-to-one.
a source to share
If you are using HQL to write a query for the Test class, it should do what you want. My guess is that QBE is effectively adding your example entity class as one of the query parameters.
So how:
from Test t where t.property = :value
must return Test or TestExtension objects. Please note that (at least with the Hibernate versions I have used). In this case, Hibernate should immediately return the actual objects to you, not the proxy - remember that TestExtension objects can sometimes be returned as simple lazy-loaded test proxies.
a source to share