[국비 77일차 TIL] JPA 생성 CRUD

jpa 생성

jpa와 반대되는 것은 mybatis (동시에 사용하지 않음.)

 

데이터베이스 방언(Dialect)

* 특정 데이터베이스 시스템에서 사용되는 고유한 언어 또는 문법 규칙을 가리킵니다.

* 각 데이터베이스 시스템은 자체적인 방언을 가지고 있으며, SQL(Structured Query Language) 쿼리를 작성할 때 이러한 방언을 이해해야 합니다. 예를 들어, MySQL, PostgreSQL, Oracle 등의 데이터베이스는 각각 고유한 방언을 가지고 있으며, 이러한 방언은 SQL 문법의 특정 부분이나 기능에 대한 차이점을 나타냅니다. 데이터베이스 방언을 이해하고 사용함으로써 특정 데이터베이스 시스템에 최적화된 쿼리를 작성할 수 있습니다.

---

jpa 생성 순서

1. spring 프로젝트 생성
2. build.gradle 필요한 것들이 다 있는지 확인하기
3. application.properties에 DB정보, 포트 설정, jpa 방언 설정, 타임리프 경로 설정
4. 컨트롤러 생성 (cohttp://m.example.web.controller)
5. html 생성 (templates)
6. Service, Repository, Entity 생성
7. Repository는 ==interface==임.
8. 컨트롤러에 @Autowired BoardService
9. 서비스에 @Autowired BoardRepository
10. JPABoard에 DTO처럼 컬럼 추가 + 해당 어노테이션 붙이기
11. JPABoard에서 만든 내용으로 DB에 테이블이 자동 추가됨.

 

build.gradle

dependencies {
	implementation 'org.springframework.boot:spring-boot-starter-data-jpa'
	implementation 'org.springframework.boot:spring-boot-starter-thymeleaf'
	implementation 'org.springframework.boot:spring-boot-starter-web'
	compileOnly 'org.projectlombok:lombok'
	developmentOnly 'org.springframework.boot:spring-boot-devtools'
	runtimeOnly 'org.mariadb.jdbc:mariadb-java-client'
	annotationProcessor 'org.projectlombok:lombok'
	testImplementation 'org.springframework.boot:spring-boot-starter-test'
}

JPABoard

** jpa에서는 컬럼명으로 언더바( _ ) 사용 불가함.

package com.example.web.entiry;

import java.time.LocalDateTime;

import org.hibernate.annotations.ColumnDefault;

import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import lombok.Getter;
import lombok.Setter;

@Getter
@Setter
@Entity
public class JPABoard {
	
	// 언더바(_) 사용 불가
	// 영속성 = jpa 사용하는 목적
	
	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private int jbno; // jpa board no
	
	@Column(columnDefinition = "TEXT")
	private String jbtitle;
	
	@Column(columnDefinition = "LONGTEXT")
	private String jbcontent;
	
	@ColumnDefault("CURRENT_TIMESTAMP")
	private LocalDateTime jbdate = LocalDateTime.now();
	
	@ColumnDefault("0")
	private int jblike;
	
	@ColumnDefault("1")
	private int jbread;
}

 

실행하면 DB에 테이블이 자동 생성됨.

 

spring.jpa.hibernate.ddl-auto=create

Hibernate: 
    drop table if exists jpaboard

 

여기에 create로 하면, 엔티티에서 생성한 테이블이 계속 새로 생김. 기존에 있던건 drop됨.
그래서 내용을 추가하려면 update로 해야함!

 

#----------------------------------------------------------------------------------#
#-----------------------------------   ddl-auto -----------------------------------#
#----------------------------------------------------------------------------------#
# create	  : 실행할 때마다 기존 테이블 삭제 후 다시 생성 (DROP + CREATE)
# create-drop : create와 같으나 애플리케이션 종료 시점에 테이블 삭제
# update 	  : 엔티티 매핑 정보를 비교하여 변경된 내용만 반영 (세부 설정은 업뎃 안됨(?))
# validate    : 엔티티와 테이블이 정상적으로 매핑되었는지만 확인
# none 		  : 사용하지 않음
#----------------------------------------------------------------------------------#

 

BoardRepository

여기에서 내용을 추가해서 sql문 없이 작동하도록 함.

 

BoardController.java

package com.example.web.controller;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;

import com.example.web.entiry.JPABoard;
import com.example.web.service.BoardService;

@Controller
public class BoardController {
	
	// 서비스 = Service
	// DAO 	  = Repository
	// DTO 	  = Entity (원래 board로 만드는데, db에 이미 board가 있기 때문에 앞에 jpa를 붙여서 만듦)

	@Autowired
	private BoardService boardService;
	
	@GetMapping({"/board", "/"})
	public String board(Model model) {
		List<JPABoard> list = boardService.boardList();
		model.addAttribute("list",list);
		return "board";
	}
}

BoardService.java

package com.example.web.service;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.example.web.entiry.JPABoard;
import com.example.web.repository.BoardRepository;

@Service
public class BoardService {
	
	@Autowired
	private BoardRepository boardRepository;

	public List<JPABoard> boardList() {
		return boardRepository.findAll(); // repository에서 상속받았기 때문에 바로 사용 가능
	}
}

board.html

<!DOCTYPE html>
<html xmlns:th="http://thymeleaf.org">
<head>
<meta charset="UTF-8">
<title>board.html</title>
</head>
<body>
	[[${list}]]
	<table border="1">
		<tr>
			<th>번호</th>
			<th>제목</th>
			<th>작성일</th>
			<th>조회수</th>
			<th>좋아요</th>
		</tr>
		<tr th:each="row : ${list}">
			<td th:text="${row.jbno}"></td>
			<td th:onclick="|location.href='@{/detail(no=${row.jbno})}'|">[[${row.jbtitle}]]</td>
			<td th:text="${row.jbdate}"></td>
			<td th:text="${row.jbread}"></td>
			<td th:text="${row.jblike}"></td>
		</tr>
	</table>
</body>
</html>

BoardController.java

	@GetMapping("/detail")
	public String detail(@RequestParam("no") int no, Model model) {
		JPABoard detail = boardService.detail(no);
		
		return "detail";
	}

BoardService

	public JPABoard detail(int no) {
	
		//  Optional = 값이 없는 경우를 표현하기 위한 클래스
		//	Optional<JPABoard> detail = boardRepository.findById(no);

		JPABoard detail = boardRepository.findByJbno(no);
		return detail;
	}

BoardRepository.java

@Repository
public interface BoardRepository extends JpaRepository<JPABoard, Integer>{

	JPABoard findByJbno(int no);
}

---

TEST 해서 확인하기

Jpa1ApplicationTests

package com.example.web;

import static org.junit.jupiter.api.Assertions.assertEquals;

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 com.example.web.entiry.JPABoard;
import com.example.web.service.BoardService;

@SpringBootTest
class Jpa1ApplicationTests {

	@Autowired
	BoardService boardService;
	
	@DisplayName("detail에 데이터 오는지 확인")
	@Test
	void contextLoads() {
		JPABoard detail = boardService.detail(3);
		//assertEquals("제목", detail.getJbtitle());
		assertEquals(5, detail.getJblike());
	}
}

 

* 실행 방법 : Run as - 3. JUnit test

---

Detail

BoardController.java

detail 모델 추가하기

	@GetMapping("/detail")
	public String detail(@RequestParam("no") int no, Model model) {
		JPABoard detail = boardService.detail(no);
		model.addAttribute("detail", detail);
		return "detail";
	}

detail.html

<!DOCTYPE html>
<html xmlns:th="http://thymeleaf.org">
<head>
<meta charset="UTF-8">
<title>DETAIL</title>
</head>
<body>
	<table>
		<tr>
			<th>[[${detail.jbno}]]</th>
			<th>[[${detail.jbtitle}]]</th>
			<th>[[${detail.jbcontent}]]</th>
			<th>[[${detail.jbdate}]]</th>
			<th>[[${detail.jbread}]]</th>
			<th>[[${detail.jblike}]]</th>
		</tr>
	</table>

	<button onclick="location.href='/write'">글쓰기</button>
</body>
</html>

---

BoardController

	@GetMapping("/write")
	public String write() {
		return "write";
	}
	
	@PostMapping("/write")
	public String write(@RequestParam) {
		return "redirect:/board";
	}

write.html

<!DOCTYPE html>
<html xmlns:th="http://thymeleaf.org">
<head>
<meta charset="UTF-8">
<title>글 작성하기</title>
</head>
<body>

<form action="/write" method="post">
	<input name="title" id="title">
	<textarea name="content" id="content"></textarea>
	<button type="submit">글 등록하기</button>
</form>

	<button onclick="location.href='/write'">글쓰기</button>
</body>
</html>

BoardController

	@PostMapping("/write")
	public String write(@RequestParam("title") String title, @RequestParam("content") String content) {
		JPABoard post = new JPABoard();
		post.setJbtitle(title);
		post.setJbcontent(content);
		
		boardService.write(post);
		return "redirect:/board";
	}

BoardService

	public void write(JPABoard post) {
		boardRepository.save(post); 
		// save = entity에 기본값을 안 정해주면 무조건 null이 됨. 통째로 들어가기때문임.
	}

---

글 삭제하기 postDel

detail.html

<!DOCTYPE html>
<html xmlns:th="http://thymeleaf.org">
<head>
<meta charset="UTF-8">
<title>DETAIL</title>
</head>
<body>
	<table>
		<tr>
			<th>[[${detail.jbno}]]</th>
			<th>[[${detail.jbtitle}]] 
				<button th:onclick="|location.href='@{/postDel(no=${detail.jbno})}'|">삭제</button>
				<button>수정</button></th>
			<th>[[${detail.jbcontent}]]</th>
			<th>[[${detail.jbdate}]]</th>
			<th>[[${detail.jbread}]]</th>
			<th>[[${detail.jblike}]]</th>
		</tr>
	</table>

	<button onclick="location.href='/write'">글쓰기</button>
</body>
</html>

 

1) deleteById 방법

