[Day4] JSP/JDBC 4 - Board

[Day4] JSP/JDBC 4 [10/31]

주의) PL/SQL 은 저장프로시저에서 예시로 봤을 때 테이블명과 칼럼명은 변수로 사용하지 못함

게시판 생성

1. 게시판(board) 테이블 생성

C# .net (닷넷)

seq : 일련번호, 기본키. 각 글을 구분하는 번호입니다.
writer : 글쓴이
pwd : 비밀번호
email : 메일주소
title : 제목
writedate : 글쓴 일시
readed : 조회수
mode : 글의 형식( 0-TEXT, 1-HTML 태그 허용 )
content : 글의 내용

CREATE SEQUENCE seq_tbl_cstVSBoard;
CREATE TABLE tbl_cstVSBoard(
	seq       NUMBER NOT NULL PRIMARY KEY           
	, writer VARCHAR2(20) NOT NULL 
	, pwd    VARCHAR2(20) NOT NULL 
	, email  VARCHAR2(100) 
	, title    VARCHAR2(200) NOT NULL 
	, writedate DATE DEFAULT SYSDATE
	, readed NUMBER DEFAULT 0
	, tag   NUMBER(1)  DEFAULT 0 -- 0 텍스트모드  1 HTML모드 
	, content CLOB 
);

2. 글쓰기

글목록 페이지
[글쓰기]버튼     ->  클릭 ->   글쓰기(새글작성) 페이지
        제목/내용/등등 입력 작업
            [저장][목록][취소] 등등
            ㄴ저장 클릭

3. domain.BoardDTO 선언

4. [순서]

Controller -> Handler -> Service -> DAO -> DB (Oracle)

처리 완료되면 반대로 결과물 반환

Controller - Service - Handler(Model) -> DAO -> 오라클,

insert(dto)

dto dto dto

* 여기선 (Controller + Model + View) 하나로 처리

[기본설정]

* main

Connection conn = DBConn.getcConnection();
BoardDAO dao = new BoardDAOImpl(conn);
BoardService service = new BoradService(dao);
BoardController controller = new BoardController(service);
controller.boardStart();

* DTO

package days03.board;

import java.sql.Date;

public class BoardDTO {

	private int seq;           
	private String  writer; 
	private String  pwd;
	private String  email;
	private String  title;
	private Date  writedate;
	private int  readed;
	private int tag;
	private String   content;

	public BoardDTO() {
		super(); 
	}

	public BoardDTO(int seq, String writer, String pwd, String email, String title, Date writedate, int readed, int tag,
			String content) {
		super();
		this.seq = seq;
		this.writer = writer;
		this.pwd = pwd;
		this.email = email;
		this.title = title;
		this.writedate = writedate;
		this.readed = readed;
		this.tag = tag;
		this.content = content;
	}

	public int getSeq() {
		return seq;
	}

	public void setSeq(int seq) {
		this.seq = seq;
	}

	public String getWriter() {
		return writer;
	}

	public void setWriter(String writer) {
		this.writer = writer;
	}

	public String getPwd() {
		return pwd;
	}

	public void setPwd(String pwd) {
		this.pwd = pwd;
	}

	public String getEmail() {
		return email;
	}

	public void setEmail(String email) {
		this.email = email;
	}

	public String getTitle() {
		return title;
	}

	public void setTitle(String title) {
		this.title = title;
	}

	public Date getWritedate() {
		return writedate;
	}

	public void setWritedate(Date writedate) {
		this.writedate = writedate;
	}

	public int getReaded() {
		return readed;
	}

	public void setReaded(int readed) {
		this.readed = readed;
	}

	public int getTag() {
		return tag;
	}

	public void setTag(int tag) {
		this.tag = tag;
	}

	public String getContent() {
		return content;
	}

	public void setContent(String content) {
		this.content = content;
	}

	@Override
	public String toString() {
		return "BoardDTO [seq=" + seq + ", writer=" + writer + ", pwd=" + pwd + ", email=" + email + ", title=" + title
				+ ", writedate=" + writedate + ", readed=" + readed + ", tag=" + tag + ", content=" + content + "]";
	} 
} // class

* DAO interface

package days03.board;

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

public interface BoardDAO {
	
	// 1. 글 쓰기
	int insert( BoardDTO dto ) throws SQLException; 
	
	// 2. 글 목록
	ArrayList<BoardDTO> select( int currentPage, int numberPerPage  ) throws SQLException; 

	// 3. 글 상세보기
	// 3-1. 조회수 증가
	int increaseReaded(int seq)  throws SQLException; 
	// 3-2. 해당 게시글 반환
	BoardDTO view(int seq) throws SQLException;

	// 4. 글 삭제
	// 4-1. 비밀번호 확인
	String getOriginalPwd(int seq) throws SQLException;
	// 4-2. 글 삭제.
	int delete(int seq) throws SQLException;

	// 5. 수정
	int update(BoardDTO dto) throws SQLException;

	// 6. 검색
	ArrayList<BoardDTO> search( int currentPage, int numberPerPage, int searchCondition, String searchWord) throws SQLException;

	// 총레코드수 
	int getTotalRecords() throws SQLException;
	// 총페이지수
	int getTotalPages(int numberPerPage) throws SQLException;

	//  검색하기 - 총레코드수
	int getTotalRecords(int searchCondition, String searchWord) throws SQLException;
     // 검색하기 - 총페이지수
	int getTotalPages(int numberPerPage, int searchCondition, String searchWord) throws SQLException;
	
}  // interface

* DAOImpl

public class BoardDAOImpl implements BoardDAO{

	private Connection conn = null;
	private PreparedStatement pstmt  = null;
	private ResultSet rs = null;

