타닥타닥 개발자의 일상

Spring Frame Work 와 SQL data 연결, SQL 데이터 회원가입 페이지랑 연동시켜 중복 조회, 회원가입으로 데이터 추가, 게시판 만들어 연결하기 본문

코딩 기록/Spring

Spring Frame Work 와 SQL data 연결, SQL 데이터 회원가입 페이지랑 연동시켜 중복 조회, 회원가입으로 데이터 추가, 게시판 만들어 연결하기

NomadHaven 2022. 1. 6. 00:27

<기본 설정>

 

 Dynamic Web Project 로 파일생성.

Configure - Convert to Maven

log4j.xml, jdbc.properties, Member.xml Bbs.xml, applicationContext.xml

servelt-context.xml, web.xml, pom.xml   미리 설정

<SQL Developer에 입력한 테이블 자료>

기존 회원정보 입력

 

게시판 테이블 생성

 

 

<스프링 내에서 데이터의 흐름>

 

클라이언트 요청 → 컨트롤러 → 서비스(service)  →  DAO  →  SQL session  →  DAO  → 서비스(Service)   

 

 

<jdbc.properties>

 

#oracle setting

jdbc.driverClassName = oracle.jdbc.driver.OracleDriver
jdbc.url = jdbc:oracle:thin:@localhost:1521:xe
jdbc.username = hr
jdbc.password = hr
jdbc.initialSize = 5
jdbc.maxActive = 20

 

<Member.xml> (MemberDao 와 MemberService 모두 연관, SQL Data 와 연결된 곳, mybatis이용.)

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
        
<mapper namespace="Member">

