For some reason, i cannot get Hibernate to add the proper queried tables to the from clause while doing a Criteria query. The only particular thing is that the queried class (Recipient) has a composite key.

It's a very simple schema: I have recipients, that have a number of alerts attached to them. Also, they have a number of users attached to them too.

I changed my class mapping strategy so it uses an autonumeric unique key and it works that way.

Here is the detail of the problem.

Thanks in advance !!
Bye !!

Hibernate version:
3.05
Mapping documents:

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

<hibernate-mapping package="ar.com.gminds.argos.businesslogic.model">

<class name="Recipient">

<composite-id>
<key-many-to-one name="user" class="User" column="`user`" />
<key-many-to-one name="alert" class="Alert" />
</composite-id>

<property name="mailSent" type="boolean" />
</class>

</hibernate-mapping>

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

<hibernate-mapping package="ar.com.gminds.argos.businesslogic.model" default-cascade="none">

<class name="Alert">

<id name="id">
<generator class="native"/>
</id>

<property name="title" type="string"/>
<property name="description" type="string"/>
<property name="scheduledDate" type="timestamp"/>
<property name="executedDate" type="timestamp"/>
<property name="deleted" type="boolean"/>

<many-to-one name="alertType" class="AlertType" />
<many-to-one name="alertState" class="AlertState"/>

<set name="recipients" cascade="all,delete-orphan" inverse="true">
<key column="alert" />
<one-to-many class="Recipient" />
</set>
</class>

</hibernate-mapping>

<?xml version="1.0" encoding='UTF-8'?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >

<hibernate-mapping package="ar.com.gminds.argos.businesslogic.model" default-cascade="none">

<class name="User" table="`user`">

<id name="id" column="id">
<generator class="native"/>
</id>

<property name="login" type="string"/>
<property name="password" type="string"/>
<property name="firstname" type="string"/>
<property name="lastname" type="string"/>
<property name="email" type="string"/>
<property name="deleted" column="deleted" type="boolean" />

<set name="userProfileSet" inverse="true" cascade="persist,all,delete">
<key column="id_user"/>
<one-to-many class="UserProfile"/>
</set>
</class>

</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():

Calendar calendar = Calendar.getInstance();
calendar.add(Calendar.DATE, 1);
Date date = calendar.getTime();

return (Recipient[]) Persistence.getSession().
createCriteria(Recipient.class).
add(Restrictions.eq("mailSent", Boolean.FALSE)).
createCriteria("alert").
add(Restrictions.lt("scheduledDate", date)).
createCriteria("alertState").
add(Restrictions.eq("pending", Boolean.TRUE)).
list().toArray(new Recipient[0]);

Full stack trace of any exception that occurs:


Name and version of the database you are using:

SQL Server 2000 sp3

The generated SQL (show_sql=true):

Hibernate: select this_.[user] as user1_0_, this_.alert as alert0_, this_.mailSent as mailSent12_0_ from Recipient this_ where this_.mailSent=? and alert1_.scheduledDate<? and alertstate2_.pending=?

Debug level Hibernate log excerpt:

java.lang.reflect.InvocationTargetException
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at ar.com.gminds.framework.service.proxys.PackageServiceProxy.invoke(PackageServiceProxy.java:46)
at $Proxy1.findPendingRecipientsForToday(Unknown Source)
at ar.com.gminds.argos.businesslogic.job.AlertJob.run(AlertJob.java:28)
at ar.com.gminds.argos.test.Test.main(Test.java:47)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:86)
Caused by: org.hibernate.exception.GenericJDBCException: could not execute query
at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:82)
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:70)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:1596)
at org.hibernate.loader.Loader.list(Loader.java:1577)
at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:111)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1322)
at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:300)
at org.hibernate.impl.CriteriaImpl$Subcriteria.list(CriteriaImpl.java:142)
at ar.com.gminds.argos.businesslogic.service.RecipientServiceImpl.findPendingRecipientsForToday(RecipientServiceImpl.java:36)
... 13 more
Caused by: java.sql.SQLException: The column prefix 'alert1_' does not match with a table name or alias name used in the query.
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:365)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2781)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2224)
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:628)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:418)
at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java:693)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:120)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1272)
at org.hibernate.loader.Loader.doQuery(Loader.java:391)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:218)
at org.hibernate.loader.Loader.doList(Loader.java:1593)
... 19 more
org.hibernate.exception.GenericJDBCException: could not execute query
at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:82)
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:70)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:1596)
at org.hibernate.loader.Loader.list(Loader.java:1577)
at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:111)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1322)
at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:300)
at org.hibernate.impl.CriteriaImpl$Subcriteria.list(CriteriaImpl.java:142)
at ar.com.gminds.argos.businesslogic.service.RecipientServiceImpl.findPendingRecipientsForToday(RecipientServiceImpl.java:36)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at ar.com.gminds.framework.service.proxys.PackageServiceProxy.invoke(PackageServiceProxy.java:46)
at $Proxy1.findPendingRecipientsForToday(Unknown Source)
at ar.com.gminds.argos.businesslogic.job.AlertJob.run(AlertJob.java:28)
at ar.com.gminds.argos.test.Test.main(Test.java:47)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:86)
Caused by: java.sql.SQLException: The column prefix 'alert1_' does not match with a table name or alias name used in the query.
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:365)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2781)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2224)
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:628)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:418)
at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java:693)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:120)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1272)
at org.hibernate.loader.Loader.doQuery(Loader.java:391)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:218)
at org.hibernate.loader.Loader.doList(Loader.java:1593)
... 19 more

I had a hard time with it since yesterday and I found out a way to make it generate the join between the tables. I am not sure if this is a bug, but I couldn't find anyone telling if this behaviour is correct (nor any workaround). I think it is worth to file a bug report and see what the core developers say.

However, I made it work. In my case, the code was pretty much like yours, except that I use annotations (but they don't make difference for this problem). I had a composite key defined in another class, and this key was composed of an integer and a foreign key:



I wanted to have a list where the marker.name was a given parameter, but Criteria didn't generate the join, as you said. So I declared, in my main class a property named marker, with the same configuration as the key in the above composite key, with one difference: not updateable and not insertable.



Note that the above annotations are set for field and the below for property, because I didn't have this field.

Now my criteria uses this marker property (from the main class) and the join is generated correctly and my query is performed. Any comments about this approach are very welcome.

A minor update concerning those who use annotations. Actually, the primary key class must only have regular properties (not associations). I found a comment from Emmanuel about it (http://forum.hibernate.org/viewtopic.php?t=945575):



Therefore, the code above, for the primary key, would be slightly differente:



instead of "marker" property in MarkerAlelePk.

Unfortunately, that seems (or seemed) to be the only way to do that. Do a search for key-many-to-one (or similar) and you should find some postings on the subject.

I'm working actually about this issue, the solution wrote by rcastro is right, but in my code was necessary to define also a property with the same name that the property defined in the pk, it's not enough to define only the getter and setters, the result code must be something like this:


Greetings.