在Spring Data Query中获取两个java.time.Instant实例之间的记录 [英] Fetching records BETWEEN two java.time.Instant instances in Spring Data Query

问题描述

我正在尝试使用Spring Data的 @Query 获取两个Instants之间的记录列表。

I am trying to fetch a list of records between two Instants using Spring Data's @Query.

 /**
   * Finds all the non-duplicate customers.
   * 
   * @return The list of non-duplicate customers.
   */
  //@formatter:off
  @Query("SELECT c "
      + "FROM Customer c "
      + "WHERE c.isDuplicate = false "
      + "  AND c.created BETWEEN :start AND :end "
      + "ORDER BY c.created")
  //@formatter:on
  List<Customer> findAllNonDuplicateCustomers(
      @Param("start") Instant start,
      @Param("end") Instant end
  );

测试时我发现：

this.customerRepository.findAllNonDuplicateCustomers(
    Instant.MIN, 
    Instant.MAX
)

返回一个空列表，但是：

returns an empty list, but:

this.customerRepository.findAllNonDuplicateCustomers(
    this.customers.get("rob").getCreated(), 
    this.customers.get("robba").getCreated()
)

返回所需的结果。

测试场景：

我的测试将6个客户插入到嵌入式H2数据库中并尝试运行查询。

My test inserts 6 customers into an embedded H2 database and tries to run the query.

因为Instant是Comparable，它可以工作对于确切的客户来说，使用Instant.MIN和Instant.MAX进行边界测试是不对的？

Since Instant is Comparable, and it works fine for exact customers, is it wrong to use Instant.MIN and Instant.MAX for boundary testing?

推荐答案

我创建了一点示例项目并启用hibernates记录绑定参数，结果如下：

I created a little example project and enabled hibernates logging for the bind parameters resulting in this:

binding parameter [1] as [TIMESTAMP] - [-1000000000-01-01T00:00:00Z]
binding parameter [2] as [TIMESTAMP] - [[phone]T23:59:59.999999999Z]

正如可以看到参数传递到数据库而不做任何修改。数据库无法正常处理，但它显然可以处理更多正常值。

As one can see the arguments get passed to the database without any modification. The database just can't handle it properly, but it obviously can handle more "normal" values.

我认为它是一个错误或至少是一个限制JDBC驱动程序。
我也尝试过HSQLDB。它有一个类似的限制，但它不会返回空结果而是抛出异常。

I'd consider it a bug or at least a limitation of the JDBC driver. I also tried it with HSQLDB. It has a similar limitation but instead of returning empty results it throws an exception.

那么你能做什么：

• 提交H2问题。他们可能会添加支票并抛出异常，但我怀疑他们会增加适当的支持。

• Submit an issue with H2. They might add a check and throw an exception but I doubt that they will add proper support.

定义你自己的 MIN ， MAX 实际工作的值。经过一些实验，这似乎在H2可以处理它的情况下运行得相当好，并且很难提出真实场景，这些场景在未来/过去都足够远。

Define your own MIN, MAX values that actually work. After a little experimentation, this seems to work reasonably well in the sense that H2 can handle it and it's hard to come up with real-world scenarios where these are sufficiently far in the future/past.

static final Instant CUSTOM_MIN = new Date(Long.MIN_VALUE / 2).toInstant();
static final Instant CUSTOM_MAX = new Date(Long.MAX_VALUE / 2).toInstant();

注意：

不存在与数据库无关的最小/最大日期。

最小/最大 java.util.Date 这里的值'don'无论如何工作，但显然激发了我的解决方案。

The minimum/maximum java.util.Date values here don't work either but obviously inspired my solution.

这篇关于在Spring Data Query中获取两个java.time.Instant实例之间的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！