새소식

인기 검색어

TIL

[TIL] 230516 <Spring> 메모장, Database와 SQL

-

[메모장 프로젝트 설계]

프로젝트 생성 및 준비

images 폴더 생성 후 그 안에 delete,done,edit,send.png 넣기 (src > main > resources > static)

index.html (src > main > resources > static)

<!DOCTYPE html>
<html lang="en">

<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>Memo Service</title>

  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
  <link href="https://fonts.googleapis.com/css2?family=Noto+Sans+KR:wght@500&display=swap" rel="stylesheet">

  <style>
    @import url(//spoqa.github.io/spoqa-han-sans/css/SpoqaHanSans-kr.css);

    body {
      margin: 0px;
    }

    .area-edit {
      display: none;
    }

    .wrap {
      width: 538px;
      margin: 10px auto;
    }

    #contents {
      width: 538px;
    }

    .area-write {
      position: relative;
      width: 538px;
    }

    .area-write img {
      cursor: pointer;
      position: absolute;
      width: 22.2px;
      height: 18.7px;
      bottom: 15px;
      right: 17px;
    }

    .background-header {
      position: fixed;
      z-index: -1;
      top: 0px;
      width: 100%;
      height: 428px;
      background-color: #339af0;
    }

    .background-body {
      position: fixed;
      z-index: -1;
      top: 428px;
      height: 100%;
      width: 100%;
      background-color: #dee2e6;
    }

    .header {
      margin-top: 50px;
    }

    .header h2 {
      /*font-family: 'Noto Sans KR', sans-serif;*/
      height: 33px;
      font-size: 42px;
      font-weight: 500;
      font-stretch: normal;
      font-style: normal;
      line-height: 0.79;
      letter-spacing: -0.5px;
      text-align: center;
      color: #ffffff;
    }

    .header p {
      margin: 40px auto;
      width: 217px;
      height: 48px;
      font-family: 'Noto Sans KR', sans-serif;
      font-size: 16px;
      font-weight: 500;
      font-stretch: normal;
      font-style: normal;
      line-height: 1.5;
      letter-spacing: -1.12px;
      text-align: center;
      color: #ffffff;
    }

    textarea.field {
      width: 502px !important;
      height: 146px;
      border-radius: 5px;
      background-color: #ffffff;
      border: none;
      padding: 18px;
      resize: none;
    }

    textarea.field::placeholder {
      width: 216px;
      height: 16px;
      font-family: 'Noto Sans KR', sans-serif;
      font-size: 16px;
      font-weight: normal;
      font-stretch: normal;
      font-style: normal;
      line-height: 1;
      letter-spacing: -0.96px;
      text-align: left;
      color: #868e96;
    }

    .card {
      width: 538px;
      border-radius: 5px;
      background-color: #ffffff;
      margin-bottom: 12px;
    }

    .card .metadata {
      position: relative;
      display: flex;
      font-family: 'Spoqa Han Sans';
      font-size: 11px;
      font-weight: normal;
      font-stretch: normal;
      font-style: normal;
      line-height: 1;
      letter-spacing: -0.77px;
      text-align: left;
      color: #adb5bd;
      height: 14px;
      padding: 10px 23px;
    }

    .card .metadata .date {

    }

    .card .metadata .username {
      margin-left: 20px;
    }

    .contents {
      padding: 0px 23px;
      word-wrap: break-word;
      word-break: break-all;
    }

    .contents div.edit {
      display: none;
    }

    .contents textarea.te-edit {
      border-right: none;
      border-top: none;
      border-left: none;
      resize: none;
      border-bottom: 1px solid #212529;
      width: 100%;
      font-family: 'Spoqa Han Sans';
    }

    .footer {
      position: relative;
      height: 40px;
    }

    .footer img.icon-start-edit {
      cursor: pointer;
      position: absolute;
      bottom: 14px;
      right: 55px;
      width: 18px;
      height: 18px;
    }

    .footer img.icon-end-edit {
      cursor: pointer;
      position: absolute;
      display: none;
      bottom: 14px;
      right: 55px;
      width: 20px;
      height: 15px;
    }

    .footer img.icon-delete {
      cursor: pointer;
      position: absolute;
      bottom: 12px;
      right: 19px;
      width: 14px;
      height: 18px;
    }

    #cards-box {
      margin-top: 12px;
    }
  </style>
  <script>
    // 사용자가 내용을 올바르게 입력하였는지 확인합니다.
    function isValidContents(contents) {
      if (contents == '') {
        alert('내용을 입력해주세요');
        return false;
      }
      if (contents.trim().length > 140) {
        alert('공백 포함 140자 이하로 입력해주세요');
        return false;
      }
      return true;
    }

    // 익명의 username을 만듭니다.
    function genRandomName(length) {
      let result = '';
      let characters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789';
      let charactersLength = characters.length;
      for (let i = 0; i < length; i++) {
        let number = Math.random() * charactersLength;
        let index = Math.floor(number);
        result += characters.charAt(index);
      }
      return result;
    }

    // 수정 버튼을 눌렀을 때, 기존 작성 내용을 textarea 에 전달합니다.
    // 숨길 버튼을 숨기고, 나타낼 버튼을 나타냅니다.
    function editPost(id) {
      showEdits(id);
      let contents = $(`#${id}-contents`).text().trim();
      $(`#${id}-textarea`).val(contents);
    }

    function showEdits(id) {
      $(`#${id}-editarea`).show();
      $(`#${id}-submit`).show();
      $(`#${id}-delete`).show();

      $(`#${id}-contents`).hide();
      $(`#${id}-edit`).hide();
    }

    $(document).ready(function () {
      // HTML 문서를 로드할 때마다 실행합니다.
      getMessages();
    })

    // 메모를 불러와서 보여줍니다.
    function getMessages() {
      // 1. 기존 메모 내용을 지웁니다.
      $('#cards-box').empty();
      // 2. 메모 목록을 불러와서 HTML로 붙입니다.
      $.ajax({
        type: 'GET',
        url: '/api/memos',
        success: function (response) {
          for (let i = 0; i < response.length; i++) {
            let message = response[i];
            let id = message['id'];
            let username = message['username'];
            let contents = message['contents'];
            let modifiedAt = message['modifiedAt'];
            addHTML(id, username, contents, modifiedAt);
          }
        }
      })
    }

    // 메모 하나를 HTML로 만들어서 body 태그 내 원하는 곳에 붙입니다.
    function addHTML(id, username, contents, modifiedAt) {
      // 1. HTML 태그를 만듭니다.
      let tempHtml = `<div class="card">
                
                <div class="metadata">
                    <div class="date">
                        ${modifiedAt}
                    </div>
                    <div id="${id}-username" class="username">
                        ${username}
                    </div>
                </div>
                
                <div class="contents">
                    <div id="${id}-contents" class="text">
                        ${contents}
                    </div>
                    <div id="${id}-editarea" class="edit">
                        <textarea id="${id}-textarea" class="te-edit" name="" id="" cols="30" rows="5"></textarea>
                    </div>
                </div>
                
                <div class="footer">
                    <img id="${id}-edit" class="icon-start-edit" src="images/edit.png" alt="" onclick="editPost('${id}')">
                    <img id="${id}-delete" class="icon-delete" src="images/delete.png" alt="" onclick="deleteOne('${id}')">
                    <img id="${id}-submit" class="icon-end-edit" src="images/done.png" alt="" onclick="submitEdit('${id}')">
                </div>
            </div>`;
      // 2. #cards-box 에 HTML을 붙인다.
      $('#cards-box').append(tempHtml);
    }

    // 메모를 생성합니다.
    function writePost() {
      // 1. 작성한 메모를 불러옵니다.
      let contents = $('#contents').val();

      // 2. 작성한 메모가 올바른지 isValidContents 함수를 통해 확인합니다.
      if (isValidContents(contents) == false) {
        return;
      }
      // 3. genRandomName 함수를 통해 익명의 username을 만듭니다.
      let username = genRandomName(10);

      // 4. 전달할 data JSON으로 만듭니다.
      let data = {'username': username, 'contents': contents};

      // 5. POST /api/memos 에 data를 전달합니다.
      $.ajax({
        type: "POST",
        url: "/api/memos",
        contentType: "application/json",
        data: JSON.stringify(data),
        success: function (response) {
          alert('메시지가 성공적으로 작성되었습니다.');
          window.location.reload();
        }
      });
    }

    // 메모를 수정합니다.
    function submitEdit(id) {
      // 1. 작성 대상 메모의 username과 contents 를 확인합니다.
      let username = $(`#${id}-username`).text().trim();
      let contents = $(`#${id}-textarea`).val().trim();

      // 2. 작성한 메모가 올바른지 isValidContents 함수를 통해 확인합니다.
      if (isValidContents(contents) == false) {
        return;
      }

      // 3. 전달할 data JSON으로 만듭니다.
      let data = {'username': username, 'contents': contents};

      // 4. PUT /api/memos/{id} 에 data를 전달합니다.
      $.ajax({
        type: "PUT",
        url: `/api/memos/${id}`,
        contentType: "application/json",
        data: JSON.stringify(data),
        success: function (response) {
          alert('메시지 변경에 성공하였습니다.');
          window.location.reload();
        }
      });
    }

    // 메모를 삭제합니다.
    function deleteOne(id) {
      // 1. DELETE /api/memos/{id} 에 요청해서 메모를 삭제합니다.
      $.ajax({
        type: "DELETE",
        url: `/api/memos/${id}`,
        success: function (response) {
          alert('메시지 삭제에 성공하였습니다.');
          window.location.reload();
        }
      })
    }
  </script>
