Jdbc 반복 문제 해결 -JdbcTemplate

MemberRepository 인터페이스

package hello.jdbc.repository;

import hello.jdbc.domain.Member;

public interface MemberRepository {

    Member save(Member member);
    Member findById(String memberId);
    void update(String memberId, int money);
    void delete(String memberId);
}

MemberRepositoryV4.class

@Slf4j
public class MemberRepositoryV4 implements MemberRepository{

    private final DataSource dataSource;

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

    @Override
    public Member save(Member member) {
        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) {
            throw new MyDbException(e);
        } finally {
            close(con, pstmt, null);
        }
    }

    @Override
    public Member findById(String memberId) {
        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) {
            throw new MyDbException(e);
        } finally {
            close(con, pstmt, rs);
        }
    }

    @Override
    public void update(String memberId, int money) {
        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);
            int resultSize = pstmt.executeUpdate();
            log.info("resultSize={}", resultSize);
        } catch (SQLException e) {
            throw new MyDbException(e);
        } finally {
            close(con, pstmt, null);
        }
    }

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

        
    }

    private void close(Connection con, Statement stmt, ResultSet rs) {
        JdbcUtils.closeResultSet(rs);
        JdbcUtils.closeStatement(stmt);
        DataSourceUtils.releaseConnection(con, dataSource);
    }

    private Connection getConnection() throws SQLException {
        Connection con = DataSourceUtils.getConnection(dataSource);
        log.info("get connection={}, class={}", con, con.getClass());
        return con;
    }
}

Connection con = null;
PreparedStatement pstmt = null;

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

연결 설정 및 해제, 예외 처리를 위한 위의 코드들이 중복됨을 보인다.

이는 JdbcTemplate로 손쉽게 해결 가능

MemberRepositoryV5.class

생성자

private final JdbcTemplate template;

public MemberRepositoryV5(DataSource dataSource) {
    this.template = new JdbcTemplate(dataSource);
}

생성자로 DataSource를 받고, JdbcTemplate에 주입

save()

@Override
public Member save(Member member) {
		String sql = "insert into member(member_id, money) values (?, ?)";
    template.update(sql, member.getMemberId(), member.getMoney());
    return member;
}

• 반복되는 지저분한 코드를 제거하고 template.update() 메소드를 사용.
• 파라미터로 sql문법과 member의 정보를 넣어준다.

findById()

@Override
public Member findById(String memberId) {
    String sql = "select * from member where member_id = ?";
    return template.queryForObject(sql, memberRowMapper(), memberId);
}

private RowMapper<Member> memberRowMapper() {
        return (rs, rowNum) -> {
            Member member = new Member();
            member.setMemberId(rs.getString("member_id"));
            member.setMoney(rs.getInt("money"));
            return member;
        };
    }

• V4의 findById()의 rs = pstmt.executeQuery(); 대신 template.queryForObject()를 사용
• template.queryForObject(sql, memberRowMapper(), memberId);
  • sql의 결과가 ResultSet으로 memberRowMapper()에 전달되어 람다식을 거친 후 결과로 member가 반환됨.

update()

@Override
public void update(String memberId, int money) {
    String sql = "update member set money=? where member_id=?";
    template.update(sql, money, memberId);
}

• save()와 동일하게 template.update()를 사용하지만 money와 memberId의 순서의 유의

delete()

@Override
public void delete(String memberId) {
    String sql = "delete from member where member_id = ?";
    template.update(sql, memberId);
}

• 동일하게 template.update() 사용하지만 파라미터가 다름

 

JdbcTemplate은 Jdbc로 개발할 때 발생하는 '트랜잭션을 위한 커넥션 동기화'는 물론, 예외 발생 시 '스프링 예외 변환기'도 자동으로 실행해준다.