JPA条件API最后按NULL顺序排序 [英] JPA criteria API order by NULL last
问题描述
我使用JPA条件API从日期库中获取记录。
我有实体记录,字段 dateTime ,可以为null。我会编码:
I use JPA criteria API to fetch records from the datebase. I have entity Record with field dateTime which can be null. I would code:
public List<Record> find(RecordFilter recordFilter, int page, int pageSize) {
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<Record> criteriaQuery = criteriaBuilder.createQuery(Record.class);
Root<Record> recordRoot = criteriaQuery.from(Record.class);
/*
* JOINS. Left Joins are used for optional fields, or fields inside of the optional fields.
*/
Join<Record, Agency> recordAgencyJoin = recordRoot.join(RecordTable.FIELD_AGENCY);
//Some other joins
//This is where I had the problem.
applyOrderBy(criteriaQuery, criteriaBuilder, recordRoot);
/*
* Specify which columns to select and their order.
* criteriaQuery.multiselect(....);
*/
applyMultiSelect(recordRoot, recordAgencyJoin, /*other joins*/ criteriaQuery);
/*
* criteriaQuery.where(somePredicate);
*/
applyFilter(recordFilter, criteriaQuery, criteriaBuilder,
recordRoot, recordAgencyJoin /*, other joins*/);
TypedQuery<Record> query = entityManager.<Record>createQuery(criteriaQuery);
RepositoryUtils.applyPagination(query, page, pageSize);
return query.getResultList();
}
private void applyOrderBy(CriteriaBuilder criteriaBuilder, Root<Record> recordRoot, CriteriaQuery<Record> criteriaQuery) {
//Other fields to be added to the final sort.
Order dateTimeDescOrder = criteriaBuilder.desc(recordRoot.get(RecordTable.FIELD_DATE_TIME));
criteriaQuery.orderBy(dateTimeDescOrder /*, other orders by*/);
}
事实证明,首先显示带有NULL dateTimeField的记录。
我使用Postrgres数据库。
我会回答这个问题因为我找到了解决方案。
这是一篇类似的帖子。
JPA Criteria Query API,最后按null排序
It turns out, records with NULL dateTimeField are shown first. I use Postrgres database. I will answer this question because I found a solution. Here is a similar post. JPA Criteria Query API and order by null last
推荐答案
这里我给出了这个任务的答案。
Here I put an answer to this task.
首先,Postgres默认首先返回空值。
First, Postgres by default returns nulls first.
SELECT * FROM record ORDER BY date_time_field DESC;
https ://stackoverflow.com/a/7621232/4587961
SELECT * FROM record ORDER BY date_time_field DESC NULLS LAST;
其次,我必须更改 applyOrderBy 方法
private void applyOrderBy(CriteriaBuilder criteriaBuilder, Root<Record> recordRoot, CriteriaQuery<Record> criteriaQuery) {
//In the class code
//private static final Date MIN_DATE = new Date(0L);
final Date MIN_DATE = new Date(0L);
//We treat records will NULL dateTimeField as if it was MIN_DATE.
Order dateTimeDescOrder = criteriaBuilder.desc(
//NULL values - last - WORKAROUND.
criteriaBuilder.coalesce(recordRoot.get(RecordTable.FIELD_DATE_TIME), MIN_DATE));
criteriaQuery.orderBy(dateTimeDescOrder);
}
注意,来自hibernate-jpa-2.1的 CriteriaBuilder 。
Note, CriteriaBuilder from hibernate-jpa-2.1.
/**
* Create an expression that returns null if all its arguments
* evaluate to null, and the value of the first non-null argument
* otherwise.
*
* @param x expression
* @param y value
*
* @return coalesce expression
*/
<Y> Expression<Y> coalesce(Expression<? extends Y> x, Y y);
这篇关于JPA条件API最后按NULL顺序排序的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!