DAY 90 - JPA (2024.11.13) - 쿼리메서드 / 메인화면, 회원가입폼 (2024.11.13)

쿼리 메서드

위의 메서드들은 모든 엔티티에 대해 공통으로 쓰일 수 있는 메서드를 제공하지만,

사실 비즈니스 로직을 다루는 것은 그리 간단하지 않다.

 

조건을 지정하여 조회하거나, 제거하거나 저장할 수 있는 기능들을 커스터마이징 해야 한다.

 

Data JPA는 Repository를 커스터마이징 하기 위해 쿼리 메서드 기능을 제공한다.

1. 메서드 이름으로 쿼리 생성 -> 간단한 쿼리 처리 시 좋음

2. @Query 안에 JPQL 정의 -> 복잡한 쿼리 처리 시 좋음

3. 메서드 이름으로 JPA NamedQuery 호출 (잘 안쓰임)

---

---

findBySeq

-- 우리가 만든 것이다.

 

JPA_BoardRepository2Test.java

	//쿼리메서드
	@Test
	@Order(4)
	public void bySeq() {
		BoardEntity boardEntity = boardDAO.findBySeq(7);
		System.out.println(boardEntity);
	}

---

BoardDAO.java

public BoardEntity findBySeq(int seq);

package com.example.demo;

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

@Repository
public interface BoardDAO extends JpaRepository<BoardEntity, Integer>{

	public BoardEntity findBySeq(int seq);

}

---

findById

-- 이미 존재하는 것이다.

 

JPA_BoardRepository2Test.java

	@Test
	@Order(5)
	public void byId() {
		BoardEntity boardEntity = boardDAO.findById(7).get(); //@Id 어노테이션이 있는 컬럼을 조회한다.
		System.out.println(boardEntity);
	}

분명 나는 id로 물어봤지만 seq로 가져온다.

 

findById는 워크벤치에 있는 테이블 컬럼명인 id가 아니다.

저 id는 우리가 @Id 이렇게 걸어놓은 id를 의미한다.

 

@Id 어노테이션이 있는 컬럼을 조회한다.

---

findByLogtimeNull

JPA_BoardRepository2Test.java

	@Test
	@Order(6)
	public void byLogtimeNull() {
		List<BoardEntity> list = boardDAO.findByLogtimeNull();
		list.stream().forEach(System.out::println);
	}

---

BoardDAO.java

public List<BoardEntity> findByLogtimeNull();

package com.example.demo;

import java.util.List;

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

@Repository
public interface BoardDAO extends JpaRepository<BoardEntity, Integer>{

	public BoardEntity findBySeq(int seq);

	public List<BoardEntity> findByLogtimeNull();

}

---

id 값에 'o' 자가 들어가는 레코드를 모두 조회하시오.

findByIdContaining

JPA_BoardRepository2Test.java

	@Test
	@Order(7)
	public void byIdContaining() {
		List<BoardEntity> list = boardDAO.findByIdContaining("o");
		list.stream().forEach(System.out::println);
	}

---

BoardDAO.java

public List<BoardEntity> findByIdContaining(String string);

package com.example.demo;

import java.util.List;

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

@Repository
public interface BoardDAO extends JpaRepository<BoardEntity, Integer>{

	public BoardEntity findBySeq(int seq);

	public List<BoardEntity> findByLogtimeNull();

	public List<BoardEntity> findByIdContaining(String string);

}

---

데이터 추가 삽입하기

JPA_BoardRepository2Test.java

	@Test
	@Order(3) //실행순서를 정한다, 숫자가 작을 수록 먼저 실행한다.
	@Rollback(false)//Rollback이 기본이다. DML문 수행한 후에 rollback을 하고싶지 않으면 false로 설정해주어야한다.
	public void insert3() {
		BoardEntity boardEntity = new BoardEntity();
		boardEntity.setId("strawberry");
		boardEntity.setName("딸기");
		boardEntity.setSubject("빨간 딸기");
		boardEntity.setContent("새콤달콤 딸기 맛있다 !!!");
		
		boardDAO.save(boardEntity); //레코드가 없으면 입력(insert), 있으면 수정(update)
	}

---

findByLogtimeAfter

JPA_BoardRepository2Test.java

	@Test
	public void byLogTimeAfter() {
		//BoardEntity에서 private java.sql.Date logtime; 설정했을 때
		//List<BoardEntity> list = boardDAO.findByLogtimeAfter(java.sql.Date.valueOf("2024.11.12"));
		
		//BoardEntity에서 private LocalDateTime logtime = LocalDateTime.now(); 설정했을 때
		//List<BoardEntity> list = boardDAO.findByLocaltimeAfter(LocalDate.parse("2024-11-12").atStartOfDay());
		
		//List<BoardEntity> list2 = boardDAO.findByLogtimeAfter(LocalDateTime.of(24, 11, 12, 00, 00));
		
		LocalDateTime dateTime = LocalDateTime.parse("2024-11-13T00:00:00");
		List<BoardEntity> list = boardDAO.findByLogtimeAfter(dateTime);
		list.stream().forEach(System.out::println);
	}

