Exception using jTable and Hibernate on Paging & Sorting

Question

I'm getting the following exception:

org.hibernate.exception.SQLGrammarException: could not execute query

Caused by: org.postgresql.util.PSQLException: ERREUR: constante non entière dans ORDER BY
  Position: 222

i have a mehode in my GareDAOIpml to show all the rows from the database,my methode show() is:

public List<Gare> show(int startIndex, int pageSize, String jtSorting) {
    Session session = HibernateUtil.getSessionFactory().getCurrentSession();
    session.beginTransaction();
    List<Gare> gares = null;
    try {
        Query q = session.createQuery("from Gare ORDER BY "+jtSorting+" ");
        q.setFirstResult(startIndex);
        q.setMaxResults(pageSize);
        gares = (List<Gare>)q.list();

    } catch (HibernateException e) {
        e.printStackTrace();
        session.getTransaction().rollback();
    }
    session.getTransaction().commit();
    return gares;
}

---

<script type="text/javascript">
$(document).ready(function() {
    $('#StudentTableContainer').jtable({
        title : 'liste des gares',
        paging: true,
        pageSize: 10,
        sorting: false,

        actions : {
            listAction : 'listergare',
            updateAction: 'updategare',
            deleteAction: 'deletegare'
        },
        fields : {
            idgare : {
                title : 'identifiant de la gare',
                key : true,
                list : false,
                edit : false
            },
            usergare : {
                title : 'utilisateur de la gare',
                width : '25%',
                list : true,
                edit : true
            },
            ville : {
                title : 'ville ',
                width : '25%',
                type: 'date',
                list : true,
                edit : true
            }
        }
    });
    $('#StudentTableContainer').jtable('load');
});

Answer 1

Acording to David J. in [PostgreSQL] non-integer constant in ORDER BY: why exactly, and documentation? - Grokbase, the error occurs when:

A possible situation is that the user meant to use double-quotes to specify an identifier but instead used single quotes. Since a literal constant would not impact the sort order the planner should either discard it silently or throw an exception. The exception is preferred since the presence of a constant literal likely means whatever generated the query is broken and should be fixed.

Check the ORDER BY clause in your query.

---

In another hand, you can enable the logging of all the generated SQL statements by Hibernate to the console. Add the next property in the Hibernate configuration file hibernate.cfg.xml.

<prop key="hibernate.show_sql">true</prop>

See also How to print a query string with parameter values when using Hibernate.

---

UPDATE

You need an alias in you HQL string:

session.createQuery("SELECT g FROM Gare g" + 
        (jtSorting != null ? " ORDER BY g." + jtSorting : ""));

See more in Chapter 14. HQL: The Hibernate Query Language