</head>

<body>
<div class="background-header">

</div>
<div class="background-body">

</div>
<div class="wrap">
  <div class="header">
    <h2>Memo</h2>
    <p>
      공유하고 싶은 소식을 입력해주세요.
    </p>
  </div>
  <div class="area-write">
        <textarea class="field" placeholder="공유하고 싶은 소식을 입력해주세요" name="contents" id="contents" cols="30"
                  rows="10"></textarea>
    
    <img src="images/send.png" alt="" onclick="writePost()">
  </div>
  <div id="cards-box" class="area-read">
    <div class="card">
      
      <div class="metadata">
        <div class="date">
          October 10, 2020
        </div>
        <div class="username">
          anonymous
        </div>
      </div>
      
      <div class="contents">

      </div>
      
      <div class="footer">
        <img id="1-edit" class="icon-start-edit" src="images/edit.png" alt="" onclick="editPost('1')">
        <img id="1-delete" class="icon-delete" src="images/delete.png" alt="" onclick="deleteOne('1')">
        <img id="1-submit" class="icon-end-edit" src="images/done.png" alt="" onclick="submitEdit('1')">
      </div>
    </div>
  </div>
</div>
</body>

</html>

메모장 기능 설계

접속 하자마자 메모 전체 목록 조회하기
- GET API 사용해서 메모 목록 불러오기
메모 생성하기
- POST API 사용해서 메모 신규 생성하기
- 생성된 메모 반환
메모 변경하기
- PUT API 사용해서 메모 내용 변경하기
- 사용자가 클릭한 메모가 DB에 존재하는지 확인하기
- 해당 메모 내용 변경
메모 삭제하기
- DELETE API 사용해서 메모 삭제하기
- 사용자가 삭제하려는 메모가 DB에 존재하는지 확인하기
- DB에서 해당 메모 삭제