---

BoardDAO.java

public List<BoardEntity> findByLogtimeAfter(LocalDateTime dateTime);

package com.example.demo;

import java.sql.Date;
import java.time.LocalDateTime;
import java.util.List;

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

@Repository
public interface BoardDAO extends JpaRepository<BoardEntity, Integer>{

	public BoardEntity findBySeq(int seq);

	public List<BoardEntity> findByLogtimeNull();

	public List<BoardEntity> findByIdContaining(String string);

	public List<BoardEntity> findByLogtimeAfter(LocalDateTime dateTime);

}

---

findBySeqBetween

seq의 값이 2보다 크고 5보다 작은 값을 조회하시오.

JPA_BoardRepository2Test.java

	//seq의 값이 2보다 크고 5보다 작은 값을 조회하시오.
	@Test
	public void bySeqBetween() {
		List<BoardEntity> list = boardDAO.findBySeqBetween(2, 5);
		list.stream().forEach(System.out::println);
	}

 

---

BoardDAO.java

public List<BoardEntity> findBySeqBetween(int i, int j);

package com.example.demo;

import java.sql.Date;
import java.time.LocalDateTime;
import java.util.List;

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

@Repository
public interface BoardDAO extends JpaRepository<BoardEntity, Integer>{

	public BoardEntity findBySeq(int seq);

	public List<BoardEntity> findByLogtimeNull();

	public List<BoardEntity> findByIdContaining(String string);

	public List<BoardEntity> findByLogtimeAfter(LocalDateTime dateTime);

	public List<BoardEntity> findBySeqBetween(int i, int j);

}

---

BoardController.java

package com.example.demo;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController
public class BoardController {
	@Autowired
	private BoardService boardService;
	
	@GetMapping(value = "/board/write")
	public String write() {
		boardService.write();
		return "등록 성공";
	}
}

---

BoardService.java

package com.example.demo;

public interface BoardService {

	public void write();

}

---

BoardServiceImpl.java

package com.example.demo;

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

@Service
public class BoardServiceImpl implements BoardService {

	@Autowired
	private BoardDAO boardDAO;
	
	@Override
	public void write() {
		BoardEntity boardEntity = new BoardEntity();
		boardEntity.setId("grape");
		boardEntity.setName("포도");
		boardEntity.setSubject("포도송이");
		boardEntity.setContent("포도포도색 좋아");
		
		//DB
		boardDAO.save(boardEntity);
	}

}

---

DB의 내용을 최신순으로 출력한다. JSON 형식으로 브라우저에 뿌린다.

 

BoardController.java

	@GetMapping(value = "/board/list")
	public List<BoardEntity> list() {
		return boardService.list(); // 최신순으로 출력한다. JSON 형식으로 브라우저에 뿌린다.
	}

---

BoardService.java

public List<BoardEntity> list();

package com.example.demo;

import java.util.List;

public interface BoardService {

	public void write();

	public List<BoardEntity> list();

}

---

BoardServiceImpl.java

	@Override
	public List<BoardEntity> list() {
		return boardDAO.findAllByOrderBySeqDesc(); //쿼리 메서드
	}

 

나는 밑에꺼를 사용했는데 밑에 방식은 동적인 정렬이라고 생각하면 된다.

@Override
public List<BoardEntity> list() {
    return boardDAO.findAll(Sort.by("seq").descending());
}

---

BoardDAO.java

public List<BoardEntity> findAllByOrderBySeqDesc();

package com.example.demo;

import java.sql.Date;
import java.time.LocalDateTime;
import java.util.List;

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

@Repository
public interface BoardDAO extends JpaRepository<BoardEntity, Integer>{

	//쿼리 메서드
	public BoardEntity findBySeq(int seq);

	public List<BoardEntity> findByLogtimeNull();

	public List<BoardEntity> findByIdContaining(String string);

	public List<BoardEntity> findByLogtimeAfter(LocalDateTime dateTime);

	public List<BoardEntity> findBySeqBetween(int i, int j);

	public List<BoardEntity> findAllByOrderBySeqDesc();

}

---

Spring Starter Project

Chapter04JPA

        src/main/java

             com.example.demo

                    Chapter04JpaApplication.java (main 메서드)

---

application.properties

spring.application.name=Chapter04JPA

# Server Port
server.port=8080
server.address=localhost

