[내일배움캠프_Spring Boot 입문주차] Database와 SQL

Database(데이터베이스)

데이터베이스는 데이터의 집합으로, 정보를 저장하고 관리해주는 역할 수행

DBMS(Database Management System): Database를 관리하고 운영하는 소프트웨어

RDBMS(Relational DBMS)

관계형 데이터베이스

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

---

SQL(Structured Query Language)

SQL은 RDBMS에서 사용되는 언어로, 수 많은 정보를 Database에서 조작하고 관리하기 위해서는 SQL 언어를 사용해야 함

• 국제표준화기구에서 SQL에 대한 표준을 정해서 발표하고 있음
• 그러나 DBMS를 만드는 회사가 여러 곳이기 때문에 DBMS 마다 표준 SQL을 준수하되, 각 제품의 특성을 반영하기 위한 약간의 차이가 존재함

DDL(Data Definition Language)

테이블이나 관계의 구조를 생성하는데 사용

 

CREATE

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

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

 

[제약 조건]

• AUTO_INCREMENT: 컬럼의 값이 중복되지 않게 1씩 자동으로 증가하게 해줌(고유번호 생성)

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

• NOT NULL: 해당 필드는 NULL 값을 저장할 수 없음

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

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

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

• PRIMARY KEY: 해당 필드가 NOT NULL과 UNIQUE 제약 조건의 특징을 모두 가짐

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

PRIMARY KEY(기본 키)는 테이블 내에서 '유일하게 존재하는 값의 조합'을 설정해서 중복된 데이터가 테이블에 삽입되는 것을 방지하는 제약조건임

1. 데이터의 중복 방지
- 만약 기본키를 설정하지 않고서 회원을 관리하는 테이블이 있을 때 기존에 있던 회원이 예전에 가입한 것을 잊고 다시 회원가입을 하는 경우 혹은 최근에 번호 및 주소가 변경된 경우에
둘 중에 어느 정보가 정확한 정보인지 판단하기 어려움 ► 데이터 무결성이 깨짐

2. 데이터를 빠르게 탐색 가능
- 기본 키를 설정하면 DBMS는 인덱스를 만듦
- 인덱스는 해당 데이터를 빨리 찾을 수 있게 도와주는 일종의 목차임
- 만약 주민번호 컬럼에 기본 키가 설정되어 있지 않는다면, 주민번호가 중복될 수 없다는 것은 우리는 알지만 DBMS는 모르기 때문에 주민번호가 중복될 수 있는 것을 가정해 5000만 row를 전부 확인하게됨
이때, 주민번호에 기본 키 설정이 되어있으면 row를 전부 확인하지 않고 1개만 찾으면 바로 해당 데이터 반환 가능

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

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

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

