18. 스프링 JDBC 기능

Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
    conn = dataSource.getConnection();
    pstmt = conn.prepareStatement(
        "SELECT * FROM member WHERE id = ?");
    pstmt.setString(1, id);
    rs = pstmt.executeQuery();
    // 결과 처리
} catch (SQLException e) {
    e.printStackTrace();
} finally {
    // 리소스 해제 (역순)
    if (rs != null) rs.close();
    if (pstmt != null) pstmt.close();
    if (conn != null) conn.close();
}

jdbc.driverClass=oracle.jdbc.driver.OracleDriver
jdbc.url=jdbc:oracle:thin:@localhost:1521:XE
jdbc.username=scott
jdbc.password=tiger

<beans ...>
    <!-- properties 파일 로드 -->
    <context:property-placeholder
        location="classpath:jdbc.properties" />

    <!-- DataSource 설정 -->
    <bean id="dataSource"
      class="org.apache.commons.dbcp
             .BasicDataSource"
      destroy-method="close">
        <property name="driverClassName"
            value="${jdbc.driverClass}" />
        <property name="url"
            value="${jdbc.url}" />
        <property name="username"
            value="${jdbc.username}" />
        <property name="password"
            value="${jdbc.password}" />
    </bean>
</beans>

<web-app ...>
    <listener>
        <listener-class>
            org.springframework.web
            .context.ContextLoaderListener
        </listener-class>
    </listener>

    <context-param>
        <param-name>
            contextConfigLocation
        </param-name>
        <param-value>
            /WEB-INF/config/action-*.xml
        </param-value>
    </context-param>
</web-app>

@Repository("memberDAO")
public class MemberDAOImpl implements MemberDAO {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    // 전체 회원 조회
    @Override
    public List<MemberVO> listMembers() {
        String sql = "SELECT * FROM t_member"
            + " ORDER BY joinDate DESC";

        List<MemberVO> list =
            jdbcTemplate.query(sql,
                new RowMapper<MemberVO>() {
            @Override
            public MemberVO mapRow(
                    ResultSet rs, int rowNum)
                    throws SQLException {
                MemberVO vo = new MemberVO();
                vo.setId(rs.getString("id"));
                vo.setPwd(rs.getString("pwd"));
                vo.setName(rs.getString("name"));
                vo.setEmail(rs.getString("email"));
                vo.setJoinDate(
                    rs.getDate("joinDate"));
                return vo;
            }
        });
        return list;
    }

    // 회원 추가
    @Override
    public int addMember(MemberVO vo) {
        String sql = "INSERT INTO t_member"
            + " (id, pwd, name, email)"
            + " VALUES (?, ?, ?, ?)";

        return jdbcTemplate.update(sql,
            vo.getId(),
            vo.getPwd(),
            vo.getName(),
            vo.getEmail());
    }

    // 회원 삭제
    @Override
    public int delMember(String id) {
        String sql = "DELETE FROM t_member"
            + " WHERE id = ?";

        return jdbcTemplate.update(sql, id);
    }
}

public List<MemberVO> listMembers() {
    String sql =
        "SELECT * FROM t_member";

    return jdbcTemplate.query(sql,
        (rs, rowNum) -> {
            MemberVO vo = new MemberVO();
            vo.setId(rs.getString("id"));
            vo.setPwd(rs.getString("pwd"));
            vo.setName(rs.getString("name"));
            vo.setEmail(rs.getString("email"));
            return vo;
        });
}

<!-- JdbcTemplate 빈 등록 -->
<bean id="jdbcTemplate"
  class="org.springframework.jdbc
         .core.JdbcTemplate">
    <property name="dataSource"
        ref="dataSource" />
</bean>

<!-- DAO에 JdbcTemplate 주입 -->
<bean id="memberDAO"
    class="com.spring.dao.MemberDAOImpl">
    <property name="jdbcTemplate"
        ref="jdbcTemplate" />
</bean>

@Repository("memberDAO")
public class MemberDAOImpl
        implements MemberDAO {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    // ...
}