API 테이블

[Create, Read 구현]

DTO (Data Transfer Object)

데이터 전송 및 이동을 위해 생성되는 객체

Client에서 보내오는 데이터를 객체로 처리할 때 사용
서버의 계층간의 이동에도 사용
DB와의 소통을 담당하는 Java 클래스를 그대로 Client에 반환하는 것이 아니라 DTO로 한번 변환한 후 반환할 때도 사용

📌 Request의 데이터를 처리할 때 사용되는 객체는 RequestDto,
Response를 할 때 사용되는 객체는 ResponseDto라는 이름을 붙여 DTO 클래스
(절대적인 규칙은 아니기 때문에 조직에 따라 규칙이 다를 수 있음)

Create 구현 (createMemo 메서드)

1. 메모 데이터를 저장할 Memo 클래스 생성

 package com.sparta.memo.entity;
 
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;
 
@Getter
@Setter
@NoArgsConstructor
public class Memo {
    private Long id;
    private String username;
    private String contents;
}

2. 메모 생성하기 API를 받을 수 있는 Controller와 메서드 생성

MemoController.java

 @RestController
@RequestMapping("/api")
public class MemoController {
 
    @PostMapping("/memos")
    public MemoResponseDto createMemo(@RequestBody MemoRequestDto requestDto) {
        return null;
    }
 
}

3. Client에 데이터를 반환할 때 사용할 MemoResponseDto 클래스 생성

 package com.sparta.memo.dto;
 
import lombok.Getter;
 
@Getter
public class MemoResponseDto {
    private Long id;
    private String username;
    private String contents;
}

4. Client의 요청 데이터를 받아줄 MemoRequestDto 클래스 생성

 package com.sparta.memo.dto;
 
import lombok.Getter;
 
@Getter
public class MemoRequestDto {
    private String username;
    private String contents;
}

4. RequestDto를 Entity로 수정하기 위해 Memo 객체 생성 (Client에서 보내준 데이터가 있는 requestDto를 넣어야하는데 현재 Memo 클래스에는 기본생성자 밖에 없으므로 Memo 클래스에 새롭게 생성자 추가할 것!)

MemoController.java

 @RestController
@RequestMapping("/api")
public class MemoController {
 
	@PostMapping("/memos")
	public MemoResponseDto createMemo(@RequestBody MemoRequestDto requestDto) {
 
		 // RequestDto -> Entity
		Memo memo = new Memo(requestDto);
	}
}

Memo.java

 	public Memo(MemoRequestDto requestDto) {
		this.username = requestDto.getUsername();
		this.contents = requestDto.getContents();
	}

5. ID 중복을 막기 위하여 현재 DB의 마지막 값을 구해서 +1을 하도록 하려는데
DB와 연결을 하지 않았기 때문에 메모 데이터를 저장할 Java 컬렉션 생성 (Map 자료구조 사용)

MemoController.java

 @RestController
@RequestMapping("/api")
public class MemoController {
 
	// 메모 데이터 저장할 컬렉션(맵) - Long에는 ID값 들어감(Key)
	private final Map<Long, Memo> memoList = new HashMap<>();
 
	@PostMapping("/memos")
	public MemoResponseDto createMemo(@RequestBody MemoRequestDto requestDto) {
 
		 // RequestDto -> Entity
  		Memo memo = new Memo(requestDto);
 
		// Memo Max ID Check
		Long maxId = memoList.size() > 0 ? Collections.max(memoList.keySet()) + 1 : 1;
		memo.setId(maxId);
}

6. DB에 저장

MemoController.java

 		// DB 저장
		memoList.put(memo.getId(), memo);

7. Entity를 ResponseDto로 바꾸어 반환하기 위해 MemoResponseDto 객체 생성 (memo를 넣어야 하는데 현재 MemoResponseDto 클래스에는 기본생성자 밖에 없으므로 MemoResponseDto 클래스에 새롭게 생성자 추가할 것!)

MemoController.java

 @RestController
@RequestMapping("/api")
public class MemoController {
 
	private final Map<Long, Memo> memoList = new HashMap<>();
 
	@PostMapping("/memos")
	public MemoResponseDto createMemo(@RequestBody MemoRequestDto requestDto) {
 
		 // RequestDto -> Entity
  		Memo memo = new Memo(requestDto);
 
		// Memo Max ID Check
		Long maxId = memoList.size() > 0 ? Collections.max(memoList.keySet()) + 1 : 1;
		memo.setId(maxId);
 
		// DB 저장
		memoList.put(memo.getId(), memo);
 
		// Entity -> ResponseDto
		MemoResponseDto memoResponseDto = new MemoResponseDto(memo);
		
		return memoResponseDto;
	}
}

MemoResponseDto.java

 	public MemoResponseDto(Memo memo) {
		this.id = memo.getId();
		this.username = memo.getUsername();
		this.contents = memo.getContents();
	}

Read 구현 (getMemos 메서드)

MemoController.java

 	@GetMapping("/memos")
	public List<MemoResponseDto> getMemos() {
		
		// Map To List
		List<MemoResponseDto> responseList = memoList.values().stream()
			.map(MemoResponseDto::new).toList();
 
		return responseList;
	}

[ 실행 화면 ]

[ PostMan에서 POST했을 때 ]

[ PostMan에서 GET했을 때 ]

[Update, Delete 구현]

Update 구현

MemoController.java

