Hibernate Named Query Order By parameter
Can anyone point me to how we c开发者_如何学JAVAan 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
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
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;
}
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
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
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.
精彩评论