[JPA] 조건식

1. 타입 표현

JPQL에서 타입 표현은 대소문자를 구분하지 않는다

 

(1) 문자

문자 타입은 작은따옴표(')로 감싸서 표현해준다

List<Member> resultList = em.createQuery(
        "SELECT m" +
                " FROM Member m" +
                " WHERE m.username BETWEEN 'Avenus3' AND 'Avenus6'",
        Member.class
).getResultList();

 

List<Member> resultList = em.createQuery(
        "SELECT m" +
                " FROM Member m" +
                " WHERE m.username BETWEEN \"Avenus3\" AND \"Avenus6\"",
        Member.class
).getResultList();

이렇게 문자 타입은 큰따옴표(")로 감싸면 다음과 같은 에러가 도출된다

 

(2) 숫자

Integer = 그냥 자연수 표현하는 그대로 (10)
Long = L붙이기 (10L)
Double = D붙이기 (10D)
Float = F붙이기 (10F)

 

(3) 날짜

DATE = {d 'yyyy-mm-dd'}
TIME = {t 'hh-mm-ss}
DATETIME = {ts 'yyyy-mm-dd hh:mm:ss.f'} 

List<Member> resultList = em.createQuery(
        "SELECT m" +
                " FROM Member m" +
                " WHERE m.birth > '2000-01-01'",
        Member.class
).getResultList();

List<Order> resultList1 = em.createQuery(
        "SELECT o" +
                " FROM Order o" +
                " WHERE o.orderDate > '2022-08-10 09:00:00.0'",
        Order.class
).getResultList();

타입표현식대로 JPQL에서 [WHERE m.birth > {d '2000-01-1'}" / WHERE o.orderDate > {ts '2022-08-10 09:00:00.0}]으로 적어주고 쿼리를 실행하였는데 다음과 같은 오류가 발생하였다
→ java.lang.IllegalArgumentException: org.hibernate.QueryException: unexpected char: '{' [SELECT m FROM JPA.QueryPractice.domain.Member m WHERE m.birth > {d '2000-01-01'}]

JPA의 구현체인 Hibernate에서는 위와 같은 문법을 지원하지 않기 때문에 날짜와 관련된 쿼리를 작성하려면 작은 따옴표(') 사이에 날짜를 입력해주어서 쿼리를 작성해야 한다
위의 문법은 JPA의 또 다른 구현체인 EclipseLink가 지원하는 문법이다

 

(4) Boolean

TRUE, FALSE

List<Member> resultList = em.createQuery(
        "SELECT m" +
                " FROM Member m" +
                " WHERE m.isAdmin = TRUE",
        Member.class
).getResultList();

 

(5) Enum

Enum을 확인하려면 Enum 클래스의 "패키지명 포함 전체 이름"을 적어야 한다

// DOCTOR
List<Member> resultList = em.createQuery(
        "SELECT m" +
                " FROM Member m" +
                " WHERE m.type = JPA.QueryPractice.domain.MemberType.DOCTOR",
        Member.class
).getResultList();

// MANAGER
List<Member> resultList = em.createQuery(
        "SELECT m" +
                " FROM Member m" +
                " WHERE m.type = JPA.QueryPractice.domain.MemberType.MANAGER",
        Member.class
).getResultList();

// PLAYER
List<Member> resultList = em.createQuery(
        "SELECT m" +
                " FROM Member m" +
                " WHERE m.type = JPA.QueryPractice.domain.MemberType.PLAYER",
        Member.class
).getResultList();

 

(6) 엔티티 타입

엔티티 타입은 주로 상속 관계의 엔티티들 간의 조회를 위해서 사용한다

TYPE(m) = Member
TYPE(i) = Item

인프런 김영한 : 자바 ORM 표준 JPA 프로그래밍 - 기본편

 

2. 연산자 우선순위

순위	연산자
1순위	경로 탐색 연산 = 점(.)
2순위	수학 연산 [+, -, *, /]
3순위	비교 연산
4순위	논리 연산

 

3. Between, IN, Like, NULL 비교

(1) Between

X [NOT] Between A AND B = X가 A ~ B 사이의 값이면 참

List<Member> resultList = em.createQuery(
        "SELECT m" +
                " FROM Member m" +
                " WHERE m.birth BETWEEN '1999-01-01' AND '2001-12-31'",
        Member.class
).getResultList();

 

(2) IN

X [NOT] IN = X가 IN 내부 값들중에 하나라도 만족한다면 참

List<Member> resultList = em.createQuery(
        "SELECT m" +
                " FROM Member m" +
                " WHERE m.team.name IN('Team-A', 'Team-C')",
        Member.class
).getResultList();

 

(3) Like

X [NOT] LIKE 패턴값 [escape 문자] = X가 패턴에 적용된 문자열과 비교해서 포함되면 참

• "%" = 0개 이상의 문자
• "_" = 1개 이상의 문자
• escape 문자 = 패턴값에 포함되지 않을 문자

List<Member> resultList = em.createQuery(
        "SELECT m" +
                " FROM Member m" +
                " WHERE m.username LIKE '%Avenus1%'",
        Member.class
).getResultList();

 

(4) NULL

NULL은 =로 비교하면 안되고 반드시 IS NULL이나 IS NOT NULL로 비교해야 한다

// Team이 소속되지 않은 멤버
List<Member> resultList = em.createQuery(
        "SELECT m" +
                " FROM Member m" +
                " WHERE m.team IS NULL" +
                " ORDER BY m.id",
        Member.class
).getResultList();

// Team에 소속된 멤버
List<Member> resultList = em.createQuery(
        "SELECT m" +
                " FROM Member m" +
                " WHERE m.team IS NOT NULL" +
                " ORDER BY m.id",
        Member.class
).getResultList();

 

4. 컬렉션 식

컬렉션 식은 "컬렉션 필드"에만 사용하는 특별한 JPQL 기능이다

 

(1) 빈 컬렉션 비교

{컬렉션 경로} IS [NOT] EMPTY = 컬렉션이 비어있다면 참(size == 0)

@Entity
@Table(name = "member")
public class Member {
    ...
    ...

    @OneToMany(mappedBy = "member")
    private List<Order> orderList = new ArrayList<>();
}

// 주문 내역이 없는 멤버
List<Member> resultList = em.createQuery(
        "SELECT m" +
                " FROM Member m" +
                " WHERE m.orderList IS EMPTY",
        Member.class
).getResultList();

// 주문 내역이 존재하는 멤버
List<Member> resultList = em.createQuery(
        "SELECT m" +
                " FROM Member m" +
                " WHERE m.orderList IS NOT EMPTY",
        Member.class
).getResultList();

 

(2) 컬렉션 멤버

{엔티티/값} [NOT] MEMBER OF {컬렉션 경로} = 컬렉션에 엔티티/값이 들어있으면 참

Member findMember = em.find(Member.class, 1L);

Team team = em.createQuery(
                "SELECT t" +
                        " FROM Team t" +
                        " WHERE :member MEMBER OF t.memberList",
                Team.class
        ).setParameter("member", findMember)
        .getSingleResult();

 

5. 스칼라 식

스칼라란 가장 기본적인 타입[숫자, 문자, 날짜, case, 엔티티 타입]들을 의미한다

 

(1) 문자 함수

▶ CONCAT(문자1, 문자2, ....) = 문자들을 합친 결과를 반환

List<String> resultList = em.createQuery(
        "SELECT CONCAT(m.id, ' -> ', m.username)" +
                " FROM Member m",
        String.class
).getResultList();

 

▶ SUBSTRING(문자, 위치, [길이]) = '문자'에서 "위치"부터 시작한 substring을 구한다

• 길이를 지정하지 않으면 기본적으로 전체 길이를 의미
• 문자의 시작 index는 "1"이다

List<String> resultList = em.createQuery(
        "SELECT CONCAT(m.username, ' -> ', SUBSTRING(m.username, 3))" +
                " FROM Member m",
        String.class
).getResultList();

List<String> resultList = em.createQuery(
        "SELECT CONCAT(m.username, ' -> ', SUBSTRING(m.username, 5, 2))" +
                " FROM Member m",
        String.class
).getResultList();

 

 

▶ TRIM([[LEADING | TRAILING | BOTH] [트림문자] FROM] 문자) = 트림문자를 제거

• LEADING = 왼쪽 트림문자 제거
• TRAILING = 오른쪽 트림문자 제거
• BOTH(default) = 양쪽 트림문자 제거
• 트림문자의 기본값은 공백(SPACE)이다

// 1. 왼쪽 'x'문자 제거
List<String> resultList = em.createQuery(
        "SELECT TRIM(LEADING 'x' FROM CONCAT('xxxxx', m.username, 'xxxxx'))" +
                " FROM Member m",
        String.class
).getResultList();

// 2. 오른쪽 'x'문자 제거
List<String> resultList = em.createQuery(
        "SELECT TRIM(TRAILING 'x' FROM CONCAT('xxxxx', m.username, 'xxxxx'))" +
                " FROM Member m",
        String.class
).getResultList();

// 3. 양쪽 'x'문자 제거
List<String> resultList = em.createQuery(
        "SELECT TRIM(BOTH 'x' FROM CONCAT('xxxxx', m.username, 'xxxxx'))" +
                " FROM Member m",
        String.class
).getResultList();

// 4. 오른쪽 공백문자 제거
List<String> resultList = em.createQuery(
        "SELECT TRIM(TRAILING FROM CONCAT('     ', m.username, '     '))" +
                " FROM Member m",
        String.class
).getResultList();

1. 왼쪽 'x'문자 제거

2. 오른쪽 'X'문자 제거

3. 양쪽 'x'문자 제거

4. 오른쪽 공백문자 제거

 

 

▶ LOWER(문자) = 문자를 전부 소문자로 변경

List<String> resultList = em.createQuery(
        "SELECT LOWER(t.name)" +
                " FROM Team t",
        String.class
).getResultList();

 

▶ UPPER(문자) = 문자를 모두 대문자로 변경

List<String> resultList = em.createQuery(
        "SELECT UPPER(m.username)" +
                " FROM Member m",
        String.class
).getResultList();

 

▶ LENGTH(문자) = 문자의 길이를 구한다 (Return Type = Integer)

List<String> resultList = em.createQuery(
        "SELECT CONCAT(m.username, ' -> 길이=', LENGTH(m.username))" +
                " FROM Member m",
        String.class
).getResultList();

 

▶ LOCATE(찾을 문자, 원본 문자, [검색 시작 위치]) = "원본 문자"에서 "찾을 문자의 시작 Index"를 "검색 시작 위치"로부터 조회

• 검색 시작 위치의 기본값은 "1"이다
• LOCATE의 return type은 "Integer"이다

List<Integer> resultList = em.createQuery(
        "SELECT LOCATE('venus', m.username)" +
                " FROM Member m",
        Integer.class
).setMaxResults(1)
 .getResultList();
 // 어차피 유저의 이름은 전부 <Avenus~>이므로 LOCATE는 동일한 값들이 나올 것이므로 결과를 1행만 가져오기

List<Integer> resultList = em.createQuery(
        "SELECT LOCATE('venus', m.username, 2)" +
                " FROM Member m",
        Integer.class
).setMaxResults(1)
 .getResultList();

return되는 Index는 "검색 시작 위치"기준이 아닌 "원래 문자"기준으로 도출된다

List<Integer> resultList = em.createQuery(
        "SELECT LOCATE('venus', m.username, 3)" +
                " FROM Member m",
        Integer.class
).setMaxResults(1)
 .getResultList();

검색 시작위치로부터 조회한 결과 "찾을 문자"를 찾지 못하면 0을 반환한다

 

 

(2) 수학 함수

▶ ABS(수학식) = 수학식의 절댓값 (Return Type = Integer)

Integer singleResult = em.createQuery(
        "SELECT ABS(-100) FROM Member m",
        Integer.class
).getSingleResult();

 

▶ SQRT(수학식) = 수학식의 제곱근 (Return Type = Double)

Double singleResult = em.createQuery(
        "SELECT SQRT(166) FROM Member m",
        Double.class
).setMaxResults(1)
 .getSingleResult();

Double singleResult = em.createQuery(
        "SELECT SQRT(-100) FROM Member m",
        Double.class
).getSingleResult();

음수값에 대해서 sqrt함수를 적용하면 null이 return된다

 

 

▶ MOD(수학식, 나눌 수) = 수학식에서 "나눌 수"로 나눈 나머지 (Return Type = Integer)

Integer singleResult = em.createQuery(
        "SELECT MOD(5, 3) FROM Member m",
                Integer.class
).setMaxResults(1)
.getSingleResult();

 

▶ SIZE(컬렉션 경로) = "컬렉션"의 사이즈 (Return Type = Integer)

// 멤버별로 주문한 횟수
List<Integer> resultList = em.createQuery(
        "SELECT SIZE(m.orderList)" +
                " FROM Member m",
        Integer.class
).getResultList();

 

▶ INDEX(별칭) = List 타입 컬렉션의 위치값을 구한다

• 단 List컬렉션에 @OrderColumn이 붙어있어야 사용 가능하다

 

(3) 날짜 함수

CURRENT_DATE = 현재 날짜
CURRENT_TIME = 현재 시간
CURRENT_TIMESTAMP = 현재 날짜 + 시간

List<Object[]> resultList = em.createQuery(
        "SELECT DISTINCT CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP" +
                " FROM Member m"
).getResultList();

List<Member> resultList = em.createQuery(
        "SELECT m" +
                " FROM Member m" +
                " WHERE m.birth > '2000-01-01' AND m.birth < CURRENT_DATE ",
        Member.class
).getResultList();

 

 

6. CASE 식

특정 조건에 따라 값들을 분기할 때 CASE 식을 사용한다

 

(1) 기본 CASE

CASE
    WHEN <조건식> THEN <스칼라식>
    WHEN <조건식> THEN <스칼라식>
    ...
    ELSE <스칼라식>
END

---

List<String> resultList = em.createQuery(
        "SELECT " +
                "   CASE" +
                "       WHEN m.id = 1 THEN '첫번째 멤버'" +
                "       WHEN m.id = 10 THEN '마지막 멤버'" +
                "       ELSE m.username" +
                "   END" +
                " FROM Member m",
        String.class
).getResultList();

 

(2) 심플 CASE

CASE <조건 대상>
    WHEN <스칼라식1> THEN <스칼라식2>
    WHEN <스칼라식1> THEN <스칼라식2>
    ...
    ELSE <스칼라식>
END

---

List<String> resultList = em.createQuery(
        "SELECT " +
                "   CASE m.id" +
                "       WHEN 1 THEN '첫번째 멤버'" +
                "       WHEN 10 THEN '마지막 멤버'" +
                "       ELSE m.username" +
                "   END" +
                " FROM Member m",
        String.class
).getResultList();

 

(3) COALESCE

COALESCE(<스칼라식>, [<스칼라식>, <스칼라식>, ....] = 스칼라식을 차례대로 조회해서 null이 아닌 "첫번째 값"을 반환

• 스칼리식 전부 null이면 결국 null을 반환한다

Object resultList = em.createQuery(
                "SELECT COALESCE(m.team, '팀이 없는 멤버')" +
                        " FROM Member m" +
                        " WHERE m.id = 1"
        ).setMaxResults(1)
        .getSingleResult();

 

(4) NULLIF

NULLIF(<스칼라식>, <스칼라식>) = 두 값이 동일하면 null을 반환, 두 값이 다르면 첫번째 값을 반환

Long singleResult = em.createQuery(
        "SELECT NULLIF(m.team.id, null)" +
                " FROM Member m" +
                " WHERE m.id = 1",
        Long.class
).setMaxResults(1)
 .getSingleResult();

• PK값이 1인 멤버의 팀은 "Team-A = PK(1)"이다

Long singleResult = em.createQuery(
        "SELECT NULLIF(m.team.id, null)" +
                " FROM Member m" +
                " WHERE m.id = 3",
        Long.class
).setMaxResults(1)
 .getSingleResult();

• PK값이 3인 멤버의 팀은 "null"이다