boardProject_MVC. flow 정리

1. index.jsp로 시작

<jsp:forward page="ListController.do"></jsp:forward>

<jsp:>을 사용해 ListController로 이동

2. ListController에서 BoardDAO를 통해 게시글 전체를 받아옴

package org.kosta.webstudy25.controller;

import java.util.ArrayList;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.kosta.webstudy25.model.BoardDAO;
import org.kosta.webstudy25.model.PostVO;

// Controller Interface implements
public class ListController implements Controller {
	@Override
	public String execute(HttpServletRequest request, HttpServletResponse response) throws Exception {
		// Post Value를 저장할 객체 생성
		ArrayList<PostVO> list = BoardDAO.getInstance().getPostingList();
		// 객체를 request.setAttribute를 사용해 공유 준비
		request.setAttribute("list", list);
		// url을 request.setAttribute를 사용해 body가 어떤 jsp인지 표현 준비
		request.setAttribute("url", "/board/list.jsp");
		// layout.jsp로 이동할 준비
		return "/template/layout.jsp";
	}
}

2-1. PostDAO에서 게시글 목록 조회

package org.kosta.webstudy25.model;

import java.sql.Connection;

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

import javax.sql.DataSource;

public class BoardDAO {
	private static BoardDAO dao = new BoardDAO();
	private DataSource dataSource;

	private BoardDAO() {
		dataSource = DataSourceManager.getInstance().getDataSource();
	}

	public static BoardDAO getInstance() {
		return dao;
	}

	public Connection getConnection() throws SQLException {
		return dataSource.getConnection();
	}

	public void closeAll(PreparedStatement pstmt, Connection con) throws SQLException {
		if (pstmt != null)
			pstmt.close();
		if (con != null)
			con.close();
	}

	public void closeAll(ResultSet rs, PreparedStatement pstmt, Connection con) throws SQLException {
		if (rs != null)
			rs.close();
		closeAll(pstmt, con);
	}

	/**
	 * 페이지 번호에 해당하는 게시물 목록 리스트를 반환하는 메서드 LIST SQL -> Test 후 반영하세요 SELECT
	 * b.no,b.title,b.hits,to_char(time_posted,'YYYY.MM.DD') as
	 * time_posted,m.id,m.name FROM board b , board_member m WHERE b.id=m.id order
	 * by no desc
	 * 
	 * @param pageNo
	 * @return
	 * @throws SQLException
	 */
	public ArrayList<PostVO> getPostingList() throws SQLException {
		ArrayList<PostVO> list = new ArrayList<PostVO>();
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			con = getConnection();
			StringBuilder sql = new StringBuilder();
			sql.append("SELECT b.no,b.title,b.hits,to_char(time_posted,'YYYY.MM.DD') as time_posted,m.id,m.name ");
			sql.append("FROM board b , board_member m ");
			sql.append("WHERE b.id=m.id ");
			sql.append("order by no desc");
			pstmt = con.prepareStatement(sql.toString());
			rs = pstmt.executeQuery();
			// 목록에서 게시물 content는 필요없으므로 null로 setting
			// select no,title,time_posted,hits,id,name
			while (rs.next()) {
				PostVO pvo = new PostVO();
				pvo.setNo(rs.getString(1));
				pvo.setTitle(rs.getString(2));
				pvo.setHits(rs.getInt(3));
				pvo.setTimePosted(rs.getString(4));
				MemberVO mvo = new MemberVO();
				mvo.setId(rs.getString(5));
				mvo.setName(rs.getString(6));
				pvo.setMemberVO(mvo);
				list.add(pvo);
			}
		} finally {
			closeAll(rs, pstmt, con);
		}
		return list;
	}
}

- DataSourceManager(singleton)

package org.kosta.webstudy25.model;

import javax.sql.DataSource;

import org.apache.tomcat.dbcp.dbcp2.BasicDataSource;

/**
 * 컨넥션 풀pool을 생성하여 공유하는 객체 (컨넥션풀은 컨넥션 필요시 생성,소멸하는 방식이 아니라 빌려오고 반납받는 형식으로 동작하여
 * 성능을 향상시키는 것이 목적)
 * 
 * 시스템 상에서 dbcp 는 하나만 존재하면 되고 여러곳에서 사용하면 되므로 singleton pattern을 적용하여 구현한다.
 * 
 * @author KOSTA
 *
 */
public class DataSourceManager {
	private static DataSourceManager instance = new DataSourceManager();
	private DataSource ds;

	private DataSourceManager() {
		BasicDataSource dataSource = new BasicDataSource();
		dataSource.setDriverClassName("oracle.jdbc.driver.OracleDriver");
		dataSource.setUrl("jdbc:oracle:thin:@127.0.0.1:1521:xe");
		dataSource.setUsername("scott");
		dataSource.setPassword("tiger");
		ds = dataSource;
	}

	public static DataSourceManager getInstance() {
		return instance;
	}

	public DataSource getDataSource() {
		return ds;
	}
}

Controller Interface

package org.kosta.webstudy25.controller;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public interface Controller {
	public String execute(HttpServletRequest request, HttpServletResponse response) throws Exception;
}