I have a self-referencing table, let's call it FOO, with foo_id as PK. There is also FOO_LOG, where foo_id is a foreign key. It has all the same columns as FOO, plus an unmapped column named "__$operation", where only the following values occur: 1 for delete, 2 for insert, 3 for before-update and 4 for after-update. FOO is mapped to eg.Foo. I'd like to be able to select eg.Foo objects from FOO_LOG as well, based on "as of date" parameter. Foo.hbm.xml and FooLog.hbm.xml both map to eg.Foo, but use different entity-name values to distinguish between them.
Here is what Foo.hbm.xml looks like:
Code:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated Nov 3, 2010 4:28:21 PM by Hibernate Tools 3.4.0.Beta1 -->
<hibernate-mapping>
<class name="eg.Foo" entity-name="Foo" table="Foo" schema="eg" catalog="TESTCAT">
<id name="fooId" type="int">
<column name="foo_id"/>
</id>
<timestamp column="update_dts" name="updateDts" />
<many-to-one name="parentFoo" entity-name="Foo" fetch="select">
<column name="parent_foo_id"/>
</many-to-one>
<property name="fooName" type="string">
<column name="foo_name" length="50" not-null="true" />
</property>
<set name="children" table="FOO" inverse="true" lazy="true" fetch="select">
<key>
<column name="parent_foo_id"/>
</key>
<one-to-many entity-name="Foo"/>
</set>
</class>
</hibernate-mapping>
Like I said, FooLog.hbm.xml is almost the same, but points to FOO_LOG, has a different entity-name both as class element attribute and as many-to-one and its inverse one-to-many attribute, and also specifies insert="false" update="false" on its many-to-one mapping. In addition, it has the following named SQL query:
Code:
<sql-query name="FooAsOfDts">
<return alias="foo" entity-name="FooLog" lock-mode="none"/>
SELECT f.foo_id AS {foo.fooId},
f.update_dts AS {foo.updateDts},
f.parent_foo_id AS {foo.parentFoo},
f.foo_name AS {foo.fooName},
FROM FOO_LOG f
WHERE f.__$operation in (2,4)
AND f.update_dts = (
SELECT MAX(f1.update_dts)
FROM FOO_LOG f1
WHERE f1.foo_id = f.foo_id
AND f1.__$operation in (2,4)
AND :asOfDate >= f1.update_dts)
AND f.foo_id NOT IN (
SELECT DISTINCT f2.foo_id
FROM FOO_LOG f2
WHERE f2.foo_id = f.foo_id
AND f2.__$operation = 1
AND f2.update_dts > f.update_dts)
ORDER BY f.foo_name
</sql-query>
I should end up with just one record for each Foo loaded from FOO_LOG, inserted or updated most recently before a specified date, and not subsequently deleted prior to that date. When I execute this query (with CURRENT_TIMESTAMP instead of the placeholder), I get the expected number of results. However, if Hibernate executes it, I get a lot more results - not just most recent update, but prior updates as well (so, there are multiple instances of Foo objects with the same foo_id). Any ideas?
Code:
List<Foo> fooList = session.getNamedQuery("FooAsOfDts").setDate("asOfDate", new Date()).list();
Login
FAQ
Search
