Spring - 데이터베이스

학습하며 정리한 내용을 올리는 것이니, 참고용으로만 봐 주시길 바랍니다.
자바 스프링 프레임워크(renew ver.) - 신입 프로그래머를 위한 강좌

데이터베이스

: (시스템계정) system / root
: sqlplus
: create user root idientified by root;
: grant connect, resource to root;
: exit
: show user
: drop user root cascade; (system 계정에서 실행)

CREATE TABLE member (
    memId VARCHAR2(10) CONSTRAINT memId_pk PRIMARY KEY,
    memPw VARCHAR2(10),
    memMail VARCHAR2(15),
    memPurcNum NUMBER(3) DEFAULT 0 CONSTRAINT memPurNum_ck CHECK (memPurcNum < 3)
);

INSERT INTO member (memId, memPw, memMail) values ('b', 'bb', 'bbb@gamil.com');

SELECT * FROM member;

DELETE FROM member WHERE memId = 'b';

DROP TABLE member;

commit;

JDBC

: 드라이버 로딩 -> DB 연결 -> SQL 작성 및 전송 -> 자원 해제

// MemberDao.java

private String driver = "oracle.jdbc.driver.OracleDriver";
private String url = "jdbc:oracle:thin:@localhost:1521:xe";
private String userid = "root";
private String userpw = "root";

private Connection conn = null;
private PreparedStatement pstmt = null;
private ResultSet rs = null;

public int memberInsert(Member member) {
    int result = 0;

    try {
        // Driver 로딩
        Class.forName(driver); 

        // DB 연결
        conn = DriverManager.getConnection(url, userid, userpw);

        // Query 전송
        String sql = "INSERT INTO member (memId, memPw, memMail) values (?, ?, ?)";
        pstmt = conn.preparedStatement(sql);
        pstmt.setString(1, member.getMemId());
        pstmt.setString(2, member.getMemPw());
        pstmt.setString(3, member.getMemMail());
        result = pstmt.executeUpdate();

    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    } catch (SQLException e) {
        e.printStackTrace();
    } finally { // 자원 해제
        try {
            if (pstmt != null) pstmt.close();
            if (conn != null) conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }   
    }
    return result;
}

