[55일차]2015.09.23.수, Q&A만들기

board.QnaDTO

board.QnaDAO

1.새글입력 메서드명 : setContent()

글쓰기 : writeForm.jsp

답글쓰기 : rewriteForm.jsp

글리스트 : listView.jsp

뷰페이지 : contentView.jsp

table : qnaBoard

글번호 : num (primary key)

글제목 : title

글내용 : content

원본글 : ref(원본글이면 원본글의 num, 답변글이면,? 원본글의 num?)

답글순서 : step ( 최신글이면 1, ++ )

들여쓰기 : depth(원본글의 답변이면 1, 답변글의 답변글이면 2)

******************************************************************

Q&A 테이블

create table qnaboard(

num number(6) primary key,

title varchar2(2000),

content varchar2(3000),

ref number(6),

step numbeR(6),

depth number(6)

)

******************************************************************

QnaDTO.java

package board;

public class QnaDTO {

private int num;

private String title;

private String content;

private int ref;

private int step;

private int depth;

public int getNum() {

return num;

}

public void setNum(int num) {

this.num = num;

}

public String getTitle() {

return title;

}

public void setTitle(String title) {

this.title = title;

}

public String getContent() {

return content;

}

public void setContent(String content) {

this.content = content;

}

public int getRef() {

return ref;

}

public void setRef(int ref) {

this.ref = ref;

}

public int getStep() {

return step;

}

public void setStep(int step) {

this.step = step;

}

public int getDepth() {

return depth;

}

public void setDepth(int depth) {

this.depth = depth;

}

}

******************************************************************

package board;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.ArrayList;

import com.sun.corba.se.spi.orbutil.fsm.State;

