[048] 뷰&정규화 과정 (+JDBC 프로그래밍 중요코드)

 

2024.07.16(화)
 
MySQL 5일차
 
하단에 중요한 코드 3개를 적어놨다.

출퇴근 때 계속계속 보기 !!

---

 

이것이 MySQL이다
 

PART01 MySQL 설치 및 DB 구축과정 미리 실습하기

• CH01 DBMS 개요와 MySQL 소개
• CH02 MySQL 설치
• CH03 MySQL 전체 운영 실습

PART02 MySQL 기본

• CH04 데이터베이스 모델링
• CH05 MySQL 유틸리티 사용법
• CH06 SQL 기본
• CH07 SQL 고급

PART03 MySQL 고급

• CH08 테이블과 뷰
• CH09 인덱스
• CH10 JDBC 프로그래밍
• CH11 전체 텍스트 검색과 파티션

---

 

CH08.2 뷰

 

뷰(View)

뷰의 정의

• 이전에 작성했던 복잡한 쿼리를 재사용할 수 있도록 저장한 객체.
• 복잡한 쿼리를 단순화하고 보안에 도움을 줌.

뷰 생성 구문

• CREATE VIEW

CREATE VIEW v_usertbl AS SELECT * FROM usertbl;

• 이름 앞에 'v_'를 붙이는 것은 관례.

뷰의 장점

1. 보안 강화
2. 복잡한 쿼리 단순화

• 뷰 삭제

DROP VIEW v_usertbl;

CREATE OR REPLACE VIEW v_usertbl AS SELECT * FROM usertbl;

• 뷰 사용 예시

SELECT * FROM v_usertbl;

 

GROUP_CONCAT 및 GROUP BY

GROUP_CONCAT 사용 예시

SELECT userID, GROUP_CONCAT(prodName SEPARATOR ',')
FROM buytbl
GROUP BY userID;

• GROUP BY와 함께 사용.
• SEPARATOR를 이용해 구분자 설정.

테이블스페이스

테이블스페이스 개념

• 파일 공간 관리 방법.
• 주로 DBA가 관리하며 개발자의 영역은 아님.

정규화(Normalization)

 

정규화의 개념과 이상 현상

이상 현상(Anomalies)

• 삽입 이상: 불필요한 데이터를 삽입해야 하는 문제.
• 갱신 이상: 중복된 데이터를 일관되게 수정하지 못해 발생하는 문제.
• 삭제 이상: 필요한 데이터를 손실할 위험이 있는 문제.

갱신 이상 예시

 

정규화: 이상 현상을 제거하고 데이터베이스를 올바르게 설계하는 과정.

 

함수적 종속성(FD; Functional Dependency)

함수적 종속성 정의

• 속성 간의 관계를 나타냄.
• "X가 Y를 함수적으로 결정한다"는 의미.

정규형과 정규화 과정

기본 정규형

• 제1정규형: 모든 속성은 하나의 값만 가져야 함.
• 제2정규형: 모든 일반 속성은 모든 주식별자에 종속되어야 함.
• 제3정규형: 주식별자가 아닌 모든 속성 간에는 서로 종속될 수 없음.

 

정규화 요약

• 제1, 제2, 제3정규형을 지키는 것이 중요.
• 복잡한 데이터를 효율적으로 관리할 수 있도록 도움.

 

정규화 / 출처: 이것이 MySQL이다.

 

 

 

JDBC 프로그래밍

 

JDBC 프로그래밍

 

resources 생성

공백 넣으면 안됨, 단어 뒤에 들어가지 않도록 주의

 

Statement

: SQL 문 실행 클래스

: Connection 객체를 통해 생성 ...

ㄴ Statment stmmt = conn.createStatment( );

 

SQL 실행 메서드

- ResultSet executeQuery(sql문): select문 실행

- int executeUpdate(SQL문): insert, update, delete ... 여기서 컴파일 실행

 

ResultSet

- 컬럼 값 추출하기 위해서 getXxxx("컬럼명) ... Xxx는 타입명

ㄴ getString(), ...

 

# SQL 문을 만들어둔 후, Connection 안에 넣는다.

# 그리고 파라미터 설정해서 실행하면, 업데이트할 때마다 count를 가져온다

 

PreparedStatment

: SQL문에 값을 넣을 때 파라미터화 해서 처리

# 컴파일을 미리 해둔다

 

@Test ... 서로 독립적이서 순서도 알 수 없음 → 순서대로 진행해야한다면?

@TestMethodOrder(MethodOrderer.OrderAnnotation.class)

> @TestMethodOrder로 순서 지정

> OrderAnnotation로 넘버링

 

CrudTest.java

> 파라미터화는 갯수주의!

 

# try( ){ ... 괄호 안에 들어갈 수 있는 것은?

- AutoCloseable 인터페이스 구현체

- 배정문, 초기화코드

ex) PrepareStatemet stmt = conn>prepareSatatement(sql)

 

