23.06.16 금 68일차
JAVA
- 답변형 게시판
그훕 - 게시클이 생성된 부모의 순번
답변순 - 게시글의 부모에 포함된 답글이 존재할 경우 순번 + 1을 정하여 상위에서부터 순차적으로 번호가 설정된다 (EX: 101글의 답글 101-1 이 존재시 102-1 의 답글의 순번에 +1)
레벨 - 답글의 깊이에 대한 내용으로 부모 글의 답글작성이 될 시에 +1을 하여 들여쓰기를 한다.- 오라클 DB 생성
테이블 생성
Create table reboard (
idx number(5) not null PRIMARY KEY,
sname varchar2(10) ,
title nvarchar2(50),
ref int,
re_step int,
re_level int,
cnt number(4)
);
시퀀스생성
Create sequence idx_reboard
start with 101 -- 초기값
increment by 1; -- 증가 크기
레코드 값 삽입
insert into reboard(idx, sname, title, ref, re_step, re_level, cnt)
values(idx_reboard.nextval, '김춘배','나는 김춘배요', idx_reboard.nextval, 1, 1, 1);- 답변 추가하기
중요 코드만 보자면
DAOimpl
package reboard;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import conn.DB;
public class ReDaoImpl implements ReDao{
DBConnection dbconn = null;
Connection conn = null;
String SQL = "";
PreparedStatement pstmt = null;
ResultSet rs= null;
@Override
public void insert(ReBoardVO vo) {
try {
dbconn = DBConnection.getInstance();
conn = dbconn.getConnection();
SQL = "insert into reboard(idx, sname, title, ref, re_step, re_level, cnt)"
+ "values(idx_reboard.nextval, ?, ?, idx_reboard.nextval, ?, ?, ? )";
pstmt = conn.prepareStatement(SQL);
pstmt.setString(1, vo.getSname());
pstmt.setString(2, vo.getTitle());
pstmt.setInt(3, vo.getRe_step());
pstmt.setInt(4, vo.getRe_level());
pstmt.setInt(5, vo.getCnt());
pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally {
dbconn.close1(pstmt, conn);
}
}
@Override
public void update(ReBoardVO vo) {
try {
dbconn = DBConnection.getInstance();
conn = dbconn.getConnection();
SQL = "update reboard set sname = ?, title = ? where idx = ?";
pstmt = conn.prepareStatement(SQL);
pstmt.setString(1, vo.getSname());
pstmt.setString(2, vo.getTitle());
pstmt.setInt(3, vo.getIdx());
pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally {
dbconn.close1(pstmt, conn);
}
}
@Override
public void delete(int idx) {
try {
dbconn = DBConnection.getInstance();
conn = dbconn.getConnection();
SQL = "delete from reboard where idx = ? ";
pstmt=conn.prepareStatement(SQL);
pstmt.setInt(1, idx);
pstmt.executeUpdate();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
if(pstmt != null) {
try {
pstmt.close();
}catch(Exception e) {
e.printStackTrace();
}
}
if(conn != null) {
try {
conn.close();
}catch(Exception e) {
e.printStackTrace();
}
}
}
}
@Override
public void cntUpdate(int idx) {
try {
dbconn = DBConnection.getInstance();
conn = dbconn.getConnection();
SQL = "update reboard set cnt = cnt+1 where idx = ?";
pstmt = conn.prepareStatement(SQL);
pstmt.setInt(1, idx);
pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally {
dbconn.close1(pstmt, conn);
}
}
@Override
public ReBoardVO edit(int idx) {
ReBoardVO m = null;
try {
dbconn = DBConnection.getInstance();
conn = dbconn.getConnection();
SQL = "select * from reboard where idx = ?";
pstmt = conn.prepareStatement(SQL);
pstmt.setInt(1, idx);
rs = pstmt.executeQuery();
while(rs.next()) {
m=new ReBoardVO();
m.setIdx(rs.getInt("idx"));
m.setSname(rs.getString("sname"));
m.setTitle(rs.getString("title"));
m.setRef(rs.getInt("ref"));
m.setRe_step(rs.getInt("re_step"));
m.setRe_level(rs.getInt("re_level"));
m.setCnt(rs.getInt("cnt"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
dbconn.close2(rs,pstmt, conn);
}
return m;
}
@Override
public List<ReBoardVO> select(ReBoardVO vo) {
List<ReBoardVO> li = null;
try {
li = new ArrayList<ReBoardVO>();
dbconn = DBConnection.getInstance();
conn = dbconn.getConnection();
SQL = "select idx,sname, title, ref, re_step, re_level, cnt from reboard order by ref desc, re_step asc";
pstmt = conn.prepareStatement(SQL);
rs = pstmt.executeQuery();
ReBoardVO m = null;
while(rs.next()) {
m=new ReBoardVO();
m.setIdx(rs.getInt("idx"));
m.setSname(rs.getString("sname"));
m.setTitle(rs.getString("title"));
m.setRef(rs.getInt("ref"));
m.setRe_step(rs.getInt("re_step"));
m.setRe_level(rs.getInt("re_level"));
m.setCnt(rs.getInt("cnt"));
li.add(m);
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
dbconn.close2(rs,pstmt, conn);
}
return li;
}
@Override
public void reWrite(ReBoardVO vo) {
try {
dbconn = DBConnection.getInstance();
conn = dbconn.getConnection();
SQL = "insert into reboard(idx, sname, title, ref, re_step, re_level, cnt)"
+ "values(idx_reboard.nextval, ?, ?, ?, ?, ?, 1 )";
pstmt = conn.prepareStatement(SQL);
pstmt.setString(1, vo.getSname());
pstmt.setString(2, vo.getTitle());
pstmt.setInt(3, vo.getRef());
pstmt.setInt(4, vo.getRe_step()+1);
pstmt.setInt(5, vo.getRe_level()+1);
pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally {
dbconn.close1(pstmt, conn);
}
}
@Override
public void reStep(ReBoardVO vo) {
try {
dbconn = DBConnection.getInstance();
conn = dbconn.getConnection();
SQL = "update reboard set re_step=re_step+1 "
+ " where ref = ? and re_step > ?";
pstmt = conn.prepareStatement(SQL);
pstmt.setInt(1, vo.getRef());
pstmt.setInt(2, vo.getRe_step());
pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally {
dbconn.close1(pstmt, conn);
}
}
}각종 쿼리문을 작성하며, 오늘 진행한 답글을 위해 reStep과 reWrite를 작성했고, step의 비교를 통해 답글의 갯수가 들어날시 step이 1이 증가되어 표현했다.
Service Impl
package reboard;
import java.util.List;
public class ReServiceImpl implements ReService{
private ReDao dao = null;
public ReServiceImpl() {
dao = new ReDaoImpl();
}
@Override
public void insert(ReBoardVO vo) {
dao.insert(vo);
}
@Override
public void update(ReBoardVO vo) {
dao.update(vo);
}
@Override
public void delete(int idx) {
dao.delete(idx);
}
@Override
public void cntUpdate(int idx) {
dao.cntUpdate(idx);
}
@Override
public ReBoardVO edit(int idx) {
return dao.edit(idx);
}
@Override
public List<ReBoardVO> select(ReBoardVO vo) {
return dao.select(vo);
}
@Override
public void reWrite(ReBoardVO vo) {
dao.reStep(vo);
dao.reWrite(vo);
}
}
처음으로 dao와 service 의 IMplement 파일이 다르게 동작했다.
reWrite메소드는 컨트롤러 부분에서 불러오기때문에 dao에서 작성된 두개의 메소드를 하나의 메소드로 불러와 실행할 수 있다.
따라서 스텝의 비교 후 답글이 삽입되도록 동작을 설정했다.
Controller
package reboard;
import java.io.IOException;
import java.util.List;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
/**
* Servlet implementation class ReController
*/
@WebServlet("/ReController")
public class ReController extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public ReController() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
// response.getWriter().append("Served at: ").append(request.getContextPath());
request.setCharacterEncoding("utf-8");
response.setContentType("text/html; charset=utf-8");
response.setCharacterEncoding("utf-8");
String sw = request.getParameter("sw");
ReService service = new ReServiceImpl();
ReBoardVO vo = new ReBoardVO();
if(sw.equals("I")) {
System.out.println("====>I");
String sname=request.getParameter("sname");
String title=request.getParameter("title");
vo.setSname(sname);
vo.setTitle(title);
vo.setRe_step(1);
vo.setRe_level(1);
vo.setCnt(1);
service.insert(vo);
response.sendRedirect("ReController?sw=S");
}else if(sw.equals("S")) {
System.out.println("====>S");
List<ReBoardVO> li = service.select(vo);
request.setAttribute("li", li);
RequestDispatcher dispatcher = request.getRequestDispatcher("/reboard/re_list.jsp");
dispatcher.forward(request, response);
}else if(sw.equals("E")) {
System.out.println("====>E");
String idx = request.getParameter("idx");
service.cntUpdate(Integer.parseInt(idx));
ReBoardVO m = service.edit(Integer.parseInt(idx));
request.setAttribute("m", m);
RequestDispatcher dispatcher = request.getRequestDispatcher("/reboard/re_edit.jsp");
dispatcher.forward(request, response);
}else if(sw.equals("D")) {
System.out.println("====>D");
String idx = request.getParameter("idx");
service.delete(Integer.parseInt(idx));
response.sendRedirect("ReController?sw=S");
}else if(sw.equals("수정하기")) {
System.out.println("====>U");
String idx = request.getParameter("idx");
String sname = request.getParameter("sname");
String title = request.getParameter("title");
vo.setIdx(Integer.parseInt(idx));
vo.setSname(sname);
vo.setTitle(title);
service.update(vo);
response.sendRedirect("ReController?sw=S");
}else if(sw.equals("답글작성")) {
System.out.println("====>RE");
int idx = Integer.parseInt(request.getParameter("idx"));
String sname = request.getParameter("sname");
String title = request.getParameter("title");
int ref = Integer.parseInt(request.getParameter("ref"));
int re_step = Integer.parseInt(request.getParameter("re_step"));
int re_level = Integer.parseInt(request.getParameter("re_level"));
vo.setIdx(idx);
vo.setSname(sname);
vo.setTitle(title);
vo.setRef((ref));
vo.setRe_step(re_step);
vo.setRe_level(re_level);
service.reWrite(vo);
response.sendRedirect("ReController?sw=S");
}
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request, response);
}
}jsp파일과 연결되어 파라미터값을 불러오고 보내며 서비스를 통해 dao의 쿼리문도 연결을 해준다.
값을 불러오고 보내는 부분이 많이 복잡하지만 익숙해진다면 굉장히 유용한 파일이 될것같다.
form. jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import="java.util.*" %>
<%@ page import="reboard.*" %>
<%@ include file ="/include/top.jsp" %>
<%@ include file ="/include/DB.jsp" %>
<%
@SuppressWarnings("unchecked")
List<ReBoardVO> li =(ArrayList<ReBoardVO>) request.getAttribute("li");
%>
<section>
<br>
<div align="center">
<h2> 답변형 목록보기 </h2>
<table border=1 width=500 >
<tr align="center">
<td>순번 </td><td>이름 </td><td>제목 </td>
<td>그룹 </td><td>step </td><td>level </td><td>조회수 </td>
</tr>
<% for(ReBoardVO m : li) { %>
<tr align="center">
<td><%=m.getIdx()%></td>
<td align=left><%=m.getSname() %> </td>
<td align=left>
<a href=ReController?sw=E&idx=<%=m.getIdx()%>>
<%
int w =0;
if (m.getRe_level() > 1 ) {
w =m.getRe_level() * 7;
%>
<img src="<%=path %>/reboard/space.png" height=0 width=<%=w %>><img src="<%=path %>/reboard/next.png" height=17 width=17>
<%
}
%>
<%=m.getTitle() %></a>
</td>
<td><%=m.getRef() %> </td>
<td><%=m.getRe_step() %></td>
<td><%=m.getRe_level() %> </td>
<td><%=m.getCnt() %> </td>
</tr>
<% } %>
</table>
</div>
</section>
<%@ include file ="/include/footer.jsp" %>
list.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import="java.util.*" %>
<%@ page import="reboard.*" %>
<%@ include file ="/include/top.jsp" %>
<%@ include file ="/include/DB.jsp" %>
<%
@SuppressWarnings("unchecked")
List<ReBoardVO> li =(ArrayList<ReBoardVO>) request.getAttribute("li");
%>
<section>
<br>
<div align="center">
<h2> 답변형 목록보기 </h2>
<table border=1 width=500 >
<tr align="center">
<td>순번 </td><td>이름 </td><td>제목 </td>
<td>그룹 </td><td>step </td><td>level </td><td>조회수 </td>
</tr>
<% for(ReBoardVO m : li) { %>
<tr align="center">
<td><%=m.getIdx()%></td>
<td align=left><%=m.getSname() %> </td>
<td align=left>
<a href=ReController?sw=E&idx=<%=m.getIdx()%>>
<%
int w =0;
if (m.getRe_level() > 1 ) {
w =m.getRe_level() * 7;
%>
<img src="<%=path %>/reboard/space.png" height=0 width=<%=w %>><img src="<%=path %>/reboard/next.png" height=17 width=17>
<%
}
%>
<%=m.getTitle() %></a>
</td>
<td><%=m.getRef() %> </td>
<td><%=m.getRe_step() %></td>
<td><%=m.getRe_level() %> </td>
<td><%=m.getCnt() %> </td>
</tr>
<% } %>
</table>
</div>
</section>
<%@ include file ="/include/footer.jsp" %>
edit.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<%@ page import="reboard.*" %>
<%@ page import="java.util.*"%>
<%@ include file ="/include/top.jsp" %>
<%
ReBoardVO m = (ReBoardVO) request.getAttribute("m");
%>
<script>
function delK(k){
alert("K확인:" +k)
location.href="ReController?sw=D&idx="+k
// location.href=`psd_delete.jsp?m_idx=${'${k}'}`;
// 안쪽은 ES6의 표기법이고 바깥쪽은 EL 표기법 ()
}
function btn_Click(str){
alert("확인"+str)
if(str == 'modify'){
alert("수정하기")
f1.action="<%=path %>/ReController"
}else if(str == 'reWrite'){
alert("답글작성")
f1.action="<%=path %>/ReController"
}
}
function listK(){
alert("목록이동")
location.href="ReController?sw=S"
}
function writeK(){
alert("새글작성")
location.href="<%=path%>/reboard/re_form.jsp"
}
</script>
<section>
<br>
<div align="center">
<h2> 회 원 상 세 보 기 </h2>
<form name="f1" >
<table border=1 width=450 height=270>
<tr>
<td width=50 align="center">idx </td>
<td><input type=text name=idx value=<%=m.getIdx() %> > </td>
</tr>
<tr>
<td width=50 align="center">ref </td>
<td><input type=text name=ref value=<%=m.getRef() %> > </td>
</tr>
<tr>
<td width=50 align="center">re_step </td>
<td><input type=text name=re_step value=<%=m.getRe_step() %> > </td>
</tr>
<tr>
<td width=50 align="center">re_level </td>
<td><input type=text name=re_level value=<%=m.getRe_level() %> > </td>
</tr>
<tr>
<td width=50 align="center">이름 </td>
<td><input type=text name=sname value=<%=m.getSname() %> > </td>
</tr>
<tr>
<td width=50 align="center">제목 </td>
<td><input type=text name=title value="<%=m.getTitle() %>" size=30 > </td>
</tr>
<tr>
<td colspan=3 align="center">
<input type=submit value="수정하기" name="sw" onClick="btn_Click('modify')" >
<input type=button value="목록보기" onClick="listK()">
<input type=button value="새글작성" onClick="writeK()">
<input type=button value="삭제하기" onClick="delK('<%=m.getIdx()%>')">
<input type=submit value="답글작성" name="sw" onClick="btn_Click('reWrite')" >
</td>
</tr>
</table>
</form>
</div>
</section>
<%@ include file ="/include/footer.jsp" %>form을 통해 테이블을 작성하여 DB에 값을 삽입하는 폼을 만들고,
List를 통해 DB에 삽입된 레코드를 불러와 리스트 형식으로 확인 할 수 있다.
edit를 통해 상세화면의 테이블을 작성하고, 삭제, 수정, 답글설정 등등 submit과 button을 통해 이동 될 수 있도록 한다.
MVC 패턴을 통해 파라미터의 변수값을 넣어 각 쿼리문을 실행할 수 있도록 구분할 수 있다.
그리고 두개의 Submit을 통해 action이 되도록 onClick과 Script의 if문 파라미터의 변수를 설정하여 각각 다르게 동작하도록 설정했다.
Back-end Developer Preparation Students