Chapter 10. 객체지향 쿼리 언어1

Spring Framework/[인프런] Java ORM 표준 프로그래밍 - JPA

Chapter 10. 객체지향 쿼리 언어1 - 기본 문법

계란💕 2022. 9. 8. 17:38

10.0 intro

JPA가 지원하는 다양한 쿼리 방법

JPQL(Java Persistence Query Language)
- JPA Criteria(객체 지향 쿼리 빌더): Java 코드를 짜서 JPQL을 빌드해주는 모음
  - (JPQL의 작성을 도와주는 빌더 클래스이다.)
- 기본으로 JPQL을 쓰고 안 되는 경우에 네이티브 SQL 사용
QueryDSL
- 네이티브 SQL
- 쿼리를 Java 코드로 작성할 수 있도록 도와주는 기술이다.
- JDBC API 직접 사용, MyBatis, SpringJdbcTemplate을 함께 쓴다.

JPQL - 객체 지향 SQL

가장 단순한 조회 방법
- EntityManager.find()
- 객체 그래프 탐색(a.getB().getC())
JPA를 사용하면 엔티티 중심으로 개발한다.
문제는 검색 쿼리
검색할 때도 테이블이 아닌 엔티티 객체를 대상으로 검색한다. - 객체 지향 쿼리
모든 DB 데이터를 객체로 변환해서 검색하는 것은 불가능하다.
애플리케이션이 필요한 데이터만 DB에서 불러오려면 결국 검색 조건(WHERE, GROUP BY)이 포함된 SQL이 필요하다.
JPA는 SQL을 추상화한 JPQL이라는 객체 지향 쿼리 언어 제공한다.
- SQL을 추상화해서 특정 데이터베이스 SQL에 의존하지 않는다.
SQL문법과 유사하며 SELECT, FROM, WHERE, GROUP BY, HACVING, JOIN 지원한다.
JPQL은 엔티티 객체를 대상으로 쿼리
SQL은 데이터베이스 테이블을 대상으로 쿼리

Ex)

엔티티 객체를 대상으로 쿼리를 실행한다.

<hide/>
package hellojpa;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.EntityTransaction;
import javax.persistence.Persistence;
public class JpaMain {
    public static void main(String[] args) {

        EntityManagerFactory emf = Persistence.createEntityManagerFactory("hello");
        EntityManager em = emf.createEntityManager();
        EntityTransaction tx = em.getTransaction();
        tx.begin();

        try{

            List<Member> result = em.createQuery(
                            " SELECT m FROM Member m WHERE m.userName LIKE '%kim%'",
                                Member.class
                            ).getResultList();

            for(Member member : result){
                System.out.println("member = " + member);
            }

            tx.commit();

        }catch (Exception e) {
            tx.rollback();
            e.printStackTrace();
        }finally {
            em.close();
        }
        emf.close();    // 팩토리를 나중에 닫는다.
    }
}

Note) 실행 결과

JPQL이 주석 안에 먼저 나온다.
그러고나서 실제 번역된 SQL이 나온다.

<hide/>
/*  SELECT
    m 
FROM
    Member m 
WHERE
    m.userName LIKE '%kim%' */ select
        member0_.MEMBER_ID as MEMBER_I1_4_,
        member0_.city as city2_4_,
        member0_.street as street3_4_,
        member0_.zipcode as zipcode4_4_,
        member0_.USERNAME as USERNAME5_4_ 
    from
        Member member0_ 
    where
        member0_.USERNAME like '%kim%'

Criteria

문자가 아닌 Java 코드로 JPQL을 작성 가능하다.
JPQL 빌더 역할을 한다.
자바 표준에서 제공한다. JPA 공식 기능
장점 - 동적 쿼리가 깔끔하게 나온다.
단점 - 너무 복잡하고 실용성이 없다. 표준에는 들어가지만 강사님은 실무에서 안 쓰신다. SQL 스럽지 않아서 디버깅을 못한다. => QueryDSL 사용을 권장

Ex) criteria

<hide/>
package hellojpa;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.EntityTransaction;
import javax.persistence.Persistence;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Root;
import org.hibernate.Criteria;

public class JpaMain {
    public static void main(String[] args) {

        EntityManagerFactory emf = Persistence.createEntityManagerFactory("hello");
        EntityManager em = emf.createEntityManager();
        EntityTransaction tx = em.getTransaction();
        tx.begin();

        try{
            //사용 준비
            CriteriaBuilder cb = em.getCriteriaBuilder();
            CriteriaQuery<Member> query = cb.createQuery(Member.class);

            Root<Member> m = query.from(Member.class);
            CriteriaQuery<Member> cq = query.select(m).where(cb.equal(m.get("userName"), "kim"));
            List<Member> resultList =  em.createQuery(cq).getResultList();
            tx.commit();

        }catch (Exception e) {
            tx.rollback();
            e.printStackTrace();
        }finally {
            em.close();
        }
        emf.close();    // 팩토리를 나중에 닫는다.
    }
}

Note) 실행 결과

<hide/>
/* select
    generatedAlias0 
from
    Member as generatedAlias0 
where
    generatedAlias0.userName=:param0 */ select
        member0_.MEMBER_ID as MEMBER_I1_4_,
        member0_.city as city2_4_,
        member0_.street as street3_4_,
        member0_.zipcode as zipcode4_4_,
        member0_.USERNAME as USERNAME5_4_ 
    from
        Member member0_ 
    where
        member0_.USERNAME=?

QueryDSL

문자가 아닌 자바 코드로 JPQL을 작성 가능
JPQL 빌더 역할을 한다.
컴파일 시점에 문법 오류를 찾을 수 있다.
동적 쿼리 작성이 편리하다.
단순하고 쉬워서 실무에서 사용하기를 권장한다.

Ex) QueryDSL

<hide/>
//JPQL 
 //select m from Member m where m.age > 18
JPAFactoryQuery query = new JPAQueryFactory(em);
 QMember m = QMember.member; 
 
 List<Member> list = query.selectFrom(m)
                     .where(m.age.gt(18)) 
                     .orderBy(m.name.desc())
                     .fetch();

네이티브 SQL

JPA가 제공하는 SQL을 직접 사용하는 기능
JPQL로 해결할 수 없는 특정 데이터베이스에 의존적인 기능
ex) Oracle CONNECT BY, 특정 DB만 사용하는 SQL 힌트

Ex) native SQL

createNaticeQuery() 이용

<hide/>
package hellojpa;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.EntityTransaction;
import javax.persistence.Persistence;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Root;
import org.hibernate.Criteria;

public class JpaMain {
    public static void main(String[] args) {

        EntityManagerFactory emf = Persistence.createEntityManagerFactory("hello");
        EntityManager em = emf.createEntityManager();
        EntityTransaction tx = em.getTransaction();
        tx.begin();

        try{
            em.createNativeQuery(" SELECT MEMBER_ID, city, street, zipcode, USERNAME FROM MEMBER").getResultList();
            tx.commit();

        }catch (Exception e) {
            tx.rollback();
            e.printStackTrace();
        }finally {
            em.close();
        }
        emf.close();    // 팩토리를 나중에 닫는다.
    }
}

Note) 실행 결과

<hide/>
/* dynamic native SQL query */  SELECT
    MEMBER_ID,
    city,
    street,
    zipcode,
    USERNAME 
FROM
    MEMBER

JDBC 직접 사용, SpringJdbcTemplate 등

JPA를 사용하면서 JDBC 커넥션을 직접 사용하거나 스프링 JdbcTemplate, MyBatis 등 함께 사용 가능하다
단, 영속성 컨텍스트를 적절한 시점에 강제로 flush() 해야한다.
ex) JPA를 우회해서 SQL을 실행하기 직전에 영속성 컨텍스트 수동으로 flush()

cf) flush() 자동 실행: commit, query 실행되는 경우

10.1 기본 문법과 쿼리 API

Ex)

새로운 프로젝트 생성

<hide/>
package jpql;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
@Entity
public class Member {

    @Id
    @GeneratedValue
    private Long id;

    private  String username;

    private int age;

    @ManyToOne
    @JoinColumn(name = "TEAM_ID")
    private Team team;


    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }
}

<hide/>
package jpql;
import java.util.ArrayList;
import java.util.List;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.OneToMany;
@Entity
public class Team {

