본문 바로가기
서버/SprintBoot
[스프링부트(Spring Boot)] JPA
Hana_h 2023. 7. 2. 14:19

// JPA, Java Persistent API
    - Java ORM 기술에 대한 API 표준 명세
    - JPA 구현 기술 > Hibernate, EclipseLin, DataNucleus 등
    - 대부분 Hibernate를 사용


// ORM, Object Relational Mapping
    - Entity 객체와 Database 테이블을 서로 매핑해서, SQL 쿼리가 아닌 Java 메소드를 사용해서 데이터를 조작한다.
    - DB의 데이터 구조를 자바의 객체 구조로 취급

// Spring Boot + JPA

    - 의존성

        ~ Lombok
        ~ MyBatis Framework
        ~ Oracle Driver
        ~ Spring Boot DevTools
        ~ Spring Data JPA
        ~ Spring Web
        ~ Tymeleaf

    - 파일 생성
        ~ boot-jpa > "script.sql"

create table Item (
    name varchar2(30) primary key,
    price number not null,
    color varchar2(30) not null,
    owner varchar2(30) null,
    orderdate date default sysdate not null
);

insert into Item (name, price, color, owner, orderdate) values ('키보드', 100000, 'black', '홍길동', sysdate - 1);
insert into Item (name, price, color, owner, orderdate) values ('무접점 고급 키보드', 120000, 'white', null, sysdate - 1.5);
insert into Item (name, price, color, owner, orderdate) values ('기계식 키보드', 150000, 'black', '홍길동', sysdate - 2);
insert into Item (name, price, color, owner, orderdate) values ('멤브레인 키보드', 80000, 'white', '홍길동', sysdate - 2.5);
insert into Item (name, price, color, owner, orderdate) values ('마우스', 50000, 'silver', '홍길동', sysdate - 3);
insert into Item (name, price, color, owner, orderdate) values ('버티컬 마우스', 90000, 'silver', '아무개', sysdate - 3.5);
insert into Item (name, price, color, owner, orderdate) values ('게이밍 마우스', 120000, 'black', '아무개', sysdate - 4);
insert into Item (name, price, color, owner, orderdate) values ('고급 볼 마우스', 95000, 'yellow', '아무개', sysdate - 3);
insert into Item (name, price, color, owner, orderdate) values ('노트북', 1100000, 'white', '아무개', sysdate - 4.5);
insert into Item (name, price, color, owner, orderdate) values ('노트북 가방', 120000, 'blue', null, sysdate - 5);
insert into Item (name, price, color, owner, orderdate) values ('노트북 받침대', 95000, 'yellow', '하하하', sysdate - 5.5);
insert into Item (name, price, color, owner, orderdate) values ('노트북 파우치', 95000, 'yellow', '하하하', sysdate - 5);


select * from Item;

commit;


        ~ src/main/java > "com.test.controller" > "ItemController.java"

package com.test.controller;

import java.util.List;
import java.util.Optional;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Sort;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestParam;

import com.test.domain.Item;
import com.test.repository.ItemRepository;

@Controller
public class ItemController {

	//CRUD
	// - JpaRepository 구현체
	@Autowired
	private ItemRepository itemRepo;
	
	@GetMapping("/item/m1")
	public String m1(Model model) {
	
		System.out.println("m1");
		//'C'RUD
		// - 레코드 추가하기
		
		Item item = new Item();
		
		item.setName("잉크젯 프린터");
		item.setPrice(250000);
		item.setColor("yellow");
		item.setOwner("홍길동");
		
		//save > (JPA) > insert
		Item result = itemRepo.save(item);
		
		model.addAttribute("result", result);
		
		return "item/result";
	}
	
	@GetMapping("/item/m2")
	public String m2(Model model) {
		
		// - 레코드 추가하기
		
		//빌더 패턴(Builder Pattern)
		// - OOP에서 객체를 생성하는 패턴 중 하나
		// - 유연함
		
		Item item = Item.builder()
						.name("레이저 프린터")
						.price(300000)
						.color("black")
						.owner("아무개")
						.build();
		
		Item result = itemRepo.save(item);
				
		model.addAttribute("result", result);
		
		return "item/result";
	}
	