	public BoardDAOImpl() {}
	// 1. 생성자 DI( 의존성 주입 )
	public BoardDAOImpl(Connection conn) {
		this.conn = conn;
	}
	// 2. setter DI
	public void setConn(Connection conn) {
		this.conn = conn;
	}
}

* Service

public class BoardService {

	private BoardDAO dao = null;

	// 1. 생성자 DI
	public BoardService(BoardDAO dao) {
		super();
		this.dao = dao;
	}

	// 2. setter DI
	public void setDao(BoardDAO dao) {
		this.dao = dao;
	}
}

* Controller

public class BoardController {
	
	// 요청(C) + 로직처리(M) + 출력(V)	
	private BoardService service = null;
	private int selectedNumber = 1;
	private Scanner scanner = null;
	
	public BoardController() {
		super();
		this.scanner = new Scanner(System.in);
	}

	// 1. 생성자 DI(의존성 주입)
	public BoardController(BoardService service) {
		this();
		this.service = service;
	}
}

public void boardStart() {
		
		while(true) {
			게시판메뉴출력(); // 
			게시판메뉴선택(); // 요청(C) +
			게시판메뉴처리();  // Model + View X
		} // while
		
	}

private void 게시판메뉴출력() {
		 String [] menus = {"새글", "목록", "보기", "수정", "삭제", "검색", "종료" };
		 System.out.print("[ 메뉴 ]  ");
		 for (int i = 0; i < menus.length; i++) {
			System.out.printf("%d. %s\t", i+1, menus[i]);			
		} // for
		 System.out.println();
	}

private void 게시판메뉴선택() {
		System.out.print("> 메뉴 선택하세요 ? ");
		selectedNumber = scanner.nextInt();  // 1  엔터           13,10
		scanner.nextLine();  // 13,10 버림.
	}

private void 게시판메뉴처리() {
		 switch (  selectedNumber  ) {
			case 1:   // "새글"
				새글쓰기();
				break;
			case 2:   // , "목록"
				목록보기();
				break;
			case 3:   // , "보기",
				상세보기();
				break;			
			case 4:   //  "수정"
				수정하기();
				break;			
			case 5:  // , "삭제
				삭제하기();
				break;	
			case 6:   // ", "검색"
				break;		
			case 7:  //  , "종료"
				exit();
				break;	
			default:
				System.out.println("\t\t[경고] 메뉴 선택 잘못했다( 1~6)!!!");
				break;
			} // switch	
	}

private static void exit() {
		System.out.println("\t\t 프로그램 종료 !!!");
		DBConn.close();
		System.exit(-1);
}

[1. 글쓰기]

글쓰기 요청

-> 1) DB insert

-> 2) 로그 기록

-> 3) 작성자에게 포인트 1증가 update

1), 2), 3) 모두 성공해야 성공하는 처리 = 트랜잭션 처리 (transaction)

1) DAOImpl

@Override
public int insert(BoardDTO dto) throws SQLException {
	String sql = "INSERT INTO TBL_CSTVSBOARD "
		+ " ( seq, writer, pwd, email, title, tag, content )"
		+ " VALUES (  SEQ_TBL_CSTVSBOARD.NEXTVAL , ? , ? , ? , ? , ? , ? ) ";

	this.pstmt = this.conn.prepareStatement(sql);
	// ? , ? , ? , ? , ? , ?
	this.pstmt.setString(1,  dto.getWriter());
	this.pstmt.setString(2,  dto.getPwd());
	this.pstmt.setString(3,  dto.getEmail());
	this.pstmt.setString(4,  dto.getTitle());
	this.pstmt.setInt(5,  dto.getTag());
	this.pstmt.setString(6,  dto.getContent());

	int rowCount =  this.pstmt.executeUpdate();
	
	this.pstmt.close();

	return rowCount;
}

2) DAOImpl test

@Test
void insert_test() {
	//fail("Not yet implemented");

	Connection conn = DBConn.getConnection();
	BoardDAOImpl dao = new BoardDAOImpl(conn);

	BoardDTO dto = new BoardDTO();
	// writer, pwd, email, title, tag, content
	dto.setWriter("홍길동");
	dto.setPwd("1234");
	dto.setEmail("hong@naver.com");
	dto.setTitle("첫 번째 게시글 - 단위 테스트 ");
	dto.setTag(0);  // 텍스트 모드
	dto.setContent("첫 번째 게시글 내용");

	try {
		int rowCount = dao.insert(dto);
		if( rowCount == 1 ) {
			System.out.println(" 글쓰기 단위 테스트 성공!!!");
		}else {
			System.out.println(" 글쓰기 실패!!!");
		}
	} catch (SQLException e) {
		e.printStackTrace();
	}		 
	DBConn.close();
}

3) Service

public int insertService( BoardDTO dto) {
	int rowCount = 0;		

	try {
		// 트랜잭선 처리 시작			T1
		// 1. 로그 기록 서비스
		System.out.println("> 게시글 쓰기(추가) -> 로그 기록 작업...");
		// 2. 게시글 추가
		rowCount = this.dao.insert(dto);
		// 3. 작성자의 포인트 1증가 - update
		//    회원테이블 X - point 컬럼  X
		// this.dao.updateMemberPoint(dto.getWriter());
		System.out.println("> 게시글 쓰기(추가)  작성자 포인트 증가 this. dao.update~() ..");
        
        // 커밋			 T2
	} catch (SQLException e) {
		// 롤백 T3
		e.printStackTrace();
	}
	return rowCount;
}

4) Service 테스트

