Jsp - 게시글 생성,삭제,갱신,목록조회 DAO

package board;
 
import java.sql.Timestamp;
 
public class BoardDTO {
    private int num;
    private int readcount;
    private String id;
    private String pass;
    private String name;
    private Timestamp time;
    private String subject;
    private String content;
    public int getNum() {
        return num;
    }
    public void setNum(int num) {
        this.num = num;
    }
    public int getReadcount() {
        return readcount;
    }
    public void setReadcount(int readcount) {
        this.readcount = readcount;
    }
    public String getId() {
        return id;
    }
    public void setId(String id) {
        this.id = id;
    }
    public String getPass() {
        return pass;
    }
    public void setPass(String pass) {
        this.pass = pass;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public Timestamp getTime() {
        return time;
    }
    public void setTime(Timestamp time) {
        this.time = time;
    }
    public String getSubject() {
        return subject;
    }
    public void setSubject(String subject) {
        this.subject = subject;
    }
    public String getContent() {
        return content;
    }
    public void setContent(String content) {
        this.content = content;
    }
}
 
//-----------------BoardDTO 끝------------------------
 
package board;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
 
public class BoardDAO {
    
    public  Connection con= null;
    public  PreparedStatement pstmt=null;
    public  ResultSet rs= null;
    
    public Connection getConnection()  throws Exception{//커넥션 
        Class.forName("com.mysql.jdbc.Driver");
        String url="jdbc:mysql://localhost:3306/jspdb5";
        String user="jspid";
        String password="jsppass";
        con=DriverManager.getConnection(url, user, password);
        
        
        return con;
    }
 
    public void insertBoard(BoardDTO bb) {//게시판생성
    
        try {
            con=getConnection();
            String sql="select max(num) from board";
            pstmt=con.prepareStatement(sql);
            rs=pstmt.executeQuery();
            if(rs.next()){
                int num=rs.getInt("max(num)")+1;
                bb.setNum(num);
            }
            
            sql="insert into board(num,name,pass,subject,content,readcount,time) values(?,?,?,?,?,?,?)";
            pstmt=con.prepareStatement(sql);
            pstmt.setInt(1, bb.getNum());
            pstmt.setString(2, bb.getName());
            pstmt.setString(3, bb.getPass());
            pstmt.setString(4, bb.getSubject());
            pstmt.setString(5, bb.getContent());
            pstmt.setInt(6, bb.getReadcount());
            pstmt.setTimestamp(7, bb.getTime());
 
            
            pstmt.executeUpdate();
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            if (rs!=null) try{rs.close();} catch(SQLException ex) {}
            if (pstmt!=null) try{pstmt.close();} catch(SQLException ex) {}
            if (con!=null) try{con.close();} catch(SQLException ex) {}
                
        }
        
    }
    
