Oracle 17일차 - 답변형 게시판

Notice

Recent Posts

Recent Comments

Link

« 2024/05 »
일	월	화	수	목	금	토
			1	2	3	4
5	6	7	8	9	10	11
12	13	14	15	16	17	18
19	20	21	22	23	24	25
26	27	28	29	30	31

Tags more

Archives

Today

Total

관리 메뉴

이안의 평일코딩

Oracle 17일차 - 답변형 게시판 본문

Back-end/Oracle

Oracle 17일차 - 답변형 게시판

이안92 2020. 8. 27. 09:51

2020.08.27(목)

input

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<center>
	<h1>GET방식으로 데이터 전송 (output.jsp)</h1>
	<a href="output.jsp?id=admin&pwd=1234&name='홍길동'"=>값 전송</a>
	</center>
</body>
</html>

output

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<center>
		<h1>화면출력</h1>
		<%
		String id=request.getParameter("id");
		String pwd=request.getParameter("pwd");
		String name=request.getParameter("name");
		%>
		<%=id %>
		<br>
		<%=pwd %>
		<br>
		<%=name %>
	</center>
</body>
</html>

input2

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<center>
	<h1>POST로 데이터 보내기</h1>
	<%--
		<form method=post> , ajax
	 --%>
	 <form method=post action="output2.jsp">
	 <table borer=1 width=350>
	 	<tr>
	 		<td width=30% align=right>이름</td>
	 		<td width=70% align=left>
	 			<input type=text name=name size=15><%-- name=admin --%>
	 		</td>
	 	</tr>
	 	<tr>
	 		<td width=30% align=right>성별</td>
	 		<td width=70% align=left>
	 			<input type=radio value="남자" name=sex checked>남자
	 			<input type=radio value="여자" name=sex>여자
	 		</td>
	 	</tr>
	 	<tr>
	 		<td width=30% align=right>지역</td>
	 		<td width=70% align=left>
	 			<select name="loc">
	 				<option>서울</option>
	 				<option>부산</option>
	 				<option>인천</option>
	 				<option>경기</option>
	 				<option>강원</option>
	 			</select>
	 		</td>
	 	</tr>
	 	<tr>
	 		<td width=30% align=right>취미</td>
	 		<td width=70% align=left>
	 			<input type="checkbox" value="등산" name=hobby>등산
	 			<input type="checkbox" value="낚시" name=hobby>낚시
	 			<input type="checkbox" value="게임" name=hobby>게임
	 			<input type="checkbox" value="독서" name=hobby>독서
	 			<input type="checkbox" value="여행" name=hobby>여행
	 		</td>
	 	</tr>
	 	<tr>
	 		<td width=30% align=right>소개</td>
	 		<td width=70% align=left>
	 			<textarea rows="7" cols="30" name=content></textarea>
	 		</td>
	 	</tr>
	 	<tr>
	 		<td colspan=2 align=center>
	 			<button>전송</button>
	 			<%--
	 				<input type=submit>
	 				<button>
	 				<input type=image>
	 			 --%>
	 		</td>
	 	</tr>
	 </table>
	 </form>
	</center>
</body>
</html>

output2

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<center>
		<h1>input2.jsp에서 받은 값</h1>
		<%--
		hobby 여러개 선택 => 동일한 변수명 => 배열 [] => for문으로 받아옴
		 --%>
		<%
			try{
				request.setCharacterEncoding("UTF-8");
			}catch(Exception ex){}
			String name=request.getParameter("name");
			String sex=request.getParameter("sex");
			String loc=request.getParameter("loc");
			String content=request.getParameter("content");
			String[] hobby=request.getParameterValues("hobby");
			// checkbox, select(multi)
		%>
		이름 : <%=name %><br>
		성별 : <%=sex %><br>
		지역 : <%=loc %><br>
		소개 : <%=content %><br>
		취미 :
			<ul>
			 <%
			 	try{
			 	for(String s:hobby){
			 %>
			 	<li><%=s %></li>
			 <%
			 	}
			 }catch(Exception ex){
			%>
				<font color=red>취미가 없습니다</font>
			<%
			 }
			 %>
			</ul>
			IP : <%= request.getRemoteAddr() %><br>
			요청경로 : <%=request.getRequestURI() %><br>
			전체 주소 : <%=request.getRequestURL() %><br>
			컨텍스트 : <%=request.getContextPath() %><br>
			요청방식 : <%=request.getMethod() %><br>
			인코딩 : <%=request.getCharacterEncoding() %><br>
			서버주소 : <%=request.getServerName() %><br>
			서버포트: <%=request.getServerPort() %>
	</center>
