JPA Criteria API + PostgreSQL 按分钟精度筛选日期

huangapple go评论41阅读模式
英文:

JPA Criteria API + PostgreSQL filter by date with minute precision

问题

由于已知的 Hibernate+Postgres bug,我决定使用 Criteria API,因为我的许多参数可能为 null(来自 REST 调用)。

由于 Criteria API 在每个数据库客户端上的行为不同,所以在涉及日期(yyyy-MM-dd HH:mm:ss 格式)时,我无法找到解决方案。对我有效的任何方法都不令人满意,因为我要么失去了精度,要么遇到了异常。具体来说:

这是我的实体中映射到 Postgres 数据库中的 timestamp 列类型的字段/列定义:

@Column(name = "last_modified_date",
        nullable = false,
        updatable = false,
        columnDefinition = "TIMESTAMP WITHOUT TIME ZONE")
@Temporal(TemporalType.DATE)
protected Date lastModifiedDate;

这是我在 JpaRepository 接口中使用 Criteria API 的地方:

public interface PageHistoryRepository extends JpaRepository<PageHistoryEntity, Long>, JpaSpecificationExecutor<PageHistoryEntity> {

    default Page<PageHistoryEntity> findPages(Date fromDate,
                                              Date toDate,
                                              Pageable pageable) {
        return findAll(search(fromDate, toDate), pageable);
    }

    static Specification<PageHistoryEntity> search(Date fromDate, Date toDate) {
        return (root, cq, cb) -> {
            // 确保我们从谓词开始
            Predicate predicate = cb.isTrue(cb.literal(true));

            // 这个有效,但没有时间精度
            if (fromDate != null) {
                Predicate _predicate = cb.greaterThanOrEqualTo(root.get(PageHistoryEntity_.lastModifiedDate.getName()).as(java.util.Date.class), fromDate);
                predicate = cb.and(predicate,_predicate);
            }

            // 这也有效,但再次失去了时间精度
            if (toDate != null) {
                Predicate _predicate = cb.lessThanOrEqualTo(root.get(PageHistoryEntity_.lastModifiedDate.getName()), toDate);
                predicate = cb.and(predicate,_predicate);
            }

            // 这不起作用
            if (toDate != null) {
                Predicate _predicate = cb.lessThanOrEqualTo(
                        cb.function("date_trunc", Calendar.class, cb.literal("minute"), root.get(PageHistoryEntity_.lastModifiedDate.getName()).as(Calendar.class)),
                        cb.function("date_trunc", Calendar.class, cb.literal("minute"), cb.literal(toDate)).as(Calendar.class));
                predicate = cb.and(predicate,_predicate);
            }

            return predicate;
        };
    }
}

我希望最后一个谓词可以工作,但它引发了异常:

org.postgresql.util.PSQLException: ERROR: function date_trunc(character varying, unknown) is not unique
Wskaz&#243;wka: Could not choose a best candidate function. You might need to add explicit type casts.

传入日期的格式没问题,在调试时也有值(它对应于从 Postman 发送的值,所以一切都正常):

JPA Criteria API + PostgreSQL 按分钟精度筛选日期

JPA Criteria API + PostgreSQL 按分钟精度筛选日期

在这里,我还提供了数据在 Postgres 中的外观:

JPA Criteria API + PostgreSQL 按分钟精度筛选日期

我的问题是,如何使用 Criteria API+PostgreSQL 按日期筛选我的搜索结果,知道用户可能不提供任何日期,只提供其中一个日期或两个日期?理想情况下,我希望使用 date_trunc 的最后一个谓词可以工作,但现在对我来说太雄心勃勃了。

英文:

Due to a well known Hibernate+Postgres bug I decided to use Criteria API as many of my arguments can be null (fed from REST call).

As Criteria API behaves differently with each db client I can not get the solution when it comes to dates (yyyy-MM-dd HH:mm:ss format). Anything that worked for me has not been satisfying as either I am loosing precision or I am getting exceptions. To the point:

