Spring 입문 1-4 (메모장 프로젝트 ~ JDBC)

SJ.CHO·2024년 9월 27일

CRUD JDBC Spring WAS WebServer db sql

스파르타코딩클럽_Spring3기_Spring_강의

목록 보기

4/19

프로젝트 설계

기능
API 테이블

DTO

DTO(Data Transfer Object)는 데이터 전송 및 이동을 위해 생성되는 객체를 의미
Client가 보내는 데이터를 객체로처리 할때 사용
서버 계층간의 이동에서도 사용됌.
DB와의 소통 담당 JAVA클래스를 그대로 반환하는게 아닌 DTO로 변환 후 반환.
Request의 데이터를 처리할 때 사용되는 객체는 RequestDto, Response를 할 때 사용되는 객체는 ResponseDto라는 이름을 붙여 DTO 클래스

Create, Read, Update, Delete 구현하기

1. Create

@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;
}

아직 DB와 연결되지 않았기 때문에 Map 을 임시 DB로 선정
RequestDto 를 @RequestBody(JSON 데이터 객체화) 로 받아와 Memo memo = new Memo(requestDto); 를 통해 Enttiy 화 후 DB저장 작업을 수행하고 ResponseDto 형태로 만들어 반환한다.

2. Read

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

    return responseList;
}

DB역할을 하는 Map 을 List화 시켜서 반환한다.

3. Update

@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("선택한 메모는 존재하지 않습니다.");
    }
}

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

@PathVariable 을 통해 id를 받아온다.
해당 메모가 DB에 존재하는지 확인 후, 변경 내용을 Set하여 재저장 이후 id 값을 반환해준다.

4. Delete

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

@PathVariable 을 통해 id를 받아온다.
해당 메모가 DB에 존재하는지 확인 후, DB에서 값을 삭제한다 이후 id 값을 반환해준다.

SQL

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

1. DDL

‘Data Definition Language’ 의 약자로 테이블이나 관계의 구조를 생성하는데 사용

Craete : 새로운 DB 및 Table 생성에 사용


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

ALTER : DB 와 Table 내용을 수정

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

DROP : DB와 TABLE 삭제가능(전체 삭제)

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

TRUNCATE : DB 와 Table 을 컬럼값만 남기고 삭제 (초기화)

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

2. 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];

3. 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 필드이름=데이터값;

제약조건

AUTO_INCREMENT : 컬럼의 값이 중복되지 않도록 1씩 자동으로 증가한다. (고유번호 생성할때 많이 사용)
NOT NULL : 해당 필드에는 NULL 값을 허용하지않음.
UNIQUE : 해당 필드는 중복을 허용하지않음.
PRIMARY KEY : 해당필드가 NOT NULL, UNIQUE 제약을 가짐. 자세한설명(KEY 부분참조)
FOREIGN KEY : 하나의 테이블을 다른테이블과의 의존관계를 만들어 데이터 무결성 보장 도구 (위 기본키 링크 참조)

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

CASCADE : FOREIGN KEY 로 연관된 데이터를 삭제, 변경이 가능

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

JOIN : 링크 참조(클릭)

JDBC

등장 배경:
APP Server에서 DB에 접근하기 위해선 여러가지 확인작업이 필요.
하지만 다른 RDBMS 시스템으로 변경시 로직의 변경이 필요.
이러한 확장성 문제를 해결하기 위해 JDBC InterFace 가 등장
각 DBMS 회사가 자신들의 DB에 맞도록 JDBC 인터페이스 수현 및 라이브러리 등록 : JDBC 드라이버

JdbcTemplate

JDBC로 인한 DB의 교체 는 편해졌지만, 연결에대한 불편함은 아직 존재.
이런 불편함을 해결하기 위한 반복적이고 중복되는 작업들을 대신 처리해주는 JdbcTemplate 이 등장

private final JdbcTemplate jdbctemplate;

public MemoRepository(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
}

DB 연결이 필요한곳에 JdbcTemplate 을 외부에서 주입해와서 사용.

