[JPA] JPQL CASE

JPQL CASE

• JPQL 에서 case문 사용하기
• SQL 과 비슷하다.

 

DB의 설정한값

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

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

em.persist(member1);

Team team1 = new Team();
team1.setName("teamB");
em.persist(team1);

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

em.persist(member2);
em.flush();

 

기본 CASE

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

 

단순 CASE

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

 

 

예제

// case when 예제
String select = "select case when m.age <= 10 then '학생요금' when m.age >= 60 then '경로요금' else '일반요금' end from JPQLMember m";
List<String> resultList1 = em.createQuery(select, String.class).getResultList();

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

 

 

결과

Hibernate: 
    /* select
        case 
            when m.age <= 10 then '학생요금' 
            when m.age >= 60 then '경로요금' 
            else '일반요금' 
        end 
    from
        JPQLMember m */ select
            case 
                when jpqlmember0_.age<=10 then '학생요금' 
                when jpqlmember0_.age>=60 then '경로요금' 
                else '일반요금' 
            end as col_0_0_ 
        from
            JPQLMember jpqlmember0_
s = 학생요금
s = 학생요금

 

 

 

JPQL COALESCE

• 하나씩 조회해서 해당값이 null이 아니면 조회된데이터를 반환하고
• null 이면 사용자가 지정한 값을 반환한다.

 

 

예제

 // coalesce 예제
 String selectCoalesce = "select coalesce(m.username,'이름없는회원') as username from JPQLMember m";
 List<String> resultList2 = em.createQuery(selectCoalesce, String.class).getResultList();

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

 

결과

Hibernate: 
    /* select
        coalesce(m.username,
        '이름없는회원') as username 
    from
        JPQLMember m */ select
            coalesce(jpqlmember0_.USER_NAME,
            '이름없는회원') as col_0_0_ 
        from
            JPQLMember jpqlmember0_
            
s = member2
s = 이름없는회원

 

 

 

JPQL NULL IF

• 두값이 같으면 null 을 반환한다.
• 다르면 첫번째 값을 반환한다.

예제

// nullif 예제
String query = "select nullif(m.username,'teamA') as username from JPQLMember m";
List<String> resultList3 = em.createQuery(query, String.class).getResultList();

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

 

결과

Hibernate: 
    /* select
        nullif(m.username,
        'teamA') as username 
    from
        JPQLMember m */ select
            nullif(jpqlmember0_.USER_NAME,
            'teamA') as col_0_0_ 
        from
            JPQLMember jpqlmember0_
s = member2
s = null

 

 

 

 

참고:

https://www.inflearn.com/course/ORM-JPA-Basic/lecture/21726?tab=curriculum 

자바 ORM 표준 JPA 프로그래밍 - 기본편 - 인프런 | 학습 페이지