Java Hibernate 命名查询 Order By 参数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4120388/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Hibernate Named Query Order By parameter
提问by Jet Abe
Can anyone point me to how we can pass an order by clause as a named parameter to HQL?
谁能指出我们如何将 order by 子句作为命名参数传递给 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
采纳答案by Pascal Thivent
Not supported, input parameters are only allowed in the WHEREand HAVINGclauses and you cannot use parameters for the ORDER BYclause. Or if I rephrase, you can't use parameters for columns, only values. So, either:
不支持，只能在WHEREandHAVING子句中输入参数，不能在ORDER BY子句中使用参数。或者，如果我改写，您不能对列使用参数，只能使用值。所以，要么：
- Have as much named queries as possible sort orders
- Concatenate the ordering string to the query string
- Use criteria queries
- 有尽可能多的命名查询排序顺序
- 将排序字符串连接到查询字符串
- 使用条件查询
回答by SaSConsul
Try storing the named query without the order by clause, getting the query string and adding the order by clause pieces at run time.
尝试在没有 order by 子句的情况下存储命名查询，获取查询字符串并在运行时添加 order by 子句片段。
Brian Fields explained it in his blog: http://brainfields.blogspot.com/2009/08/order-by-in-hibernate-named-queries.html
Brian Fields 在他的博客中解释了它：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;
}
回答by CaughtOnNet
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
在将字符串连接到 jpql 或 hql 之前验证 DAL 上的字段名称，您将避免 sql 注入或进一步的问题
回答by Dark Knight
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
您可以像这样在 setString 函数中传递“pato_id”或“last_update_dt”
q.setString("orderBy", "last_update_dt");
or
q.setString("orderBy", "pato_id");
This works with MS SQL Server, not sure about others.
这适用于 MS SQL Server，不确定其他人。
回答by Diego de la Riva
This works for me in MySQL:
这在 MySQL 中对我有用：
ORDER BY CASE :orderby WHEN 0 THEN field_x WHEN 1 THEN field_y ELSE field_z END ASC
