[JPA/Java] JPQL CASE 문, COALESCE, NULLIF

JPQL도 SELECT 문에 CASE 문을 사용할 수 있다.

 

JPQL 기본 CASE 식

String query = "select " + 
               "       case when m.age <= 10 then '학생요금' " +
               "            when m.age >= 60 then '경로요금' " +
               "       else '일반요금' " +
               "       end " +
               " from Member m";

 

단순 CASE  식

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

 

JPQL CASE 예시)

JpaMain.java

Team team = new Team();
team.setName("teamA");
em.persist(team);

Member member = new Member();
member.setUsername("teamA");
member.setAge(10);
member.setTeam(team);
member.setType(MemberType.ADMIN);
em.persist(member);

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

String jpql =
        "select " +
                "case when m.age <= 10 then '학생요금'" +
                "     when m.age >= 60 then '경로요금'" +
                "     else '일반요금' " +
                "end " +
        "from Member m";

List<String> resultList = em.createQuery(jpql, String.class)
        .getResultList();

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

JPQL CASE 문 콘솔 내용

 

CASE 외의 조건식

 - COALESCE : 하나씩 조회해서 null이 아니면 반환

 - NULLIF : 두 값이 같으면 null 반환, 다르면 첫번째 값 반환

 

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

Team team = new Team();
team.setName("teamA");
em.persist(team);

Member member = new Member();
member.setUsername(null);
member.setAge(10);
member.setTeam(team);
member.setType(MemberType.ADMIN);
em.persist(member);

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

String jpql = "select coalesce(m.username, '이름 없는 회원') as username  from Member m";

List<String> resultList = em.createQuery(jpql, String.class)
        .getResultList();

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

coalesce 함수 사용

 

사용자 이름이 '관리자'면 null을 반환하고, 나머지는 본인의 이름을 반환

Team team = new Team();
team.setName("teamA");
em.persist(team);

Member member = new Member();
member.setUsername("관리자");
member.setAge(10);
member.setTeam(team);
member.setType(MemberType.ADMIN);
em.persist(member);

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

// 주로 데이터를 숨길 때 사용
String jpql = "select nullif(m.username, '관리자') as username  from Member m";

List<String> resultList = em.createQuery(jpql, String.class)
        .getResultList();

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

nullif 함수 사용