@Test
void inserService_test() {
		
	Connection conn = DBConn.getConnection();
	BoardDAOImpl dao = new BoardDAOImpl(conn);
	BoardService service = new BoardService(dao);
		
	BoardDTO dto = new BoardDTO();
	// writer, pwd, email, title, tag, content
	dto.setWriter("홍길동");
	dto.setPwd("1234");
	dto.setEmail("hong@naver.com");
	dto.setTitle("두 번째 게시글 - 서비스 단위 테스트 ");
	dto.setTag(0);  // 텍스트 모드
	dto.setContent("두 번째 게시글 내용");
		
	int rowCount =  service.insertService(dto);
	if( rowCount == 1 ) {
		System.out.println(" 글쓰기 단위 테스트 성공!!!");
	}else {
		System.out.println(" 글쓰기 실패!!!");
	}	
	DBConn.close();
}

5) Controller

private void 새글쓰기() {
	// 새 게시글 정보를 콘솔에서 입력
	System.out.print("> writer, pwd, email, title, tag, content 입력 ? ");
	//  "권재현,1234,kkk@naver.com,세  번째  게시글,0,세  번째  게시글 내용"
	String [] datas = scanner.nextLine().split(",");
		
	String writer =  datas[0];
	String pwd = datas[1];
	String email = datas[2];
	String title = datas[3];
	int tag = Integer.parseInt(datas[4]);
	String content = datas[5];
		
	// [C]dto -> [S]dto-> [D]dto-> DB INSERT
	BoardDTO dto = new BoardDTO();
	dto.setWriter(writer);
	dto.setPwd(pwd);
	dto.setEmail(email);
	dto.setTitle(title);
	dto.setTag(tag);
	dto.setContent(content);
	
	int rowCount =  this.service.insertService(dto);
	
	if( rowCount == 1) {
		System.out.println("> 새 게시글 쓰기 완료!!!");
		// 게시글 목록 페이지 이동
	}		
	일시정지();
}

private tatic void 일시정지() {
	System.out.println("\t\t 엔터치면 계속합니다.");
	try {
		System.in.read();
 		System.in.skip( System.in.available() ); // 13, 10
	} catch (IOException e) { 
		e.printStackTrace();
	} // try
}

[2. 목록보기]

1) DAO Impl

@Override
public ArrayList<BoardDTO> select(int currentPage, int numberPerPage) throws SQLException {

	ArrayList<BoardDTO> list = null;
	BoardDTO dto = null; 

	int begin = (currentPage-1)*numberPerPage + 1;
	int end = begin + numberPerPage -1;

	String sql = 
			" SELECT b.* "
				+ " FROM (  "
				+ "        SELECT ROWNUM no, t.* "
				+ "        FROM (  "
				+ "                SELECT seq, writer, email, title, readed, writedate "
				+ "                FROM tbl_cstvsboard "
				+ "                ORDER BY seq DESC "
				+ "              ) t "
				+ "     ) b "
				+ " WHERE b.no BETWEEN ? AND ?";

	this.pstmt = this.conn.prepareStatement(sql);

	// BETWEEN ? AND ?"
	this.pstmt.setInt(1, begin);
	this.pstmt.setInt(2, end);

	this.rs =  this.pstmt.executeQuery();

	//		int no, seq, readed;
	//		String  writer, email, title;
	//		Date writedate;

	if ( this.rs.next()) {
		list = new ArrayList<BoardDTO>();

		do {
			dto = new BoardDTO();
			dto.setSeq(this.rs.getInt("seq"));
			dto.setReaded(this.rs.getInt("readed"));
			dto.setWriter( this.rs.getString("writer"));
			dto.setEmail( this.rs.getString("email"));
			dto.setTitle( this.rs.getString("title"));
			dto.setWritedate(this.rs.getDate("writedate"));
			list.add(dto);
		} while ( this.rs.next() );
	} // if

	this.rs.close(); // 
	this.pstmt.close();

	return  list;  // 게시글이 존재하지 않으면  list = null ..
}

2) DAO Impl 테스트

@Test
void select_test() {
	//fail("Not yet implemented");

	Connection conn = DBConn.getConnection();
	BoardDAOImpl dao = new BoardDAOImpl(conn);

	ArrayList<BoardDTO> list = null;

	try {
		list = dao.select(1, 15);  // 1번페이지,   15개 출력			
		// 출력
		Iterator<BoardDTO> ir = list.iterator();			
		while (ir.hasNext()) {
			BoardDTO dto =   ir.next();
			System.out.println( dto );
		}

	} catch (SQLException e) {
		e.printStackTrace();
	}		 
	DBConn.close();
}

3) Service

// 2. 게시글 목록 서비스 메서드
public ArrayList<BoardDTO> selectService( int currentPage, int numberPerPage ){
	ArrayList<BoardDTO> list = null;

	try {
		// 1. 로그 기록 서비스
		System.out.println("> 게시글 목록 -> 로그 기록 작업...");
		// 2.
		list =  this.dao.select(currentPage, numberPerPage);
	} catch (SQLException e) {
		e.printStackTrace();
	}
	return list;
}

4) Service 테스트

5) Controller