    @Id
    @GeneratedValue
    private Long id;
    private String name;

    @OneToMany(mappedBy = "team")
    private List<Member> members = new ArrayList<>();

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}

<hide/>
package jpql;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.EntityTransaction;
import javax.persistence.Persistence;
public class JpaMain {
    public static void main(String[] args) {

        EntityManagerFactory emf = Persistence.createEntityManagerFactory("hello");
        EntityManager em = emf.createEntityManager();
        EntityTransaction tx = em.getTransaction();
        tx.begin();

        try{
            Member member = new Member();
            member.setUsername("member1");
            em.persist(member);
            tx.commit();

        }catch (Exception e) {
            tx.rollback();
            e.printStackTrace();
        }finally {
            em.close();
        }
        emf.close();    // 팩토리를 나중에 닫는다.
    }
}

Note) 실행 결과 - INSERT 쿼리 실행

<hide/>
/* insert jpql.Member
    */ insert 
    into
        Member
        (age, TEAM_ID, username, id) 
    values
        (?, ?, ?, ?)

Ex) JPQL

<hide/>
package jpql;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;

@Entity
public class Member {

    @Id
    @GeneratedValue
    private Long id;

    private  String username;

    private int age;

    @ManyToOne
    @JoinColumn(name = "TEAM_ID")
    private Team team;


    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }
}

<hide/>
package jpql;

import java.util.ArrayList;
import java.util.List;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.OneToMany;

@Entity
public class Team {

    @Id
    @GeneratedValue
    private Long id;
    private String name;

    @OneToMany(mappedBy = "team")
    private List<Member> members = new ArrayList<>();

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}

<hide/>
package jpql;

import javax.persistence.Embedded;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.Table;

@Entity
@Table(name = "ORDERS")
public  class Order {

    @Id @GeneratedValue
    private Long id;
    private int orderAmount;

    @Embedded
    private Address address;

    @ManyToOne
    @JoinColumn(name = "PRODUCT_ID")
    private Product product;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public int getOrderAmount() {
        return orderAmount;
    }

    public void setOrderAmount(int orderAmount) {
        this.orderAmount = orderAmount;
    }

    public Address getAddress() {
        return address;
    }

    public void setAddress(Address address) {
        this.address = address;
    }

    public Product getProduct() {
        return product;
    }

    public void setProduct(Product product) {
        this.product = product;
    }
}

address

<hide/>
package jpql;
import javax.persistence.Embeddable;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;

@Embeddable
public class Address {
    private String city;
    private String street;
    private String zipcode;
}

<hide/>
package jpql;
import javax.persistence.Embeddable;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;

@Entity
public class Product {
    @Id @GeneratedValue
    private Long id;
    private String name;
    private int price;
}

<hide/>
package jpql;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.EntityTransaction;
import javax.persistence.Persistence;

public class JpaMain {
    public static void main(String[] args) {

        EntityManagerFactory emf = Persistence.createEntityManagerFactory("hello");
        EntityManager em = emf.createEntityManager();
        EntityTransaction tx = em.getTransaction();
        tx.begin();

        try{
            Member member = new Member();
            member.setUsername("member1");
            em.persist(member);
            tx.commit();

        }catch (Exception e) {
            tx.rollback();
            e.printStackTrace();
        }finally {
            em.close();
        }
        emf.close();    // 팩토리를 나중에 닫는다.
    }
}

Note) 실행 결과

ORDERS에 값이 잘 들어간다.

<hide/>
Hibernate: 
    
    alter table ORDERS 
       add constraint FKtlx3qxs8vwir2b80i3oumx2qm 
       foreign key (PRODUCT_ID) 
       references Product
Hibernate: 
    call next value for hibernate_sequence
Hibernate: 
    /* insert jpql.Member
        */ insert 
        into
            Member
            (age, TEAM_ID, username, id) 
        values
            (?, ?, ?, ?)

JPQL 문법

ex) SELECT m FROM Member AS m WHERE m.age > 18 ;
엔티티와 속성: 대소문자 구분 O ex) Member, Age
JPQL 키워드: 대소문자 구분 X ex) SELECT, FROM, WHERE
엔티티 이름 사용, 테이블 이름이 아니다.(Member)
별칭(Alias)은 필수이다.
집합과 정렬
- COUNT(), SUM(), AVG(), MAX(), MIN()
- GROUP BY... HAVING, ORDER BY

TypeQuery, Query

TypeQuery: 반환 타입이 명확할 때 (한 종류), 사용한다.
Query: 반환 타입이 명확하지 않을 때(여러 종류) 사용한다.

Ex) TypeQuery , Query

<hide/>
package jpql;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.EntityTransaction;
import javax.persistence.Persistence;
import javax.persistence.Query;
import javax.persistence.TypedQuery;

public class JpaMain {
    public static void main(String[] args) {

        EntityManagerFactory emf = Persistence.createEntityManagerFactory("hello");
        EntityManager em = emf.createEntityManager();
        EntityTransaction tx = em.getTransaction();
        tx.begin();

        try{
            Member member = new Member();
            member.setUsername("member1");
            member.setAge(10);
            em.persist(member);

            TypedQuery<Member> query1 = em.createQuery("SELECT m FROM Member m", Member.class);
            TypedQuery<String> query2 = em.createQuery("SELECT m.username FROM Member m", String.class);
            Query query3 = em.createQuery("SELECT m.username, m.age FROM Member m");
            tx.commit();

        }catch (Exception e) {
            tx.rollback();
            e.printStackTrace();
        }finally {
            em.close();
        }
        emf.close();    // 팩토리를 나중에 닫는다.
    }
}

결과 조회 API

query.getResultList(): 결과가 하나 이상일 때, 리스트 반환
- 결과가 없으면 빈 리스트를 반환한다. (~~NullPointerException~~)
query.getSingleResult(): 결과가 정확히 하나, 단일 객체를 반환한다. 결과가 있는 게 보장될 때, 쓴다.
- 결과가 없으면? NoResultException
- 둘 이상인 경우는? NonUniqueResultException
  - 결과가 있는데 예외가 터지면? => 예외 처리를 해야한다. - 논란 많음
  - 이와 다르게, Spring DATA JPA => 하나만 가져오는 함수들에 대해 결과 없으면 Null /Optional 반환하거나 예외 안 터뜨린다.

Ex) 결과가 없을 때

<hide/>
package jpql;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.EntityTransaction;
import javax.persistence.Persistence;
import javax.persistence.Query;
import javax.persistence.TypedQuery;

public class JpaMain {
    public static void main(String[] args) {

        EntityManagerFactory emf = Persistence.createEntityManagerFactory("hello");
        EntityManager em = emf.createEntityManager();
        EntityTransaction tx = em.getTransaction();
        tx.begin();

        try{

            TypedQuery<Member> query = em.createQuery("SELECT m FROM Member m", Member.class);
            Member result = query.getSingleResult();
            System.out.println("result = " + result);
            tx.commit();

        }catch (Exception e) {
            tx.rollback();
            e.printStackTrace();
        }finally {
            em.close();
        }
        emf.close();    // 팩토리를 나중에 닫는다.
    }
}

Note) 실행 결과 - NoResultException

<hide/>
javax.persistence.NoResultException: No entity found for query
	at org.hibernate.query.internal.AbstractProducedQuery.getSingleResult(AbstractProducedQuery.java:1555)
	at jpql.JpaMain.main(JpaMain.java:20)

JPQL의 파라미터 바인딩

이름 기준 - 파라미터를 이름으로 구분하는 방법, 명확하다.
위치 기준 - 웬만하면 쓰지 말 것 (위치 바꾸면 버그 생긴다.)

Ex) 파라미터 바인딩 - 이름 기준

파라미터 앞에 ":" 콜론을 붙인다.

<hide/>
package jpql;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.EntityTransaction;
import javax.persistence.Persistence;
import javax.persistence.Query;
import javax.persistence.TypedQuery;

public class JpaMain {
    public static void main(String[] args) {

        EntityManagerFactory emf = Persistence.createEntityManagerFactory("hello");
        EntityManager em = emf.createEntityManager();
        EntityTransaction tx = em.getTransaction();
        tx.begin();

        try{
            Member member = new Member();
            member.setUsername("member1");
            member.setAge(10);
            em.persist(member);

            TypedQuery<Member> query = em.createQuery("SELECT m FROM Member m WHERE m.username = :username", Member.class);
            query.setParameter("username", "member1");
            Member singleResult = query.getSingleResult();
            System.out.println("SingleResult = " + singleResult.getUsername());
            tx.commit();

        }catch (Exception e) {
            tx.rollback();
            e.printStackTrace();
        }finally {
            em.close();
        }
        emf.close();    // 팩토리를 나중에 닫는다.
    }
}

