Case식

1. Case식 예제

String query = "select t.name, " +
                    "case t.name " +
                    "     when '팀A' then '인센티브110%' " +
                    "     when '팀B' then '인센티브120%' " +
                    "     else '인센티브105%' " +
                    "end " +
          "from Team t";

List<Object[]> resultList = em.createQuery(query).getResultList();

for (Object[] objects : resultList) {
    System.out.println("objects[0] = " + objects[0]);
    System.out.println("objects[0] = " + objects[1]);
}

// 결과
objects[0] = 팀A
objects[0] = 인센티브110%
objects[0] = 팀B
objects[0] = 인센티브120%

2. Case 관련 함수들

1) COALESCE

사용자 이름이 없으면 이름 없는 회원을 반환

Member member = new Member();
// member.setUsername("A");
member.setAge(10);
member.setTeam(team);
em.persist(member);

Member member2 = new Member();
member2.setUsername("B");
member2.setAge(20);
member2.setTeam(team2);
em.persist(member2);

em.flush();
em.clear();

String query = "select COALESCE(m.username, '이름없는 회원' from Member m"
List<String> resultList = em.createQuery(query, String.class).getResultList();

for (String s : resultList) {
    System.out.println("s = " + s);
}

// 결과
s = 이름 없는 회원
s = B

2) NULLIF

사용자 이름이 관리자면 null을 반환하고 관리자가 아니면 본인의 이름을 반환

Member member = new Member();
member.setUsername("관리자");
member.setAge(10);
em.persist(member);

Member member2 = new Member();
member2.setUsername("회원");
member2.setAge(20);
em.persist(member2);

em.flush();
em.clear();

String query = "select NULLIF(m.username, '관리자') from Member m";
List<String> resultList = em.createQuery(query, String.class).getResultList();

for (String s : resultList) {
    System.out.println("s = " + s);
}

// 결과
s = null
s = 회원