Firstly can I just say what an awesome tool Hibernate is - it's saved
us a huge amount of work!

I'm trying to map a legacy database using Hibernate. One of the
problems that I just can't seem to solve is that a class 'where'
clause seems to be ignored when resolving a one-to-one association to
that class via a property-ref. Given the format of the legacy DB we need the where clause so that only one item is pulled back from the table when using property-ref but it seems to be missing from the generated SQL unless we're directly issuing a query for the associated class. Is this behaviour by design? Is there
some way that I can force the where clause to be inserted when
resolving the association? If it's a bug or it makes sense for the
option to include the where clause to be added as a new feature then
I'm happy to do the work but can someone give me a clue where to start
please?

I've cut the problem down to a simplest test case that I can - the
details are included below.

Thanks in advance,
Rich.

Hibernate version: 2.1.6

Mapping documents:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN" "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd"><hibernate-mapping auto-import="false">

<class table="Core.dbo.Patient" name="Patient">
<id name="id">
<column name="oid" not-null="true" />
<generator class="increment" />
</id>

<one-to-one name="AcuityAssociation" class="PatientAcuity" property-ref="fromId" cascade="all" />
</class>

<class discriminator-value="6557" table="Core.dbo.Association" where="RelationObjectId = 6557" polymorphism="explicit" name="PatientAcuity">
<id name="id">
<column name="oid" not-null="true" />
<generator class="increment" />
</id>
<discriminator>
<column name="RelationObjectId" not-null="true" />
</discriminator>
<property name="fromId" insert="false" update="false">
<column name="FromKey" not-null="true" />
</property>
</class>

</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():
Transaction tx = session.beginTransaction();
Query query = session.createQuery("from Patient");
query.setMaxResults(1);
query.iterate().next();
tx.commit();

Full stack trace of any exception that occurs:
net.sf.hibernate.HibernateException: More than one row with the given identifier was found: 16, for class: PatientAcuity
at net.sf.hibernate.loader.EntityLoader.load(EntityLoader.java:71)
at net.sf.hibernate.loader.EntityLoader.loadByUniqueKey(EntityLoader.java:55)
at net.sf.hibernate.persister.AbstractEntityPersister.loadByUniqueKey(AbstractEntityPersister.java:1103)
at net.sf.hibernate.impl.SessionImpl.loadByUniqueKey(SessionImpl.java:3844)
at net.sf.hibernate.type.EntityType.resolveIdentifier(EntityType.java:207)
at net.sf.hibernate.impl.SessionImpl.initializeEntity(SessionImpl.java:2201)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:240)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:133)
at net.sf.hibernate.loader.Loader.loadEntity(Loader.java:836)
at net.sf.hibernate.loader.Loader.loadEntity(Loader.java:856)
at net.sf.hibernate.loader.EntityLoader.load(EntityLoader.java:59)
at net.sf.hibernate.loader.EntityLoader.load(EntityLoader.java:51)
at net.sf.hibernate.persister.EntityPersister.load(EntityPersister.java:419)
at net.sf.hibernate.impl.SessionImpl.doLoad(SessionImpl.java:2113)
at net.sf.hibernate.impl.SessionImpl.doLoadByClass(SessionImpl.java:1987)
at net.sf.hibernate.impl.SessionImpl.internalLoad(SessionImpl.java:1949)
at net.sf.hibernate.type.ManyToOneType.resolveIdentifier(ManyToOneType.java:69)
at net.sf.hibernate.type.EntityType.resolveIdentifier(EntityType.java:204)
at net.sf.hibernate.type.EntityType.nullSafeGet(EntityType.java:126)
at net.sf.hibernate.impl.IteratorImpl.postNext(IteratorImpl.java:85)
at net.sf.hibernate.impl.IteratorImpl.<init>(IteratorImpl.java:62)
at net.sf.hibernate.hql.QueryTranslator.iterate(QueryTranslator.java:861)
at net.sf.hibernate.impl.SessionImpl.iterate(SessionImpl.java:1608)
at net.sf.hibernate.impl.QueryImpl.iterate(QueryImpl.java:27)
at scripttest.ScriptTest.main(ScriptTest.java:26)

Name and version of the database you are using: SQL Server 2000

Debug level Hibernate log excerpt:

