JDBC 2편

 

저번 JDBC 1편에 이어서 설명을 진행하겠다.

 

설정 정보들을

 

properties 정보들로 연결 & CRUD Test

 

resources 하위에 application.properties 파일을 만들어 담는다.

 

application.properties

driver=com.mysql.cj.jdbc.Driver
 url=jdbc:mysql://127.0.0.1:3306/jdbc_ex
 id=scoula
 password=1234

 

 

 

그리고 위 경로에 JDBCUtil.java를 만들어서

 

JDBCUtil.java

package org.scoula.jdbc_ex.common;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

public class JDBCUtil {
    static Connection conn = null;

    static {
        try {
            Properties properties = new Properties();
            properties.load(JDBCUtil.class.getResourceAsStream("/application.properties"));
            String driver = properties.getProperty("driver");
            String url = properties.getProperty("url");
            String id = properties.getProperty("id");
            String password = properties.getProperty("password");

            Class.forName(driver);
            conn = DriverManager.getConnection(url, id, password);
        } catch (Exception e){
            e.printStackTrace();
        }
    }

    public static Connection getConnection() {
        return conn;
    }

    public static void close() {
        try {
            if (conn != null){
                conn.close();;
                conn=null;
            }
        } catch(SQLException e){
            e.printStackTrace();
        }
    }
}

 

ConnectionTest.java

  @Test
    @DisplayName("jdbc_ex에 접속한다.(자동 닫기)")
    public void testConnection2() throws SQLException {
        try(Connection conn = JDBCUtil.getConnection()){
            System.out.println("DB 연결 성공");
        }
    }

 

CrudTest.java

package org.scoula.jdbc_ex.test;

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

import java.sql.*;

@TestMethodOrder(MethodOrderer.OrderAnnotation.class)
public class CrudTest {
    Connection conn = JDBCUtil.getConnection();

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

    @Test
    @DisplayName("새로운 user를 등록한다.")
    @Order(1)
    public void insertUser() throws SQLException {
        String sql = "insert into users(id, password, name, role) values(?, ?, ?, ?)";
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1,"scoula");
            pstmt.setString(2,"scoula3");
            pstmt.setString(3,"스콜라");
            pstmt.setString(4,"USER");

            int count = pstmt.executeUpdate();
            Assertions.assertEquals(1,count);
        }
    }

    @Test
    @DisplayName("user 목록을 추출한다.")
    @Order(2)
    public void selectUser() throws SQLException {
        String sql = "select * from users";
        try(Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);) {
            while(rs.next()){
                System.out.println(rs.getString("name"));
            }
        }
    }

    @Test
    @DisplayName("특정 user 검색한다.")
    @Order(3)
    public void selectUserById() throws SQLException {
        String userid = "scoula";
        String sql = "select * from users where id=?";
        try(PreparedStatement stmt = conn.prepareStatement(sql)){
            stmt.setString(1,userid);
            try(ResultSet rs = stmt.executeQuery()) {
                if(rs.next()) {
                    System.out.println(rs.getString("name"));
                } else {
                    throw new SQLException("scoula not found");
                }
            }
        }
    }

    @Test
    @DisplayName("특정 user 수정한다.")
    @Order(4)
    public void updateUser() throws SQLException {
        String userid = "scoula";
        String sql = "update users set name= ? where id = ?";
        try(PreparedStatement stmt = conn.prepareStatement(sql)){
            stmt.setString(1,"스콜라 수정");
            stmt.setString(2,userid);
            int count = stmt.executeUpdate();
            Assertions.assertEquals(1,count);
        }
    }

    @Test
    @DisplayName("지정한 사용자를 삭제한다")
    @Order(5)
    public void deleteUser() throws SQLException {
        String userid = "scoula";
        String sql = "delete from users where id = ?";
        try(PreparedStatement stmt = conn.prepareStatement(sql)){
            stmt.setString(1,userid);
            int count = stmt.executeUpdate();
            Assertions.assertEquals(1,count);
        }
    }

}

 

 

VO 패턴

Value Object

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

 

클래스 정의 -> 테이블

필드들 -> 컬럼들

인스턴스 -> 한 행

 

package org.scoula.jdbc_ex.domain;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@NoArgsConstructor
@AllArgsConstructor
public class UserVO {
    private String id;
    private String password;
    private String name;
    private String role;
}

 

DAO 패턴 적용

Data Access Object

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

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

 

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;

public interface UserDao {
    // 회원 등록
    int create(UserVO user) throws SQLException;

    //회원 목록 조회
    List<UserVO> getList() throws SQLException;

    //회원 정보 조회
    Optional<UserVO> get(String id) throws SQLException;

    //회원 수정
    int update(UserVO user)throws SQLException;

    // 회원 삭제
    int delete(String id) throws SQLException;
}

 

UserDaoImpl

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;

public class UserDaoImpl implements UserDao{

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

    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<UserVO>();
       Connection conn = JDBCUtil.getConnection();
       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 {
        Connection conn = JDBCUtil.getConnection();
        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

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.*;

@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 {
        UserVO user = dao.get("ssamz3").orElseThrow(NoSuchElementException::new);
        Assertions.assertNotNull(user);
    }

    @Test
    @DisplayName("user 정보 수정")
    @Order(4)
    void update() throws SQLException {
        UserVO user = dao.get("ssamz3").orElseThrow();
        user.setName("쌤즈3");
        int count = dao.update(user);
        Assertions.assertEquals(1,count);
    }

    @Test
    @DisplayName("user 정보 삭제")
    @Order(5)
    void delete() throws SQLException {
        int count = dao.delete("ssamz3");
        Assertions.assertEquals(1,count);
    }
}

 

PreparedStatement pstmt = conn.prepareStatement(sql);

Statemen stmt = conn.createStatement();

비교

1. pstmt는 stmt에 비해 SQL Injection 방어 잘 됨.

 

2. pstmt는 DBMS 쪽에서 SQL 구조를 미리 컴파일 해놓고 데이터만 바인딩 하는 형식이라 시간 효율적 

 

UserDAO에서

int를 반환하는 것이 아니고, void로 반환하게 인터페이스를 구현했다면 어떤 Test 메서드로 확인을 했어야 했었을까?