Note) 실행 결과

물음표로 바인딩된다.

<hide/>
  /* SELECT
        m 
    FROM
        Member m 
    WHERE
        m.username = :username */ select
            member0_.id as id1_0_,
            member0_.age as age2_0_,
            member0_.TEAM_ID as TEAM_ID4_0_,
            member0_.username as username3_0_ 
        from
            Member member0_ 
        where
            member0_.username=?
SingleResult = member1

10.2 프로젝션 (SELECT)

프로젝션

SELECT 절에 조회할 대상을 지정하는 것
프로젝션 대상: 엔티티, 임베디드 타입, 스칼라 타입(숫자, 문자 등 기본 데이터 타입)
SELECT m FROM Member m => 엔티티 프로젝션
SELECT m.team FROM Member m => 엔티티 프로젝션
SELECT m.address FROM Member m => 임베디드 타입 프로젝션
SELECT m.username, m.age FROM Member m => 스칼라 타입 프로젝션
DISTINCT로 중복 제거 가능

Ex) 엔티티 프로젝션

<hide/>
package jpql;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.EntityTransaction;
import javax.persistence.Persistence;
import javax.persistence.Query;
import javax.persistence.TypedQuery;

public class JpaMain {
    public static void main(String[] args) {

        EntityManagerFactory emf = Persistence.createEntityManagerFactory("hello");
        EntityManager em = emf.createEntityManager();
        EntityTransaction tx = em.getTransaction();
        tx.begin();

        try{
            Member member = new Member();
            member.setUsername("member1");
            member.setAge(10);
            em.persist(member);

            List<Member> result = em.createQuery("SELECT m FROM Member m ").getResultList();
            Member findMember = result.get(0);
            findMember.setAge(20);
            tx.commit();

        }catch (Exception e) {
            tx.rollback();
            e.printStackTrace();
        }finally {
            em.close();
        }
        emf.close();    // 팩토리를 나중에 닫는다.
    }
}

Note) 실행 결과 - UPDATE 쿼리가 나간다.

엔티티 프로젝션을 하면 SELECT, .... 대상이 여러 개지만 영속성 컨텍스트에서 모두 관리된다.
그래서 persist()한 다음이더라도 수정한 내용이 반영된다.

<hide/>
/* update
    jpql.Member */ update
        Member 
    set
        age=?,
        TEAM_ID=?,
        username=? 
    where
        id=?

Ex) 엔티티 프로젝션 - 묵시적 조인 (추천하지 않는 방법)

<hide/>
package jpql;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.EntityTransaction;
import javax.persistence.Persistence;
import javax.persistence.Query;
import javax.persistence.TypedQuery;

public class JpaMain {
    public static void main(String[] args) {

        EntityManagerFactory emf = Persistence.createEntityManagerFactory("hello");
        EntityManager em = emf.createEntityManager();
        EntityTransaction tx = em.getTransaction();
        tx.begin();

        try{
            Member member = new Member();
            member.setUsername("member1");
            member.setAge(10);
            em.persist(member);
            List<Team> result = em.createQuery("SELECT m.team FROM Member m ", Team.class).getResultList();
            tx.commit();

        }catch (Exception e) {
            tx.rollback();
            e.printStackTrace();
        }finally {
            em.close();
        }
        emf.close();    // 팩토리를 나중에 닫는다.
    }
}

Note) 실행 결과

팀과 inner JOIN

<hide/>
/* SELECT
        m.team 
    FROM
        Member m  */ select
            team1_.id as id1_3_,
            team1_.name as name2_3_ 
        from
            Member member0_ 
        inner join
            Team team1_ 
                on member0_.TEAM_ID=team1_.id

위 쿼리는 내가 JOIN 하지 않았지만 결과에 JOIN 으로 나오고 있다.
그러나, 내 쿼리를 SQL과 비슷하게 작성하는 게 더 좋은 방법이다.
엔티티 프로젝션 - 명시적 JOIN (추천)

<hide/>
package jpql;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.EntityTransaction;
import javax.persistence.Persistence;
import javax.persistence.Query;
import javax.persistence.TypedQuery;

public class JpaMain {
    public static void main(String[] args) {

        EntityManagerFactory emf = Persistence.createEntityManagerFactory("hello");
        EntityManager em = emf.createEntityManager();
        EntityTransaction tx = em.getTransaction();
        tx.begin();

        try{
            Member member = new Member();
            member.setUsername("member1");
            member.setAge(10);
            em.persist(member);
            List<Team> result = em.createQuery("SELECT t FROM Member m JOIN m.team t ", Team.class).getResultList();
            tx.commit();

        }catch (Exception e) {
            tx.rollback();
            e.printStackTrace();
        }finally {
            em.close();
        }
        emf.close();    // 팩토리를 나중에 닫는다.
    }
}

Note) 실행 결과 - 결과는 같다.

qlString()에 "join"을 명시해줘야 예측하기 쉽기 때문에 이런 식으로 작성하는 것이 더 좋다.

<hide/>
 /* SELECT
        t 
    FROM
        Member m 
    JOIN
        m.team t  */ select
            team1_.id as id1_3_,
            team1_.name as name2_3_ 
        from
            Member member0_ 
        inner join
            Team team1_ 
                on member0_.TEAM_ID=team1_.id

cf) qlString()에 넣는 값은 꼭 별칭이 필요하다. address 같은 값은 어딘가 엔티티에 소속되어 있기 때문이다. 어디 소속인지 알려줘야한다. => 값 타입의 한계

Ex) 임베디드 파입 프로젝션

<hide/>
package jpql;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.EntityTransaction;
import javax.persistence.Persistence;
import javax.persistence.Query;
import javax.persistence.TypedQuery;

public class JpaMain {
    public static void main(String[] args) {

        EntityManagerFactory emf = Persistence.createEntityManagerFactory("hello");
        EntityManager em = emf.createEntityManager();
        EntityTransaction tx = em.getTransaction();
        tx.begin();

        try{
            Member member = new Member();
            member.setUsername("member1");
            member.setAge(10);
            em.persist(member);
            em.createQuery("SELECT o.address FROM Order o ", Address.class).getResultList();
            tx.commit();

        }catch (Exception e) {
            tx.rollback();
            e.printStackTrace();
        }finally {
            em.close();
        }
        emf.close();    // 팩토리를 나중에 닫는다.
    }
}

Note) 실행 결과

<hide/>
/* SELECT
    o.address 
FROM

Order o  */ select
    order0_.city as col_0_0_,
    order0_.street as col_0_1_,
    order0_.zipcode as col_0_2_ from
        ORDERS order0_

Ex) 스칼라 타입 프로젝션

나이와 name만 조회한다.

<hide/>
package jpql;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.EntityTransaction;
import javax.persistence.Persistence;
import javax.persistence.Query;
import javax.persistence.TypedQuery;

public class JpaMain {
    public static void main(String[] args) {

        EntityManagerFactory emf = Persistence.createEntityManagerFactory("hello");
        EntityManager em = emf.createEntityManager();
        EntityTransaction tx = em.getTransaction();
        tx.begin();

        try{
            Member member = new Member();
            member.setUsername("member1");
            member.setAge(10);
            em.persist(member);
            em.flush();
            em.clear();
            em.createQuery("SELECT DISTINCT m.username, m.age FROM Member m").getResultList();
            tx.commit();

        }catch (Exception e) {
            tx.rollback();
            e.printStackTrace();
        }finally {
            em.close();
        }
        emf.close();    // 팩토리를 나중에 닫는다.
    }
}

Note) 실행 결과

<hide/>
/* SELECT
    DISTINCT m.username,
    m.age 
FROM
    Member m */ select
        distinct member0_.username as col_0_0_,
        member0_.age as col_1_0_ 
    from
        Member member0_

프로젝션 - 여러 종류의 값 조회

SELECT m.username, m.age FROM Member m