• 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 { 유저명 | 롤명 | PUBLIC } [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
[CASCASE 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 연동하기

1. 오른쪽 탭에 Database 탭 클릭하고 + 버튼 누른 후, 데이터 소스 > MySQL 클릭

 

2. 사용자, 비밀번호, 데이터베이스 정보 추가 후 OK 클릭

 

3. 데이터베이스 연결 성공

---

테이블 생성하기

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 '튜터'
);

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

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 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 INTO MAJOR VALUES('m1', '스프링', '남병관');
INSERT INTO MAJOR VALUES('m2', '노드', '강승현');
INSERT INTO MAJOR VALUES('m3', '플라스크', '이범규');
INSERT INTO MAJOR VALUES('m4', '루비온레일즈', '차은서');
...

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 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 STUDENT VALUES('s0', '수강생', '20220331', 'M', '01000000005', 'm1');
UPDATE STUDENT SET major_code= 'm2' where student_code= 's0';

테이블 내 데이터 삭제하기

DELETE FROM STUDENT WHERE student_code = 's0';

테이블 내 특정 데이터 조회하기

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

2번째 SELECT문 조회

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 SELECT 수행

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

‼️ JOIN을 하기 위해 외래 키를 설정하는 것이 항상 좋은 선택이 아닐 수 있음!
- 외래 키를 설정하면 데이터 무결성을 확인하는 추가 연산 발생
- 또한 무결성을 지켜야하기 때문에 상황에 따라 개발하는데 불편할 수 있음

항상 테이블에 모든 제약조건을 걸어야 하는 것은 아니고, 프로젝트의 상황에 따라 가장 효율적인 제약조건을 테이블에 적용해야 함

---

JDBC란?

애플리케이션 서버와 데이터베이스는 요청을 처리하기 위해 소통을 해야함. 이때, Java 애플리케이션과 데이터베이스를 연결해주는 역할을 하는 것이 JDBC임!

애플리케이션 서버와 데이터베이스 간의 소통

애플리케이션 서버와 데이터베이스 간의 소통

1. 데이터베이스 연결을 위해 커넥션 연결해야 함

2. SQL 작성 후 커넥션을 통해 SQL 요청

3. 요청한 SQL에 대한 응답을 받아야함

 

이때 MySQL 서버가 아니라 PostgreSQL 서버로 변경해야 하는 경우, MySQL과 PostgreSQL은 커넥션을 연결하는 방법, SQL 전달 방법, 결과 응답 전달하는 방법 모두 다를 수 있음

즉, 애플리케이션 서버 쪽에 작성했던 데이터베이스 연결 코드인 로직을 전부 수정해야 함

 

이런 문제를 해결하기 위해 JDBC 표준 인터페이스 등장함!

JDBC(Java Database Connectivity)

데이터베이스에 접근할 수 있도록 Java에서 제공하는 API

• JDBC에 연결을 해야하는 데이터베이스의 JDBC 드라이버를 제공하면, 이 데이터베이스의 연결 로직을 변경할 필요 없이 손쉽게 데이터베이스 변경 가능
• JDBC 드라이버는 데이터베이스 회사들이 자신들의 데이터베이스에 맞도록 JDBC 인터페이스를 실제로 구현한 후 제공한 라이브러리
  → MySQL 드라이버를 사용해서 데이터베이스 연결을 사용하고 있다가 PostgreSQL 서버로 변경이 필요할 때, 드라이버만 교체해주면 됨

JDBC

JdbcTemplate

데이터베이스 연결 과정

 

JDBC로 손쉽게 데이터베이스 교체가 가능해졌지만, 데이터베이스 연결을 하기 위해 위와 같이 수많은 작업 필요함

이런 작업들을 대신 처리해주는 Spring JDBC 등장함! 그 중 하나가 JdbcTemplate임

 

JdbcTemplate를 사용하기 위해 application.properties에 데이터베이스에 접근하기 위한 정보 작성해야함!

﹡실제로 개발할 때에는 JdbcTemplate을 사용할 확률이 낮음

// build.gradle 내 dependencies에 추가
// MySQL
implementation 'mysql:mysql-connector-java:8.0.28'
implementation 'org.springframework.boot:spring-boot-starter-data-jdbc'

# memo 데이터베이스 생성하기
create database memo;

// application.properties에 추가
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

 

Memo 테이블 생성

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

 

MemoController에 JdbcTemplate 적용

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; // jdbcTemplate 변수 선언

    // 초기화를 위한 생성자
    // 외부에서 만들어져 있는 JdbcTemplate을 여기에 넣어줌
    // JdbcTemplate 객체는 Spring에서 따로 관리해주고 있음
    public MemoController(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    @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 (?, ?)"; // INSERT문
        // INSERT, UPDATE, DELETE 세 가지 모두 update 메서드 이용
        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";

        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 내용 수정
            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 삭제
            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);
    }
}

 

JdbcTemplate을 사용해도 작성하기 어렵고 까다로운 문제 존재함. 자바 개발자들을 위해 데이터베이스와 Java 객체를 mapping해서 소통할 수 있는 ORM이라는 기술 등장함!