public class QnaDAO {

private String user = "user02";

private String password = "user02";

private String url = "jdbc:oracle:thin:@192.168.116.128:1521:xe";

private String dirver = "oracle.jdbc.driver.OracleDriver";

private Connection con;

private PreparedStatement st;

private ResultSet rs;

public QnaDAO(){

try {

Class.forName(dirver);

con = DriverManager.getConnection(url, user, password);

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

/*

//setDummy 메소드 실행

public static void main(String[] args) {

QnaDAO q = new QnaDAO();

q.setDummy();

}

//Dummy data 만드는 메서드

public void setDummy(){

String sql = "insert into qnaboard values(?,?,?,?,0,0)";

try {

st = con.prepareStatement(sql);

for(int i=8 ; i<120 ; i++){

st.setInt(1, i);

st.setString(2, "title"+i);

st.setString(3, "content"+i);

st.setInt(4, i);

st.executeQuery();

}

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

*/

//총 게시물 수를 받아오는 메서드

public int getCount(){

   int result = 0;

String sql = "select count(num) from qnaboard";

try {

st = con.prepareStatement(sql);

rs = st.executeQuery();

if(rs.next()){

result = rs.getInt(1);

}

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

return result;

}

//리스트 받아오기

public ArrayList<QnaDTO> getList(int curpage){

int start = 0;

int last = 0;

start = (curpage-1)*10+1;

last = curpage*10;

String sql ="select * from"

+"(select rownum R, A.* from" 

+"(select * from qnaboard order by num desc) A)"

+ "where R between ? and ?";

ArrayList<QnaDTO> ar = new ArrayList<>();

try {

st = con.prepareStatement(sql);

st.setInt(1, 1);

st.setInt(2, 10);

rs = st.executeQuery();

while(rs.next()){

QnaDTO q = new QnaDTO();

q.setNum(rs.getInt("num"));

q.setTitle(rs.getString("title"));

q.setContent(rs.getString("content"));

q.setRef(rs.getInt("ref"));

q.setStep(rs.getInt("step"));

q.setDepth(rs.getInt("depth"));

ar.add(q);

}

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

return ar;

}

//부모 새글 쓰기

public int setContent(QnaDTO qd){

int result = 0;

try {

int max = getMax()+1;

String sql = "insert into qnaboard values(?, ?, ?, ?, ?, ?)";

st = con.prepareStatement(sql);

st.setInt(1, max);

st.setString(2, qd.getTitle());

st.setString(3, qd.getContent());

st.setInt(4, max); //원본글의 ref는 자신의 num

st.setInt(5, 0); //원본글의 스텝 0

st.setInt(6, 0); //원보글의 들여쓰기 0

result = st.executeUpdate();

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

return result;

}

//글의 최신번호를 가져오기위한 내부 메서드

private int getMax(){

int max = 0;

try {

String sql = "select max(num) from qnaboard";

Statement st = con.createStatement();

rs = st.executeQuery(sql);

if(rs.next()){

max = rs.getInt(1);

}

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

return max;

}

}

******************************************************************

index.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"

    pageEncoding="UTF-8"%>

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">

<html>

<head>

<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">

<title>Insert title here</title>

</head>

<body>

<a href="writeForm.jsp">글쓰기</a>

<a href="listView.jsp?curpage=1">리스트보기</a>

</body>

</html>

******************************************************************

writeForm.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"

    pageEncoding="UTF-8"%>

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">

<html>

<head>

<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">

<title>Insert title here</title>

</head>

<body>

원본글쓰기 폼

<form action="writePro.jsp" method="post">

<input type="text" name="title"><br>

<textarea rows="20" cols="20" name="content"></textarea><br>

<input type="submit" value="등록">

</form>

</body>

</html>

******************************************************************

writePro.jsp

<%@page import="board.QnaDAO"%>

<%@ page language="java" contentType="text/html; charset=UTF-8"

    pageEncoding="UTF-8"%>

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">

<html>

<head>

<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">

<title>Insert title here</title>

</head>

<body>

<jsp:useBean id="qd" class="board.QnaDTO" />

<jsp:setProperty property="*" name="qd"/>

<%

QnaDAO qa = new QnaDAO();

int result = qa.setContent(qd);

if(result>0){

%>

<h2>글쓰기 성공</h2>

<a href="#">리스트 보기</a>

<% }else{ %>

<h2>글쓰기 실패</h2>

<a href="./qnaWrite.jsp">글쓰기</a>

<% }  %>

</body>

</html>

******************************************************************

listView.jsp

<%@page import="board.QnaDTO"%>

<%@page import="java.util.ArrayList"%>

<%@page import="board.QnaDAO"%>

<%@ page language="java" contentType="text/html; charset=UTF-8"

    pageEncoding="UTF-8"%>

<%

int curpage = Integer.parseInt(request.getParameter("curpage"));

QnaDAO qdao = new QnaDAO();

ArrayList<QnaDTO> ar = qdao.getList(curpage);

int total = qdao.getCount();

if(total%10==0){

total = total/10;

}else{

total = (total/10)+1;

}

%>    

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">

<html>

<head>

<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">

<title>Insert title here</title>

</head>

<body>

<table>

<tr>

<td>번호</td><td>제목</td>

</tr>

<%

for(int i=0 ; i <ar.size() ; i++){

%>

<tr>

<td><%=ar.get(i).getNum() %></td>

<td><%=ar.get(i).getTitle() %></td>

</tr>

<%} %>

<%

for(int i=1 ; i<=total ; i++){%>

<a href="listView.jsp?curpage=<%=i %>"><%=i %></a>

<% }%>

</table>

</body>

</html>

******************************************************************

답글 들여쓰기 쿼리문

insert into qnaboard values(1,'test','test',1,0,0)

insert into qnaboard values(2,'test2','test2',2,0,0)

insert into qnaboard values(3,'retest' ,'retest',1,1,1)

insert into qnaboard values(4,'re2test','re2test',1,1,1)

insert into qnaboard values(5,'re3test','re3test',1,1,1)

update qnaboard set step=step+1 where ref=1 and step>0;

select * from qnaboard

select * from qnaboard order by ref desc;

select * from qnaboard order by step asc;

select * from qnaboard order by ref desc, step asc

******************************************************************

parameter 받아오는 방법

1. get방식

2. useBean 액션태그 사용

******************************************************************

자바 전체 주석 한번에

씨프트 + 화살표로 블럭지정  + 컨트롤 + 슬러시