Here is the field/column definition from my entity which maps to timestamp column type in Postgres Database:

@Column(name = &quot;last_modified_date&quot;,
nullable = false,
updatable = false,
columnDefinition = &quot;TIMESTAMP WITHOUT TIME ZONE&quot;)
@Temporal(TemporalType.DATE)
protected Date lastModifiedDate;

Here is the the JpaRepository interface where I use CriteriaAPI:

public interface PageHistoryRepository extends JpaRepository<PageHistoryEntity, Long>, JpaSpecificationExecutor<PageHistoryEntity> {

default Page&lt;PageHistoryEntity&gt; findPages(Date fromDate,
Date toDate,
Pageable pageable) {
return findAll(search(fromDate, toDate), pageable);
}
static Specification&lt;PageHistoryEntity&gt; search(Date fromDate, Date toDate) {
return (root, cq, cb) -&gt; {
//To ensure we start with a predicate
Predicate predicate = cb.isTrue(cb.literal(true));
// this works but has no time precision
if (fromDate != null) {
Predicate _predicate = cb.greaterThanOrEqualTo(root.get(PageHistoryEntity_.lastModifiedDate.getName()).as(java.util.Date.class), fromDate);
predicate = cb.and(predicate,_predicate);
}
// this works aswell but again looses time precision
if (toDate != null) {
Predicate _predicate = cb.lessThanOrEqualTo(root.get(PageHistoryEntity_.lastModifiedDate.getName()), toDate);
predicate = cb.and(predicate,_predicate);
}
// this does not work
if (toDate != null) {
Predicate _predicate = cb.lessThanOrEqualTo(
cb.function(&quot;date_trunc&quot;, Calendar.class, cb.literal(&quot;minute&quot;), root.get(PageHistoryEntity_.lastModifiedDate.getName()).as(Calendar.class)),
cb.function(&quot;date_trunc&quot;, Calendar.class, cb.literal(&quot;minute&quot;), cb.literal(toDate)).as(Calendar.class));
predicate = cb.and(predicate,_predicate);
}
return predicate;
};
}

}

The last of the predicates that I was hoping for it to work throws exception:

org.postgresql.util.PSQLException: ERROR: function date_trunc(character varying, unknown) is not unique
Wskaz&#243;wka: Could not choose a best candidate function. You might need to add explicit type casts.

The format of the incoming date is fine, there is the value while debugging (it corresponds to the value sent from Postman so all is ok):

JPA Criteria API + PostgreSQL 按分钟精度筛选日期

JPA Criteria API + PostgreSQL 按分钟精度筛选日期

Here I also provide screen how the data looks in Postgres:

JPA Criteria API + PostgreSQL 按分钟精度筛选日期

My question here is, how can I filter my search results by date knowing that the user might not provide any of dates or just one of them or both of them using Criteria API+PostgreSQL? Ideally I would like to my last Predicate that uses date_trunc to work but this is too ambitious for me now.

答案1

得分: 2

我猜我实验得还不够,构建保留时间精度的条件的正确方式是:

if (toDate != null) {
    Predicate _predicate = cb.lessThanOrEqualTo(
            cb.function("date_trunc", Date.class, cb.literal("minute"), root.get(PageHistoryEntity_.lastModifiedDate.getName())),
            toDate);
    predicate = cb.and(predicate, _predicate);
}
英文:

I guess I experimented not enough, the proper way to build such criteria that would retain the time precision:

if (toDate != null) {
Predicate _predicate = cb.lessThanOrEqualTo(
cb.function(&quot;date_trunc&quot;, Date.class, cb.literal(&quot;minute&quot;), root.get(PageHistoryEntity_.lastModifiedDate.getName())),
toDate);
predicate = cb.and(predicate,_predicate);
}

huangapple
  • 本文由 发表于 2020年7月30日 22:43:34
  • 转载请务必保留本文链接:https://go.coder-hub.com/63175620.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定