Hibernate Named Query Order By parameter

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

Solution 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

Solution 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;
}

Solution 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