I am trying to map an existing (immutable) schema to a set of Java objects using hibernate 2.1. I have a lookup table with a composite key which is used to lookup infrequently changing values such as marital status/gender/etc.

I am attempting to work around the composite key using a mapping like this:

<class name="Gender" table="DESCPF"
where="DESCPFX = 'IT' AND DESCCOY = '1' AND DESCTABL = 'T5670'">

<id name="rowid" column="ROWID" type="string">
<generator class="assigned"/>
</id>

<property name="code" column="DESCITEM"/>

<property name="description" column="LONGDESC"/>

</class>

The purpose of the where clause is to effectively make the "code" property a unique key (i.e., all possible values of genders are stored in IT/1/T5670). I had initially mapped DESCITEM as the primary key but Hibernate was not appending the where clause I specified, I figured this was fair enough as afterall I had specified it as the primary key. So I specified Oracle's ROWID as the unique key and mapped code as a regular property.

This works fine for queries such as "from Gender where code = 'M'" -- hibernate appends the required SQL as expected and only one row is returned.

However when I joining this object to my Person object as so:

<many-to-one name="gender"
class="com.ff.model.life400.common.Gender"
column="CLTSEX" outer-join="false"
property-ref="code"/>

The query does not include the where clause:

net.sf.hibernate.SQL - select gender0_.ROWID as ROWID0_, gender0_.DESCITEM as DESCITEM0_, gender0_.LONGDESC as LONGDESC0_ from DESCPF gender0_ where gender0_.DESCITEM=?
getPreparedStatement - preparing statement
net.sf.hibernate.type.StringType - binding 'M' to parameter: 1

This query returns more then one row and causes an exception.

Is this a bug? The documentation led me to believe the where clause I specified would be used.

Thanks

Niall

i know that for collection (one to many, many to many) the clause where is ignored since collection have their own where attribute in the mapping file, but for many-to-one, that is a good question... ;)

According to the DTD it does not.

i know that you cannot define a where clause for a many-to-one but i wanted to know if a many to one respects the where attribute defined for the class.

Excuse my english...

This is very correct and desirable functionality. We do NOT want to replace valid FK references with nulls! Ughh!

i was thinking about fixing "parameters", these kind of parameter that never change or that change once a year...

but you're right, the impacts could be dangerous

Gavin

Can you explain what you mean? I don't see how the FK can be null in this case. I am expecting Hibernate to append the class where clause when it is building the FK query, changing it to:

select gender0_.ROWID as ROWID0_, gender0_.DESCITEM as DESCITEM0_, gender0_.LONGDESC as LONGDESC0_ from DESCPF gender0_ where gender0_.DESCITEM=? and DESCPFX = 'IT' AND DESCCOY = '1' AND DESCTABL = 'T5670'

That is what I would have expected from the documentation.

In the interim I have worked around this by building views off DESCPF for each lookup, in the views the descitem column is a non-composite (atomic?) key.

Thanks

Niall