sql - 按条件按自定义顺序进行条件查询
问题描述
我正在尝试按字段排序数据
查询:
select * from user
where (location = "%loc%" and role = "TECH")
order by field (department, "ELEC", "MECH");
我为 order by 字段创建了自定义 Order 类,如果我提供两个 where 条件 Order by not working 并返回 0 条记录,则它只能在单个 where 条件下正常工作。
如果我删除订单,它会返回预期结果。
USER TABLE
______________________________________________________
|NAME | AGE | ROLE | DEPARTMENT | LOCATION |
|________|______|____________|____________|___________|
|name1 | 24 | TECH | MECH | location1 |
|name2 | 25 | TECH | ELEC | location2 |
|name3 | 26 | TECH | COMP | location3 |
|name4 | 27 | NONTECH | CVL | location4 |
-------------------------------------------------------
*** EXPECTED RESULT ***
______________________________________________________
|NAME | AGE | ROLE | DEPARTMENT | LOCATION |
|________|______|____________|____________|___________|
|name2 | 25 | TECH | ELEC | location2 |
|name1 | 24 | TECH | MECH | location1 |
|name3 | 26 | TECH | COMP | location3 |
------------------------------------------------------
import java.util.List;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.Expression;
import javax.persistence.criteria.Order;
public class OrderByField implements Order {
private Expression<?> expression;
@Override
public Order reverse() {
return null;
}
@Override
public boolean isAscending() {
return true;
}
@Override
public Expression<?> getExpression() {
return expression;
}
public OrderByField(CriteriaBuilder cb, List<Expression<?>> list) {
this.expression = cb.function("field", String.class, list.toArray(new Expression<?>[list.size()]));
}
}
标准逻辑
CriteriaBuilder builder = em.getCriteriaBuilder();
CriteriaQuery<User> criteriaQuery = builder.createQuery(User.class);
Root<User> userData = criteriaQuery.from(User.class);
//Order By field DEPARTMENT
List<Expression<?>> departmentList = new ArrayList<>();
departmentList.add(userData.get("department"));
departmentList.add(builder.parameter(String.class, "param1"));
departmentList.add(builder.parameter(String.class, "param2"));
OrderByField order = new OrderByField(builder, departmentList); //custom class for ordeBy field
//WHERE CLUASE for location and role
List<Predicate> predicates = new ArrayList<>();
predicates.add(builder.like(userData.get("location"), "%" + "loc" + "%"));
predicates.add(builder.like(userData.get("role"), "%" + "TECH" + "%"));
criteriaQuery.where(predicates.toArray(new Predicate[predicates.size()]));
criteriaQuery.orderBy(order);
TypedQuery<User> query = em.createQuery(criteriaQuery);
//ORDER BY FIELD PARAMS
query.setParameter("param1", "ELEC");
query.setParameter("param2", "MECH");
return query.getResultList();
** 用户实体 **
public class User {
@Column(name = "NAME")
private String name;
@Column(name = "AGE")
private String age;
@Column(name = "ROLE")
private String role;
@Column(name = "DEPARTMENT")
private String department;
@Column(name = "LOCATION")
private String location;
}
解决方案
您可以通过以下方式解决它:
1)OrderByField
像这样声明类:
import javax.persistence.criteria.Expression;
import javax.persistence.criteria.Order;
public class OrderByField implements Order
{
private Expression<?> expression;
public OrderByField(Expression<?> expression)
{
this.expression = expression;
}
@Override
public Order reverse()
{
return null;
}
@Override
public boolean isAscending()
{
return true;
}
@Override
public Expression<?> getExpression()
{
return expression;
}
}
2)创建以下内容CriteriaQuery
:
CriteriaBuilder builder = session.getCriteriaBuilder();
CriteriaQuery<User> criteria = builder.createQuery(User.class);
Root<User> root = criteria.from(User.class);
criteria.select(root);
criteria.where(
builder.and(
builder.like(root.get("location"), "%loc%"),
builder.equal(root.get("role"), "TECH")
)
);
Expression<Integer> ord = builder.function(
"field",
Integer.class,
root.get("department"),
builder.literal("ELEC"),
builder.literal("MECH")
);
OrderByField order = new OrderByField(ord);
criteria.orderBy(order);
List<User> result = session.createQuery(criteria).getResultList();
您将获得以下信息result
:
User (3, COMP)
User (2, ELEC)
User (1, MECH)
推荐阅读
- java - package-info.java 如何在 maven 项目中的 main 和 test 之间共享?
- amazon-web-services - Can API Gateway use values from DynamoDB as input to request mapping template?
- flutter - 颤振:抑制异常“未布置渲染框”
- java - 尝试在 Java 中编写合并排序时出现 StackOverFlow 错误
- graphics - 将模型分成网格以进行深度排序渲染
- c - c - 反向字符串比较(回文)
- mysql - 在 MySQL 5 中将 Limit 与 group_contact 一起使用
- sql-server - 如何在asp net微服务中查询来自不同数据库服务器的数据?
- python - 如何在opencv python中通过udp连接?
- javascript - 通过Jquery将无序列表转换为下拉列表:当前页码不显示