BoardController

	@GetMapping("/postDel")
	public String postDel(@RequestParam("no") int no) {
		// deleteById
		// delete
		boardService.postDel(no);
		return "redirect:/board";
	}

BoardService

	public void postDel(int no) {
		boardRepository.deleteById(no);
	}

 

2) delete 방법

BoardController

	@GetMapping("/postDel")
	public String postDel(@RequestParam("no") int no) {
		// deleteById
		// delete
		// boardService.postDel(no); 
		
		JPABoard post = new JPABoard();
		post.setJbno(no);
		
		boardService.postDel2(post); // entity 통째로 던짐..(?)
		return "redirect:/board";
	}

BoardService

	public void postDel2(JPABoard post) {
		boardRepository.delete(post);;
	}

---

수정하기 update

detail.html

			<th>[[${detail.jbtitle}]] 
				<button th:onclick="|location.href='@{/postDel(no=${detail.jbno})}'|">삭제</button>
				<button th:onclick="|location.href='@{/update(no=${detail.jbno})}'|">수정</button></th>

BoardController

	@GetMapping("/update")
	public String update(@RequestParam("no") int no, Model model) {
		JPABoard detail = boardService.detail(no);
		model.addAttribute("detail", detail);
		return "update";
	}

update.html

(write 복사해서 생성하면 빠름)

