잡동사니에도 사랑을

[JSP] 게시판 만들기(1) - jQuery 유효성 검사, Connection Pool 사용, 페이징 처리 등 본문

JAVA_EE/JSP

[JSP] 게시판 만들기(1) - jQuery 유효성 검사, Connection Pool 사용, 페이징 처리 등

luvforjunk 2021. 10. 3. 00:24
728x90
반응형

[21.10.01] 게시판 만들기(1) - jQuery 유효성 검사, Connection Pool 사용, 페이징 처리 등(boardJSP)

 

 

게시판을 구현하는 작업을 진행해보았다.

조건은 다음과 같다.

 

 

 

 

작성한 예제를 살펴보면서 내용을 정리해보자..!

 

 

////////boardWriteForm.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
	pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>게시판</title>
<style type="text/css">
div{
	color: red;
	font-size: 8pt;
	font-weight: bold;
}
table {
	border-collapse: collapse;
}
</style>
</head>
<body>
	<h2>글쓰기</h2>
	<form name="boardWriteForm" method="post" action="boardWrite.jsp">
	<table border="1" cellspacing="0" cellpadding="5">
		<tr>
			<td width="100" align="center">제목</td>
			<td><input type="text" name="subject" id="subject" size="50">
				<div id="subjectDiv"></div>
			</td>
		</tr>

		<tr>
			<td width="100" align="center">내용</td>
			<td><textarea name="content" id="content" rows="15" cols="50"></textarea>
				<div id="contentDiv"></div>
			</td>
		</tr>
		<tr>
			<td colspan="2" align="center">
				<input type="button" id="writeBtn" value="글쓰기">
				<input type="reset" value="다시작성">
				<!-- JQuery를 쓸 때는 onclick이 굳이 필요없음 -->
	</table>
	</form>
	
	<script type="text/javascript" src="http://code.jquery.com/jquery-3.6.0.min.js"></script>

	<script type="text/javascript" src="../js/board.js"></script>
</body>
</html>

 

 

////////board.js

$(function () {
	$('#writeBtn').click(function () {
		//초기화
		$('#subjectDiv').empty();
		$('#contentDiv').empty();

		/*//id속성
		
		if ($('#subject').val() == '')
			$('#subjectDiv').html('제목 입력');
		else if 
			($('#content').val() == '')
			$('#contentDiv').html('내용 입력');
		else $('#boardWriteForm').submit();*/
		 


		//name속성
		if ($('input[name=subject]').val() == ''){
			$('#subjectDiv').html('제목 입력');
			$('#subject').focus();
		}
		else if ($('textarea[name=content]').val() == ''){
			$('#contentDiv').html('내용 입력');
			$('#content').focus();
		}
		else
			$('form[name=boardWriteForm]').submit();
	});

});

 

 

////////BoardDTO

package board.bean;

import lombok.Data;

@Data
public class BoardDTO {
	private int seq;
	private String id;
	private String name;
	private String email;
	private String subject;
	private String content;
	private int ref;
	private int lev;
	private int step;
	private int pseq;
	private int reply;
	private int hit;
	private int logtime;
}

/* 
 * 필요한 데이터는 subject와 content밖에 없지만 
 * 꺼내올 때는 리스트 전부를 꺼내온다
*/

 

 

////////BoardWrite

<%@ page language="java" contentType="text/html; charset=UTF-8"
	pageEncoding="UTF-8"%> 
<%@ page import="board.bean.BoardDTO"%>
<%@ page import="board.dao.BoardDAO"%>
<%
//데이터
request.setCharacterEncoding("UTF-8");

String subject = request.getParameter("subject");
// --> Form에서 subject라고 명칭한 애를 불러와라
// 반드시 name속성을 불러와야 한다. id속성(X)
String content = request.getParameter("content");

BoardDTO boardDTO = new BoardDTO();
boardDTO.setId("csj");
boardDTO.setName("csj");
boardDTO.setEmail("csj3104@naver.com");
boardDTO.setSubject(subject);
boardDTO.setContent(content);
// 나머지 값들은 default라 굳이 필요없다

//DB
BoardDAO boardDAO = BoardDAO.getInstance();
boardDAO.write(boardDTO); // DTO를 한꺼번에 묶어서 boardDAO에 보낸다
// ????? = boardDAO.write(boardDTO); 하면 return값이 있는 거지만
// 여기서는 그렇지 않으니 return값이 없다.

//응답
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<h3>글쓰기 성공</h3>
	<br>
	<input type="button" id="ListBtn" value="글목록"
		onclick="location.href='boardList.jsp?pg=1'">
</body>
</html>

*주요 내용

→ String subject = request.getParameter("subject"); 에서 "subject"는 name속성임을 반드시 기억해라!

 

 

 

////////BoardDAO - Write

package board.dao;

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

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import board.bean.BoardDTO;

