The details of this are VERY sketchy... but in our Oracle9i, a particular very long query generated by hibernate will hang.
Calling setMaxResults on the criteria creates 1 parameter by tacking "where rownum <= ?" to the end of the query. After copying and isolating this query to run in a standalone test case of establishing a java.sql.Connection and forming a java.sql.PreparedStatement and setting the parameter to 30 via PreparedStatement.setInt(1, 30), we see that this query hangs on calling java.sql.PreapredStatement.executeQuery() [Hanging lasts 7 - 10 minutes, and eventually returns with correct data]
Furthermore, removing the parameter and replacing the ? with 30 directly in the string causes the query to correctly execute instantly.
Removing the "where rownum <= 30" from our generated query and trying again, but calling java.sql.PreparedStatement.setMaxRows(30) correctly returns instantly.
Given this observation, it seems strange that org.hibernate.loader.Loader would have the following block:
Code:
if ( useLimit ) {
sql = dialect.getLimitString(
sql.trim(), //use of trim() here is ugly?
useOffset ? getFirstRow(selection) : 0,
getMaxOrLimit(selection, dialect)
);
}
Why not call setMaxRows on the prepared statement?
As a temporary workaround to this odd problem (which we THINK is the ojdbc driver ultimately failing but who knows), I've set my hibernate.cfg.xml Dialect to a custom implementation. This just extends the Oracle9iDialect class and overrides the getLimitString method to replace "where rownum =< ?" with "where rownum < ?+1" -- which works for some god awful unknown reason. A cleaner workaround without modifying+recompiling hibernate's core doesn't seem to be obvious. This is a pretty terrible hack.
Maybe someone has had a similar issue or otherwise has some thoughts about this
hibernate.cfg.xml change
Code:
<property name="dialect">bpf.database.BPFDialect</property>
bpf.database.BPFDialect implementation
Code:
package bpf.database;
import org.hibernate.dialect.*;
public class BPFDialect extends Oracle9iDialect{
public String getLimitString(String sql, boolean hasOffset) {
sql = sql.trim();
boolean isForUpdate = false;
if ( sql.toLowerCase().endsWith(" for update") ) {
sql = sql.substring( 0, sql.length()-11 );
isForUpdate = true;
}
StringBuffer pagingSelect = new StringBuffer( sql.length()+100 );
if (hasOffset) {
pagingSelect.append("select * from ( select row_.*, rownum rownum_ from ( ");
}
else {
pagingSelect.append("select * from ( ");
}
pagingSelect.append(sql);
if (hasOffset) {
pagingSelect.append(" ) row_ where rownum < ?+1) where rownum_ > ?");
}
else {
pagingSelect.append(" ) where rownum < ?+1");
}
if ( isForUpdate ) {
pagingSelect.append( " for update" );
}
return pagingSelect.toString();
}
}
Login
FAQ
Search