<form action="/update" method="post">
	<input name="title" id="title" th:value="${detail.jbtitle}">
	<textarea name="content" id="content">[[${detail.jbcontent}]]</textarea>
	<button type="submit">글 수정하기</button>
	<input type="hidden" name="no" th:value="${detail.jbno}">
</form>

BoardController

	@PostMapping("/update")
	public String update(@RequestParam("title") String title, 
						@RequestParam("content") String content,
						@RequestParam("no") int no) {
		JPABoard post = new JPABoard();
		post.setJbno(no);
		post.setJbtitle(title);
		post.setJbcontent(content);
		
		boardService.update(post);
		
		return "redirect:/detail?no="+no;
	}

BoardService

	public void update(JPABoard post) {
		boardRepository.save(post);
	}

---

정렬 DESC

1. 메소드 생성하기 findAllByOrderByJbnoDesc

BoardService

	public List<JPABoard> boardList() {
		return boardRepository.findAllByOrderByJbnoDesc(); // 생성함
	}

BoardRepository

	List<JPABoard> findAllByOrderByJbnoDesc();

 

2. @Query : repository에 직접 쿼리문을 입력할 수도 있음. 
NatvieQuery는 JPQL이 아닌 SQL를 직접 정의하여 사용하는 방식이다. 위에서 이야기 한것과 같이 function과 join를 하는 경우 JPQL를 사용할 수도 있지만 SQL를 직접 정의할수있는 NativeQuery를 사용할 수 있다.

 