 	@PutMapping("/memos/{id}")
	public Long updateMemo(@PathVariable Long id, @RequestBody MemoRequestDto requestDto) {
 
		// 해당 메모가 DB에 존재하는지 확인
		if(memoList.containsKey(id)) {
 
			// 해당 메모 가져오기
			Memo memo = memoList.get(id);
 
			// memo 수정
			memo.update(requestDto);
 
			return memo.getId();
 
		} else {
			throw new IllegalArgumentException("선택한 메모는 존재하지 않습니다.");
		}
	}

Memo.java

 public void update(MemoRequestDto requestDto) {
    this.username = requestDto.getUsername();
    this.contents = requestDto.getContents();
}

Delete 구현

MemoController.java

 @DeleteMapping("/memos/{id}")
	public Long deleteMemo(@PathVariable Long id) {
		
		// 해당 메모가 DB에 존재하는지 확인
		if(memoList.containsKey(id)) {
			
			// 해당 메모 삭제하기
			memoList.remove(id);
			
			return id;
			
		} else {
			throw new IllegalArgumentException("선택한 메모는 존재하지 않습니다.");
		}
	}

[ 실행 화면 ]

메모 수정

메모 삭제

[Database]

'데이터의 집합' - 매일 사용하는 카톡 메시지, 인스타그램의 사진 등의 정보를 저장하고 관리해줌

DBMS

‘Database Management System’ 의 약자로, Database를 관리하고 운영하는 소프트웨어를 의미

RDBMS

‘Relational DBMS’의 약자로 관계형 데이터베이스
(종류 : MySQL, PostgreSQL 등)

RDBMS는 테이블(table)이라는 최소 단위로 구성되며, 이 테이블은 열(column)과 행(row)으로 이루어져짐
테이블간 FK(Foreign Key)를 통해 다른 데이터를 조합해서 함께 볼 수 있음

[SQL]

‘Structured Query Language’ 의 약자로, RDBMS에서 사용되는 언어

DDL

‘Data Definition Language’ 의 약자로, 테이블이나 관계의 구조를 생성

CREATE : 새로운 데이터베이스 및 테이블을 생성

 CREATE DATABASE 데이터베이스이름;
CREATE TABLE 테이블이름
(
		필드이름1 필드타입1,
    필드이름2 필드타입2,
    ...
);

<제약조건>
(1) AUTO_INCREMENT : 컬럼의 값이 중복되지 않게 1씩 자동으로 증가하게 해줘 고유번호를 생성

 CREATE TABLE 테이블이름
(
    필드이름 필드타입 AUTO_INCREMENT,
    // id bigint AUTO_INCREMENT,
    ...
);

(2) NOT NULL : 해당 필드는 NULL 값을 저장할 수 없게 됨

 CREATE TABLE 테이블이름
(
    필드이름 필드타입 NOT NULL,
    ...
);

(3) UNIQUE : 해당 필드는 서로 다른 값을 가져야만 함

 CREATE TABLE 테이블이름
(
    필드이름 필드타입 UNIQUE,
    ...
);

(4) PRIMARY KEY (기본키) : 해당 필드가 NOT NULL과 UNIQUE 제약 조건의 특징을 모두 가지게 됨

 CREATE TABLE 테이블이름
(
    필드이름 필드타입 PRIMARY KEY,
    ...
);

테이블 내에서 '유일하게 존재하는 값의 조합'을 설정해서 중복된 데이터가 테이블에 삽입되는 것을 방지
<기본 키를 사용하는 이유> : 데이터의 중복을 방지, 데이터를 매우 빠르게 찾을 수 있음

(5) FOREIGN KEY (외래키) : 하나의 테이블을 다른 테이블에 의존하게 만들며 데이터의 무결성을 보장
ㄴ FK 를 가지는 테이블이 참조하는 기준 테이블의 열은 반드시 PK, UNIQUE 제약조건이 설정되어 있어야함

 CREATE TABLE 테이블이름
(
    필드이름 필드타입,
    ...
	FOREIGN KEY(필드이름)
    REFERENCES 테이블이름(필드이름)
);

외래 키를 사용하면 중복되는 데이터를 없애고 어떤 테이블에서 외래 키를 사용해서 다른 테이블에 접근해 정보도 가져올 수 있음
기본 키가 하나의 테이블에서 중복된 데이터가 삽입되는 것을 방지하는 역할을 해주는데 외래 키 역시 비슷한 문제를 방지
외래 키는 데이터가 새롭게 추가될 때 외래 키에 해당하는 값이 외래 키가 참조하는 테이블에 존재하는지를 확인함

(6) CASCADE : FOREIGN KEY 로 연관된 데이터를 삭제,변경

 CREATE TABLE 테이블이름
(
    필드이름 필드타입,
    ...
		FOREIGN KEY(필드이름)
    REFERENCES 테이블이름(필드이름) ON DELETE CASCADE 
														 //ON UPDATE CASCADE
);

ALTER : 데이터베이스와 테이블의 내용을 수정

 ALTER TABLE 테이블이름 ADD 필드이름 필드타입;
ALTER TABLE 테이블이름 DROP 필드이름;
ALTER TABLE 테이블이름 MODIFY COLUMN 필드이름 필드타입;

DROP : 데이터 및 테이블 전체를 삭제

 DROP DATABASE 데이터베이스이름;
DROP TABLE 테이블이름;

TRUNCATE : 데이터베이스와 테이블을 삭제하는데 최초 테이블이 만들어졌던 상태 즉, 컬럼값만 남김

