JSP 소규모 게시판 - 12. 글 내용 불러오기 DAO

2023. 9. 11. 23:21

위로 아래

DAO에 메소드 추가

글 목록에서 제목을 누르면 글 내용 페이지가 나올 것이다.

글 목록에 있는 데이터 중 bno를 이용해, 해당 bno의 다른 데이터를 불러오는 방식을 취하겠다.

필요한 인자는 bno이고, 반환할 데이터는 subject, writer, content, passwd 등 여럿이니,

파라미터는 int bno로, 반환은 DTO로 한다.

한 게시글의 정보만 받아오면 되니 배열은 필요 없다.

public BoardDTO getArticle(int bno) {
    
    ...

    return dto;
}

getArticle() 메소드 기본형

public BoardDTO getArticle(int bno) {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    BoardDTO dto = new BoardDTO();

    try {
        String sql = " ... ";
        conn = DBConnectionManager.getInstance().getConnection();
        pstmt = conn.prepareStatement(sql);

        }

    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        try {
            if(conn != null) conn.close();
            if(pstmt != null) pstmt.close();
            if(rs != null) rs.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    return dto;
}

SELECT 쿼리문

해당 bno일 때 모든 행을 조회하면 된다.

String sql = "SELECT bno, bref, bstep, blevel, readcount, "
            + "subject, content, writer, regdate, ip, passwd "
            + "FROM board WHERE bno = ?";

?에는 해당 게시글의 bno를 넣어주면 된다.

pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, bno);

getArticle 메소드

public BoardDTO getArticle(int bno) {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    int r = 0;
    BoardDTO dto = new BoardDTO();

    try {
        String sql = "SELECT bno, bref, bstep, blevel, readcount, "
                + "subject, content, writer, regdate, ip, passwd "
                + "FROM board WHERE bno = ?";
        conn = DBConnectionManager.getInstance().getConnection();
        pstmt = conn.prepareStatement(sql);
        pstmt.setInt(1, bno);
        rs = pstmt.executeQuery();
        if(rs.next()) {
            dto.setBno(rs.getInt("bno"));
            dto.setBref(rs.getInt("bref"));
            dto.setBstep(rs.getInt("bstep"));
            dto.setBlevel(rs.getInt("blevel"));
            dto.setReadcount(rs.getInt("readcount"));
            dto.setSubject(rs.getString("subject"));
            dto.setWriter(rs.getString("writer"));
            dto.setContent(rs.getString("content"));
            dto.setRegdate(rs.getString("regdate"));
            dto.setIp(rs.getString("ip"));
            dto.setPasswd(rs.getString("passwd"));
        }

    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        try {
            if(conn != null) conn.close();
            if(pstmt != null) pstmt.close();
            if(rs != null) rs.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    return dto;
}

전체 코드

package bean;
import java.sql.*;
import java.util.*;

public class BoardDAO {
	private static BoardDAO dao = null;
	
	private BoardDAO () {};
	
	public static BoardDAO getInstance(){
		if(dao==null)
			dao = new BoardDAO();
		return dao;
	}
	
	public int BoardWrite (BoardDTO dto) {
		Connection conn=null;
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		int r = 0;
		conn = DBConnectionManager.getInstance().getConnection();
		
		try {
			
			int newBno = 0;
			String sql = "SELECT IFNULL(MAX(bno),0)+1 AS bno FROM board";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if(rs.next())
				newBno = rs.getInt("bno");
			
			pstmt.close();
			rs.close();
			
			
			sql = "INSERT INTO board "
					+ "(bno, bref, bstep, blevel, "
					+ "subject, content, writer, regdate, "
					+ "ip, passwd, readcount) "
					+ "VALUES(?,?,?,?,?,?,?,SYSDATE(),?,?,?)";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, newBno);
			pstmt.setInt(2, newBno);
			pstmt.setInt(3, dto.getBstep());
			pstmt.setInt(4, dto.getBlevel());
			pstmt.setString(5, dto.getSubject());
			pstmt.setString(6, dto.getContent());
			pstmt.setString(7, dto.getWriter());
			pstmt.setString(8, dto.getIp());
			pstmt.setString(9, dto.getPasswd());
			pstmt.setInt(10, dto.getReadcount());
			
			r = pstmt.executeUpdate();
			
		} catch(SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if(conn!=null) conn.close();
				if(pstmt!=null) pstmt.close();
				if(rs!=null) rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return r;
		
	}
	
	public List<BoardDTO> getArticles() {
		List<BoardDTO> articles = new ArrayList<>();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try {
			String sql = "SELECT ROW_NUMBER() "
					+ "OVER(ORDER BY bref DESC, bno, bstep) AS rn, "
					+ "bno, bref, bstep, blevel, readcount, "
					+ "subject, content, writer, regdate, ip, passwd "
					+ "FROM board";
			conn = DBConnectionManager.getInstance().getConnection();
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			
			while(rs.next()) {
				BoardDTO dto = new BoardDTO();
				dto.setBno(rs.getInt("bno"));
				dto.setBref(rs.getInt("bref"));
				dto.setBstep(rs.getInt("bstep"));
				dto.setBlevel(rs.getInt("blevel"));
				dto.setReadcount(rs.getInt("readcount"));
				dto.setSubject(rs.getString("subject"));
				dto.setWriter(rs.getString("writer"));
				dto.setRegdate(rs.getString("regdate"));
				dto.setIp(rs.getString("ip"));
				dto.setPasswd(rs.getString("passwd"));
				articles.add(dto);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if(conn != null) conn.close();
				if(pstmt != null) pstmt.close();
				if(rs != null) rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return articles;
	}
	
	public BoardDTO getArticle(int bno) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int r = 0;
		BoardDTO dto = new BoardDTO();
		
		try {
			String sql = "SELECT bno, bref, bstep, blevel, readcount, "
					+ "subject, content, writer, regdate, ip, passwd "
					+ "FROM board WHERE bno = ?";
			conn = DBConnectionManager.getInstance().getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, bno);
			rs = pstmt.executeQuery();
			if(rs.next()) {
				dto.setBno(rs.getInt("bno"));
				dto.setBref(rs.getInt("bref"));
				dto.setBstep(rs.getInt("bstep"));
				dto.setBlevel(rs.getInt("blevel"));
				dto.setReadcount(rs.getInt("readcount"));
				dto.setSubject(rs.getString("subject"));
				dto.setWriter(rs.getString("writer"));
				dto.setContent(rs.getString("content"));
				dto.setRegdate(rs.getString("regdate"));
				dto.setIp(rs.getString("ip"));
				dto.setPasswd(rs.getString("passwd"));
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if(conn != null) conn.close();
				if(pstmt != null) pstmt.close();
				if(rs != null) rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return dto;
	}
}

저작자표시

개 발소리 춉춉