//Criteria 사용 준비
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();
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();
String sql =
“SELECT ID, AGE, TEAM_ID, NAME FROM MEMBER WHERE NAME = ‘kim’";
List<Member> resultList =
em.createNativeQuery(sql, Member.class).getResultList();
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
select
m from
Member m
group by m.name // 이름으로 그룹
order by m.id // 정렬은 id 순서
/* 반환 타입이 정확히 Member 클래스 --> 반환 클래스 파라미터로 지정! */
TypedQuery<Member> query =
em.createQuery("SELECT m FROM Member m", Member.class);
/* 반환 타입이 정확히 Member 클래스 --> 2번째 파라미터 없음 */
Query query =
em.createQuery("SELECT m.name, m.age FROM Member m");
쿼리 결과 조회 API
파라미터 바인딩
:변수명
+ setParameter()
를 사용한다./* 이름 기준 파라미터 바인딩 -- 이름으로 바인딩 위치를 찾음 */
em.createQuery("select m from Member m where m.name=:name", Member.class)
.setParameter("name","범철").getResultList();
/* 위치 기준 파라미터 바인딩 -- 파라미터 위치 순서에 맞게 지정 */
em.createQuery("select m from Member m where m.name=?1", Member.class)
.setParameter(1,"범철").getResultList();
//페이징 쿼리
String jpql = "select m from Member m order by m.name desc";
List<Member> resultList = em.createQuery(jpql, Member.class)
.setFirstResult(10) // 11번째부터 (0부터 시작이니까)
.setMaxResults(20) // 20개를 가져와라
.getResultList();
[ DB별 페이징시 실제 쿼리 ]
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 * FROM
( SELECT ROW_.*, ROWNUM ROWNUM_
FROM
( 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
) ROW_
WHERE ROWNUM <= ?
)
WHERE ROWNUM_ > ?
/* inner join */
List<Member> innerJoin = em.createQuery
("select m from Member m inner join m.team t", Member.class)
.getResultList();
/* 결과 */
Hibernate:
select
생략 ..
from
Member member0_
inner join
Team team1_
on member0_.team_id=team1_.team_id
/* outer join */
List<Member> leftOuterJoin = em.createQuery
("select m from Member m left join m.team t", Member.class)
.getResultList();
/* 결과 */
Hibernate:
select
생략 ..
from
Member member0_
left outer join
Team team1_
on member0_.team_id=team1_.team_id
[ on 절을 활용한 조인 ]
/* 조인 대상 필터링 */
List<Member> resultList = em.createQuery
("select m from Member m left join m.team t on t.teamName = m.name", Member.class)
.getResultList();
// 같은 쿼리를 where로 했을 때
List<Member> resultList = em.createQuery
("seelct m from Member m left join m.team t where t.teamName = m.name", Member.class)
.getResultList();
on절 뒤에 조건을 주가해서 대상을 필터링한다.
/* 연관관계가 없는 엔티티 조인 */
List<Member> result = em.createQuery
("select m from Member m left join User u on m.name = u.name", Member.class)
.getResultList();
on절 뒤에 추가적인 조건으로 join을 명시한다.