[Spring] 16. 게시판 검색기능 추가

Hyeongmin Jung·2023년 8월 29일
IntelliJSpringstudy
0

Spring

목록 보기
15/17

🐤 게시판 검색

✅ 검색할 대상을 뭘로 할지에 따라 쿼리 달리짐(동적쿼리)
✅ 게시글 페이지에서 목록을 눌렀을 때 게시글이 있는 목록 페이지으로 이동

🐣 MyBatis의 동적쿼리

⚫ <sql>과 <include>

공통 부분을 <sql>로 정의하고 <include>로 포함시켜 재사용

⚫ <if> / <choose>와 <when>

🃏 와일드 카드

✅ % (mysql/Oracle) : 여러글자(0~n)
✅ _ / ? (mysql/Oracle) : 한글자(1)
ex) 'title%' : title (O) | title1 (O)
     'title_' : title (X) | title1 (O)

select * from board
↪ where true and title like concat('title1', '%'); -- title%
↪ where true and title like concat('title1', ''); -- title
↪ where true and title not like concat('title1', ''); -- title 빼고

⚫ <foreach>

✔️ 한 개일 때 WHERE bno = 1
✔️ 여러 개일 때 WHERE bno in (1, 2, 3)
✔️ 개수가 정해져 있지 않은 경우 foreach 사용

select * from board
↪ where true and bno in (990, 991, 992)
↪ where true and bno not in (990, 991, 992)
order by bno;

🐤 실습

✔️ script를 넣지 못하도록 title과 content는 out 태그처리
<c:out value="${boardDto.title}" />
<c:out value="${boardDto.content}"/>

SearchCondition.java

public class SearchCondition {
    private Integer page = 1;
    private Integer pageSize = 10;
//    private Integer offset = 0;
    private String keyword = "";
    private String option = "";

    public SearchCondition(){}
    public SearchCondition( Integer page, Integer pageSize, String keyword, String option) {
        this.option = option;
        this.keyword = keyword;
        this.page = page;
        this.pageSize = pageSize;
    }

    public String getQueryString() {
        return getQueryString(page);
    }

    public String getQueryString(Integer page) {
        // ?page=10&pageSize=10&option=A&keyword=title
        return UriComponentsBuilder.newInstance()
                .queryParam("page",     page)
                .queryParam("pageSize", pageSize)
                .queryParam("option",   option)
                .queryParam("keyword",  keyword)
                .build().toString();
    }

    public Integer getPageSize() {
        return pageSize;
    }
    public void setPageSize(Integer pageSize) {
        this.pageSize = pageSize;
    }

    public Integer getPage() {
        return page;
    }
    public void setPage(Integer page) {
        this.page = page;
    }

    public String getKeyword() {
        return keyword;
    }
    public void setKeyword(String keyword) {
        this.keyword = keyword;
    }

    public String getOption() {
        return option;
    }
    public void setOption(String option) {
        this.option = option;
    }

    public Integer getOffset() {
        return (page-1)*pageSize;
    }

    @Override
    public String toString() {
        return "SearchCondition{" +
                "page=" + page +
                ", pageSize=" + pageSize +
                "page=" + getOffset() +
                ", keyword='" + keyword + '\'' +
                ", option='" + option + '\'' +
                '}';
    }
}

boardMapper.xml