Query 타입으로 조회
Object[] 타입으로 조회
new 명령어로 조회
- 단순 값을 DTO로 바로 조회
- 패키지 명을 포함한 전체 클래스명 입력
- 순서와 타입이 일치하는 생성자가 필요하다.

Ex 1) Query 타입으로 조회

타입을 명시하지 못하니까 Object로 넘긴다.

<hide/>
package jpql;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.EntityTransaction;
import javax.persistence.Persistence;
import javax.persistence.Query;
import javax.persistence.TypedQuery;

public class JpaMain {
    public static void main(String[] args) {

        EntityManagerFactory emf = Persistence.createEntityManagerFactory("hello");
        EntityManager em = emf.createEntityManager();
        EntityTransaction tx = em.getTransaction();
        tx.begin();

        try{
            Member member = new Member();
            member.setUsername("member1");
            member.setAge(10);
            em.persist(member);
            em.flush();
            em.clear();

            List resultList = em.createQuery("SELECT DISTINCT m.username, m.age FROM Member m").getResultList();
            Object o = resultList.get(0);
            Object[] result = (Object[]) o;
            System.out.println("username = " + result[0]);
            System.out.println("age = " + result[1]);
            tx.commit();

        }catch (Exception e) {
            tx.rollback();
            e.printStackTrace();
        }finally {
            em.close();
        }
        emf.close();    // 팩토리를 나중에 닫는다.
    }
}

Note) 실행 결과

<hide/>
   /* SELECT
        DISTINCT m.username,
        m.age 
    FROM
        Member m */ select
            distinct member0_.username as col_0_0_,
            member0_.age as col_1_0_ 
        from
            Member member0_
username = member1
age = 10

Ex 2) Object[] 타입으로 조회

<hide/>
package jpql;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.EntityTransaction;
import javax.persistence.Persistence;
import javax.persistence.Query;
import javax.persistence.TypedQuery;

public class JpaMain {
    public static void main(String[] args) {

        EntityManagerFactory emf = Persistence.createEntityManagerFactory("hello");
        EntityManager em = emf.createEntityManager();
        EntityTransaction tx = em.getTransaction();
        tx.begin();

        try{
            Member member = new Member();
            member.setUsername("member1");
            member.setAge(10);
            em.persist(member);
            em.flush();
            em.clear();

            List<Object[]> resultList = em.createQuery("SELECT DISTINCT m.username, m.age FROM Member m").getResultList();
            Object[] result = resultList.get(0);
            System.out.println("username = " + result[0]);
            System.out.println("age = " + result[1]);
            tx.commit();

        }catch (Exception e) {
            tx.rollback();
            e.printStackTrace();
        }finally {
            em.close();
        }
        emf.close();    // 팩토리를 나중에 닫는다.
    }
}

Note) 실행 결과

<hide/>
 /* SELECT
        DISTINCT m.username,
        m.age 
    FROM
        Member m */ select
            distinct member0_.username as col_0_0_,
            member0_.age as col_1_0_ 
        from
            Member member0_
username = member1
age = 10

Ex 3) new 명령어로 조회 - 깔끔한 방법

엔티티가 아닌 다른 객체인 경우 ex) MemberDto
MemberDto의 생성자를 호출하듯이 쿼리를 짠다.

<hide/>
package jpql;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.EntityTransaction;
import javax.persistence.Persistence;
import javax.persistence.Query;
import javax.persistence.TypedQuery;

public class JpaMain {
    public static void main(String[] args) {

        EntityManagerFactory emf = Persistence.createEntityManagerFactory("hello");
        EntityManager em = emf.createEntityManager();
        EntityTransaction tx = em.getTransaction();
        tx.begin();

        try{
            Member member = new Member();
            member.setUsername("member1");
            member.setAge(10);
            em.persist(member);
            em.flush();
            em.clear();

            List<MemberDto> result = em.createQuery("SELECT new jpql.MemberDto(m.username, m.age) FROM Member m", MemberDto.class).getResultList();
            MemberDto memberDto = result.get(0);
            System.out.println("memberDto = " + memberDto.getUsername());
            System.out.println("memberDto = " + memberDto.getAge());
            tx.commit();

        }catch (Exception e) {
            tx.rollback();
            e.printStackTrace();
        }finally {
            em.close();
        }
        emf.close();    // 팩토리를 나중에 닫는다.
    }
}

Note) 실행 결과

깔끔하지만 패키지 이름을 다 적어줘야한다는 단점이 있다.

<hide/>
  /* SELECT
        new jpql.MemberDto(m.username,
        m.age) 
    FROM
        Member m */ select
            member0_.username as col_0_0_,
            member0_.age as col_1_0_ 
        from
            Member member0_
memberDto = member1
memberDto = 10

10.3 페이징 (paging)

페이징 API

JPA는 페이징을 다음 두 API로 추상화한다.
- SetFirstResult(int startPosition): 조회 시작 위치 (0부터 시작)
- setMaxResults(int maxResult): 조회한 데이터 수

Ex) 페이지 API

<hide/>
package jpql;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.EntityTransaction;
import javax.persistence.Persistence;
import javax.persistence.Query;
import javax.persistence.TypedQuery;

public class JpaMain {
    public static void main(String[] args) {

        EntityManagerFactory emf = Persistence.createEntityManagerFactory("hello");
        EntityManager em = emf.createEntityManager();
        EntityTransaction tx = em.getTransaction();
        tx.begin();

        try{
            Member member = new Member();
            member.setUsername("member1");
            member.setAge(10);
            em.persist(member);
            em.flush();
            em.clear();

            List<Member> result =  em.createQuery("SELECT m FROM Member m ORDER BY m.age DESC", Member.class)
                    .setFirstResult(1)  // 시작 위치
                        .setMaxResults(10)  // 10개
                            .getResultList();
            System.out.println("result.size = " + result.size());
            
            for (Member member1 : result) {
                System.out.println("member1 = " + member1.toString());
            }
            tx.commit();
            
        }catch (Exception e) {
            tx.rollback();
            e.printStackTrace();
        }finally {
            em.close();
        }
        emf.close();    // 팩토리를 나중에 닫는다.
    }
}

Note) 실행 결과 - LIMIT ? OFFSET ? 추가

<hide/>
  /* SELECT
        m 
    FROM
        Member m 
    ORDER BY
        m.age DESC */ select
            member0_.id as id1_0_,
            member0_.age as age2_0_,
            member0_.TEAM_ID as TEAM_ID4_0_,
            member0_.username as username3_0_ 
        from
            Member member0_ 
        order by
            member0_.age DESC limit ? offset ?
result.size = 0

Ex) for loop

<hide/>
package jpql;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.EntityTransaction;
import javax.persistence.Persistence;
import javax.persistence.Query;
import javax.persistence.TypedQuery;

public class JpaMain {
    public static void main(String[] args) {

        EntityManagerFactory emf = Persistence.createEntityManagerFactory("hello");
        EntityManager em = emf.createEntityManager();
        EntityTransaction tx = em.getTransaction();
        tx.begin();

        try{
            for(int i = 0; i < 100; ++i){
                Member member = new Member();
                member.setUsername("member " + i);
                member.setAge(i);
                em.persist(member);
            }

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

            List<Member> result =  em.createQuery("SELECT m FROM Member m ORDER BY m.age DESC", Member.class)
                    .setFirstResult(1)  // 시작 위치
                        .setMaxResults(10)  // 10개
                            .getResultList();
            System.out.println("result.size = " + result.size());

            for (Member member1 : result) {
                System.out.println("member1 = " + member1.toString());
            }
            tx.commit();

        }catch (Exception e) {
            tx.rollback();
            e.printStackTrace();
        }finally {
            em.close();
        }
        emf.close();    // 팩토리를 나중에 닫는다.
    }
}

Note) 실행 결과 - H2

나이 많은 것부터 나온다. 내림차순

<hide/>
    /* SELECT
        m 
    FROM
        Member m 
    ORDER BY
        m.age DESC */ select
            member0_.id as id1_0_,
            member0_.age as age2_0_,
            member0_.TEAM_ID as TEAM_ID4_0_,
            member0_.username as username3_0_ 
        from
            Member member0_ 
        order by
            member0_.age DESC limit ? offset ?
