I am using JPA + hibernate + Oracle:
- Hibernate: 3.2.6.ga
- Hibernate EntityManager: 3.3.2.GA
- Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit
- org.hibernate.dialect.Oracle10gDialect
- Oracle Driver 11.1.0.7.0
I am seeing some strange behavior when using JPA with Hibernate in combination with Oracle. When attempting to retrieve some results by criteria, I can only get back at most 10 results (when more than 10 qualify) no matter how high maxResults is set on the query. If maxResults is set to a number < 10, the correct number of results is returned.
I've seen a similar
post about this from March of 2008 which seems to imply that this may be a problem with the distinct keyword. In my case, however, the distinct keyword is not even used.
If offset is set to be greater than 1 on the query, you will also get back the correct number of rows.
And, if I remove the column from the ORDER BY clause that enforces unique ordering on my query, I also get back the correct number of rows.
Stepping through hibernate's code, if I inspect the PreparedStatement on line 673 of the org.hibernate.loader.Loader, I can see that the jdbc fetch size is set to 10. I'm not sure if this is defaulted by oracle or hibernate, however, I would expect that multiple round trips would be made to the database to fetch
all results matching the criteria, however, in my case, it appears that only one trip is being made to fetch the first 10 results matching the criteria. If I explicitly set the jdbc fetch size in my persistence.xml file to something higher (i.e. <property name="hibernate.jdbc.fetch_size" value="100"/>) I can get back at most the number I set the jdbc fetch size to.
Below is an example of my code that generates the query and gets the results:
Code:
Query query = em.createQuery("SELECT r FROM MyResult r WHERE r.attributes.person.id=:personIds ORDER BY r.attributes.resultDtTm.dtTm DESC, r.resultId DESC"); // removing r.resultId from the ORDER BY clause yields the correct number of results!
query.setMaxResults(11);
// Offset is 0 by default.
query.setParameter("personIds", "11111111-2222-3333-4444-555555555555")
List<MyResult> results = query.getResultList(); // Should return 11, but only returns 10!
The SQL generated by hibernate for this query is posted below. Copying and pasting this query into a oracle db viewer (such as sqldeveloper), substituting the '?' for the values passed above also yields the correct results.
Code:
select
*
from
( select
result0_.result_id as result1_0_,
result0_.comment_txt as comment5_0_,
result0_.create_dt_tm as create8_0_,
result0_.last_updt_dt_tm as last13_0_,
result0_.owner_id as owner17_0_,
result0_.owner_id_type as owner18_0_,
result0_.person_id as person19_0_,
result0_.person_id_type as person20_0_,
result0_.result_dt_tm as result21_0_,
result0_.result_text as result24_0_,
result0_.result_type as result25_0_,
result0_.version_create_id as version53_0_,
result0_.version_create_id_type as version54_0_,
result0_.result_vrsn as result55_0_
from
result result0_
where
result0_.person_id=?
order by
result0_.result_dt_tm DESC,
result0_.result_id DESC )
where
rownum <= ?
Another workaround I have found is that overriding the Oracle10gDialect's getLimitString as such also appears to resolve the issue:
Code:
public class Oracle10gDialectMaxResultsWorkaround extends Oracle10gDialect
{
@Override
public String getLimitString(String sql, boolean hasOffset)
{
StringBuffer pagingSelect = new StringBuffer( sql.length()+100 );
pagingSelect.append("select * from ( select row_.*, rownum rownum_ from ( ");
pagingSelect.append(sql);
if (hasOffset)
{
pagingSelect.append(" ) row_ ) where rownum_ <= ? and rownum_ > ?");
}
else
{
pagingSelect.append(" ) row_ ) where rownum_ <= ? and rownum_ > 0");
}
return pagingSelect.toString();
}
}
This workaround was taken from:
http://www.jroller.com/sjivan/entry/hibernate_and_oracle_pagination_gotchaThe workaround dialect from this post:
https://forum.hibernate.org/viewtopic.php?f=1&t=984652 does not work for me.
It could also be noted that this query works fine in MySQL.
If anyone could shed some light on this strange behavior, I would really appreciate it. Thanks!
Login
FAQ
Search