在Criteria Query where子句中使用日期

I have an entity with a java.util.Date field stored as a TemporalType.DATE. When passing a java.util.Date with hours, minutes or seconds to the where clause of a criteria query I can't seem to get a match from the database.

我有一个实体,其java.util.Date字段存储为TemporalType.DATE。将带有小时,分钟或秒的java.util.Date传递给条件查询的where子句时,我似乎无法从数据库中获得匹配。

The setup is an embedded H2-database in Spring with Hibernate. I've tried using PostgreSQL instead of H2 and it works. I've also tried to set H2 in PostgreSQL-mode, but it doesn't change anything.

该设置是Spring中使用Hibernate的嵌入式H2数据库。我尝试使用PostgreSQL而不是H2,它可以工作。我也尝试在PostgreSQL模式下设置H2,但它没有改变任何东西。

Given the entity

给定实体

@Entity
public class SomeEntity {

    @Id
    private int id;

    @Temporal(TemporalType.DATE)
    private java.util.Date aDate;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public Date getDate() {
        return aDate;
    }

    public void setDate(Date aDate) {
        this.aDate = aDate;
    }
}

The following query only returns a match if the hours, minutes and seconds of the parameter have been set to 0.

如果参数的小时,分钟和秒设置为0,则以下查询仅返回匹配项。

public List<SomeEntity> someQueryOnDate(Date date) {
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<SomeEntity> query = cb.createQuery(SomeEntity.class);

    Root<SomeEntity> root = query.from(SomeEntity.class);
    Predicate dateEquals = cb.equal(root.get(SomeEntity_.date), date);
    query.where(dateEquals);

    // This list is always empty if the date in the predicate has a time part
    return em.createQuery(query).getResultList(); 
}

A full example follows. The test fails on the last assertion, where I query the database using a Date with hours, minutes and seconds set.

完整的例子如下。最后一个断言测试失败,我使用设置小时,分钟和秒的日期查询数据库。

@Test
public void testDateEquals() throws ParseException {
    Date dateWithoutTime = new SimpleDateFormat("yyyy-MM-dd").parse("2014-07-03");
    Date dateWithTime    = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse("2014-07-03 09:45:01");

    createEntity(dateWithoutTime);

    List<SomeEntity> entitiesMatchingDateWithTime    = listAllEntitiesWithDate(dateWithTime);
    List<SomeEntity> entitiesMatchingDateWithoutTime = listAllEntitiesWithDate(dateWithoutTime);

    Assert.assertFalse("No entities matched the date without time", entitiesMatchingDateWithoutTime.isEmpty());
    Assert.assertFalse("No entities matched the date with time"   , entitiesMatchingDateWithTime.isEmpty());
}

private void createEntity(Date d) {
    SomeEntity entity = new SomeEntity();
    entity.setDate(d);
    em.persist(entity);

    // For good measure
    em.flush();
    em.clear();
}

private List<SomeEntity> listAllEntitiesWithDate(Date date) {
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<SomeEntity> query = cb.createQuery(SomeEntity.class);

    Root<SomeEntity> root = query.from(SomeEntity.class);
    Predicate dateEquals = cb.equal(root.get(SomeEntity_.date), date);
    query.where(dateEquals);

    return em.createQuery(query).getResultList();
}

1 个解决方案

#1

Maybe the solution is provided here:

也许解决方案在这里提供:

Hibernate Criteria for Dates

日期的休眠标准

You can use Restrictions to compare dates.

您可以使用限制来比较日期。

#1