java 스프링부트 ( spring boot ) JPQL ( 2 )

김동명·2022년 12월 12일
JPAJPQLJavaSpringSpringboot
0

스프링부트

목록 보기
18/19

프로젝트 세팅

  • persistence.xml 생성
    • resources > META-INF > persistence.xml
<?xml version="1.0" encoding="UTF-8"?>

<persistence version="2.2"
   xmlns="http://xmlns.jcp.org/xml/ns/persistence"
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_2.xsd">
   <persistence-unit name="hello">
      <properties>
         <!-- 필수 속성 -->
         <property name="javax.persistence.jdbc.driver" value="oracle.jdbc.driver.OracleDriver" />
         <property name="javax.persistence.jdbc.user" value="springjpa" />
         <property name="javax.persistence.jdbc.password" value="springjpa" />
         <property name="javax.persistence.jdbc.url" value="jdbc:oracle:thin:@localhost:1521:xe" />
         <property name="hibernate.dialect" value="org.hibernate.dialect.Oracle10gDialect" />
<!--          <property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQL10Dialect" /> -->
		
		 <!-- 테이블 생성 옵션 -->
	     <property name="hibernate.hbm2ddl.auto" value="create"/>
		
         <!-- 옵션 -->
         <!-- 콘솔에 하이버네이트가 실행하는 SQL문 출력 -->
         <property name="hibernate.show_sql" value="true" />
         <!-- SQL 출력 시 보기 쉽게 정렬 -->
         <property name="hibernate.format_sql" value="true" />
         <!-- 쿼리 출력 시 주석(comments)도 함께 출력 -->
         <property name="hibernate.use_sql_comments" value="true" />
      </properties>
   </persistence-unit>
</persistence>


시작

1. DTO 생성

  • domain 패키지 생성

- domain > `Member.java` 생성 
@Entity
@Getter @Setter @ToString
public class Member {
	
	@Id @GeneratedValue
	private Long id;
	private String username;
	private int age;
    
    @ManyToOne
	@JoinColumn(name = "TEAM_ID")
	private Team team;
}

  • domain > Team.java 생성
@Entity
@Getter @Setter
public class Team {

	@Id @GeneratedValue
	@Column(name = "TEAM_ID")
	private Long id;
	private String name;
  
	@OneToMany(mappedBy = "team")
	private List<Member> members = new ArrayList<Member>();  
}
  • domain > Order.java 생성
@Entity
@Getter @Setter
@Table(name = "Orders")
public class Order {
	
	@Id @GeneratedValue
	private Long id;
	private int orderAmount;
	@Embedded
	private Address address;
}
  • domain > Address.java 생성
@Embeddable
@Setter @Getter
public class Address {

	private String city;
	private String street;
	private String zipcode;
}


2. 기본 데이터 조회하기

  • JpaMain.java 생성
  • 데이터 1건 insert
  • 영속성 컨텍스트 비우고 조회하기
public class JpaMain {

	public static void main(String[] args) {
		
		EntityManagerFactory emf = Persistence.createEntityManagerFactory("hello");
		EntityManager em = emf.createEntityManager();
		EntityTransaction tx = em.getTransaction();
		tx.begin();
		
		try {
			
			Member member = new Member();
			member.setUsername("memeber1");
			member.setAge(10);
			em.persist(member);
			
			em.flush();
			em.clear();
			
			List<Member> result = em.createQuery("select m from Member m", Member.class).getResultList();
			
			// 업데이트가 될 경우, 영속성 컨텍스트에서 관리가 된다고 볼 수 있다.
			Member findMember = result.get(0);
			findMember.setAge(20);
			
			tx.commit();
		} catch (Exception e) {
			tx.rollback();
		} finally {
			em.close();
			emf.close();
		}
	}
}
  • 결과 ( Run as > Spring Boot App )
  • 콘솔창 ( insert )
  • 콘솔창 ( update )


3. join이용 데이터 조회하기

  • JpaMain2.java 생성
    • JpaMain.java 복사붙여넣기 후 수정
...
//			List<Team> result = em.createQuery("select m.team from Member m", Team.class).getResultList();
//			권장
			List<Team> result = em.createQuery("select t from Member m join m.team t", Team.class).getResultList();
...
  • 결과


4. 임베디드 데이터 조회하기

  • JpaMain3.java 생성
    • JpaMain 복사 붙여넣기 후 수정
...
			em.clear();
			// 에러 발생
//			em.createQuery("select o.address from Order o", Address.class).getResultList();


			em.createQuery("select o.address from Order o", Address.class).getResultList();
			
			tx.commit();
...
  • 결과


5. 함수 사용 데이터 조회하기

  • JpaMain3.java 수정
...
//			em.createQuery("select o.address from Order o", Address.class).getResultList();
			
			//exception
//			em.createQuery("select o.address from Address o", Address.class).getResultList();
			
			em.createQuery("select distinct m.username, m.age from Member m").getResultList();
			
...
  • 결과


6. 타입이 특정되지 않은 데이터 조회하기

6-1 방법 ( 1 )

  • JpaMain4.java 생성