    public List<BoardDTO> checkList() {//글목록
        List<BoardDTO> list = new ArrayList<BoardDTO>();
        try {
            con=getConnection();
            String sql="select * from board order by num desc";
            pstmt=con.prepareStatement(sql);
            rs=pstmt.executeQuery();
            while(rs.next()) {
                BoardDTO bb=new BoardDTO();
                bb.setNum(rs.getInt("num"));
                bb.setName(rs.getString("name"));
                bb.setPass(rs.getString("pass"));
                bb.setReadcount(rs.getInt("readcount"));
                bb.setContent(rs.getString("content"));
                bb.setSubject(rs.getString("subject"));
                bb.setTime(rs.getTimestamp("time"));
                list.add(bb);
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            if (rs!=null) try{rs.close();} catch(SQLException ex) {}
            if (pstmt!=null) try{pstmt.close();} catch(SQLException ex) {}
            if (con!=null) try{con.close();} catch(SQLException ex) {}
        }
        
        return list;
    }
    
    public void updateReadCount(int num) {//조회수상승
        try {
            con=getConnection();
            String sql= "UPDATE board SET readcount=readcount+1 WHERE num=?";
            pstmt=con.prepareStatement(sql);
            pstmt.setInt(1, num);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            if (rs!=null) try{rs.close();} catch(SQLException ex) {}
            if (pstmt!=null) try{pstmt.close();} catch(SQLException ex) {}
            if (con!=null) try{con.close();} catch(SQLException ex) {}
        }
        
    }
    
    public BoardDTO ContentCheck(int num) {//글내용 확인
        
        BoardDTO bb=new BoardDTO();
        try {
            con=getConnection();
    
            rs=getResultSet(num);
            if(rs.next()) {
                bb.setNum(rs.getInt("num"));
                bb.setName(rs.getString("name"));
                bb.setPass(rs.getString("pass"));
                bb.setReadcount(rs.getInt("readcount")+1);
                bb.setContent(rs.getString("content"));
                bb.setSubject(rs.getString("subject"));
                bb.setTime(rs.getTimestamp("time"));
                
                
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            if (rs!=null) try{rs.close();} catch(SQLException ex) {}
            if (pstmt!=null) try{pstmt.close();} catch(SQLException ex) {}
            if (con!=null) try{con.close();} catch(SQLException ex) {}
        }
        return bb;
    }
    
    public ResultSet getResultSet(int num)throws Exception{//ResultSet  리턴
        con=getConnection();
        String sql="SELECT * FROM board where num=?";
        pstmt=con.prepareStatement(sql);
        pstmt.setInt(1, num);
        rs=pstmt.executeQuery();
        
        return rs;
    }
    
    public void UpdateBoard(BoardDTO bb) {//글내용 수정
        try {
        
            rs=getResultSet(bb.getNum());
            if(rs.next()) {
                
                if(bb.getPass().equals(rs.getString("pass"))){
                    String sql="UPDATE board SET name=?, subject=?, content=? where num=? ";
                    pstmt=con.prepareStatement(sql);
                    pstmt.setString(1, bb.getName());
                    pstmt.setString(2, bb.getSubject());
                    pstmt.setString(3, bb.getContent());
                    pstmt.setInt(4,bb.getNum());
                    
                    pstmt.executeUpdate();
                    
                }
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            if (rs!=null) try{rs.close();} catch(SQLException ex) {}
            if (pstmt!=null) try{pstmt.close();} catch(SQLException ex) {}
            if (con!=null) try{con.close();} catch(SQLException ex) {}
        }
        
        
    }
    
    public void DeleteBoard(BoardDTO bb) {//글내용 삭제
        try {
            con=getConnection();
            rs=getResultSet(bb.getNum());
            if(rs.next()) {
                if(bb.getPass().equals(rs.getString("pass"))) {
                    String sql="DELETE FROM board WHERE num=?";
                    pstmt=con.prepareStatement(sql);
                    pstmt.setInt(1, bb.getNum());
                    pstmt.executeUpdate();
                }
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            if (rs!=null) try{rs.close();} catch(SQLException ex) {}
            if (pstmt!=null) try{pstmt.close();} catch(SQLException ex) {}
            if (con!=null) try{con.close();} catch(SQLException ex) {}
        }
    }
    
    public int  getBoardCount() {//게시글 수 가져오기(페이징)
        int c=0;
        try {
            con=getConnection();
            String sql="SELECT count(*) FROM board";
            pstmt=con.prepareStatement(sql);
            rs=pstmt.executeQuery();
            if(rs.next()) {
                
                c=rs.getInt("count(*)");
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            if (rs!=null) try{rs.close();} catch(SQLException ex) {}
            if (pstmt!=null) try{pstmt.close();} catch(SQLException ex) {}
            if (con!=null) try{con.close();} catch(SQLException ex) {}
        }
        
        return c;
    }
    
    public List<BoardDTO> getBoardList(int startRow,int pageSize) {//게시판 글갯수만큼 가져오기(페이징)
        List<BoardDTO> list = new ArrayList<BoardDTO>();
        try {
            con=getConnection();
//            String sql="select * from board order by num desc limit 시작행-1,개수";
            String sql="select * from board order by num desc limit ?,?";
            pstmt=con.prepareStatement(sql);
            pstmt.setInt(1, startRow-1);
            pstmt.setInt(2, pageSize);
            rs=pstmt.executeQuery();
            while(rs.next()) {
                BoardDTO bb=new BoardDTO();
                bb.setNum(rs.getInt("num"));
                bb.setName(rs.getString("name"));
                bb.setPass(rs.getString("pass"));
                bb.setReadcount(rs.getInt("readcount"));
                bb.setContent(rs.getString("content"));
                bb.setSubject(rs.getString("subject"));
                bb.setTime(rs.getTimestamp("time"));
                list.add(bb);
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            if (rs!=null) try{rs.close();} catch(SQLException ex) {}
            if (pstmt!=null) try{pstmt.close();} catch(SQLException ex) {}
            if (con!=null) try{con.close();} catch(SQLException ex) {}
        }
        
        return list;
    }
    
}
 
 
 

 

DB에 데이터를 저장과 반환을 위한 DTO(bean)과 DTO를 활용한 기능을 구현하기 위한 메서드의 집합인 DAO를 적어보았다. 어려운 건 없다고 생각하는데 sql 구문만 조금 달라지고 구현하기 위한 필수 코드들은 비슷하다.

그래도 헷갈리지말고 잘 활용해서 깔끔한 코딩을 해야겠다.