Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp
Hibernate version: 3.0.5
Mapping documents: (truncated)
<class
name="com.fluid.engage.model.Category"
table="category">
<id
name="id"
column="id"
type="long"
unsaved-value="-1"
>
<generator class="native">
<!--
To add non XDoclet generator parameters, create a file named
hibernate-generator-params-Category.xml
containing the additional parameters and place it in your merge dir.
-->
</generator>
</id>
<many-to-one
name="productView"
class="com.fluid.engage.model.ProductView"
cascade="none"
outer-join="auto"
update="true"
insert="true"
column="productViewId"
not-null="true" />
<set
name="images"
table="image_category"
lazy="true"
inverse="true"
cascade="none"
sort="unsorted">
<key
column="categoryId">
</key>
<many-to-many
class="com.fluid.engage.model.Image"
column="imageId"
outer-join="auto"
/>
</set>
</class>
<class name="com.fluid.engage.model.Image" table="image" >
<id
name="id"
column="id"
type="long"
unsaved-value="-1"
>
<generator class="native">
</generator>
<many-to-one
name="productView"
class="com.fluid.engage.model.ProductView"
cascade="none"
outer-join="auto"
update="true"
insert="true"
column="productViewId"
not-null="true"
/>
<set
name="categories"
table="image_category"
lazy="true"
inverse="false"
cascade="none"
sort="unsorted"
>
<key
column="imageId"
>
</key>
<many-to-many
class="com.fluid.engage.model.Category"
column="categoryId"
outer-join="auto"
/>
</set>
</class>
Code between sessionFactory.openSession() and session.close():
tx = session.beginTransaction();
...
// Load the product view.
ProductView productView = (ProductView) session.get(ProductView.class,
new Long(productViewIds[i]));
// Figure out how many MAIN images *actually are* present.
// WORKING H2
// List imageCountResult = session.find(
// "select c.images.size from Category c where c.groupName='IMAGETYPE' and c.name='MAIN' and c.productView=?",
// productView, Hibernate.entity(ProductView.class));
// BROKEN H3
List imageCountResult = session.createQuery(
"select c.images.size from Category c where c.groupName='IMAGETYPE' and c.name='MAIN' and c.productView=?")
.setEntity(0, productView)
.list();
int actualImageCount = ((Integer) imageCountResult.get(0)).intValue();
log.debug("Actual image count for product view " +
productView.getId() + ": " + actualImageCount);
counts[i] = correctImageCount - actualImageCount;
}
...
tx.rollback()
Full stack trace of any exception that occurs: (truncated)
... 37 more^M
Caused by: java.sql.SQLException: Unknown column 'image2_.categoryId' in 'field list'^M
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1997)^M
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1167)^M
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1278)^M
at com.mysql.jdbc.Connection.execSQL(Connection.java:2251)^M
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1586)^M
at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:92)^M
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:120)^M
at org.hibernate.loader.Loader.getResultSet(Loader.java:1272)^M
at org.hibernate.loader.Loader.doQuery(Loader.java:391)^M
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:218)^M
at org.hibernate.loader.Loader.doList(Loader.java:1593)^M
... 43 more^M
Hibernate:
Name and version of the database you are using:4.1.7
The generated SQL (show_sql=true):
select (select count(image2_.categoryId) from image_category images1_, image image2_ where category0_.id=imag
es1_.categoryId and images1_.imageId=image2_.id) as col_0_0_ from category category0_, image_category images1_, image im
age2_ where category0_.id=images1_.categoryId and images1_.imageId=image2_.id and category0_.groupName='IMAGETYPE' and category0_.name='MAIN' and category0_.productViewId=?
---------
The SQL being generated by H3 is creating incorrect SQL. It should say select count(images1_.categoryId), I think. I fixed this and ran directly in mysql, and there were other problems. Is this maybe due to a limitation in related sub-queries with MySQL?
Also, I tried changing the HQL to "select count( c.images )" and this produced a strange SQL prefix with select count(.) instead of the usual select count(*). ?!?
This seems to be a bug.
Can anyone think of a different approach with HQL? I know that straight SQL will work.
Thanks!
Daniel