private void 목록보기() {
	// 357 INSERT
	// BoardService.selectService()/  BoardDAO.select()
	System.out.print("> 현재 페이지( currentPage) 입력 ? " );  
	this.currentPage = this.scanner.nextInt();
	this.scanner.nextLine(); // 13,10

	ArrayList<BoardDTO> list = this.service.selectService(this.currentPage, this.numberPerPage);

	// M[V]C  - 출력 담당 뷰.
	System.out.println("\t\t\t  게시판");
	System.out.println("--------------------------------------------------------------");
	System.out.printf("%s\t%-40s\t%s\t%-10s\t%s\n", 
			"글번호","글제목","글쓴이","작성일","조회수");
	System.out.println("--------------------------------------------------------------");

	if (  list == null) {
		System.out.println("\t\t 게시글 존재 X ");
	} else {
		Iterator<BoardDTO> ir =list.iterator();
		while (ir.hasNext()) {
			BoardDTO dto =   ir.next();
			System.out.printf("%d\t%-30s  %s\t%-10s\t%d\n",
					dto.getSeq()
					, dto.getTitle()
					, dto.getWriter()
					, dto.getWritedate()
					, dto.getReaded()
					);
		}
	} // if
	System.out.println("--------------------------------------------------------------");
	// System.out.println("\t\t\t [1] 2 3 4 5 6 7 8 9 10 >");  // 페이징블럭
	String pagingBlock = this.service.pagingService(
			   this.currentPage
			   , this.numberPerPage
			   , this.numberOfPageBlock
			);
	System.out.println(pagingBlock);
	System.out.println("--------------------------------------------------------------");
	
	일시정지();
}

[3. 상세보기]

1) DAO Impl

// 3-1. 조회수 증가
// 단위 테스트
@Override
public int increaseReaded(int seq) throws SQLException {
	String sql =   "UPDATE tbl_cstvsboard "
			+ "SET readed = readed + 1 "
			+ "WHERE seq = ? ";
	this.pstmt = this.conn.prepareStatement(sql);
	// ?
	this.pstmt.setInt(1, seq);
	int rowCount = this.pstmt.executeUpdate();		
	this.pstmt.close();		
	return rowCount;
}

// 3-2. 해당 게시글 정보 반환
@Override
public BoardDTO view(int seq) throws SQLException {
	
    String sql = " SELECT seq, writer, email, title, readed, writedate, content "
			+ " FROM tbl_cstvsboard "
			+ " WHERE seq = ? ";
	this.pstmt = this.conn.prepareStatement(sql);
	// ?
	this.pstmt.setInt(1, seq);
	this.rs = this.pstmt.executeQuery()	;

	BoardDTO dto = null;

	if( rs.next() ) {

		dto = new BoardDTO();
		dto.setSeq( rs.getInt("seq") );
		dto.setWriter(rs.getString("writer"));
		dto.setEmail(rs.getString("email"));
		dto.setTitle(rs.getString("title"));
		dto.setReaded( rs.getInt("readed") );
		dto.setWritedate( rs.getDate("writedate"));
		dto.setContent(rs.getString("content"));			

	} // if

	this.pstmt.close();
	this.rs.close();

	// 해당 게시글이 없는 경우는 dto = null
	return dto;
}

2) DAO Impl 테스트

@Test
void increaseReaded_test() {
	//fail("Not yet implemented");
	Connection conn = DBConn.getConnection();
	BoardDAOImpl dao = new BoardDAOImpl(conn); 

	try {
		int rowCount = dao.increaseReaded( 363 );
		System.out.println( rowCount );
	} catch (SQLException e) {
		e.printStackTrace();
	}		 
	DBConn.close();
}

3) Service

// 3. 게시글 상세 보기 서비스 메서드 
public BoardDTO viewService(int seq) {
	BoardDTO dto = null;

	try {
		// 1. 로그 기록 서비스
		System.out.println("> 게시글 상세보기 -> 로그 기록 작업...");
		// 2. 조회수 증가
		this.dao.increaseReaded(seq);
		// 3. 
		dto = this.dao. view(seq);			 
	} catch (SQLException e) {			 
		e.printStackTrace();
	}
	return dto;
}

4) Service 테스트

5) Controller

private void 상세보기() {
	System.out.print("> 게시글 번호(seq) 입력 ? " );  
	int seq = this.scanner.nextInt();
	this.scanner.nextLine(); // 13,10
		
	BoardDTO dto = this.service.viewService(seq);
	if( dto == null ) {
	    System.out.println("> 해당 게시글 존재 X ");
	    return ;
	}
		
	//
	System.out.println("\tㄱ. 글번호 : " + seq );
	System.out.println("\tㄴ. 작성자 : " + dto.getWriter() );
	System.out.println("\tㄷ. 조회수 : " + dto.getReaded() );
	System.out.println("\tㄹ. 글제목 : " + dto.getTitle() );
	System.out.println("\tㅁ. 글내용 : " + dto.getContent() );
	System.out.println("\tㅂ. 작성일 : " + dto.getWritedate() );
		
	System.out.println("\t\n [수정] [삭제] [목록(home)]");
		
	일시정지();
}

[4. 수정하기]

1) DAO Impl

@Override
public int update(BoardDTO dto) throws SQLException {

	String sql = "UPDATE  tbl_cstvsboard  "
			+ " SET email=?, title=?, content=? "
			+ " WHERE seq =  ? ";		

	this.pstmt = this.conn.prepareStatement(sql);

	this.pstmt.setString(1, dto.getEmail());
	this.pstmt.setString(2, dto.getTitle());
	this.pstmt.setString(3, dto.getContent());
	this.pstmt.setInt(4, dto.getSeq());

	int rowCount   = this.pstmt.executeUpdate();		

	this.pstmt.close();		
	return rowCount;
}

2) DAO Impl 테스트

3) Service

// seq, email, title, content
public int updateService(BoardDTO dto) {		
	int rowCount = 0;

	try {
		// 1. 로그 기록
		// 2.
		// 3. 
		rowCount =  this.dao.update( dto );

	} catch (SQLException e) { 
		e.printStackTrace();
	}
	return rowCount;
}

4) Service 테스트

5) Controller