result.size = 10
member1 = Member{id=99, username='member 98', age=98}
member1 = Member{id=98, username='member 97', age=97}
member1 = Member{id=97, username='member 96', age=96}
member1 = Member{id=96, username='member 95', age=95}
member1 = Member{id=95, username='member 94', age=94}
member1 = Member{id=94, username='member 93', age=93}
member1 = Member{id=93, username='member 92', age=92}
member1 = Member{id=92, username='member 91', age=91}
member1 = Member{id=91, username='member 90', age=90}
member1 = Member{id=90, username='member 89', age=89}

Note) 실행 결과 - Oracle12cDialect 로 설정한 실행 결과

SELECT가 세 번 들어간다.
rownum에 대한 식이 나온다.

<hide/>
   /* SELECT
        m 
    FROM
        Member m 
    ORDER BY
        m.age DESC */ select
            * 
        from
            ( select
                row_.*,
                rownum rownum_ 
            from
                ( select
                    member0_.id as id1_0_,
                    member0_.age as age2_0_,
                    member0_.TEAM_ID as TEAM_ID4_0_,
                    member0_.username as username3_0_ 
                from
                    Member member0_ 
                order by
                    member0_.age DESC ) row_ 
            where
                rownum <= ?
            ) 
        where
            rownum_ > ?
result.size = 10
member1 = Member{id=99, username='member 98', age=98}
member1 = Member{id=98, username='member 97', age=97}
member1 = Member{id=97, username='member 96', age=96}
member1 = Member{id=96, username='member 95', age=95}
member1 = Member{id=95, username='member 94', age=94}
member1 = Member{id=94, username='member 93', age=93}
member1 = Member{id=93, username='member 92', age=92}
member1 = Member{id=92, username='member 91', age=91}
member1 = Member{id=91, username='member 90', age=90}
member1 = Member{id=90, username='member 89', age=89}

Note) SQLServer2012 으로 설정한 경우

<hide/>
   /* SELECT
        m 
    FROM
        Member m 
    ORDER BY
        m.age DESC */ select
            member0_.id as id1_0_,
            member0_.age as age2_0_,
            member0_.TEAM_ID as TEAM_ID4_0_,
            member0_.username as username3_0_ 
        from
            Member member0_ 
        order by
            member0_.age DESC offset ? rows fetch next ? rows only
result.size = 10
member1 = Member{id=99, username='member 98', age=98}
member1 = Member{id=98, username='member 97', age=97}
member1 = Member{id=97, username='member 96', age=96}
member1 = Member{id=96, username='member 95', age=95}
member1 = Member{id=95, username='member 94', age=94}
member1 = Member{id=94, username='member 93', age=93}
member1 = Member{id=93, username='member 92', age=92}
member1 = Member{id=92, username='member 91', age=91}
member1 = Member{id=91, username='member 90', age=90}
member1 = Member{id=90, username='member 89', age=89}

10.4 조인(Join)

JOIN

내부 조인: SELECT m FROM Member m [INNER] JOIN m.team t
- 교집합 느낌
외부 조인: SELECT m FROM Member m LEFT [OUTER] JOIN m.team t
- 합집합 느낌
세타 조인: SELECT COUNT(m) FROM Member m, Team t WHERE m.username = t.name - 카르테시안 (Cartesian Product, 카티션 프로덕트, CROSS JOIN)

Ex)

멤버 클래스에 연관 관계 편의 메서드 ?>??????

<hide/>
package jpql;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;

@Entity
public class Member {

    @Id
    @GeneratedValue
    private Long id;

    private  String username;

    private int age;

    @ManyToOne
    @JoinColumn(name = "TEAM_ID")
    private Team team;

    // 연관관계 편의 메서드
    public  void changeTeam(Team team){
        this.team = team;
        team.getMembers().add(this);    // ?????
    }

    @Override
    public String toString() {
        return "Member{" +
            "id=" + id +
            ", username='" + username + '\'' +
            ", age=" + age +
//            ", team=" + team +
            '}';
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public Team getTeam() {
        return team;
    }

    public void setTeam(Team team) {
        this.team = team;
    }
}

<hide/>
package jpql;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.EntityTransaction;
import javax.persistence.Persistence;
import javax.persistence.Query;
import javax.persistence.TypedQuery;

public class JpaMain {
    public static void main(String[] args) {

        EntityManagerFactory emf = Persistence.createEntityManagerFactory("hello");
        EntityManager em = emf.createEntityManager();
        EntityTransaction tx = em.getTransaction();
        tx.begin();

        try{
            Team team = new Team();
            team.setName("teamA");
            em.persist(team);
            Member member = new Member();
            member.setUsername("member ");
            member.setAge(10);
            member.setTeam(team);
            em.persist(member);
            em.flush();
            em.clear();
            List<Member> result =  em.createQuery("SELECT m FROM Member m INNER JOIN m.team t", Member.class)
                                    .getResultList();
            tx.commit();

        }catch (Exception e) {
            tx.rollback();
            e.printStackTrace();
        }finally {
            em.close();
        }
        emf.close();    // 팩토리를 나중에 닫는다.
    }
}

Note) 실행 결과

멤버와 팀이 inner join
그런데 아래의 SELECT 쿼리는 왜 실행될까?
- Member클래스의 team에 달린 ManyToOne의기본값이 EAGER 이기 때문이다.
- 따라서 이를 LAZY로 바꿔야한다
- 그럼 inner join 아래의 select 쿼리가 사라진다.

<hide/>
    /* SELECT
        m 
    FROM
        Member m 
    INNER JOIN
        m.team t */ select
            member0_.id as id1_0_,
            member0_.age as age2_0_,
            member0_.TEAM_ID as TEAM_ID4_0_,
            member0_.username as username3_0_ 
        from
            Member member0_ 
        inner join
            Team team1_ 
                on member0_.TEAM_ID=team1_.id
Hibernate: 
    select
        team0_.id as id1_3_0_,
        team0_.name as name2_3_0_ 
    from
        Team team0_ 
    where
        team0_.id=?

Ex) LEFT JOIN

<hide/>
package jpql;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.EntityTransaction;
import javax.persistence.Persistence;
import javax.persistence.Query;
import javax.persistence.TypedQuery;

public class JpaMain {
    public static void main(String[] args) {

        EntityManagerFactory emf = Persistence.createEntityManagerFactory("hello");
        EntityManager em = emf.createEntityManager();
        EntityTransaction tx = em.getTransaction();
        tx.begin();

        try{
            Team team = new Team();
            team.setName("teamA");
            em.persist(team);
            Member member = new Member();
            member.setUsername("member ");
            member.setAge(10);
            member.setTeam(team);
            em.persist(member);
            em.flush();
            em.clear();
            List<Member> result =  em.createQuery("SELECT m FROM Member m LEFT JOIN m.team t", Member.class)
                                    .getResultList();
            tx.commit();

        }catch (Exception e) {
            tx.rollback();
            e.printStackTrace();
        }finally {
            em.close();
        }
        emf.close();    // 팩토리를 나중에 닫는다.
    }
}

Note) 실행 결과

<hide/>
/* SELECT
    m 
FROM
    Member m 
LEFT JOIN
    m.team t */ select
        member0_.id as id1_0_,
        member0_.age as age2_0_,
        member0_.TEAM_ID as TEAM_ID4_0_,
        member0_.username as username3_0_ 
    from
        Member member0_ 
    left outer join
        Team team1_ 
            on member0_.TEAM_ID=team1_.id

Ex) 세타 조인 (CROSS JOIN)

<hide/>
package jpql;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.EntityTransaction;
import javax.persistence.Persistence;
import javax.persistence.Query;
import javax.persistence.TypedQuery;
public class JpaMain {
    public static void main(String[] args) {
        EntityManagerFactory emf = Persistence.createEntityManagerFactory("hello");
        EntityManager em = emf.createEntityManager();
        EntityTransaction tx = em.getTransaction();
        tx.begin();

        try{
            Team team = new Team();
            team.setName("teamA");
            em.persist(team);
            Member member = new Member();
            member.setUsername("member ");
            member.setAge(10);
            member.setTeam(team);
            em.persist(member);
            em.flush();
            em.clear();
            List<Member> result =  em.createQuery("SELECT m FROM Member m, Team t WHERE m.username = t.name", Member.class)
                                    .getResultList();
            tx.commit();

        }catch (Exception e) {
            tx.rollback();
            e.printStackTrace();
        }finally {
            em.close();
        }
        emf.close();    // 팩토리를 나중에 닫는다.
    }
}

