java 如何根据特定顺序对休眠的结果进行排序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31281162/
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
How to order result of hibernate based on a specific order
提问by Hyman
I need to send a query to retrieve values that has a specific group of characters as following:
我需要发送一个查询来检索具有特定字符组的值，如下所示：
Lets say I am interested in 'XX' so it should search for any field that its value starts with 'XX' or has ' XX' (space XX). For example XXCDEF, PD XXRFand CMKJIEK XXare valid results.
假设我对“XX”感兴趣，因此它应该搜索其值以“XX”开头或具有“XX”（空格 XX）的任何字段。例如XXCDEF，PD XXRF和CMKJIEK XX是有效的结果。
I have following query that
returns the correct results but I need to sort themin a way that it first return those with XXat the beginning then other results. As following:
我有以下查询
返回正确的结果，但我需要以一种方式对它们进行排序，它首先返回那些XX在开头然后其他结果的结果。如下：
XXABCD
XXPLER
XXRFKF
AB XXAB
CD XXCD
ZZ XXOI
POLO XX
Code
代码
Criteria criteria = session.createCriteria(Name.class, "name")
.add(Restrictions.disjunction()
.add(Restrictions.ilike("name.fname", fname + "%"))
.add(Restrictions.ilike("name.fname", "%" + " " + fname + "%"))
)
.setProjection(Projections.property("name.fname").as("fname"));
List<String> names = (List<String>) criteria.list();
回答by Dragan Bozanovic
With JPQL (HQL):
使用 JPQL (HQL)：
select fname from Name
where upper(fname) like :fnameStart or upper(fname) like :fnameMiddle
order by (case when upper(fname) like :fnameStart then 1 else 2 end), fname
query.setParameter("fnameStart", "XX%");
query.setParameter("fnameMiddle", "% XX%");
With Criteria
有标准
With Criteriait's much trickier. Firstly, you have to resort to native SQL in the orderclause. Secondly, you have to bind the variable.
有了Criteria它就麻烦多了。首先，您必须在order子句中使用本机 SQL 。其次，你必须绑定变量。
public class FirstNameOrder extends Order {
public FirstNameOrder() {
super("", true);
}
@Override
public String toSqlString(Criteria criteria, CriteriaQuery criteriaQuery) throws HibernateException {
return "case when upper(FIRST_NAME) like ? then 1 else 2 end";
}
}
The case expression syntax and the upperfunction name should be changed in accordance with your database (and the column name if it's different, of course).
case 表达式语法和upper函数名称应该根据您的数据库进行更改（当然，如果列名称不同，则更改列名称）。
It is easy to add this to the Criteria, but there is no API to bind the parameter.
将它添加到 中很容易Criteria，但是没有 API 来绑定参数。
I tried to trick Hibernate by passing in an unused variable to the custom sql restriction so that it is effectively used for the variable in the order byclause:
我试图通过将一个未使用的变量传递给自定义 sql 限制来欺骗 Hibernate，以便它有效地用于order by子句中的变量：
Criteria criteria = session.createCriteria(Name.class, "name")
.add(Restrictions.disjunction()
.add(Restrictions.ilike("name.fname", fname + "%"))
.add(Restrictions.ilike("name.fname", "%" + " " + fname + "%")))
.setProjection(Projections.property("name.fname").as("fname"))
.add(Restrictions.sqlRestriction("1 = 1", fname + "%", StringType.INSTANCE))
.addOrder(new FirstNameOrder())
.addOrder(Order.asc("fname"));
and it works fine.
它工作正常。
Obviously, this solution is not recommended and I suggest using JPQL for this query.
显然，不推荐使用此解决方案，我建议对此查询使用 JPQL。
回答by Kenny Tai Huynh
Hibernate supports Order: http://docs.jboss.org/hibernate/orm/4.2/devguide/en-US/html/ch11.html#ql-orderingBecause of the special criteria, I think you have to custom the Order in Hibernate. This link may help: http://blog.tremend.ro/2008/06/10/how-to-order-by-a-custom-sql-formulaexpression-when-using-hibernate-criteria-api/
Hibernate 支持 Order：http: //docs.jboss.org/hibernate/orm/4.2/devguide/en-US/html/ch11.html#ql-ordering因为特殊的标准，我认为你必须在休眠。此链接可能有帮助：http: //blog.tremend.ro/2008/06/10/how-to-order-by-a-custom-sql-formulaexpression-when-using-hibernate-criteria-api/
回答by wero
Run two selects, one filtered for all the strings starting with 'XX', the second filtered for the others.
运行两次选择，一次过滤以 'XX' 开头的所有字符串，第二次过滤其他字符串。
回答by Gabi Carballo
You can use Predicates in criteria... something like this:
您可以在条件中使用谓词......像这样：
public List<Name> findByParameter(String key, String value, String orderKey)
CriteriaBuilder builder = this.entityManager.getCriteriaBuilder();
CriteriaQuery<Name> criteria = builder.createQuery(this.getClazz());
Root<Name> root = criteria.from(Name.getClass());
criteria.select(root);
List<Predicate> predicates = new ArrayList<Predicate>();
predicates.add(builder.equal(root.get(key), value));
criteria.where(predicates.toArray(new Predicate[predicates.size()]));
if (orderKey!=null && !orderKey.isEmpty()) {
criteria.orderBy(builder.asc(root.get(orderKey)));
}
result = this.entityManager.createQuery(criteria).getResultList();
return result;
}
回答by George
If you don't want to sort the result in memory,you can modify your criteria,I'll show you the SQL
如果你不想在内存中对结果进行排序，你可以修改你的标准，我会向你展示 SQL
select * from table where fname like 'XX%' order by fname
union all
select * from table where fname like '% XX%' order by fname
union all
select * from table where fname like '% XX' order by fname
the result will be your order and alphabetical and then apply your filter.
结果将是您的顺序和字母顺序，然后应用您的过滤器。
回答by Jegg
Stupid but it may work for your case.
愚蠢，但它可能适用于您的情况。
Since you got your correct result you can just reconstruct your results as follows:
既然你得到了正确的结果，你可以按如下方式重建你的结果：
- pick up all results starting with XX you put them into a list L1 and do the normal sort like Collections.sort(L1);
- for all other results, do the same like Collections.sort(L2)as list of L2
At last , put them together
List newList = new ArrayList(L1);
newList.addAll(L2);
- 选择以 XX 开头的所有结果，将它们放入列表 L1 中，然后进行像 Collections.sort(L1) 这样的正常排序；
- 对于所有其他结果，执行与 Collections.sort(L2)as list of L2 相同的操作
最后，把它们放在一起
List newList = new ArrayList(L1);
newList.addAll(L2);
Please note. Collections.sort are following the natural ordering of its elements.
请注意。Collections.sort 遵循其元素的自然顺序。