<sql id="searchCondition">
    <choose>
        <when test='option=="T"'>
            AND title LIKE concat('%', #{keyword}, '%')
        </when>
        <when test='option=="W"'>
            AND writer LIKE concat('%', #{keyword}, '%')
        </when>
        <otherwise>
            AND (title   LIKE concat('%', #{keyword}, '%')
            OR   content LIKE concat('%', #{keyword}, '%'))
        </otherwise>
    </choose>
</sql>

<select id="searchSelectPage" parameterType="SearchCondition" resultType="BoardDto">
    SELECT bno, title, content, writer, view_cnt, comment_cnt, reg_date
    FROM  board
    WHERE true
    <include refid="searchCondition"/>
    ORDER BY reg_date DESC, bno DESC
        LIMIT #{offset}, #{pageSize}
</select>

<select id="searchResultCnt" parameterType="SearchCondition" resultType="int">
    SELECT count(*)
    FROM  board
    WHERE true
    <include refid="searchCondition"/>
</select>

BoardDaoImpl.java

@Override
public List<BoardDto> searchSelectPage(SearchCondition sc) throws Exception {
    return session.selectList(namespace+"searchSelectPage", sc);
} // List<E> selectList(String statement, Object parameter)

@Override
public int searchResultCnt(SearchCondition sc) throws Exception {
    return session.selectOne(namespace+"searchResultCnt", sc);
} // T selectOne(String statement, Object parameter)

BoardDaoImplTest.java

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = {"file:src/main/webapp/WEB-INF/spring/root-context.xml"})
public class BoardDaoImplTest {
    @Autowired
    private BoardDao boardDao;

    @Test
    public void searchResultCntTest() throws Exception {
        boardDao.deleteAll();
        for (int i = 1; i<=20; i++){
            BoardDto boardDto = new BoardDto("title"+i, "hello world", "asdf"+i);
            boardDao.insert(boardDto);
        }

        SearchCondition sc = new SearchCondition(1, 10, "title2", "T"); // title2%
        int cnt = boardDao.searchResultCnt(sc);
        assertTrue(cnt==2); // 1~20 | title2, title20

        sc = new SearchCondition(1, 10, "asdf2", "W"); // asdf2%
        cnt = boardDao.searchResultCnt(sc);
        assertTrue(cnt==2); // 1~20 | asdf2, asdf20
    }

    @Test
    public void searchSelectPageTest() throws Exception {
        boardDao.deleteAll();
        for (int i = 1; i<=20; i++){
            BoardDto boardDto = new BoardDto("title"+i, "hello world", "asdf"+i);
            boardDao.insert(boardDto);
        }

        SearchCondition sc = new SearchCondition(1, 10, "title2", "T"); // asdf2%
        List<BoardDto> list = boardDao.searchSelectPage(sc);
//        System.out.println("list = " + list);
        assertTrue(list.size()==2); // 1~20 | title2, title20

        sc = new SearchCondition(1, 10, "asdf2", "W"); // title2%
        list = boardDao.searchSelectPage(sc);
        assertTrue(list.size()==2); // 1~20 | asdf2, asdf20
    }
 }

BoardController.java

@Controller
@RequestMapping("/board")
public class BoardController {
    @Autowired
    BoardService boardService;
    
        @PostMapping("/modify")
    public String modify(Integer page, Integer pageSize,BoardDto boardDto, Model m,
                         HttpSession session, RedirectAttributes rattr){
        String writer = (String)session.getAttribute("id");
        boardDto.setWriter(writer);

        try {
            int rowCnt = boardService.modify(boardDto);
            if(rowCnt!=1)
                throw new Exception("Modify failed");

            rattr.addAttribute("page", page);
            rattr.addAttribute("pageSize", pageSize);
            rattr.addFlashAttribute("msg","MOD_OK");
            return "redirect:/board/list";

        } catch (Exception e) {
            e.printStackTrace();
            m.addAttribute("mode", "modify"); // 수정 모드로
            m.addAttribute(boardDto); // "boardDto" 생략
            m.addAttribute("page", page);
            m.addAttribute("pageSize", pageSize);
            m.addAttribute("msg","MOD_ERR");
            return "board";  // 등록하려던 내용을 보여줘야 함.
        }
    }
    
    @GetMapping("/list")
    public String list(@ModelAttribute SearchCondition sc, Model m, HttpServletRequest request) {
        if(!loginCheck(request))
            return "redirect:/login/login?toURL="+request.getRequestURL();  // 로그인을 안했으면 로그인 화면으로 이동

        try {
            int totalCnt = boardService.getsearchResultCnt(sc);
            m.addAttribute("totalCnt", totalCnt);

            PageHandler pageHandler = new PageHandler(totalCnt, sc);

            List<BoardDto> list = boardService.getsearchResultPage(sc);
            m.addAttribute("list", list);
            m.addAttribute("ph", pageHandler);

            Instant startOfToday = LocalDate.now().atStartOfDay(ZoneId.systemDefault()).toInstant();
            m.addAttribute("startOfToday", startOfToday.toEpochMilli());

        } catch (Exception e) {
            e.printStackTrace();
            m.addAttribute("msg", "LIST_ERR");
            m.addAttribute("totalCnt", 0);
        }

        return "boardList"; // 로그인을 한 상태이면, 게시판 화면으로 이동
    }
}

BoardServiceImpl.java

@Repository
public class BoardServiceImpl implements BoardService {
    @Autowired
    BoardDao boardDao;

    @Override
    public List<BoardDto> getsearchResultPage(SearchCondition sc) throws Exception {
        return boardDao.searchSelectPage(sc);
    }

    @Override
    public int getsearchResultCnt(SearchCondition sc) throws Exception {
        return boardDao.searchResultCnt(sc);
    }
}

BoardService.java

public interface BoardService {
    List<BoardDto> getsearchResultPage(SearchCondition sc) throws Exception;
    int getsearchResultCnt(SearchCondition sc) throws Exception;
}

PageHandler.java

public class PageHandler  {
    private SearchCondition sc;

    private int totalCnt;
    private int naviSize = 10;
    private int totalPage;
    private int beginPage;
    private int endPage;
    private boolean showPrev;
    private boolean showNext;

    public PageHandler(int totalCnt, SearchCondition sc) {
        this.totalCnt = totalCnt;
        this.sc = sc;

        doPaging(totalCnt, sc);
    }

    public void doPaging(int totalCnt, SearchCondition sc){
        this.totalPage = totalCnt / sc.getPageSize() + (totalCnt % sc.getPageSize()==0? 0:1);
        this.sc.setPage(Math.min(sc.getPage(), totalPage));  // page가 totalPage보다 크지 않게
        this.beginPage = (this.sc.getPage() -1) / NAV_SIZE * NAV_SIZE + 1; // 11 -> 11, 10 -> 1, 15->11. 따로 떼어내서 테스트
        this.endPage = Math.min(beginPage + NAV_SIZE - 1, totalPage);
        this.showPrev = beginPage!=1;
        this.showNext = endPage!=totalPage;
    }
    
    public String getQueryString() {
        return getQueryString(this.sc.getPage());
    }

    public String getQueryString(Integer page) {
        // ?page=10&pageSize=10&option=A&keyword=title
        return UriComponentsBuilder.newInstance()
                .queryParam("page",     page)
                .queryParam("pageSize", sc.getPageSize())
                .queryParam("option",   sc.getOption())
                .queryParam("keyword",  sc.getKeyword())
                .build().toString();
    }

    void print() {
        System.out.println("page = "+ sc.getPage());
        System.out.print(showPrev? "[PREV] " : " ");

        for(int i=beginPage;i<=endPage;i++) {
            System.out.print(i+" ");
        }
        System.out.println(showNext? " [NEXT]" : " ");
    }

	// toString() 생략
    // getter & setter 생략
}

board.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"%>
<%@taglib prefix="c" uri="http://java.sun.com/jstl/core_rt" %>
<%@ page session="true"%>

<c:set var="loginId" value="${sessionScope.id}"/>
<c:set var="loginOutLink" value="${loginId=='' ? '/login/login' : '/login/logout'}"/>
<c:set var="loginOut" value="${loginId=='' ? 'Login' : loginId}"/>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta http-equiv="x-ua-compatible" content="IE-edge">
    <meta name="viewport" content="width-device-width, initial-scale-1.0">
    <meta charset="UTF-8">
    <title>fastcampus</title>
    <link rel="stylesheet" href="<c:url value='/css/menu.css'/>">
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css">
    <script src="https://code.jquery.com/jquery-1.11.3.js"></script>
    <style>생략</style>
</head>
<body>
<div id="menu">
    <ul>
        <li id="logo">fastcampus</li>
        <li><a href="<c:url value='/'/>">Home</a></li>
        <li><a href="<c:url value='/board/list'/>">Board</a></li>
        <li><a href="<c:url value='${loginOutLink}'/>">${loginOut}</a></li>
        <li><a href="<c:url value='/register/add'/>">Sign in</a></li>
        <li><a href=""><i class="fas fa-search small"></i></a></li>
    </ul>
</div>
<script>
    let msg = "${msg}";
    if(msg=="WRT_ERR") alert("게시물 등록에 실패했습니다. 다시 시도해주세요.");
    if(msg=="MOD_ERR") alert("게시물 등록에 실패했습니다. 다시 시도해주세요.");
</script>
<div class="container">
	<%-- 게시물 읽기(mode="")/게시물 수정(mode="modify")/게시물 글쓰기(mode="new") --%>
    <h2 class="writing-header">게시물 ${mode!="new" ? "읽기":(mode=="modify")? "수정":"글쓰기"}</h2>

    <form action="" id="form" class="frm" method="post">
        <%-- 읽어올 때는 readonly 수정할 때는 X --%>
        <input type="hidden" name="bno" value="${boardDto.bno}">
        <input type="text" name="title" value="<c:out value='${boardDto.title}'/>" placeholder="제목을 입력해 주세요." ${mode=="new" ?'':'readonly="readonly"'}>
        <textarea name="content" rows="20" placeholder=" 내용을 입력해 주세요." ${mode=="new" ?'':'readonly="readonly"'}><c:out value="${boardDto.content}"/></textarea>

        <c:if test="${mode eq 'new'}">
            <button type="button" id="writeBtn" class="btn btn-write"><i class="fa fa-pencil"></i> 등록</button>
        </c:if>
        <c:if test="${mode ne 'new'}">
            <button type="button" id="writeNewBtn" class="btn btn-write"><i class="fa fa-pencil"></i> 글쓰기</button>
        </c:if>
        <c:if test="${boardDto.writer eq loginId}">
            <button type="button" id="modifyBtn" class="btn btn-modify"><i class="fa fa-edit"></i> 수정</button>
            <button type="button" id="removeBtn" class="btn btn-remove"><i class="fa fa-trash"></i> 삭제</button>
        </c:if>
        <button type="button" id="listBtn" class="btn btn-list"><i class="fa fa-bars"></i> 목록</button>
    </form>
</div>
<script>
    $(document).ready(function(){
        let formCheck = function() {
            let form = document.getElementById("form");
            // 내용이 비워있으면
            if(form.title.value=="") {
                alert("제목을 입력해 주세요.");
                form.title.focus();
                return false;
            }
            // 제목이 비워있으면
            if(form.content.value=="") {
                alert("내용을 입력해 주세요.");
                form.content.focus();
                return false;
            }
            return true;
        }

        $('#listBtn').on("click", function(){
            // 브라우저 주소창에 다음 주소 입력 GET
            location.href="<c:url value='/board/list${searchCondition.queryString}'/>";
        });

        $('#modifyBtn').on("click", function(){
            // 1. 읽기 상태이면 수정 상태로 변경
            let form = $('#form');
            let isReadOnly = $("input[name=title]").attr('readonly');

            if(isReadOnly=='readonly'){
                $("input[name=title]").attr('readonly',false); // title
                $("textarea").attr('readonly',false); // content
                $("#modifyBtn").html("<i class='fa fa-pencil'></i> 등록");
                $(".writing-header").html("게시글 수정");
                return;
            }

            // 2. 수정 상태이면 수정된 내용을 서버로 전송하고 원래 게시물 목록 페이지로
            form.attr("action", "<c:url value='/board/modify${searchCondition.queryString}'/>");
            form.attr("method", "post");
            if(formCheck())
                form.submit();
        });

        $('#writeNewBtn').on("click", function(){
            location.href="<c:url value='/board/write'/>";
        });

        $('#writeBtn').on("click", function(){
            if(!confirm("정말로 등록하시겠습니까?")) return;
            let form = $('#form');
            form.attr("action", "<c:url value='/board/write'/>");
            form.attr("method", "post");

            if(formCheck())
                form.submit();
        });

        $('#removeBtn').on("click", function(){
            if(!confirm("정말로 삭제하시겠습니까?")) return;

            let form = $("#form");
            form.attr("action", "<c:url value='/board/remove${searchCondition.queryString}'/>");
            form.attr("method", "post");
            form.submit();
        });
    });
</script>
</body>
</html>

boardList.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"%>
<%@taglib prefix="c" uri="http://java.sun.com/jstl/core_rt" %>
<%@taglib prefix="fmt" uri="http://java.sun.com/jstl/fmt_rt" %>
<%@ page session="true"%>

<c:set var="loginId" value="${sessionScope.id}"/>
<c:set var="loginOutLink" value="${loginId=='' ? '/login/login' : '/login/logout'}"/>
<c:set var="loginOut" value="${loginId=='' ? 'Login' : loginId}"/>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>fastcampus</title>
    <link rel="stylesheet" href="<c:url value='/css/menu.css'/>">
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css">
    <script src="https://code.jquery.com/jquery-1.11.3.js"></script>
    <style>생략</style>
</head>
<body>
<div id="menu">
    <ul>
        <li id="logo">fastcampus</li>
        <li><a href="<c:url value='/'/>">Home</a></li>
        <li><a href="<c:url value='/board/list'/>">Board</a></li>
        <li><a href="<c:url value='${loginOutLink}'/>">${loginOut}</a></li>
        <li><a href="<c:url value='/register/add'/>">Sign in</a></li>
        <li><a href=""><i class="fas fa-search small"></i></a></li>
    </ul>
</div>
<script>
    let msg = "${msg}";
    if(msg=="LIST_ERR")  alert("게시물 목록을 가져오는데 실패했습니다. 다시 시도해 주세요.");
    if(msg=="READ_ERR")  alert("삭제되었거나 없는 게시물입니다.");
    if(msg=="DEL_ERR")   alert("삭제되었거나 없는 게시물입니다.");

    if(msg=="DEL_OK")    alert("성공적으로 삭제되었습니다.");
    if(msg=="WRT_OK")    alert("성공적으로 등록되었습니다.");
    if(msg=="MOD_OK")    alert("성공적으로 수정되었습니다.");
</script>

<div style="text-align:center">
<%--    onclick function(){location.href=}을 생략하고 그 안의 location.href=만 써줌
        location.href 주소 지정, GET방식 --%>
    <div class="board-container">
        <div class="search-container">
            <form action="<c:url value="/board/list"/>" class="search-form" method="get">
                <select class="search-option" name="option">
                    <option value="A" ${ph.sc.option=='A' || ph.sc.option=='' ? "selected" : ""}>제목+내용</option>
                    <option value="T" ${ph.sc.option=='T' ? "selected" : ""}>제목만</option>
                    <option value="W" ${ph.sc.option=='W' ? "selected" : ""}>작성자</option>
                </select>

                <input type="text" name="keyword" class="search-input"
                       type="text" value="${ph.sc.keyword}" placeholder="검색어를 입력해주세요">
                <input type="submit" class="search-button" value="검색">
            </form>
            <button id="writeBtn" class="btn-write"token tag"><c:url value="/board/write"/>'"><i class="fa fa-pencil"></i> 글쓰기</button>
        </div>

        <table border="1">
            <tr>
                <th class="no">번호</th>
                <th class="title">제목</th>
                <th class="writer">이름</th>
                <th class="reg_date">등록일</th>
                <th class="view_cnt">조회수</th>
            </tr>
            <c:forEach var="boardDto" items="${list}">
            <tr>
                <td class="no">${boardDto.bno}</td>
                <td class="title"><a href="<c:url value="/board/read${ph.sc.queryString}&bno=${boardDto.bno}"/>"><c:out value="${boardDto.title}"/></a></td>
                <td class="writer">${boardDto.writer}</td>

                <c:choose> <%-- 당일이면 시간, 그 전이면 날짜만--%>
                    <c:when test="${boardDto.reg_date.time >= startOfToday}">
                        <td class="regdate"><fmt:formatDate value="${boardDto.reg_date}" pattern="HH:mm" type="time"/></td>
                    </c:when>
                    <c:otherwise>
                        <td class="regdate"><fmt:formatDate value="${boardDto.reg_date}" pattern="yyyy-MM-dd" type="date"/></td>
                    </c:otherwise>
                </c:choose>
                <td class="viewcnt">${boardDto.view_cnt}</td>
            </tr>
            </c:forEach>
        </table>
        <br>
        <div class="paging-container">
            <div class="paging">
                <c:if test="${totalCnt==null || totalCnt==0}">
                    <div> 게시물이 없습니다. </div>
                </c:if>
                <c:if test="${totalCnt!=null && totalCnt!=0}">
                    <c:if test="${ph.showPrev}">
                        <a class="page" href="<c:url value="/board/list${ph.sc.getQueryString(ph.beginPage-1)}"/>">&lt;</a>
                    </c:if>
                    <c:forEach var="i" begin="${ph.beginPage}" end="${ph.endPage}">
                        <a class="page ${i==ph.sc.page? "paging-active" : ""}" href="<c:url value="/board/list${ph.sc.getQueryString(i)}"/>">${i}</a>
                    </c:forEach>
                    <c:if test="${ph.showNext}">
                        <a class="page" href="<c:url value="/board/list${ph.sc.getQueryString(ph.endPage+1)}"/>">&gt;</a>
                    </c:if>
                </c:if>
            </div>
        </div>
    </div>
</body>
</html>

참고) 자바의 정석 | 남궁성과 끝까지 간다

profile
Hyeongmin Jung
java
이전 포스트

[Spring] 15. 게시판 CRUD 구현

다음 포스트

[Spring] 17. REST API&Ajax

0개의 댓글