# MySQL
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/mydb?serverTimezone=Asia/Seoul
spring.datasource.username=root
spring.datasource.password=1234

# JPA
## 스키마 생성 - create(기존 테이블이 있으면 삭제 후 생성), update(변경된 부분만 반영)
spring.jpa.hibernate.ddl-auto=update

## DDL 생성 시 데이터베이스 고유의 기능을 사용하겠는가?
spring.jpa.generate-ddl=true

## api 호툴 시 실행되는 sql문을 콘솔에 보여줄 것인가?
spring.jpa.show-sql=true

## 사용할 데이터베이스
spring.jpa.database=mysql

 

templates -- 서버로부터 응답이 오는 것 (컨트롤러 갔다가 하는거)

static -- 요청만 하는 것(js, css 등)

---

메인화면

Spring Starter Project

Chapter04JPA

        src/main/java

             com.example.demo

                    Chapter04JpaApplication.java (main 메서드)

             main.controller

                    MainController.java

        src/main/resources

             static

                     index.html                  

 

http://localhost:8080/

 

index.html

*** 메인화면 *** 

등록

목록

---

index.html

<!DOCTYPE html>
<html xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8">
    <title>메인화면</title>
    <link rel="stylesheet" href="../css/main.css">
</head>
<body>
<div class="main-container">
    <a href="/">
        <img src="../image/free-icon-love-4096198.png" width="80px" height="80px" alt="heart"/>
    </a>
    <h3 class="main-title"> 메인화면 </h3>
    <hr class="divider"/>
    <div class="link-container">
        <a href="/member/writeForm" class="main-link">가입</a>
        <a href="/member/list" class="main-link">목록</a>
    </div>
</div>
</body>
</html>

---

MainController.java

package main.controller;

import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;

@Controller  
public class MainController {

    @GetMapping(value = "/")
    public String index() {
        return "index";  
    }
}

---

회원가입

Spring Starter Project

Chapter04JPA

        src/main/java

             com.example.demo

                    Chapter04JpaApplication.java (main 메서드)

             main.controller

                    MainController.java

             member.controller

                    MemberController.java

        src/main/resources

             templates

                     member

                               writeForm.html

             static

                     index.html     

---

MemberController.java

package member.controller;


import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;

@Controller
@RequestMapping("member")
public class MemberController {
	
	@GetMapping(value="writeForm")
	public String writeForm() {
		return "member/writeForm";
	}
	
}

---

Chapter04JpaApplication.java

@ComponentScan(basePackages = {"member.controller"})

package com.example.demo;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.ComponentScan;

@ComponentScan(basePackages = {"member.controller"})
@SpringBootApplication
public class Chapter04JpaApplication {

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

}

---

writeForm.html

<!DOCTYPE html>
<html xmlns:th="http://www.thymeleaf.org">
<head>
<meta charset="UTF-8">
<title>회원가입</title>
<link rel="stylesheet" href="../css/writeForm.css"> 
</head>
<body>
<div class="write-container">
    <a href="/">
        <img src="../image/free-icon-love-4096198.png" width="80px" height="80px" alt="heart"/>
    </a>
    <h2 class="board-list-title">회원가입</h2>
    <!-- <form id="userWriteForm" class="write-box"> -->
	<form method="post" id="memberWriteForm" class="write-box" th:action="@{/user/write2}">
	    <table>
	        <tr>
	            <td><label class="write-title" for="id">아이디</label></td>
	            <td><input type="text" class="write-input" name="id" id="id" /></td>
	        </tr>
	        <tr>
	            <td colspan="2">
	                <div id="idDiv" class="div"></div>
	            </td>
	        </tr>
	        <tr>
	            <td><label class="write-title" for="pwd">비밀번호</label></td>
	            <td><input type="password" class="write-input" name="pwd" id="pwd" /></td>
	        </tr>
	        <tr>
	            <td colspan="2">
	                <div id="pwdDiv" class="div"></div>
	            </td>
	        </tr>
	        <tr>
	            <td><label class="write-title" for="name">이름</label></td>
	            <td><input type="text" class="write-input" name="name" id="name" /></td>
	        </tr>
	        <tr>
	            <td colspan="2">
	                <div id="nameDiv" class="div"></div>
	            </td>
	        </tr>
	    </table>
	    <div class="button-container">
	        <button type="button" id="writeBtn" class="write-button">회원가입</button>
	        <button type="button" id="resetBtn" class="write-button2">초기화</button>
	    </div>
	</form>
<script type="text/javascript" src="https://code.jquery.com/jquery-3.7.1.min.js"></script>
<script type="text/javascript" src="../js/writeForm.js"></script>
</div>
</body>
</html>

---