[21] Manager Class (ConnectionMgr.java)

 

파일명 : ConnectionMgr.java

------------------------------------------------------

package www.utility;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

/**
 * 데이터베이스 접속 관리
 */
public class ConnectionMgr{

    /** 데이터베이스 접속 커넥션 모드를 정의 
     *  1: Connection Pool 사용 안함
     *  2: DBCP Connection Pool 사용
     */   
    int connectionMode = 1;
   
    /**
     * 기본 생성자
     */
    public ConnectionMgr() {
        super();
    }
   
    public ConnectionMgr(int connectionMode){
        this.connectionMode = connectionMode;
    }

    /**
     * DBMS에 연결하여 Connection 객체 리턴
     * @return
     */
    public Connection getConnection(){
        Connection con = null;
        try {
            if ( connectionMode == 1 ){ // Pool 사용 안함
                DBConnect dbconnect = new DBConnect();
                con = dbconnect.getConnection(); //MySQL 연결
                System.out.println("ConnectionMode 1: " + con.hashCode());
            } else if ( connectionMode == 2 ){ // Pool 사용
                // ora10g2 은 web.xml의 registerPool 변수의 값이어야 함 
                String poolName = "jdbc:apache:commons:dbcp:ora10g2";
                con = DriverManager.getConnection(poolName);
                System.out.println("ConnectionMode 2: " + con.hashCode());
                               }
        } catch(SQLException ex) {
            ex.printStackTrace();
        } finally {
        }
        return con;
    }
}

------------------------------------------------------

 

파일명 : BbsMgr.java

------------------------------------------------------

 package www.bbs;

import java.sql.Connection;
import java.util.ArrayList;
import java.util.List;

import www.utility.ConnectionMgr;
import www.utility.DBClose;

public class BbsMgr {
    /** Connection Pool manager Class */
    private ConnectionMgr dbconnect = null;  
   
//    DBConnection dbconnect=new DBConnection();
//    Connection con=dbconnect.getConnection();
   
    /** SQL 처리 클래스 */
    private static BbsDAO dao = null;     
  
    /**
     * 기본 생성자
     */
    public BbsMgr(){
        if (dbconnect == null){
            dbconnect = new ConnectionMgr();
        }
    }
   
    /**
     * DAO객체 생성 메소드
     * @return
     */
    public synchronized BbsDAO getDAO(){
        if (dao == null){
            dao = new BbsDAO();           
        }
        return dao;
    }

    /**
     * 데이터 목록 가져오기
     */
    public ArrayList list(){
        Connection con = this.dbconnect.getConnection();
        ArrayList list = null;

        try{
            list = getDAO().list(con); //BbsDAO클래스의 list()호출
        }catch(Exception e){
            System.out.println(e.toString());
        }finally{
            DBClose.close(con);
        }
  
        return list;       
    }
   
    //페이징,검색 목록
    public ArrayList list(int nowPage){
        Connection con = this.dbconnect.getConnection();
        ArrayList list = null;

        try{
            list = getDAO().list(con,nowPage); //BbsDAO클래스의 list()호출
        }catch(Exception e){
            System.out.println(e.toString());
        }finally{
            DBClose.close(con);
        }
  
        return list;       
    }
   
    /**
     * 레코드 추가하기
     * @param dto 추가할 데이터를 가지고 있는 개체
     * @return 성공하면 "true", 실패하면 "false"
     */
    public boolean create(BbsDTO dto){
        Connection con = this.dbconnect.getConnection();
        boolean retVal = false; //성공 true, 실패 false
        int cnt = 0;

        try{
            cnt = getDAO().create(con,dto);
            if (cnt == 1){
                retVal = true;
            }
        }catch(Exception e){
            e.printStackTrace();
            System.out.println(e.toString());
        }finally{
            DBClose.close(con);
        }
       
        return retVal;     
    }
   
    /**
     * 한건의 데이터를 읽어 옵니다.
     * @param key Primary Key
     */
    public BbsDTO read(int key){
        Connection con = this.dbconnect.getConnection();
        BbsDTO dto = null;
       
        try{
            // 글 읽어 오기
            dto = getDAO().read(con, key);
        }catch(Exception e){
            System.out.println(e.toString());
        }finally{
            DBClose.close(con);
        }

        return dto;
    }
   
}

 

------------------------------------------------------

 

파일명 : BbsDAO.java

------------------------------------------------------

 package www.bbs;

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

import www.utility.DBConnect;
import www.utility.DBClose;

public class BbsDAO {
    DBConnect dbconnect=null;
    /** 테이블 이름 */
    private String table_name="tb_bbs";
   
    public BbsDAO() {
        dbconnect=new DBConnect();
    }
   
