티스토리 뷰

수업/└SpringBoot

[02]Spring Data JPA

onlyun 2022. 3. 21. 10:04

 

간단한 개념은 앞선 글에 정리.

Item 클래스를 가지고 보다 심화된 내용 정리할 것.

 

기본으로 만들어주는 쿼리는 복잡한 것 사용하기 어려움. WHERE의 여러 조건문들을(?)

그래서 자동으로 만들어주는 쿼리 메소드 대신 직접 만들어서 사용하는 쿼리?

 

간단한 쿼리는 JPA - CRUD 같은 거는 엄청 편함

근데 쿼리가 복잡해지면... Mybatis 사용이 편하기도?

 

repository에서 작업하는 것은 서비스단 작업 - 앞쪽 작업.

 

♣ 원래 쿼리 메소드는 동적 처리 불가 → Annotation으로 동적 쿼리 작업

♣ 동적 쿼리를 위한 dsl 사용

    동적쿼리 생성. 쿼리 재사용할 수 있어 제약조건 조립 및 가독성 향상/

    문자열 x 자바 소스코드로 작성하기 때문에 컴파일 시점에서 오류 발견 가능. 자동완성기능 사용가능

♣ 연관관계 매핑

♣ 시큐리티(Security) - 수요일?

 

 

2. Member 만들어서 실습

(0) 만약 프로젝트 만들 때, 필요한 라이브러리를 선택하지 않았다면

pom.xml에 추가하면 된다. maven repository에서 검색해서

<!-- https://mvnrepository.com/artifact/org.springframework.data/spring-data-jpa -->
<dependency>
    <groupId>org.springframework.data</groupId>
    <artifactId>spring-data-jpa</artifactId>
</dependency>

JPA 선택했는데 누락되어 있어서 테이블 생성이 안 됐던 것임. 위의 것을 추가해줘야 JPA 테이블 생성할 수 있음.

 

(1) 클래스 Member  생성

-위치 : com.wsy.demojpa01.domain

package com.wsy.demojpa01.domain;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.EnumType;
import javax.persistence.Enumerated;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

import com.wsy.demojpa01.constant.Role;

import lombok.Data;
import lombok.Getter;
import lombok.Setter;
import lombok.ToString;

@Data
@Entity
@Table(name="member")
@Getter @Setter @ToString
public class Member {
	@Id  //pk
	@Column(name="member_id")
	@GeneratedValue(strategy = GenerationType.IDENTITY) //pk 자동 생성될 때, 전략 - AutoIncrement 
	private Long id;
	
	private String password;
	
	private String name;
	
	private String address;
	
	@Column(unique=true)
	private String email;
		
	@Enumerated(EnumType.STRING)
	private Role role;
}

(1-1) enum 생성

package com.wsy.demojpa01.constant;

public enum Role {
	USER, ADMIN
}

(1-3)  MemberRepository01 인터페이스 생성

package com.wsy.demojpa01.repository;

import org.springframework.data.jpa.repository.JpaRepository;

import com.wsy.demojpa01.domain.Member;

public interface MemberRepository01 extends JpaRepository<Member, Long>{

}

 

(2) MemberTest 클래스 생성 해 테스트

-위치 : src/test/java

(3-1) insert 데이터 입력/추가

package com.wsy.demojpa01.repository;

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.TestPropertySource;

import com.wsy.demojpa01.constant.Role;
import com.wsy.demojpa01.domain.Member;

@SpringBootTest
@TestPropertySource(locations="classpath:application.properties")
public class MemberTest {
	
	@Autowired
	MemberRepository01 memberRepository;
	
	@Test
	public void createMemberTest() {
		Member member = new Member();
		member.setName("홍길동");
		member.setEmail("hong@gmail.com");
		member.setPassword("1234");
		member.setAddress("부산 부산진구");
		member.setRole(Role.ADMIN);
		memberRepository.save(member);
	}
	@Test
	public void createMemberListTest() {
		for(int i=0; i<5; i++) {
			Member member = new Member();
			member.setName("고길동"+i);
			member.setEmail("go"+i+"@gmail.com");
			member.setPassword("1234");
			member.setAddress("부산 부산진구"+i);
			member.setRole(Role.USER);
			memberRepository.save(member);
		}
	}
	@Test
	public void createMemberListTest02() {
		for(int i=5; i<10; i++) {
			Member member = new Member();
			member.setName("희동이"+i);
			member.setEmail("go"+i+"@gmail.com");
			member.setPassword("1234");
			member.setAddress("부산 부산진구"+i);
			member.setRole(Role.USER);
			memberRepository.save(member);
		}
	}
}

└각각 junit 테스트 후 데이터가 제대로 들어갔는지 확인

(3-2) 데이터 가져오기 - MemberTest.java

//리스트 전체 가져오기
@Test
public void getMemberList() {
	List<Member> members = memberRepository.findAll(); //findAll은 만들지 않아도 쓸 수 있음.
	for(int i = 0; i<members.size(); i++) {
		System.out.println(members.get(i)); //toString이 있기 때문에 이것만 써도 됨.
	}
}