	@GetMapping("/item/m3")
	public String m3(Model model) {
		
		//C'R'UD
		// - 단일 레코드 읽기
		//Item item = itemRepo.getById("마우스");
		//model.addAttribute("result", item);
		
		//Item item = itemRepo.getOne("키보드");
		//model.addAttribute("result");
		
		Optional<Item> item = itemRepo.findById("노트북");
		model.addAttribute("result", item.get());
		
		
		return "item/result";
	}
	
	@GetMapping("/item/m4")
	public String m4(Model model) {
		
		//CR'U'D
		// - 레코드 수정하기
		//update시 모든 컬럼을 명시해야하므로 바뀌지 않는 데이터도 적어야한다.
	    //save 메소드는 select문을 먼저 실행하여서 기존에 없는 데이터면 insert, 있는 데이터면 update를 한다.

//		Item item = Item.builder()
//						.name("프린터")
//						.price(230000)
//						.color("white")
//						.owner("홍길동")
//						.build();
		
		Optional<Item> item = itemRepo.findById("프린터");
		
		if(item.isPresent()) {
			
			item.get().setPrice(24000);
			Item result = itemRepo.save(item.get());
			model.addAttribute("result", result);
		}
		
		
		return "item/result";
	}
		
	@GetMapping("/item/m5")
	public String m5(Model model) {
		
		//CRU'D'
		
		Optional<Item> item = itemRepo.findById("노트북");
		
		itemRepo.delete(item.get());
		
		
		return "item/result";
	}
	
	@GetMapping("/item/m6")
	public String m6(Model model) {
		
		//C'R'UD
		//Query Methods
		
		//다중 레코드 조회
		List<Item> list = itemRepo.findAll();
		
		model.addAttribute("list", list);
		
		return "item/result";
	}
	
	@GetMapping("/item/m7")
	public String m7(Model model) {
		
		//존재 유무 확인
		boolean bool = itemRepo.existsById("마우스");
		
		model.addAttribute("bool", bool);
		
		return "item/result";
	}
	
	@GetMapping("/item/m8")
	public String m8(Model model) {
		
		//카운트
		long count = itemRepo.count();
		
		model.addAttribute("count", count);
		
		return "item/result";
	}
	
	@GetMapping("/item/m9")
	public String m9(Model model) {
		
		//findByName을 자동으로 구현
		
		//Item item = itemRepo.findByName("마우스");
		//Item item = itemRepo.findByPrice(300000);
		
		//findBy 컬럼명
		// - By : Predicate Part. 조건절
		
		//Item item = itemRepo.findByNameIs("키보드");
		Item item = itemRepo.findByNameEquals("키보드");
		
		model.addAttribute("result", item);
		
		return "item/result";
	}
	
	@GetMapping("/item/m10")
	public String m10(Model model) {
		
		//And, Or
		//Item item = itemRepo.findByColorAndOwner("yellow", "홍길동");

		//Item item = itemRepo.findByColorAndOwnerAndPrice("black", "홍길동", 150000);
		
		Item item = itemRepo.findByColorOrPrice("gold", 300000);
		
		model.addAttribute("result", item);
		
		return "item/result";
	}
	
	@GetMapping("/item/m11")
	public String m11(Model model) {
		
		
		//List<Item> list = itemRepo.findByColor("white");
		
		//List<Item> list =itemRepo.findByColor("white", Sort.by(Sort.Direction.ASC, "price"));
		
		//List<Item> list = itemRepo.findByColorOrOwner("white", "홍길동");
		
		//findByNameLike()
		//findByNameIsLike()
		
		//findByNameNotLike()
		//findByNameIsNotLike()
		
		//- %, _ 를 직접 명시
		//List<Item> list = itemRepo.findByNameLike("키보드");
		//List<Item> list = itemRepo.findByNameLike("%키보드");
		
		// - findByName[Is]Startingwith
		// - findByName[Is]Endingwith
		// - findByName[Is]Containing
		

		List<Item> list = itemRepo.findByNameEndingWith("마우스");
		model.addAttribute("list", list);
		
		return "item/result";
	}
	
