스프링 검색처리

Criterea.java        내용추가

// 검색처리 관련 추가
private String type;
private String keyword;

public String[] getTypeArr() {
return type == null? new String[] {} : type.split("");
}

 

 

 

 

BoardMapper.xml

 

<select id="getListWithPaging"
resultType="com.mystudy.domain.BoardVO">
  <![CDATA[
  select 
    bno, title, content, writer, regdate, updatedate
  from 
      (
      select /*+INDEX_DESC(tbl_board pk_board) */
        rownum rn, bno, title, content, writer, regdate, updatedate 
      from 
        tbl_board
      where 
  ]]>
<trim prefix="(" suffix=") AND " prefixOverrides="OR">
<foreach item='type' collection="typeArr">
<trim prefix="OR">
<choose>
<when test="type == 'T'.toString()">
title like '%'||#{keyword}||'%'
</when>
<when test="type == 'C'.toString()">
content like '%'||#{keyword}||'%'
</when>
<when test="type == 'W'.toString()">
writer like '%'||#{keyword}||'%'
</when>
</choose>
</trim>
</foreach>
</trim>
      
  <![CDATA[    
      rownum <= #{pageNum} * #{amount}
      )
  where rn > (#{pageNum} -1) * #{amount}   
  ]]>
</select>

 

---

 

너무 길어서!!!

sql 태그 사용하면 코드 깔끔하고 재사용도 가능하다.

 

---

 

 

<sql id="criteria">
   <trim prefix="(" suffix=") AND " prefixOverrides="OR">
<foreach item='type' collection="typeArr">
<trim prefix="OR">
<choose>
<when test="type == 'T'.toString()">
title like '%'||#{keyword}||'%'
</when>
<when test="type == 'C'.toString()">
content like '%'||#{keyword}||'%'
</when>
<when test="type == 'W'.toString()">
writer like '%'||#{keyword}||'%'
</when>
</choose>
</trim>
</foreach>
</trim>
 </sql>

 

 

<select id="getListWithPaging"
resultType="com.mystudy.domain.BoardVO">
  <![CDATA[
  select 
    bno, title, content, writer, regdate, updatedate
  from 
      (
      select /*+INDEX_DESC(tbl_board pk_board) */
        rownum rn, bno, title, content, writer, regdate, updatedate 
      from 
        tbl_board
      where 
  ]]>

<include refid="criteria"></include>
      
      
  <![CDATA[    
      rownum <= #{pageNum} * #{amount}
      )
  where rn > (#{pageNum} -1) * #{amount}   
  ]]>
</select>

 

 

---

 

 

화면에서 검색조건 처리

 

- 페이지 번호처럼 검색조건과 키워드 역시 화면이동시 같이 전송

- 검색버튼 클릭시 1페이지로 이동

- 한글 GET 방식 이동하면 문제생길 수 있어 주의

 

 

 

 

 

list.jsp

 

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

<!-- 부트스트랩 -->
<%@include file="../includes/header.jsp" %>


                <!-- Begin Page Content -->
                <div class="container-fluid">

                    <!-- Page Heading -->
                    <h1 class="h3 mb-2 text-gray-800">게시판</h1>
                    <p class="mb-4">눈누난나 스프링으로 게시판 만들기 </p>

                    <!-- DataTales Example -->
                    <div class="card shadow mb-4">
                        <div class="card-header py-3">
                            <h5 class="m-0 font-weight-bold text-primary">내가 만든 게시판
                            <button id='regBtn' type="button" class="btn btn-xs btn-primary float-right">Register</button>
                            </h5>
                        </div>
                        <div class="card-body">
                            <div class="table-responsive">
                                <table class="table table-striped table-bordered table-hover" id="dataTable" width="100%" cellspacing="0">
                                    <thead>
                                        <tr>
                                            <th>#번호</th>
                                            <th>제목</th>
                                            <th>작성자</th>
                                            <th>작성일</th>
                                            <th>수정일</th>
                                        </tr>
                                    </thead>
                                    <tbody>
                                    <c:forEach var="board" items="${list}">
                                        <tr>
                                            <td>${board.bno}</td>
                                            <td>
                                            <a class='move' href='${board.bno}'>
                                            ${board.title}
                                            </a>
                                            </td>
                                            <td>${board.writer}</td>
                                            <td>
                                             <fmt:formatDate pattern="yyyy-MM-dd" value="${board.regdate}" />
                                           </td>
                                            <td>
                                             <fmt:formatDate pattern="yyyy-MM-dd" value="${board.updatedate}" />
                                            </td>
                                        </tr>
                                        </c:forEach>
                                    </tbody>
                                </table>
                                
                                <br>
                                
                                
                                <div class = 'row'>
                                 <div class="col-lg-12">
                                 <form id='searchForm' action="/board/list" method="get">
                                 <select name='type'>
                                 <option value=""
                                 <c:out value="${pageMaker.cri.type == null?'selected':''}" />>--</option>
                                 <option value="T"
                                 <c:out value="${pageMaker.cri.type eq 'T'?'selected':''}"/>>제목</option>
                                 <option value="C"
                                 <c:out value="${pageMaker.cri.type eq 'C'?'selected':''}"/>>내용</option>
                                 <option value="W"
                                 <c:out value="${pageMaker.cri.type eq 'W'?'selected':''}"/>>작성자</option>
                                 <option value="TC"
                                 <c:out value="${pageMaker.cri.type eq 'TC'?'selected':''}"/>>제목 or 작성자</option>
                                 <option value="TW"
                                 <c:out value="${pageMaker.cri.type eq 'TW'?'selected':''}"/>>제목 or 작성자</option>
                                 <option value="TWC"
                                 <c:out value="${pageMaker.cri.type eq 'TWC'?'selected':''}"/>>제목 or 내용 or 작성자</option>
                                 </select>
                                
                                 <input type='text' name='keyword' value="${pageMaker.cri.keyword}" />

                                 <button class='btn btn-default'>Search</button>
                                 </form>
                                 </div>
                                </div>
                                
                                
                                
                                
                                
                                
                                
                                
                                <br><br>
                                <!-- 페이징처리 -->
                                <div class='pull-right'>
                                 <ul class="pagination">
                                
                                  <c:if test="${pageMaker.prev}">
<li class="paginate_button previous"><a
href="${pageMaker.startPage -1}">Previous</a></li>
</c:if>

<c:forEach var="num" begin="${pageMaker.startPage}"
end="${pageMaker.endPage}">
<li class="paginate_button  ${pageMaker.cri.pageNum == num ? "active":""} ">
<a href="${num}">${num}</a>
</li>
</c:forEach>

<c:if test="${pageMaker.next}">
<li class="paginate_button next"><a
href="${pageMaker.endPage +1 }">Next</a></li>
</c:if>
                              
                                 </ul>
                                </div>
                                <!-- 페이징처리 -->
                                
                                
                                
                                <form id='actionForm' action="/board/list" method='get'>
                                 <input type='hidden' name='pageNum' value='${pageMaker.cri.pageNum}'>
                                 <input type='hidden' name='amount' value='${pageMaker.cri.amount}'>
                                 <input type='hidden' name='type' value='${pageMaker.cri.type}'>
                                 <input type='hidden' name='type' value='${pageMaker.cri.keyword}'>
                                </form> 
                                
                                
                            </div>
                        </div>
                    </div>

                </div>
                <!-- /.container-fluid -->

            </div>
            <!-- End of Main Content -->
          

<!-- Modal-->
    <div class="modal fade" id="myModal" tabindex="-1" role="dialog" aria-labelledby="myModalLabel"
        aria-hidden="true">
        <div class="modal-dialog" role="document">
            <div class="modal-content">
                <div class="modal-header">
                    <h5 class="modal-title" id="myModalLabel">Modal title</h5>
                    <button class="close" type="button" data-dismiss="modal" aria-hidden="true">
                    &times;</button>
                </div>
                <div class="modal-body">처리 완료</div>
                <div class="modal-footer">
                    <button class="btn btn-secondary" type="button" data-dismiss="modal">Close</button>
                    <button class="btn btn-primary" type="button" >Save changes</button>
                </div>
            </div>
        </div>
    </div>     
          
          
            
<script type="text/javascript">

$(document).ready(function(){

/* 모달 설정 */
var result = '${result}';

checkModal(result);

history.replaceState({}, null, null);

function checkModal(result){

if(result === '' || history.state){
return;
}

if(parseInt(result) > 0 ) {
$(".modal-body").html("게시글 " + parseInt(result) + " 번이 등록되었습니다!");
}
$("#myModal").modal("show");
}

/* 등록버튼 */
$("#regBtn").on("click", function(){
self.location = "/board/register";
})


/* 페이징 처리 */
var actionForm = $("#actionForm");

$(".paginate_button a").on("click", function(e){
e.preventDefault();

console.log('click');

actionForm.find("input[name='pageNum']").val($(this).attr("href"));
actionForm.submit();
});


/* 게시물 조회를 위한 이벤트 처리 추가  */
$(".move").on("click", function(e){

e.preventDefault();
actionForm.append("<input type='hidden' name='bno' value='"+$(this).attr("href")+"'>")
actionForm.attr("action", "/board/get");
actionForm.submit();
});



/* 검색처리 */
var searchForm = $("#searchForm");

$("#searchForm button").on("click", function(e){

if(!searchForm.find("option:selected").val()){
alert("검색종류를 선택하세요");
return false;
}

if(!searchForm.find("input[name='keyword']").val()){
alert("키워드를 입력하세요");
return false;
}

searchForm.find("input[name='pageNum']").val("1");
e.preventDefault();

searchForm.submit();

});

});


</script>    

            
<%@include file="../includes/footer.jsp" %>

 

 

 

 

 

get.jsp

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

<!-- 부트스트랩 -->
<%@include file="../includes/header2.jsp" %>

<link rel="stylesheet" type="text/css" href="resources/mycss.css" />


<div class="row">
<div class="col-lg-12 d-flex justify-content-center">
<h1 class="page-header" style="color:#00498c">Board Read</h1>
</div>
</div>


<div class="row">
<div class="col-lg-10" style="float:none; margin:0 auto;">

<div class="panel panel-default">
<!-- <div class="panel-heading">Board Register</div> -->
<div class="panel-body">
<div class="form-group">
<label style="color:tomato">bno</label> 
<input class="form-control" name="bno" value="${board.bno}" readonly="readonly">
</div>
<div class="form-group">
<label style="color:tomato">Title</label> 
<input class="form-control" name="title" value="${board.title}" readonly="readonly">
</div>
<div class="form-group">
<label style="color:tomato">Text area</label> 
<textarea class="form-control" rows="5" name="content" readonly="readonly">${board.content}</textarea>
</div>
<div class="form-group">
<label style="color:tomato">Writer</label> 
<input class="form-control" name="writer" value="${board.writer}" readonly="readonly">
</div>
<div style="text-align:center;">
<button data-oper='modify' class="btn btn-primary"
<%-- onclick="location.href='/board/modify?bno=${board.bno}'" --%>>Modify</button>
<button data-oper='list' class="btn btn-info"
<%-- onclick="location.href='/board/list'" --%>>List</button>
</div>

<form id='operForm' action="/board/modify" method="get">
<input type='hidden' id='bno' name='bno' value="${board.bno}">
<input type='hidden' name='pageNum' value="${cri.pageNum}">
<input type='hidden' name='amount' value="${cri.amount}">

<input type='hidden' name='keyword' value='${cri.keyword}' >
<input type='hidden' name='type' value='${cri.type}' >
</form>
</div>
</div>
</div>
</div>

<script>
$(document).ready(function(){

var oper = $("#operForm");

$("button[data-oper='modify']").on("click", function(e){
oper.attr("action", "/board/modify").submit();
});

$("button[data-oper='list']").on("click", function(e){
oper.find("#bno").remove();
oper.attr("action", "/board/list");
oper.submit();
});

});

</script>


<%@include file="../includes/footer.jsp" %>

 

 

 

 

 

 

BoardController.java

@PostMapping("/modify")

@PostMapping("/remove")

 

두개에 각각

rttr.addAttribute("type", cri.getType());
rttr.addAttribute("keyword", cri.getKeyword());

 

추가해줘야함!

 

 

 

 

 

 

 

modify.jsp

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

<!-- 부트스트랩 -->
<%@include file="../includes/header2.jsp" %>

<link rel="stylesheet" type="text/css" href="resources/mycss.css" />




<div class="row">
<div class="col-lg-12 d-flex justify-content-center">
<h1 class="page-header" style="color:#00498c">Board Modify Page</h1>
</div>
</div>


<div class="row">
<div class="col-lg-10" style="float:none; margin:0 auto;">

<div class="panel panel-default">
<!-- <div class="panel-heading">Board Register</div> -->
<div class="panel-body">

<form action="/board/modify" method="post" id="myform">
<div class="form-group">
<label style="color:tomato">bno</label> 
<input class="form-control" name="bno" value="${board.bno}" readonly="readonly">
</div>
<div class="form-group">
<label style="color:tomato">Title</label> 
<input class="form-control" name="title" value="${board.title}" >
</div>
<div class="form-group">
<label style="color:tomato">Text area</label> 
<textarea class="form-control" rows="5" name="content" >${board.content}</textarea>
</div>
<div class="form-group">
<label style="color:tomato">Writer</label> 
<input class="form-control" name="writer" value="${board.writer}" readonly="readonly">
</div>
<div class="form-group">
<input type="hidden" class="form-control" name="regdate" 
value='<fmt:formatDate pattern = "yyyy/MM/dd" value = "${board.regdate}" />' 
readonly="readonly">
</div>
<div class="form-group">
<input type="hidden" class="form-control" name="updatedate" 
value='<fmt:formatDate pattern = "yyyy/MM/dd" value = "${board.updatedate}" />'
readonly="readonly">
</div>



<!-- 페이징처리, 검색처리  -->
<input type='hidden' name='pageNum' value='${cri.pageNum}'>
<input type='hidden' name='amount' value='${cri.amount}'>

<input type='hidden' name='type' value='${cri.type}'>
<input type='hidden' name='keyword' value='${cri.keyword}'>
<!--  -->



<div style="text-align:center;">
<button type="submit" data-oper='modify' class="btn btn-primary">Modify</button>
<button type="submit" data-oper='remove' class="btn btn-danger">Remove</button>
<button type="submit" data-oper='list' class="btn btn-info">List</button>
</div>
</form>





</div>
</div>
</div>
</div>



<script type="text/javascript">
$(document).ready(function(){

var form = $("#myform");

$('button').on("click", function(e){

e.preventDefault();  //submit 처리되는것 막아

var operation = $(this).data("oper");

console.log(operation);

if(operation === 'remove'){
form.attr("action", "/board/remove");
} else if(operation === 'list'){
form.attr("action", "/board/list").attr("method","get");
var pageNumTag = $("input[name='pageNum']").clone();
var amountTag = $("input[name='amount']").clone();
var keywordTag = $("input[name='keyword']").clone();
var typeTag = $("input[name='type']").clone();

form.empty();
form.append(pageNumTag);
form.append(amountTag);
form.append(keywordTag);
form.append(typeTag);
//self.location = "/board/list";
//return;
}
form.submit();

});




});

</script>


<%@include file="../includes/footer.jsp" %>

 

 

 

---

코드 더 간결하게!

 

스프링 UriComponentsBuilder를 이용한 링크 생성