// 싱글톤
public class BoardDAO {
	private Connection conn; // 오라클에 접근하려고
	private PreparedStatement pstmt; //오라클 명령을 자바에서 수행하려고
	private ResultSet rs; // 셀렉트한 결과물을 저장하려고

	private DataSource ds; // Connection Pool을 사용하기로 했으니,,, 
	// DataSource는 은행원이지 Connection Pool 자체가 아니다

	private static BoardDAO instance = null;

	public static BoardDAO getInstance() {
		if (instance == null) {
			synchronized (BoardDAO.class) {
				instance = new BoardDAO();
			}
		}
		return instance;
	}

	// Connection Pool
	public BoardDAO() {

		try {
			Context ctx = new InitialContext();
			
			// 직접적인 선언이 안된다
			// Naming Service가 뭘까? 
			// 외워야 할 것을 이름으로 대체해주는 것을 Naming Service라고 한다
			// 네이버 123.123.123(IP) 이름과 IP를 연결해주는 것이 DNS서버의 역할이다.
			// lookup이라는 method를 이용해서 lookup("java:comp/env/jdbc/oracle")
			// 괄호 안에 있는 값을 가져와 ds에 전달한다 "java:comp/env/jdbc/oracle" : DataSource Connection Pool
			
			
			ds = (DataSource) ctx.lookup("java:comp/env/jdbc/oracle");

		} catch (NamingException e) {
			e.printStackTrace();
		}
	}