	@GetMapping("/item/m12")
	public String m12(Model model) {
		//[Is]Null. [Is]NotNull
		//- 컬럼값이 null인 레코드 검색

		//[Is]Empty, [Is]NotEmpty
		//- 컬럼값이 null이거나 빈문자열인 레코드

		//List<Item> list = itemRepo.findByOwnerNull();
		//List<Item> list = itemRepo.findByOwnerEmpty(); > 이건 안된당??

		//List<Item> list = itemRepo.findByOwnerNotNull();


		//정렬
		//List<Item> list = itemRepo.findAll(Sort.by(Sort.Direction.ASC, "price"));

		//List<Item> list = itemRepo.findAll(Sort.by("name")); // Direction 안쓰면 오름차순

		//List<Item> list = itemRepo.findAllByOrderByColor();

		//List<Item> list = itemRepo.findAllByOrderByColorDesc();

		//List<Item> list = itemRepo.findAllByOwnerOrderByColorDesc("홍길동");


		//[Is]GreaterThan, [Is]LessThan, [Is]Between
		//[Is]GreaterThanEqual, [Is]LessThanEqual

		//List<Item> list = itemRepo.findByPriceGreaterThan(100000);

		//List<Item> list = itemRepo.findByPriceGreaterThan(100000, Sort.by("price"));

		//List<Item> list = itemRepo.findByPriceLessThan(100000);

		//List<Item> list = itemRepo.findByPriceBetween(90000, 120000);

		//List<Item> list = itemRepo.findByOrderdateBetween("2023-06-25", "2023-06-27");

		//IgnoreCase
		//- 특정 컬럼의 대소문자를 구분하지 않고 검색

		//List<Item> list = itemRepo.findByColor("White");

		//List<Item> list = itemRepo.findByColorIgnoreCase("White");
		//where upper(item0_.color)=upper(?)


		//In, NotIn
		//- where color in ('yellow', 'blue')
		//List<String> colors = new ArrayList<String>();
		//colors.add("yellow");
		//colors.add("blue");

		//List<Item> list = itemRepo.findByColorIn(colors);

		List<Item> list = itemRepo.findByOwnerNotIn(new String[]{"홍길동", "아무개"});



		model.addAttribute("list", list);


		return "item/result";
	}


	@GetMapping("/item/m13")
	public String m13(Model model, @RequestParam("name") Item result) {

		//도메인 클래스 컨버터(Domain Class Converter)
		//- PK를 넘겨서, 바로 Entity를 조회할 수 있다.

		//item/m13?name=마우스

		//"마우스" > PK
		//Optional<Item> result = itemRepo.findById(name);
		//model.addAttribute("result", result.get());

		System.out.println(result);
		model.addAttribute("result", result);

		return "item/result";
	}


	@GetMapping("/item/m14/{name}")
	public String m14(Model model, @PathVariable("name") Item result) {

		//item/m13?name=마우스
		//item/m13/마우스
		model.addAttribute("result", result);

		return "item/result";
	}

	@GetMapping("/item/m15")
	public String m15(Model model) {

		//First
		//Top

		//이거 application.properties 가서 수정해야됨
		//spring.jpa.database-platform=org.hibernate.dialect.Oracle10gDialect
		//Item result = itemRepo.findFirstByOrderByPriceAsc();
		//Item result = itemRepo.findTopByOrderByPriceAsc();
		//model.addAttribute("result", result);

		List<Item> list = itemRepo.findTop3ByOrderByPriceDesc();

		model.addAttribute("list", list);


		return "item/result";
	}



