JSP 소규모 게시판 - 9. 글목록을 불러오기 위한 DAO 메소드 생성

2023. 9. 10. 21:23

위로 아래

글 목록을 불러오기 위한 getArticles() 메소드

기본형은 다음과 같다.

public 반환타입 getArticles(인자) {
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;

    try {
        String sql = "";
        conn = DBConnectionManager.getInstance().getConnection();
        pstmt = conn.prepareStatement(sql);
        rs = pstmt.executeQuery();
    } 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;
}

반환 타입과 인자

하나의 게시글은 여러 파라미터를 가지고 있는 객체다.

글목록은 게시글을 글의 개수만큼 배열로 가지고 있어야 한다.

즉, 글목록은 객체의 배열로 볼 수 있다.

ArrayList를 이용한다.

ArrayList 타입의 articles 변수를 생성하고,

데이터베이스에서 SELECT 문을 통해 조사한 파라미터를 articles에 저장한다.

public List<BoardDTO> getArticles() {
	List<BoardDTO> articles = new ArrayList<>();
    
    ...
    
	return articles;
}

SQL문

ROWNUM()을 이용해서 정렬해준다.

순서대로 정렬해야하므로 ORDER BY를 쓰기 위해 서브쿼리문으로 작성한다.

순서를 의미하는 bref를 우선순위로 내림차순 정렬하고,

bno와 bstep을 오름차순으로 정렬한다.

String sql = "SELECT ROWNUM AS rn, A.* FROM("
            + "SELECT bno, bref, bstep, blevel, readcount, "
            + "subject, content, writer, regdate, ip, passwd "
            + "FROM board ORDER BY bref DESC bno, bstep) A";

이제 보니 ROWNUM()은 오라클에서만 사용이 가능하다..

MySQL에서 사용할 수 있는 ROW_NUMBER()로 함수를 교체하자.

결과 저장

위의 SQL 문을 통해 조회된 데이터를 DTO 객체 하나에 저장한다.

반복문을 통해 ArrayList에 들은 DTO에 게시글의 개수만큼 객체를 저장한다.

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);
}

getArticles() 메소드가 완성되었다.

public List<BoardDTO> getArticles() {
    List<BoardDTO> articles = new ArrayList<>();
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;

    try {
        String sql = "SELECT ROWNUM AS rn, A.* FROM("
                + "SELECT bno, bref, bstep, blevel, readcount, "
                + "subject, content, writer, regdate, ip, passwd "
                + "FROM board ORDER BY bref DESC bno, bstep) A";
        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;
}

전체 코드

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) "
					+ "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());
			
			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 ROWNUM AS rn, A.* FROM("
					+ "SELECT bno, bref, bstep, blevel, readcount, "
					+ "subject, content, writer, regdate, ip, passwd "
					+ "FROM board ORDER BY bref DESC bno, bstep) A";
			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;
	}
}

저작자표시

개 발소리 춉춉