@Test
@DisplayName("특정 user 검색한다.")
@Order(3)
public void selectUserById() throws SQLException {
// 검색할 사용자의 ID를 문자열 scoula로 설정합니다.
    String userid = "scoula";
    String sql ="select * from users where id = ?";
    try(PreparedStatement stmt = conn.prepareStatement(sql)){
    // 첫 번째 파라미터(?)에 userid 값을 설정합니다.
        stmt.setString(1, userid);
        try(ResultSet rs = stmt.executeQuery()) {
        // 결과 집합에서 다음 레코드로 이동합니다. 레코드가 존재하면 true를 반환하고, 존재하지 않으면 false를 반환합니다.
            if(rs.next()) {
            // 결과 집합에서 name 열의 값을 출력합니다.
                System.out.println(rs.getString("name"));
            } else {
                throw new SQLException("scoula not found");
            }
        }
    }
}

 

# CrudTest.java = 화이트박스 테스트 

 

VO 패턴

 

VO 객체

ㄴ 특정 테이블의 한 행을 매핑하는 클래스

ㄴ 클래스 정의 → 테이블 / 필드들 → 컬럼들 / 인스턴스 → 한 행

 

DAO 패턴 적용

ㄴ Date Access Object

ㄴ 데이터베이스에 접근하여 실질적인 데이터베이스 연동 작업을 담당하는 클래스(CRUD)

ㄴ 인터페이스 정의 후 구현 클래스 작성

 

build.gradle

2개의 행이 있어야 LOMBOK 가능

 

실습

 

Dao 구현 코드

 

 

UserDao.java

package org.scoula.jdbc_ex.dao;

import org.scoula.jdbc_ex.domain.UserVO;

import java.sql.SQLException;
import java.util.List;
import java.util.Optional;

// DAO: Data Access Object
// 테이블에 대한 CRUD 연산을 처리하는 인터페이스
public interface UserDao {
//    새로운 사용자 등록 (CRUD 중 C)
    int create(UserVO user) throws SQLException;

//    모든 사용자 목록 조회 (CRUD 중 R)
    List<UserVO> getList() throws SQLException;

//    특정 사용자 정보 조회 (CRUD 중 R)
//    존재하지 않는 경우 빈 Optional 객체 반환
    Optional<UserVO> get(String id) throws SQLException;

//    사용자의 정보 수정 (CRUD 중 U)
    int update(UserVO user) throws SQLException;

//        특정 사용자 삭제(CRUD 중 D)
    int delete(String id) throws SQLException;

}

 

UserDaoImpl.java

package org.scoula.jdbc_ex.dao;

import org.scoula.jdbc_ex.common.JDBCUtil;
import org.scoula.jdbc_ex.domain.UserVO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;

// UserDao 인터페이스를 구현하여 사용자 정보에 대한 실제 CRUD 작업 수행
public class UserDaoImpl implements UserDao {
    // JDBC 연결 객체를 생성해서 초기화
    Connection conn = JDBCUtil.getConnection();

    // USERS 테이블 관련 SQL 명령어
    private String USER_LIST = "SELECT * FROM users";
    private String USER_GET = "SELECT * FROM users WHERE id =?";
    private String USER_INSERT = "INSERT INTO users(id, password, name, role) VALUES(?,?,?,?)";
    private String USER_UPDATE = "UPDATE users SET name=?, role=? WHERE id=?";
    private String USER_DELETE = "DELETE FROM users WHERE id = ?";

    // 회원 등록
    @Override
    public int create(UserVO user) throws SQLException {
        try (PreparedStatement stmt = conn.prepareStatement(USER_INSERT)) {
            stmt.setString(1, user.getId());
            stmt.setString(2, user.getPassword());
            stmt.setString(3, user.getName());
            stmt.setString(4, user.getRole());
            return stmt.executeUpdate();
        }
    }

