DataSource - 커넥션 풀 사용

회원 리포지토리

• DataSource 의존관계 주입 외부에서 DataSource 를 주입 받아서 사용한다.
• DataSource 는 표준 인터페이스 이기 때문에 DriverManagerDataSource 에서 HikariDataSource 로 변경되어도 해당 코드를 변경하지 않아도 된다.(DI)
• JdbcUtils 편의 메서드 스프링은 JDBC를 편리하게 다룰 수 있는 JdbcUtils 라는 편의 메서드를 제공한다. JdbcUtils 을 사용하면 커넥션을 좀 더 편리하게 닫을 수 있다.

@Slf4j
public class MemberRepositoryV1 {

    private final DataSource dataSource;

    public MemberRepositoryV1(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    public Member save(Member member) throws SQLException {
        String sql = "insert into member(member_id, money) values(?, ?)";

        Connection con = null;
        PreparedStatement pstmt = null;

        try {
            con = getConnection();
            pstmt = con.prepareStatement(sql);
            pstmt.setString(1, member.getMemberId());
            pstmt.setInt(2, member.getMoney());
            pstmt.executeUpdate();
            return member;
        } catch(SQLException e) {
            log.error("db error", e);
            throw e;
        } finally {
            close(con, pstmt, null);
        }

    }

    public Member findById(String memberId) throws SQLException {
        String sql = "select * from member where member_id = ?";

        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            con = getConnection();
            pstmt = con.prepareStatement(sql);
            pstmt.setString(1, memberId);

            rs = pstmt.executeQuery();
            if(rs.next()) {
                Member member = new Member();
                member.setMemberId(rs.getString("member_id"));
                member.setMoney(rs.getInt("money"));
                return member;
            } else {
                throw new NoSuchElementException("member not found memberId=" + memberId);
            }

        } catch(SQLException e) {
            log.error("db error", e);
            throw e;
        } finally {
            close(con, pstmt, null);
        }
    }

    public void update(String memberId, int money) throws SQLException {
        String sql = "update member set money=? where member_id =?";

        Connection con = null;
        PreparedStatement pstmt = null;

        try {
            con = getConnection();
            pstmt = con.prepareStatement(sql);
            pstmt.setInt(1, money);
            pstmt.setString(2, memberId);
            pstmt.executeUpdate();
        } catch(SQLException e) {
            log.error("db error", e);
            throw e;
        } finally {
            close(con, pstmt, null);
        }

    }

    public void delete(String memberId) throws SQLException {
        String sql = "delete from member where member_id =?";

        Connection con = null;
        PreparedStatement pstmt = null;

        try {
            con = getConnection();
            pstmt = con.prepareStatement(sql);
            pstmt.setString(1, memberId);
            pstmt.executeUpdate();
        } catch(SQLException e) {
            log.error("db error", e);
            throw e;
        } finally {
            close(con, pstmt, null);
        }

    }

    // 리소스 정리
    private void close(Connection con, Statement stmt, ResultSet rs) {
        JdbcUtils.closeResultSet(rs);
        JdbcUtils.closeStatement(stmt);
        JdbcUtils.closeConnection(con);
    }

    // DB 커넥션 얻기
    private Connection getConnection() throws SQLException {
        Connection con = dataSource.getConnection();
        log.info("set connection={}, class={}", con, con.getClass());
        return con;
    }
}

TEST

@BeforeEach
void beforeEach() {
    // 커넥션을 계속 새로 얻어옴
    // 항상 새로운 커넥션 사용
	//DriverManagerDataSource dataSource =
	//       new DriverManagerDataSource(ConnectionConst.URL, ConnectionConst.USERNAME, ConnectionConst.PASSWORD);

    // 카넥션 풀
    // 커넥션 재사용 
    // 커넥션 사용 후 다시 돌려주는 것을 사용한다
	HikariDataSource dataSource = new HikariDataSource();
	dataSource.setJdbcUrl(ConnectionConst.URL);
	dataSource.setUsername(ConnectionConst.USERNAME);
	dataSource.setPassword(ConnectionConst.PASSWORD);
	repository = new MemberRepositoryV1(dataSource);
}

@Test
void crud() throws SQLException {
	// create
	Member member = new Member("memberV1", 10000);
	repository.save(member);

	// read
	Member findMember = repository.findById(member.getMemberId());
	Assertions.assertThat(member.getMemberId()).isEqualTo(findMember.getMemberId());

	// update
	repository.update(findMember.getMemberId(), 20000);
	Member updateMember = repository.findById(findMember.getMemberId());
	Assertions.assertThat(updateMember.getMoney()).isEqualTo(20000);

	// delete
	repository.delete(findMember.getMemberId());
	Assertions.assertThatThrownBy(() -> repository.findById(findMember.getMemberId()))
			.isInstanceOf(NoSuchElementException.class);
}