[JSP & Servlet] 게시판 만들기 (feat. MySQL) - DAO(Data Access Object)

📌src/db/JdbcUtil.java

package db;

import java.sql.*;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

public class JdbcUtil {

	public static Connection getConnection(){
		
		Connection con=null;
		
		String driver = "com.mysql.jdbc.Driver";
		String url = "jdbc:mysql://localhost:3306/testDB";
		
		try {
			
			Class.forName(driver);
			con=DriverManager.getConnection(url,"아이디","비밀번호");
			con.setAutoCommit(false);
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return con;
	}
	
	public static void close(Connection con){
		
		try {
			con.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}
	
	public static void close(Statement stmt){
		
		try {
			stmt.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}
	
	public static void close(ResultSet rs){
		
		try {
			rs.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}
	
	public static void commit(Connection con){
		
		try {
			con.commit();
			System.out.println("commit success");
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}
	
	public static void rollback(Connection con){
		
		try {
			con.rollback();
			System.out.println("rollback success");
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}
	
}

📌DB로 SQL 구문을 전송하는 클래스

boardDAO 클래스

package dao;

import static db.JdbcUtil.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import vo.BoardBean;

public class BoardDAO {

	Connection con;
	private static BoardDAO boardDAO; //외부 클래스에서 접근하지 못하도록 private로 지정

	private BoardDAO() {              //외부 클래스에서 생성자를 사용해서 객체 새로 생성할 없도록
		                              //private로 지정함
	}
	
    //싱글톤 패턴
	public static BoardDAO getInstance(){   
		if(boardDAO == null){       
			boardDAO = new BoardDAO();
		}
		return boardDAO;
	}
    
    //con 연결
	public void setConnection(Connection con){
		this.con = con;
	}



	...
    
    
    
}

위 코드를 보면 알 수 있듯이 싱글톤 패턴으로 BoardDAO 객체를 생성해서 리턴해주도록 만들었다.

싱글톤 패턴을 포함하여 다른 디자인 패턴을 알고 싶다면 다음 링크를 참고해보면 좋다.

https://yeo-computerclass.tistory.com/91

[디자인 패턴] 싱글톤(Singleton) 패턴

Singleton patter이란 전역 변수를 사용하지 않고 객체를 하나만 생성 하도록 하며, 생성된 객체를 어디에서든지 참조할 수 있도록 하는 패턴이다. 방법은 어플리케이션이 시작될 때 어떤 클래스가

yeo-computerclass.tistory.com

게시판 게시글 개수 구하는 메소드

//게시판 전체 글의 개수 구하기.
	public int selectListCount() {

		int listCount= 0;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String query="select count(*) from board";

		try{
			pstmt=con.prepareStatement(query);
			rs = pstmt.executeQuery();
			
			if(rs.next()){
				listCount=rs.getInt(1);
			}
			
		}catch(Exception ex){
			System.out.println("getListCount 에러: " + ex);			
		}finally{
			close(rs);
			close(pstmt);
		}

		return listCount;
	}

게시글 목록 List 추출 메소드

	//게시글 목록 보기.	
	public ArrayList<BoardBean> selectBoardList(int page,int limit){

		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String listQuery="select * from board order by BOARD_RE_REF desc,BOARD_RE_SEQ asc limit ?,10";
		ArrayList<BoardBean> boardList = new ArrayList<BoardBean>();
		BoardBean board = null;
		int startrow=(page-1)*10; //각 page 첫 번째 목록 글 번호	

		try{
			pstmt = con.prepareStatement(listQuery);
			pstmt.setInt(1, startrow);
			rs = pstmt.executeQuery();

			while(rs.next()){
				board = new BoardBean();
				board.setBOARD_NUM(rs.getInt("BOARD_NUM"));
				board.setBOARD_NAME(rs.getString("BOARD_NAME"));
				board.setBOARD_SUBJECT(rs.getString("BOARD_SUBJECT"));
				board.setBOARD_CONTENT(rs.getString("BOARD_CONTENT"));
				board.setBOARD_FILE(rs.getString("BOARD_FILE"));
				board.setBOARD_RE_REF(rs.getInt("BOARD_RE_REF"));
				board.setBOARD_RE_LEV(rs.getInt("BOARD_RE_LEV"));
				board.setBOARD_RE_SEQ(rs.getInt("BOARD_RE_SEQ"));
				board.setBOARD_READCOUNT(rs.getInt("BOARD_READCOUNT"));
				board.setBOARD_DATE(rs.getDate("BOARD_DATE"));
				boardList.add(board);
			}

		}catch(Exception ex){
			System.out.println("getBoardList error : " + ex);
		}finally{
			close(rs);
			close(pstmt);
		}
		return boardList;
	}

listQuery 쿼리는 각 페이지에서 변수로 주어지는 limit 만큼 만 글 목록이 나오게 하는 query이다. SQL 구문이 이해가 잘 되지 않는다면 다음 링크를 참고

https://yeo-computerclass.tistory.com/192?category=1221897

[SQL]개수 제한하여 출력(selelct)하게 해주는 limit

SELECT * FROM member; member 테이블의 레코드 수가 몇 천 개 있다고 가정해보자. 이 때,위 쿼리를 실행하면 member 테이블에서 모든 레코드가 SELECT될 것이다. 만약 우리가 모든 레코드가 아닌 50개 정도만

yeo-computerclass.tistory.com

게시글 상세 보기

	//게시글 상세 보기.
	public BoardBean selectBoard(int board_num){

		PreparedStatement pstmt = null;
		ResultSet rs = null;
		BoardBean boardBean = null;
		String query = "select * from board where BOARD_NUM = ?";

		try{
			pstmt = con.prepareStatement(query);
			pstmt.setInt(1, board_num);
			rs= pstmt.executeQuery();

			if(rs.next()){
				boardBean = new BoardBean();
				boardBean.setBOARD_NUM(rs.getInt("BOARD_NUM"));
				boardBean.setBOARD_NAME(rs.getString("BOARD_NAME"));
				boardBean.setBOARD_SUBJECT(rs.getString("BOARD_SUBJECT"));
				boardBean.setBOARD_CONTENT(rs.getString("BOARD_CONTENT"));
				boardBean.setBOARD_FILE(rs.getString("BOARD_FILE"));
				boardBean.setBOARD_RE_REF(rs.getInt("BOARD_RE_REF"));
				boardBean.setBOARD_RE_LEV(rs.getInt("BOARD_RE_LEV"));
				boardBean.setBOARD_RE_SEQ(rs.getInt("BOARD_RE_SEQ"));
				boardBean.setBOARD_READCOUNT(rs.getInt("BOARD_READCOUNT"));
				boardBean.setBOARD_DATE(rs.getDate("BOARD_DATE"));
			}
		}catch(Exception ex){
			System.out.println("getDetail error : " + ex);
		}finally{
			close(rs);
			close(pstmt);
		}
		return boardBean;
	}

게시글 등록

	//게시글 등록.
	public int insertBoard(BoardBean board){

		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int num =0;
		String query="";
		int insertCount=0;

		try{
			//새로 글이 등록될 게시글 번호
			pstmt=con.prepareStatement("select max(board_num) from board");
			rs = pstmt.executeQuery();
			if(rs.next())
				num =rs.getInt(1)+1;
			else
				num=1;

			query="insert into board (BOARD_NUM,BOARD_NAME,BOARD_PASS,BOARD_SUBJECT,"
					+ "BOARD_CONTENT, BOARD_FILE, BOARD_RE_REF,"
					+ "BOARD_RE_LEV,BOARD_RE_SEQ,BOARD_READCOUNT,"
					+ "BOARD_DATE) values(?,?,?,?,?,?,?,?,?,?,now())";

			pstmt = con.prepareStatement(query);
			pstmt.setInt(1, num);
			pstmt.setString(2, board.getBOARD_NAME());
			pstmt.setString(3, board.getBOARD_PASS());
			pstmt.setString(4, board.getBOARD_SUBJECT());
			pstmt.setString(5, board.getBOARD_CONTENT());
			pstmt.setString(6, board.getBOARD_FILE());
			pstmt.setInt(7, num);
			pstmt.setInt(8, 0);
			pstmt.setInt(9, 0);
			pstmt.setInt(10, 0);

			insertCount=pstmt.executeUpdate();

		}catch(Exception ex){
			System.out.println("boardInsert 에러 : "+ex);
		}finally{
			close(rs);
			close(pstmt);
		}

		return insertCount;

	}

새로 글이 등록될 때 BOARD_NUM과 BOARD_RE_REF에 같은 번호(num)를 부여함으로 원글과 그 글의 답변글이 하나로 묶인다.

게시글 답변

	//게시글 답변
	public int insertReplyBoard(BoardBean board){

		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String maxBoardNumQuery="select max(board_num) from board";
		String query="";
		int num=0;
		int insertCount=0;
		int re_ref=board.getBOARD_RE_REF();
		int re_lev=board.getBOARD_RE_LEV();
		int re_seq=board.getBOARD_RE_SEQ();

		try{
			pstmt=con.prepareStatement(maxBoardNumQuery);
			rs = pstmt.executeQuery();
			if(rs.next())num =rs.getInt(1)+1;
			else num=1;
			
			//답변글의 번호가 입력되는 부분
			query="update board set BOARD_RE_SEQ=BOARD_RE_SEQ+1 "
					+ "where BOARD_RE_REF=? and BOARD_RE_SEQ>?";
			pstmt = con.prepareStatement(query);
			pstmt.setInt(1,re_ref);
			pstmt.setInt(2,re_seq);
			int updateCount=pstmt.executeUpdate();

			if(updateCount > 0){
				commit(con);
			}

			re_seq = re_seq + 1;
			re_lev = re_lev + 1;
            
			query="insert into board (BOARD_NUM,BOARD_NAME,BOARD_PASS,BOARD_SUBJECT,"
					+ "BOARD_CONTENT, BOARD_FILE,BOARD_RE_REF,BOARD_RE_LEV,BOARD_RE_SEQ,"
					+ "BOARD_READCOUNT,BOARD_DATE) values(?,?,?,?,?,?,?,?,?,?,now())";
			pstmt = con.prepareStatement(query);
			pstmt.setInt(1, num);
			pstmt.setString(2, board.getBOARD_NAME());
			pstmt.setString(3, board.getBOARD_PASS());
			pstmt.setString(4, board.getBOARD_SUBJECT());
			pstmt.setString(5, board.getBOARD_CONTENT());
			pstmt.setString(6, ""); //답변에는 파일 업로드 기능 없음.
			pstmt.setInt(7, re_ref);
			pstmt.setInt(8, re_lev);
			pstmt.setInt(9, re_seq);
			pstmt.setInt(10, 0);
			insertCount = pstmt.executeUpdate();
		}catch(SQLException ex){
			System.out.println("boardReply error : "+ex);
		}finally{
			close(rs);
			close(pstmt);
		}

		return insertCount;

	}

	BOARD_RE_REF	BOARD_RE_SEQ
1.원글	1	0
4.답글3	1	1 (0+1)
3.답글2	1	2 (1+1)
2.답글1	1	3 (2+1)

이 처럼 답변을 달고자 하는 글과 같은 글 그룹에 속하고, 답변이 달릴 때 마다 출력순서(BOARD_RE_SEQ)를 하나씩 증가시키는 SQL 구문이다.

게시글 수정

	//게시글 수정
	public int updateBoard(BoardBean board){

		int updateCount = 0;
		PreparedStatement pstmt = null;
		String query="update board set BOARD_SUBJECT=?,BOARD_CONTENT=? where BOARD_NUM=?";

		try{
			pstmt = con.prepareStatement(query);
			pstmt.setString(1, board.getBOARD_SUBJECT());
			pstmt.setString(2, board.getBOARD_CONTENT());
			pstmt.setInt(3, board.getBOARD_NUM());
			updateCount = pstmt.executeUpdate();
		}catch(Exception ex){
			System.out.println("boardModify 에러 : " + ex);
		}finally{
			close(pstmt);
		}
		return updateCount;
	}

게시글 삭제

	//게시글 삭제
	public int deleteBoard(int board_num){

		PreparedStatement pstmt = null;
		String deleteQuery="delete from board where BOARD_num=?";
		int deleteCount=0;

		try{
			pstmt=con.prepareStatement(deleteQuery);
			pstmt.setInt(1, board_num);
			deleteCount=pstmt.executeUpdate();
		}catch(Exception ex){
			System.out.println("boardDelete 에러 : "+ex);
		}	finally{
			close(pstmt);
		}
		return deleteCount;
	}

조회수 증가

	//조회수 업데이트.
	public int updateReadCount(int board_num){

		PreparedStatement pstmt = null;
		int updateCount = 0;
		String sql="update board set BOARD_READCOUNT = "
				+ "BOARD_READCOUNT+1 where BOARD_NUM = "+ board_num;

		try{
			pstmt=con.prepareStatement(sql);
			updateCount = pstmt.executeUpdate();
		}catch(SQLException ex){
			System.out.println("setReadCountUpdate 에러 : "+ex);
		}
		finally{
			close(pstmt);

		}
		return updateCount;
	}

작성자인지 확인

	//글쓴이인지 확인.
	public boolean isBoardWriter(int board_num,String pass){

		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String boardQuery="select * from board where BOARD_NUM=?";
		boolean isWriter = false;

		try{
			pstmt=con.prepareStatement(boardQuery);
			pstmt.setInt(1, board_num);
			rs=pstmt.executeQuery();
			rs.next();

			if(pass.equals(rs.getString("BOARD_PASS"))){
				isWriter = true;
			}
		}catch(SQLException ex){
			System.out.println("isBoardWriter 에러 : "+ex);
		}
		finally{
			close(pstmt);
		}
		return isWriter;
	}

'JSP & Servlet > 실습' 카테고리의 다른 글

[JSP & Servlet] 로그인 (feat. 세션 & 쿠키) - 테이블 생성 및 vo(Value Object) (0)	2022.06.30
[JSP & Servlet] 게시판 만들기 (feat. MySQL) - View (0)	2022.06.29
[JSP & Servlet] 게시판 만들기 (feat. MySQL) - Service 클래스 (0)	2022.06.27
[JSP & Servlet] 게시판 만들기 (feat. MySQL) - Action 클래스 (0)	2022.06.26
[JSP & Servlet] 게시판 만들기 (feat. MySQL) - Controller 클래스 (0)	2022.06.26