 TRUNCATE DATABASE 데이터베이스이름;
TRUNCATE TABLE 테이블이름;

DCL

‘Data Control Language’ 의 약자로. 데이터의 사용 권한을 관리하는데 사용

GRANT : 사용자 또는 ROLE에 대해 권한을 부여

 GRANT [객체권한명] (컬럼)
ON [객체명]
TO { 유저명 | 롤명 | PUBLC} [WITH GRANT OPTION];
 
//ex
GRANT SELECT ,INSERT 
ON mp
TO scott WITH GRANT OPTION;

REVOKE : 사용자 또는 ROLE에 부여한 권한을 회수

 REVOKE { 권한명 [, 권한명...] ALL}
ON 객체명
FROM {유저명 [, 유저명...] | 롤명(ROLE) | PUBLIC} 
[CASCADE CONSTRAINTS];
 
//ex
REVOKE SELECT , INSERT
ON emp
FROM scott
[CASCADE CONSTRAINTS];

DML

‘Data Manipulation Language’ 의 약자로, 테이블에 데이터를 검색, 삽입, 수정, 삭제

INSERT : 테이블에 새로운 row를 추가

 INSERT INTO 테이블이름(필드이름1, 필드이름2, 필드이름3, ...) VALUES(데이터값1, 데이터값2, 데이터값3, ...);
INSERT INTO 테이블이름 VALUES(데이터값1, 데이터값2, 데이터값3, ...);

SELECT : 테이블의 row를 선택

SELECT 필드이름 FROM 테이블이름 [WHERE 조건];

UPDATE : 테이블의 row의 내용을 수정

UPDATE 테이블이름 SET 필드이름1=데이터값1, 필드이름2=데이터값2, ... WHERE 필드이름=데이터값;

DELETE : 테이블의 row를 삭제

DELETE FROM 테이블이름 WHERE 필드이름=데이터값;

[SQL 연습]

IntelliJ에 Database 연동

CREATE

MAJOR 테이블

 CREATE TABLE IF NOT EXISTS MAJOR
(	
	major_code varchar(100) primary key comment '주특기코드', 
	major_name varchar(100) not null comment '주특기명',
	tutor_name varchar(100) not null comment '튜터'
);

STUDENT 테이블

 CREATE TABLE IF NOT EXISTS STUDENT
(
	student_code varchar(100) primary key comment '수강생코드', 
	name varchar(100) not null comment '이름',
	birth varchar(8) null comment '생년월일',
	gender varchar(1) not null comment '성별',
	phone varchar(11) null comment '전화번호',
	major_code varchar(100) not null comment '주특기코드',
	foreign key(major_code) references major(major_code)
);

EXAM 테이블

 CREATE TABLE IF NOT EXISTS EXAM
(
	student_code varchar(100) not null comment '수강생코드', 
	exam_seq int not null comment '시험주차', 
	score decimal(10,2) not null comment '시험점수',
	result varchar(1) not null comment '합불'
);

ERD

ALTER

 ALTER TABLE EXAM ADD PRIMARY KEY(student_code, exam_seq);
ALTER TABLE EXAM ADD CONSTRAINT exam_fk_student_code FOREIGN KEY(student_code) REFERENCES STUDENT(student_code);

ERD

INSERT

MAJOR 테이블

 INSERT INTO MAJOR VALUES('m1', '스프링', '남병관');
INSERT INTO MAJOR VALUES('m2', '노드', '강승현');
INSERT INTO MAJOR VALUES('m3', '플라스크', '이범규');
INSERT INTO MAJOR VALUES('m4', '루비온레일즈', '차은서');
INSERT INTO MAJOR VALUES('m5', '라라벨', '구름');
INSERT INTO MAJOR VALUES('m6', '리엑트', '임민영');
INSERT INTO MAJOR VALUES('m7', '뷰', '김서영');
INSERT INTO MAJOR VALUES('m8', '엥귤러', '한현아');

STUDENT 테이블

 INSERT INTO STUDENT VALUES('s1', '최원빈', '20220331', 'M', '01000000001', 'm1');
INSERT INTO STUDENT VALUES('s2', '강준규', '20220501', 'M', '01000000002', 'm1');
INSERT INTO STUDENT VALUES('s3', '김영철', '20220711', 'M', '01000000003', 'm1');
INSERT INTO STUDENT VALUES('s4', '예상기', '20220408', 'M', '01000000004', 'm6');
INSERT INTO STUDENT VALUES('s5', '안지현', '20220921', 'F', '01000000005', 'm6');
INSERT INTO STUDENT VALUES('s6', '이대호', '20221111', 'M', '01000000006', 'm7');
INSERT INTO STUDENT VALUES('s7', '정주혜', '20221117', 'F', '01000000007', 'm8');
INSERT INTO STUDENT VALUES('s8', '고미송', '20220623', 'F', '01000000008', 'm6');
INSERT INTO STUDENT VALUES('s9', '이용우', '20220511', 'M', '01000000009', 'm2');
INSERT INTO STUDENT VALUES('s10', '심선아', '20220504', 'F', '01000000010', 'm8');
INSERT INTO STUDENT VALUES('s11', '변정섭', '20220222', 'M', '01000000020', 'm2');
INSERT INTO STUDENT(student_code, name, gender, major_code) VALUES('s12', '권오빈', 'M', 'm3');
INSERT INTO STUDENT VALUES('s13', '김가은', '20220121', 'F', '01000000030', 'm1');
INSERT INTO STUDENT(student_code, name, gender, major_code) VALUES('s14', '김동현', 'M', 'm4');
INSERT INTO STUDENT VALUES('s15', '박은진', '20221101', 'F', '01000000040', 'm1');
INSERT INTO STUDENT(student_code, name, birth, gender, phone, major_code) VALUES('s16', '정영호', '20221105', 'M', '01000000050', 'm5');
INSERT INTO STUDENT(student_code, name, gender, major_code) VALUES('s17', '박가현', 'F', 'm7');
INSERT INTO STUDENT(student_code, name, birth, gender, phone, major_code) VALUES('s18', '박용태', '20220508', 'M', '01000000060', 'm6');
INSERT INTO STUDENT VALUES('s19', '김예지', '20220505', 'F', '01000000070', 'm2');
INSERT INTO STUDENT VALUES('s20', '윤지용', '20220909', 'M', '01000000080', 'm3');
INSERT INTO STUDENT VALUES('s21', '손윤주', '20220303', 'F', '01000000090', 'm6');

EXAM 테이블

