如何用JUnit断言SQL OR条件查询结果？

SELECT ID, NAME, CREATE_DATE, UPDATE_DATE 
FROM USER 
WHERE ( CREATE_DATE > SYSDATE-1 OR UPDATE_DATE > SYSDATE -1)

问题 #

如何用JUnit断言OR条件查询的结果？

以下测试代码执行失败，比如当createDate为"2022-07-28"且updateDate为null的场景：

// 此测试失败：createDate为"2022-07-28" || updateDate为null
String oneDaysAgo = "2022-07-27";
List<UserInfo> list = jpaRepository.findAllByCreateDateGreaterThanOrUpdateDateGreaterThan(oneDaysAgo,oneDaysAgo);
for (UserInfo vo : list) {
   assertThat(vo.getCreatedDate(), is(greaterThan(oneDaysAgo)));
   assertThat(vo.getUpdateDate(), is(greaterThan(oneDaysAgo)));
   log.info(vo.toString());
}

问题根源 #

当前断言逻辑是同时要求createdDate和updateDate都大于oneDaysAgo，和查询语句的OR逻辑完全矛盾，导致仅符合单一条件的记录被断言判定为失败。

正确的断言逻辑需与查询逻辑一致：( createDate > oneDaysAgo ) or ( updateDate > oneDaysAgo )

其中OneDaysAgo = "2022-07-27";

可以通过两种方式修正断言逻辑：

方式1：直接用逻辑或判断 #

String oneDaysAgo = "2022-07-27";
List<UserInfo> list = jpaRepository.findAllByCreateDateGreaterThanOrUpdateDateGreaterThan(oneDaysAgo,oneDaysAgo);
for (UserInfo vo : list) {
    boolean meetsCondition = 
        (vo.getCreatedDate() != null && vo.getCreatedDate().compareTo(oneDaysAgo) > 0)
        || (vo.getUpdateDate() != null && vo.getUpdateDate().compareTo(oneDaysAgo) > 0);
    assertThat(meetsCondition, is(true));
    log.info(vo.toString());
}

方式2：使用Hamcrest的anyOf匹配器 #

若项目依赖Hamcrest断言库，可更优雅实现OR逻辑：

import static org.hamcrest.Matchers.anyOf;
import static org.hamcrest.Matchers.greaterThan;
import static org.hamcrest.beans.HasPropertyWithValue.hasProperty;

// ...

String oneDaysAgo = "2022-07-27";
List<UserInfo> list = jpaRepository.findAllByCreateDateGreaterThanOrUpdateDateGreaterThan(oneDaysAgo,oneDaysAgo);
for (UserInfo vo : list) {
    assertThat(vo, anyOf(
        hasProperty("createdDate", greaterThan(oneDaysAgo)),
        hasProperty("updateDate", greaterThan(oneDaysAgo))
    ));
    log.info(vo.toString());
}

额外提示 #

• 务必处理日期为null的情况，避免空指针异常；
• 若使用LocalDate等日期类型，建议用isAfter()方法替代字符串比较，逻辑更可靠：

LocalDate oneDaysAgo = LocalDate.of(2022, 7, 27);
// ...
boolean meetsCondition = 
    (vo.getCreatedDate() != null && vo.getCreatedDate().isAfter(oneDaysAgo))
    || (vo.getUpdateDate() != null && vo.getUpdateDate().isAfter(oneDaysAgo));

内容的提问来源于stack exchange，提问作者Hulk Choi