JPA는 다양한 쿼리 방법을 지원
JPQL 소개
EntityManager.find()
a.getB().getC()
) 가능JPQL 특징
JPQL 기본
코드 (ex) 이름에 'hello'가 포함된 멤버를 조회)
String read = "select m from Member m where m.username like '%hello%'";
List<Member> resultList = em.createQuery(read, Member.class).getResultList();
쿼리
print:
Hibernate:
/* select
m
from
Member m
where
m.username like '%hello%' */ select
member0_.member_id as member_i1_6_,
member0_.city as city2_6_,
member0_.street as street3_6_,
member0_.zipcode as zipcode4_6_,
member0_.team_id as team_id8_6_,
member0_.username as username5_6_,
member0_.endDate as endDate6_6_,
member0_.startDate as startDat7_6_
from
Member member0_
where
member0_.username like '%hello%'
Criteria (거의 안 씀)
Criteria 예제
//Criteria 사용 준비
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Member> query = cb.createQuery(Member.class);
//루트 클래스 (조회를 시작할 클래스)
Root<Member> m = query.from(Member.class);
//쿼리 생성 cb.XXX를 통해서 검색 쿼리 생성
CriteriaQuery<Member> cq = query.select(m).where(cb.equal(m.get("username"), "hello"));
List<Member> resultList = em.createQuery(cq).getResultList();
문자(String)가 아닌 자바 코드로 JPQL을 작성
장점
컴파일 시점에 문법 오류 탐색
동적 쿼리 작성
//Criteria 사용 준비
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Member> query = cb.createQuery(Member.class);
//루트 클래스 (조회를 시작할 클래스)
Root<Member> m = query.from(Member.class);
//쿼리 생성 cb.XXX를 통해서 검색 쿼리 생성
CriteriaQuery<Member> cq = query.select(m);
//동적 쿼리 생성
String username = "XXX";
if (username != null) {
cq = cq.where(cb.equal(m.get("username"), "hello"));
}
List<Member> resultList = em.createQuery(cq).getResultList();
단점
QueryDSL
QueryDSL을 사용하기 위해선 의존성 + 플러그인 필요(당장에는 중요하지 않기에 예제만 살펴봄)
QueryDSL 예제
//JPQL
//select m from Member m where m.age > 18
JPAFactoryQuery q = new JPAFactoryQuery(em);
QMember m = QMember.member;
List<Member> list = query
.selectFrom(m)
.where(m.age.gt(18))
.orderBy(m.name.desc())
.fetch();
문자가 아닌 자바 코드로 JPQL을 작성
장점
네이티브 SQL
JPA가 제공하는 SQL을 직접 사용
JPQL로 해결할 수 없는 특정 DB에 의존 (ex) 오라클 CONNECT BY, 특정 DB만 사용하는 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 등
JPA를 사용하면서 JDBC Connection을 사용하거나 스프링 JdbcTemplate, Mynatis 등 함께 사용
💥단 영속성 컨텍스트를 적절한 시점에 강제로 .flush()
를 해야한다.
예) JPA를 우회해서 SQL을 실행하기 직전에 영속성 컨텍스트 수동 .flush()
try {
Member member = new Member();
member.setUsername("member1");
em.persist(member);
/**
* flush 시점 -> commit, query(Native SQL, QueryDSL, JPQL 등)
* But, DB 커넥션을 획득해서 쿼리를 생성할때는 flush가 되지 않기 떄문에
* 강제 flush가 필요하다.
*/
//em.flush();
//ex) dbConnection.createQuery("select * from Member")
tx.commit();
}
JPQL (Java Persistence Query Language)
새 프로젝트 생성
persistence.xml
추가모델
Member
@Entity
public class Member {
@Id
@GeneratedValue
private Long id;
@Column(name = "name")
private String username;
private int age;
@ManyToOne
@JoinColumn(name = "team_id")
private Team team;
}
Team
@Entity
public class Team {
@Id
@GeneratedValue
@Column(name = "team_id")
private Long id;
private String name;
@OneToMany(mappedBy = "team")
private List<Member> members = new ArrayList<>();
}
Order
@Entity
@Table(name = "orders")
public class Order {
@Id
@GeneratedValue
@Column(name = "order_id")
private Long id;
private int orderMount;
@Embedded
private Address address;
@ManyToOne
@JoinColumn(name = "product_id")
private Product product;
}
Product
@Entity
public class Product {
@Id
@GeneratedValue
private Long id;
private String name;
private int price;
private int stockAmount;
}
Address
@Embeddable
public class Address {
private String city;
private String street;
private String zipcode;
}
JPQL 문법
select m from Member (as) m where m.age > 18
⇒ Member는 Entity집합과 정렬
select
count(m),
sum(m.age),
avg(m.age),
max(m.age),
min(m.age)
from Member m
TypeQuery, Query
TypeQuery : 반환 타입이 명확할 때 사용
Query : 반환 타입이 명확하지 않을 때 사용
TypedQuery<Member> query = em.createQuery("select m from Member m", Member.class);
Query query2 = em.createQuery("select m.username, m.age from Member m");
String
/ m.age : int
결과 조회 API
query.getResultList()
: 결과가 하나 이상일 때, 리스트 반환query.getSingleResult()
: 결과가 한 개만 있을 때, 단일 객체 반환javax.persistence.NoResultException
javax.persistence.NonUniqueResultException
파라미터 바인딩
이름 기준
try {
Member member = new Member();
member.setUsername("member1");
member.setAge(20);
em.persist(member);
//메소드 체이닝 이용
Member res = em.createQuery("select m from Member m where m.username = :username", Member.class)
.setParameter("username", "member1")
.getSingleResult();
System.out.println("result = " + res.getUsername());
tx.commit();
}
위치 기준(쓰지 마세용)
Member res = em.createQuery("select m from Member m where m.username = ?1", Member.class)
.setParameter(1, "member1")
.getSingleResult();
select
절에 조회할 대상을 지정
대상 : 엔티티, 임베디드 타입, 스칼라 타입(숫자, 문자 기본 데이터 타입)
예제)
select m from Member m
→ 엔티티 프로젝션
select m.team from Member m
→ 엔티티 프로젝션
다만, 위와 같이 문장을 사용하면 연관관계의 유무를 구분하기 어렵다.
⇒ "select t from Member m join m.team t", "Team.class"
"select o.address from Order o", "Address.class"
→ 임베디드 타입 프로젝션
select m.username, m.age from Member m
→ 스칼라 타입 프로젝션
distinct
중복 제거
(참고) - 주석
try {
Member member = new Member();
member.setUsername("member1");
member.setAge(10);
em.persist(member);
em.flush(); //db에 반영
em.clear(); //영속성 컨텍스트 비우기
List<Member> results = em.createQuery("select m from Member m", Member.class)
.getResultList(); **//과연 영속성 컨텍스트가 관리할까?**
/**
* 아래와 같은 코드는 영속성 컨텍스트의 관리하에 DB에 반영된다.
* jqpl을 통한 결과값들(위의 results)은 영속성 컨텍스트에 속한다.
*/
Member findMember = results.get(0);
findMember.setAge(20);
// age 값이 변하는 것을 확인할 수 있다.
tx.commit();
}
프로젝션 - 여러 값 조회(String, int 등) ex) select m.username, m.age from Member m
1) Query 타입 조회 (TypeQuery X)
//username : String
//age : int
List resultList = em.createQuery("select m.username, m.age from Member m")
.getResultList();
Object o = resultList.get(0);
Object[] results = (Object[]) o;
System.out.println("username = " + results[0]);
System.out.println("age = " + results[1]);
print:
username = member1
age = 10
2) Object[] 타입 조회
List<Object[]> resultList = em.createQuery("select m.username, m.age from Member m")
.getResultList();
Object[] results = resultList.get(0);
System.out.println("username = " + results[0]);
System.out.println("age = " + results[1]);
3) new 명령어 조회
MemberDTO
package jpql;
public class MemberDTO {
private String username;
private int age;
public MemberDTO(String username, int age) {
this.username = username;
this.age = age;
}
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;
}
}
단순 값을 DTO로 바로 조회
List<MemberDTO> resultList = em.createQuery("select new jpql.MemberDTO(m.username, m.age) from Member m", MemberDTO.class)
.getResultList();
MemberDTO memberDTO = resultList.get(0);
System.out.println(memberDTO.getUsername());
System.out.println(memberDTO.getAge());
new
연산자 + 패키지 명을 포함한 전체 클래스명 필요
순서와 타입이 일치하는 생성자 필요
현재는 String인데 QueryDSL을 사용하면 패키지명 또한 동적으로 사용할 수 있다.
JPA는 페이징을 두 API로 추상화
setFirstResult(int startPosition)
: 조회 시작 위치(0부터)
setMaxResults(int maxResult)
: 조회할 데이터 수
예제에 앞서 Member 클래스에 toString()
오버라이드 (💥team 제외)
try {
for (int i = 0; i < 100; i++) {
Member member = new Member();
member.setUsername("member" + i);
member.setAge(i);
em.persist(member);
}
em.flush(); //db에 반영
em.clear(); //영속성 컨텍스트 비우기
List<Member> resultList = em.createQuery("select m from Member m order by m.age desc", Member.class)
.setFirstResult(0)
.setMaxResults(10)
.getResultList();
System.out.println("resultList.size = " + resultList.size());
for (Member memberList : resultList) {
System.out.println("member1 = " + memberList);
}
tx.commit();
}
print:
resultList.size = 10
member1 = Member{id=100, username='member99', age=99}
member1 = Member{id=99, username='member98', age=98}
member1 = Member{id=98, username='member97', age=97}
member1 = Member{id=97, username='member96', age=96}
member1 = Member{id=96, username='member95', age=95}
member1 = Member{id=95, username='member94', age=94}
member1 = Member{id=94, username='member93', age=93}
member1 = Member{id=93, username='member92', age=92}
member1 = Member{id=92, username='member91', age=91}
member1 = Member{id=91, username='member90', age=90}
오버라이드 한 .toString()
의 결과값들이 출력
desc
에 의한 역순
SQL 방언에 따라 전략을 달리한다.
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 ?, OFFSET ?
ORACLE
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_ > ?
내부 조인 : select m from Member m [INNER] join m.team t
(inner 생략 가능)
Member
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "team_id")
private Team team;
public void changeTeam(Team team) {
this.team = team;
team.getMembers().add(this);
}
Main
try {
Team team = new Team();
team.setName("teamA");
em.persist(team);
Member member = new Member();
member.setUsername("member1");
member.setAge(10);
member.changeTeam(team);
em.persist(member);
em.flush(); //db에 반영
em.clear(); //영속성 컨텍스트 비우기
String query = "select m from Member m inner join m.team t"; //where t.name = :teamName과 같이 파라미터 이용
List<Member> resultList = em.createQuery(query, Member.class)
.getResultList();
tx.commit();
}
print:
Hibernate:
/* 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_.name as name3_0_
from
Member member0_
inner join
Team team1_
on member0_.team_id=team1_.team_id
외부 조인 : select m from Member m LEFT [OUTER] join m.team t
(left, right 등 명시하면 outer 생략 가능)
세타 조인 : select count(m) from Member m, Team t where m.username = t.name
조인 - ON 절
select m, t from Member m left join m.team t on t.name = 'A'
SELECT m.*, t.* FROM Member m LEFT JOIN Team t ON m.TEAM_ID=t.id and t.name='A'
select m, t from Member m left join Team t on m.username = t.name
select m from Member m where m.age > **(selelct 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)
[NOT] IN (subquery)
: 서브쿼리의 결과 중 하나라도 같은 것이 있으면 참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)
selelct m from Member m where m.team = ANY (select t from Team t)
타입 표현
문자 : 'HELLO', 'She''s'
숫자 : 10L(Long), 10D(double), 10F(float)
Boolean : TRUE, FALSE
ENUM : jpabook.MemberType.Admin
(패키지명 포함)
하지만 파라미터를 이요해서 하면 복잡한 편은 아니다. (예제)
try {
Team team = new Team();
team.setName("teamA");
em.persist(team);
Member member = new Member();
member.setUsername("member1");
member.setAge(10);
member.setType(MemberType.ADMIN);
member.changeTeam(team);
em.persist(member);
em.flush(); //db에 반영
em.clear(); //영속성 컨텍스트 비우기
String query = "select m.username, 'HELLO', true from Member m where m.type = :userType";
List<Object[]> resultList = em.createQuery(query)
.setParameter("userType", MemberType.ADMIN)
.getResultList();
for (Object[] objects : resultList) {
System.out.println("objects = " + objects[0]);
System.out.println("objects = " + objects[1]);
System.out.println("objects = " + objects[2]);
}
tx.commit();
}
엔티티 타입 : TYPE(m) = Member (상속관계에서 사용)
"selelct i from Item i where type(i) = Book", Item.class
기타
QueryDSL을 다룰 줄 알면 이렇게 문자로 쓸 일이 없다. 공부하자!
기본 CASE
select
case
when m.age <= 10 then '학생요금'
when m.age >= 60 then '경로요금'
else '일반요금'
end
from Member m
단순 CASE
select
case t.name
when '팀A' then '인센티브110%'
when '팀B' then '인센티브120'
end
from Team t
COALESCE : 하나씩 조회해서 null이 아니면 반환
select coalesce(m.username, '이름 없는 회원') from Member m
NULLIF : 두 값이 같으면 null 반환, 다르면 첫 번째 값 반환
select nullif(m.username, '관리자') from Member m
기본(표준) 함수 - DB 상관 없음
select concat('a', 'b') from Member m
select substring(m.username, 2, 3) from -
select locate('de', 'abcdefg') from -
select size(t.members) from Team t
사용자 정의 함수 호출
하이버네이트는 사용전 방언에 추가해야함
사용하는 DB 방언을 상속받고, 사용자 정의 함수를 등록한다.
persistence.xml
<!-- 패키지명.클래스 (경로) -->
<property name="hibernate.dialect" value="dialect.MyDialect"/>
MyDialect
package dialect;
import org.hibernate.dialect.H2Dialect;
import org.hibernate.dialect.function.StandardSQLFunction;
import org.hibernate.type.StandardBasicTypes;
public class MyDialect extends H2Dialect {
public MyDialect() {
registerFunction("group_concat", new StandardSQLFunction("group_concat", StandardBasicTypes.STRING));
}
}
Main
try {
Member member1 = new Member();
member1.setUsername("관리자1");
em.persist(member1);
Member member2 = new Member();
member2.setUsername("관리자2");
em.persist(member2);
em.flush(); //db에 반영
em.clear(); //영속성 컨텍스트 비우기
//"select group_concat(m.username) from Member m"
String query = "select function('group_concat', m.username) from Member m";
List<String> resultList = em.createQuery(query, String.class).getResultList();
for (String s : resultList) {
System.out.println("s = " + s);
}
tx.commit();
}
print:
s = 관리자1,관리자2