private void 수정하기() {
	System.out.print("> 수정할 글번호(seq), 이메일, 제목, 내용 입력 ? ");
	int seq = this.scanner.nextInt();
	// 수정할 글 번호의 게시글 정보 출력..
	BoardDTO dto = this.service.viewService(seq);
	if( dto == null ) {
	    System.out.println("> 해당 게시글 존재 X ");
	    return ;
	} 
	System.out.println("\tㄱ. 글번호 : " + seq );
	System.out.println("\tㄴ. 작성자 : " + dto.getWriter() );
	System.out.println("\tㄷ. 조회수 : " + dto.getReaded() );
	System.out.println("\tㄹ. 글제목 : " + dto.getTitle() );
	System.out.println("\tㅁ. 글내용 : " + dto.getContent() );
	// 수정일, 작성일
	System.out.println("\tㅂ. 작성일 : " + dto.getWritedate() );		
	System.out.println("\t\n [수정] [삭제] [목록(home)]");
	
	// 이메일/제목/내용 만 수정.
	String email = this.scanner.next();
	String title = this.scanner.next();
	String content = this.scanner.next();
		
	this.scanner.nextLine(); // 13.10
	
	// this.service.updateService( seq, email , title, cotent);
	
	dto = new BoardDTO();
	dto.setSeq(seq);
	dto.setEmail(email);
	dto.setTitle(title);
	dto.setContent(content);
	int rowCount = this.service.updateService( dto);
	
	if( rowCount == 1 ) {
		System.out.printf("> 글번호( %d ) 게시글 수정 완료!!!\n ", seq);
	}		
	일시정지();
}

[5. 삭제하기]

1) DAO Impl

// 4. 삭제
@Override
public String getOriginalPwd(int seq) throws SQLException {
	String pwd = null;
	String sql = "SELECT pwd "
			+ " FROM tbl_cstvsboard "
			+ " WHERE seq =  ? ";

	this.pstmt = this.conn.prepareStatement(sql); 
	this.pstmt.setInt(1, seq);
	this.rs = this.pstmt.executeQuery()	;		
	if( rs.next() ) {
		pwd = rs.getString("pwd");
	}		
	this.pstmt.close();
	this.rs.close();
	return pwd;
}

@Override
public int delete(int seq) throws SQLException {      
	String sql = "DELETE FROM  tbl_cstvsboard  " 
			+ " WHERE seq =  ? ";		
	this.pstmt = this.conn.prepareStatement(sql); 
	this.pstmt.setInt(1, seq);
	int rowCount   = this.pstmt.executeUpdate();		
	this.pstmt.close();		
	return rowCount;
}

2) DAO Impl 테스트

3) Service

public int deleteService(int seq, String pwd) {
	int result = 0;

	try {
		// 저장 프로시저
		// 1. 
		String originalPwd = this.dao.getOriginalPwd(seq);
		// 
		if(  originalPwd.equals( pwd ) ) {
			// 2.
			result =  this.dao.delete( seq );
		}else {
			result = -1;
		}
	} catch (SQLException e) { 
		e.printStackTrace();
	}
	return result;
}

4) Service 테스트

5) Controller

private void 삭제하기() {
	// 1. 삭제할 글번호(seq)  입력 
	// 2. 게시글 비밀번호 입력
	System.out.print("> 삭제할 글번호(seq), 비밀번호(pwd) 입력 ? ");
	int seq = this.scanner.nextInt();
	String pwd = this.scanner.next();		
	this.scanner.nextLine(); // 13,10
		
	//   -1(비밀번호 틀린 경우 )    1(삭제 성공 )   0(삭제 실패 )
	int result = this.service.deleteService(seq, pwd);
		
	if(result == -1 ) {
		System.out.println("> 비밀번호가 틀립니다. ");
	}else if( result == 1 ){
		System.out.println("> 게시글 삭제 완료!!!");
	}else if( result == 0 ) {
		System.out.println("> 게시글 삭제 실패!!!");
	}
	일시정지();
}

[6. 검색하기]

1) DAO Impl

// 한 페이지 : 10 출력  numberPerPagge
// 현재  페이지 : 1       currentPage
// 검색된 결과 : 35 행 -> 1번 페이지 10개 출력

@Override
public ArrayList<BoardDTO> search( int currentPage, int numberPerPage, int searchCondition, String searchWord) throws SQLException {

	ArrayList<BoardDTO> list = null;
	BoardDTO dto = null; 

	int begin = ( currentPage-1)*numberPerPage + 1;
	int end = begin + numberPerPage -1;

	String sql = 
			" SELECT b.* "
				+ " FROM (  "
				+ "        SELECT ROWNUM no, t.* "
				+ "        FROM (  "
				+ "                SELECT seq, writer, email, title, readed, writedate "
				+ "                FROM tbl_cstvsboard ";

	switch (searchCondition) {
	case 1: // 제목
		sql += " WHERE REGEXP_LIKE( title , ? , 'i' ) ";
		break;
	case 2:  // 내용
		sql += " WHERE REGEXP_LIKE( content , ? , 'i' ) ";
		break;
	case 3: // 작성자
		sql += " WHERE REGEXP_LIKE( writer , ? , 'i' ) ";
		break;
	case 4:  // 제목 + 내용
		sql += " WHERE REGEXP_LIKE( title , ? , 'i' )  OR REGEXP_LIKE( content , ? , 'i' )  ";
		break;									
	}		
	sql +=  "                ORDER BY seq DESC "
			+ "              ) t "
			+ "     ) b "
			+ " WHERE b.no BETWEEN ? AND ?";
	this.pstmt = this.conn.prepareStatement(sql);

	this.pstmt.setString(1,  searchWord );
	if ( searchCondition == 4) {
		this.pstmt.setString(2,  searchWord );
		this.pstmt.setInt(3, begin);
		this.pstmt.setInt(4, end);
	} else {
		// BETWEEN ? AND ?"
		this.pstmt.setInt(2, begin);
		this.pstmt.setInt(3, end);
	}

	this.rs =  this.pstmt.executeQuery();

	//		int no, seq, readed;
	//		String  writer, email, title;
	//		Date writedate;

	if ( this.rs.next()) {
		list = new ArrayList<BoardDTO>();

		do {
			dto = new BoardDTO();
			dto.setSeq(this.rs.getInt("seq"));
			dto.setReaded(this.rs.getInt("readed"));
			dto.setWriter( this.rs.getString("writer"));
			dto.setEmail( this.rs.getString("email"));

			if( searchCondition == 1 ) {  // 제목 검색
				String title =	this.rs.getString("title").replace(searchWord, "["+searchWord +"]");
				dto.setTitle( title );
			} else {
				dto.setTitle( this.rs.getString("title"));
			}

			dto.setWritedate(this.rs.getDate("writedate"));
			list.add(dto);
		} while ( this.rs.next() );
	} // if

	this.rs.close(); // 
	this.pstmt.close();

	return  list;  // 게시글이 존재하지 않으면  list = null ..
}