    //추가
    public int create(Connection con,BbsDTO dto) throws SQLException{
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        int cnt = 0;

        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT MAX(grpno)+1 as maxno FROM "+table_name);
       
        pstmt = con.prepareStatement(sql.toString());
        rs = pstmt.executeQuery();
        rs.next();//레코드포인터 정확히 가르킴
        int grpno = rs.getInt("maxno");
 
        dto.setGrpno(grpno);
       
        // StringBuffer를 재사용하기위해 기존에 저장된 SQL 삭제, 글 추가 SQL
        // sql.toString().length(): 저장된 문자열의 전체 길이
        sql.delete(0, sql.toString().length()); // SQL 문자열 전체 삭제
       
        sql.append(" INSERT INTO " + table_name);
        sql.append(" (wname, subject, content, grpno, indent");
        sql.append(" ,ansnum, regdate, passwd, cnt, ip)");
        sql.append(" VALUES(?,?,?,?,?,?,now(),?,?,?)");
                 
        pstmt = con.prepareStatement(sql.toString());

        pstmt.setString(1, dto.getWname());
        pstmt.setString(2, dto.getSubject());
        pstmt.setString(3, dto.getContent());
        pstmt.setInt(4, dto.getGrpno());
        pstmt.setInt(5, dto.getIndent());
        pstmt.setInt(6, dto.getAnsnum());
        pstmt.setString(7, dto.getPasswd());
        pstmt.setInt(8, dto.getCnt());
        pstmt.setString(9, dto.getIp());           
        // 추가한 레코드의 1이 cnt 변수에 저장
        cnt = pstmt.executeUpdate();
       
        DBClose.close(pstmt, rs);           

        return cnt;  // 1 or 0

    }
   
   
     
    //목록
    public ArrayList list(Connection con) throws SQLException{
        //Connection con=dbconnect.getConnection(); BbsMgr클래스에서 처리
        PreparedStatement pstmt=null;
        ResultSet rs=null;
        StringBuffer sql=null;
        ArrayList list=null;
       
        sql=new StringBuffer();
        sql.append(" SELECT bbsno,grpno,indent,ansnum,subject,wname,cnt,regdate ");
        sql.append(" FROM "+table_name);
        sql.append(" ORDER BY grpno DESC,ansnum ASC ");
       
        list=new ArrayList();

        pstmt=con.prepareStatement(sql.toString());
        rs=pstmt.executeQuery();
        while(rs.next()){
            BbsDTO dto=new BbsDTO();
            dto.setBbsno(rs.getInt("bbsno"));
            dto.setSubject(rs.getString("subject"));
            dto.setWname(rs.getString("wname"));
            dto.setGrpno(rs.getInt("grpno"));
            dto.setIndent(rs.getInt("indent"));
            dto.setAnsnum(rs.getInt("ansnum"));
            dto.setCnt(rs.getInt("cnt"));
            dto.setRegdate(rs.getString("regdate"));
            list.add(dto);
        }

        DBClose.close(pstmt,rs);
       
        return list;
    }

   
    //페이징, 검색 목록
    public ArrayList list(Connection con,int nowPage) throws SQLException{
        PreparedStatement pstmt=null;
        ResultSet rs=null;
        StringBuffer sql=null;
        ArrayList list=null;
       
        sql=new StringBuffer();
        sql.append(" SELECT bbsno,grpno,indent,ansnum,subject,wname,cnt,regdate ");
        sql.append(" FROM "+table_name);
        sql.append(" ORDER BY grpno DESC,ansnum ASC limit "+(       ) ",5");
        //SELECT 결과 중 11번째부터 5개 가져오기
        //LIMIT 10,5
       
       
        list=new ArrayList();

        pstmt=con.prepareStatement(sql.toString());
        rs=pstmt.executeQuery();
        while(rs.next()){
            BbsDTO dto=new BbsDTO();
            dto.setBbsno(rs.getInt("bbsno"));
            dto.setSubject(rs.getString("subject"));
            dto.setWname(rs.getString("wname"));
            dto.setGrpno(rs.getInt("grpno"));
            dto.setIndent(rs.getInt("indent"));
            dto.setAnsnum(rs.getInt("ansnum"));
            dto.setCnt(rs.getInt("cnt"));
            dto.setRegdate(rs.getString("regdate"));
            list.add(dto);
        }

        DBClose.close(pstmt,rs);
       
        return list;
    }
   
    //상세보기
    public BbsDTO read(Connection con,int bbsno) throws SQLException{
        PreparedStatement pstmt=null;
        ResultSet rs=null;
        StringBuffer sql=null;
        BbsDTO dto=new BbsDTO();       
       
     //조회수 증가
        sql = new StringBuffer();
        sql.append(" UPDATE "+table_name);
        sql.append(" SET cnt = cnt+1 ");
        sql.append(" WHERE bbsno=?");
        pstmt = con.prepareStatement(sql.toString());
        pstmt.setInt(1, bbsno);       
        pstmt.executeUpdate();         
        sql.delete(0, sql.toString().length());  // 기존에 저장된 SQL 삭제
     
     //레코드 가져오기
        sql=new StringBuffer();
        sql.append(" SELECT bbsno,subject,content,wname,cnt,regdate ");
        sql.append(" FROM "+table_name);
        sql.append(" where bbsno=? ");   
   
        pstmt = con.prepareStatement(sql.toString());           
        pstmt.setInt(1,bbsno); //첫번째 ?표로 글번호를 할당           
        rs = pstmt.executeQuery(); //ResultSet에 레코드 담기
       
        if(rs.next()){
            dto.setBbsno(rs.getInt("bbsno"));
            dto.setSubject(rs.getString("subject"));
            dto.setContent(rs.getString("content"));
            dto.setWname(rs.getString("wname"));
            dto.setCnt(rs.getInt("cnt"));
            dto.setRegdate(rs.getString("regdate"));
        }
           
        DBClose.close(pstmt,rs);
       
        return dto;

    }

------------------------------------------------------