SELECT * FROM orderTable order by
CASE priority when 'CRITICAL' THEN 1
when 'HIGH' then 2
when 'MEDIUM' then 3
when 'LOW' then 4
when 'NOT_ASSIGNED' then 5
end ASC ,
CreatedAt ASC;这是我的mysql查询及其工作原理。数据库列priority是字符串类型,且该字符串具有以下优先级
我想用jpa语言编写它,比如
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<T> cq = cb.createQuery(entityClass);
Root<T> root = cq.from(entityClass);
cb.selectCase().when(cb.equal(root.get("priority"), "CRITICAL"), 1)
.when(cb.equal(root.get("priority"), "HIGH"), 2)
.when(cb.equal(root.get("priority"), "MEDIUM"), 3)
.when(cb.equal(root.get("priority"), "LOW"), 4)
.when(cb.equal(root.get("priority"), "NOT_ASSIGNED"), 5).;
Order temp2 = cb.desc(root.get("priority"));
cq = cq.orderBy(temp2);这是行不通的,它返回的结果只按字符串优先级排序。select case未应用于标准构建查询。
发布于 2017-03-07 15:18:39
您可以使用行cb.desc(root.get("priority"));选择原始的"piority“。注意,在您的第一个示例中,您使用asc和第二个desc。我还添加了一个默认情况。
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<TableTest> cq = cb.createQuery(TableTest.class);
Root<TableTest> root = cq.from(TableTest.class);
Expression<Object> caseExpression = cb.selectCase()
.when(cb.equal(root.get("priority"), cb.literal("CRITICAL")), 1)
.when(cb.equal(root.get("priority"), cb.literal("HIGH")), 2)
.when(cb.equal(root.get("priority"), cb.literal("MEDIUM")), 3)
.when(cb.equal(root.get("priority"), cb.literal("LOW")), 4)
.when(cb.equal(root.get("priority"), cb.literal("NOT_ASSIGNED")), 5)
.otherwise(6);
Order temp2 = cb.desc(caseExpression);
cq = cq.orderBy(temp2);发布于 2021-07-09 02:06:50
我们也可以使用javax.persistence.criteria.CriteriaBuilder.SimpleCase
// ArrayList<T> order - Define Custom order ArrayList // {"HIGH", "MEDIUM", "LOW", "NOT_ASSIGNED"}
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<TableTest> query = cb.createQuery(TableTest.class);
Root<TableTest> root = query.from(TableTest.class);
query.select(root);
Path<String> priority = root.get("priority");
SimpleCase<String, Integer> orderCase = builder.selectCase(priority);
int i;
for (i = 0; i < order.size(); i++) {
orderCase = orderCase.when(order.get(i),i); // .when("HIGH", 0);
}
query.orderBy(cb.asc(orderCase.otherwise(i)));
entityManager.createQuery(query);我们可以使用CriteriaBuilder.SimpleCase,而不是使用cb.selectCase().when(#)。因为我们可以很容易地循环它。
https://stackoverflow.com/questions/42633141
复制相似问题