public Member memberSelect(Member member) {
    Member mem = null;
    try {
        // Driver 로딩
        Class.forName(driver);

        // DB 연결
        conn = DriverManager.getConnection(url, userid, userpw);

        // Query 전송
        String sql = "SELECT * FROM member WHERE memId = ? AND memPw = ?";
        pstmt = conn.preparedStatement(sql);
        pstmt.setString(1, member.getMemId());
        pstmt.setString(2, member.getMemPw());
        rs = pstmt.executeUpdate();

        while (rs.netx()) {
            String memId = rs.getString("memid);
            String memPw = rs.getString("mempw);
            ...

            mem = new Member();
            mem.setMemId(memId);
            ...

        }
    } catch (Exception e) {
        ...
    } finally { // 자원해제
        try {
            if (rs != null) rs.close();
            if (pstmt != null) pstmt.close();
            if (conn != null) conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    return mem;
}

: 매번 Driver 로딩, DB 연결, 자원 해제를 해 주어야 한다는 단점 존재한다. 이를 간편하게 하기위해 JdbcTemplate을 사용한다.

JDBC Template 사용

: JDBC 사용시 Driver 로딩, DB 연결, 자원 해제를 JDBC Template에서 자동으로 해 주고, 각각의 함수에서는 SQL Query문만 작성해서 실행해주면 된다.

// pom.xml

<!-- DB -->
<dependency>
    <groupId>com.oracle</groupId>
    <artifactId>ojdbc6</artifactId>
    <version>12.1.0.2</version>
</dependency>
<dependency>                            // 스프링 JDBC 추가
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>4.1.6.RELEASE</version>
</dependency>
<dependency>                            // 추가
    <groupId>com.mchange</groupId>
    <artifactId>c3p0</artifactId>
    <version>0.9.5</version>
</dependency>

// MemberDao.java

public class MemberDao implements IMemberDao {

    private DriverManagerDataSource dataSource;
    private JdbcTemplate template;

    /*
    private Connection conn = null;
    private PreparedStatement pstmt = null;
    private ResultSet rs = null;
    */

    public MemberDao() {
        /* c3p0 의 DriverManagerDataSource 사용 */
        dataSource = new DriverManagerDataSource();
        dataSource.setDriverClass(dirver); //  드라이버 로딩
        dataSource.setJdbcUrl(url); // DB 연결
        dataSource.setUser(userid);
        dataSource.setPassowrd(iserpw);

        /* spring의 DriverManagerDataSource 사용
        dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName(dirver); 
        dataSource.setUrl(url); 
        dataSource.setUsername(userid);
        dataSource.setPassowrd(iserpw);
        */

        template = new JdbcTemplate(); // JDBC Template 사용
        template.setDataSource(dataSource);
    }
}

public int memberInsert(Member member) {
    int result = 0;

    String sql = "INSET INTO member (memId, memPw, memMail) values (?, ?, ?)";

    // 1st 방법
    result = template.update(sql, member.getMemId(), ...);

    /*
    // 2nd 방법 (이 때에는, sql문이 외부에서 사용될 수 있으므로 final로 선언해야한다 > final String sql)
    result = template.update(new PreparedStatementCreator() {
        @Override
        public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, member.getMemId());
            ...

            return pstmt;
        }
    });

    // 3rd 방법 (이 때에는, sql문이 외부에서 사용될 수 있으므로 final로 선언해야한다 > final String sql)
    result = template.update(sql, new PreparedStatementSetter()) {
        @Override
        public void setValues(PreparedStatement pstmt) throws SQLException {
            pstmt.setString(1, member.getMemId());
            ...
        }
    });
    */

    return result;
}

public Member memberSelect(Member memeber) {
    List<Member> members = null;

    String sql = "SELECT * FROM member WHERE memId = ? AND memPw = ?";

    // 1st
    members = template.query(sql, new PreparedStatementSetter() {
        @Override
        public void setValues(PreparedStatement pstmt) throws SQLException {
            pstmt.setString(1, member.getMemId());
            pstmt.setString(2, member.getMemPw());
        }
    }, new RowMapper<Member>() {
        @Override
        public Member mapRow(ResultSet rs, int rowNum) throws SQLException {
            Member mem = new Member();

            mem.setMemId(rs.getString("memId"));
            ...

            return mem;
        }
    });

    if (member.isEmpty()) return null;

    /*
    // 2nd
    members = template.query(new PreparedStatementCreator() {

        @Override
        public PreparedStatement createPreparedStatement(Connection conn)
                throws SQLException {
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, member.getMemId());
            pstmt.setString(2, member.getMemPw());
            return pstmt;
        }
    }, new RowMapper<Member>() {

        @Override
        public Member mapRow(ResultSet rs, int rowNum) throws SQLException {
            Member mem = new Member();
            mem.setMemId(rs.getString("memId"));
            mem.setMemPw(rs.getString("memPw"));
            mem.setMemMail(rs.getString("memMail"));
            mem.setMemPurcNum(rs.getInt("memPurcNum"));
            return mem;
        }
    });

    // 3rd
    members = template.query(sql, new RowMapper<Member>() {

        @Override
        public Member mapRow(ResultSet rs, int rowNum) throws SQLException {
            Member mem = new Member();
            mem.setMemId(rs.getString("memId"));
            mem.setMemPw(rs.getString("memPw"));
            mem.setMemMail(rs.getString("memMail"));
            mem.setMemPurcNum(rs.getInt("memPurcNum"));
            return mem;
        }

    }, member.getMemId(), member.getMemPw());

    // 4th
    members = template.query(sql, new Object[]{member.getMemId(), member.getMemPw()}, new RowMapper<Member>() {

        @Override
        public Member mapRow(ResultSet rs, int rowNum) throws SQLException {
            Member mem = new Member();
            mem.setMemId(rs.getString("memId"));
            mem.setMemPw(rs.getString("memPw"));
            mem.setMemMail(rs.getString("memMail"));
            mem.setMemPurcNum(rs.getInt("memPurcNum"));
            return mem;
        }

    });
    */

    return members.get(0);
}

커넥션풀

: 다수의 사용자가 DB 접속 시 드라이버를 로딩하고, 커넥션을 만드는 작업은 메모리를 상당히 많이 차지하고 이는 서버에 상당한 부하를 줄 수 있다. 따라서 사용자 요청이 들어오지 않아도 미리 커넥션을 만들어 놓고 요청 있을 때 미리 만들어 놓은 것을 이용하는 방법.
: c3p0모듈의 ComboPooledDataSource를 사용
: 사용방법은 이전(public MemberDao() { ... })과 동일. 하지만,

dataSource = new ComboPooledDataSource();
try {
    dataSource.setDriverClass(driver);
    ...
} catch (PropertyVetoException e) {
    e.printStackTrace();
}

와 같이 try-catch 문을 사용해 주어야 한다.