211018_ajax로 게시판 만들기

퀀텀db로 테이블 생성

 

 

 

DTO

package ajaxboard;

 

import java.sql.Timestamp;

 

public class AjaxBoardDto {

 

//퀀텀db랑 개수,이름 같아야 함

private String num;  //파라메타로 보내는 num값으로만 쓰기에 string, 계산할게 없기에 int로 안함..int로 하면 매번 형변환해줘야 함

private String writer;

private String subject;

private String content;

private String avata;

private Timestamp writeday;

 

 

//setter,getter

public String getNum() {

return num;

}

public void setNum(String num) {

this.num = num;

}

public String getWriter() {

return writer;

}

public void setWriter(String writer) {

this.writer = writer;

}

public String getSubject() {

return subject;

}

public void setSubject(String subject) {

this.subject = subject;

}

public String getContent() {

return content;

}

public void setContent(String content) {

this.content = content;

}

public String getAvata() {

return avata;

}

public void setAvata(String avata) {

this.avata = avata;

}

public Timestamp getWriteday() {

return writeday;

}

public void setWriteday(Timestamp writeday) {

this.writeday = writeday;

}

}

 

 

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

 

DAO

package ajaxboard;

 

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.ArrayList;

 

import mysql.db.DbConnect;

 

public class AjaxBoardDao {

 

//db연결

DbConnect db=new DbConnect();

 

//모든 메서드 처리

 

//insert

//파라메타값으로 dto 넘기기

//resultset은 조회할때만 필요

public void insertBoard(AjaxBoardDto dto) {

Connection conn=db.getConnection();

PreparedStatement pstmt=null;  //연결되어 있는 커넥션을 통해 sql을 전달

 

String sql="insert into ajaxboard(writer,subject,content,avata,writeday) values(?,?,?,?,now())";

 

try {

pstmt=conn.prepareStatement(sql);

 

//?바인딩..dto로부터 가져와서 dao에 set해준다

pstmt.setString(1, dto.getWriter());

pstmt.setString(2, dto.getSubject());

pstmt.setString(3, dto.getContent());

pstmt.setString(4, dto.getAvata());

 

//실행

pstmt.execute();

 

} catch (SQLException e) {

 

} finally {

db.dbClose(pstmt, conn);

}

}

 

 

//전체출력

//실제 출력을 해야 하기에 void 아님

//ArrayList..리턴값 있음

public ArrayList<AjaxBoardDto> getAllDatas(){

 

ArrayList<AjaxBoardDto> list=new ArrayList<AjaxBoardDto>();

 

Connection conn=db.getConnection();

PreparedStatement pstmt=null;  //statement도 가능

ResultSet rs=null;  //조회할게 있기에 

 

String sql="select * from ajaxboard order by num desc";

 

try {

pstmt=conn.prepareStatement(sql);

 

//실행

rs=pstmt.executeQuery();

 

//전체데이터가 끝날때까지 필요하기에 while

while(rs.next()) {

 

AjaxBoardDto dto=new AjaxBoardDto();

 

dto.setNum(rs.getString("num"));

dto.setWriter(rs.getString("writer"));

dto.setSubject(rs.getString("subject"));

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

dto.setAvata(rs.getString("avata"));

dto.setWriteday(rs.getTimestamp("writeday"));

 

//list에 추가(dto값 넘기기)

list.add(dto);

}

} catch (SQLException e) {

 

} finally {

db.dbClose(rs, pstmt, conn);

}

return list;

}

 

 

//한개의 데이터 반환이기에 dto꾸러미인 ArrayList<AjaxBoardDto> 필요없음

//조회하기에 resultset 필요

public AjaxBoardDto getData(String num) {

 

AjaxBoardDto dto=new AjaxBoardDto();

 

Connection conn=db.getConnection();

PreparedStatement pstmt=null;

ResultSet rs=null;

 

String sql="select * from ajaxboard where num=?";

 

try {

pstmt=conn.prepareStatement(sql);

 

//?바인딩

pstmt.setString(1, num);

 

//실행

rs=pstmt.executeQuery();

 

//하나만 구할 땐 if, 여러개 구할 땐 while

if(rs.next()) {

dto.setNum(rs.getString("num"));

dto.setWriter(rs.getString("writer"));

dto.setSubject(rs.getString("subject"));

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

dto.setAvata(rs.getString("avata"));

dto.setWriteday(rs.getTimestamp("writeday"));

}

} catch (SQLException e) {

 

} finally {

db.dbClose(rs, pstmt, conn);

}

return dto;

}

}

 

 

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

 

insertboard.jsp

<%@page import="ajaxboard.AjaxBoardDao"%>

<%@page import="ajaxboard.AjaxBoardDto"%>

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

    pageEncoding="UTF-8"%>

