Hello, I have a case where using .class in a HQL query leads to incorrect SQL. I could be using the .class in an incorrect way, but paragraph 10.7 of the manual is not entirely clear if the .class is only for <any> mappings or is always available.
The joined-subclasses are the "PilootImage", "InternalImage" and "ExternalImage" classes.
Mapping (w. XDoclet 1.2):
Code:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping>
<class
name="nl.sogeti.piloot.server.model.PilootImage"
table="image"
dynamic-update="false"
dynamic-insert="false"
>
<id
name="persistentId"
column="id"
type="java.lang.Long"
>
<generator class="assigned">
</generator>
</id>
<version
name="hibernateVersion"
type="java.lang.Integer"
column="hibernateVersion"
/>
<property
name="name"
type="java.lang.String"
update="true"
insert="true"
column="name"
/>
<property
name="version"
type="long"
update="true"
insert="true"
column="version"
/>
<property
name="lastversion"
type="long"
update="true"
insert="true"
column="lastversion"
/>
<property
name="deleted"
type="yes_no"
update="true"
insert="true"
column="deleted"
/>
<!--
To add non XDoclet property mappings, create a file named
hibernate-properties-PilootImage.xml
containing the additional properties and place it in your merge dir.
-->
<joined-subclass
name="nl.sogeti.piloot.server.model.ExternalImage"
table="externalimage"
dynamic-update="false"
dynamic-insert="false"
>
<key
column="id"
/>
<property
name="conceptId"
type="int"
update="true"
insert="true"
column="conceptid"
/>
<property
name="databaseId"
type="int"
update="true"
insert="true"
column="databaseid"
/>
<property
name="synonymNumber"
type="int"
update="true"
insert="true"
column="synonymnumber"
/>
</joined-subclass>
<joined-subclass
name="nl.sogeti.piloot.server.model.InternalImage"
table="internalimage"
dynamic-update="false"
dynamic-insert="false"
>
<key
column="id"
/>
<property
name="type"
type="java.lang.String"
update="true"
insert="true"
column="internal_type"
/>
<property
name="imageBytes"
type="binary"
update="true"
insert="true"
column="internal_binary"
/>
</joined-subclass>
</class>
</hibernate-mapping>
My code:
the commented-out code is the version that will fail. The way it is now it works correctly, but I need to select either the Internal- or ExternalImages. HibernateUtil is a wrapper around a Hibernate Session.
Code:
private List findImagesOfSubscription(HibernateUtil persistency, long id, String className) throws HibernateException
{
Set result = new HashSet();
String messageImagesQuery =
"select new nl.sogeti.piloot.server.framework.VersionedId(pim.id, pim.version, pim.lastversion, pim.deleted) "
+ "from Subscription as sub "
+ "join elements(sub.books) as bk "
+ "join elements(bk.messages) as msg "
+ "join elements(msg.messageItems) as itm "
+ "join itm.pilootImage as pim "
+ "where (sub.id = ?) ";
// + "and (pim.class = ?)";
List messageImages = persistency.find(messageImagesQuery, new Long(id), Hibernate.LONG);
// List messageImages = persistency.find(messageImagesQuery, new Long(id), Hibernate.LONG, className, Hibernate.STRING);
result.addAll(messageImages);
String pictureBookImagesQuery =
"select new nl.sogeti.piloot.server.framework.VersionedId(pim.id, pim.version, pim.lastversion, pim.deleted) "
+ "from Subscription as sub "
+ "join elements(sub.books) as bk "
+ "join elements(bk.pictureBooks) as pb "
+ "join pb.pilootImage as pim "
+ "where (sub.id = ?) ";
// + "and (pim.class = ?)";
List picturebookImages = persistency.find(pictureBookImagesQuery, new Long(id), Hibernate.LONG);
// List picturebookImages = persistency.find(pictureBookImagesQuery, new Long(id), Hibernate.LONG, className, Hibernate.STRING);
result.addAll(picturebookImages);
String pictureBookItemsImagesQuery =
"select new nl.sogeti.piloot.server.framework.VersionedId(pim.id, pim.version, pim.lastversion, pim.deleted) "
+ "from Subscription as sub "
+ "join elements(sub.books) as bk "
+ "join elements(bk.pictureBooks) as pb "
+ "join elements(pb.items) as pbi "
+ "join pbi.pilootImage as pim "
+ "where (sub.id = ?) ";
// + "and (pim.class = ?)";
List picturebookItemImages = persistency.find(pictureBookItemsImagesQuery, new Long(id), Hibernate.LONG);
// List picturebookItemImages = persistency.find(pictureBookItemsImagesQuery, new Long(id), Hibernate.LONG, className, Hibernate.STRING);
result.addAll(picturebookItemImages);
String userPortraitQuery =
"select new nl.sogeti.piloot.server.framework.VersionedId(pim.id, pim.version, pim.lastversion, pim.deleted) "
+ "from Subscription as sub "
+ "join elements(sub.users) as usr "
+ "join usr.portrait as pim "
+ "where (sub.id = ?) ";
// + "and (pim.class = ?)";
List userPortraitImages = persistency.find(userPortraitQuery, new Long(id), Hibernate.LONG);
// List userPortraitImages = persistency.find(userPortraitQuery, new Long(id), Hibernate.LONG, className, Hibernate.STRING);
result.addAll(userPortraitImages);
return new ArrayList(result);
}
Now the not working (commented out) version yields this SQL. I formatted it for clarity. I just show the first query, the others are similar.
Code:
select
pilootimag4_.id as x0_0_,
pilootimag4_.version as x1_0_,
pilootimag4_.lastversion as x2_0_,
pilootimag4_.deleted as x3_0_
from
subscription subscripti0_
inner join book books1_ on
subscripti0_.id=books1_.fk_subscription
inner join message messages2_ on
books1_.id=messages2_.fk_book
inner join messageitem messageite3_ on
messages2_.id=messageite3_.fk_message
inner join image pilootimag4_ on
messageite3_.fk_image=pilootimag4_.id
where
((subscripti0_.id=? ))
and (
(case
when pilootimag4__1_.id is not null then 1
when pilootimag4__2_.id is not null then 2
when pilootimag4_.id is not null then 0
end=? ))
...and yields the error
Code:
2004-01-23 11:16:17,368 WARN [net.sf.hibernate.util.JDBCExceptionReporter] SQL Error: 1109, SQLState: S1000
2004-01-23 11:16:17,368 ERROR [net.sf.hibernate.util.JDBCExceptionReporter] General error, message from server: "Unknown table 'pilootimag4__1_' in where clause"
2004-01-23 11:16:17,368 DEBUG [net.sf.hibernate.impl.BatcherImpl] done closing: 0 open PreparedStatements, 0 open ResultSets
2004-01-23 11:16:17,368 DEBUG [net.sf.hibernate.impl.BatcherImpl] closing statement
2004-01-23 11:16:17,368 DEBUG [net.sf.hibernate.util.JDBCExceptionReporter] SQL Exception
java.sql.SQLException: General error, message from server: "Unknown table 'pilootimag4__1_' in where clause"
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1651)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:889)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:956)
at com.mysql.jdbc.Connection.execSQL(Connection.java:1874)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1538)
at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:302)
at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:83)
at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:794)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:188)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:132)
at net.sf.hibernate.loader.Loader.doList(Loader.java:949)
at net.sf.hibernate.loader.Loader.list(Loader.java:940)
at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:833)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1475)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1454)
at nl.sogeti.piloot.server.model.persistency.HibernateUtil.find(HibernateUtil.java:130)
and for completeness, the working version, without any mention of .class. Again formatted for clarity.
Code:
select
messages2_.id as x0_0_,
messages2_.version as x1_0_,
messages2_.lastversion as x2_0_,
messages2_.deleted as x3_0_
from
subscription subscripti0_
inner join book books1_ on
subscripti0_.id=books1_.fk_subscription
inner join message messages2_ on
books1_.id=messages2_.fk_book
where
(subscripti0_.id=? )
So what is the matter here? A bug, or wrong usage of .class?
Login
FAQ
Search