12:18:27,382 DEBUG SessionImpl:1587 - iterate: from Patient
12:18:27,382 DEBUG QueryParameters:109 - named parameters: {}
12:18:27,392 DEBUG QueryTranslator:147 - compiling query
12:18:27,412 DEBUG SessionImpl:2242 - flushing session
12:18:27,422 DEBUG SessionImpl:2435 - Flushing entities and processing referenced collections
12:18:27,422 DEBUG SessionImpl:2776 - Processing unreferenced collections
12:18:27,422 DEBUG SessionImpl:2790 - Scheduling collection removes/(re)creates/updates
12:18:27,422 DEBUG SessionImpl:2266 - Flushed: 0 insertions, 0 updates, 0 deletions to 0 objects
12:18:27,422 DEBUG SessionImpl:2271 - Flushed: 0 (re)creations, 0 updates, 0 removals to 0 collections
12:18:27,422 DEBUG SessionImpl:1814 - Dont need to execute flush
12:18:27,422 DEBUG QueryTranslator:199 - HQL: from Patient
12:18:27,422 DEBUG QueryTranslator:200 - SQL: select patient0_.oid as x0_0_ from Core.dbo.Patient patient0_
12:18:27,422 DEBUG BatcherImpl:196 - about to open: 0 open PreparedStatements, 0 open ResultSets
12:18:27,422 DEBUG SQL:237 - select patient0_.oid as x0_0_ from Core.dbo.Patient patient0_
12:18:27,422 DEBUG BatcherImpl:241 - preparing statement
12:18:27,552 DEBUG IteratorImpl:83 - retrieving next results
12:18:27,552 DEBUG SessionImpl:1982 - loading [Patient#16]
12:18:27,552 DEBUG SessionImpl:2079 - attempting to resolve [Patient#16]
12:18:27,562 DEBUG SessionImpl:2112 - object not resolved in any cache [Patient#16]
12:18:27,562 DEBUG EntityPersister:416 - Materializing entity: [Patient#16]
12:18:27,562 DEBUG BatcherImpl:196 - about to open: 1 open PreparedStatements, 1 open ResultSets
12:18:27,562 DEBUG SQL:237 - select patient0_.oid as oid0_ from Core.dbo.Patient patient0_ where patient0_.oid=?
12:18:27,562 DEBUG BatcherImpl:241 - preparing statement
12:18:27,592 DEBUG Loader:197 - processing result set
12:18:27,592 DEBUG Loader:405 - result row: 16
12:18:27,902 DEBUG Loader:536 - Initializing object from ResultSet: 16
12:18:27,902 DEBUG Loader:605 - Hydrating entity: Patient#16
12:18:27,902 DEBUG Loader:226 - done processing result set (1 rows)
12:18:27,902 DEBUG BatcherImpl:203 - done closing: 1 open PreparedStatements, 1 open ResultSets
12:18:27,902 DEBUG BatcherImpl:261 - closing statement
12:18:27,902 DEBUG Loader:239 - total objects hydrated: 1
12:18:27,902 DEBUG SessionImpl:2198 - resolving associations for [Patient#16]
12:18:27,902 DEBUG BatcherImpl:196 - about to open: 1 open PreparedStatements, 1 open ResultSets
12:18:27,902 DEBUG SQL:237 - select patientacu0_.oid as oid0_, patientacu0_.FromKey as FromKey0_ from Core.dbo.Association patientacu0_ where patientacu0_.FromKey=?
12:18:27,912 DEBUG BatcherImpl:241 - preparing statement
12:18:27,912 DEBUG Loader:197 - processing result set
12:18:27,922 DEBUG Loader:405 - result row: 3115
12:18:27,922 DEBUG Loader:536 - Initializing object from ResultSet: 3115
12:18:27,922 DEBUG Loader:605 - Hydrating entity: PatientAcuity#3115
12:18:27,922 DEBUG Loader:405 - result row: 4240184
12:18:27,922 DEBUG Loader:536 - Initializing object from ResultSet: 4240184
12:18:27,922 DEBUG Loader:605 - Hydrating entity: PatientAcuity#4240184
.....
12:18:30,656 DEBUG Loader:536 - Initializing object from ResultSet: 1903008813
12:18:30,656 DEBUG Loader:605 - Hydrating entity: PatientAcuity#1903008813
12:18:30,656 DEBUG Loader:226 - done processing result set (1201 rows)
12:18:30,656 DEBUG BatcherImpl:203 - done closing: 1 open PreparedStatements, 1 open ResultSets
12:18:30,656 DEBUG BatcherImpl:261 - closing statement
12:18:30,666 DEBUG Loader:239 - total objects hydrated: 1201
12:18:30,666 DEBUG SessionImpl:2198 - resolving associations for [PatientAcuity#3115]
.....

It is by design, actually. (and I think there would be big problems with any other design).


However, I ran into this once at a customer site, and there *is* a workaround.

Map the two columns that form a unique key to a single property using a component or usertype.

Then map the many-to-one like this:



Note the use of a "literal column name". enclosed in single quotes.

Make sure you are using a recent version of Hibernate.

Oh, you still got problems if the association is optional, my solution won't work. (The last time I saw this it was for a true many-to-one, not a one-to-one.)


You may be better off handling this as a query, than as a mapped association.

Thanks for the quick reply Gavin - I'll give that a go. I wanted to do something along those lines but I didn't know that you could enter literal column values - that's a useful trick.

I tried the new mapping that you suggested but it doesn't seem to like the literal column name - I've included the new SQL log and exception below - any ideas?

Thanks again,
Rich.

Hibernate: select patient0_.oid as x0_0_ from Core.dbo.Patient patient0_
Hibernate: select patient0_.oid as oid0_, patient0_.oid as oid0_, patient0_.'6557' as '6557'0_ from Core.dbo.Patient patient0_ where patient0_.oid=?
net.sf.hibernate.JDBCException: could not load: [Patient#16]
at net.sf.hibernate.persister.EntityPersister.load(EntityPersister.java:422)
at net.sf.hibernate.impl.SessionImpl.doLoad(SessionImpl.java:2113)
at net.sf.hibernate.impl.SessionImpl.doLoadByClass(SessionImpl.java:1987)
at net.sf.hibernate.impl.SessionImpl.internalLoad(SessionImpl.java:1949)
at net.sf.hibernate.type.ManyToOneType.resolveIdentifier(ManyToOneType.java:69)
at net.sf.hibernate.type.EntityType.resolveIdentifier(EntityType.java:204)
at net.sf.hibernate.type.EntityType.nullSafeGet(EntityType.java:126)
at net.sf.hibernate.impl.IteratorImpl.postNext(IteratorImpl.java:85)
at net.sf.hibernate.impl.IteratorImpl.<init>(IteratorImpl.java:62)
at net.sf.hibernate.hql.QueryTranslator.iterate(QueryTranslator.java:861)
at net.sf.hibernate.impl.SessionImpl.iterate(SessionImpl.java:1608)
at net.sf.hibernate.impl.QueryImpl.iterate(QueryImpl.java:27)
at cvscripttest.CVScriptTest.main(CVScriptTest.java:26)
Caused by: java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Line 1: Incorrect syntax near '6557'.
at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)
at com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processErrorToken(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReplyToken(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRPCRequest.processReplyToken(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSRequest.processReply(Unknown Source)
at com.microsoft.jdbc.sqlserver.tds.TDSCursorRequest.openCursor(Unknown Source)
at com.microsoft.jdbc.sqlserver.SQLServerImplStatement.execute(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.commonExecute(Unknown Source)
at com.microsoft.jdbc.base.BaseStatement.executeQueryInternal(Unknown Source)
at com.microsoft.jdbc.base.BasePreparedStatement.executeQuery(Unknown Source)
at com.opensourceconnections.msjdbcproxy.PreparedStatementProxy.executeQuery(PreparedStatementProxy.java:32)
at com.opensourceconnections.msjdbcproxy.PreparedStatementProxy.executeQuery(PreparedStatementProxy.java:32)
at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:87)
at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:800)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:189)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:133)
at net.sf.hibernate.loader.Loader.loadEntity(Loader.java:836)
at net.sf.hibernate.loader.Loader.loadEntity(Loader.java:856)
at net.sf.hibernate.loader.EntityLoader.load(EntityLoader.java:59)
at net.sf.hibernate.loader.EntityLoader.load(EntityLoader.java:51)
at net.sf.hibernate.persister.EntityPersister.load(EntityPersister.java:419)
... 12 more

you *sure* you got the current version?

You're right - I was on v2.1.4 but I thought I was on the latest version so I just put that rather than checking the file instead... Sorry.

Updating to v2.1.6 fixed it - thanks for all of your help!

One last question - I've implemented your suggestion using a component, but it seems that the literal has to map to a string on the component object - is there any way to use an int instead without implementing a custom type?

Thanks again,
Rich.

oh. thats interesting. um. its easily fixable.

All this is much better in HIbernate3, where you can do <column formula="..."/>

Now, you are sure that your many-to-one is never null, right?

I have some cases where that's true and some where it isn't - at least this
solves the problem for the cases where it is.

Forgive me for being thick, my DB knowledge isn't great by any means, but why can't this work when the many-to-one is null?

Just as an aside - I also tried to implement this using property-ref to a formula derived property that effectively added the where clause in, but it blew away with a null pointer exception when trying to build the session factory - having had a quick look at the code I presume that this is something that's not supported?

Thanks,
Rich.

ah. its to do with how hibernate handles resultsets. for a many-to-one, it wants the FK columns to be null if the association is null (this makes sense, if it is a relational schema with referential integrity!)