 INSERT INTO EXAM VALUES('s1', 1, 8.5, 'P');
INSERT INTO EXAM VALUES('s1', 2, 9.5, 'P');
INSERT INTO EXAM VALUES('s1', 3, 3.5, 'F');
INSERT INTO EXAM VALUES('s2', 1, 8.2, 'P');
INSERT INTO EXAM VALUES('s2', 2, 9.5, 'P');
INSERT INTO EXAM VALUES('s2', 3, 7.5, 'P');
INSERT INTO EXAM VALUES('s3', 1, 9.3, 'P');
INSERT INTO EXAM VALUES('s3', 2, 5.3, 'F');
INSERT INTO EXAM VALUES('s3', 3, 9.9, 'P');
INSERT INTO EXAM VALUES('s4', 1, 8.4, 'P');
INSERT INTO EXAM VALUES('s5', 1, 9.5, 'P');
INSERT INTO EXAM VALUES('s5', 2, 3.5, 'F');
INSERT INTO EXAM VALUES('s6', 1, 8.3, 'P');
INSERT INTO EXAM VALUES('s7', 1, 9.2, 'P');
INSERT INTO EXAM VALUES('s7', 2, 9.9, 'P');
INSERT INTO EXAM VALUES('s7', 3, 3.6, 'F');
INSERT INTO EXAM VALUES('s8', 1, 8.4, 'P');
INSERT INTO EXAM VALUES('s9', 1, 9.7, 'P');
INSERT INTO EXAM VALUES('s10', 1, 8.4, 'P');
INSERT INTO EXAM VALUES('s10', 2, 9.8, 'P');
INSERT INTO EXAM VALUES('s10', 3, 8.4, 'P');
INSERT INTO EXAM VALUES('s11', 1, 8.6, 'P');
INSERT INTO EXAM VALUES('s12', 1, 9.2, 'P');
INSERT INTO EXAM VALUES('s13', 1, 8.1, 'P');
INSERT INTO EXAM VALUES('s13', 2, 9.5, 'P');
INSERT INTO EXAM VALUES('s13', 3, 2.1, 'F');
INSERT INTO EXAM VALUES('s14', 1, 9.2, 'P');
INSERT INTO EXAM VALUES('s15', 1, 9.7, 'P');
INSERT INTO EXAM VALUES('s15', 2, 1.7, 'F');
INSERT INTO EXAM VALUES('s16', 1, 8.4, 'P');
INSERT INTO EXAM VALUES('s17', 1, 9.3, 'P');
INSERT INTO EXAM VALUES('s17', 2, 9.9, 'P');
INSERT INTO EXAM VALUES('s17', 3, 1.3, 'F');
INSERT INTO EXAM VALUES('s18', 1, 9.9, 'P');
INSERT INTO EXAM VALUES('s19', 1, 9.4, 'P');
INSERT INTO EXAM VALUES('s19', 2, 8.9, 'P');
INSERT INTO EXAM VALUES('s19', 3, 7.4, 'F');
INSERT INTO EXAM VALUES('s20', 1, 8.1, 'P');
INSERT INTO EXAM VALUES('s20', 2, 6.4, 'F');
INSERT INTO EXAM VALUES('s21', 1, 9.5, 'P');
INSERT INTO EXAM VALUES('s21', 2, 8.8, 'P');
INSERT INTO EXAM VALUES('s21', 3, 8.2, 'P');

UPDATE

 INSERT INTO STUDENT VALUES('s0', '수강생', '20220331', 'M', '01000000005', 'm1');
UPDATE STUDENT SET major_code= 'm2' where student_code= 's0';

DELETE

DELETE FROM STUDENT WHERE student_code = 's0';

SELECT

 SELECT * FROM STUDENT;
SELECT * FROM STUDENT WHERE STUDENT_CODE = 's1';
SELECT name, major_code FROM STUDENT WHERE student_code = 's1';

JOIN

 SELECT s.name, s.major_code, m.major_name FROM STUDENT s JOIN MAJOR m ON s.major_code = m.major_code;
SELECT s.name, s.major_code, m.major_name FROM STUDENT s, MAJOR m WHERE s.major_code = m.major_code;

JOIN은 나누어진 테이블을 하나로 합치기 위해 데이터베이스가 제공하는 기능
ON 이라는 키워드를 통해 기준이 되는 컬럼을 선택하여 2개의 테이블을 합쳐줌
JOIN을 할 때에는 적어도 하나의 컬럼을 서로 공유하고 있어야 하기 때문에 테이블에 외래 키가 설정 되어 있다면 해당 컬럼을 통해 JOIN을 하면 조건을 충족가능

❗️다만 JOIN을 하기 위해 외래 키를 설정하는 것이 항상 좋은 선택이 아닐 수도 있음

외래 키를 설정하면 데이터 무결성을 확인하는 추가 연산이 발생
또한 무결성을 지켜야하기 때문에 상황에 따라 개발하는데 불편할 수 있음

👉 결론은 항상 테이블에 모든 제약조건을 걸어야 하는 것은 아님! 프로젝트의 상황에 따라 가장 효율적인 제약조건을 테이블에 적용할 것!