2) DAO Impl 테스트

3) Service

public ArrayList<BoardDTO> searchService( int  currentPage, int numberPerPage,  int searchCondition, String searchWord) {
		
	ArrayList<BoardDTO> list = null;
		
	try {
		// 1. 로그 기록
		// 2.
		// 3. 
		list =  this.dao.search(  currentPage, numberPerPage,  searchCondition,  searchWord);
	} catch (SQLException e) { 
		e.printStackTrace();
	}
	return list;
}

4) Service 테스트

5) Controller

private int currentPage = 1;  // 목록 보고 있는 현재 페이지.
private int numberPerPage = 10; // 한 페이지에 출력할 게시글 수
private int numberOfPageBlock = 10;   // 1 2 3 4 5 6 7 8 9 10

private void 검색하기() {
	// 1. 제목 1 , 작성자 2,  내용 3 , 제목 + 내용  4 검색조건 입력
	// 2. 검색어 입력
		
	System.out.print("> 검색된 현재 페이지( currentPage) 입력 ? " );  
	this.currentPage = this.scanner.nextInt(); 
	int searchCondition;  // 검색조건
	System.out.print("> 검색 조건 : 제목(1) , 내용(2), 작성자(3), 제목+내용(4) 선택  ? ");
	searchCondition = this.scanner.nextInt();
	this.scanner.nextLine();  // 13,10 
	System.out.print("> 검색어 입력 ? ");
	String searchWord = this.scanner.nextLine();		 

	ArrayList<BoardDTO> list = this.service.searchService(
			 currentPage, numberPerPage	, searchCondition, searchWord);
	
    //  목록보기()  코딩 복사 붙이기
	System.out.println("\t\t\t  게시판");
	System.out.println("--------------------------------------------------------------");
	System.out.printf("%s\t%-40s\t%s\t%-10s\t%s\n", 
			"글번호","글제목","글쓴이","작성일","조회수");
	System.out.println("--------------------------------------------------------------");
	
    if (  list == null) {
		System.out.println("\t\t 검색된 게시글 존재 X ");
	} else {
		Iterator<BoardDTO> ir =list.iterator();
		while (ir.hasNext()) {
			BoardDTO dto =   ir.next();
			System.out.printf("%d\t%-30s  %s\t%-10s\t%d\n",
					dto.getSeq()
					, dto.getTitle()
					, dto.getWriter()
					, dto.getWritedate()
					, dto.getReaded()
					);
		}
	} // if
    
	System.out.println("--------------------------------------------------------------");
	// System.out.println("\t\t\t [1] 2 3 4 5 6 7 8 9 10 >");  // 페이징블럭
	// 목록보기의 페이징 처리 부분을 참조하면 수정...
	String pagingBlock = this.service.pagingService(
    	this.currentPage
    	, this.numberPerPage
    	, this.numberOfPageBlock
    	, searchCondition   // 검색조건
    	, searchWord           // 검색어
	);
	System.out.println(pagingBlock);
	System.out.println("--------------------------------------------------------------");
	일시정지();
}

[7. 페이징 처리1 - 목록보기]

1) 총 레코드 수 파악
2) 한 페이지에 몇 개 레코드 수
3) 총 페이지 = 총 레코드 / 한 페이지 레코드 수 (올림)
4) 현재 페이지
5) 페이지 블럭 수 (1~10까지)
6) 시작 블럭 (1)
7) 끝 블럭 (10)
8) prev < 이전, next > 다음 표시 여부

1) DAO Impl

@Override
public int getTotalRecords() throws SQLException {
	int totalRecords = 0;

	String sql = "SELECT COUNT(*) "
			+ " FROM tbl_cstvsboard ";	 

	this.pstmt = this.conn.prepareStatement(sql); 

	this.rs = this.pstmt.executeQuery()	;		
	if( rs.next() ) {
		totalRecords = rs.getInt( 1 );
	}		
	this.pstmt.close();
	this.rs.close();

	return totalRecords;
}

@Override
public int getTotalPages(int numberPerPage) throws SQLException {
	int totalPages = 0;

	String sql = "SELECT CEIL(COUNT(*)/?) "
			+ " FROM tbl_cstvsboard ";	 

	this.pstmt = this.conn.prepareStatement(sql); 
	this.pstmt.setInt(1, numberPerPage) ;
	this.rs = this.pstmt.executeQuery()	;		
	if( rs.next() ) {
		totalPages = rs.getInt( 1 );
	}		
	this.pstmt.close();
	this.rs.close();

	return totalPages;
}

