IJdbcBoardDao /

IJdbcBoardDao

package kr.or.ddit.board.dao;

import java.util.List;

import kr.or.ddit.board.vo.JdbcBoardVO;

public interface IJdbcBoardDao {
	
	/**
	 * JdbcBoardVO에 담겨진 자료를 DB에 insert하는 메서드
	 * 
	 * @param boardVo DB에 insert할 자료가 저장된 JdbcBoardVO객체
	 * @return 작업성공 : 1, 작업실패 : 0
	 */
	public int insertBoard(JdbcBoardVO boardVo);
	
	/**
	 * 게시글 번호를 인수값으로 받아서 해당 게시글을 삭제하는 메서드
	 * 
	 * @param boardNo 삭제할 게시글 번호
	 * @return	작업성공 : 1, 작업실패 : 0
	 */
	public int deleteBoard(int boardNo);
	
	/**
	 * 하나의 JdbcBoardVO자료를 이용하여 DB에 update하는 메서드
	 * 
	 * @param boardVo update할 게시글 정보가 저장된 JdbcBoardVO객체
	 * @return 작업성공 : 1, 작업실패 : 0
	 */
	public int updateBoard(JdbcBoardVO boardVo);
	
	/**
	 * DB에서 전체 게시글 목록을 가져오는 메서드
	 * @return JdbcBoardVO객체를 담고 있는 List객체 
	 */
	public List<JdbcBoardVO> getAllBoardList();
	
	/**
	 * 게시글 번호를 인수값으로 받아서 해당 게시글 정보를 가져오는 메서드
	 * 
	 * @param boardNo 가져올 게시글의 게시글 번호
	 * @return	게시글 번호에 맞는 자료가 있으면 해당 게시글 정보를
	 * 			담고 있는 JdbcBoardVO객체, 자료가 없으면 null 반환
	 */
	public JdbcBoardVO getBoard(int boardNo);
	
	/**
	 * 게시글의 제목을 인수값으로 받아서 게시글을 검색하는 메서드
	 *  
	 * @param title 검색할 게시글의 제목
	 * @return 검색한 결과가 저장된 List객체
	 */
	public List<JdbcBoardVO> getSearchBoardList(String title);
	
	/**
	 * 게시글 번호를 인수값으로 받아서 해당 게시글의 조회수를 증가시키는 메서드
	 * 
	 * @param boardNo 조회수를 증가할 게시글 번호
	 * @return 작업성공 : 1, 작업실패 : 0
	 */
	public int setCountIncrement(int boardNo);
	
}

JdbcBoardDaoImpl

package kr.or.ddit.board.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import kr.or.ddit.board.vo.JdbcBoardVO;
import kr.or.ddit.util.DBUtil3;

public class JdbcBoardDaoImpl implements IJdbcBoardDao{
	private static JdbcBoardDaoImpl dao;
	
	private JdbcBoardDaoImpl(){ }
	
	public static JdbcBoardDaoImpl getInstance(){
		if(dao==null) dao = new JdbcBoardDaoImpl();
		
		return dao;
	}
	
	private Connection conn;
	private PreparedStatement pstmt;
	private Statement stmt;
	private ResultSet rs;
	
	// 사용한 자원을 반납하는 메서드
	private void disConnect(){
		if(rs!=null) try{ rs.close(); }catch(SQLException e){}
		if(stmt!=null) try{ stmt.close(); }catch(SQLException e){}
		if(pstmt!=null) try{ pstmt.close(); }catch(SQLException e){}
		if(conn!=null) try{ conn.close(); }catch(SQLException e){}
	}
	

	@Override
	public int insertBoard(JdbcBoardVO boardVo) {
		int cnt = 0;
		
		try {
			conn = DBUtil3.getConnection();
			
			String sql = "insert into jdbc_board "
					+ "(board_no, board_title, board_writer,"
					+ " board_date, board_cnt, board_content) "
					+ " values(board_seq.nextval, ?, ? , sysdate, 0, ? ) ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, boardVo.getBoard_title());
			pstmt.setString(2, boardVo.getBoard_writer());
			pstmt.setString(3, boardVo.getBoard_content());
			
			cnt = pstmt.executeUpdate();
			
		} catch (SQLException e) {
			cnt = 0;
			e.printStackTrace();
		} finally{
			disConnect();
		}
		
		return cnt;
	}

	@Override
	public int deleteBoard(int boardNo) {
		int cnt = 0;
		
		try {
			conn = DBUtil3.getConnection();
			
			String sql = "delete from jdbc_board where board_no = ? ";
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, boardNo);
			
			cnt = pstmt.executeUpdate();
			
		} catch (SQLException e) {
			cnt = 0;
			e.printStackTrace();
		} finally{
			disConnect();
		}
		
