[FIXED] Hibernate Criteria.setMaxResults() fails on Oracle 11g

Issue

When using hibernate to retrieve data from Oracle 11g DB using either org.hibernate.dialect.Oracle10gDialect or org.hibernate.dialect.OracleDialect I get the following:

org.hibernate.exception.SQLGrammarException: could not execute query
Caused by: java.sql.SQLSyntaxErrorException: ORA-00923: FROM keyword not found where expected

Looking in the log we can see the query:

select top ? this_.LI_ILN as LI1_8_0_, this_.COUNTRY_CODE ... 

Obviously, the DB doesn't recognize the keyword and this is where the problem lies, because in Oracle pagination can only be done by using ROWNUM, a thing which Hibernate should know.

The hibernate configuration looks as follows:

<hibernate-configuration>
<session-factory name="HibernateSessionFactory">
    <property name="hibernate.bytecode.use_reflection_optimizer">false</property>
    <property name="hibernate.connection.driver_class">oracle.jdbc.OracleDriver</property>
    <property name="hibernate.connection.password">...</property>
    <property name="hibernate.connection.url">...</property>
    <property name="hibernate.connection.username">...</property>
    <property name="hibernate.default_schema">...</property>
    <property name="hibernate.dialect">org.hibernate.dialect.OracleDialect</property>
    <property name="hibernate.search.autoregister_listeners">false</property>
    <property name="hibernate.show_sql">true</property>
    <property name="hibernate.hbm2ddl.auto">validate</property>
    <property name="hibernate.transaction.auto_close_session">false</property>

    <mapping resource="HB_Mappings/Supplier.hbm.xml" />
</session-factory>

The query is done like so:

Criteria crit = sessionFactory.getCurrentSession().createCriteria(Supplier.class);
crit.setFirstResult(50 * pageIndex);
crit.setMaxResults(50);

List<Supplier> list = crit.list();

Any help is appreciated.

---

Solved:

Forgot to mention that I am using spring in which the applicationContext.xml looks like:

<bean id="sessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
    <property name="dataSource" ref="dataSource" />
    <property name="configLocation">
        <value>classpath:HB_Mappings/hibernate.cfg.xml</value>
    </property>
    <property name="hibernateProperties">
        <value>hibernate.dialect=org.hibernate.dialect.HSQLDialect</value>
    </property>
</bean>

It overwrote the hibernate.cfg.xml's property...

Note to self: go easy on copy-paste

Solution

OracleDialect is deprecated, see here: http://docs.jboss.org/hibernate/core/4.1/javadocs/org/hibernate/dialect/OracleDialect.html

Use the Oracle10gDialect instead: http://docs.jboss.org/hibernate/core/4.1/javadocs/org/hibernate/dialect/Oracle10gDialect.html

Also Check this out, and make sure that you are using the newest Oracle JDBC driver.

https://web.archive.org/web/20130204044852/https://community.jboss.org/wiki/SupportedDatabases2

Answered By - Istvan Devai
Answer Checked By - Dawn Plyler (JavaFixing Volunteer)