Note) 실행 결과 - CROSS JOIN

<hide/>
/* SELECT
    m 
FROM
    Member m,
    Team t 
WHERE
    m.username = t.name */ select
        member0_.id as id1_0_,
        member0_.age as age2_0_,
        member0_.TEAM_ID as TEAM_ID4_0_,
        member0_.username as username3_0_ 
    from
        Member member0_ cross 
    join
        Team team1_ 
    where
        member0_.username=team1_.name

Ex) 세타 조인

member의 이름이 teamA 라면?

<hide/>
package jpql;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.EntityTransaction;
import javax.persistence.Persistence;
import javax.persistence.Query;
import javax.persistence.TypedQuery;

public class JpaMain {
    public static void main(String[] args) {

        EntityManagerFactory emf = Persistence.createEntityManagerFactory("hello");
        EntityManager em = emf.createEntityManager();
        EntityTransaction tx = em.getTransaction();
        tx.begin();

        try{
            Team team = new Team();
            team.setName("teamA");
            em.persist(team);
            Member member = new Member();
            member.setUsername("teamA");
            member.setAge(10);
            member.setTeam(team);
            em.persist(member);
            em.flush();
            em.clear();
            List<Member> result =  em.createQuery("SELECT m FROM Member m, Team t WHERE m.username = t.name", Member.class)
                                    .getResultList();
            System.out.println("result.size : " + result.size());
            tx.commit();

        }catch (Exception e) {
            tx.rollback();
            e.printStackTrace();
        }finally {
            em.close();
        }
        emf.close();    // 팩토리를 나중에 닫는다.
    }
}

Note) 실행 결과 - 관련된 멤버 수 1이 나온다.

<hide/>
    /* SELECT
        m 
    FROM
        Member m,
        Team t 
    WHERE
        m.username = t.name */ select
            member0_.id as id1_0_,
            member0_.age as age2_0_,
            member0_.TEAM_ID as TEAM_ID4_0_,
            member0_.username as username3_0_ 
        from
            Member member0_ cross 
        join
            Team team1_ 
        where
            member0_.username=team1_.name
result.size : 1

JOIN - ON절 활용한 조인

조인 대상 미리 필터링
연관 관계 없는 엔티티 외부 조인 ex) 세타 조인과 비슷하다

1) 조인 대상 필터링

ex) 회원과 팀을 조인하면서 팀 이름이 A인 팀만 조인
JPQL
- SELECT m, t FROM Member m LEFT JOIN m.team t ON t.name = 'A'
SQL
- SELECT m.*, t.* FROM Member m LEFT JOIN Team t ON m.TEAM_ID = t.id AND t.name = 'A'

2) 연관 관계 없는 엔티티 외부 조인

ex) 회원의 이름과 팀의 이름이 같은 대상 외부 조인
JPQL
- SELECT m, t FROM Member m LEFT JOIN Team t ON m.username = t.name
SQL
- SELECT m.* t.* FROM Member m LEFT JOIN Team t ON m.username = t.name

Ex 1) 조인 대상 필터링

<hide/>
package jpql;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.EntityTransaction;
import javax.persistence.Persistence;
import javax.persistence.Query;
import javax.persistence.TypedQuery;

public class JpaMain {
    public static void main(String[] args) {

        EntityManagerFactory emf = Persistence.createEntityManagerFactory("hello");
        EntityManager em = emf.createEntityManager();
        EntityTransaction tx = em.getTransaction();
        tx.begin();

        try{
            Team team = new Team();
            team.setName("teamA");
            em.persist(team);
            Member member = new Member();
            member.setUsername("teamA");
            member.setAge(10);
            member.setTeam(team);
            em.persist(member);
            em.flush();
            em.clear();

            String query = "SELECT m FROM Member m LEFT JOIN m.team t ON t.name = 'teamA'";
            List<Member> result =  em.createQuery(query, Member.class)
                                    .getResultList();
            System.out.println("result.size : " + result.size());
            tx.commit();

        }catch (Exception e) {
            tx.rollback();
            e.printStackTrace();
        }finally {
            em.close();
        }
        emf.close();    // 팩토리를 나중에 닫는다.
    }
}

Note) 실행 결과

left join 하고
추가로 AND해서 조인 조건문 안에 팀을 필터링하는 내용이 추가된다.

<hide/>
Hibernate: 
    /* SELECT
        m 
    FROM
        Member m 
    LEFT JOIN
        m.team t 
            ON t.name = 'teamA' */ select
                member0_.id as id1_0_,
                member0_.age as age2_0_,
                member0_.TEAM_ID as TEAM_ID4_0_,
                member0_.username as username3_0_ 
        from
            Member member0_ 
        left outer join
            Team team1_ 
                on member0_.TEAM_ID=team1_.id 
                and (
                    team1_.name='teamA'
                )
result.size : 1

Ex 2) 연관 관계 없는 엔티티 외부 조인

<hide/>
package jpql;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.EntityTransaction;
import javax.persistence.Persistence;
import javax.persistence.Query;
import javax.persistence.TypedQuery;

public class JpaMain {
    public static void main(String[] args) {

        EntityManagerFactory emf = Persistence.createEntityManagerFactory("hello");
        EntityManager em = emf.createEntityManager();
        EntityTransaction tx = em.getTransaction();
        tx.begin();

        try{
            Team team = new Team();
            team.setName("teamA");
            em.persist(team);
            Member member = new Member();
            member.setUsername("teamA");
            member.setAge(10);
            member.setTeam(team);
            em.persist(member);
            em.flush();
            em.clear();

            String query = "SELECT m FROM Member m LEFT JOIN Team t ON m.username = t.name";
            List<Member> result =  em.createQuery(query, Member.class)
                                    .getResultList();
            System.out.println("result.size : " + result.size());
            tx.commit();

        }catch (Exception e) {
            tx.rollback();
            e.printStackTrace();
        }finally {
            em.close();
        }
        emf.close();    // 팩토리를 나중에 닫는다.
    }
}

Note) 실행 결과

left join
on 절을 보면 id가 없어지고 name만 비교하는 내용이 들어간다.

<hide/>
    /* SELECT
        m 
    FROM
        Member m 
    LEFT JOIN
        Team t 
            ON m.username = t.name */ select
                member0_.id as id1_0_,
                member0_.age as age2_0_,
                member0_.TEAM_ID as TEAM_ID4_0_,
                member0_.username as username3_0_ 
        from
            Member member0_ 
        left outer join
            Team team1_ 
                on (
                    member0_.username=team1_.name
                )
result.size : 1

10.5 서브 쿼리(SubQuery)

서브 쿼리(SubQuery)

ex)
나이가 평균보다 많은회원
- SELECT m FROM Member m
- WHERE m.age > (
  - SELECT AVG(m2.age) FROM Member m2)
- 메인과 서브가 전혀 관련 없어야 성능이 잘 나온다.
한 개 이상이라도 주문한 고객
- SELECT m FROM Member m
- WHERE (
  - SELECT COUNT(o) FROM Order o WHERE m = o.member) > 0

서브 쿼리 지원 함수

[NOT] EXISTS (subquery): 서브 쿼리에 결과가 존재하면 참이다.
- {ALL | ANY | SOME} (subquery)
- ALL :모두 만족하면 참
- ANY, SOME: 같은 의미, 조건을 하나라도 만족하면 참이다.
[NOT] IN (subquery):서브 쿼리의 결과 중 하나라도 같은 것이 있으면 참이다.

Ex) 서브 쿼리

<hide/>
--팀A 소속인 회원
select m from Member m
where exists (select t from m.team t where t.name = ‘팀A') 

-- 전체 상품 각각의 재고보다 주문량이 많은 주문들
select o from Order o 
where o.orderAmount > ALL (select p.stockAmount from Product p) 

-- 어떤 팀이든 팀에 소속된 회원
select m from Member m 
where m.team = ANY (select t from Team t)

JPA 서브 쿼리의 한계

JPA 표준에서는 WHERE, HAVING 절에서만 서브 쿼리 사용 가능
SELECT 절 안에서의 서브 쿼리도 가능 - 하이버네이트 지원
~~FROM 절~~(INLINE VIEW)의 서브 쿼리는 현재 JPQL에서 불가능
- 조인으로 풀 수 있으면 풀어서 해결한다.
- 쿼리를 두 번 날린다.
- Native SQL로 해결한다.

10.6 JPQL 타입 표현과 기타식

JPQL 타입 표현

문자: 'HELLO', 'She"s'
숫자: 10L(Long), 10D(Double), 10F(Float)
Boolean: TRUE, FALSE
ENUM: jpabook.MemberType.Admin - 패키지명 포함
- Query DSR에서도 패키지명 넣어서 사용 가능
엔티티 타입: TYPE(m) = Member - 상속 관계에서 사용

Ex) Enum , 문자열 추가

