Hibernate Named Query Order By parameter

Question

Can anyone point me to how we can pass an order by clause as a named parameter to HQL?

Example which works:

select tb from TransportBooking as tb

and TIMESTAMP(tb.bookingDate, tb.bookingTime) >= current_timestamp() order by tb.bookingDate

Example which does not work:

select tb from TransportBooking as tb

and TIMESTAMP(tb.bookingDate, tb.bookingTime) >= current_timestamp() order by :order

Answer 1

Not supported, input parameters are only allowed in the WHERE and HAVING clauses and you cannot use parameters for the ORDER BY clause. Or if I rephrase, you can't use parameters for columns, only values. So, either:

• Have as much named queries as possible sort orders
• Concatenate the ordering string to the query string
• Use criteria queries

Answer 2

Try storing the named query without the order by clause, getting the query string and adding the order by clause pieces at run time.

Brian Fields explained it in his blog: http://brainfields.blogspot.com/2009/08/order-by-in-hibernate-named-queries.html

I have packaged the idea up for my project :

private static final Pattern badQueryPattern = Pattern.compile("[^\\p{ASCII}]*");

public static String getNamedQueryString(EntityManager em, String queryName) throws SQLException {
    Query tmpQuery = em.createNamedQuery(queryName);
    SQLQuery sqlQuery = tmpQuery.unwrap(SQLQuery.class);
    String queryString = sqlQuery.getQueryString();
    if (badQueryPattern.matcher(queryString).matches()) {
        throw new SQLException("Bad query string.");
    }

    return queryString;
}


public static Query getNamedQueryOrderedBy(EntityManager em, String queryName, Map<String, Boolean> columnNames) throws SQLException {

    StringBuilder sb = new StringBuilder();
    sb.append(ORDER_BY_CLAUSE_START);

    int limit = columnNames.size();
    int i = 0;
    for (String columnName: columnNames.keySet()) {
        sb.append(columnName);

        if (columnNames.get(columnName))
            sb.append(" ASC");
        else
            sb.append(" DESC");

        if (i != (limit - 1)) {
            sb.append(", \n");
        }
    }
    Query jpaQuery = em.createNativeQuery( getNamedQueryString(em, queryName)
                + sb.toString() 
                );

    return jpaQuery;
}

Answer 3

You might want to limit the sort field to the ones you have in your model. In my project I did this statically:

public static boolean isColumnName(Object domain, String columnName) {
    Field[] fields = domain.getClass().getDeclaredFields();
    for (Field field : fields) {
        Annotation[] annotations = field.getAnnotations();
        for (Annotation annotation : annotations) {
            if (annotation instanceof Column) {
                Column column = (Column) annotation;
                String foundColumnName;
                if (column.name() != null && !column.name().isEmpty()) {
                    foundColumnName = column.name();
                } else {
                    foundColumnName = field.getName();
                }
                if (columnName.toUpperCase().equals(
                    foundColumnName.toUpperCase())) {
                    return true;
                }
            }
        }
    }
    return false;
}   

Validating the field name on you DAL before concat the string into the jpql or hql you will avoid sql injection or further problems

Answer 4

This works for me in MySQL:

ORDER BY CASE :orderby WHEN 0 THEN field_x WHEN 1 THEN field_y ELSE field_z END ASC

Answer 5

It can be done like this

 order by CASE :orderBy
          WHEN 'pato_id' THEN PATO.id
          WHEN 'last_update_dt' THEN PATO.last_update_dt
     END desc

and you can pass "pato_id" or "last_update_dt" in the setString function like this

 q.setString("orderBy", "last_update_dt");
 or
 q.setString("orderBy", "pato_id");

This works with MS SQL Server, not sure about others.