Team team = new Team();
team.setName("teamA");
em.persist(team);

Member member = new Member();
member.setUsername("userA");
member.setAge(10);
member.setType(MemberType.ADMIN);

Member member1 = new Member();
member1.setUsername("userB");
member1.setAge(70);
member1.setType(MemberType.USER);

member.setTeam(team);
member1.setTeam(team);

em.persist(member);
em.persist(member1);


em.flush();
em.clear();

String query ="select case when m.age<=10 then '학생요금'" +
"when m.age>=60 then '경로요금'" +
"else '일반요금' end" +
" from Member m";
List<String> resultList = em.createQuery(query, String.class).getResultList();

for (String s : resultList) {
System.out.println("s = " + s);
}

System.out.println(" ======================================================= ");
tx.commit(); //이시점에 DB 쿼리가 날라감

JPA에서는 인라인뷰가 안먹힌다.

스칼라 서브쿼리랑 서브쿼리는 다 사용가능.

스칼라 서브쿼리

String query = "select (select m1 from Member m1) from Member m left join Team t on m.username=t.name";

뭐이런거나

서브쿼리
String query = "select m from Member m where m.age<(select m1.age from Member m1 where m1.id=1)";

뭐 이런거 사용가능하단 얘기

INNER JOIN (inner 생략가능)

Team team = new Team();
team.setName("teamA");
em.persist(team);

Member member = new Member();
member.setUsername("member1");
member.setAge(10);

member.setTeam(team);

em.persist(member);


em.flush();
em.clear();

String query = "select m from Member m join m.team t where t.name=:teamName";
List<Member> result = em.createQuery(query, Member.class)
.setParameter("teamName","teamA").getResultList();


System.out.println(" ======================================================= ");
tx.commit(); //이시점에 DB 쿼리가 날라감

LEFT OUTER JOIN(또는 RIGHT) (outer 생략가능 - 오라클과 같음)

String query = "select m from Member m left join m.team t where t.name=:teamName";
List<Member> result = em.createQuery(query, Member.class)
.setParameter("teamName","teamA").getResultList();

세타조인(크로스조인, 카티션 프로덕트, 막조인)

String query = "select m from Member m, Team t where m.username=t.name";
List<Member> result = em.createQuery(query, Member.class)
.getResultList();

System.out.println("result.size() = " + result.size());

JPA에서는 페이징 관련 API를 지원한다.

진짜 개꿀이다.

List<Member> result = em.createQuery("select m from Member m order by m.age desc", Member.class)
.setFirstResult(45).setMaxResults(10).getResultList();

그냥 위처럼 사용하면 된다. 타입을 아니까 타입 적어서 List로 빼면 된다.

뺄때 setFirstResult()와 setMaxResults() 를 써주면 개꿀빤다.

rownum에 인라인뷰 3개에 뭐 이딴 짓 안해도됨.ㅡㅡ

desc를 써주는것은 최근 게시물일 경우 니까 쓰는것이것지.

for (int i=0; i<100; i++){
Member member = new Member();
member.setUsername("member"+i);
member.setAge(i);
em.persist(member);
}

em.flush();
em.clear();

List<Member> result = em.createQuery("select m from Member m order by m.age desc", Member.class)
.setFirstResult(45).setMaxResults(10).getResultList();

System.out.println("result.size() = " + result.size());
for (Member member1 : result) {
System.out.println("member1 = " + member1);
}

select 절에 조회할 대상을 지정하는 것을 프로젝션이라고 한다.

쿼리타입으로 조회

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

for (Member member1 : members) {
System.out.println("member1 = " + member1.getAge());
}

Object[] 타입으로 조회

List resultList = em.createQuery("select m.age,m.username from Member m")
.getResultList();

Object o=resultList.get(0);
Object[] result=(Object[]) o;
System.out.println("result = " + result[0]);
System.out.println("result = " + result[1]);

new 명령어로 조회

단순값을 원하는 대로 클래스 만들어서 DTO로 뽑는다.

ex)select new jpql.MemberDTD(m.username,m.age) from Member m

new 패키지명.클래스명(뽑을 대상들)~쿼리~

public class MemberDTD {
private String username;
private int age;

public MemberDTD(String username, int age) {
this.username = username;
this.age = age;
}

public MemberDTD() {
}

@Override
public String toString() {
return "MemberDTD{" +
"username='" + username + '\'' +
", 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;
}
}

List<MemberDTD> members = em.createQuery("select new jpql.MemberDTD(m.username,m.age) from Member m",
MemberDTD.class)
.getResultList();

for (MemberDTD memberDTD : members) {
System.out.println("memberDTD = " + memberDTD);
}

쌩 코드 날리기.

Member member = new Member();
member.setUserName("aaa");
em.persist(member);

List<Member> resultList = em.createNativeQuery("select member_id,city,street from member", Member.class).getResultList();
for (Member o : resultList) {
System.out.println("o = " + o.getId());
}

System.out.println(" ======================================================= ");
tx.commit(); //이시점에 DB 쿼리가 날라감

오픈소스가 있다. (사이트 있음)

동적쿼리 작성이 정말 편함.

단순하고 쉬움 (직관적)

실무사용 권장

Criteria는 그냥 쿼리를 코드로 짜는 방법이다.

간단한것 같지만 엄청 어렵다..

실무에서 안쓰는 걸추천...

못알아봐서 유지보수가 어렵다.......

//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();

뭐 아래와 같은 식으로 동적쿼리도 짤수있다.

//Criteria 사용준비
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Member> query = cb.createQuery(Member.class);

//루트 클래스 (조회를 시작할 클래스)
Root<Member> m = query.from(Member.class);

//쿼리 생성
String username = "name";
CriteriaQuery<Member> cq = query.select(m);
if (username != null) {
cq = cq.where(cb.equal(m.get("userName"), "kim"));
}

List<Member> resultList = em.createQuery(cq).getResultList();