231023 JDBC TEMPLATE을 이용한 게시판 구현하기

BoardVo

package com.example.demo.board2;

import lombok.*;

import java.sql.Date;
@Getter
@Setter
@ToString
@NoArgsConstructor
@AllArgsConstructor

public class Board {
    private int num;
    private Date wdate;
    private String writer;
    private String title;
    private String content;
}

BoardDao

package com.example.demo.board2;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

@Repository
public class BoardDao2 {
    @Autowired
    private JdbcTemplate temp;

    //resultMap: 검색한 결과행을 VO에 매핑하는 클래스
    public class BoardMapper implements RowMapper<Board> {//RowMapper 상속

        @Override
        public Board mapRow(ResultSet rs, int rowNum) throws SQLException {//파람1(rs): 검색결과, 파람2(rowNum): 현재 처리할 줄번호
            return new Board(rs.getInt(1), rs.getDate(2), rs.getString(3), rs.getString(4), rs.getString(5));//검색한 결과인 Board객체
        }
    }

    //db작업하는 method 작성
    public void insert(Board b) {
        String sql = "INSERT INTO board VALUES(seq_board.nextval, sysdate, ?, ?, ?)";
        //jdbc template에는 update(쓰기), query(읽기)
        //Object 배열에 writer, title, content 담기
        temp.update(sql, new Object[]{b.getWriter(), b.getTitle(), b.getContent()});
    }

    public Board select(int num) {//pk로 검색 => 한 줄이 검색되거나 검색되지 않거나
        String sql = "SELECT * FROM board WHERE num=?";
        Board b = null;
        try {
            b = temp.queryForObject(sql, new BoardMapper(), num);//검색된 결과를 담는 BoardMapper, num: 키값
        } catch (Exception e) {
            System.out.println("not found");//검색된 결과를 없으면 not found 출력
        }
        return b;
    }

    public ArrayList<Board> selectAll() {
        String sql = "SELECT * FROM board ORDER BY num DESC";
        return (ArrayList<Board>) temp.query(sql, new BoardMapper());
    }

    public void update(Board b) {
        String sql = "UPDATE board SET title=?, content=? WHERE num=?";
        temp.update(sql, new Object[]{b.getTitle(), b.getContent(), b.getNum()});//b.getNum()은 왜 필요할까 =? WHERE num=?
    }

    public void delete(int num) {
        String sql = "DELETE FROM board WHERE num=?";
        temp.update(sql, num);
    }
}

BoardService

package com.example.demo.board2;

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

import java.util.ArrayList;

@Service
public class BoardService2 {
    @Autowired
    private BoardDao2 dao;

    public void addBoard(Board b){
        dao.insert(b);
    }

    public Board getBoard(int num){
        return dao.select(num);
    }

    public ArrayList<Board> getAll(){
        return dao.selectAll();
    }

    public void editBoard(Board b){ dao.update(b);}

    public void delBoard(int num){
        dao.delete(num);
    }
}

BoardController

package com.example.demo.board2;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;

import java.util.ArrayList;

@Controller
@RequestMapping("/board2")

public class BoardController2 {
    @Autowired
    private BoardService2 service;
    //글 작성, 목록, 번호로 검색, 수정, 삭제

    //글목록
    @RequestMapping("/list")
    public void list(ModelMap map) {//model, modelmap, modelandview 셋 중 하나를 선택하는 기분은?map은 자동으로 뷰페이지로 전달된다.
        ArrayList<Board> list = service.getAll();
        map.addAttribute("list", list);
        //뷰페이지: /board2/list.jsp
    }

    //글작성
    @GetMapping("/add")
    public void addForm() {
    }

    @PostMapping("/add")
    public String add(Board b) {
        service.addBoard(b);
        return "redirect:/board2/list";
    }

    //글 검색 및 수정
    @GetMapping("/edit")
    public void editForm(int num, ModelMap map) {
        Board b = service.getBoard(num);
        map.addAttribute("b", b);
    }

    @PostMapping("/edit")
    public String edit(Board b) {
        service.editBoard(b);
        return "redirect:/board2/list";
    }

    //글 삭제
    @RequestMapping("/del")
    public String delBoard(int num) {
        service.delBoard(num);
        return "redirect:/board2/list";
    }

}

add.jsp

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>게시판 글 작성</title>
</head>
<body>
<form action="/board2/add" method="post">
<table border="1">
    <tr><th>작성자</th><td><input type="text" name="writer" value="${sessionScope.loginId}" readonly></td></tr>
    <tr><th>제목</th><td><input type="text" name="title"></td></tr>
    <tr><th>내용</th><td><textarea name="content" rows="30" cols="50"></textarea></td></tr>
    <tr><th>작성</th><td><input type="submit" value="작성"></td></tr>
</table>
</form>
</body>
</html>

edit.jsp

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<html>
<head>
    <title>Title</title>
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.4/jquery.min.js"></script>
    <script type="text/javascript">
        $(document).ready(function(){
            $("#del").click(function(){
                location.href="/board2/del?num=${b.num}"; //board2/del로 삭제 요청.
            });
        });
    </script>
</head>
<body>
<c:if test="${sessionScope.loginId!=b.writer}">
    <c:set var="stat">readonly</c:set>
</c:if>
<h3>상세 페이지</h3>
<form action="/board2/edit" method="post">
    <table border="1">
        <tr>
            <th>번호</th>
            <td><input type="number" name="num" value="${b.num}" readonly></td>
        </tr>
        <tr>
            <th>작성일</th>
            <td><input type="text" value="${b.wdate}" readonly></td>
        </tr>
        <tr>
            <th>작성자</th>
            <td><input type="text" name="writer" value="${b.writer}" readonly></td>
        </tr>
        <tr>
            <th>제목</th>
            <td><input type="text" name="title" value="${b.title}" ${stat }></td>
        </tr>
        <tr>
            <th>내용</th>
            <td><textarea name="content" rows="30" cols="50" ${stat}>${b.content}</textarea></td>
        </tr>
        <c:if test="${sessionScope.loginId==b.writer}">
        <tr>
            <th>편집</th>
            <td><input type="submit" value="수정"><input type="button" value="삭제" id="del"></td>
        </tr>
        </c:if>
    </table>
</form>
</body>
</html>

list.jsp

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>Title</title>
</head>
<body>
<h3>글 목록</h3>
<a href="/board2/add">글 작성하기</a>
<table border="1">
    <tr>
        <th>글번호</th>
        <th>제목</th>
        <th>작성자</th>
    </tr>
    <c:forEach var="b" items="${list }">
    <tr>
        <td>${b.num }</td>
        <td><a href="/board2/edit?num=${b.num }">${b.title }</a></td>
        <td>${b.writer }</td>
    </tr>
        </c:forEach>
</table>
</body>
</html>