</body>
</html>

답변형 게시판 (묻고답하기)

CREATE TABLE replyBoard(
    no NUMBER,
    name VARCHAR2(34) CONSTRAINT rb_name_nn NOT NULL,
    subject VARCHAR2(1000) CONSTRAINT rb_subject_nn NOT NULL,
    content CLOB CONSTRAINT rb_content_nn NOT NULL,
    pwd VARCHAR2(10) CONSTRAINT rb_pwd_nn NOT NULL,
    regdate DATE DEFAULT SYSDATE,
    hit NUMBER DEFAULT 0,
    group_id NUMBER,
    group_step NUMBER DEFAULT 0,
    group_tab NUMBER DEFAULT 0,
    root NUMBER DEFAULT 0,
    depth NUMBER DEFAULT 0,
    CONSTRAINT rb_no_pk PRIMARY KEY(no)
);
-- 자동증가는 시퀀스 사용
CREATE SEQUENCE rb_no_seq
    START WITH 1
    INCREMENT BY 1
    NOCACHE
    NOCYCLE;

	no	gi(답변별)	gs(출력순서)	gt	root	depth
AAAAA	1	1	0	0	0	2
-BBBBB	2	1	1	1	1	1
--CCCCC	3	1	2	2	2	0
-DDDDD	4	1	3	1	1	0
EEEEE	5	2	0	0	0	1
-TTTTT	6	2	1	1	5	0

컬럼

1. no ~ hit

2. group_id NUMBER, (질문별로 그룹핑)

3. group_step NUMBER DEFAULT 0, (그룹 내에서의 순서)

4. group_tab NUMBER DEFAULT 0, (답변/답변의 답변여부)

5. root NUMBER DEFAULT 0, (질문에 대한 뿌리글번호(no))

6. depth NUMBER DEFAULT 0, (답변갯수)

root(누구의 답변인지), depth는 삭제형으로 사용

실무 : 게시판 기능

= 답변

= 댓글

= 자료실

= 갤러리

브라우저 localhost/main/main.jsp

톰캣 - 웹서버=>사용자로부터 요청,응답

- 컨테이너=>main.jsp html변환/ JSP,Servlet실행 HTML

INSERT INTO replyBoard(no, name, subject, content, pwd, group_id)
VALUES(rb_no_seq.nextval,'홍길동','답변형 게시판 만들기', '답변형 게시판 만들기', '1234', 1);
INSERT INTO replyBoard(no, name, subject, content, pwd, group_id)
VALUES(rb_no_seq.nextval,'홍길동','답변형 게시판 만들기', '답변형 게시판 만들기', '1234', 2);
INSERT INTO replyBoard(no, name, subject, content, pwd, group_id)
VALUES(rb_no_seq.nextval,'홍길동','답변형 게시판 만들기', '답변형 게시판 만들기', '1234', 3);
INSERT INTO replyBoard(no, name, subject, content, pwd, group_id)
VALUES(rb_no_seq.nextval,'홍길동','답변형 게시판 만들기', '답변형 게시판 만들기', '1234', 4);
INSERT INTO replyBoard(no, name, subject, content, pwd, group_id)
VALUES(rb_no_seq.nextval,'홍길동','답변형 게시판 만들기', '답변형 게시판 만들기', '1234', 5);
COMMIT;

INSERT INTO replyBoard(no,name,subject,content,pwd,group_id,group_step,group_tab)
VALUES(rb_no_seq.nextval, '심청이', '답변입니다', '답변입니다', '1234', 5, 1, 1);
INSERT INTO replyBoard(no,name,subject,content,pwd,group_id,group_step,group_tab)
VALUES(rb_no_seq.nextval, '심청이', '답변입니다', '답변입니다', '1234', 5, 2, 2);
INSERT INTO replyBoard(no,name,subject,content,pwd,group_id,group_step,group_tab)
VALUES(rb_no_seq.nextval, '심청이', '답변입니다', '답변입니다', '1234', 4, 1, 1);
INSERT INTO replyBoard(no,name,subject,content,pwd,group_id,group_step,group_tab)
VALUES(rb_no_seq.nextval, '심청이', '답변입니다', '답변입니다', '1234', 4, 2, 2);
INSERT INTO replyBoard(no,name,subject,content,pwd,group_id,group_step,group_tab)
VALUES(rb_no_seq.nextval, '심청이', '답변입니다', '답변입니다', '1234', 3, 1, 1);
INSERT INTO replyBoard(no,name,subject,content,pwd,group_id,group_step,group_tab)
VALUES(rb_no_seq.nextval, '심청이', '답변입니다', '답변입니다', '1234', 3, 2, 2);

