DBCP를 배워봅시다.

DBCP의 의미

데이터 베이스에 연결하여 사용하는 경우 데이터 베이스에 접속하기 위해 Connection 등의 객체를 생성해야 한다.

이게 혼자서 쓸 때는 접속 할 때마다 객체를 생성해도 괜찮지만 사람들이 많이 접속하는 사이트에서는 사용자 한 명당 하나씩 계속 객체를 생성하게 되면 서버가 객체를 생성하는데 리소스를 많이 쓰게 된다.

이러한 현상을 해결하고자 ‘커넥션 풀’이라는 공간을 만들어 커넥션 객체들을 담아놓고 차후 사용자가 데이터 베이스에 접속을 시도하면 커넥션 풀에 담겨있는 커넥션 객체를 하나하나 꺼내주는 방법을 적용시켰다.

이것이 바로 DBCP(DataBase Connection Pool)이다.

---

사용환경을 위한 준비

jar 준비

---

페이지 만들기

DB

• green.sql

DROP TABLE GREEN;
CREATE TABLE GREEN
(
    NO NUMBER PRIMARY KEY,
    WRITER VARCHAR2(100),
    TITLE VARCHAR2(1000) NOT NULL,
    CONTENT VARCHAR2(4000),
    HIT NUMBER,
    POSTDATE DATE
);

DROP SEQUENCE GREEN_SEQ;
CREATE SEQUENCE GREEN_SEQ
START WITH 1000
INCREMENT BY 1
MAXVALUE 999999
NOCYCLE
NOCACHE;

Dto

GreenDto

package dto;

import java.sql.Date;

public class GreenDto {

    private int no;
    private String writer;
    private String title;
    private String content;
    private int hit;
    private Date postDate;

    public final int getNo() {
        return no;
    }
    public final void setNo(int no) {
        this.no = no;
    }
    public final String getWriter() {
        return writer;
    }
    public final void setWriter(String writer) {
        this.writer = writer;
    }
    public final String getTitle() {
        return title;
    }
    public final void setTitle(String title) {
        this.title = title;
    }
    public final String getContent() {
        return content;
    }
    public final void setContent(String content) {
        this.content = content;
    }
    public final int getHit() {
        return hit;
    }
    public final void setHit(int hit) {
        this.hit = hit;
    }
    public final Date getPostDate() {
        return postDate;
    }
    public final void setPostDate(Date postDate) {
        this.postDate = postDate;
    }

}

DAO

기능 추가될때마다 들어와서 수정해 주어야 한다.

package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import dto.GreenDto;

public class GreenDao {

    private Connection con;
    private PreparedStatement ps;
    private ResultSet rs;
    private String sql;

    private static DataSource dataSource;
    static {
        try {
            Context context = new InitialContext();
            dataSource = (DataSource)context.lookup("java:comp/env/jdbc/oracle");
        } catch (NamingException e) {
            e.printStackTrace();
        }
    }

    private GreenDao() {}
    private static GreenDao greenDao = new GreenDao();
    public static GreenDao getInstance() {
        return greenDao;
    }

