views:

273

answers:

2

I am having a querying issue in Hibernate. I have a table, 'test', with existing data. I have a requirement where I can not modify 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. Data in testExtension is a subset of the data in test. i.e. There will be less rows in 'testExtension' than in 'test'.

I have defined this relationship in a configuration 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 am able to create a TestExtension object in my Java program, populate it with data, 'save' it via Hibernate, and commit the transaction. And it correctly saves data in both Test and TestExtension.

My problem is occurring when I am trying to query data from these tables. Right now if I query for a particular test_id using the TestExtension.class to QBE, it will only return a row if that id exists in both Test and TestExtension. If I use the Test.class to QBE, it will return the row but I will not have access to any of the data stored in TestExtension.

My question is: how can I query these tables so that the results are based off a 'left outer join' of both Test and TestExtension? Any solution is appreciated, whether it's query by example, HQL, or something else (though preferably not raw SQL).

Thanks!

+1  A: 

HQL is probably the easiest way to do this. Docs are here:

http://docs.jboss.org/hibernate/stable/core/reference/en/html/queryhql-joins.html

Sounds like what you might want to do is remap your relationships so that Test and TestExtension use a one-to-one relationship instead of inheritance. Then you can query for Test and TestExtension using a left outer join across the one-to-one.

cliff.meyers
Yep, this works. I ended up doing a one-to-one mapping where TestExtension is just another object in the 'Test' POJO. Setting up the relationship this way causes QBE to behave the way I was expecting it to... When I do a query by example using the 'Test' class, it will query against all entries in the 'test' table and will populate the Test.TestExtension object if an associated entry exists in my extension table.
Todd
A: 

If you use HQL to write a query for the Test class, it should do what you want. I assume QBE is effectively adding the class of your example entity as one of the query parameters.

So sth like:

from Test t where t.property = :value

should return either Test or TestExtension entities. Note that (at least with the versions of Hibernate I've used). In this case, Hibernate should immediately give you back the actual entities rather than a proxy too--- be aware that TestExtension entities can sometimes be returned as plain Test lazy-loading proxies.

araqnid