Hi,
We have a query that is performed regularily in our application. The first few times things go fine, but then it starts to return only a single row. From the log I can see that 'limit ?' is added to the generated SQL. I've tried different variants of the query, and also invoking it in different ways (directly via Spring's HibernateTemplate, Query created from Session and SQLQuery created from Session), always with same result. The only workaround I've found so far is to obtain a Connection from the Session and performing a raw SQL query.
Have anyone seen this before or have some good advice? I can provide more info if needed, but making an isolated demo of the problem will take some time.
Regards,
Nils
Hibernate version: 3.1.3 and 3.2
Environment:
Tomcat 5.5.17
Spring 2.0
MySQL 5.0.18
MySQL Connector/J 5.0.3
Code:
Code:
String sql = "select mtbr.index, mtpr.productNumber " +
"from com.oppie.oms.model.MinibarTemplateBundleRef mtbr, " +
"com.oppie.oms.model.MinibarTemplateProductRef mtpr, " +
"com.oppie.oms.model.BundleProductRef bpr " +
"where mtbr.bundle = bpr.bundle " +
"and mtpr.product = bpr.product " +
"and mtpr.minibarTemplate = mtbr.minibarTemplate " +
"and mtpr.minibarTemplate = ? " +
"order by mtbr.index, bpr.index";
List bundles = getHibernateTemplate().find(sql, template);
The generated SQL (show_sql=true):Correct SQL:
Code:
select
minibartem0_.bundle_index as col_0_0_,
minibartem1_.product_number as col_1_0_
from
minibar_template_bundle_xr minibartem0_,
minibar_template_product_xr minibartem1_,
bundle_product_xr bundleprod2_
where
minibartem0_.bundle_id=bundleprod2_.bundle_id
and minibartem1_.product_id=bundleprod2_.product_id
and minibartem1_.minibar_template_id=minibartem0_.minibar_template_id
and minibartem1_.minibar_template_id=?
order by
minibartem0_.bundle_index,
bundleprod2_.product_index
Incorrect SQL:
Code:
select
minibartem0_.bundle_index as col_0_0_,
minibartem1_.product_number as col_1_0_
from
minibar_template_bundle_xr minibartem0_,
minibar_template_product_xr minibartem1_,
bundle_product_xr bundleprod2_
where
minibartem0_.bundle_id=bundleprod2_.bundle_id
and minibartem1_.product_id=bundleprod2_.product_id
and minibartem1_.minibar_template_id=minibartem0_.minibar_template_id
and minibartem1_.minibar_template_id=?
order by
minibartem0_.bundle_index,
bundleprod2_.product_index limit ?
Login
FAQ
Search