DEV ℧ Developer Diary

[JPA] JPQL - 조건식

20 Aug 2022

해당 포스트는 인프런 김영한님의 자바 ORM 표준 JPA 프로그래밍 - 기본편 을 듣고 정리한 글입니다.

조건식

기본 CASE 식

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

예시

10살인 member 데이터를 추가해 case로 조건을 걸어보자.

Member member = new Member();
member.setUsername("member");
member.setAge(10);
member.setType(MemberType.valueOf("USER"));
entityManager.persist(member);

entityManager.flush();
entityManager.clear();

String conditionalQuery = "select " +
                            "m.username," +
                            "case when m.age <= 10 then '학생요금'" +
                            "     when m.age >= 60 then '경로요금'" +
                            "     else '일반요금' " +
                            "end " +
                            "from Member m ";
List<Object[]> result = entityManager.createQuery(conditionalQuery)
        .getResultList();

for (Object[] obj : result) {
    System.out.println("username : " + obj[0]);
    System.out.println("fee : " + obj[1]);
}

    /* select
        m.username,
        case
            when m.age <= 10 then '학생요금'
            when m.age >= 60 then '경로요금'
            else '일반요금'
        end
    from
        Member m  */ select
            member0_.username as col_0_0_,
            case
                when member0_.age<=10 then '학생요금'
                when member0_.age>=60 then '경로요금'
                else '일반요금'
            end as col_1_0_
        from
            Member member0_

username : member
fee : 학생요금

case로 조건을 걸어 해당하는 학생이 age <= 10에 해당하는 학생요금으로 결과가 조회되는 것을 확인 할 수 있다.

단순 CASE 식

select
 case t.name
 when '팀A' then '인센티브110%'
 when '팀B' then '인센티브120%'
 else '인센티브105%'
 end
from Team t

예시

팀의 이름에 따라 한글로 명칭을 부여하는 조건문을 작성해보자.

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

entityManager.flush();
entityManager.clear();

String conditionalQuery = "select " +
                            "t.name," +
                            "case t.name" +
                            "     when 'teamA' then '개발팀A'" +
                            "     when 'teamB' then '개발팀B'" +
                            "     else '개발팀C' " +
                            "end " +
                            "from Team t";
List<Object[]> result = entityManager.createQuery(conditionalQuery)
        .getResultList();

for (Object[] obj : result) {
    System.out.println("name : " + obj[0]);
    System.out.println("이름 : " + obj[1]);
}

    /* select
        t.name,
        case t.name
            when 'teamA' then '개발팀A'
            when 'teamB' then '개발팀B'
            else '개발팀C'
        end
    from
        Team t */ select
            team0_.name as col_0_0_,
            case team0_.name
                when 'teamA' then '개발팀A'
                when 'teamB' then '개발팀B'
                else '개발팀C'
            end as col_1_0_
        from
            Team team0_

name : teamA
이름 : 개발팀A

조건식

  • COALESCE: 하나씩 조회해서 null이 아니면 반환
  • 해당 함수는 MySQL 에서는 IFNULL, Oracle에서는 NVL로 사용된다.

사용자 이름이 없으면 이름 없는 회원을 반환 select coalesce(m.username,’이름 없는 회원’) from Member m

Member member = new Member();
member.setUsername(null); /* username을 null로 입력 */
member.setAge(10);
member.setType(MemberType.valueOf("USER"));
entityManager.persist(member);

entityManager.flush();
entityManager.clear();

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

for (String name : result) {
    System.out.println("member name : " + name);
}

    /* select
        coalesce(m.username,
        '이름 없는 회원')
    from
        Member m */ select
            coalesce(member0_.username,
            '이름 없는 회원') as col_0_0_
        from
            Member member0_

member name : 이름 없는 회원
  • NULLIF: 두 값이 같으면 null 반환, 다르면 첫번째 값 반환

사용자 이름이 ‘관리자’면 null을 반환하고 나머지는 본인의 이름을 반환 select NULLIF(m.username, ‘관리자’) from Member m

Member member = new Member();
member.setUsername(null); /* username을 null로 입력 */
member.setAge(10);
member.setType(MemberType.valueOf("USER"));
entityManager.persist(member);

entityManager.flush();
entityManager.clear();

String query2 = "select nullif(m.username, '관리자') as username from Member m";
List<String> result2 = entityManager.createQuery(query2, String.class)
                .getResultList();

for (String name : result2) {
    System.out.println("member name : " + name);
}

/* select
        nullif(m.username,
        '관리자') as username
    from
        Member m */ select
            nullif(member0_.username,
            '관리자') as col_0_0_
        from
            Member member0_

member name : null

Related posts