Junit How to assert Or Condition SQL Query

Question

Or Condition Query Example

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

How to assert OR Condition Query with junit?

// this test is fail : createDate is "2022-07-28" || updateDate is 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());
}

Expect assert

( createDate > oneDaysAgo ) or ( updateDate > oneDaysAgo )

OneDaysAgo = "2022-07-27";

Create Date	Update Date	Expect Result	Actual Result
null or lessThan or equal	null or lessThan or equal	False	False
"2022-07-28"	null or lessThan or equal	True	False
null or lessThan or equal	"2022-07-28"	True	False
"2022-07-28"	"2022-07-28"	True	True

Maybe its enough to create test data were two entries matches and you will check if these entities are returned. Or write a test for every acceptance criteria. 3rd: Helper method that returns true if one criteria match. Edit: Even better: https://stackoverflow.com/a/19064484/2683501 — pL4Gu33, Jul 28 '22 at 06:37
@pL4Gu33 thx for comment. i think can't work with http://stackoverflow.com/a/19064484/2683501 i think helper method only way to solve this problem thx ! — Hulk Choi, Jul 28 '22 at 06:43

Hulk Choi · Answer 1 · 2022-07-29T07:08:56.760

thx for comment @pL4Gu33

Solve problem with Add Helper Method

  @Test
  void test_orCondition() {
    String oneDaysAgo = "2022-07-27";
    List<UserVo> list = getUserVoList(oneDaysAgo);
    assertThat(list.size(), is(greaterThan(0)));
    for (UserVo vo : list) {
      assertThat(
              getOrConditionBoolean(LocalDate.parse(oneDaysAgo)
                      , vo.getCreateDate(), vo.getUpdateDate()), is(true));
    }
  }

  private Boolean getOrConditionBoolean(LocalDate baseDate, LocalDate arg1, LocalDate arg2) {
    if (checkDate(arg1, baseDate)) {
      return true;
    }
    if (checkDate(arg2, baseDate)) {
      return true;
    }
    return false;
  }

  private boolean checkDate(LocalDate argDate, LocalDate baseDate) {
    if (argDate == null) {
      return false;
    }
    if (baseDate.isEqual(argDate) || baseDate.isBefore(argDate)) {
      return true;
    }
    return false;
  }

Additional info/review: If it is a UNIT test mock the repository result and verify the repo/... is really called. If it is a INT test using a DB create a bench of testdata where you know how the filtered result look like and verify that result is coming back by that query. The test here is not very clean — LenglBoy, Jul 29 '22 at 07:17

Junit How to assert Or Condition SQL Query

Or Condition Query Example

Expect assert

1 Answers1