	public void write(BoardDTO boardDTO) {
		// insert into board values() → 테이블의 모든 컬럼(13개)을 빠짐없이 순서대로 기입
		String sql = "insert into board values(seq_board.nextval, ?, ?, ?, ?, ?, seq_board.currval, 0, 0, 0, 0, 0, sysdate)";
		
		/*
		 * 
		// insert into board (컬럼명, 컬럼명, ....) values → 원하는 컬럼(13개)을 순서 상관없이 기입
		 String sql = "insert into board(seq, id, name, email, subject, content, ref)" 
		 			+ "values(seq_board.nextval, ?, ?, ?, ?, ?, seq_board.currval)";
		
		*/	
		
		try {

			conn = ds.getConnection();
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, "csj");
			pstmt.setString(2, "csj");
			pstmt.setString(3, "csj@naver.com");
			pstmt.setString(4, boardDTO.getContent());
			pstmt.setString(5, boardDTO.getSubject());

			pstmt.executeUpdate(); // 실행

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

 

 

////////boardList

<%@ page language="java" contentType="text/html; charset=UTF-8"
	pageEncoding="UTF-8"%>
<%@ page import="board.dao.BoardDAO"%>
<%@ page import="board.bean.BoardDTO"%>
<%@ page import="java.util.List"%>

<%
// 데이터
int pg = Integer.parseInt(request.getParameter("pg"));

//페이징 처리 - 1페이지당 5개씩
int endNum = pg*5;
int startNum = endNum-4;

BoardDAO boardDAO = BoardDAO.getInstance();
List<BoardDTO> list = boardDAO.getBoardList(startNum, endNum);

int totalA = boardDAO.getTotalA();
int totalP = (totalA+4)/5;

%>

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<%if(list != null){ %>
	<table border="1">
		<tr>
			<td width="100" align="center">글번호</td>
			<td width="100" align="center">제목</td>
			<td width="100" align="center">작성자</td>
			<td width="100" align="center">조회수</td>
			<td width="100" align="center">작성일</td>
			
		</tr>

		<%for (BoardDTO boardDTO : list) {%>
		<tr>
			<td width="100" ><%=boardDTO.getSeq() %></td>
			<td><a href="boardView.jsp?seq=<%=boardDTO.getSeq() %>"><%=boardDTO.getSubject() %></a></td>
			<td width="100" align="center"><%=boardDTO.getId() %></td>
			<td width="100" align="center"><%=boardDTO.getHit() %></td>
			<td width="100" align="center"><%=boardDTO.getLogtime() %></td>
		</tr>
	</table>
	<%} // for %>

	<div style="width: 450px; text-align: center;">
	<%for(int i=1; i<=totalP; i++){ %>
		[<a href="boardList.jsp?pg=<%=i %>"><%=i %></a>]
	<%}//for %>
	</div>
<%}//if %>
</body>
</html>

 

 

////////BoardDAO - List

	public List<BoardDTO> getBoardList(int startNum, int endNum) {
		List<BoardDTO> list = new ArrayList<BoardDTO>();
		String sql = "select *" 
				+ " from (select rownum rn, tt.*"
				+ " from (select seq, id, name, email, subject, content"
				+ " ref, lev, step, pseq, reply, hit, to_char(logtime, 'YYYY.MM.DD') aslogtime" + " from board"
				+ " order by seq desc) tt" + " )where rn>=? and rn<=?";
		try {
			conn = ds.getConnection();

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

			rs = pstmt.executeQuery(); // 조건에 맞는 값을 행단위로 싹다 가져옴

			while (rs.next()) {
				BoardDTO boardDTO = new BoardDTO();
				boardDTO.setSeq(rs.getInt("seq"));
				boardDTO.setSubject(rs.getString("subject"));
				boardDTO.setId(rs.getString("Id"));
				boardDTO.setHit(rs.getInt("hit"));
				boardDTO.setLogtime(rs.getString("logtime"));

				list.add(boardDTO);
			} //while
			
		} catch (Exception e) { // try안에 있는 오류에 대한 보험
			e.printStackTrace(); // 그 오류를 Console창에 박박 찍어라
			list = null; // list에 문제가 생겼을 것을 당연하게 생각하고 아예 처음부터 보험을 적용을 하는 것이다.

		} finally { // 오류가 있던 없던 무조건 실행.. 오류가 있던 없던 pstmt를 닫아줘라
			try {
				if (rs != null)
					rs.close();
				if (pstmt != null)
					pstmt.close();
				if (conn != null)
					conn.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return list;
	}

	public int getTotalA() {
		int totalA = 0;
		String sql = "select count(*) from board";

		try {
			conn = ds.getConnection();

			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();

			rs.next();
			totalA = rs.getInt(1); // count(*)는 칼럼명이 아님.
			
		} catch (Exception e) {
			e.printStackTrace();
			
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (pstmt != null)
					pstmt.close();
				if (conn != null)
					conn.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return totalA;
	}

 

 

////////boardView

<%@ page language="java" contentType="text/html; charset=UTF-8"
	pageEncoding="UTF-8"%>
<%@ page import="board.dao.BoardDAO"%>
<%@ page import="board.bean.BoardDTO"%>

<%
int seq = Integer.parseInt(request.getParameter("seq"));

BoardDTO boardDTO = new BoardDTO();

BoardDAO boardDAO = BoardDAO.getInstance();
boardDTO = boardDAO.View(seq);
%>

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<form>
		<table>
			<tr id="subject">
				<td><%=boardDTO.getSubject()%></td>
			</tr>

			<tr>
				<td>글 번호 : <%=boardDTO.getSeq()%></td>
				<td>작성자 : <%=boardDTO.getId()%></td>
				<td>조회수 : <%=boardDTO.getHit()%></td>
			</tr>

			<tr>
				<td><pre><%=boardDTO.getContent()%></pre></td>
			</tr>

		</table>
	</form>

</body>
</html>

 

 

////////BoardDAO - View

public BoardDTO View(int seq) {
		BoardDTO boardDTO = new BoardDTO();
		String sql = "select * from board where seq=?";
		
		try {
			conn = ds.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, seq);
			
			rs = pstmt.executeQuery();
			
			if(rs.next()) {
				boardDTO.setSubject(rs.getString("subject"));
				boardDTO.setSeq(rs.getInt("seq"));
				boardDTO.setId(rs.getString("id"));
				boardDTO.setHit(rs.getInt("hit"));
				boardDTO.setContent(rs.getString("content"));
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
			
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (pstmt != null)
					pstmt.close();
				if (conn != null)
					conn.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return boardDTO;
	}	
}

 

 

////////Context.xml

<?xml version="1.0" encoding="UTF-8"?>
<Context docBase="boardJSP" 
		 path="/boardJSP" 
		 reloadable="true" 
		 source="org.eclipse.jst.jee.server:boardJSP">
		 <Resource name="jdbc/oracle"
		 		   type="javax.sql.DataSource"
			   driverClassName="oracle.jdbc.driver.OracleDriver"
			   url="jdbc:oracle:thin:@localhost:1521:xe"
			   username="c##java"
			   password="bit"
		   	   maxActive="20"
			   maxIdle="3"
		 	   removeAbandoned="true"/>	 
</Context>

 

 

////////Database

CREATE TABLE board(
seq NUMBER NOT NULL, -- 글번호 (시퀀스 객체 이용)
id VARCHAR2(20) NOT NULL, -- 아이디
name VARCHAR2(40) NOT NULL, -- 이름
email VARCHAR2(40), -- 이메일
subject VARCHAR2(255) NOT NULL, -- 제목
content VARCHAR2(4000) NOT NULL, -- 내용 
ref NUMBER NOT NULL, -- 그룹번호
lev NUMBER DEFAULT 0 NOT NULL, -- 단계
step NUMBER DEFAULT 0 NOT NULL, -- 글순서
pseq NUMBER DEFAULT 0 NOT NULL, -- 원글번호
reply NUMBER DEFAULT 0 NOT NULL, -- 답변수
hit NUMBER DEFAULT 0, -- 조회수
logtime DATE DEFAULT SYSDATE
);

-- 시퀀스 객체
CREATE SEQUENCE seq_board  NOCACHE NOCYCLE;
728x90
반응형

'JAVA_EE > JSP' 카테고리의 다른 글

[JSP] 방명록 만들기(2)  (0) 2021.10.02
[JSP] 방명록 만들기(1)  (0) 2021.10.01
'JAVA_EE/JSP' Related Articles more