SELECT no,subject,name,regdate,hit,group_tab,num
FROM (SELECT no,subject,name,regdate,hit,group_tab,rownum as num
FROM (SELECT no,subject,name,regdate,hit,group_tab
FROM replyBoard ORDER BY group_id DESC,group_step ASC))
WHERE num BETWEEN 1 AND 10;

ReplyBoardVO

package com.sist.dao;
import java.util.*;
public class ReplyBoardVO {
	private int no;
	private String name;
	private String content;
	private String pwd;
	private String subject;
	private Date regdate;
	private int hit;
	private int group_id;
	private int group_step;
	private int group_tab;
	private int root;
	private int depth;
	
	public String getSubject() {
		return subject;
	}
	public void setSubject(String subject) {
		this.subject = subject;
	}
	public int getNo() {
		return no;
	}
	public void setNo(int no) {
		this.no = no;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getContent() {
		return content;
	}
	public void setContent(String content) {
		this.content = content;
	}
	public String getPwd() {
		return pwd;
	}
	public void setPwd(String pwd) {
		this.pwd = pwd;
	}
	public Date getRegdate() {
		return regdate;
	}
	public void setRegdate(Date regdate) {
		this.regdate = regdate;
	}
	public int getHit() {
		return hit;
	}
	public void setHit(int hit) {
		this.hit = hit;
	}
	public int getGroup_id() {
		return group_id;
	}
	public void setGroup_id(int group_id) {
		this.group_id = group_id;
	}
	public int getGroup_step() {
		return group_step;
	}
	public void setGroup_step(int group_step) {
		this.group_step = group_step;
	}
	public int getGroup_tab() {
		return group_tab;
	}
	public void setGroup_tab(int group_tab) {
		this.group_tab = group_tab;
	}
	public int getRoot() {
		return root;
	}
	public void setRoot(int root) {
		this.root = root;
	}
	public int getDepth() {
		return depth;
	}
	public void setDepth(int depth) {
		this.depth = depth;
	}

}

ReplyBoardDAO

package com.sist.dao;
import java.util.*;
import java.sql.*;
public class ReplyBoardDAO {
	private Connection conn;
	private PreparedStatement ps;
	private final String URL="jdbc:oracle:thin:@localhost:1521:XE";
	public ReplyBoardDAO() {
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
		}catch(Exception ex) {}
	}
	public void getConnection() {
		try {
			conn=DriverManager.getConnection(URL,"hr","happy");
		}catch(Exception ex) {}
	}