<%

    //post방식으로 보낼것임

    request.setCharacterEncoding("utf-8");

    

    String writer=request.getParameter("writer");

    String subject=request.getParameter("subject");

    String content=request.getParameter("content");

    String avata=request.getParameter("avata");

    

    //dto선언

    AjaxBoardDto dto=new AjaxBoardDto();

    

    //dto에 request한 값을 set해주기

    dto.setWriter(writer);

    dto.setSubject(subject);

    dto.setContent(content);

    dto.setAvata(avata);

    

    //insert

    AjaxBoardDao db=new AjaxBoardDao(); //dao선언  

    db.insertBoard(dto);

%>

 

 

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

 

listboard.jsp

<%@page import="org.json.simple.JSONObject"%>

<%@page import="org.json.simple.JSONArray"%>

<%@page import="java.text.SimpleDateFormat"%>

<%@page import="ajaxboard.AjaxBoardDto"%>

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

<%@page import="ajaxboard.AjaxBoardDao"%>

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

    pageEncoding="UTF-8"%>

<%

    //전체출력 list 불러오기

    AjaxBoardDao db=new AjaxBoardDao();

    ArrayList<AjaxBoardDto> list=db.getAllDatas();

    

    SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd hh:mm");

    

    //json_simple.jar 라이브러리 이용해서 json 데이터 생성하기

    JSONArray arr=new JSONArray();    

    //for each문

    for(AjaxBoardDto dto:list){

    JSONObject ob=new JSONObject();

    ob.put("num", dto.getNum());

    ob.put("writer", dto.getWriter());

    ob.put("subject", dto.getSubject());

    ob.put("content", dto.getContent());

    ob.put("avata", dto.getAvata());

    ob.put("writeday", sdf.format(dto.getWriteday()));

    

    //json배열에 추가

        arr.add(ob);

    }

%>

 

<!-- 표현식 -->

<%=arr.toString() %>

 

 

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

 

homeboard.html

<!DOCTYPE html>

<html>

<head>

<meta charset="UTF-8">

<title>Insert title here</title>

<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">

<script src="https://code.jquery.com/jquery-3.5.0.js"></script>

<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/js/bootstrap.min.js"></script>

<link href="https://fonts.googleapis.com/css2?family=Do+Hyeon&display=swap" rel="stylesheet">

 

<style type="text/css">

div.list,div.detail {

position: absolute;

left: 100px;

top: 100px;

}

 

div.writeform {

position: absolute;

left: 700px;

top: 100px;

}

 

div.list *,div.writeform *{

font-family: 'Do Hyeon';

font-size: 13pt;

}

 

span.detail {

cursor: pointer;

}

 

div.detail button {

width: 80px;

margin-left: 10px;

}

</style>

 

<script type="text/javascript">

$(function() {

 

//사용자지정함수 불러오기

list();

 

//입력폼 처음에는 안보이게

$("div.writeform").hide();

 

//글쓰기 누르면 나타나게

$("#btnwrite").click(function() {

$("div.writeform").toggle();  /* show와 hide 합쳐놓은 기능 */

});

 

//db추가버튼

$("#btnadd").click(function() {

var writer=$("#writer").val();

var subject=$("#subject").val();

var content=$("#content").val();

var avata=$("#avata").val();

 

console.log(writer,subject,content,avata);  //확인용

 

$.ajax({

type:"post",

dataType:"html",

url:"insertboard.jsp",

data:{"writer":writer,"subject":subject,"content":content,"avata":avata}, //json형태의 key값과 value값

success:function(){

 

//insert 성공시 처리할 것들..list 불러오기

$("#writer").val("");  //초기화

$("#subject").val("");

$("#content").val("");

 

//다시출력

list();

 

//입력폼 사라지게

$("div.writeform").hide();

}

});

});

 

 

//제목 클릭시 내용보기 이벤트

$(document).on("click","span.detail",function(){

 

//span태그에 넣어둔 넘버값 가져오기

var num=$(this).attr("num");

//alert(num);  //확인용

 

$.ajax({

type:"get",

dataType:"json",

data:{"num":num},

url:"detailboard.jsp",

success:function(data){

 

//detail 테이블을 어떠한 형태로 만들지..div로 해도 됨

var s="<table class='table table-bordered' style='width:500px;'>";

s+="<caption><b>내용 확인</b></caption>";

s+="<tr bgcolor='#ffc0cb'>";

s+="<td><b style='font-size:1.5em'>"+data.subject+"</b>";

s+="<span style='float:right, color:gray;'>"+data.writeday+"</span></td></tr>";

s+="<tr><td>";

s+="<img src='"+data.avata+"' width='50' align='left hspace='20'>";

s+="<br>"+data.writer+"</td></tr>";

s+="<tr height='150'><td><pre>"+data.content+"</pre></td></tr>";

//버튼

s+="<tr><td align='right' num='"+data.num+"'>";

s+="<button type='button' class='btn btn-info btnlist'>목록</button>";

s+="<button type='button' class='btn btn-info btnupdate'>수정</button>";

 

s+="<button type='button' class='btn btn-info btndel'>삭제</button>";

s+="</td></tr>";

 

s+="</table>";

 

$("div.detail").html(s);

 

//list랑 겹치니까 숨기기

$("div.list").hide();

//detail보이기

$("div.detail").show();

}

});

});

});

 

 

