JDBC를 이용한 반복문제 해결 - JdbcTemplate

리포지토리에서 JDBC를 사용하면 서비스계층의 순수함을 유지시키기 위해 많은 코드들이 반복되었다.

• 커넥션 조회, 커넥션 동기화
• PreparedStatement 생성및 파라미터 바인딩
• 쿼리 실행
• 결과 바인딩
• 예외 발생시 스프링 예외 변환기 실행
• 리소스 종료

하지만 스프링은 JDBC의 이러한 반복 문제를 해결하기 위해 JdbcTemplate이라는 템플릿을 제공한다.

 

<JdbcTemplate 적용 전 리포지토리 코드>

package hello.jdbc.repository;

import hello.jdbc.domain.Member;
import hello.jdbc.repository.ex.MyDbException;
import lombok.extern.slf4j.Slf4j;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.jdbc.support.JdbcUtils;
import org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator;
import org.springframework.jdbc.support.SQLExceptionTranslator;

import javax.sql.DataSource;
import java.sql.*;
import java.util.NoSuchElementException;

/**
 * SQLExceptionTranslator 추가
 */
@Slf4j
public class MemberRepositoryV4_2 implements MemberRepository {

    private final DataSource dataSource;
    private final SQLExceptionTranslator exTranslator;

    public MemberRepositoryV4_2(DataSource dataSource) {
        this.dataSource = dataSource;
        this.exTranslator = new SQLErrorCodeSQLExceptionTranslator(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 exTranslator.translate("save",sql,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 fond memberId="+memberId);
            }
        }catch (SQLException e){
            throw exTranslator.translate("findById",sql,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 exTranslator.translate("update",sql,e);
        }finally {
            close(con, pstmt, null);
        }
    }
    @Override
    public void delete(String memberId){
        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) {
            throw exTranslator.translate("delete",sql,e);
        }finally {
            close(con, pstmt, null);
        }
    }


    private void close(Connection con, Statement stmt, ResultSet rs){
        JdbcUtils.closeResultSet(rs);
        JdbcUtils.closeStatement(stmt);
        //트랜잭션 동기화를 사용하려면 DataSourceUtils를 사용해야한다.
        DataSourceUtils.releaseConnection(con,dataSource);
    }
    private Connection getConnection() throws SQLException {
        Connection con = DataSourceUtils.getConnection(dataSource);
        log.info("get connection={}, class={}",con,con.getClass());
        return con;
    }

}

 

 

<JdbcTemplate 적용 후 코드>

package hello.jdbc.repository;

import hello.jdbc.domain.Member;
import lombok.extern.slf4j.Slf4j;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.jdbc.support.JdbcUtils;
import org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator;
import org.springframework.jdbc.support.SQLExceptionTranslator;

import javax.sql.DataSource;
import java.sql.*;
import java.util.NoSuchElementException;

/**
 * JdbcTemplate 사용
 */
@Slf4j
public class MemberRepositoryV5 implements MemberRepository {

    private final JdbcTemplate template;

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

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

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

        Member member = template.queryForObject(sql,memberRowMapper(),memberId);
        return member;
    }

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


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


}

 

JdbcTemplate를 적용한 코드를 보면 트랜잭션을 위한 커넥션 동기화, 스프링 예외변환기 등의 반복되는 코드들을 자동으로 실행해주어 코드가 매우 간략해진다.