	public void disConnection() {
		try {
			if(ps!=null) ps.close();
			if(conn!=null) conn.close();
		}catch(Exception ex) {}
	}
	// 2001 => 19년전 코딩 => MVC/MyBatis // 자바(Model) <- Controller(Spring) -> HTML(View)
	//1. 목록 출력 => 페이지 나누기
	public ArrayList<ReplyBoardVO> boardListData(int page){
		ArrayList<ReplyBoardVO> list=
				new ArrayList<ReplyBoardVO>();
		try {
			getConnection();
			String sql="SELECT no,subject,name,regdate,hit,group_tab,num "
					+"FROM (SELECT no,subject,name,regdate,hit,group_tab,rownum as num "
					+"FROM (SELECT no,subject,name,regdate,hit,group_tab "
					+"FROM replyBoard ORDER BY group_id DESC,group_step ASC)) "
					+"WHERE num BETWEEN ? AND ?";
			/*
			 * 					group_id	group_step
			 * 	AAAAA				2			0
			 *   ->BBBBB			2			1
			 * 	  ->CCCCC			2			2
			 * 	 ->KKKKK			2			3
			 *  DDDDD				1			0
			 */
			ps=conn.prepareStatement(sql);
			int rowSize=10;
			int start=(rowSize*page)-(rowSize-1);
			int end=rowSize*page;
			
			// ? 에 값 채우기
			ps.setInt(1, start);
			ps.setInt(2, end);
			
			// 실행후에 결과 값 읽기
			ResultSet rs = ps.executeQuery();
			while(rs.next()) {
				ReplyBoardVO vo = new ReplyBoardVO();
				vo.setNo(rs.getInt(1));
				vo.setSubject(rs.getString(2));
				vo.setName(rs.getString(3));
				vo.setRegdate(rs.getDate(4));
				vo.setHit(rs.getInt(5));
				vo.setGroup_tab(rs.getInt(6));
				list.add(vo);
			}
			rs.close();
		}catch(Exception ex) {
			System.out.println(ex.getMessage());
		}finally {
			disConnection();
		}
		return list;
	}
	//1-1전체 갯수 //게시판번호 순서대로 출력 (중간 비거나 건너뛰는거 방지) count 순차적감소 -- count--
	public int boardRowCount() {
		int count=0;
		try {
			getConnection();
			String sql="SELECT COUNT(*) FROM replyBoard";
			ps=conn.prepareStatement(sql);
			ResultSet rs=ps.executeQuery();
			rs.next();
			count=rs.getInt(1);
			rs.close();
			
		}catch(Exception ex) {
			System.out.println(ex.getMessage());
		}finally {
			disConnection();
		}
		return count;
	}
	//2. 상세보기
	//3. 새글등록 => INSERT
	public void boardInsert(ReplyBoardVO vo) {
		try {
			getConnection();
			String sql="INSERT INTO replyBoard(no,name,subject,content,pwd,group_id) "
					+ "VALUES(rb_no_seq.nextval,?,?,?,?,"
					+ "(SELECT NVL(MAX(group_id)+1,1) FROM replyBoard))";
			ps=conn.prepareStatement(sql);
			ps.setString(1, vo.getName());
			ps.setString(2, vo.getSubject());
			ps.setString(3, vo.getContent());
			ps.setString(4, vo.getPwd());

			ps.executeUpdate();
			
		}catch(Exception ex) {
			System.out.println(ex.getMessage());
		}finally {
			disConnection();
		}
	}
	//4. 답변하기 => SQL => 합쳐서 처리 (서브쿼리)
	//5. 수정하기 => UPDATE
	//6. 삭제하기 => SQL => 합쳐서 처리 (서브쿼리)
	//7. 찾기 => LIKE, REGEXP_LIKE
}

list.jsp

<%@page import="java.text.SimpleDateFormat"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8" import="java.util.*, com.sist.dao.*"%>
<%
	String strPage=request.getParameter("page"); //사용자가 페이지 선택시 변경
	if(strPage==null) // 첫페이지는 페이지선택이 불가능 => 1페이지를 디폴트로 지정
			strPage="1";
	int curpage=Integer.parseInt(strPage); //현재페이지
	// 페이지에 해당되는 데이터를 읽기
	ReplyBoardDAO dao = new ReplyBoardDAO();
	// 10개씩
	ArrayList<ReplyBoardVO> list = dao.boardListData(curpage);
	// 출력
	int count=dao.boardRowCount();
	// 11/10.0 => 1.1 => 2
	int totalpage=(int)(Math.ceil(count/10.0));
	count=count-((curpage*10)-10);
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<link rel="stylesheet" href="css/table.css">
</head>
<body>
	<center>
		<h1>묻고답하기</h1>
		<table class="table_content" width=700>
		  <tr>
		  	<td>
		  		<a href="insert.jsp">등록</a>
		  	</td>
		  	</tr>
		  	</table>
		  	<table class="table_content" width=700>
		  	<tr height=30>
		   <th width=10%>번호</th>
		   <th width=45%>제목</th>
		   <th width=15%>이름</th>
		   <th width=20%>작성일</th>
		   <th width=10%>조회수</th>
		  </tr>
		 <%
		 	for(ReplyBoardVO vo : list){
		 %>
		  <tr>
		   <td width=10% align=center><%=count-- %></td>
		   <td width=45%>
		   <%
		   	if(vo.getGroup_tab()>0){
		   		for(int i=0; i<vo.getGroup_tab();i++){
		   			out.println("&nbsp;&nbsp;");
		   		}
		   %>
		   		<img src="image/icon_reply.gif" style="border:none">
		   <%
		   	}
		   %>
		   <%=vo.getSubject() %>
		   &nbsp;
		   <%
		   		Date date = new Date();
		   		SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
		   		String today=sdf.format(date);
		   		String dbday=vo.getRegdate().toString();
		   		
		   		if(today.equals(dbday)){
		   %>
		   		<sup><img src="image/new.gif" style="border:none"></sup>
		   <%
		   		}
		   %>
		   </td>
		   <td width=15% align=center><%=vo.getName () %></td>
		   <td width=20% align=center><%=vo.getRegdate().toString() %></td>
		   <td width=10% align=center><%=vo.getHit() %></td>
		  </tr>
		 <%
		 	}
		 %>
		</table>
		<table class="table_content" width=700>
			<tr>
				<td align=left>
				Search:
				<%--
					WHERE name LIKE '%홈%'
				 --%>
				<select name=fd>
					<option value="name">이름</option>
					<option value="subject">제목</option>
					<option value="content">내용</option>
				</select>
				<input type=text name=ss size=10>
				<input type=submit value="찾기">
				</td>
				<td align=right>
				<a href="list.jsp?page=<%= curpage>1?curpage-1:curpage%>">이전</a>
				 <%=curpage %> page / <%=totalpage %> pages
				<a href="list.jsp?page=<%=curpage<totalpage?curpage+1:curpage%>">다음</a>
				</td>
				
			</tr>
		</table>
	</center>