2) DAO Impl 테스트

@Test
void getTotalRecords_test() {
		 
	Connection conn = DBConn.getConnection();
	BoardDAOImpl dao = new BoardDAOImpl(conn); 
		
	try {
		 // int totalRecords = dao.getTotalRecords();		   
		 // System.out.println( totalRecords );
			
		int totalPages = dao.getTotalPages( 10  );
		 System.out.println( totalPages );
			
	} catch (SQLException e) {
		e.printStackTrace();
	}		 
	DBConn.close();
}

3) Service

//  "\t\t\t [1] 2 3 4 5 6 7 8 9 10 >"
public String pagingService(int currentPage, int numberPerPage, int numberOfPageBlock) {
		
	String pagingBlock = "\t\t\t";
		
	try {
		int totalRecords = this.dao.getTotalRecords()  ;
		// int totalPages =   totalRecords / numberPerPage + 1 ;		
		int totalPages =   this.dao.getTotalPages( numberPerPage )  ;
		int begin = ( currentPage - 1)/numberOfPageBlock * numberOfPageBlock + 1;
		int end    = begin + numberOfPageBlock - 1;
		if( end > totalPages )  end = totalPages;
		  
		boolean prev =   begin == 1 ?  false : true;
		boolean next  =  end == totalPages ? false: true;
		
		if(prev)  pagingBlock +=" < " ;
		for (int j = begin; j <= end; j++) {
			 pagingBlock += String.format(  currentPage == j ? " [%d]" : " %d ", j) ;
		}
		if( next )   pagingBlock +=" > " ;
	} catch( SQLException e) {
		e.printStackTrace();
	}	
	return pagingBlock;
}

4) Service 테스트

5) Controller

private void 목록보기() {
	// 357 INSERT
	// BoardService.selectService()/  BoardDAO.select()
	System.out.print("> 현재 페이지( currentPage) 입력 ? " );  
	this.currentPage = this.scanner.nextInt();
	this.scanner.nextLine(); // 13,10

	ArrayList<BoardDTO> list = this.service.selectService(this.currentPage, this.numberPerPage);

	// M[V]C  - 출력 담당 뷰.
	System.out.println("\t\t\t  게시판");
	System.out.println("--------------------------------------------------------------");
	System.out.printf("%s\t%-40s\t%s\t%-10s\t%s\n", 
			"글번호","글제목","글쓴이","작성일","조회수");
	System.out.println("--------------------------------------------------------------");

	if (  list == null) {
		System.out.println("\t\t 게시글 존재 X ");
	} else {
		Iterator<BoardDTO> ir =list.iterator();
		while (ir.hasNext()) {
			BoardDTO dto =   ir.next();
			System.out.printf("%d\t%-30s  %s\t%-10s\t%d\n",
					dto.getSeq()
					, dto.getTitle()
					, dto.getWriter()
					, dto.getWritedate()
					, dto.getReaded()
					);
		}// if

	System.out.println("--------------------------------------------------------------");
	// System.out.println("\t\t\t [1] 2 3 4 5 6 7 8 9 10 >");  // 페이징블럭
	String pagingBlock = this.service.pagingService(
			   this.currentPage
			   , this.numberPerPage
			   , this.numberOfPageBlock
			);
	System.out.println(pagingBlock);
	System.out.println("--------------------------------------------------------------");
	
	일시정지();
}

[7. 페이징 처리2 - 검색결과]

1) DAO Impl

// 검색하기
@Override
public int getTotalRecords(int searchCondition, String searchWord) throws SQLException {
	int totalRecords = 0;

	String sql = "SELECT COUNT(*) "
			+ " FROM tbl_cstvsboard ";	 
	switch (searchCondition) {
	case 1: // 제목
		sql += " WHERE REGEXP_LIKE( title , ? , 'i' ) ";
		break;
	case 2:  // 내용
		sql += " WHERE REGEXP_LIKE( content , ? , 'i' ) ";
		break;
	case 3: // 작성자
		sql += " WHERE REGEXP_LIKE( writer , ? , 'i' ) ";
		break;
	case 4:  // 제목 + 내용
		sql += " WHERE REGEXP_LIKE( title , ? , 'i' )  OR REGEXP_LIKE( content , ? , 'i' )  ";
		break;									
	}		

	this.pstmt = this.conn.prepareStatement(sql); 		
	this.pstmt.setString(1, searchWord);
	if ( searchCondition == 4 ) {
		this.pstmt.setString(2, searchWord);
	}		 
	this.rs = this.pstmt.executeQuery()	;		
	if( rs.next() ) {
		totalRecords = rs.getInt( 1 );
	}		
	this.pstmt.close();
	this.rs.close();
    
	return totalRecords;
}

// 단위 테스트
@Override
public int getTotalPages(int numberPerPage,int searchCondition, String searchWord) throws SQLException {
	int totalPages = 0;

	String sql = "SELECT CEIL(COUNT(*)/?) "
			+ " FROM tbl_cstvsboard ";	 
	switch (searchCondition) {
	case 1: // 제목
		sql += " WHERE REGEXP_LIKE( title , ? , 'i' ) ";
		break;
	case 2:  // 내용
		sql += " WHERE REGEXP_LIKE( content , ? , 'i' ) ";
		break;
	case 3: // 작성자
		sql += " WHERE REGEXP_LIKE( writer , ? , 'i' ) ";
		break;
	case 4:  // 제목 + 내용
		sql += " WHERE REGEXP_LIKE( title , ? , 'i' )  OR REGEXP_LIKE( content , ? , 'i' )  ";
		break;									
	}	

	this.pstmt = this.conn.prepareStatement(sql); 
	this.pstmt.setInt(1, numberPerPage) ;
	this.pstmt.setString(2,  searchWord);
	if( searchCondition == 4 ) this.pstmt.setString(3,  searchWord);   

	this.rs = this.pstmt.executeQuery()	;		
	if( rs.next() ) {
		totalPages = rs.getInt( 1 );
	}		
	this.pstmt.close();
	this.rs.close();
    
	return totalPages;
}