패키지명을 직접 넣는 경우

<hide/>
package jpql;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.EntityTransaction;
import javax.persistence.Persistence;

public class JpaMain {
    public static void main(String[] args) {

        EntityManagerFactory emf = Persistence.createEntityManagerFactory("hello");
        EntityManager em = emf.createEntityManager();
        EntityTransaction tx = em.getTransaction();
        tx.begin();

        try{
            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 query = "SELECT m.username, 'HELLO', TRUE FROM Member m " +
                            "WHERE m.type = jpql.MemberType.ADMIN"
                ;
            List<Object[]> result =  em.createQuery(query)
                                    .getResultList();

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

            tx.commit();

        }catch (Exception e) {
            tx.rollback();
            e.printStackTrace();
        }finally {
            em.close();
        }
        emf.close();    // 팩토리를 나중에 닫는다.
    }
}

Note) 실행 결과

<hide/>
Hibernate: 
    /* SELECT
        m.username,
        'HELLO',
        TRUE 
    FROM
        Member m */ select
            member0_.username as col_0_0_,
            'HELLO' as col_1_0_,
            1 as col_2_0_ 
        from
            Member member0_
object = teamA
object = HELLO
object = true

패키지명 안 넣어도 되는 버전

<hide/>
package jpql;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.EntityTransaction;
import javax.persistence.Persistence;

public class JpaMain {
    public static void main(String[] args) {

        EntityManagerFactory emf = Persistence.createEntityManagerFactory("hello");
        EntityManager em = emf.createEntityManager();
        EntityTransaction tx = em.getTransaction();
        tx.begin();

        try{
            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 query = "SELECT m.username, 'HELLO', TRUE FROM Member m " +
                            "WHERE m.type = :userType";

            List<Object[]> result =  em.createQuery(query)
                                    .setParameter("userType", MemberType.ADMIN)
                                    .getResultList();

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

            tx.commit();

        }catch (Exception e) {
            tx.rollback();
            e.printStackTrace();
        }finally {
            em.close();
        }
        emf.close();    // 팩토리를 나중에 닫는다.
    }
}

Note) 실행 결과

<hide/>
    /* SELECT
        m.username,
        'HELLO',
        TRUE 
    FROM
        Member m 
    WHERE
        m.type = :userType */ select
            member0_.username as col_0_0_,
            'HELLO' as col_1_0_,
            1 as col_2_0_ 
        from
            Member member0_ 
        where
            member0_.type=?
object = teamA
object = HELLO
object = true

Ex) 엔티티 타입: TYPE(m) = Member - 상속 관계에서 사용

jpashop
Order 에서 book 관련 정보만 조회하려면?

<hide/>
package jpabook.jpashop;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.EntityTransaction;
import javax.persistence.Persistence;
import javax.persistence.TypedQuery;
import jpabook.jpashop.domain.Book;
import jpabook.jpashop.domain.Item;
import jpabook.jpashop.domain.Order;
import jpabook.jpashop.domain.OrderItem;
public class JpaMain {
    public static void main(String[] args) {

        EntityManagerFactory emf =
            Persistence.createEntityManagerFactory("hello");
        EntityManager em = emf.createEntityManager();
        EntityTransaction tx = em.getTransaction();
        tx.begin();
        try{
           Book book = new Book();
           book.setName("JPA");
           book.setAuthor("김영한");

           em.persist(book);
           em.createQuery("SELECT i FROM Item i WHERE TYPE(i) = Book ", Item.class)
               .getResultList();
           tx.commit();
        }catch (Exception e) {
            tx.rollback();
        }finally {
            em.close();
        }
        emf.close();    // 팩토리를 나중에 닫는다.
    }
}

Note) 실행 결과

Item에는 @DiscriminatorColumn가 있다.
Book에 @DiscriminatorValue(name ="")을 지정하면 DTYPE에 이름 지정 가능하다. 생략하면 클래스 이름 그대로 Book으로 들어간다.

<hide/>
  /* SELECT
        i 
    FROM
        Item i 
    WHERE
        TYPE(i) = Book  */ select
            item0_.ITEM_ID as ITEM_ID2_3_,
            item0_.createdBy as createdB3_3_,
            item0_.createdDate as createdD4_3_,
            item0_.lastModifiedBy as lastModi5_3_,
            item0_.lastModifiedDate as lastModi6_3_,
            item0_.name as name7_3_,
            item0_.price as price8_3_,
            item0_.stockQuantity as stockQua9_3_,
            item0_.actor as actor10_3_,
            item0_.director as directo11_3_,
            item0_.author as author12_3_,
            item0_.isbn as isbn13_3_,
            item0_.artist as artist14_3_,
            item0_.etc as etc15_3_,
            item0_.DTYPE as DTYPE1_3_ 
        from
            Item item0_ 
        where
            item0_.DTYPE='Book'

JPQL 기타

SQL과 문법이 같은 식
EXISTS, IN
AND, OR, NOT
=, >, >=, <=, <, <>
BETWEEN, LIKE, IS NULL
표준 SQL은 거의 다 지원한다.

10.7 조건식(CASE 등)

조건식 - CASE

기본 CASE
단순 CASE
COALESCE:: 하나씩 조회해서 NULL이 아니면 반환한다.
NULLIF: 두 값이 같으면 NULL 반환, 다르면 첫째 값 반환

Ex)

<hide/>
package jpql;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.EntityTransaction;
import javax.persistence.Persistence;

public class JpaMain {
    public static void main(String[] args) {

        EntityManagerFactory emf = Persistence.createEntityManagerFactory("hello");
        EntityManager em = emf.createEntityManager();
        EntityTransaction tx = em.getTransaction();
        tx.begin();

        try{
            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 query = "SELECT " +
                            "CASE WHEN m.age <= 10 THEN '학생 요금' " +
                                 "WHEN m.age >= 60 THEN '경로 요금' " +
                                 "ELSE '일반 요금' END " +
                            "FROM Member m";

            List<String> result = em.createQuery(query, String.class).getResultList();
            for (String s : result) {
                System.out.println("res " + result);
            }
            tx.commit();

        }catch (Exception e) {
            tx.rollback();
            e.printStackTrace();
        }finally {
            em.close();
        }
        emf.close();    // 팩토리를 나중에 닫는다.
    }
}

Note) 실행 결과

<hide/>
    /* SELECT
        CASE 
            WHEN m.age <= 10 THEN '학생 요금' 
            WHEN m.age >= 60 THEN '경로 요금' 
            ELSE '일반 요금' 
        END 
    FROM
        Member m */ select
            case 
                when member0_.age<=10 then '학생 요금' 
                when member0_.age>=60 then '경로 요금' 
                else '일반 요금' 
            end as col_0_0_ 
        from
            Member member0_
res [학생 요금]

Ex) COALESCE

<hide/>
package jpql;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.EntityTransaction;
import javax.persistence.Persistence;

public class JpaMain {
    public static void main(String[] args) {

        EntityManagerFactory emf = Persistence.createEntityManagerFactory("hello");
        EntityManager em = emf.createEntityManager();
        EntityTransaction tx = em.getTransaction();
        tx.begin();

        try{
            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 query = "SELECT COALESCE(m.username, '이름 없는 회원') FROM Member m ";
            List<String> result = em.createQuery(query, String.class).getResultList();
            for (String s : result) {
                System.out.println("s " + s);
            }
            tx.commit();

        }catch (Exception e) {
            tx.rollback();
            e.printStackTrace();
        }finally {
            em.close();
        }
        emf.close();    // 팩토리를 나중에 닫는다.
    }
}

Note) 실행 결과

<hide/>
    /* SELECT
        COALESCE(m.username,
        '이름 없는 회원') 
    FROM
        Member m  */ select
            coalesce(member0_.username,
            '이름 없는 회원') as col_0_0_ 
        from
            Member member0_
