使用JPA条件API进行日期比较 [英] Date comparison using the JPA criteria API

问题描述

我有一个带两个日期的范围日期选择器:start和end，两个都可以为空.我想过滤一个表，其中实体有一个确切的日期:date.

I got a range date picker with two dates: start and end, where both can be empty. I would like to filter a table, where the entity has exact one date: date.

因此，这里有一些例子.我想比赛.想象要匹配的日期是当前日期(2016年7月17日).

So, here are some examples. I'd like to match. Imaging the date to match is the current date (17/07/2016).

• 空-2016年7月17日->比赛
• 2016年7月17日-空->匹配
• 2016年7月17日-2016年7月17日->比赛
• null-null->全部匹配

在我看来，这就是最极端的情况，也是我之所以如此努力的原因.

Those are the edge cases in my opinion and the reason why I am struggling so much.

实际上我的代码如下:

CriteriaBuilder cb = getEm().getCriteriaBuilder();
CriteriaQuery<Transaction> cq = cb.createQuery(Transaction.class);
Root<Transaction> root = cq.from(Transaction.class);

List<Predicate> predicates = new ArrayList<>();

Predicate startPredicate = cb.greaterThanOrEqualTo(root.get(Transaction_.date), start);
Predicate endPredicate = cb.greaterThanOrEqualTo(root.get(Transaction_.date), end);

predicates.add(startPredicate);
predicates.add(endPredicate);
cq.select(root).where(predicates.toArray(new Predicate[] {}));

TypedQuery<Transaction> query = getEm().createQuery(cq);
query.setFirstResult(firstRow);
query.setMaxResults(maxRow);
List<Transaction> resultList = query.getResultList();

我想得到这样的查询:

SELECT * FROM transaction
WHERE ((cast(date AS DATE) >= '2016-07-16' OR cast(date AS DATE) IS NULL))
       AND ((cast(date AS DATE) <= '2016-07-17' OR cast(date AS DATE) IS NULL))

请注意:静态日期是模拟开始和结束日期.并且date是表格列.

Please note: The static date is to simulate a start and end date. and date is the table column.

我知道我的代码是错误的.它仅匹配范围，而不考虑空值.另外，如果开始和结束是同一天，我将得到零结果.

I know that my code is wrong. It matches only ranges, without considering null values. Also, if start and end is the same day, I will get zero results.

你有什么主意吗?如何修改代码以匹配所有提到的模式?

Do you have you any idea? How can I edit my code to match all the mentioned patterns?

推荐答案

我有一个名为discount的现有数据库表，在MySQL数据库中有两列类型为TIMESTAMP的列，分别为discount_start_date和discount_end_date.因此，请根据表名和该表中的相应列调整查询.

I have an existing database table named discount with two columns of type TIMESTAMP named discount_start_date and discount_end_date in a MySQL database. So, please adjust your query according to the name of the table and respective columns in that table.

基于问题中给出的SQL语句的完整条件查询可以构造如下(我希望代码是不言自明的).

The complete criteria query based on the SQL statement given in the question can be constructed as follows (I hope the code would be self-explanatory).

CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<Discount> criteriaQuery = criteriaBuilder.createQuery(Discount.class);
Root<Discount> root = criteriaQuery.from(entityManager.getMetamodel().entity(Discount.class));

SimpleDateFormat dateFormat = new SimpleDateFormat("dd-MM-yyyy");
java.util.Date startDate = dateFormat.parse("24-02-2016");
java.util.Date endDate = dateFormat.parse("24-03-2016");

ParameterExpression<java.util.Date> parameter = criteriaBuilder.parameter(java.util.Date.class);

Predicate startDatePredicate = criteriaBuilder.greaterThanOrEqualTo(root.get(Discount_.discountStartDate).as(java.sql.Date.class), parameter);
Predicate endDatePredicate = criteriaBuilder.lessThanOrEqualTo(root.get(Discount_.discountEndDate).as(java.sql.Date.class), parameter);

Predicate startDateOrPredicate = criteriaBuilder.or(startDatePredicate, root.get(Discount_.discountStartDate).isNull());
Predicate endDateOrPredicate = criteriaBuilder.or(endDatePredicate, root.get(Discount_.discountEndDate).isNull());

Predicate and = criteriaBuilder.and(startDateOrPredicate, endDateOrPredicate);
criteriaQuery.where(and);

List<Discount> list = entityManager.createQuery(criteriaQuery)
        .setParameter(parameter, startDate, TemporalType.DATE)
        .setParameter(parameter, endDate, TemporalType.DATE)
        .getResultList();

它会产生您感兴趣的以下SQL查询(如您所述，这两个字段都包含在内).

It produces the following SQL query of your interest (both fields are inclusive as you stated).

select
    discount0_.discount_id as discount1_15_,
    discount0_.discount_code as discount2_15_,
    discount0_.discount_end_date as discount3_15_,
    discount0_.discount_percent as discount4_15_,
    discount0_.discount_start_date as discount5_15_ 
from
    project.discount discount0_ 
where
    (
        cast(discount0_.discount_start_date as date)>=? 
        or discount0_.discount_start_date is null
    ) 
    and (
        cast(discount0_.discount_end_date as date)<=? 
        or discount0_.discount_end_date is null
    )

在Hibernate 4.3.6最终版本上进行了测试，但是普通的ORM框架应该产生相同的查询，而无需进行任何修改.

Tested on Hibernate 4.3.6 final but average ORM frameworks should produce the same query without any modifications.

在此方法setParameter(parameter, startDate, TemporalType.DATE)中，仅当数据库中具有类型为DATETIME或TIMESTAMP的列并且想要比较忽略该时间部分的日期时，才需要最后一个参数即TemporalType.DATE列.如果您的列没有像DATE(MySQL)这样的时间部分，则可以简单地排除该参数.

In this method setParameter(parameter, startDate, TemporalType.DATE), the last parameter i.e. TemporalType.DATE is only needed, if you have a column of type DATETIME or TIMESTAMP in your database and you want to compare dates ignoring the time portion of such columns. You can simply exclude that parameter, if your columns do not have a time portion like DATE (MySQL).

如有必要，您还可以使用其他日期(时间)处理API，例如java.time或JodaTime，将Date替换为SimpleDateFormat

You can, if necessary, also use other date (time) handling APIs like java.time or JodaTime replacing Date along with SimpleDateFormat

这篇关于使用JPA条件API进行日期比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！