[Java][SQL] カラムをNULL判定して切り替えるCASE式を作成

SqlUtils.java

/**
 * @param cols 切替候補カラム
 * @return SQL文case式部分
 */
public String editAltNullCase(String[] cols) {
  StringBuilder res = new StringBuilder();
  if (cols.length == 1) {
    res.append(cols[0]);
    res.append(" ");
  } else {
    String[] subCols = new String[cols.length - 1];
    for (int i = 0; i < subCols.length; i++) {
      subCols[i] = cols[i + 1];
    }
    res.append("(case when ");
    res.append(cols[0]);
    res.append(" is not null then ");
    res.append(cols[0]);
    res.append(" else ");
    res.append(editAltNullCase(subCols));
    res.append("end) ");
  }
  return res.toString();
}


@Test
public void testEditNullCase() {
  String result = dao.editAltNullCase(new String[] {"COL1", "COL2"});
  assertEquals("(case when COL1 is not null then COL1 else COL2 end) ", result);
}

@Test
public void testEditNullCase3() {
  String result = dao.editAltNullCase(new String[] {"COL1", "COL2", "COL3"});
  assertEquals("(case when COL1 is not null then COL1 else "
    + "(case when COL2 is not null then COL2 else COL3 end) end) ", result);
}

@Test
public void testEditNullCase1() {
  String result = dao.editAltNullCase(new String[] {"COL1"});
  assertEquals("COL1 ", result);
}