java - Java JPA OrderBy 字段和多列条件搜索不起作用
问题描述
我尝试从 mysql 表中获取具有某些条件的数据并使用 JPA 进行排序。我的代码在没有订单的情况下运行完美,如果我包含订单我得到零记录(它不是预期的)。
我从控制台获取生成的查询并在 mysql cmd 中运行。我得到了结果,但没有通过 JPA
请帮我
USER TABLE
--------------------------------------------------
NAME | AGE | ROLE | ROLE_CD | CITY_CODE
-------------------------------------------------
name1 | 24 | developer | SR_DEV | ch1 |
name2 | 25 | tester | SR_QAE | ch2 |
name3 | 26 | developer | DEV | ch1 |
name4 | 27 | tester | QAE | ch4 |
ADDRESS TABLE
--------------------------------
STATE | CITY | CITY_CODE
--------------------------------
state1 | city1 | ch1 |
state2 | city2 | ch2 |
state3 | city3 | ch3 |
state4 | city4 | ch4 |
public class User {
@Column(name = "NAME")
private String name;
@Column(name = "AGE")
private String age;
@Column(name = "ROLE")
private String role;
@Column(name = "ROLE_CD")
private String roleCd;
@Column(name = "CITY_CODE")
private String cityCode;
@OneToOne
@JoinColumn(name = "cityCode", referencedColumnName = "cityCode",
insertable = false, updatable = false,
foreignKey = @javax.persistence.ForeignKey(value = ConstraintMode.NO_CONSTRAINT))
private Address address;
}
public class Address {
@Column(name = "CITY_CODE")
private String cityCode;
@Column(name = "CITY")
private String city;
@Column(name = "STATE")
private String state;
}
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);
Join<User, Address> joinAddress = userData.join("address");
//Order By field ROLE_CODE
List<Expression<?>> roleList = new ArrayList<>();
roleList.add(userData.get("roleCd"));
roleList.add(builder.parameter(String.class, "param1"));
roleList.add(builder.parameter(String.class, "param2"));
OrderByField order = new OrderByField(builder, roleList);//created custom class for ordeBy field
//WHERE CLUASE city like city1 and role like developer
List<Predicate> predicates = new ArrayList<>();
predicates.add(builder.like(joinAddress.get("city"), "%" + "city1" + "%"));
predicates.add(builder.like(userData.get("role"), "%" + "developer" + "%"));
criteriaQuery.where(predicates.toArray(new Predicate[predicates.size()]));
criteriaQuery.orderBy(order);
TypedQuery<User> query = em.createQuery(criteriaQuery);
//ORDER BY FIELD PARAMS
query.setParameter("param1", "SR_DEV");
query.setParameter("param2", "DEV");
//THIS FOR LIMIT THE RECORD
if (pageNumber != null && pageSize != null) {
query.setFirstResult(pageNumber * pageSize);
query.setMaxResults(pageSize);
}
return query.getResultList();
解决方案
推荐阅读
- c++ - QT_VERSION 不正确?
- r - 如何将因子转换为数字
- java - 部署到不同机器时如何使用 selenium/standalone-chrome?
- android - PreferenceFragmentCompant 在 rootKey 上的 onCreatePreferences 中显示 null
- perl - Net::SSH::Perl:收到断开连接消息:用户名身份验证失败太多
- reactjs - 在redux中API调用成功后调用另一个动作?
- networking - NS2中的Linux TCP拥塞控制算法
- r - 在R中一次替换矩阵中的多个值
- apache - 将站点移动到子域,.htaccess 重定向 Apache
- css - 单击时在两个 ngClass 类之间切换,但仅适用于单击的元素