...
			em.clear();

			List resultList = em.createQuery("select m.username, m.age from Member m").getResultList();
			
			// 타입을 지정하지 못했기 때문에 object로 받아온다.
			Object o = resultList.get(0);
			Object[] result = (Object[])o;
			System.out.println("username = " + result[0]);
			System.out.println("userage = " + result[1]);
			
			tx.commit();
...

  • 결과

6-2 방법 ( 2 )

  • JpaMain4.java 수정
...
			List<Object[]> resultList = em.createQuery("select m.username, m.age from Member m").getResultList();
			
			Object[] result = resultList.get(0);
			System.out.println("username = " + result[0]);
			System.out.println("userage = " + result[1]);
...

  • 결과

6-3 방법 ( 3 ) 가장 많이 쓰는 방법

  • MemberDTO.java 생성
@Getter @Setter @ToString
public class MemberDTO {

	private String username;
	private int age;
    
	public MemberDTO(String username, int age) {
		super();
		this.username = username;
		this.age = age;
	}
}
  • JpaMain4.java 수정
...
			List<MemberDTO> result = em.createQuery("select new com.example.jpql.MemberDTO (m.username, m.age) from Member m").getResultList();
			
			MemberDTO memberDTO = result.get(0);
			System.out.println("username = " + memberDTO.getUsername());
			System.out.println("age = " + memberDTO.getAge());
...



7. 페이징 처리

페이징 처리
- setFirstResult(int startPoint) : 조회 시작 위치 ( 0 부터 시작 )
- setMaxResults(int maxResult) : 조회할 데이터 수
- 시작위치부터 조회할 데이터 수 가져오기

  • JpaMain5.java 생성
public class JpaMain5 {

	public static void main(String[] args) {
		
		EntityManagerFactory emf = Persistence.createEntityManagerFactory("hello");
		EntityManager em = emf.createEntityManager();
		EntityTransaction tx = em.getTransaction();
		tx.begin();
		
		try {
			
			for(int i = 0 ; i < 100; i++ ) {
			Member member = new Member();
			member.setUsername("memeber" + i);
			member.setAge(i);
			em.persist(member);
			}
			
			em.flush();
			em.clear();
			
			// 페이징 처리
			String jpql = "select m from Member m order by m.id";
			List<Member> resultList = em.createQuery(jpql, Member.class).setFirstResult(10).setMaxResults(20).getResultList();
			
			System.out.println("result.size : " + resultList.size());
			
			for ( Member member1 : resultList) {
				System.out.println("member1 = " + member1.toString());
			}
			
			tx.commit();
		} catch (Exception e) {
			tx.rollback();
		} finally {
			em.close();
			emf.close();
		}
	}
}

  • 결과


8. join 이용

8-1. inner join

  • JpaMain6.java 생성
public class JpaMain5 {

	public static void main(String[] args) {
		
		EntityManagerFactory emf = Persistence.createEntityManagerFactory("hello");
		EntityManager em = emf.createEntityManager();
		EntityTransaction tx = em.getTransaction();
		tx.begin();
		
		try {
			Member member = new Member();
			member.setUsername("memeber");
			member.setAge(10);
			em.persist(member);
			
			em.flush();
			em.clear();
			
			// 조인 : inner join -> inner는 생략 가능
			String sql = "select m from Member m inner join m.team t";
			List<Member> resultList = em.createQuery(sql, Member.class).getResultList();
			
			tx.commit();
		} catch (Exception e) {
			tx.rollback();
		} finally {
			em.close();
			emf.close();
		}
	}
}

  • 결과


8-2. outer join

  • JpaMain6.java 수정
...
			// 조인 : inner join -> inner는 생략 가능
//			String sql = "select m from Member m inner join m.team t";
//			List<Member> resultList = em.createQuery(sql, Member.class).getResultList();
			
			// left outer join
			String sql = "select m from Member m left outer join m.team t";
			List<Member> resultList = em.createQuery(sql, Member.class).getResultList();
			
			tx.commit();
...

  • 결과


9. 서브쿼리 이용

JPA 서브 쿼리 한계
- where, having 절에서 사용 가능 -> JPA 표준 스펙
- select 절에서도 가능 -> 하이버네이트에서 지원
- from 절의 서브쿼리는 현재 JPQL에서 불가능

  • JpaMain7.java 생성
public class JpaMain7 {

	public static void main(String[] args) {
		
		EntityManagerFactory emf = Persistence.createEntityManagerFactory("hello");
		EntityManager em = emf.createEntityManager();
		EntityTransaction tx = em.getTransaction();
		tx.begin();
		
		try {
			Member member = new Member();
			member.setUsername("memeber");
			member.setAge(10);
			em.persist(member);
			
			em.flush();
			em.clear();
			
			String sql = "select m from Member m where m.age > (select avg(m2.age) from Member m2)";
			List<Member> resultList = em.createQuery(sql, Member.class).getResultList();
			
			tx.commit();
		} catch (Exception e) {
			tx.rollback();
		} finally {
			em.close();
			emf.close();
		}
	}
}

  • 결과
profile
김동명
코딩공부
이전 포스트

java 스프링부트 ( spring boot ) / JPQL

다음 포스트

java 스프링부트 ( spring boot ) / JPA 활용예제

0개의 댓글