BoardRepository

@Repository
public interface BoardRepository extends JpaRepository<JPABoard, Integer>{

	@Query(value = "SELECT * FROM jpaboard jWHERE j.jbno=?1", nativeQuery = true) // ?1에 no값이 들어감.
	JPABoard findByJbno(int no);

	List<JPABoard> findAllByOrderByJbnoDesc();
}

JPABoard

@Column 뒤에 다른 내용들을 입력할 수 있음.

	// @Column(columnDefinition = "TEXT")
	@Column(name="jbtitle", columnDefinition="VARCHAR(50)")
	private String jbtitle;

---

작성자 member

JPAMember

package com.example.web.entity;

import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import lombok.Getter;
import lombok.Setter;

@Getter
@Setter
@Entity
public class JPAMember {

	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private int jmno;

	@Column(columnDefinition = "VARCHAR(10)")
	private String jmid;
	
	@Column(columnDefinition = "VARCHAR(30)")
	private String jmpw;
	
	// 컬럼의 name은 DB의 이름 및 설정, String name은 JPA에서 관리하는 이름
	@Column(name="jmname", columnDefinition = "VARCHAR(10)")
	private String name;
}

 

jpa 방언 설정 (ddl-auto)

ddl-auto=update의 경우, 속성은 적용되지 않음.

# jpa 방언 설정
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MariaDB106Dialect
spring.jpa.hibernate.ddl-auto=create
spring.jpa.properties.hibernate.format_sql=true
spring.jpa.properties.hibernate.show_sql=true

#----------------------------------------------------------------------------------#
#-----------------------------------   ddl-auto -----------------------------------#
#----------------------------------------------------------------------------------#
# create	  : 실행할 때마다 기존 테이블 삭제 후 다시 생성 (DROP + CREATE)
# create-drop : create와 같으나 애플리케이션 종료 시점에 테이블 삭제
# update 	  : 엔티티 매핑 정보를 비교하여 변경된 내용만 반영 (세부 설정은 업뎃 안됨(?))
# validate    : 엔티티와 테이블이 정상적으로 매핑되었는지만 확인
# none 		  : 사용하지 않음
#----------------------------------------------------------------------------------#

JPABoard

@Getter
@Setter
@Entity
public class JPABoard {

	@ManyToOne
	@JoinColumn
	private JPAMember jpaMember;

-> 데이터베이스 jpaboard에 새로운 컬럼이 생성됨.

 

 

 

---

글쓰기

BoardController

	@PostMapping("/write")
	public String write(@RequestParam("title") String title, 
						@RequestParam("content") String content) {
		JPABoard post = new JPABoard();
		post.setJbtitle(title);
		post.setJbcontent(content);
		
		JPAMember jpaMember = new JPAMember();
		jpaMember = boardService.findByJmid("admin");
		post.setJpaMember(jpaMember);
		
		boardService.write(post);
		
		return "redirect:/board";
	}

BoardService

MemberService 를 생성해도 되고, BoardService에 @Autowired로 받아도 된다.

	@Autowired
	private MemberRepository memberRepository;
	
	public JPAMember findByJmid(String string) {
		return memberRepository.findByJmid(string);
	}

MemberRepository

package com.example.web.repository;

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

import com.example.web.entity.JPAMember;

public interface MemberRepository extends JpaRepository<JPAMember, Integer>{
	JPAMember findByJmid(String string);
}