    // ResultSet을 UserVO 객체로 매핑(변환)하는 메소드
    private UserVO map(ResultSet rs) throws SQLException {
        UserVO user = new UserVO();
        user.setId(rs.getString("ID"));
        user.setPassword(rs.getString("PASSWORD"));
        user.setName(rs.getString("NAME"));
        user.setRole(rs.getString("ROLE"));
        return user;
    }

    // 회원 전체 목록 조회
    @Override
    public List<UserVO> getList() throws SQLException {
        List<UserVO> userList = new ArrayList<>();
        try (PreparedStatement stmt = conn.prepareStatement(USER_LIST);
             ResultSet rs = stmt.executeQuery()) {
            while (rs.next()) {
                UserVO user = map(rs);
                userList.add(user);
            }
        }
        return userList;
    }

    // 특정 아이디를 가진 회원 조회
    @Override
    public Optional<UserVO> get(String id) throws SQLException {
        try (PreparedStatement stmt = conn.prepareStatement(USER_GET)) {
            stmt.setString(1, id);
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    return Optional.of(map(rs));
                }
            }
        }
        // 해당 아이디를 가진 회원이 없을 때 도달하는 코드
        return Optional.empty();
    }

    // 회원 수정
    @Override
    public int update(UserVO user) throws SQLException {
        try (PreparedStatement stmt = conn.prepareStatement(USER_UPDATE)) {
            stmt.setString(1, user.getName());
            stmt.setString(2, user.getRole());
            stmt.setString(3, user.getId());
            return stmt.executeUpdate();
        }
    }

    // 회원 삭제
    @Override
    public int delete(String id) throws SQLException {
        try (PreparedStatement stmt = conn.prepareStatement(USER_DELETE)) {
            stmt.setString(1, id);
            return stmt.executeUpdate();
        }
    }
}

 

UserDaoTest.java

package org.scoula.jdbc_ex.dao;

import org.junit.jupiter.api.*;
import org.scoula.jdbc_ex.common.JDBCUtil;
import org.scoula.jdbc_ex.domain.UserVO;

import java.sql.SQLException;
import java.util.List;
import java.util.NoSuchElementException;

import static org.junit.jupiter.api.Assertions.*;

// UserDao 구현체의 crud 작업을 테스트하는 클래스
@TestMethodOrder(MethodOrderer.OrderAnnotation.class)
class UserDaoTest {

    // 업캐스팅 => 다른 클래스로 갈아끼우기 쉽다
    UserDao dao = new UserDaoImpl();

    @AfterAll
    static void tearDown(){
        JDBCUtil.close();
    }

    @Test
    @DisplayName("user를 등록합니다.")
    @Order(1)
    void create() throws SQLException {
        UserVO user = new UserVO("ssamz3", "ssamz123", "쌤즈", "ADMIN");
        int count = dao.create(user);
        Assertions.assertEquals(1, count);
    }

    @Test
    @DisplayName("UserDao User 목록을 추출합니다.")
    @Order(2)
    void getList() throws SQLException {
    // 모든 사용자 목록을 조회
        List<UserVO> list = dao.getList();
        // 조회된 사용자 목록을 출력
        for(UserVO vo: list){
            System.out.println(vo);
        }
    }

    @Test
    @DisplayName("특정 user 1건을 추출합니다.")
    @Order(3)
    void get() throws SQLException {
    // 특정 사용자 정보 조회
        // 해당 값이 없으면 NoSuchElementException 예외 던지기
        UserVO user = dao.get("ssamz3").orElseThrow(NoSuchElementException::new);
//        조회된 사용자 값이 null이 아닌지 확인하는 테스트
        Assertions.assertNotNull(user);

    }

    @Test
    @DisplayName("user의 정보를 수정합니다.")
    @Order(4)
    void update() throws SQLException {
        // 특정 사용자 정보를 조회하여 수정
//         :: new -> 생성자 참조
        UserVO user = dao.get("ssamz3").orElseThrow(NoSuchElementException::new);
        user.setName("썜즈3");
        int count = dao.update(user);
    }

    @Test
    @DisplayName("user를 삭제합니다.")
    @Order(5)
    void delete() throws SQLException {
        // 특정 사용자 삭제
        int count = dao.delete("ssamz3");
        Assertions.assertEquals(1, count);
    }
}

 

실습 과정에서 실수했던 코드

//            SQL 쿼리 실행 및 수정된 행의 수 반환
//            실행이 잘되었는지 확인하는 함수
            int count = pstmt.executeUpdate();
            Assertions.assertEquals(1, count);

 

 

---