		return cnt;
	}

	@Override
	public int updateBoard(JdbcBoardVO boardVo) {
		int cnt = 0;
		
		try {
			conn = DBUtil3.getConnection();
			
			String sql = "update jdbc_board set "
					+ " board_title = ? ,"
					+ " board_content = ? ,"
					+ " board_date = sysdate "
					+ " where board_no = ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, boardVo.getBoard_title());
			pstmt.setString(2, boardVo.getBoard_content());
			pstmt.setInt(3, boardVo.getBoard_no());
			
			cnt = pstmt.executeUpdate();
			
		} catch (SQLException e) {
			cnt = 0;
			e.printStackTrace();
		} finally {
			disConnect();
		}
		
		return cnt;
	}

	@Override
	public List<JdbcBoardVO> getAllBoardList() {
		List<JdbcBoardVO> boardList = null;
		
		try {
			conn = DBUtil3.getConnection();
			
			String sql = "select board_no, board_title, board_writer,"
					+ " to_char(board_date, 'YYYY-MM-DD') board_date , board_cnt, board_content "
					+ " from jdbc_board "
					+ " order by board_no desc";
			stmt = conn.createStatement();
			
			rs = stmt.executeQuery(sql);
			
			boardList = new ArrayList<>();
			
			while(rs.next()){
				JdbcBoardVO boardVo = new JdbcBoardVO();
				boardVo.setBoard_no(rs.getInt("board_no"));
				boardVo.setBoard_title(rs.getString("board_title"));
				boardVo.setBoard_writer(rs.getString("board_writer"));
				boardVo.setBoard_date(rs.getString("board_date"));
				boardVo.setBoard_cnt(rs.getInt("board_cnt"));
				boardVo.setBoard_content(rs.getString("board_content"));
				
				boardList.add(boardVo);
			}
			
			
		} catch (SQLException e) {
			boardList = null;
			e.printStackTrace();
		} finally {
			disConnect();
		}
		return boardList;
	}

	@Override
	public JdbcBoardVO getBoard(int boardNo) {
		JdbcBoardVO boardVo = null;
		
		try {
			conn = DBUtil3.getConnection();
			
			String sql = "select board_no, board_title, board_writer,"
					+ " to_char(board_date, 'YYYY-MM-DD') board_date , board_cnt, board_content "
					+ " from jdbc_board "
					+ " where board_no = ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, boardNo);
			
			rs = pstmt.executeQuery();
			
			if(rs.next()){
				boardVo = new JdbcBoardVO();
				boardVo.setBoard_no(rs.getInt("board_no"));
				boardVo.setBoard_title(rs.getString("board_title"));
				boardVo.setBoard_writer(rs.getString("board_writer"));
				boardVo.setBoard_date(rs.getString("board_date"));
				boardVo.setBoard_cnt(rs.getInt("board_cnt"));
				boardVo.setBoard_content(rs.getString("board_content"));
			}
			
		} catch (SQLException e) {
			boardVo = null;
			e.printStackTrace();
		} finally{
			disConnect();
		}
		
		return boardVo;
	}

	@Override
	public List<JdbcBoardVO> getSearchBoardList(String title) {
		List<JdbcBoardVO> boardList = null;
		
		try {
			conn = DBUtil3.getConnection();
			
			String sql = "select board_no, board_title, board_writer,"
					+ " to_char(board_date, 'YYYY-MM-DD') board_date , board_cnt, board_content "
					+ " from jdbc_board "
					+ " where board_title like '%' || ? || '%'"
					+ " order by board_no desc";
					// where board_title like '%' || ? || '%'
					// where board_title like '%' || '가' || '%'
			
					// where board_title like '%?%'  ==> 사용 불가
					// where board_title like '%'가'%'
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, title);
			
			rs = pstmt.executeQuery();
			
			boardList = new ArrayList<>();
			
			while(rs.next()){
				JdbcBoardVO boardVo = new JdbcBoardVO();
				boardVo.setBoard_no(rs.getInt("board_no"));
				boardVo.setBoard_title(rs.getString("board_title"));
				boardVo.setBoard_writer(rs.getString("board_writer"));
				boardVo.setBoard_date(rs.getString("board_date"));
				boardVo.setBoard_cnt(rs.getInt("board_cnt"));
				boardVo.setBoard_content(rs.getString("board_content"));
				
				boardList.add(boardVo);
			}
			
			
		} catch (SQLException e) {
			boardList = null;
			e.printStackTrace();
		} finally {
			disConnect();
		}
		return boardList;
	}

	@Override
	public int setCountIncrement(int boardNo) {
		int cnt = 0;
		
		try {
			conn = DBUtil3.getConnection();
			
			String sql = "update jdbc_board "
					+ " set board_cnt = board_cnt + 1 "
					+ "	where board_no = ? ";
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, boardNo);
			
			cnt = pstmt.executeUpdate();
			
		} catch (SQLException e) {
			cnt = 0;
			e.printStackTrace();
		} finally {
			disConnect();
		}
		
		
		return cnt;
	}

}