-- Spring 3.x 버전 특징
구 -> 신
1. Java 17 이상 사용
2. javax.* -> jakarta.*
3. application.properties / application.yaml (use-legacy-processing)
4. AntPathMatcher -> PathPatternParser
5. 외부 라이브러리 사용 시, Jakarta, Spring Framework 6.x 버전 지원 여부를 확인해야 함
1. Basic Authentication
- 사용자 이름 / 비밀번호를 Base64로 인코딩하여 Authorizaiton 헤더에 포함하여 전송
- 매우 안전하지 않음, SSL/TLS와 함께 사용됨
Ex: Authorization: Basic ~~~
2. Bearer Token Authentication
- 헤더에 토큰을 포함하여 전송 Authorization 헤더에 포함하여 전송
- JWT을 사용하여 인증
- 간단한 방식, 상태를 유지하지 않음, 확장성이 높음
- 단점 : 토큰 노출 위험, 토큰 관리가 힘듬, 귀찮음
Ex: Authorizaiton: Bearer ~~~~~
3. OAuth 방식
- 토큰기반 인증 방식, 사용자가 직접 자격을 증명 X 미리 인증 받아서 토큰을 발급 받고
- 이 토큰을 이용하여 API를 요청하는 방식, OAuth 2.0
Ex: Kakao, Naver, Git, Facebook login ..
4. API Key 방식
- 발급 받은 키를 요청
5. Session based Authentication
- Session ID를 생성하여 세션이나 쿠키에 포함하여 인증
- JWT (JSON Web Token) : 클레임이라고 불리는 정보를 JSON 형태로 안전하게 전송하기 위한 토큰
- 인증과 정보 교환에 사용, 서명이 되어 있어서 신뢰성 확보가 가능
1. Header : 토큰의 타입과 사용된 알고리즘 정보를 담고 있음, Base64 URL로 인코딩
2. Payload : 클레임 정보, 대상, 발행자, 만료 시간 등 다양한 정보가 포함, Base64 URL로 인코딩
3. Signature : Header와 Payload, Secret Key를 사용하여 생성된 서명
인증, 정보교환
장점 :
상태유지하지 않는다. = stateless, 서버가 클라이언트의 상태를 유지안한다
간단하고 자기 포함적
확장성이 높다, 토큰을 만들어 놓으면 여러 시스템에서 사용 가능
단점 :
크기 : 클레임이 많을수록 토큰의 크기가 커짐
보안 : 서명은 되어있지만, 암호화는 되어있지 않음, 중요한 정보를 JWT에 포함할 수 없음
토큰 관리 : 만료 시간, 갱신 잘해줘야 함
3. Spring Initializr로 Gradle Project 생성 (Gradle 2.7.14)
- src/main/java/test 하위 삭제및 src/main/java 하위 폴더 구조 잡기
CREATE OR REPLACE VIEW board_list_view AS
SELECT
B.board_number AS board_number
, B.title AS title
, B.content AS content
, I.image AS image
, B.favorite_count AS favorite_count
, B.comment_count AS comment_count
, B.view_count AS view_count
, B.write_datetime AS write_datetime
, B.writer_email AS writer_email
, U.nickname AS writer_nickname
, U.profile_image AS writer_profile_image
FROM board AS B
INNER JOIN user AS U
ON B.writer_email = U.email
LEFT JOIN (SELECT board_number, ANY_VALUE(image) AS image FROM image GROUP BY board_number) AS I
ON B.board_number = I.board_number;
2. API 기반 DML 작성하기
-- Active: 1725368211487@@localhost@3306@board
-- 회원가입
INSERT INTO user VALUES ('email@email.com', 'P!ssw0rd', 'nickname', '01012345678', '서울특별시 종로구', '그랑서울', null);
-- 로그인
SELECT * FROM user WHERE email = 'email@email.com';
-- 게시물 작성
INSERT INTO board (title, content, write_datetime, favorite_count, comment_count, view_count, writer_email)
VALUES ('제목', '내용', '2024-09-06 23:25:54', 0, 0, 0, 'email@email.com');
INSERT INTO image
VALUES (2, 'url');
-- 댓글 작성
INSERT INTO comment (content, write_datetime, user_email, board_number)
VALUES ('반갑습니다', '2024-09-06 23:28:50', 'email@email.com', 3);
UPDATE board
SET comment_count = comment_count + 1
WHERE board_number = 2;
-- 좋아요 등록, 취소
INSERT INTO favorite
VALUES ('email@email.com', 2);
UPDATE board
SET favorite_count = favorite_count + 1
WHERE board_number = 2;
DELETE FROM favorite
WHERE user_email = 'email@email.com'
AND board_number = 2;
UPDATE board
SET favorite_count = favorite_count - 1
WHERE board_number = 2;
-- 게시물 수정
UPDATE board
SET title = '수정 제목'
, content = '수정 내용'
WHERE board_number = 2;
DELETE FROM image
WHERE board_number = 2;
INSERT INTO image
VALUES (2, 'url');
INSERT INTO image
VALUES (3, 'url3');
-- 게시물 삭제
DELETE FROM comment WHERE board_number = 2;
DELETE FROM favorite WHERE board_number = 2;
DELETE FROM image WHERE board_number = 2;
DELETE FROM board WHERE board_number = 2;
-- 상세 게시물 불러오기
SELECT
B.board_number AS board_number
, B.title AS title
, B.content AS content
, B.write_datetime AS write_datetime
, B.writer_email AS writer_email
, U.nickname AS nickname
, U.profile_image AS profile_image
FROM board AS B
INNER JOIN user AS U
ON B.writer_email = U.email
WHERE board_number = 2;
SELECT image
FROM image
WHERE board_number = 2;
SELECT
U.email AS email
, U.nickname AS nickname
, U.profile_image AS profile_image
FROM favorite AS F
INNER JOIN user AS U
ON F.user_email = U.email
WHERE F.board_number = 2;
SELECT
U.nickname AS nickname
, U.profile_image AS profile_image
, C.write_datetime AS write_datetime
, C.content AS content
FROM comment AS C
INNER JOIN user AS U
ON C.user_email = U.email
WHERE C.board_number = 2
ORDER BY write_datetime DESC;
-- 최신 게시물 리스트 불러오기
SELECT *
FROM board AS B
INNER JOIN user AS U
ON B.writer_email = U.email
LEFT JOIN image AS I
ON B.board_number = I.board_number;
-- 최신 게시물 리스트 불러오기 (image row 중복 제거)
-- SELECT
-- B.board_number AS board_number
-- , B.title AS title
-- , B.content AS content
-- , I.image AS image
-- , B.favorite_count AS favorite_count
-- , B.comment_count AS comment_count
-- , B.view_count AS view_count
-- , B.write_datetime AS write_datetime
-- , U.nickname AS writer_nickname
-- , U.profile_image AS writer_profile_image
-- FROM board AS B
-- INNER JOIN user AS U
-- ON B.writer_email = U.email
-- LEFT JOIN (SELECT board_number, ANY_VALUE(image) AS image FROM image GROUP BY board_number) AS I
-- ON B.board_number = I.board_number
SELECT * FROM board_list_view
ORDER BY write_datetime DESC
-- LIMIT 0, 5; -- (페이지 - 1) * 5
LIMIT 5, 5;
-- 검색어 리스트
-- SELECT
-- B.board_number AS board_number
-- , B.title AS title
-- , B.content AS content
-- , I.image AS image
-- , B.favorite_count AS favorite_count
-- , B.comment_count AS comment_count
-- , B.view_count AS view_count
-- , B.write_datetime AS write_datetime
-- , U.nickname AS writer_nickname
-- , U.profile_image AS writer_profile_image
-- FROM board AS B
-- INNER JOIN user AS U
-- ON B.writer_email = U.email
-- LEFT JOIN (SELECT board_number, ANY_VALUE(image) AS image FROM image GROUP BY board_number) AS I
-- ON B.board_number = I.board_number
SELECT * FROM board_list_view
WHERE title LIKE '%수정%' OR content LIKE '%수정%'
ORDER BY write_datetime DESC;
-- 검색어 주간 상위 TOP 3 불러오기
-- SELECT
-- B.board_number AS board_number
-- , B.title AS title
-- , B.content AS content
-- , I.image AS image
-- , B.favorite_count AS favorite_count
-- , B.comment_count AS comment_count
-- , B.view_count AS view_count
-- , B.write_datetime AS write_datetime
-- , U.nickname AS writer_nickname
-- , U.profile_image AS writer_profile_image
-- FROM board AS B
-- INNER JOIN user AS U
-- ON B.writer_email = U.email
-- LEFT JOIN (SELECT board_number, ANY_VALUE(image) AS image FROM image GROUP BY board_number) AS I
-- ON B.board_number = I.board_number
SELECT * FROM board_list_view
WHERE write_datetime BETWEEN '2024-09-02 00:00:00' AND '2024-09-08 23:59:59'
ORDER BY favorite_count DESC, comment_count DESC, view_count DESC, write_datetime DESC
LIMIT 3; -- 3개만
-- 특정 유저 게시물 리스트 불러오기
-- SELECT
-- B.board_number AS board_number
-- , B.title AS title
-- , B.content AS content
-- , I.image AS image
-- , B.favorite_count AS favorite_count
-- , B.comment_count AS comment_count
-- , B.view_count AS view_count
-- , B.write_datetime AS write_datetime
-- , U.nickname AS writer_nickname
-- , U.profile_image AS writer_profile_image
-- FROM board AS B
-- INNER JOIN user AS U
-- ON B.writer_email = U.email
-- LEFT JOIN (SELECT board_number, ANY_VALUE(image) AS image FROM image GROUP BY board_number) AS I
-- ON B.board_number = I.board_number
SELECT * FROM board_list_view
WHERE writer_email = 'email@email.com'
ORDER BY write_datetime DESC, board_number DESC;
-- 인기 검색어 리스트
SELECT search_word, count(search_word) AS count -- search_word를 기준으로 count를 친다.
FROM search_log
WHERE relation IS FALSE -- 관계형이 아닌 것
GROUP BY search_word; -- search_word로 group by 해준다.
ORDER BY count DESC
LIMIT 15;
-- 관련 검색어 리스트
SELECT relation_word, count(relation_word) AS count
FROM search_log
WHERE search_word = '검색어'
GROUP BY relation_word
ORDER BY count DESC
LIMIT 15;
-- 유저 정보 불러오기 / 로그인 유저 정보 불러오기
SELECT *
FROM user
WHERE email = 'email@email.com';
-- 닉네임 수정하기
UPDATE user
SET nickname = '수정 닉네임'
WHERE email = 'email@email.com';
-- 프로필 이미지 수정
UPDATE user
SET profile_image = 'new_profile_url'
WHERE email = 'email@email.com';