    /***** 1. 접속 해제 *****/
    public void close(Connection con, PreparedStatement ps, ResultSet rs) {
        try {
            if (rs != null) { rs.close(); }
            if (ps != null) { ps.close(); }
            if (con != null) { con.close(); }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /***** 2. 전체 목록 가져오기 *****/
    public ArrayList<GreenDto> list() {
        ArrayList<GreenDto> list = new ArrayList<>();
        try {
            con = dataSource.getConnection();
            sql = "SELECT * FROM GREEN ORDER BY HIT DESC";
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                GreenDto greenDto = new GreenDto();
                greenDto.setNo( rs.getInt("NO") );
                greenDto.setWriter( rs.getString("WRITER") );
                greenDto.setTitle( rs.getString("TITLE") );
                greenDto.setContent( rs.getString("CONTENT") );
                greenDto.setHit( rs.getInt("HIT") );
                greenDto.setPostDate( rs.getDate("POSTDATE") );
                list.add(greenDto);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(con, ps, rs);
        }
        return list;
    }

    /***** 3. 삽입하기 *****/
    public int insert(GreenDto greenDto) {
        int result = 0;
        try {
            con = dataSource.getConnection();
            sql = "INSERT INTO GREEN VALUES (GREEN_SEQ.NEXTVAL, ?, ?, ?, 0, SYSDATE)";
            ps = con.prepareStatement(sql);
            ps.setString(1, greenDto.getWriter());
            ps.setString(2, greenDto.getTitle());
            ps.setString(3, greenDto.getContent());
            result = ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(con, ps, null);
        }
        return result;
    }


    /***** 4. 게시글 가져오기 *****/
    public GreenDto view(int no) {

        GreenDto greenDto = null;

        try {
            con = dataSource.getConnection();
            sql = "SELECT * FROM GREEN WHERE NO = ?";
            ps = con.prepareStatement(sql);
            ps.setInt(1, no);

            rs = ps.executeQuery();

            if (rs.next()) {
                greenDto = new GreenDto();
                greenDto.setNo( rs.getInt(1) );
                greenDto.setWriter( rs.getString(2) );
                greenDto.setTitle( rs.getString(3) );
                greenDto.setContent( rs.getString(4) );
                greenDto.setHit( rs.getInt(5) );
                greenDto.setPostDate( rs.getDate(6) );
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(con, ps, rs);
        }

        return greenDto;
    }

    /***** 5. 조회수 증가하기 *****/
    public int updateHit(int no) {
        int result = 0;

        try {
            con = dataSource.getConnection();
            sql = "UPDATE GREEN SET HIT = HIT + 1 WHERE NO = ?";
            ps = con.prepareStatement(sql);
            ps.setInt(1, no);

            result = ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(con, ps, null);
        }

        return result;
    }

    /***** 6. 게시글 삭제하기 *****/
    public int delete(int no) {
        int result = 0;

        try {
            con = dataSource.getConnection();
            sql = "DELETE FROM GREEN WHERE NO = ?";
            ps = con.prepareStatement(sql);
            ps.setInt(1, no);

            result = ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(con, ps, null);
        }

        return result;
    }

    /***** 7. 게시글 수정하기 *****/
    public int update(GreenDto greenDto) {
        int result = 0;
        try {
            con = dataSource.getConnection();
            sql = "UPDATE GREEN SET TITLE = ?, CONTENT = ? WHERE NO = ?";
            ps = con.prepareStatement(sql);
            ps.setString(1, greenDto.getTitle());
            ps.setString(2, greenDto.getContent());
            ps.setInt(3, greenDto.getNo());

            result = ps.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(con, ps, null);
        }
        return result;
    }

}

Common

PathNRedirect.java 생성

package common;

public class PathNRedirect {

    private String path;    // 경로
    private boolean isRedirect;    // 리다이렉트 여부

    public String getPath() {
        return path;
    }
    public void setPath(String path) {
        this.path = path;
    }
    public boolean isRedirect() {
        return isRedirect;
    }
    public void setRedirect(boolean isRedirect) {
        this.isRedirect = isRedirect;
    }

}

Command

Interface 생성으로 다른 command 들이 implements 할 Command 생성

GreenCommand.java

package command;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import common.PathNRedirect;

public interface GreenCommand {

    public PathNRedirect execute(HttpServletRequest request, HttpServletResponse response);

}

GreenListCommand.java

package command;

import java.util.ArrayList;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import common.PathNRedirect;
import dao.GreenDao;
import dto.GreenDto;

public class GreenListCommand implements GreenCommand {

    @Override
    public PathNRedirect execute(HttpServletRequest request, HttpServletResponse response) {
        // Green테이블의 전체목록을 request에 저장해 두기
        ArrayList<GreenDto> list = GreenDao.getInstance().list();
        request.setAttribute("list", list);

        PathNRedirect pathNRedirect = new PathNRedirect();
        pathNRedirect.setPath("green/listPage.jsp");
        pathNRedirect.setRedirect(false);    // forward (request.setAttribute("list", list);) 잘 전달된다.

        return pathNRedirect;
    }

}

GreenInsertCommand.java

package command;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import common.PathNRedirect;
import dao.GreenDao;
import dto.GreenDto;

public class GreenInsertCommand implements GreenCommand {

    @Override
    public PathNRedirect execute(HttpServletRequest request, HttpServletResponse response) {

        // DB에 삽입 후 결과를 request에 저장
        String writer = request.getParameter("writer");
        String title = request.getParameter("title");
        String content = request.getParameter("content");

        GreenDto greenDto = new GreenDto();
        greenDto.setWriter(writer);
        greenDto.setTitle(title);
        greenDto.setContent(content);

        int result = GreenDao.getInstance().insert(greenDto);

        PathNRedirect pathNRedirect = new PathNRedirect();
        pathNRedirect.setPath("green/insertResult.jsp?result=" + result);    // redirect도 새로운 request는 전달된다.
        pathNRedirect.setRedirect(true);    // redirect (기존 request를 넘기지 않는다)

        return pathNRedirect; 

    }

}

GreenViewCommand.java

package command;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import common.PathNRedirect;
import dao.GreenDao;
import dto.GreenDto;

public class GreenViewCommand implements GreenCommand {

    @Override
    public PathNRedirect execute(HttpServletRequest request, HttpServletResponse response) {

        String strNo = request.getParameter("no");
        int no = 0;

        if (strNo != null && !strNo.isEmpty()) {
            no = Integer.parseInt(strNo);
        }

        GreenDao.getInstance().updateHit(no);    // 조회수 증가 결과는 처리하지 않음
        GreenDto greenDto = GreenDao.getInstance().view(no);    

        boolean result = false;
        if (greenDto != null) {
            result = true;
        }
        // boolean result = (greenDto != null);

        PathNRedirect pathNRedirect = new PathNRedirect();
        if (result) {     // result가 true이므로 성공
            request.setAttribute("greenDto", greenDto);
            pathNRedirect.setPath("green/viewPage.jsp");
            pathNRedirect.setRedirect(false);    // forward
        } else {    // 실패
            pathNRedirect.setPath("green/viewFail.jsp");
            pathNRedirect.setRedirect(false);    // forward
        }
            return pathNRedirect;
    }

}

GreenDeleteCommand.java

package command;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import common.PathNRedirect;
import dao.GreenDao;

public class GreenDeleteCommand implements GreenCommand {

    @Override
    public PathNRedirect execute(HttpServletRequest request, HttpServletResponse response) {

        int no = Integer.parseInt(request.getParameter("no"));
        int result = GreenDao.getInstance().delete(no);

        PathNRedirect pathNRedirect = new PathNRedirect();

        //pathNRedirect.setPath("green/deleteResult.jsp");
        if (result > 0) { // 삭제가 성공했을때
            pathNRedirect.setPath("green/deleteSuccess.jsp");
            pathNRedirect.setRedirect(true);    // redirect
        } else {    // 삭제가 실패했을 떄
            pathNRedirect.setPath("green/deleteFail.jsp");
            pathNRedirect.setRedirect(false);    // forward
        }

        return pathNRedirect;
    }

}

GreenUpdateCommand.java

package command;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import common.PathNRedirect;
import dao.GreenDao;
import dto.GreenDto;

public class GreenUpdateCommand implements GreenCommand {

    @Override
    public PathNRedirect execute(HttpServletRequest request, HttpServletResponse response) {

        int no = Integer.parseInt(request.getParameter("no"));
        String title = request.getParameter("title");
        String content = request.getParameter("content");

        GreenDto greenDto = new GreenDto();
        greenDto.setNo(no);
        greenDto.setTitle(title);
        greenDto.setContent(content);

        int result = GreenDao.getInstance().update(greenDto);

        PathNRedirect pathNRedirect = new PathNRedirect();
        if (result > 0) {    // 수정이 성공
            pathNRedirect.setPath("green/updateSuccess.jsp?no=" + no);
            pathNRedirect.setRedirect(true);    // redirect
        } else {    // 수정이 실패하면
            pathNRedirect.setPath("green/updateFail.jsp");
            pathNRedirect.setRedirect(false);    // forward
        }

        return pathNRedirect;
    }

}

---

Controller

Controller에 새로운 기능 들이 추가 될 때 마다. Command를 만들어준다.

GreenController.java

package controller;

import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import command.GreenCommand;
import command.GreenDeleteCommand;
import command.GreenInsertCommand;
import command.GreenListCommand;
import command.GreenUpdateCommand;
import command.GreenViewCommand;
import common.PathNRedirect;

@WebServlet("*.do")
public class GreenController extends HttpServlet {
    private static final long serialVersionUID = 1L;
    public GreenController() {
        super();
    }


    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

        request.setCharacterEncoding("UTF-8");
        response.setContentType("text/html; chareset=UTF-8");

        String requestURI = request.getRequestURI();
        String contextPath = request.getContextPath();
        String cmd = requestURI.substring(contextPath.length());

        GreenCommand command = null;
        // String path = null;
        PathNRedirect pathNRedirect = null;

        switch (cmd) {
        // Command 필요(DB, 로직)
        case "/listPage.do":
            command = new GreenListCommand();
            pathNRedirect = command.execute(request, response);
            break;
        case "/insert.do":
            command = new GreenInsertCommand();
            pathNRedirect = command.execute(request, response);
            break;
        case "/viewPage.do":
            command = new GreenViewCommand();
            pathNRedirect = command.execute(request, response);
            break;
        case "/delete.do" :
            command = new GreenDeleteCommand();
            pathNRedirect = command.execute(request, response);
            break;
        case "/update.do" :
            command = new GreenUpdateCommand();
            pathNRedirect = command.execute(request, response);
            break;

        // 단순이동
        case "/insertPage.do":
            pathNRedirect = new PathNRedirect();
            pathNRedirect.setPath("green/insertPage.jsp");
            pathNRedirect.setRedirect(true);
            break;
        case "/updatePage.do" :
            pathNRedirect = new PathNRedirect();
            pathNRedirect.setPath("green/updatePage.jsp");
            pathNRedirect.setRedirect(false);    // forward
            break;
        }

        String path = pathNRedirect.getPath();

        if (pathNRedirect.isRedirect()) {
            response.sendRedirect(path);
        } else {
            request.getRequestDispatcher(path).forward(request, response);
        }
    }

    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doGet(request, response);
    }
}

ViewPage

다음 포스트