String jpql = "select m From Member m where m.age > 18";
List<Member> result = em.createQuery(jpql, Member.class).getResultList();
// 실행된 SQL
select
m.id as id,
m.age as age,
m.USERNAME as USERNAME,
m.TEAM_ID as TEAM_ID
from
Member m
where
m.age > 18
TypedQuery<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()
: 결과가 정확히 하나일 때, 단일 객체 반환Spring-Data-JPA
를 사용하면, 예외를 무조건 발생시키는 것이 아닌 Optional을 반환함Query query = em.createQuery(
"SELECT m FROM Member m where m.username =:username");
query.setParameter("username", usernameParam);
Member singleResult = query.getSingleResult();
SELECT new jpql.UserDTO(m.username, m.age) FROM Member m
setFirstResult(int startPosition)
: 0부터 시작setMaxResults(int maxResult)
: 조회할 데이터 수예시)
// 페이징 쿼리
String jpql = "select m from Member m order by m.name desc";
List<Member> resultList = em.createQuery(jpql, Member.class)
.setFirstResult(10)
.setMaxResult(20)
.getResultList();
// 실제 나가는 쿼리 - MySQL 방언
SELECT
M.ID AS ID,
M.AGE AS AGE,
M.TEAM_ID AS TEAM_ID,
M.NAME AS NAME
FROM
MEMBER M
ORDER BY
M.NAME DESC LIMIT ?, ?
SELECT m FROM Member m [INNER] JOIN m.team t;
SELECT m FROM Member m LEFT [OUTER] JOIN m.team t;
ON
을 사용하면 SELECT m FROM Member m LEFT JOIN m.team t on t.name = 'A';
SELECT m, t FROM Member m LEFT JOIN Team t on m.username = t.name;
SELECT COUNT(m) from Member m, Team t WHERE m.username = t.name;
[NOT] EXIST (sq)
, [NOT] IN (sq)
를 쓸 수 있다.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
case when m.age <= 10 then '학생요금'
when m.age >= 60 then '경로요금'
else '일반요금'
end
from Member m
select coalesce(m.username, '이름 없는 회원') from Member m
select NULLIF(m.username, '관리자') from Member m
//JPQL
select o.member from Order o
//SQL
select m.*
from Orders o
inner join Member m on o.member_id = m.id
💡 명시적 조인, 묵시적 조인
- 명시적 조인 : join 키워드를 직접 사용
select m from Member m join m.team t
- 묵시적 조인 : 경로 표현식에 의해 묵시적으로 SQL 조인 발생 (내부 조인만 가능)
select m.team from Member m
- 컬렉션은 경로 탐색의 끝, 명시적 조인을 통해 별칭을 얻어야 한다.
select m.username from Team t join t.members m
- 묵시적 조인은 상황을 한눈에 파악하기 어려우므로 가급적 묵시적 조인 대신에 명시적 조인을 사용 권장
@Entity
@NamedQuery(
name = "Member.findByUsername",
query = "select m from Member m where m.username = :username")
public class Member {
...
}
List<Member> resultList =
em.createNameQuery("Member.findByUsername", Member.class)
.setParameter("username", "회원1")
.getResultList();
executeUpdate()
의 결과는 영향받는 엔티티 수를 반환한다.@Modifying
이라는 어노테이션이 자동으로 영속성 컨텍스트를 초기화해준다.