IT/development

[mybatis] ๊ฒ€์ƒ‰(Oracle)

์•Œ ์ˆ˜ ์—†๋Š” ์‚ฌ์šฉ์ž 2023. 5. 6.
<!--๊ฒŒ์‹œ๊ธ€ ๋ชฉ๋ก ์กฐํšŒ-->
<select id="selectBoards" parameterType="BoardVo" resultType="BoardVo">
    /* ๊ฒŒ์‹œ๊ธ€ ๋ชฉ๋ก ์กฐํšŒ */
    SELECT ROWNUM AS no
         , T1.boardNo
         , T1.title
         , T1.content
         , T1.writer
         , T1.createDate
         , T1.updateDate
      FROM
          (SELECT BOARD_NO AS boardNo
                , TITLE AS title
                , CONTENT AS content
                , WRITER AS writer
                , TO_CHAR(CREATE_DATE, 'YYYY-MM-DD') AS createDate
                , TO_CHAR(UPDATE_DATE, 'YYYY-MM-DD') AS updateDate
             FROM BOARD
            WHERE 1=1
              AND USE_YN = 'Y'
              <include refid="searchParam"/>
            ORDER BY CREATE_DATE DESC
          )T1
     WHERE 1=1
</select>
<!-- ๊ฒ€์ƒ‰ parameter๊ฐ’ -->
<sql id="searchParam">
    <!-- ์ œ๋ชฉ -->
    <if test="type == 'T'.toString()">
        AND TITLE LIKE '%' || #{keyword} || '%'
    </if>
    <!-- ๋‚ด์šฉ -->
    <if test="type == 'C'.toString()">
        AND CONTENT LIKE '%' || #{keyword} || '%'
    </if>
    <!-- ์ž‘์„ฑ์ž -->
    <if test="type == 'W'.toString()">
        AND WRITER LIKE '%' || #{keyword} || '%'
    </if>
</sql>
package study.example.thboard.vo;

import lombok.Data;

@Data
public class BoardVo extends CommonVo{

    private int no;
    private int boardNo;
    private String keyword;
    private String type;
    private String title;
    private String content;
    private String writer;
    private String useYn;
}
CREATE TABLE "BOARD" 
   ("BOARD_NO" NUMBER(10,0) NOT NULL ENABLE, 
	"TITLE" VARCHAR2(50) NOT NULL ENABLE, 
	"CONTENT" VARCHAR2(4000) NOT NULL ENABLE, 
	"WRITER" VARCHAR2(20) NOT NULL ENABLE, 
	"CREATE_DATE" DATE NOT NULL ENABLE, 
	"UPDATE_DATE" DATE DEFAULT SYSDATE, 
	"USE_YN" CHAR(1) DEFAULT 'Y', 
	 CONSTRAINT "BOARD_PK" PRIMARY KEY ("BOARD_NO")
);
<!DOCTYPE html>
<html xmlns:th="http://www.thymeleaf.org"
      xmlns:layout="http://www.ultraq.net.nz/thymeleaf/layout"
      layout:decorate="~{layouts/default_layout}">

    <script layout:fragment="script" th:inline="javascript" type="text/javascript">
        $(document).ready(function () {
            //๊ธ€์“ฐ๊ธฐ
            $("#btnSave").on("click", function () {
                $("#frm").attr("action", "/regForm").submit();
            });

            //๊ฒ€์ƒ‰
            $("#btnSearch").on("click", function () {
                $("#frm").submit();
            });
        });

        //์‚ญ์ œ
        function del(no) {
            let boardNo = $("<input>").attr("type", "hidden").attr("name", "boardNo").val(no);
            $("#frm").attr("method", "post")
                     .attr("action", "del")
                     .append(boardNo)
                     .submit();
        }
    </script>

<!-- Content -->
<div layout:fragment="content">
      <form id="frm" th:object="${boardVo}" action="" method="get">
      <main>
        <div class="container-fluid px-4">
          <h1>๊ฒŒ์‹œ๊ธ€ ๊ด€๋ฆฌ</h1>

            <div class="input-group mb3">
                <select name="type" th:field="*{type}">
                    <option value="">--</option>
                    <option value="W">์ž‘์„ฑ์ž</option>
                    <option value="T">์ œ๋ชฉ</option>
                    <option value="C">๋‚ด์šฉ</option>
                </select>
                <input type="text" name="keyword" th:field="*{keyword}">
                <button type="button" id="btnSearch" class="btn btn-secondary">๊ฒ€์ƒ‰</button>
            </div>

            <table class="table table-striped">
                                    <thead>
                                        <tr>
                                            <th>์ˆœ๋ฒˆ</th>
                                            <th>์ œ๋ชฉ</th>
                                            <th>๋‚ด์šฉ</th>
                                            <th>์ž‘์„ฑ์ž</th>
                                            <th>๋“ฑ๋ก์ผ</th>
                                            <th>์ˆ˜์ •์ผ</th>
                                            <th></th>
                                        </tr>
                                    </thead>
                                    <tbody>
                                        <tr th:if="${#lists.size(list) >0}" th:each="list : ${list}">
                                            <td th:text="${list.no}"></td>
                                            <td><a th:href="@{/detail(boardNo=${list.boardNo})}" th:text="${list.title}"></a></td>
                                            <td th:text="${list.content}"></td>
                                            <td th:text="${list.writer}"></td>
                                            <td th:text="${list.createDate}"></td>
                                            <td th:text="${list.updateDate}"></td>
                                            <td><a href="#" th:href="'javascript:del('+${list.boardNo}+')'" class="btn btn-danger">์‚ญ์ œ</a></td>
                                        </tr>
                                    </tbody>
                                </table>
        </div>

          <button type="button" id="btnSave" class="btn btn-info">๊ธ€์“ฐ๊ธฐ</button>

      </main>
      </form>
</div>
</html>
    /**
     * ๊ฒŒ์‹œ๊ธ€ ๋ชฉ๋ก ์กฐํšŒ
     * @return
     */
    @GetMapping(value = "")
    public ModelAndView main(@ModelAttribute BoardVo boardVo) {
        ModelAndView mv = new ModelAndView("pages/index");
        List<BoardVo> boardList = null;

        try {
            boardList = boardService.getBoards(boardVo);
            mv.addObject("list", boardList);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return mv;
    }

๋Œ“๊ธ€