//사용자지정함수 출력하는 list

 function list(){

$.ajax({

 

type:"get",

dataType:"json",

url:"listboard.jsp",

success:function(data){

 

//alert(data.length);  //나중에 확인용

 

//제목출력

var s="<table class='table table-bordered' style='width=650px;'>";

s+="<caption><b>Ajax게시판</b></caption>";

s+="<tr bgcolor='#ffffe0'>";

s+="<th width='50'>번호</th>";

s+="<th width='300'>제목</th>";

s+="<th width='70'>작성자</th>";

s+="<th width='100'>작성일</th>";

s+="</tr>";

 

if(data.length==0){

s+="<tr>";

s+="<td colspan='4' align='center'>";

s+="<b>저장된글이 없습니다</b>";

s+="</td></tr>";

} else{

//데이터가 1개이상일 경우

$.each(data,function(i,item){

s+="<tr>";

s+="<td align='center'>"+(i+1)+"</td>";

s+="<td>";

s+="<span num='"+item.num+"' class='detail'>"+item.subject+"</span>";

s+="</td>";

    s+="<td>"+item.writer+"</td>";

s+="<td>"+item.writeday+"</td>";

s+="</tr>"

});

}

s+="</table>";

$("#board").html(s);

}

});

}

</script>

 

</head>

<body>

<div class="list">

  <button type="button" class="btn btn-info" id="btnwrite">글 작성</button>

  <div id="board">List</div>

</div>

 

<div class="writeform">

  <form action="#">

    <table class="table table-bordered" style="width: 300px;">

      <caption><b>게시글 작성</b></caption>

      <tr>

        <th bgcolor="#e0ffff">작성자</th>

        <td>

          <input type="text" id="writer" style="width: 120px;"

                 class="form-control input-sm">

        </td>

      </tr>

      

      <tr>

        <th bgcolor="#e0ffff">제목</th>

        <td>

          <input type="text" id="subject" style="width: 200px;"

                 class="form-control input-sm">

        </td>

      </tr>

      

      <tr>

        <th bgcolor="#e0ffff">아바타</th>

        <td>

          <select id="avata" class="form-control">

            <option value="../image/08.png">PX-41</option>

            <option value="../image/10.png">그루</option>

            <option value="../image/11.png">아그네스</option>

            <option value="../image/09.png">스튜어트</option>

            <option value="../image/03.png">케빈</option>

          </select>

        </td>

      </tr>

      

      <tr>

        <td colspan="2">

          <textarea style="width: 300px; height: 80px;" id="content"

                    class="form-control"></textarea>

        </td>

      </tr>

      

      <tr>

        <td colspan="2" align="center">

          <button type="button" class="btn btn-danger" id="btnadd">DB추가</button>

        </td>

      </tr>

    </table>

  </form>

</div>

 

<div class="detail"></div>

</body>

</html>

 

글 작성 버튼 누르면 게시판 나타남

제목 클릭하면 내용 나타남

 

 

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

 

detailboard.jsp

<%@page import="java.text.SimpleDateFormat"%>

<%@page import="org.json.simple.JSONObject"%>

<%@page import="mysql.db.DbConnect"%>

<%@page import="ajaxboard.AjaxBoardDto"%>

<%@page import="ajaxboard.AjaxBoardDao"%>

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

    pageEncoding="UTF-8"%>

<%

    //파라메타로 보낸 num 읽기

    String num=request.getParameter("num");

 

    //dao 선언..get메서드 가져오기 위해

    AjaxBoardDao dao=new AjaxBoardDao();

    

    //num에 해당하는 dto열기

    AjaxBoardDto dto=dao.getData(num);

    

    //날짜타입 문자열 지정..아래 writeday 에러 안나기 위해

    SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd hh:mm");

    

    //dto를 json으로 변환    

    //지금은 배열 필요없음..jsonobject만 있으면 됨

    JSONObject ob=new JSONObject();

    

    //json에 데이터 추가

    ob.put("num", dto.getNum());

ob.put("writer", dto.getWriter());

ob.put("subject", dto.getSubject());

ob.put("content", dto.getContent());

ob.put("avata", dto.getAvata());

ob.put("writeday", sdf.format(dto.getWriteday()));  

%>

 

<%=ob.toString() %>

 

 

주소창에 이어서 ?num=1 입력하면 해당 데이터가 출력됨

 

 

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~