</body>
</html>

insert.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>글쓰기</title>
<link rel="stylesheet" href="css/table.css">
</head>
<body>
<%--
submit => action에 등록된 파일로 입력한 데이터 전송
	= <input> <textarea> <select>
--%>
	<center>
		<h1>글쓰기</h1>
		<form method=post action=insert_ok.jsp>
		<table class="table_content" width=500>
			<tr>
				<th width=15% align=right>이름</th>
				<td width=85%>
				<input type=text name=name size=15 required>
				</td>
			</tr>
			<tr>
				<th width=15% align=right>제목</th>
				<td width=85%>
				<input type=text name=subject size=45 required>
				</td>
			</tr>
			<tr>
				<th width=15% align=right>내용</th>
				<td width=85%>
				<textarea rows="7" cols="55" name="content" required></textarea>
				</td>
			</tr>
			<tr>
				<th width=15% align=right>비밀번호</th>
				<td width=85%>
				<input type=password name=pwd size=10 required>
				</td>
			</tr>
			<tr>
				<td colspan="2" align=center>
				<input type=submit value="글쓰기">
				<input type=button value="취소" onclick="javascript:history.back()">
				</td>
			</tr>
		</table>
	</center>
	</form>
</body>
</html>

insert_ok.jsp

<%@ page import="com.sist.dao.ReplyBoardDAO"%>
<%@ page import="com.sist.dao.ReplyBoardVO"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
    
<%
	// 값을 받아서 오라클에 추가
	try{
		request.setCharacterEncoding("UTF-8");
		/*
			한글 : EUC-KR , UTF-8
				  EUC-KR => EUC-KR
				  UTF-8 => UTF-8
				  EUC-KR => UTF-8 (O)
				  UTF-8 => EUC-KR (X)
				  => 10 (UTF-8)
		*/
	}catch(Exception ex){}
	// 목록으로 이동
	String name=request.getParameter("name");
	String subject=request.getParameter("subject");
	String content=request.getParameter("content");
	String pwd=request.getParameter("pwd");
	
	ReplyBoardVO vo = new ReplyBoardVO();
	vo.setName(name);
	vo.setSubject(subject);
	vo.setContent(content);
	vo.setPwd(pwd);
	// DAO => INSERT
	ReplyBoardDAO dao = new ReplyBoardDAO();
	// dao => insert
	dao.boardInsert(vo);
	response.sendRedirect("list.jsp");
	
%>

'Back-end > Oracle' 카테고리의 다른 글

Oracle 20일차 - Cursor, Function (0)	2020.11.11
Oracle 19일차 - 서브쿼리 (0)	2020.11.10
Oracle 16일차 - 지니뮤직, 멜론뮤직 출력 (0)	2020.08.26
Oracle 15일차 - 인라인뷰 (0)	2020.08.25
Oracle 11~13일차 - 테이블 / 영화사이트 제작 (0)	2020.08.19

'Back-end/Oracle' Related Articles

Comments

이안의 평일코딩

Oracle 17일차 - 답변형 게시판 본문

Oracle 17일차 - 답변형 게시판

2020.08.27(목)

'Back-end > Oracle' 카테고리의 다른 글

티스토리툴바