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">
<!-- date/username 영역 -->
<div class="metadata">
<div class="date">
${modifiedAt}
</div>
<div id="${id}-username" class="username">
${username}
</div>
</div>
<!-- contents 조회/수정 영역-->
<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>
<!-- <button class="btn btn-danger" onclick="writePost()">작성하기</button>-->
<img src="images/send.png" alt="" onclick="writePost()">
</div>
<div id="cards-box" class="area-read">
<div class="card">
<!-- date/username 영역 -->
<div class="metadata">
<div class="date">
October 10, 2020
</div>
<div class="username">
anonymous
</div>
</div>
<!-- contents 조회/수정 영역-->
<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에서 해당 메모 삭제
[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 '합불'
);
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);
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);
}
}
'TIL' 카테고리의 다른 글
| [TIL] 230520 <Spring> Spring Data JPA (0) | 2024.05.20 |
|---|---|
| [TIL] 230517 <Spring> 3 Layer Architecture, IoC와 DI, JPA CORE (0) | 2024.05.17 |
| [TIL] 230514 <Spring> Spring MVC (0) | 2024.05.14 |
| [TIL] 230513 <Spring> 학습 준비하기 (0) | 2024.05.13 |
| [TIL] 240510 불변 객체(Immutable Object) (0) | 2024.05.10 |
Contents
소중한 공감 감사합니다