본 문서는 인프런의 자바 ORM 표준 JPA 프로그래밍 - 기본편 (김영한) 강의를 공부하며 작성한 개인 노트입니다.
JPA는 JPQL, JPA Criteria, QueryDSL, 네이티브 SQL, JDBC API 등의 다양한 쿼리 방법을 지원한다
JPA를 사용하면 엔티티 객체를 중심으로 개발 > 검색 쿼리도 테이블이 아닌 엔티티 객체를 대상으로 검색
String jpql = "select m from Member m where m.name like '%hello%';
List<Member> result = em.createQuery(jpql, Member.class)
.getResultList();
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Member> query = cb.createQuery(Member.class);
//루트 클래스 (조회 시작할 클래스)
Root<Member> m = query.from(Member.class);
//쿼리 생성
query.select(m).where(cb.equal(m.get ("username"), "kim"));
List<Member> resultList = em.createQuery(cq).getResultList();
QueryDSL로 대체하는 것이 좋다
public void hello() {
QMember m = QMember.member;
List<Member> result = queryFactory
.select(m)
.from(m)
.where(m.name.like("kim"))
.orderBy(m.id.desc())
.fetch();
}
em.createNativeQuery()
JPQL - Java Persistence Query Language
select_문 :: =
select_절
from_절
[where_절]
[groupby_절]
[having_절]
[orderby_절]
update_문 :: = update_절 [where_절]
delete_문 :: = delete_절 [where_절]
(예) select m from Member as m where m.age > 18
select
COUNT(m),
SUM(m.age),
AVG(m.age),
MAX(m.age),
MIN(m.age)
from Member m
TypeQuery<Member> query = em.createQuery("SELECT m FROM Member m", Member.class);
Query query = em.createQuery("SELECT m.username, m.age from Member m");
query.getResultList()
: 결과가 하나 이상일 때 리스트 반환query.getSingleResult()
: 결과가 정확히 하나, 단일 객체 반환javax.persistence.NoResultException
javax.persistence.NonUniqueResultException
이름 기준
Member result = em.createQuery("select m from Member m where m.username = :username", Member.class)
.setParameter("username", "member1")
.getSingleResult();
위치 기준 (비추천)
Member result = em.createQuery("select m from Member m where m.username =?1", Member.class)
.setParameter(1, "member1")
.getSingleResult();
query.setParameter(1, "member1");
프로젝션 - SELECT 절에 조회할 대상을 지정하는 것
DISTINCT
로 중복 제거em.createQuery("select distinct m.username, m.age from Member m")
.getResultList();
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
select new jpql.MemberDTO(m.username, m.age) from Member m", MemberDTO.class
JPA는 페이징을 다음 두 API로 추상화
setFirstResult(int startPosition)
- 조회 시작 위치 setMaxResults(int maxResult)
- 조회할 데이터 수List<Member> result = em.createQuery("select m from Member m order by m.age desc", Member.class)
.setFirstResult(0)
.setMaxResults(10)
.getResultList();
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
SELECT m, t FROM Member m LEFT JOIN m.team t on t.name = 'A'
m.username
과 t-name
외부 조인 가능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 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)
타입 표현
em.createQuery("select i from Item i where type(i) = Book", Item.class)
.getResultList();
기타
조건식
select
case when (condition1) then (result1)
when (condition2) then (result2)
else (result3)
end
from Member m
select
case (var)
when (value1) then (result1)
when (value2) then (result2)
else (result3)
end
from Team t
COALESCE: 하나씩 조회해서 null이 아니면 반환
select coalesce(m.username, '이름 없는 회원') from Member m
select NULLIF(m.username, '관리자') from Member m
public class MyH2Dialect extends H2Dialect {
public MyH2Dialect() {
registerFunction("group_concat", new StandardSQLFunction("group_concat", StandardBasicTypes.STRING));
}
}
hibernate.dialect
값을 "dialect.MyH2Dialect"로 변경