//데이터 하나 가져오기
@Test
public void getMember2() {
	Member member2 = memberRepository.findById(2L).get();
	System.out.println(member2);
}
Member(id=1, password=1234, name=고길동0, address=부산 부산진구0, email=go0@gmail.com, role=USER)
Member(id=2, password=1234, name=고길동1, address=부산 부산진구1, email=go1@gmail.com, role=USER)
Member(id=3, password=1234, name=고길동2, address=부산 부산진구2, email=go2@gmail.com, role=USER)
Member(id=4, password=1234, name=고길동3, address=부산 부산진구3, email=go3@gmail.com, role=USER)
Member(id=5, password=1234, name=고길동4, address=부산 부산진구4, email=go4@gmail.com, role=USER)
Member(id=6, password=1234, name=홍길동, address=부산 부산진구, email=hong@gmail.com, role=ADMIN)
Member(id=7, password=1234, name=희동이5, address=부산 부산진구5, email=go5@gmail.com, role=USER)
Member(id=8, password=1234, name=희동이6, address=부산 부산진구6, email=go6@gmail.com, role=USER)
Member(id=9, password=1234, name=희동이7, address=부산 부산진구7, email=go7@gmail.com, role=USER)
Member(id=10, password=1234, name=희동이8, address=부산 부산진구8, email=go8@gmail.com, role=USER)
Member(id=11, password=1234, name=희동이9, address=부산 부산진구9, email=go9@gmail.com, role=USER)
2022-03-21 11:07:39.346  INFO 5208 --- [ionShutdownHook] j.LocalContainerEntityManagerFactoryBean : Closing JPA EntityManagerFactory for persistence unit 'default'
2022-03-21 11:07:39.349  INFO 5208 --- [ionShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown initiated...
2022-03-21 11:07:39.371  INFO 5208 --- [ionShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown completed.
Member(id=2, password=1234, name=고길동1, address=부산 부산진구1, email=go1@gmail.com, role=USER)
2022-03-21 11:17:54.821  INFO 10552 --- [ionShutdownHook] j.LocalContainerEntityManagerFactoryBean : Closing JPA EntityManagerFactory for persistence unit 'default'
2022-03-21 11:17:54.826  INFO 10552 --- [ionShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown initiated...
2022-03-21 11:17:54.841  INFO 10552 --- [ionShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown completed.

 

(3-3) 업데이트/딜리트

	//updqte
	@Test
	public void updateMemberTest() {
		Member member = new Member();
		member.setId(1L); //PK //member_id 1번 수정
		member.setName("둘리"); //변경
		member.setEmail("ddd@gmail.com"); //변경
		member.setPassword("1234");
		member.setAddress("제주 서귀포시"); //변경
		member.setRole(Role.ADMIN);
		memberRepository.save(member);
	}

//delete
//delete
	@Test
	public void delete() {
		Member member = new Member();
		member.setId(8L);
		memberRepository.delete(member);
		memberRepository.deleteById(9L); //mebmer_id 9번 지우겠다.
	}

>> MySQL 에서 확인

└1번 변경되었고 8번과 9번이 삭제된 것을 알 수 있다.

 

>> repository에 아무것도 작성하지 않고

데이터 입력/수정/삭제 했음. 기본 제공 쿼리로

특정한 쿼리를 사용하고 싶다면, 추가해주면 된다.

-특정 열의 데이터를 가져오는 것. 기본적으로 제공하지 않는.

-[ findBy~ ] WHERE절

(3-4) repository에 쿼리 추가해서 사용

package com.wsy.demojpa01.repository;

import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;

import com.wsy.demojpa01.domain.Member;

public interface MemberRepository01 extends JpaRepository<Member, Long>{

	public List<String> findByName();
	public List<String> findByEmail();
}

 

>> 수정

이름과 이메일을 찾는 쿼리문을 찾을 수 있는 이유. 디비 컬럼에 있으니까!

package com.wsy.demojpa01.repository;

import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;

import com.wsy.demojpa01.domain.Member;

public interface MemberRepository01 extends JpaRepository<Member, Long>{
	
	//select * from member where name = ${name}
	public List<Member> findByName(String name);
}

 

//특정한 데이터만 가져오기. 쿼리문 추가
@Test
public void name() {
	List<Member> members = memberRepository.findByName("홍길동"); //repository에서 추가한 것
	for(Member member:members) {
		System.out.println(member);
	}
}

//
@Test
public void name_email() {
	List<Member> members = memberRepository.findByName("홍길동"); //repository에서 추가한 것
	for(Member member:members) {
		System.out.println(member);
	}
	List<Member> members2 = memberRepository.findByEmail("ddd@gmail.com"); //repository에서 추가한 것
	for(Member member22:members2) {
		System.out.println(member22);
	}
}

이름이 홍길동인 사람을 찾아 List 형식으로 데이터 출력

Member(id=6, password=1234, name=홍길동, address=부산 부산진구, email=hong@gmail.com, role=ADMIN)
Member(id=1, password=1234, name=둘리, address=제주 서귀포시, email=ddd@gmail.com, role=ADMIN)

 

-MemberRepository.java

package com.wsy.demojpa01.repository;

import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;

import com.wsy.demojpa01.domain.Member;

public interface MemberRepository01 extends JpaRepository<Member, Long>{
	//컬럼명에 없는 것은 사용할 수 없음. findByNames 같은 거.
	//WHERE절
	//select * from member where name = ${name}
	public List<Member> findByName(String name);
//	public List<Member> findByEmail();
	
	//select * from member where email = ${name}
	public List<Member> findByEmail(String email);
	
	//AND절
	//select * from member where name = ${name} and email = ${name}
	public List<Member> findByNameAndEmail(String name, String email);
	
	//OR절
	//select * from member where name = ${name} Or email = ${name}
	public List<Member> findByNameOrEmail(String name, String email);
	
	//LIKE
	//select * from member where name Like '%홍길동%'
	public List<Member> findByNameLike(String name);
	
	//IN
	//select * from member where name in('고길동1', '고길동3')
	public List<Member> findByNameIn(String...names); //보낼 때, 배열로 보내면 됨.
    
	//정렬
	//select * from member order by id desc;
	public List<Member> findAllByOrderByIdDesc();
}

-MemberTest.java : 

package com.wsy.demojpa01.repository;

import java.util.List;

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.TestPropertySource;

import com.wsy.demojpa01.constant.Role;
import com.wsy.demojpa01.domain.Member;

@SpringBootTest
@TestPropertySource(locations="classpath:application.properties")
public class MemberTest {
	@Autowired
	MemberRepository01 memberRepository;
...
	@Test
	public void nameAndEmail() {
		List<Member> nae = memberRepository.findByNameAndEmail("둘리", "ddd@gmail.com");
		for(Member m : nae) {
			System.out.println(m);
		}
	}
	@Test
	public void nameOrEmail() {
		List<Member> aoe = memberRepository.findByNameOrEmail("둘리", "hong@gmail.com");
		for(Member m2 : aoe) {
			System.out.println(m2);
		}
	}
	@Test
	public void nameLikeEmail() {
		List<Member> nle = memberRepository.findByNameLike("%희동이%");
		for(Member m : nle) {
			System.out.println(m);
		}
	}
	
	@Test
    //배열로 받아오면 된대
	public void nameIn() {
		/*
		 * List<Member>[] ni = memberRepository.findByName("고길동1", "고길동2"); for(Member m
		 * : ni) { System.out.println(m); }
		 */
		String[] names = {"희동이9", "고길동1"};
		List<Member> members = memberRepository.findByNameIn(names);
		for(Member m:members) {
			System.out.println(m);
		}
	}
    
    //oreder by - 전체 데이터 가져와서 id로 역순 정렬
	@Test
	public void orderby() {
//		List<Member> members = memberRepository.findAllByOrderByIdDesc();
		List<Member> members = memberRepository.findAll(Sort.by(Sort.Direction.DESC, "id")); //(정렬방향, 정렬기준)
		//findAll 자동완성 중에 (Sort sort) 선택해서 위와 같이 입력할 수 있음.
		for(Member m : members) {
			System.out.println(m);
		}		
	}
}

└정렬할 때 둘 중 어느걸 사용해도 같은 결과.

 

>> 콘솔 결과 출력 : 

findByNameAndEmail
Member(id=1, password=1234, name=둘리, address=제주 서귀포시, email=ddd@gmail.com, role=ADMIN)
findByNameOrEmail
Member(id=1, password=1234, name=둘리, address=제주 서귀포시, email=ddd@gmail.com, role=ADMIN)
Member(id=6, password=1234, name=홍길동, address=부산 부산진구, email=hong@gmail.com, role=ADMIN)
findByNameLike
Member(id=7, password=1234, name=희동이5, address=부산 부산진구5, email=go5@gmail.com, role=USER)
Member(id=10, password=1234, name=희동이8, address=부산 부산진구8, email=go8@gmail.com, role=USER)
Member(id=11, password=1234, name=희동이9, address=부산 부산진구9, email=go9@gmail.com, role=USER)
findByNameIn
Member(id=2, password=1234, name=고길동1, address=부산 부산진구1, email=go1@gmail.com, role=USER)
Member(id=11, password=1234, name=희동이9, address=부산 부산진구9, email=go9@gmail.com, role=USER)
findallByOrderByIdDesc
Member(id=11, password=1234, name=희동이9, address=부산 부산진구9, email=go9@gmail.com, role=USER)
Member(id=10, password=1234, name=희동이8, address=부산 부산진구8, email=go8@gmail.com, role=USER)
Member(id=7, password=1234, name=희동이5, address=부산 부산진구5, email=go5@gmail.com, role=USER)
Member(id=6, password=1234, name=홍길동, address=부산 부산진구, email=hong@gmail.com, role=ADMIN)
Member(id=5, password=1234, name=고길동4, address=부산 부산진구4, email=go4@gmail.com, role=USER)
Member(id=4, password=1234, name=고길동3, address=부산 부산진구3, email=go3@gmail.com, role=USER)
Member(id=3, password=1234, name=고길동2, address=부산 부산진구2, email=go2@gmail.com, role=USER)
Member(id=2, password=1234, name=고길동1, address=부산 부산진구1, email=go1@gmail.com, role=USER)
Member(id=1, password=1234, name=둘리, address=제주 서귀포시, email=ddd@gmail.com, role=ADMIN)
-있는 것- findAll(Sort, sort)
Member(id=11, password=1234, name=희동이9, address=부산 부산진구9, email=go9@gmail.com, role=USER)
Member(id=10, password=1234, name=희동이8, address=부산 부산진구8, email=go8@gmail.com, role=USER)
Member(id=7, password=1234, name=희동이5, address=부산 부산진구5, email=go5@gmail.com, role=USER)
Member(id=6, password=1234, name=홍길동, address=부산 부산진구, email=hong@gmail.com, role=ADMIN)
Member(id=5, password=1234, name=고길동4, address=부산 부산진구4, email=go4@gmail.com, role=USER)
Member(id=4, password=1234, name=고길동3, address=부산 부산진구3, email=go3@gmail.com, role=USER)
Member(id=3, password=1234, name=고길동2, address=부산 부산진구2, email=go2@gmail.com, role=USER)
Member(id=2, password=1234, name=고길동1, address=부산 부산진구1, email=go1@gmail.com, role=USER)
Member(id=1, password=1234, name=둘리, address=제주 서귀포시, email=ddd@gmail.com, role=ADMIN)

 

게시판에서 데이터 일부만 가져오는 것. Controller에서 작업.

(3) Pageable

Query 메소드의 입력 변수. Pageable 변수를 추가하면 Page 타입을 반환형으로 사용

Pageable 객체를 통해 페이징과 정렬을 위한 파라미터 전달

-page : 몇번째 페이지 인지를 전달

-size : 한 페이지에 몇 개의 항목을 보여줄 것인지 전달

-sort : 정렬정보 전달. 정렬정보는 필드 이름, 정렬방향의 포맷으로 전달. 여러 필드로 순차적으로 정렬 가능

ex) sort=createdAt,desc&sort=userId,asc

*매핑할 때, GET은 안 적어도 됨. POST는 반드시 전송 메소드 적어줘야 함.

RequestMapping("") 안 적으면 GET, POST일 경우는 반드시 적어줘야함 : RequestMapping(value="post")

 

(3-1) MemberTest.java

//pageable - 레코드 일부만 가져오기 !pageDTO 안 만들어돔(PageVO를 말하는 건가?)
@Test
public void pageTest() {
	//여러 pageable 중에서 domain인 거 가져옴
	Pageable pageable = PageRequest.of(0, 5);
	Page<Member> memberPage = memberRepository.findAll(pageable);
	//그냥 쓰는게 아니라 페이지를 뽑아내야 함
	List<Member> members = memberPage.getContent();
	for(Member m : members) {
		System.out.println(m);
	}
}

└한 페이지에 출력될 것이 0번부터 5번이란 건가?

>> junit 테스트 결과

Member(id=1, password=1234, name=둘리, address=제주 서귀포시, email=ddd@gmail.com, role=ADMIN)
Member(id=2, password=1234, name=고길동1, address=부산 부산진구1, email=go1@gmail.com, role=USER)
Member(id=3, password=1234, name=고길동2, address=부산 부산진구2, email=go2@gmail.com, role=USER)
Member(id=4, password=1234, name=고길동3, address=부산 부산진구3, email=go3@gmail.com, role=USER)
Member(id=5, password=1234, name=고길동4, address=부산 부산진구4, email=go4@gmail.com, role=USER)

 

 

3. 실습

domain 패키지의 Item 가지고 배운 내용 복습

(0) 기존의 Item.java

package com.wsy.demojpa01.domain;

import java.time.LocalDateTime;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.EnumType;
import javax.persistence.Enumerated;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Lob;
import javax.persistence.Table;

import org.hibernate.annotations.CreationTimestamp;
import org.hibernate.annotations.UpdateTimestamp;
import org.springframework.format.annotation.DateTimeFormat;

import com.fasterxml.jackson.annotation.JsonFormat;
import com.wsy.demojpa01.constant.ItemSellstatus;

import lombok.Getter;
import lombok.Setter;
import lombok.ToString;

@Entity
@Table(name="item")
@Getter @Setter @ToString
public class Item {
	
	@Id
	@Column(name="item_id")
	@GeneratedValue(strategy = GenerationType.AUTO) //시퀀스 생성한다.
	private Long id;
	
	@Column(nullable = false, length=50)
	private String itemNm;
	
	@Column(name ="price", nullable = false)
	private int price;
	
	@Column(nullable = false)
	private int stockNumber;
	
	@Lob // BLOB, CLOB 형태로 데이터를 저장한다.
	@Column(nullable = false)
	private String itemDetail;
	
	@Enumerated(EnumType.STRING)
	private ItemSellstatus itemSellStatus;
	
//	@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone="Asia/Seoul")
	@DateTimeFormat(pattern="yyyy-MM-dd'T'HH:mm:ss")
	@CreationTimestamp //생성 날짜 자동
	private LocalDateTime regTime;
	
//	@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone="Asia/Seoul")
	@DateTimeFormat(pattern="yyyy-MM-dd'T'HH:mm:ss")
	@UpdateTimestamp //수정날짜 자동 갱신
	private LocalDateTime updateTime;
}

 

(1) ItemRepository 생성

package com.wsy.demojpa01.repository;

import org.springframework.data.jpa.repository.JpaRepository;

import com.wsy.demojpa01.domain.Item;

public interface ItemRepository extends JpaRepository<Item, Long> {
	//데이터 저장, list 조회, entity 1개 조회, 수정, 삭제 작성
	

}

-테스트에서 함수를 만들어서 Repository에 자동생성(create)되도록해도 되고

Repository에서 미리 만들어둬서 ItemTest에서 자동 완성되도록 할 수도 있음. 본인 선택

 

(2) ItemTest.java

package com.wsy.demojpa01.repository;

import java.util.List;

import org.junit.jupiter.api.DisplayName;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.TestPropertySource;

import com.wsy.demojpa01.constant.ItemSellstatus;
import com.wsy.demojpa01.domain.Item;

@SpringBootTest
@TestPropertySource(locations="classpath:application.properties")
public class ItemTest {
	@Autowired
	ItemRepository itemRepository;
	
	//Item
	
	@Test
	public void createItem() {
		Item item = new Item();
		item.setItemDetail("test");
		item.setItemNm("test");
		item.setPrice(50000);
		item.setItemSellStatus(ItemSellstatus.SOLD_OUT);
		item.setStockNumber(1234);
//		item.setRegTime(LocalDateTime.now()); 
//		item.setUpdateTime(LocalDateTime.now());
		//domain 패키지 폴더의 Item 클래스에 createTimeStamp 사용하지 않을 경우 이렇게 넣어주면 됨.
		itemRepository.save(item);
	}
	//여러 개 입력
	@Test
	public void createItemList() {
		for(int i =0; i<10; i++) {
			Item item = new Item();
			item.setItemNm("상품번호"+i);
			item.setItemDetail("상품설명"+i);
			item.setPrice(500000);
			item.setStockNumber(i);
			item.setItemSellStatus(ItemSellstatus.SELL);
			itemRepository.save(item);
		}
		
	}
	//조회
	@Test
	public void getListItem() {
		List<Item> iLists = itemRepository.findAll();
		for(int i = 0; i<iLists.size(); i++) {
			System.out.println(iLists.get(i));
		}
	}
	//하나 조회 ??
	@Test
	public void getItemOne() {
		Item item = itemRepository.findById(1L).get();
		System.out.println(item);
	}
	//Item(id=1, itemNm=0000, price=100000, stockNumber=9876, itemDetail=상품설명예제, itemSellStatus=SELL, regTime=null, updateTime=2022-03-21T14:07:43.983720)
	
	//update
	@Test
	public void updateItem() {
		Item item = new Item();
		item.setId(1L);
		item.setItemDetail("상품설명예제");
		item.setItemNm("0000");
		item.setPrice(100000);
		item.setItemSellStatus(ItemSellstatus.SELL);
		item.setStockNumber(9876);
		itemRepository.save(item);
	}
	
	//delete
	@Test
	public void deleteItem() {
		Item item = new Item();
		item.setId(6L);
		itemRepository.delete(item);
	}
	
	//특정 상품 조회
	@Test
	@DisplayName("상품명 조회 테스트")
	public void findByItemNmTest() {
		List<Item> itemList = itemRepository.findByItemNm("상품번호1");
		for(Item item : itemList) {
			System.out.println(item);
		}
	}
	//결과 Item(id=3, itemNm=상품번호1, price=1, stockNumber=1, itemDetail=상품설명1, itemSellStatus=SELL, regTime=2022-03-21T14:02:46.486364, updateTime=2022-03-21T14:02:46.486364)
	
	//OR
	@Test
	@DisplayName("상품명, 상품상세설명 or 테스트")
	public void findByItemNmOrItemDetailTest() {
		List<Item> itemList = itemRepository.findByItemNmOrItemDetail("상품번호9", "상품설명5");
		for(Item item : itemList) {
			System.out.println(item.toString());
		}
	}
	/*
	 Item(id=18, itemNm=상품번호5, price=500000, stockNumber=5, itemDetail=상품설명5, itemSellStatus=SELL, regTime=2022-03-21T14:25:17.918709, updateTime=2022-03-21T14:25:17.918709)
	 Item(id=22, itemNm=상품번호9, price=500000, stockNumber=9, itemDetail=상품설명9, itemSellStatus=SELL, regTime=2022-03-21T14:25:17.959530, updateTime=2022-03-21T14:25:17.959530)
	 * */
	
	//
	@Test
	@DisplayName("가격 LessThan 테스트")
	public void findByPriceLessThanTest() {
//		this.createItemList(); //가상데이터베이스 사용해서 테스트할 때 사용
		List<Item> itemList = itemRepository.findByPriceLessThan(450000);
		for(Item item : itemList) {
			System.out.println(item.toString());
		}
	}
	//Item(id=1, itemNm=0000, price=100000, stockNumber=9876, itemDetail=상품설명예제, itemSellStatus=SELL, regTime=null, updateTime=2022-03-21T14:07:43.983720)
	
	@Test
	@DisplayName("가격 GreaterThanEqual 테스트")
	public void findByPriceGreaterThanEqualTest() {
		List<Item> itemList = itemRepository.findByPriceGreaterThanEqual(500000);
		for(Item item : itemList) {
			System.out.println(item.toString());
		}
	}
	/*
Item(id=13, itemNm=상품번호0, price=500000, stockNumber=0, itemDetail=상품설명0, itemSellStatus=SELL, regTime=2022-03-21T14:25:17.840744, updateTime=2022-03-21T14:25:17.840744)
Item(id=14, itemNm=상품번호1, price=500000, stockNumber=1, itemDetail=상품설명1, itemSellStatus=SELL, regTime=2022-03-21T14:25:17.873729, updateTime=2022-03-21T14:25:17.873729)
Item(id=15, itemNm=상품번호2, price=500000, stockNumber=2, itemDetail=상품설명2, itemSellStatus=SELL, regTime=2022-03-21T14:25:17.884724, updateTime=2022-03-21T14:25:17.884724)
Item(id=16, itemNm=상품번호3, price=500000, stockNumber=3, itemDetail=상품설명3, itemSellStatus=SELL, regTime=2022-03-21T14:25:17.894720, updateTime=2022-03-21T14:25:17.894720)
Item(id=17, itemNm=상품번호4, price=500000, stockNumber=4, itemDetail=상품설명4, itemSellStatus=SELL, regTime=2022-03-21T14:25:17.907714, updateTime=2022-03-21T14:25:17.907714)
Item(id=18, itemNm=상품번호5, price=500000, stockNumber=5, itemDetail=상품설명5, itemSellStatus=SELL, regTime=2022-03-21T14:25:17.918709, updateTime=2022-03-21T14:25:17.918709)
Item(id=19, itemNm=상품번호6, price=500000, stockNumber=6, itemDetail=상품설명6, itemSellStatus=SELL, regTime=2022-03-21T14:25:17.928705, updateTime=2022-03-21T14:25:17.928705)
Item(id=20, itemNm=상품번호7, price=500000, stockNumber=7, itemDetail=상품설명7, itemSellStatus=SELL, regTime=2022-03-21T14:25:17.940700, updateTime=2022-03-21T14:25:17.940700)
Item(id=21, itemNm=상품번호8, price=500000, stockNumber=8, itemDetail=상품설명8, itemSellStatus=SELL, regTime=2022-03-21T14:25:17.949695, updateTime=2022-03-21T14:25:17.949695)
Item(id=22, itemNm=상품번호9, price=500000, stockNumber=9, itemDetail=상품설명9, itemSellStatus=SELL, regTime=2022-03-21T14:25:17.959530, updateTime=2022-03-21T14:25:17.959530)
	*/
	
	@Test
	@DisplayName("상품명 내림차순 정렬")
	public void orderByItem() {
		List<Item> items = itemRepository.findAllByOrderByIdDesc();
//		List<Item> items = itemRepository.findAll(Sort.by(Sort.Direction.DESC), "id");
		for(Item item:items) {
			System.out.println(item);
		}
	}
/*
Item(id=22, itemNm=상품번호9, price=500000, stockNumber=9, itemDetail=상품설명9, itemSellStatus=SELL, regTime=2022-03-21T14:25:17.959530, updateTime=2022-03-21T14:25:17.959530)
Item(id=21, itemNm=상품번호8, price=500000, stockNumber=8, itemDetail=상품설명8, itemSellStatus=SELL, regTime=2022-03-21T14:25:17.949695, updateTime=2022-03-21T14:25:17.949695)
Item(id=20, itemNm=상품번호7, price=500000, stockNumber=7, itemDetail=상품설명7, itemSellStatus=SELL, regTime=2022-03-21T14:25:17.940700, updateTime=2022-03-21T14:25:17.940700)
Item(id=19, itemNm=상품번호6, price=500000, stockNumber=6, itemDetail=상품설명6, itemSellStatus=SELL, regTime=2022-03-21T14:25:17.928705, updateTime=2022-03-21T14:25:17.928705)
Item(id=18, itemNm=상품번호5, price=500000, stockNumber=5, itemDetail=상품설명5, itemSellStatus=SELL, regTime=2022-03-21T14:25:17.918709, updateTime=2022-03-21T14:25:17.918709)
Item(id=17, itemNm=상품번호4, price=500000, stockNumber=4, itemDetail=상품설명4, itemSellStatus=SELL, regTime=2022-03-21T14:25:17.907714, updateTime=2022-03-21T14:25:17.907714)
Item(id=16, itemNm=상품번호3, price=500000, stockNumber=3, itemDetail=상품설명3, itemSellStatus=SELL, regTime=2022-03-21T14:25:17.894720, updateTime=2022-03-21T14:25:17.894720)
Item(id=15, itemNm=상품번호2, price=500000, stockNumber=2, itemDetail=상품설명2, itemSellStatus=SELL, regTime=2022-03-21T14:25:17.884724, updateTime=2022-03-21T14:25:17.884724)
Item(id=14, itemNm=상품번호1, price=500000, stockNumber=1, itemDetail=상품설명1, itemSellStatus=SELL, regTime=2022-03-21T14:25:17.873729, updateTime=2022-03-21T14:25:17.873729)
Item(id=13, itemNm=상품번호0, price=500000, stockNumber=0, itemDetail=상품설명0, itemSellStatus=SELL, regTime=2022-03-21T14:25:17.840744, updateTime=2022-03-21T14:25:17.840744)
Item(id=1, itemNm=0000, price=100000, stockNumber=9876, itemDetail=상품설명예제, itemSellStatus=SELL, regTime=null, updateTime=2022-03-21T14:07:43.983720) 
  */
}

 

>> 근데,

업데이트할 때, 현재등록시간이 초기화가 되버림.

>> 해결방법 : 

 

Spring DATA JPA @Query Annotation

실제 쿼리를 사용하는 것. SQL과 유사한 JPQL이라는 객체지향 쿼리 언어 사용

(문법을 잘 지켜야 해서...기본 제공 문법은 쓰면 안 됨, 사용자 지정 쿼리를 만들 때)

@Query("select i from Item i where i.itemDetail like %:itemDetail% order by i.price desc") 
List<Item> findByItemDetail(@Param("itemDetail") String itemDetail);
//쿼리 안에 엔티티 값이 들어감.

//Native Query쓸 때, 이블에 있는 실제 테이블 이름 사용
// 파라매터 값에 itemDetail이 아니라 ? 사용해야함.
//@Query(value="select * from Item i where i.item_detail like %:itemDetail% order by i.price desc", nativeQuery=true) 
@Query(value="select * from Item i where i.item_detail like %:?% order by i.price desc", nativeQuery=true) 
List<Item> findByItemDetailByNative(@Param("itemDetail") String itemDetail);

-% : like

 

(0)ItemRepository

package com.wsy.demojpa01.repository;

import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.wsy.demojpa01.domain.Item;

public interface ItemRepository extends JpaRepository<Item, Long> {
	//데이터 저장, list 조회, entity 1개 조회, 수정, 삭제 작성

	List<Item> findByItemNm(String string);

	List<Item> findByPriceLessThan(int i);

	List<Item> findByItemNmOrItemDetail(String string, String string2);

	List<Item> findByPriceGreaterThanEqual(int i);

	List<Item> findAllByOrderByIdDesc();

	//★ Spring Data JPA @Query 사용
	@Query(value="select * from item", nativeQuery = true) //실제 쿼리를 사용한다는 뜻.
	List<Item> getListAA();
                                    //테이블 item_detail / 필드명 itemDetail ★
    @Query("select i from Item i where i.itemDetail like %:itemDetail% order by i.price desc")
	List<Item> selectItemDetailNative(@Param("itemDetail") String itemDetail);
}

 

(0) ItemTest

@Test
@DisplayName("@Query를 이용한 상품 조회 테스트")
public void findByItemDetailTest() {
    this.createItemList();
    List<Item> itemList = itemRepository.selectItemDetailNative("상품설명2");
    for (Item item : itemList) {
        System.out.println(item.toString());
    }
}

/*
Item(id=15, itemNm=상품번호2, price=500000, stockNumber=2, itemDetail=상품설명2, itemSellStatus=SELL, regTime=2022-03-21T14:25:17.884724, updateTime=2022-03-21T14:25:17.884724)
Item(id=25, itemNm=상품번호2, price=500000, stockNumber=2, itemDetail=상품설명2, itemSellStatus=SELL, regTime=2022-03-21T15:56:11.033004, updateTime=2022-03-21T15:56:11.033004)
*/

 

 

4. Spring Data JPA Querydsl

동적쿼리 생성

 쿼리 재사용할 수 있어 제약조건 조립 및 가독성 향상

 문자열이 아닌 자바 소스코드로 작성하기 때문에 컴파일 시점 오류 발견

 IDE 도움을 받아서 자동 완성 기능 사용

(1) 환경설정

(1-1) pom.xml - dependecies에 querydsl-jap와 querydsl-apt 코드를 넣고 / plugin에 apt-maven-plugin을 넣어줘야함.

<!-- 동적쿼리 사용을 위한 코드 추가 -->
<dependencies>
	<dependency>
		<groupId>com.querydsl</groupId>
		<artifactId>querydsl-jpa</artifactId>
	</dependency>

	<dependency>
		<groupId>com.querydsl</groupId>
		<artifactId>querydsl-apt</artifactId>
	</dependency>
</dependencies>
...
<plugins>
	<plugin>
		<groupId>com.mysema.maven</groupId>
		<artifactId>apt-maven-plugin</artifactId>
		<version>1.1.3</version>
		<executions>
			<execution>
				<goals>
					<goal>process</goal>
				</goals>
				<configuration>
					<outputDirectory>target/generated-sources/java</outputDirectory>
					<processor>com.querydsl.apt.jpa.JPAAnnotationProcessor</processor>
				</configuration>
			</execution>
		</executions>
	</plugin>
</plugins>

(1-2) ItemRepository

public interface ItemRepository extends JpaRepository<Item, Long>, QuerydslPredicateExecutor<Item>{
	                    //QuerydslPredicateExecutor<Item> 이거 추가 ↑
}

 

(2) Querydsl 테스트

(2-1) ItemTest

프로그램. 코드에서 바로 디비에 연결해서 사용. 자동완성 기능이라 오타 줄이고 사용이 편이.

package com.wsy.demojpa01.repository;

import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;

import org.junit.jupiter.api.DisplayName;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.TestPropertySource;

import com.querydsl.core.BooleanBuilder;
import com.querydsl.jpa.impl.JPAQuery;
import com.querydsl.jpa.impl.JPAQueryFactory;
import com.wsy.demojpa01.constant.ItemSellstatus;
import com.wsy.demojpa01.domain.Item;
import com.wsy.demojpa01.domain.QItem;

@SpringBootTest
@TestPropertySource(locations="classpath:application.properties")
public class ItemTest {
	@Autowired
	ItemRepository itemRepository;
	
	//4. dsl사용하기
	@PersistenceContext
	EntityManager em;  //실제 사용할 때, Service에 넣음 - 서비스단
    
	@Test
	@DisplayName("상품 Querydsl 조회 테스트")
	public void queryDslTest() {
		//boolean 검색조건
//		BooleanBuilder builder = new BooleanBuilder(); //빌드 만들었음.
		JPAQueryFactory queryFactory = new JPAQueryFactory(em); //엔티티 매니저를 매개변수로 넣음.
		QItem qitem = QItem.item; //쿼리 라이브러리 넣으면 자동 생성
		JPAQuery<Item> query = queryFactory.selectFrom(qitem) //셀렉트 쿼리를 사용할 거란 뜻.
											//select * from item
				.where(qitem.itemSellStatus.eq(ItemSellstatus.SELL))
				//WHERE item_sell_status = 'SELL'
				.where(qitem.itemDetail.like("%"+"설명"+"%")) // 공통 쿼리. 이후에 추가 쿼리를 점(.)을 이용해 붙일 수 있음. 점(.)은 AND
				.orderBy(qitem.price.desc()); //세미콜론(;)을 찍지 않으면 계속 이어진다는 의미.
		List<Item> items = query.fetch(); //fetch는 모든 내용을 넣는다. 어디에? items에?
							//fetch : 결과를 받아오는 것. resultSet re = query.execute()
		//결과를 받아와 List 형태의 items에 넣음.
		for(Item item : items) {
			System.out.println(item.toString());
		}
	}
...
}

%설명%인 것 모두 출력

 

(2-2) junit 테스트

Item(id=13, itemNm=상품번호0, price=500000, stockNumber=0, itemDetail=상품설명0, itemSellStatus=SELL, regTime=2022-03-21T14:25:17.840744, updateTime=2022-03-21T14:25:17.840744)
Item(id=14, itemNm=상품번호1, price=500000, stockNumber=1, itemDetail=상품설명1, itemSellStatus=SELL, regTime=2022-03-21T14:25:17.873729, updateTime=2022-03-21T14:25:17.873729)
Item(id=15, itemNm=상품번호2, price=500000, stockNumber=2, itemDetail=상품설명2, itemSellStatus=SELL, regTime=2022-03-21T14:25:17.884724, updateTime=2022-03-21T14:25:17.884724)
Item(id=16, itemNm=상품번호3, price=500000, stockNumber=3, itemDetail=상품설명3, itemSellStatus=SELL, regTime=2022-03-21T14:25:17.894720, updateTime=2022-03-21T14:25:17.894720)
Item(id=17, itemNm=상품번호4, price=500000, stockNumber=4, itemDetail=상품설명4, itemSellStatus=SELL, regTime=2022-03-21T14:25:17.907714, updateTime=2022-03-21T14:25:17.907714)
Item(id=18, itemNm=상품번호5, price=500000, stockNumber=5, itemDetail=상품설명5, itemSellStatus=SELL, regTime=2022-03-21T14:25:17.918709, updateTime=2022-03-21T14:25:17.918709)
Item(id=19, itemNm=상품번호6, price=500000, stockNumber=6, itemDetail=상품설명6, itemSellStatus=SELL, regTime=2022-03-21T14:25:17.928705, updateTime=2022-03-21T14:25:17.928705)
Item(id=20, itemNm=상품번호7, price=500000, stockNumber=7, itemDetail=상품설명7, itemSellStatus=SELL, regTime=2022-03-21T14:25:17.940700, updateTime=2022-03-21T14:25:17.940700)
Item(id=21, itemNm=상품번호8, price=500000, stockNumber=8, itemDetail=상품설명8, itemSellStatus=SELL, regTime=2022-03-21T14:25:17.949695, updateTime=2022-03-21T14:25:17.949695)
Item(id=22, itemNm=상품번호9, price=500000, stockNumber=9, itemDetail=상품설명9, itemSellStatus=SELL, regTime=2022-03-21T14:25:17.959530, updateTime=2022-03-21T14:25:17.959530)
Item(id=23, itemNm=상품번호0, price=500000, stockNumber=0, itemDetail=상품설명0, itemSellStatus=SELL, regTime=2022-03-21T15:56:10.988024, updateTime=2022-03-21T15:56:10.988024)
Item(id=24, itemNm=상품번호1, price=500000, stockNumber=1, itemDetail=상품설명1, itemSellStatus=SELL, regTime=2022-03-21T15:56:11.017011, updateTime=2022-03-21T15:56:11.017011)
Item(id=25, itemNm=상품번호2, price=500000, stockNumber=2, itemDetail=상품설명2, itemSellStatus=SELL, regTime=2022-03-21T15:56:11.033004, updateTime=2022-03-21T15:56:11.033004)
Item(id=26, itemNm=상품번호3, price=500000, stockNumber=3, itemDetail=상품설명3, itemSellStatus=SELL, regTime=2022-03-21T15:56:11.042999, updateTime=2022-03-21T15:56:11.042999)
Item(id=27, itemNm=상품번호4, price=500000, stockNumber=4, itemDetail=상품설명4, itemSellStatus=SELL, regTime=2022-03-21T15:56:11.053995, updateTime=2022-03-21T15:56:11.053995)
Item(id=28, itemNm=상품번호5, price=500000, stockNumber=5, itemDetail=상품설명5, itemSellStatus=SELL, regTime=2022-03-21T15:56:11.063990, updateTime=2022-03-21T15:56:11.063990)
Item(id=29, itemNm=상품번호6, price=500000, stockNumber=6, itemDetail=상품설명6, itemSellStatus=SELL, regTime=2022-03-21T15:56:11.073986, updateTime=2022-03-21T15:56:11.073986)
Item(id=30, itemNm=상품번호7, price=500000, stockNumber=7, itemDetail=상품설명7, itemSellStatus=SELL, regTime=2022-03-21T15:56:11.086980, updateTime=2022-03-21T15:56:11.086980)
Item(id=31, itemNm=상품번호8, price=500000, stockNumber=8, itemDetail=상품설명8, itemSellStatus=SELL, regTime=2022-03-21T15:56:11.095976, updateTime=2022-03-21T15:56:11.095976)
Item(id=32, itemNm=상품번호9, price=500000, stockNumber=9, itemDetail=상품설명9, itemSellStatus=SELL, regTime=2022-03-21T15:56:11.105972, updateTime=2022-03-21T15:56:11.105972)
Item(id=1, itemNm=0000, price=100000, stockNumber=9876, itemDetail=상품설명예제, itemSellStatus=SELL, regTime=null, updateTime=2022-03-21T14:07:43.983720)

 

(3) 조건을 미리 만들어두면 재활용가능

(3-1)

//4-2.
	@Test
	@DisplayName("test")
	public void queryDslTest2() {
		BooleanBuilder booleanbuilder = new BooleanBuilder(); //booleanBuilder.and : 조건절(WHERE)
		QItem qitem = QItem.item;
		String itemDetail = "상품";
		int price = 400000;
		String itemSellStat = "SELL";  //조건 설정하는 부분
		
		booleanbuilder.and(qitem.itemDetail.like("%"+itemDetail+"%"));
		booleanbuilder.and(qitem.price.gt(price)); //gt는 greaterThan
		System.out.println(ItemSellstatus.SELL);
		if(itemSellStat.equals(ItemSellstatus.SELL)) {
			booleanbuilder.and(qitem.itemSellStatus.eq(ItemSellstatus.SELL));
		}
		
		//page 기능
		Pageable pageable = PageRequest.of(0, 5);
		Page<Item> itemPagingResult = itemRepository.findAll(booleanbuilder, pageable);
		System.out.println("total elements : "+ itemPagingResult.getTotalElements());
		
		List<Item> resultItemList = itemPagingResult.getContent();
		for(Item resultItem: resultItemList) {
			System.out.println(resultItem.toString());
		}
	}

 

(3-2) 제이유닛 결과

total elements : 20
Item(id=13, itemNm=상품번호0, price=500000, stockNumber=0, itemDetail=상품설명0, itemSellStatus=SELL, regTime=2022-03-21T14:25:17.840744, updateTime=2022-03-21T14:25:17.840744)
Item(id=14, itemNm=상품번호1, price=500000, stockNumber=1, itemDetail=상품설명1, itemSellStatus=SELL, regTime=2022-03-21T14:25:17.873729, updateTime=2022-03-21T14:25:17.873729)
Item(id=15, itemNm=상품번호2, price=500000, stockNumber=2, itemDetail=상품설명2, itemSellStatus=SELL, regTime=2022-03-21T14:25:17.884724, updateTime=2022-03-21T14:25:17.884724)
Item(id=16, itemNm=상품번호3, price=500000, stockNumber=3, itemDetail=상품설명3, itemSellStatus=SELL, regTime=2022-03-21T14:25:17.894720, updateTime=2022-03-21T14:25:17.894720)
Item(id=17, itemNm=상품번호4, price=500000, stockNumber=4, itemDetail=상품설명4, itemSellStatus=SELL, regTime=2022-03-21T14:25:17.907714, updateTime=2022-03-21T14:25:17.907714)

 

 

 

저작자표시
댓글
공지사항
최근에 올라온 글
최근에 달린 댓글
Total
Today
Yesterday
링크
TAG more
«   2025/05   »
일 월 화 수 목 금 토
1 2 3
4 5 6 7 8 9 10
11 12 13 14 15 16 17
18 19 20 21 22 23 24
25 26 27 28 29 30 31
글 보관함