TIL

객체지향 쿼리 언어 - 기본 문법

소개

JPA는 다양한 쿼리 방법을 지원

JPQL

단순 JPQL은 동적 쿼리를 만들기 어렵다.

Criteria 소개

QueryDSL 소개

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 소개

String sql = "SELECT ID, AGE, TEAM_ID, NAME FROM MEMBER WHERE NAME = 'kim'"; 

List<Member> resultList = em.createNativeQuery(sql, Member.class)
		.getResultList();

JDBC 직접 사용, SpringJdbcTemplate 등

JPQL(Java Persistence Query Language)

JPQL - 기본 문법과 기능

JPQL 소개

JPQL 문법

집합과 정렬

select

COUNT(m), //회원수

SUM(m.age), //나이 합

AVG(m.age), //평균 나이

MAX(m.age), //최대 나이

MIN(m.age) //최소 나이

from Member m

TypeQuery, Query

TypedQuery<Member> query1 = em.createQuery("select m from Member m", Member.class);

Query query2 = 	em.createQuery("select m.userName, m.age from Member m");

결과 조회 API

List<Member> members = em.createQuery("select m from Member m", Member.class)
  	 .getResultList();

Member member = em.createQuery("select m from Member m", Member.class)
   .getSingleResult();

파라미터 바인딩 - 이름 기준, 위치 기준

em.createQuery("SELECT m FROM Member m where m.userName = :username")
   .setParameter("username", "member1")
   .getSingleResult()

프로젝션

프로젝션 - 여러 값 조회

  1. Query 타입으로 조회

  2. Object[] 타입으로 조회

List resultList = em.createQuery("select 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]);
  1. new 명령어로 조회
    • 단순 값을 DTO로 바로 조회 DTO를 만들어야 함
@Setter @Getter
@AllArgsConstructor
public class MemberDto {

   private String userName;
   private int age;
}

List<MemberDto> resultList = em.createQuery(
      "select new hellojpa.jpql.MemberDto(m.userName, m.age) from Member m", 
			MemberDto.class)
   .getResultList();

페이징 API

//페이징 쿼리
String jpql = "select m from Member m order by m.age desc";
List<Member> resultList = em.createQuery(jpql, Member.class)
   .setFirstResult(10)
   .setMaxResults(20)
   .getResultList();

select
        member0_.id as id1_0_,
        member0_.age as age2_0_,
        member0_.team_id as team_id4_0_,
        member0_.user_name as user_nam3_0_ 
    from
        member member0_ 
    order by
        member0_.age desc limit ? offset ?

조인

SELECT m FROM Member m [INNER] JOIN m.team t inner 생략 가능

SELECT m FROM Member m LEFT [OUTER] JOIN m.team t outer 생략 가능

select count(m) from Member m, Team t where m.username = t.name

조인 - ON 절

조인 대상 필터링

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'

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

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

서브 쿼리

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

메인 쿼리랑 서브 쿼리랑 서로 관계가 없어야 성능이 잘 나옴

서브 쿼리 지원 함수

서브 쿼리 - 예제

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 서브 쿼리 한계

JPQL 타입 표현

"select m.userName, 'HELLO', true, 10L from Member m"

"select m from Member m where m.type = hellojpa.jpql.MemberType.ADMIN";

String query = "select m from Member m where m.type = :type"; 
List<Member> result = em.createQuery(query, Member.class)   
		.setParameter("type", MemberType.ADMIN)   
		.getResultList();

JPQL 기타

조건식 - CASE 식

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

"select "
   + "case t.name"
   + "when 'teamA' then '인센티브 120%' "
   + "when 'teamB' then '인센티브 110%' "
   + "else '인센티브 105%' "
   + "end "
   + "from Team t";

JPQL 기본 함수

사용자 정의 함수 호출