s 이름 없는 회원

Ex) NULLIF

관리자이면 NULL 반환
관리자 이름 가리고 싶은 경우에 사용 가능하다.

<hide/>
package jpql;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.EntityTransaction;
import javax.persistence.Persistence;

public class JpaMain {
    public static void main(String[] args) {

        EntityManagerFactory emf = Persistence.createEntityManagerFactory("hello");
        EntityManager em = emf.createEntityManager();
        EntityTransaction tx = em.getTransaction();
        tx.begin();

        try{
            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 query = "SELECT NULLIF(m.username, '관리자') FROM Member m ";
            List<String> result = em.createQuery(query, String.class).getResultList();
            for (String s : result) {
                System.out.println("s = " + s);
            }
            tx.commit();

        }catch (Exception e) {
            tx.rollback();
            e.printStackTrace();
        }finally {
            em.close();
        }
        emf.close();    // 팩토리를 나중에 닫는다.
    }
}

Note) 실행 결과

<hide/>
    /* SELECT
        NULLIF(m.username,
        '관리자') 
    FROM
        Member m  */ select
            nullif(member0_.username,
            '관리자') as col_0_0_ 
        from
            Member member0_
s = null

10.8 JPQL 함수

JPQL 기본 함수

CONCAT
SUBSTRING
TRIM
LOWER, UPPER
LENGTH
LOCATE
- LOCATE('de', 'abcdef') => de가 있는 위치를 찾아서 4 반환 (위치는 0이 아닌 1부터 시작)
ABS, SQRT, MOD
SIZE, INDEX(JPA 용도)
- INDEX는 컬렉션의 위치 값을 찾을 때 쓰는데 웬만하면 안 쓰는 게 좋다. (중간에 데이터 없으면 NULL 반환될 수 있어서)

Ex) '||' 는 문자열 더하기 , CONCAT()이랑 같은 기능

<hide/>
package jpql;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.EntityTransaction;
import javax.persistence.Persistence;

public class JpaMain {
    public static void main(String[] args) {

        EntityManagerFactory emf = Persistence.createEntityManagerFactory("hello");
        EntityManager em = emf.createEntityManager();
        EntityTransaction tx = em.getTransaction();
        tx.begin();

        try{
            Member member1 = new Member();
            member1.setUsername("관리자1");
            em.persist(member1);
            Member member2 = new Member();
            member2.setUsername("관리자2");
            em.persist(member2);

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

            String query = "SELECT 'A' || 'B' FROM Member m";
            List<String> result = em.createQuery(query, String.class).getResultList();
            for (String s : result) {
                System.out.println("s = " + s);
            }
            tx.commit();

        }catch (Exception e) {
            tx.rollback();
            e.printStackTrace();
        }finally {
            em.close();
        }
        emf.close();    // 팩토리를 나중에 닫는다.
    }
}

Note) 실행 결과

<hide/>
    /* SELECT
        'A' || 'B' 
    FROM
        Member m */ select
            ('A'||'B') as col_0_0_ 
        from
            Member member0_
s = AB
s = AB

Ex) LOCATE() - 위치 찾아 주기

<hide/>
package jpql;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.EntityTransaction;
import javax.persistence.Persistence;

public class JpaMain {
    public static void main(String[] args) {

        EntityManagerFactory emf = Persistence.createEntityManagerFactory("hello");
        EntityManager em = emf.createEntityManager();
        EntityTransaction tx = em.getTransaction();
        tx.begin();

        try{
            Member member1 = new Member();
            member1.setUsername("관리자1");
            em.persist(member1);
            Member member2 = new Member();
            member2.setUsername("관리자2");
            em.persist(member2);
            em.flush();
            em.clear();

            String query = "SELECT LOCATE('de', 'abcdef') FROM Member m";
            int result = em.createQuery(query, Integer.class).getResultList().get(0);
            System.out.println("res = " + result);

//            for (String s : result) {
//                System.out.println("s = " + s);
//            }
            tx.commit();

        }catch (Exception e) {
            tx.rollback();
            e.printStackTrace();
        }finally {
            em.close();
        }
        emf.close();    // 팩토리를 나중에 닫는다.
    }
}

Note) 실행 결과

<hide/>
    /* SELECT
        LOCATE('de',
        'abcdef') 
    FROM
        Member m */ select
            locate('de',
            'abcdef') as col_0_0_ 
        from
            Member member0_
res = 4

사용자 정의 함수

하이버네이트는 사용자 방언에 추가해야한다.
사용하는 DB Dialect 를 상속 받고 사용자 정의 함수를 등록한다. (registerFunction() 이용한다.)
ex) SELECT FUNCTION('group_concat', i.name) FROM Item i
- 사용중인 Dialect를 상속 받는 새로운 Dialect 클래스 만들고 그 안에 사용자 정의 함수를 만든다.
- 함수명: group_concat
- 만든 다음에는새로운 Dialect 클래스를 persistence의 properties에 등록해야한다.

Ex) 사용자 정의 함수

<hide/>
package dialect;
import org.hibernate.dialect.H2Dialect;
import org.hibernate.dialect.function.StandardSQLFunction;
import org.hibernate.type.StandardBasicTypes;

public class MyH2Dialect extends H2Dialect {
    public MyH2Dialect(){
        registerFunction("group_concat", new StandardSQLFunction("group_concat", StandardBasicTypes.STRING));
    }
}

<property name="hibernate.dialect" value="dialect.MyH2Dialect"/>

<hide/>
package jpql;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.EntityTransaction;
import javax.persistence.Persistence;

public class JpaMain {
    public static void main(String[] args) {

        EntityManagerFactory emf = Persistence.createEntityManagerFactory("hello");
        EntityManager em = emf.createEntityManager();
        EntityTransaction tx = em.getTransaction();
        tx.begin();

        try{
            Member member1 = new Member();
            member1.setUsername("관리자1");
            em.persist(member1);
            Member member2 = new Member();
            member2.setUsername("관리자2");
            em.persist(member2);
            em.flush();
            em.clear();

            String query = "SELECT function('group_concat', m.username) FROM Member m";
            List<String> result = em.createQuery(query, String.class).getResultList();
            for (String s : result) {
                System.out.println("s = " + s);
            }
            tx.commit();

        }catch (Exception e) {
            tx.rollback();
            e.printStackTrace();
        }finally {
            em.close();
        }
        emf.close();    // 팩토리를 나중에 닫는다.
    }
}

하이버네이트는 다음과 같이 작성해도 된다. 결과 같음

String query = "SELECT group_concat(m.username) FROM Member m";

Note) 실행 결과

함수 때문에 데이터 한 줄 나온다.

<hide/>
 /* SELECT
        function('group_concat',
        m.username) 
    FROM
        Member m */ select
            group_concat(member0_.username) as col_0_0_ 
        from
            Member member0_
s = 관리자1,관리자2

출처 - https://www.inflearn.com/course/ORM-JPA-Basic

자바 ORM 표준 JPA 프로그래밍 - 기본편 - 인프런 | 강의

JPA를 처음 접하거나, 실무에서 JPA를 사용하지만 기본 이론이 부족하신 분들이 JPA의 기본 이론을 탄탄하게 학습해서 초보자도 실무에서 자신있게 JPA를 사용할 수 있습니다., - 강의 소개 | 인프런

www.inflearn.com

저작자표시

'Spring Framework > [인프런] Java ORM 표준 프로그래밍 - JPA' 카테고리의 다른 글

Chapter 11. 객체지향 쿼리 언어2 - 중급 문법 (0)	2022.09.09
Chapter 09. 값 타입 (0)	2022.09.07
Chapter 08. 프록시와 연관 관계 관리 (0)	2022.09.07
Chapter 07. 고급 매핑 (2)	2022.09.06
Chapter 06. 다양한 연관관계 매핑 (0)	2022.09.05

현재글Chapter 10. 객체지향 쿼리 언어1 - 기본 문법

Backend Developer lan4250@naver.com https://github.com/goraneee

알고리즘, DB, SQL, 백준, Queue, 데이터베이스, Java, mysql, BFS, JPA, 제로베이스 백엔드, 자바, Stack, 제로베이스 스쿨, 스프링, 프로그래머스, dp, dfs, 코드업, GIT,

Today :
Yesterday :

계란 블로그