I have legacy data that uses the not uncommon practice of marking a 'deleted' field in each row as 'Y' if the row is deleted, 'N' if not. Rows are never actually deleted. This is causing me some trouble, as I can't figure out how to specify such criteria in the mapping. I know that I can do a Query or use a Criteria, but then I'd have to build the object map myself; the goal is to have Hibernate load the related data while considering criteria other than just the FK relationship.

Here's a specific sample - a unidirectional many-to-many relationship built on a join table. I have 'projects' which contain multiple 'grades', tied to the 'code' field:

If I weren't paying attention to the deleted field, then this would (and does) work:


<set name="grades" table="project_grade">
<key column="project_key"/>
<many-to-many column="code" unique="true"
class="Grade"/>
</set>

Now, to make this respect the deleted field when making the relationship, I expected that this would work:

<set name="grades" table="project_grade">
<key column="project_key"/>
<many-to-many column="code" unique="true"
class="Grade">
<formula>
case when deleted='Y' then null else code end
</formula>
</many-to-many>
</set>

However I get a ClassCast Exception when running this. I'm guessing that it's trying to cast the 'code' field to a Grade, but maybe I'm off. One possibility is that, for some reason, this isn't a true FK relationship in that it's not enforced by the database, it's simply treated as if it were a FK in practice (the database lacks referential integrity and no, I didn't design it, and no, I don't have the option of changing it).

Any thoughts on A) is this possible and B) is this the right way to do it, and if so, then C) what am I doing wrong in the situation above? Thanks.

Use a HB3 filter or a collection where=".." mapping.

Gavin -

Thank you very much for your reply. By HB3 filter, you mean do something like this?

<filter-def name="isDeleted">
<filter-param name="deleted" type="string"/>
</filter-def>

<class name="Project" ...>

<set name="grades" table="project_grade">
<key column="project_key"/>
<many-to-many column="code" unique="true"
class="Grade"/>
</set>

<filter name="isDeleted"
condition="deleted=:deleted"/>
</filter>

---

Session session = ...;
session.enabledFilter("isDeleted").setParameter("deleted", "N");
List results = session.createQuery("from Project as p").list();

As far as a "collection where='...' mapping", do you mean in HQL/Criteria? Or is there some way to specify that in the mapping? Thanks.