 # 문제 1)
# 수강생을 관리하는 MANAGER 테이블을 만들어보세요.
# - 컬럼은 총 id, name, student_code 입니다.
# - id는 bigint 타입이며 PK입니다.
# - name은 최소 2자 이상, varchar 타입, not null 입니다.
# - student_code는 STUDENT 테이블을 참조하는 FK이며 not null 입니다.
# - FK는 CONSTRAINT 이름을 ‘manager_fk_student_code’ 로 지정해야합니다.
CREATE TABLE IF NOT EXISTS MANAGER
(
    id bigint primary key,
    name varchar(100) not null,
    student_code varchar(100) not null,
    CONSTRAINT manager_fk_student_code foreign key(student_code) references STUDENT(student_code)
);
 
 
# 문제 2)
# ALTER, MODIFY를 이용하여 MANAGER 테이블의 id 컬럼에 AUTO_INCREMENT 기능을 부여하세요.
ALTER TABLE MANAGER MODIFY COLUMN id bigint auto_increment;
 
 
# 문제 3)
# INSERT를 이용하여 수강생 s1, s2, s3, s4, s5를 관리하는 managerA와 s6, s7, s8, s9를 관리하는 managerB를 추가하세요.
# - AUTO_INCREMENT 기능을 활용하세요
INSERT INTO MANAGER(name, student_code) VALUES('managerA', 's1');
INSERT INTO MANAGER(name, student_code) VALUES('managerA', 's2');
INSERT INTO MANAGER(name, student_code) VALUES('managerA', 's3');
INSERT INTO MANAGER(name, student_code) VALUES('managerA', 's4');
INSERT INTO MANAGER(name, student_code) VALUES('managerA', 's5');
 
INSERT INTO MANAGER(name, student_code) VALUES('managerB', 's6');
INSERT INTO MANAGER(name, student_code) VALUES('managerB', 's7');
INSERT INTO MANAGER(name, student_code) VALUES('managerB', 's8');
INSERT INTO MANAGER(name, student_code) VALUES('managerB', 's9');
 
 
# 문제 4)
# JOIN을 사용하여 managerA가 관리하는 수강생들의 이름과 시험 주차 별 성적을 가져오세요.
SELECT s.name, e.exam_seq, e.score
FROM MANAGER m JOIN STUDENT s on m.student_code  = s.student_code
               JOIN EXAM e on m.student_code  = e.student_code WHERE m.name = 'managerA';
 
 
# 문제 5)
# STUDENT 테이블에서 s1 수강생을 삭제했을 때 EXAM에 있는 s1수강생의 시험성적과 MANAGER의 managerA가 관리하는 수강생 목록에 자동으로 삭제될 수 있도록 하세요.
# - ALTER, DROP, MODIFY, CASCADE 를 사용하여 EXAM, MANAGER 테이블을 수정합니다.
ALTER TABLE EXAM DROP CONSTRAINT exam_fk_student_code;
ALTER TABLE EXAM ADD CONSTRAINT exam_fk_student_code FOREIGN KEY(student_code) REFERENCES STUDENT(student_code) ON DELETE CASCADE;
ALTER TABLE MANAGER DROP CONSTRAINT manager_fk_student_code;
ALTER TABLE MANAGER ADD CONSTRAINT manager_fk_student_code FOREIGN KEY(student_code) REFERENCES STUDENT(student_code) ON DELETE CASCADE;
 
DELETE FROM STUDENT WHERE student_code = 's1';

[JDBC]

JDBC

JDBC는 Java Database Connectivity로 DB에 접근할 수 있도록 Java에서 제공하는 API
JDBC에 연결해야하는 DB의 JDBC 드라이버를 제공하면 DB 연결 로직을 변경할 필요없이 DB 변경이 가능
ㄴ DB 회사들은 자신들의 DB에 맞도록 JDBC 인터페이스를 구현한 후 라이브러리로 제공하는데 이를 JDBC 드라이버라 부름
따라서 MySQL 드라이버를 사용해 DB에 연결을 하다 PostgreSQL 서버로 변경이 필요할 때 드라이버만 교체하면 손쉽게 DB 변경 가능!

JdbcTemplate

JDBC의 등장으로 손쉽게 DB교체가 가능해졌지만 아직도 DB에 연결하기 위해 여러가지 작업 로직들을 직접 작성해야하는 불편!
이를 해결하기 위해 커넥션 연결, statement 준비 및 실행, 커넥션 종료 등의 반복적이고 중복되는 작업들을 대신 처리해주는 JdbcTemplate이 등장.

JdbcTemplate 사용방법

1. application.properties에 DB에 접근하기 위한 정보를 작성

 spring.datasource.url=jdbc:mysql://localhost:3306/memo
spring.datasource.username=root
spring.datasource.password={비밀번호}
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

2. build.gradle에 MySQL과JDBC 라이브러리를 등록

3. memo 데이터베이스 생성 후 연동하고 memo table 생성

 create table memo
(
    id       bigint       not null auto_increment,
    contents varchar(500) not null,
    username varchar(255) not null,
    primary key (id)
);

4. DB연결이 필요한 곳에서 JdbcTemplate을 주입받아와 사용

MemoController.java