	@GetMapping("/item/m16")
	public String m16(Model model, int page) {

		//페이징
		//매개 변수 > int page(페이지번호), int size(가져올 개수), Sort sort
		PageRequest pageRequest = PageRequest.of(page,  5, Sort.by("name"));

		List<Item> list = itemRepo.findPageListBy(pageRequest);

		model.addAttribute("list", list);

		return "item/result";
	}



	@GetMapping("/item/m17")
	public String m17(Model model) {

		//@Query
		//- 사용자 쿼리 작성
		//- 쿼리 메소드 키워드로 작성 불가능 쿼리 > 직접 SQL 작성

		//select * from Item

		//직접 만드는 쿼리의 메소드 이름은 아무렇게나 해도 된다!
		List<Item> list = itemRepo.findAllItem();

		model.addAttribute("list", list);

		return "item/result";
	}

	@GetMapping("/item/m18")
	public String m18(Model model, String color) {

		List<Item> list = itemRepo.findAllItemByColor(color);

		model.addAttribute("list", list);

		return "item/result";
	}



	/*
	@GetMapping("/item/m")
	public String m(Model model) {

		return "item/result";
	}
	 */
	
}


        ~ src/main/java > "com.test.repository" > "ItemRepository.java"(I)

package com.test.repository;

import java.util.List;

import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import com.test.domain.Item;

//JpaRepository<엔티티타입, PK자료형>
// - ItemRepository > 이름은 자유
// - 엔티티명 + Repository

public interface ItemRepository extends JpaRepository<Item, String> {
	
	//추상 메소드
	//1. JpaRepository 상속 메소드 > 기본
	//2. 사용자 정의 메소드 > 확장
	
	//Optional<Item> findById(String id)
	Item findByName(String name);
	Item findByNameIs(String name);
	Item findByNameEquals(String name);

	Item findByPrice(int price);
	
	Item findByColorAndOwner(String color, String owner);
	
	Item findByColorAndOwnerAndPrice(String color, String owner, int price);

	Item findByColorOrPrice(String color, int price);
	
	List<Item> findByColor(String color);
	
	List<Item> findByColor(String color, Sort sort);
	
	List<Item> findByColorOrOwner(String color, String owner);
	
	List<Item> findByNameLike(String word);

	List<Item> findByNameEndingWith(String word);

	List<Item> findByOwnerNull();

	List<Item> findByOwnerNotNull();

	List<Item> findAllByOrderByColor();

	List<Item> findAllByOrderByColorDesc();

	//인터페이스의 매개 변수의 이름은 중요하지 않다. 타입이 중요!!
	List<Item> findAllByOwnerOrderByColorDesc(String name);

	List<Item> findByPriceGreaterThan(int price);

	List<Item> findByPriceGreaterThan(int price, Sort by);

	List<Item> findByPriceLessThan(int price);

	List<Item> findByPriceBetween(int min, int max);

	List<Item> findByOrderdateBetween(String begin, String end);

	List<Item> findByColorIgnoreCase(String color);

	List<Item> findByColorIn(List<String> colors);

	List<Item> findByOwnerIn(String[] owners);

	List<Item> findByOwnerNotIn(String[] strings);

	Item findFirstByOrderByPriceAsc();

	Item findTopByOrderByPriceAsc();

	List<Item> findTop3ByOrderByPriceDesc();

	List<Item> findPageListBy(PageRequest pageRequest);

	@Query(value="select * from Item", nativeQuery = true)
	List<Item> findAllItem();

	//JPQL > Java Persistence Query Language
	// ? 자리에 :변수명 붙인다.
	@Query(value="select * from Item where color = :color", nativeQuery = true)
	List<Item> findAllItemByColor(String color);
}


        ~ src/main/java > "com.test.domain" > "Item.java"

package com.test.domain;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

//JPA > Entity
// - 데이터베이스의 테이블 <- (연결) -> JPA의 엔티티
// - 매핑되는 테이블명과 엔티티의 이름을 동일하게 만든다.