INSERT

    @PostMapping("/memos")
    public MemoResponseDto createMemo(@RequestBody MemoRequestDto requestDto) {
        // RequestDto -> Entity
        Memo memo = new Memo(requestDto);

        // DB 저장
        KeyHolder keyHolder = new GeneratedKeyHolder(); // 기본 키를 반환받기 위한 객체

        String sql = "INSERT INTO memo (username, contents) VALUES (?, ?)";
        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;
    }

String sql = "INSERT INTO memo (username, contents) VALUES (?, ?)";
jdbcTemplate.update(sql, "Robbie", "오늘 하루도 화이팅!");

SQL 을 작성 후 String 변수에 저장, 데이터 부분은 동적인 요소기에 동적바인딩을 사용한다.
jdbcTemplate.update() 메서드는 INSERT, UPDATE, DELETE 와 같이 생성, 수정, 삭제에 사용될 수 있는데 첫 번째 파라미터로 SQL을 받고 그 이후에는 ?에 들어갈 값을 받음.

2, UPDATE

@PutMapping("/memos/{id}")
    public Long updateMemo(@PathVariable Long id, @RequestBody MemoRequestDto requestDto) {
        // 해당 메모가 DB에 존재하는지 확인
        Memo memo = findById(id);
        if(memo != null) {
            // memo 내용 수정
            String sql = "UPDATE memo SET username = ?, contents = ? WHERE id = ?";
            jdbcTemplate.update(sql, requestDto.getUsername(), requestDto.getContents(), id);

            return id;
        } else {
            throw new IllegalArgumentException("선택한 메모는 존재하지 않습니다.");
        }
    }

String sql = "UPDATE memo SET username = ? WHERE id = ?";
jdbcTemplate.update(sql, "Robbert", 1);

UPDATE SQL 을 작성해 메서드 파라미터로 전송.

DELETE

    @DeleteMapping("/memos/{id}")
    public Long deleteMemo(@PathVariable Long id) {
        // 해당 메모가 DB에 존재하는지 확인
        Memo memo = findById(id);
        if(memo != null) {
						// memo 삭제
            String sql = "DELETE FROM memo WHERE id = ?";
            jdbcTemplate.update(sql, id);

            return id;
        } else {
            throw new IllegalArgumentException("선택한 메모는 존재하지 않습니다.");
        }
    }

UPDATE 와 비슷한 로직을 지님.

    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);
    }

UPDATE 와 DELETE 가 지니는 ID값이 DB에 존재하는지 확인하는 메소드

SELECT

    @GetMapping("/memos")
    public List<MemoResponseDto> getMemos() {
        // DB 조회
        String sql = "SELECT * FROM memo";

        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);
            }
        });
    }

memo 테이블에 있는 Row 들을 DTO 타입의 객체로 감싸서 한줄 식 처리
Row : Object = 1:1 관계를 갖는다.

~~사실 요즘 JDBC 잘안쓴다.~~

숙제

문제 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)
    )

IF NOT EXISTS : 해당 테이블이 존재하지 않을시 생성.
CONSTRAINT : 제약조건 생성

문제 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.score
    from manager m
join student s
on m.student_code = s.student_code
join exam e
on s.student_code = e.student_code
where m.name = 'managerA';

3중 Join

문제 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';

fk 에대한 제약조건을 삭제해줘야 해당 학생 row를 삭제가 가능해짐.
CASCADE 를 이용하고 CONSTRAINT 를 삭제해줌으로써 제약조건을 없애면 삭제가 가능해진다.

SJ.CHO

70살까지 개발하고싶은 개발자

이전 포스트

Spring 입문 1-3 (Data to Server)

다음 포스트

Spring 입문 1-4 (메모장 프로젝트 ~ JDBC)

스파르타코딩클럽_Spring3기_Spring_강의

프로젝트 설계

기능

API 테이블

DTO

Create, Read, Update, Delete 구현하기

1. Create

2. Read

3. Update

4. Delete

SQL

1. DDL

2. DCL

3. DML

제약조건

JDBC

JdbcTemplate

숙제

Spring 입문 1-3 (Data to Server)

Spring 입문 2-1 (3 Layer Architecture ~ IoC, DI)

0개의 댓글