How can I assert query while ignoring difference in time produced by LocalDateTime.now()?

Question

I have issues asserting time produced by LocalDateTime.now() methods, I want to assert whole query while ignoring time it produces. Here is the code:

 @Test
public void getLastDrawResult_lotteryIdPresent() {
    Optional<Long> lotteryId = Optional.of(3L);
    Optional<String> name = Optional.empty();
    Optional<String> byName = Optional.empty();
    Optional<String> byDate = Optional.empty();
    Optional<Boolean> jackpotOnly = Optional.empty();

    String query = QUERY_MAIN_BLOCK.replace("/*statement0*/", LocalDateTime.now().plusMinutes(buyingLock).toString()) + CONDITION_SEARCH_BY_LAST_DRAW_RESULT;
    String updatedQuery = query.replace("/*statement1*/", " WHERE d.lottery_info_id = 3") + " AND d.lottery_info_id = 3";

    DrawResultDto drawResultDto = DrawResultDto.builder().id(3L).build();
    List<DrawResultDto> expectedDrawResultDto = singletonList(drawResultDto);

    when(jdbcTemplate.query(updatedQuery, drawResultDtoQueryBuilder.queryMapper)).thenReturn(expectedDrawResultDto);
    List<DrawResultDto> actualDrawResultDto = drawResultDtoQueryBuilder.getLastDrawResult(lotteryId, name, byName, byDate, jackpotOnly);

    verify(jdbcTemplate).query(updatedQuery, drawResultDtoQueryBuilder.queryMapper);
    assertEquals(expectedDrawResultDto, actualDrawResultDto);

Here is the comparison Failure details, so you can have more idea what I need to ignore: Comparison Failure Window Screenshot.

Here is the method I'm testing:

public List<DrawResultDto> getLastDrawResult(Optional<Long> lotteryId, Optional<String> name, Optional<String> byName, Optional<String> byDate, Optional<Boolean> jackpotOnly) {
    String query = QUERY_MAIN_BLOCK.replace("/*statement0*/", LocalDateTime.now().plusMinutes(buyingLock).toString()) +
            CONDITION_SEARCH_BY_LAST_DRAW_RESULT;
    StringBuilder builder = new StringBuilder();
    if (lotteryId.isPresent()) {
        builder.append(query.replace("/*statement1*/", " WHERE d.lottery_info_id = " + lotteryId.get()))
                .append(String.format(" AND d.lottery_info_id = %d", lotteryId.get()));
    } else {
        builder.append(query);
        name.ifPresent(nameValue -> builder.append(" AND lower(li.name) LIKE '%").append(SqlUtils.escapeLike(nameValue).toLowerCase()).append("%' "));
        jackpotOnly.ifPresent(jackpotOnlyValue -> {
            if (jackpotOnlyValue) {
                builder.append(" AND ").append(CONDITION_SEARCH_BY_JACKPOT_WIN);
            }
        });
        byName.ifPresent(s -> builder.append(" ORDER BY li.name ").append(SqlUtils.escapeLike(s)));
        byDate.ifPresent(s -> builder.append(" ORDER BY nfd.nearestDrawDate ").append(SqlUtils.escapeLike(s)));
        if (!byName.isPresent() && !byDate.isPresent()){
            builder.append(" ORDER BY li.name desc ");
        }
    }
    return jdbcTemplate.query(builder.toString(), queryMapper);
}

I tried to do matches() from Mockito, no luck. I can't seem to get the regex working. Like Match everything BUT the following Regex: (\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}.\d{3}) . Which selects the part which I need to ignore.

Answer 1

Best-practices here would involve you injecting a Clock, which will give you access to any objects (including LocalDateTime) you need via an override of the now method.

public List<DrawResultDto> getLastDrawResult(
    Optional<Long> lotteryId,
    Optional<String> name,
    Optional<String> byName,
    Optional<String> byDate,
    Optional<Boolean> jackpotOnly,
    Clock clock) {
  LocalDateTime localDateTime = LocalDateTime.now(clock);
  /* ... */
}

As in Ash's answer, you could make a method overload with your current signature that creates a new SystemClock, and then test the method that accepts the clock.

/** Your original method signature. No changes to any calling code. */
public List<DrawResultDto> getLastDrawResult(
    Optional<Long> lotteryId,
    Optional<String> name,
    Optional<String> byName,
    Optional<String> byDate,
    Optional<Boolean> jackpotOnly) {
  return getLastDrawResult(lotteryId, name, byName, byDate, jackpotOnly,
      Clock.systemDefaultZone());
}

/** Your original method implementation. Test this one. */
public List<DrawResultDto> getLastDrawResult(
    Optional<Long> lotteryId,
    Optional<String> name,
    Optional<String> byName,
    Optional<String> byDate,
    Optional<Boolean> jackpotOnly,
    Clock clock) {
  LocalDateTime localDateTime = LocalDateTime.now(clock);
  /* ... */
}

This way, in your test, you can pass in a value from Clock.fixed.

Separately, you should strongly consider switching this method to parameterized queries, which JdbcTemplate supports natively. You can even support named parameters using related classes.

Answer 2

Seems like you are writing your tests after your code :p (otherwise you would have never run into such an issue).

What I would do is to pass the localedatetime to the function, so that you can unit test it properly :

public List<DrawResultDto> getLastDrawResult(LocalDateTime t, Optional<Long> lotteryId, Optional<String> name, Optional<String> byName, Optional<String> byDate, Optional<Boolean> jackpotOnly) {
    String query = QUERY_MAIN_BLOCK.replace("/*statement0*/", t.plusMinutes(buyingLock).toString()) +
    [...]
}

and to keep consistency with your existing code, keep the actual signature:

public List<DrawResultDto> getLastDrawResult(Optional<Long> lotteryId, Optional<String> name, Optional<String> byName, Optional<String> byDate, Optional<Boolean> jackpotOnly) {
    return getLastDrawResult(LocaleDateTime.now(), Optional<Long> lotteryId, Optional<String> name, Optional<String> byName, Optional<String> byDate, Optional<Boolean> jackpotOnly);
}

Now you can unit test properly the first method (by passing it a localedatetime built inside the test method), unit testing the second method is trivial.