@Entity
@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class Item {
	
	//엔티티가 참조하는 테이블의 컬럼 > 멤버 정의
	@Id
	private String name;
	
	private int price;
	private String color;
	private String owner;
	
	@Column(insertable = false, updatable = false)
	private String orderdate;

}


        ~ src/main/resources > templates > "item" > "result.html" 

<!DOCTYPE html>
<html xmlns:th="https://thymeleaf.org">
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<link rel="stylesheet" href="https://me2.do/5BvBFJ57">
</head>
<body>
	<h1>Spring Boot <small>JPA</small></h1>	
	
	<div th:text="${bool}"></div>	
	
	<div th:text="${count}"></div>	
	
	<div class="message" title="결과">
		<th:block th:if="${result != null}">
			<div th:text="${result.name}"></div>
			<div th:text="${#numbers.formatInteger(result.price, 0, 'COMMA')}"></div>
			<div th:text="${result.color}"></div>
			<div th:text="${result.owner}"></div>
			<div th:text="${result.orderdate}"></div>
		</th:block>
	</div>
	
	<hr>
	
	<table class="vertical">
      <tr>
         <th>이름</th>
         <th>가격</th>
         <th>색깔</th>
         <th>주인</th>
         <th>주문날짜</th>
      </tr>
      <tr th:each="item : ${list}">
         <td th:text="${item.name}"></td>
         <td th:text="${#numbers.formatInteger(item.price, 0, 'COMMA')}"></td>
         <td th:text="${item.color}"></td>
         <td th:text="${item.owner}"></td>
         <td th:text="${item.orderdate}"></td>
      </tr>
   </table>
	
</body>
</html>


    - 설정
        ~ src/main/resources > application.properties

# Server port
server.port=8092

# Oracle settings
spring.datasource.driver-class-name=oracle.jdbc.driver.OracleDriver
spring.datasource.url=jdbc:oracle:thin:@localhost:1521/xe
spring.datasource.username=hr
spring.datasource.password=java1234

# Hikari CP settings
spring.datasource.hikari.connection-timeout=60000
spring.datasource.hikari.maximum-pool-size=5
spring.datasource.hikari.driver-class-name=oracle.jdbc.driver.OracleDriver
spring.datasource.hikari.jdbc-url=jdbc:oracle:thin:@localhost:1521:xe
spring.datasource.hikari.username=hr
spring.datasource.hikari.password=java1234

# logging(Log4j)
logging.pattern.console=%d{yyyy-MM-dd HH:mm:ss} %-5level %logger{36} - %msg%n
logging.level.org.hibernate.SQL=debug
logging.level.org.hibernate.type.descriptor.sql=trace
logging.level.=error

# Thymeleaf
spring.thymeleaf.cache=false

# JPA
spring.jpa.database=oracle

spring.jpa.generate-ddl=false
spring.jpa.hibernate.ddl-auto=none
spring.jpa.show-sql=true

# DB마다 자기들만의 구문
spring.jpa.database-platform=org.hibernate.dialect.Oracle10gDialect


        ~ src/main/java > com.test.jpa > BootJpaApplication.java 
                  > @ComponentScan, @EntityScan, @EnableJpaRepositories 추가

package com.test.jpa;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.domain.EntityScan;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;

@SpringBootApplication
@ComponentScan(basePackages = "com.test.controller")
@EntityScan(basePackages = "com.test.domain")
@EnableJpaRepositories(basePackages = "com.test.repository")
public class BootJpaApplication {

	public static void main(String[] args) {
		SpringApplication.run(BootJpaApplication.class, args);
	}

}

'서버 > SprintBoot' 카테고리의 다른 글

[스프링부트(Spring Boot)] 검색 기능 구현  (0) 2023.07.27
[스프링부트(Spring Boot)] 페이징(Paging)  (0) 2023.07.27
[스프링부트(Spring Boot)] Thymeleaf  (0) 2023.06.27
[스프링부트(Spring Boot)] MyBatis  (0) 2023.06.27
Spring Boot 설치 및 환경 설정  (0) 2023.06.26

'서버/SprintBoot' Related Articles

티스토리툴바