2) DAO Impl 테스트

3) Service

// 검색하기 - 페이징처리 메서드
public String pagingService(int currentPage, int numberPerPage, int numberOfPageBlock
		, int searchCondition,			String searchWord) {
	String pagingBlock = "\t\t\t";
		
	try {
		int totalRecords = this.dao.getTotalRecords(searchCondition, searchWord)  ;
		// int totalPages =   totalRecords / numberPerPage + 1 ;		
		int totalPages =   this.dao.getTotalPages( numberPerPage , searchCondition, searchWord)  ;
		int begin = ( currentPage - 1)/numberOfPageBlock * numberOfPageBlock + 1;
		int end    = begin + numberOfPageBlock - 1;
		if( end > totalPages )  end = totalPages;
				  
		boolean prev =   begin == 1 ?  false : true;
		boolean next  =  end == totalPages ? false: true;
				
		if(prev)  pagingBlock +=" < " ;
		for (int j = begin; j <= end; j++) {
			 pagingBlock += String.format(  currentPage == j ? " [%d]" : " %d ", j) ;
		}
		if( next )   pagingBlock +=" > " ;
	} catch( SQLException e) {
		e.printStackTrace();
	}		
	return pagingBlock;
}

4) Service 테스트

5) Controller

private void 검색하기() {
	// 1. 제목 1 , 작성자 2,  내용 3 , 제목 + 내용  4            검색조건 입력
	// 2. 검색어 입력
		
	System.out.print("> 검색된 현재 페이지( currentPage) 입력 ? " );  
	this.currentPage = this.scanner.nextInt(); 
	int searchCondition;  // 검색조건
	System.out.print("> 검색 조건 : 제목(1) , 내용(2), 작성자(3), 제목+내용(4) 선택  ? ");
	searchCondition = this.scanner.nextInt();
	this.scanner.nextLine();  // 13,10 
	System.out.print("> 검색어 입력 ? ");
	String searchWord = this.scanner.nextLine();		 

	ArrayList<BoardDTO> list = this.service.searchService(
			 currentPage, numberPerPage	, searchCondition, searchWord);

	//  목록보기()  코딩 복사 붙이기
	System.out.println("\t\t\t  게시판");
	System.out.println("--------------------------------------------------------------");
	System.out.printf("%s\t%-40s\t%s\t%-10s\t%s\n", 
			"글번호","글제목","글쓴이","작성일","조회수");
	System.out.println("--------------------------------------------------------------");

	if (  list == null) {
		System.out.println("\t\t 검색된 게시글 존재 X ");
	} else {
		Iterator<BoardDTO> ir =list.iterator();
		while (ir.hasNext()) {
			BoardDTO dto =   ir.next();
			System.out.printf("%d\t%-30s  %s\t%-10s\t%d\n",
					dto.getSeq()
					, dto.getTitle()
					, dto.getWriter()
					, dto.getWritedate()
					, dto.getReaded()
					);
		}
	} // if

	System.out.println("--------------------------------------------------------------");
	// System.out.println("\t\t\t [1] 2 3 4 5 6 7 8 9 10 >");  // 페이징블럭
	// 목록보기의 페이징 처리 부분을 참조하면 수정...
	String pagingBlock = this.service.pagingService(
			   this.currentPage
			   , this.numberPerPage
			   , this.numberOfPageBlock
			   , searchCondition   // 검색조건
			   , searchWord           // 검색어
			);
	System.out.println(pagingBlock);
	System.out.println("--------------------------------------------------------------");
	
	일시정지();
}

계속여부확인 Controller

// days02.Ex02_02.java
private static char _continue = 'y';
private static void 계속여부확인() {
	System.out.print("> 계속 할거냐 ? ");
	try {
		_continue =  (char)System.in.read();
		System.in.skip( System.in.available()  ); // 13, 10 제거
	} catch (IOException e) { 
		e.printStackTrace();
	}
}

저작자표시

'👨‍💻 Web Development > JDBC | JSP | Servlet' 카테고리의 다른 글

[Day1] JSP/Servlet 1 - 개요 (0)	2022.12.06
[Day5] JSP/JDBC 5 - transaction 처리, callable statement, reflection (0)	2022.11.02
[Day3] JSP/JDBC 3 - 사용자 변경, 여러출력방식, linkedhashmap사용 DTO 저장, MVC, JUnit (0)	2022.10.31
[Day2] JSP/JDBC 2 - 동적쿼리, PreparedStatement (0)	2022.10.31
[Day1] JSP/JDBC 1 - 개요, jdbc driver, dto (0)	2022.10.28

[Day4] JSP/JDBC 4 - Board

[Day4] JSP/JDBC 4 [10/31]

게시판 생성

'👨‍💻 Web Development > JDBC | JSP | Servlet' 카테고리의 다른 글

전체 카테고리

블로그 인기글

티스토리툴바

[Day4] JSP/JDBC 4 [10/31]

게시판 생성

'👨‍💻 Web Development > JDBC | JSP | Servlet' 카테고리의 다른 글

전체 카테고리

최근 글

최근댓글

블로그 인기글

티스토리툴바