 package com.sparta.memo.controller;
 
import com.sparta.memo.dto.MemoRequestDto;
import com.sparta.memo.dto.MemoResponseDto;
import com.sparta.memo.entity.Memo;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.web.bind.annotation.*;
 
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
 
@RestController
@RequestMapping("/api")
public class MemoController {
 
    private final JdbcTemplate jdbcTemplate;
 
    public MemoController(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }  //생성자의 파라미터를 통해 JdbcTemplate 객체가 자동으로 넘어와 jdbctemplate 변수에 저장됨
 
    @PostMapping("/memos")
    public MemoResponseDto createMemo(@RequestBody MemoRequestDto requestDto) {
        // RequestDto -> Entity
        Memo memo = new Memo(requestDto);
 
        // DB 저장
        KeyHolder keyHolder = new GeneratedKeyHolder(); // 기본 키를 반환받기 위한 객체
		
        //INSERT
        String sql = "INSERT INTO memo (username, contents) VALUES (?, ?)";
        // jdbcTemplate.update() 메서드는 INSERT, UPDATE, DELETE 와 같이 생성, 수정, 삭제에 사용
        //첫 번째 파라미터로 SQL을 받고 그 이후에는 ?에 들어갈 값을 받음
        jdbcTemplate.update( con -> {
                    PreparedStatement preparedStatement = con.prepareStatement(sql,
                            Statement.RETURN_GENERATED_KEYS);
 
                    preparedStatement.setString(1, memo.getUsername());
                    preparedStatement.setString(2, memo.getContents());
                    return preparedStatement;
                },
                keyHolder);
 
        // DB Insert 후 받아온 기본키 확인
        Long id = keyHolder.getKey().longValue();
        memo.setId(id);
 
        // Entity -> ResponseDto
        MemoResponseDto memoResponseDto = new MemoResponseDto(memo);
 
        return memoResponseDto;
    }
 
    @GetMapping("/memos")
    public List<MemoResponseDto> getMemos() {
        // DB 조회
        String sql = "SELECT * FROM memo";
 
		//SELECT SQL 작성하여 String 변수에 저장후 query() 메서드 첫번째 파라미터에 넣어줌
		//SELECT의 경우 결과가 여러 줄로 넘어오기 때문에 RowMapper를 사용하여 한 줄씩 처리가능
        // RowMapper는 인터페이스이기 때문에 익명 클래스를 구현하여 처리
		// 오버라이딩 된 mapRow 메서드는 제네릭스에 선언한 MemoResponseDto 타입으로 데이터 한줄을 변환함
        return jdbcTemplate.query(sql, new RowMapper<MemoResponseDto>() {
            @Override
            public MemoResponseDto mapRow(ResultSet rs, int rowNum) throws SQLException {
                // SQL 의 결과로 받아온 Memo 데이터들을 MemoResponseDto 타입으로 변환해줄 메서드
                Long id = rs.getLong("id");
                String username = rs.getString("username");
                String contents = rs.getString("contents");
                return new MemoResponseDto(id, username, contents);
            }
        });
    }
 
    @PutMapping("/memos/{id}")
    public Long updateMemo(@PathVariable Long id, @RequestBody MemoRequestDto requestDto) {
        // 해당 메모가 DB에 존재하는지 확인
        Memo memo = findById(id);
        if(memo != null) {
            // memo 내용 수정
            // UPDATE SQL을 작성해 String 변수에 저장한 후 update() 메서드 첫번째 파라미터에 넣어줌
            String sql = "UPDATE memo SET username = ?, contents = ? WHERE id = ?";
            jdbcTemplate.update(sql, requestDto.getUsername(), requestDto.getContents(), id);
 
            return id;
        } else {
            throw new IllegalArgumentException("선택한 메모는 존재하지 않습니다.");
        }
    }
 
    @DeleteMapping("/memos/{id}")
    public Long deleteMemo(@PathVariable Long id) {
        // 해당 메모가 DB에 존재하는지 확인
        Memo memo = findById(id);
        if(memo != null) {
			// memo 삭제
            // DELETE SQL을 작성해 String 변수에 저장한 후 update() 메서드 첫번째 파라미터에 넣어줌
            String sql = "DELETE FROM memo WHERE id = ?";
            jdbcTemplate.update(sql, id);
 
            return id;
        } else {
            throw new IllegalArgumentException("선택한 메모는 존재하지 않습니다.");
        }
    }
 
    private Memo findById(Long id) {
        // DB 조회
        String sql = "SELECT * FROM memo WHERE id = ?";
 
        return jdbcTemplate.query(sql, resultSet -> {
            if(resultSet.next()) {
                Memo memo = new Memo();
                memo.setUsername(resultSet.getString("username"));
                memo.setContents(resultSet.getString("contents"));
                return memo;
            } else {
                return null;
            }
        }, id);
    }
}

Contents

[메모장프로젝트설계]

프로젝트생성및준비

메모장기능설계

[Create,Read구현]

DTO(DataTransferObject)

Create구현(createMemo메서드)

Read구현(getMemos메서드)

[Update,Delete구현]

Update구현

Delete구현

[Database]

DBMS

RDBMS

[SQL]

DDL

DCL

DML

[SQL연습]

IntelliJ에Database연동

CREATE

ALTER

INSERT

UPDATE

DELETE

SELECT

JOIN

[JDBC]

JDBC

JdbcTemplate

JdbcTemplate사용방법

당신이 좋아할만한 콘텐츠

댓글 0

소중한 공감 감사합니다

Receive promotional offers?

포스팅 주소를 복사했습니다

이 글이 도움이 되었다면 공감 부탁드립니다.

티스토리툴바