<insert id="addmember" parameterType="multi.camp.a.dto.MemberDto">
	INSERT INTO MEMBER(ID, PWD, NAME, EMAIL, AUTH)
	VALUES(#{id}, #{pwd}, #{name}, #{email}, 3)
</insert>


<select id="getId" parameterType="String" resultType="Integer"> 
 SELECT COUNT(*)
 FROM MEMBER
 WHERE ID=#{id}
</select>

<select id="login" parameterType="multi.camp.a.dto.MemberDto"
		resultType="multi.camp.a.dto.MemberDto">
		SELECT ID,NAME, EMAIL, AUTH
		FROM MEMBER
		WHERE ID =#{id} AND PWD=#{pwd}
</select>

<select id="bbslist" resultType="multi.camp.a.dto.BbsDto">
	SELECT SEQ, ID, REF, STEP, DEPTH, TITLE, CONTENT, WDATE, DEL, READCOUNT
	FROM BBS
</select>


<insert id="writebbs" parameterType="multi.camp.a.dto.BbsDto">
	INSERT INTO BBS(SEQ, ID, REF, STEP, DEPTH, TITLE, CONTENT, WDATE, DEL, READCOUNT)
	VALUES(SEQ_BBS.NEXTVAL, #{id}, (SELECT NVL(MAX(REF)+1, 0)FROM BBS ), 0,0,
			         		#{title},#{content},SYSDATE, 0,0      )
</insert>

</mapper>

 

접속시 첫화면 = login.jsp 파일로 구성

 

<login.jsp>

<%@ 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>

<style type="text/css">
.center{
	margin: auto; 
	width: 60%;
	border: 3px solid #0000ff;
	padding: 10px;
}
</style>

<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
<script src="http://lab.alexcican.com/set_cookies/cookie.js" type="text/javascript" ></script>
<!--아이디 기억 버튼 눌렀을때 표시될 아이디를 저장된 쿠키에서 가져오기 위한 설정-->

</head>
<body>

<h2>login page</h2>

<div class="center">

<form id="frm" action="loginAf.do" method="post">

<table border="1">
<tr>
<th>아이디</th>
	<td>
		<input type="text" id="id" name="id" size="20"><br>
		<input type="checkbox" id="chk_save_id">아이디 저장
	</td>
</tr>
<tr>
	<th>패스워드</th>
	<td>
		<input type="password" name="pwd" id="pwd" size="20">
	</td>
</tr>
<tr>
	<td colspan="2">
		<button type="button" onclick="login()">로그인</button>
		<a href="regi.do">회원가입</a>
	</td>
</tr>

</table>
</form>
</div>

<script type="text/javascript">
function login() {    //로그인 버튼과 연결된 함수login()
	
	if($("#id").val().trim() == ""){
		//공백 제거한 id 입력값이 공백이거나
	}else if($("#pwd").val().trim() == ""){
		//공백 제거한 pwd 입력값이 공백인 경우를 제외하면
	}else{ 
		alert('submit');
		$("#frm").submit();
        //submit이라는 알림창 띄우고 form태그에 있는 값을 제출(어디에?)
	}
}
/*
 web 저장 공간
  session: web server의 저장공간  java 사용자 정보 -> object
  cookie : web client의 저장공간  javascript id,pw 방문 회수 ->String
 */
 
 let user_id = $.cookie("user_id"); // cookie에서 데이터 산출
 									//  model.addarrtibute("list",list)와 같은 형식
if(user_id !=null){ //cookie 저장된 값이 있다면
	$("#id").val( user_id ); // id의 값은 user_id의 값이 된다.
	$("#chk_save_id").prop("checked",true); //체크된 상태는 참이다.
}
 
 $("#chk_save_id").click(function () { //체크 박스 클릭시 function()함수 실행
		if( $("#chk_save_id").is(":checked") ){  //체크된 경우지만
			if ($("#id").val().trim() == ""){  //공백을 제거한 id 입력 값이 없을 경우
		   		alert("id를 입력해 주십시오."); //id를 입력하라는 알람이 뜬다.
		   		$("#chk_save_id").prop("checked",false); //체크되었다는 사실은 거짓이다.
		   		$("#id").val(""); //id입력 값은 공백이된다.
 			}else{
 				//체크된 경우고 아이디 값이 있는 경우 쿠키에 저장
 				$.cookie("user_id", $("#id").val().trim(),{expires: 7, path: './'});
                //공백을 제거한 모든 id 입력값을 7일동안 쿠키에 저장한다.
 			}
		}
		else{	
			//체크박스에 체크가 되지 않은 상태라면 입력된 모든 쿠키 값을 지운다.
			$.removeCookie("user_id",{path:'./'});
	}
		
 });
</script>


</body>
</html>

 

 

 

<로그인 했을시 화면>

<bbslist.jsp> 게시판을 구성하는 코드

<%@page import="multi.camp.a.dto.BbsDto"%>
<%@page import="java.util.List"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
    
<% 
List<BbsDto> bbslist = (List<BbsDto>)request.getAttribute("bbslist");
%>    
    
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>

<h1>게시판</h1>

<div align="center">
<!-- attribute property -->
<table border="1" style="width:1000px">
<col width="30"><col width="200"><col width="80">

<thead>
<tr>
	<th>번호</th><th>제목</th><th>작성자</th>
</tr>
</thead>

<tbody>
<% 
if(bbslist == null || bbslist.size() ==0){
	%>
	<tr>
		<td colspan="3">작성된 글이 없습니다.</td>
	</tr>
	<% 	
}else{

	for(int i = 0; i<bbslist.size(); i++){
		BbsDto bbs = bbslist.get(i);
	%>
	<tr>
		<th><%=i+1%></th>
		<td>
			<a href="bbsdetail.do?seq=<%=bbs.getSeq() %>">
			<%=bbs.getTitle() %>
			</a>
		</td>
		<td><%=bbs.getId() %></td>
	</tr>

	<%
	}
}
%>
</tbody>
</table>
</div>
<br>
<div align="center">
	<a href="bbswrite.do">글쓰기</a>
</div>

</body>
</html>

<Bbs.xml>SQL 데이터와 bbslist를 연결시키는 파일

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
        
<mapper namespace="Bbs">

<select id="bbslist" resultType="multi.camp.a.dto.BbsDto">
	SELECT SEQ, ID, REF, STEP, DEPTH, CONTENT, WDATE, DEL, READCOUNT
	FROM BBS
</select>
</mapper>

 

게시판에서 글쓰기 버튼 눌렀을때 오는 화면

 

 

<bbswrite.jsp> (게시판에서 글쓰기 버튼 눌렀을때 오는 화면의 코드)

<%@page import="multi.camp.a.dto.MemberDto"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
    
<%
//session에서 사용자 정보를 산출
MemberDto mem = (MemberDto)request.getSession().getAttribute("login");

%>    
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>

<h1>글추가</h1>

<div align="center">

<form action="bbswriteAf.do" method="post">

<table style="width: 1000px">
<tr>
	<th>아이디</th>
	<td>
		<input type="text" name="id" size="70px" value="<%=mem.getId()%>" readonly="readonly">
	</td>
</tr>
<tr>
<th>제목</th>
<td>
<input type="text" name="title" size="70px">
</td>
</tr>
<tr>
<th>내용</th>
<td>
<textarea rows="20" cols="80" name="content"></textarea>
</td>
</tr>
<tr>
<td colspan="2">
<input type="submit" value="글쓰기"> 
</td>
</tr>
</table>


</form>
</div>



</body>
</html>

 

 

 

 

 

<regi.jsp> (login.jsp에서 회원가입 누르면 나타나는 페이지)

<%@ 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>

<style type="text/css">
.center{
	margin: auto; 
	width: 60%;
	border: 3px solid #0000ff;
	padding: 10px;
}
</style>

<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>

</head>
<body>

<h2>회원가입</h2>

<div class="center">

<form id="frm" action="regiAf.do" method="post">

<table border="1">
<tr>
	<th>아이디</th>
	<td>
		<input type="text" name="id" id="id" size="20">
		&nbsp;<input type="button" id="btn" value="확인"><br>
		<p id="idcheck" style="font-size: 8px"></p>	<!--id 확인 메세지가 표시될 공간-->	
	</td>
</tr>
<tr>
	<th>패스워드</th>
	<td>
		<input type="text" name="pwd" id="pwd" size="20">
	</td>
</tr>
<tr>
	<th>이름</th>
	<td>
		<input type="text" name="name" id="name" size="20">
	</td>
</tr>
<tr>
	<th>이메일</th>
	<td>
		<input type="text" name="email" id="email" size="20">
	</td>
</tr>
<tr>
	<td colspan="2">
		<!-- <input type="submit" value="회원가입">	 -->
		<button type="button" onclick="account()">회원가입</button>
	</td>
</tr>
</table>

</form>
 
</div>
<script type="text/javascript">
$(document).ready(function () {
	
	$("#btn").click(function () {	
		$.ajax({
			url:"idcheck.do", //id 메세지가 표시될 공간의 주소
			type:"post",
			data:{ id:$("#id").val() }, //전달할 id 입력값
			success:function( msg ){
				if(msg == "YES"){
					$("#idcheck").css("color", "#0000ff");
					$("#idcheck").html("사용할 수 있는 아이디입니다");
				}else{
					$("#idcheck").css("color", "#ff0000");
					$("#idcheck").html("사용 중인 아이디입니다");
					$("#id").val("");
				}			
			},
			error:function(){
				alert('error'); //위의 경우에 해당하지 않는다면 error경고
			}
		});
	});	
});

function account() {
	if($("#id").val() == ''){
		alert('아이디를 입력해 주십시오');
	}
	else if($("#pwd").val() == ''){
		alert('패스워드를 입력해 주십시오');
	}
	else if($("#name").val() == ''){
		alert('이름를 입력해 주십시오');
	}
	else if($("#email").val() == ''){
		alert('이메일을 입력해 주십시오');
	}
	else{
		$("#frm").submit(); //위의 경우에 하나도 해당하지 않는다면 form태그의 값 regiAf.do에  전달? 
	}
}

</script>




</body>
</html>

 

 

 

<index.jsp> (접속하자마자 login.jsp로 보내주는 곳)

<%@ 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>

<%
response.sendRedirect("login.do"); 
%>

</body>
</html>

 

 

<MemberController.java> 

 

package multi.camp.a.controller;

import java.util.Date;
import java.util.List;

import javax.servlet.http.HttpServletRequest;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;

import multi.camp.a.controller.MemberController;
import multi.camp.a.dto.MemberDto;
import multi.camp.a.service.MemberService;

@Controller
public class MemberController {

	private static Logger logger = LoggerFactory.getLogger(MemberController.class);
	
	@Autowired
	MemberService service;
	
	@RequestMapping(value = "login.do", method = RequestMethod.GET)
	public String login() {
		logger.info("MemberController login() " + new Date());		
		return "login";
	}
	
	@RequestMapping(value = "regi.do", method = RequestMethod.GET)
	public String regi() {
		logger.info("MemberController regi() " + new Date());
		return "regi";
	}
	
	@RequestMapping(value = "regiAf.do", method = RequestMethod.POST)
	public String regiAf(MemberDto dto) {
		logger.info("MemberController regiAf() " + new Date());
		
		boolean b = service.addmember(dto);
		if(b == true) {
			System.out.println("가입되었음");
		}
		
		return "redirect:/login.do";
	}
	
	@ResponseBody
	@RequestMapping(value = "idcheck.do", method = RequestMethod.POST)
	public String idcheck(String id) {
		logger.info("MemberController idcheck() " + new Date());		
		System.out.println("id:" + id);
		
		int count = service.getId(id);
		System.out.println("count:" + count);		
		if(count > 0) {	// 아이디가 있음
			return "NO";
		}else {			// 아이디가 없음
			return "YES";
		}		
	}
	
	@RequestMapping(value = "loginAf.do", method = RequestMethod.POST) //login.jsp에서 온 정보
	public String loginAf(MemberDto dto, HttpServletRequest req) { //request
		logger.info("MemberController loginAf() " + new Date());
		
		MemberDto mem = service.login(dto);
		if(mem !=null) { 
			
			req.getSession().setAttribute("login", mem); 
            //login(dto)의 값이 있다면 session에 login이란 이름으로 정보저장?
			
			return "redirect:/bbslist.do";
		}
		else {
			
			return "redirect:/login.do";
		}
	}

}

 

<MemberDao.java>(인터페이스)

package multi.camp.a.dao;

import multi.camp.a.dto.MemberDto;

public interface MemberDao {
	
	int addmember(MemberDto mem);
	
	int getId(String id);
	
	MemberDto login(MemberDto mem);

}

<MemberDaoImpl.java>

package multi.camp.a.dao;

import org.apache.ibatis.session.SqlSession;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import multi.camp.a.dto.MemberDto;

@Repository
public class MemberDaoImpl implements MemberDao {
	
	
	@Autowired
	SqlSession session; 
	
	String ns = "Member.";
	
	@Override
	public int addmember(MemberDto mem) {
	int count =	session.insert(ns+ "addmember" , mem);
		return count;
	}
	
	@Override
	public int getId(String id) {
		int count =session.selectOne(ns+"getId",id);
		return count;
	}
	
	@Override
	public MemberDto login(MemberDto mem) {
		return session.selectOne(ns+"login",mem);
	}
	

	

}

<MemberDto.java>

package multi.camp.a.dto;

import java.io.Serializable;

public class MemberDto implements Serializable {
	
	private String id;
	private String pwd;
	private String name;
	private String email;
	private int auth;
	
	public MemberDto() {		
	}
	public MemberDto(String id, String pwd, String name, String email, int auth) {
		super();
		this.id = id;
		this.pwd = pwd;
		this.name = name;
		this.email = email;
		this.auth = auth;
	}
	public String getId() {
		return id;
	}
	public void setId(String id) {
		this.id = id;
	}
	public String getPwd() {
		return pwd;
	}
	public void setPwd(String pwd) {
		this.pwd = pwd;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getEmail() {
		return email;
	}
	public void setEmail(String email) {
		this.email = email;
	}
	public int getAuth() {
		return auth;
	}
	public void setAuth(int auth) {
		this.auth = auth;
	}
	@Override
	public String toString() {
		return "MemberDto [id=" + id + ", pwd=" + pwd + ", name=" + name + ", email=" + email + ", auth=" + auth + "]";
	}	
}

 

 

<MemberService.java>(인터페이스)

package multi.camp.a.service;

import multi.camp.a.dto.MemberDto;

public interface MemberService {
	
	boolean addmember(MemberDto mem);
	int getId(String id);
	MemberDto login(MemberDto mem);
}

<MemberServiceImpl.java>

package multi.camp.a.service;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import multi.camp.a.dao.MemberDao;
import multi.camp.a.dto.MemberDto;

@Service
public class MemberServiceImpl implements MemberService {
	
	@Autowired
	MemberDao dao;
	
	@Override
	public boolean addmember(MemberDto mem) {

		int count =dao.addmember(mem);
		return count>0?true:false;
	}
	
	@Override
	public int getId(String id) {
		
		return dao.getId(id);
	}
	
	@Override
	public MemberDto login(MemberDto mem) {
		return dao.login(mem);
	}

	

}

 

 

< 상기의 게시판을 구현하는 Bbslist.jsp>

<%@page import="mul.camp.a.dto.BbsDto"%>
<%@page import="java.util.List"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>

<%!
// 답글의 depth와 image를 추가해 주는 함수
// depth 1 = ' '->
// deptj 2 = ' '' '->
public String arrow(int depth){
	String res = "<img src='image/arrow.png' width='20px' height='20px' />";
	String nbsp = "&nbsp;&nbsp;&nbsp;&nbsp;"; // 여백
	
	String ts = "";
	for(int i = 0; i<depth; i++){
		ts += nbsp;
	}
	
	return depth==0?"":ts+res;
}
%>

<%
List<BbsDto> bbslist = (List<BbsDto>)request.getAttribute("bbslist");
%>

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.16.0/umd/popper.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/js/bootstrap.min.js"></script>

<title>Insert title here</title>
</head>
<body>

<h1>게시판</h1>

<div align="center">

<hr>
<select id="choice">
	<option value="title">제목</option>
	<option value="content">내용</option>
	<option value="writer">작성자</option>
</select>

<input type="text" id="search" value="">

<button type="button" onclick="searchBbs()">검색</button>

<br><hr><br>
<!--    attribute property -->
	<table class="table table-hover" style="width:1000px">
	<!-- <col width="30"><col width="200"><col width="80"> -->
	<thead>
	<tr>
	<th>번호</th><th>제목</th><th>정보</th><th>작성자</th>
	</tr>
	</thead>
	<tbody>
<%
if(bbslist == null || bbslist.size() == 0){	
%>
	<tr>
		<td colspan="4">작성된 글이 없습니다.</td>
	</tr>
<%
}
else{
	for(int i=0; i< bbslist.size(); i++){
		BbsDto bbs = bbslist.get(i);	
%>
		<%
			if (bbs.getDel() == 1) {
		%>
		<tr>
			<td colspan="4">삭제된 글입니다.</td>
		</tr>
		<%
		}
			else{			
		%>
		<tr>
			<th><%=i+1%></th>
			<td>
				<%=arrow(bbs.getDepth()) %>
				<a href="bbsdetail.do?seq=<%=bbs.getSeq() %>">
					<%=bbs.getTitle() %>
				</a>
			</td>
			<td><%=bbs.getRef() %>-<%=bbs.getStep() %>-<%=bbs.getDepth() %></td>
			<td align="center"><%=bbs.getId() %></td>
		</tr>

<%
		}
	}
}
%>
		</tbody>	
	</table>
</div>
<br>
<div align="center">
	<a href = "bbswrite.do">글쓰기</a>
</div>

<!-- 
<script type="text/javascript">
location.href = "bbslist.do"; -> GET
</script>
 -->

<script type="text/javascript">
function searchBbs(){
	let choice = document.getElementById("choice").value;
	let search = document.getElementById("search").value;
	
	location.href="bbslist.do?choice=" + choice + "&search=" + search;
}
</script>

</body>
</html>

 

 

 

<BbsController.java>  게시판 이동을 관리하는 코드

package mul.camp.a.controller;

import java.util.Date;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;

import mul.camp.a.dto.BbsDto;
import mul.camp.a.dto.BbsParam;
import mul.camp.a.service.BbsService;

@Controller
public class BbsController {

	private static Logger logger = LoggerFactory.getLogger(BbsController.class);
	
	@Autowired
	BbsService service;
	
	@RequestMapping(value = "bbslist.do", method = RequestMethod.GET)
	public String bbslist(Model model, BbsParam param) {
		logger.info("BbsController bbslist() " + new Date());
		
		List<BbsDto> list = service.bbslist(param);
		model.addAttribute("bbslist", list);
		
		return "bbslist";
	}
	
	@RequestMapping(value = "bbswrite.do", method = RequestMethod.GET)
	public String bbswrite() {
		logger.info("BbsController bbswrite() " + new Date());
		
		return "bbswrite";
	}
	
	@RequestMapping(value = "bbswriteAf.do", method = RequestMethod.POST)
	public String bbswriteAf(BbsDto dto) {
		logger.info("BbsController bbswriteAf() " + new Date());
		System.out.println(dto.toString());		// 확인
		
		boolean b = service.writebbs(dto);
		if(b == true) {
			System.out.println("성공적으로 추가되었습니다");
		}
		
		return "redirect:/bbslist.do";
	}
	
	@RequestMapping(value = "bbsdetail.do", method = RequestMethod.GET)
	public String bbsdetail(Model model, int seq) {
		logger.info("BbsController bbsdetail() " + new Date());
		System.out.println("seq:" + seq);
		
		// DB -> BbsDto
		BbsDto bbs = service.getBbs(seq);
		// 짐싸!
		model.addAttribute("bbs", bbs);
		
		return "bbsdetail";
	}
	
	@RequestMapping(value = "answer.do", method = RequestMethod.GET)
	public String answer(Model model, int seq) {
		logger.info("BbsController answer() " + new Date());
		
		// DB -> BbsDto
		BbsDto bbs = service.getBbs(seq);
		// 짐싸!
		model.addAttribute("bbs", bbs);
		
		return "bbsanswer";
	}
	
	@RequestMapping(value = "answerAf.do", method = RequestMethod.GET)
	public String answerAf(BbsDto dto) {
		logger.info("BbsController answerAf() " + new Date());
		
		// DB	-> update, insert		
	//	System.out.println(dto.toString());
		service.reply(dto);
		
		return "redirect:/bbslist.do";
	}
	
	
	@RequestMapping(value = "updatebbs.do", method = RequestMethod.GET)
	public String updatebbs(Model model, int seq) {
		logger.info("BbsController updatebbs() " + new Date());
		BbsDto bbs = service.getBbs(seq);
		model.addAttribute("bbs", bbs);
		return "updatebbs";
	}

	@RequestMapping(value = "updatebbsAf.do", method = RequestMethod.POST)
	public String updatebbsAf(Model model, BbsDto bbs) {
		logger.info("BbsController updatebbsAf() " + new Date());
		int result = service.bbsupdate(bbs);

		model.addAttribute("result", result);
		return "redirect:/bbslist.do";
	}

	@RequestMapping(value = "deletebbs.do", method = RequestMethod.GET)
	public String deletebbs(Model model, int seq) {
		logger.info("BbsController deletebbs() " + new Date());
		int result = service.bbsdelete(seq);

		model.addAttribute("result", result);
		return "redirect:/bbslist.do";
	}
	
	
}

 

 

<BbsDto.java>

package mul.camp.a.dto;

import java.io.Serializable;

/*
CREATE TABLE BBS(
    SEQ NUMBER(8) PRIMARY KEY,
    ID VARCHAR2(50) NOT NULL,    
    REF NUMBER(8) NOT NULL,
    STEP NUMBER(8) NOT NULL,
    DEPTH NUMBER(8) NOT NULL,    
    TITLE VARCHAR2(200) NOT NULL,
    CONTENT VARCHAR2(4000) NOT NULL,
    WDATE DATE NOT NULL,    
    DEL NUMBER(1) NOT NULL,
    READCOUNT NUMBER(8) NOT NULL
);

CREATE SEQUENCE SEQ_BBS
START WITH 1 INCREMENT BY 1;

ALTER TABLE BBS
ADD CONSTRAINT FK_BBS_ID FOREIGN KEY(ID)
REFERENCES MEMBER(ID);
*/

// BBS(Bulletin Board System)
public class BbsDto implements Serializable{
	
	private int seq;		// 글의 번호	-> sequence
	private String id;		// 작성자
	
	private int ref;		// 그룹번호	-> 댓글용
	private int step;		// 행번호
	private int depth;		// 깊이
	
	private String title;	// 제목
	private String content;	// 내용
	private String wdate;	// 작성일

	private int del;		// 삭제
	private int readcount;	// 조회수
	
	public BbsDto() {
	}

	public BbsDto(int seq, String id, int ref, int step, int depth, String title, String content, String wdate, int del,
			int readcount) {
		super();
		this.seq = seq;
		this.id = id;
		this.ref = ref;
		this.step = step;
		this.depth = depth;
		this.title = title;
		this.content = content;
		this.wdate = wdate;
		this.del = del;
		this.readcount = readcount;
	}

	public BbsDto(String id, String title, String content) {
		super();
		this.id = id;
		this.title = title;
		this.content = content;
	}

	public int getSeq() {
		return seq;
	}

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

	public String getId() {
		return id;
	}

	public void setId(String id) {
		this.id = id;
	}

	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;
	}

	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 String getWdate() {
		return wdate;
	}

	public void setWdate(String wdate) {
		this.wdate = wdate;
	}

	public int getDel() {
		return del;
	}

	public void setDel(int del) {
		this.del = del;
	}

	public int getReadcount() {
		return readcount;
	}

	public void setReadcount(int readcount) {
		this.readcount = readcount;
	}

	@Override
	public String toString() {
		return "BbsDto [seq=" + seq + ", id=" + id + ", ref=" + ref + ", step=" + step + ", depth=" + depth + ", title="
				+ title + ", content=" + content + ", wdate=" + wdate + ", del=" + del + ", readcount=" + readcount
				+ "]";
	}
}

 

<BbsParam.java>

 

package mul.camp.a.dto;

import java.io.Serializable;

public class BbsParam implements Serializable{
	private String choice;
	private String search;
	
	public BbsParam() {
	}
	public BbsParam(String choice, String search) {
		super();
		this.choice = choice;
		this.search = search;
	}
	
	public String getChoice() {
		return choice;
	}
	public void setChoice(String choice) {
		this.choice = choice;
	}
	public String getSearch() {
		return search;
	}
	public void setSearch(String search) {
		this.search = search;
	}	
	
	@Override
	public String toString() {
		return "BbsParam [choice=" + choice + ", search=" + search + "]";
	}
	

}

 

 

 

<BbsService.java>(interface)

package mul.camp.a.service;

import java.util.List;

import mul.camp.a.dto.BbsDto;
import mul.camp.a.dto.BbsParam;

public interface BbsService {

	List<BbsDto> bbslist(BbsParam param);
	
	boolean writebbs(BbsDto dto);
	
	BbsDto getBbs(int seq);
	
	int bbscountUp(int seq);
	
	void reply(BbsDto dto);
	
	int bbsupdate(BbsDto bbs);

	int bbsdelete(int seq);
}

 

 

<BbsServiceImpl.java>

package mul.camp.a.service;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import mul.camp.a.dao.BbsDao;
import mul.camp.a.dto.BbsDto;
import mul.camp.a.dto.BbsParam;

@Service
public class BbsServiceImpl implements BbsService{

	@Autowired
	BbsDao dao;

	@Override
	public List<BbsDto> bbslist(BbsParam param) {		
		return dao.bbslist(param);
	}

	@Override
	public boolean writebbs(BbsDto dto) {
		int count = dao.writebbs(dto);
		return count>0?true:false;
	}

	@Override
	public BbsDto getBbs(int seq) {		
		return dao.getBbs(seq);
	}
	
	@Override
	public int bbscountUp(int seq) {
		return dao.bbscountUp(seq);
	}
	

	@Override
	public void reply(BbsDto dto) {
		int n = dao.replyBbsUpdate(dto);
		if(n == 0) {
			System.out.println("replyBbsUpdate fail~");
		}
		
		n = dao.replyBbsInsert(dto);
		if(n == 0) {
			System.out.println("replyBbsInsert fail~");
		}
	}	
	
	@Override
	public int bbsupdate(BbsDto bbs) {
		return dao.bbsupdate(bbs);
	}

	@Override
	public int bbsdelete(int seq) {
		return dao.bbsdelupdate(seq);
	}

	


}

 

 

 

 

<BbsDao.java>(interface)

package mul.camp.a.dao;

import java.util.List;

import mul.camp.a.dto.BbsDto;
import mul.camp.a.dto.BbsParam;

public interface BbsDao {

	List<BbsDto> bbslist(BbsParam param);
	
	int writebbs(BbsDto dto);
	
	BbsDto getBbs(int seq);
	
	int bbscountUp(int seq);
	
	int replyBbsUpdate(BbsDto dto);
	int replyBbsInsert(BbsDto dto);
	
	int bbsupdate(BbsDto bbs);

	int bbsdelete(int seq);

	int bbsdelupdate(int seq);
}

 

 

<BbsDaoImpl.java>

package mul.camp.a.dao;

import java.util.List;

import org.apache.ibatis.session.SqlSession;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import mul.camp.a.dto.BbsDto;
import mul.camp.a.dto.BbsParam;

@Repository
public class BbsDaoImpl implements BbsDao{

	@Autowired
	SqlSession session;
	
	String ns = "Bbs.";

	@Override
	public List<BbsDto> bbslist(BbsParam param) {		
		return session.selectList(ns + "bbslist", param);
	}

	@Override
	public int writebbs(BbsDto dto) {
		int count = session.insert(ns + "writebbs", dto);
		return count;
	}

	@Override
	public BbsDto getBbs(int seq) {		
		return session.selectOne(ns + "getBbs", seq);
	}
	
	@Override
	public int bbscountUp(int seq) {
		return session.update(ns+ "bbscountup", seq);

	}

	@Override
	public int replyBbsUpdate(BbsDto dto) {
		int n = session.update(ns + "replyBbsUpdate", dto);
		return n;
	}

	@Override
	public int replyBbsInsert(BbsDto dto) {	
		int n = session.insert(ns + "replyBbsInsert", dto);
		return n;
	}	
	
	@Override
	public int bbsupdate(BbsDto bbs) {
		return session.update(ns + "bbsupdate", bbs);
	}

	@Override
	public int bbsdelete(int seq) {
		return session.delete(ns + "bbsdelete", seq);
	}

	@Override
	public int bbsdelupdate(int seq) {
		return session.update(ns + "bbsdelete", seq);
	}
				
}

 

<Bbs.xml>

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
        
<mapper namespace="Bbs">

<select id="bbslist" parameterType="mul.camp.a.dto.BbsParam" resultType="mul.camp.a.dto.BbsDto">
	SELECT SEQ, ID, REF, STEP, DEPTH, TITLE, CONTENT, WDATE, DEL, READCOUNT
	FROM BBS
	<if test="choice != null and choice != '' and search != null and search != ''">
		<if test="choice == 'title'">
			WHERE TITLE LIKE '%'||#{search}||'%'
		</if>
		<if test="choice == 'content'">
			WHERE CONTENT LIKE '%'||#{search}||'%'
		</if>
		<if test="choice == 'writer'">
			WHERE ID=#{search}
		</if>	
	</if>
	ORDER BY REF DESC, STEP ASC
</select>

<insert id="writebbs" parameterType="mul.camp.a.dto.BbsDto">
	INSERT INTO BBS(SEQ, ID, REF, STEP, DEPTH, TITLE, CONTENT, WDATE, DEL, READCOUNT)
	VALUES(SEQ_BBS.NEXTVAL, #{id}, (SELECT NVL(MAX(REF)+1, 0) FROM BBS), 0, 0,       
											#{title}, #{content}, SYSDATE, 0, 0)
</insert>

<select id="getBbs" parameterType="java.lang.Integer" resultType="mul.camp.a.dto.BbsDto">
	SELECT SEQ, ID, REF, STEP, DEPTH, TITLE, CONTENT, WDATE, DEL, READCOUNT
	FROM BBS
	WHERE SEQ=#{seq}
</select>

<!-- answer -->
<update id="replyBbsUpdate" parameterType="mul.camp.a.dto.BbsDto">
	UPDATE BBS
	SET STEP = STEP + 1
	WHERE REF = (SELECT REF FROM BBS WHERE SEQ=#{seq})
		AND STEP > (SELECT STEP FROM BBS WHERE SEQ=#{seq})
</update>

<insert id="replyBbsInsert" parameterType="mul.camp.a.dto.BbsDto">
	INSERT INTO BBS(SEQ, ID, REF, STEP, DEPTH, 
					TITLE, CONTENT, WDATE, DEL, READCOUNT)
	VALUES(SEQ_BBS.NEXTVAL, #{id},
			(SELECT REF FROM BBS WHERE SEQ=#{seq}),		<!-- ref --> 
			(SELECT STEP FROM BBS WHERE SEQ=#{seq}) + 1, <!-- step -->
			(SELECT DEPTH FROM BBS WHERE SEQ=#{seq}) + 1, <!-- depth -->
			#{title}, #{content}, SYSDATE,
			0, 0)	
</insert>

<update id="bbsupdate" parameterType="mul.camp.a.dto.BbsDto">
	UPDATE BBS
	SET TITLE = #{title}, CONTENT=#{content}
	WHERE SEQ=#{seq}
</update>

<update id="bbsdelete" parameterType="Integer" >
	UPDATE BBS
	SET DEL=1
	WHERE SEQ=#{seq}
</update>


</mapper>

 

 

게시클 클릭했을때 보이는 화면

 

 

 

<게시글 화면을 구성하는 bbsdetail.jsp>

<%@page import="mul.camp.a.dto.MemberDto"%>
<%@page import="mul.camp.a.dto.BbsDto"%>
<%@page import="java.util.List"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>

<%
BbsDto bbs = (BbsDto)request.getAttribute("bbs");
%>

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.16.0/umd/popper.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/js/bootstrap.min.js"></script>

</head>
<body>

<h1>상세글보기</h1>

<div align="center">
<!--    attribute property -->
	<table class="table table-bordered" style="width:1000px">
	<!-- <col width="30"><col width="200"><col width="80"> -->
<%
if(bbs == null){	
%>
	<tr>
		<td colspan="3">작성된 글이 없습니다.</td>
	</tr>
<%
}
else{
%>
	<tr>
	<th>작성자</th>
	<td><%=bbs.getId() %></td>
	</tr>
	<tr>
		<th>제목</th>
		<td><%=bbs.getTitle() %></td>
	</tr>
		<tr>
		<th>작성일</th>
		<td><%=bbs.getWdate() %></td>
	</tr>
	<tr>
		<th>조회수</th>
		<td><%=bbs.getReadcount() %></td>
	</tr>
	<tr>
		<th>정보</th>
		<td><%=bbs.getRef() %>-<%=bbs.getStep() %>-<%=bbs.getDepth() %></td>
	</tr>
	<tr>
		<th>내용</th>
		<td><textarea rows="15" cols="100" name="content" readonly><%=bbs.getContent() %></textarea></td>
	</tr>

<%
}
%>

	</table>

<%
MemberDto mem = (MemberDto)request.getSession().getAttribute("login");
%>

<button type="button" onclick="answer(<%=bbs.getSeq() %>)">답글</button>

<%
if (mem.getId().equals(bbs.getId())){
%>
<button type="button" onclick="updatebbs(<%=bbs.getSeq() %>)">수정</button>

<button type="button" onclick="deletebbs(<%=bbs.getSeq() %>)">삭제</button>

<%
}
%>
</div>

<script type="text/javascript">
function answer( seq ){
	alert("answer");
	location.href = "answer.do?seq=" + seq;
}
function updatebbs( seq ){
	location.href = "updatebbs.do?seq=" + seq;
}
function deletebbs( seq ){
	location.href = "deletebbs.do?seq=" + seq;
}
</script>
</body>
</html>

 

 

<글쓰기 화면을 구성하는 bbswrite.jsp>

<%@page import="mul.camp.a.dto.MemberDto"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>

<%
MemberDto mem = (MemberDto)request.getSession().getAttribute("login");
%>    
<!--session에서 사용자 정보를 산출. 
가끔 로그인 시간 너무 오래돼서 login정보 날아간 나머지 505오류 뜰때도 있다. -->    
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>

<h1>글쓰기</h1>

<div align="center">

<form action="bbswriteAf.do" method="post">

<table border="1" style="width: 1000px">
<tr>
	<th>아이디</th>
	<td>
		<input type="text" name="id" size="70px" value="<%=mem.getId() %>" readonly="readonly">
	</td>	
</tr>
<tr>
	<th>제목</th>
	<td>
		<input type="text" name="title" size="70px">
	</td>
</tr>
<tr>
	<th>내용</th>
	<td>
		<textarea rows="20" cols="80" name="content"></textarea>
	</td>
</tr>
<tr>
	<td colspan="2">
		<input type="submit" value="글쓰기">
	</td>
</tr>

</table>



</form>


</div>




</body>
</html>

 

 

글 수정시 보이는 화면

 

 

<상기의 화면을 구성하는 updatebbs.jsp>

 

<%@page import="mul.camp.a.dto.BbsDto"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>

<%
BbsDto bbs = (BbsDto)request.getAttribute("bbs");
%>

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>

<h1>글수정</h1>

<div align="center">
<form action="updatebbsAf.do" method="post">
<input type="hidden" name="seq" value="<%=bbs.getSeq()%>">
<table style="width: 1000px" border="1">
<tr>
	<th>아이디</th>
	<td>
		<input type="text" name="id" size="70px" value="<%=bbs.getId() %>" readonly>
	</td>
</tr>
<tr>
	<th>제목</th>
	<td>
		<input type="text" name="title" size="70px" value="<%=bbs.getTitle() %>">
	</td>
</tr>
<tr>
	<th>내용</th>
	<td>
		<textarea rows="20" cols="80" name="content" ><%=bbs.getContent() %></textarea>
	</td>
</tr>
<tr>
	<td colspan="2">
		<input type="submit" value="수정하기">
	</td>
</tr>
</table>

</form>

</div>

</body>
</html>

 

답글 달때 화면

<상기 화면을 구성하는 bbsanswer.jsp>

 

<%@page import="mul.camp.a.dto.MemberDto"%>
<%@page import="mul.camp.a.dto.BbsDto"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>

<%
	BbsDto bbs = (BbsDto)request.getAttribute("bbs");

	MemberDto mem = (MemberDto)request.getSession().getAttribute("login");
%>    
    
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>bbsanswer</title>
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.16.0/umd/popper.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/js/bootstrap.min.js"></script>
</head>
<body>


<div align="center">
<h1>부모글</h1>

<table class="table table-bordered" style="width: 1000px">
<tr>
	<th>작성자</th>
	<td><%=bbs.getId() %></td>
</tr>
<tr>
	<th>제목</th>
	<td><%=bbs.getTitle() %></td>
</tr>
<tr>
	<th>작성일</th>
	<td><%=bbs.getWdate() %></td>
</tr>
<tr>
	<th>내용</th>
	<td align="center">
		<textarea rows="15" cols="100" readonly="readonly"><%=bbs.getContent() %></textarea>
	</td>
</tr>
</table>

<h1>답글</h1>

<form action="answerAf.do" method="get">

<!-- 부모글에 대한 sequence -->
<input type="hidden" name="seq" value="<%=bbs.getSeq() %>">

<table class="table table-bordered" style="width: 1000px">
<tr>
	<th>아이디</th>
	<td>
		<input type="text" name="id" size="70px" value="<%=mem.getId() %>" readonly="readonly">
	</td>	
</tr>
<tr>
	<th>제목</th>
	<td>
		<input type="text" name="title" size="70px">
	</td>
</tr>
<tr>
	<th>내용</th>
	<td>
		<textarea rows="20" cols="80" name="content"></textarea>
	</td>
</tr>
<tr>
	<td colspan="2">
		<input type="submit" value="글쓰기">
	</td>
</tr>

</table>
</form>

</div>


</body>
</html>

'코딩 기록 > Spring' 카테고리의 다른 글

Jnit test시 java.lang.llegalStateException 오류 났을 때 해결 방법  (0) 2022.06.01
SQL 의 BD 불러와서 페이지랑 연결하기 (Spring Frame Work), 웹 페이지에 SQL 데이터 List, String 형태로 형변환하고 출력  (0) 2021.12.30
다음 페이지를 선택하는 웹페이지 Spring jsp파일로 구성하기 / 객체생성으로 배열 만들고 배열을 table로 만들어보기  (0) 2021.12.29
Spring에서 JSP, java, Ajax, java Script이용한 기초 페이지 만들기  (0) 2021.12.29
'코딩 기록/Spring' Related Articles more
Comments