java.sql.SQLException: ResultSet may only be accessed in a forward direction

I have a program that works smoothly on MySQL database. Now for external requirements, I must switch to SQL Server. Thanks to Hibernate the switch was smooth, except for the error:

java.sql.SQLException: ResultSet may only be accessed in a forward direction.

I am getting this error when performing a custom pagination on the data that I get from the database. Below is a minimal example that tries to get and paginate the users stored in the database (the following example assumes that there are at least 3 rows in the table).

Custom pagination:

import org.hibernate.Query;
import org.hibernate.Session;

public class newMain1 {

    public static void main(String[] args) {
        getList(1, getSession());
        getList(2, getSession());
    }

    private static void getList(int page, Session s) {
        int rowsPerPage = 2;
        String hql = "FROM User u ";

            try {
                Query query = s.createQuery(hql);
                int start = (page - 1) * rowsPerPage;
                query.setFirstResult(start);
                query.setMaxResults(rowsPerPage);
                //line that throws exception when int page is 2
                query.list();
                //line that throws exception when int page is 2
            } finally {
                if (s.isOpen()) {
                    s.close();
                }
            }
    }

    private Session getSession(){
        //gets org.hibernate.Session
    }
}

User Java POJO:

public class User {    
    private Long id;
    private String username;
    private String password;
    private String email;

    public User() {
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }


    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }
}

User table for SQL Server:

CREATE TABLE [USER] (
  ID bigint NOT NULL,
  USERNAME varchar(150) NOT NULL UNIQUE,
  PASSWORD varchar(150) NOT NULL,
  EMAIL varchar(150) NOT NULL UNIQUE,
  PRIMARY KEY (ID)
);

Hibernate mapping of User POJO:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
    <class name="product.model.User" table="USER">
        <id column="ID" name="id" type="long">
            <generator class="increment"/>
        </id>
        <property column="EMAIL" name="email" type="string"/>
        <property column="USERNAME" name="username" type="string"/>
        <property column="PASSWORD" name="password" type="string"/>
    </class>
</hibernate-mapping>

Hibernate cfg xml:

<session-factory>
    <property name="hibernate.dialect">org.hibernate.dialect.SQLServerDialect</property>
    <property name="hibernate.connection.driver_class">net.sourceforge.jtds.jdbc.Driver</property>
    <property name="hibernate.connection.url">jdbc:jtds:sqlserver://127.0.0.1:1433;DatabaseName=myDB;prepareSQL=3;sendStringParametersAsUnicode=false;</property>
    <property name="hibernate.connection.username">sa</property>
    <property name="hibernate.connection.password">myPassword</property>
    <property name="hibernate.show_sql">true</property>
    <property name="hibernate.globally_quoted_identifiers">true</property>
    <mapping resource="product_mapping/user.hbm.xml"/>
  </session-factory>

I am using SQL Server 2012, JDK 1.8, Hibernate 4.0.1.Final, jtds driver 1.3.1

Please note that getList(1, getSession()) will not throw the exception, whereas getList(2, getSession()) will.

After hours of investigation i have find out that this problem was due to the hibernate dialect that i was using.

The correct dialect, considering the hibernate version i am required to use, is: org.hibernate.dialect.SQLServer2008Dialect

Source: https://forum.hibernate.org/viewtopic.php?p=2452163