Mapper

 

		<!-- 레코드 개수 계수 -->
	<select id="mysqlListSearchCount" resultType="int">
	   select count(*)  from v_free_board  where bno > 0
	   	 <include refid="search"></include>
	</select>
	
	
<sql id="search">
		
		<if test="search_option !=null">					
			<if test="search_option =='title' || search_option =='writer' || search_option=='content' || search_option=='username' ">
				and   ${search_option} like  concat( '%', #{keyword}, '%' )
			</if>
		</if>
	
	<if test="search_option == 'all'">
			 and   ( title like  concat( '%', #{keyword}, '%' ) )		  
			  or  (  writer like  concat( '%', #{keyword}, '%' ) )
			     or ( content like  concat( '%', #{keyword}, '%' )  )
	</if>
			
</sql>	
	
	<select id="mysqlListSearchCriteria" resultType="BoardVO">
	select * from v_free_board where bno > 0 
			
		 <include refid="search"></include>
		 
		order by bno desc, regdate desc  limit  #{pageStart}, #{perPageNum}
	</select>

 

Criteria

package com.macaronics.www.util.mysql;

public class Criteria {

	private Integer page;
	private Integer perPageNum;
	
	public Criteria(){
		this.page=1;
		this.perPageNum=10;
	}
	
	public void setPage(int page){
		if(page <=0){
			this.page =1;
			return ;
		}
		this.page =page;
	}
	

	public void setPerPageNum(int perPageNum){
		if(perPageNum <=10 || perPageNum >100){
			this.perPageNum=10;
			return ;
		}
		this.perPageNum =perPageNum;
	}
	
	public Integer getPage() {
		return page;
	}
	
	public Integer getPerPageNum() {
		return perPageNum;
	}
	
	
	public Integer getPageStart(){
		
		return (this.page-1) * perPageNum;
	}
	
	
	
}

PageMaker

package com.macaronics.www.util.mysql;

import org.springframework.web.util.UriComponents;
import org.springframework.web.util.UriComponentsBuilder;

import lombok.Data;

@Data
public class PageMaker {

	private int totalCount;
	private int startPage;
	private int endPage;
	private boolean prev;
	private boolean next;
	
	private int displayPageNum =10;
	private Criteria cri;
	
	private int endFinishPage;
	
	
	public void setCri(Criteria cri) {
		this.cri = cri;
	}
	
	public void setTotalCount(int totalCount) {
		this.totalCount = totalCount;
		
		calcData();
	}
	
	
	private void calcData(){
		endPage =(int)(Math.ceil(cri.getPage()/(double)displayPageNum) * displayPageNum);
		startPage =(endPage -displayPageNum) +1;
		
		endFinishPage =(int)(Math.ceil(totalCount)/(double)cri.getPerPageNum());
	
		if(endPage >endFinishPage){
			endPage =endFinishPage;
		}
	
		prev =startPage ==1 ? false :true;
		next =endPage * cri.getPerPageNum() >= totalCount ? false :true;
	}
	
	
	
	public String mysqlSearchQuery(int page){
		
		UriComponents  uriComponents =
				UriComponentsBuilder.newInstance()
				.queryParam("page", page)
				.queryParam("perPageNum", cri.getPerPageNum())
				.queryParam("search_option", ((SearchCriteria)cri).getSearch_option() )
				.queryParam("keyword", ((SearchCriteria)cri).getKeyword() )
				.build();
		
		return uriComponents.toUriString();
	}
	
	

}

SearchCriteria

package com.macaronics.www.util.mysql;

import org.springframework.web.util.UriComponents;
import org.springframework.web.util.UriComponentsBuilder;

import lombok.Data;


public class SearchCriteria extends Criteria {

	private String search_option;
	private String keyword;
	
	

	
	public String getSearch_option() {
		return search_option;
	}
	public void setSearch_option(String search_option) {
		this.search_option = search_option;
	}
	public String getKeyword() {
		return keyword;
	}
	public void setKeyword(String keyword) {
		this.keyword = keyword;
	}
	
	
	@Override
	public String toString() {
		return "SearchCriteria [search_option=" + search_option + ", keyword=" + keyword + "]";
	}
	
	public String mysqlSearchQuery(int page){
		
		UriComponents  uriComponents =
				UriComponentsBuilder.newInstance()
				.queryParam("page", page)
				
				.queryParam("search_option",search_option )
				.queryParam("keyword", keyword )
				.build();
		
		return uriComponents.toUriString();
	}

	
}

 

Controller

	
	@Inject
	private BoardService boardService;
	
	
	@RequestMapping(value="/listAll.do", method=RequestMethod.GET)
	public ModelAndView boardList(@ModelAttribute("cri") SearchCriteria cri){

		
		ModelAndView mv =null;
		
		if(SqlServerEnvironment.SQL.equals("ORACLE")){
			//Orcle DB 일 경우
			PageAndSearch pas =new PageAndSearch();
			
			pas.setCurPage(cri.getPage());
			pas.setSearch_option(cri.getSearch_option());
			pas.setKeyword(cri.getKeyword());
			mv=oraclePageList(pas);
		}else{
			// MYSQL DB 일 경우
			mv =mysqlPageList(cri);
		}	
		return mv;
	}
	
	
	// 오라클 페이지 리스트 페이지 
	public ModelAndView  oraclePageList(PageAndSearch pas){
		
			ModelAndView mv =new ModelAndView();
		
			int count =boardService.countArticle(pas.getSearch_option(), pas.getKeyword());
			
			//페이지 나누기 관련 처리
			if(pas.getCurPage()==null){pas.setCurPage(1);}
			
	
			Pager pager =new Pager(count, pas.getCurPage());
			int start =pager.getPageBegin();
			int end=pager.getPageEnd();
			
			
			logger.info(" ****************   " + pas.toString() + "    메서드  : " + pas.searchQuery(1) +
					"  count : " + count + " start : " + start + "  end  : " + end);
			
			List<BoardVO> list=boardService.boardList(start, end, pas.getSearch_option(), pas.getKeyword());
			
			Map<String, Object> map =new HashMap<>();
			map.put("countList", count);
			map.put("list", list);
			map.put("pager", pager);
			/*map.put("search_option", pas.getSearch_option());
			map.put("keyword", pas.getKeyword());
			*/
				
			mv.addObject("map", map);
			mv.addObject("pageAndSearch", pas);
			
			mv.setViewName(JSP_PAGE+"list");
			
			return mv;
	}
	
	
	//Mysql 페이지 리스트 메소드
	public ModelAndView  mysqlPageList(SearchCriteria cri){
		
		ModelAndView mv =new ModelAndView();
		
		mv.addObject("list", boardService.mysqlListSearchCriteria(cri));
		
		PageMaker pageMaker =new PageMaker();
		pageMaker.setCri(cri);
		
		int count =boardService.mysqlListSearchCount(cri);
		pageMaker.setTotalCount(count);
		
		
		mv.addObject("pageMaker", pageMaker);
		mv.addObject("countList", count);
		mv.setViewName(JSP_PAGE+"mysql_list");
		return mv;
	}
	
	
	

view

 

 

  <!-- Start Blog  -->
  <section id="aa-blog">
    <div class="container">
      <div class="row">
        <div class="col-md-12">
          <div class="aa-blog-area">
            <div class="row">
              <div class="col-md-8">
                <div class="aa-blog-content">
                  <div class="row">
                   <div class="box">
            <div class="box-header">
              <h3 class="box-title" style="margin-bottom: 10px;">Free 게시판</h3>

              <div class="box-tools">
              <form method="get" action="/board/listAll.do"  name="serarchForm">
              		<div class="row" style="margin-top: 20px; margin-bottom: 20px;">
    				<div class="col-md-4">
            	      
            	      <select name="search_option" class="form-control"  >
            	        <option value=""   <c:if test="${ cri.search_option =='' }">selected="selected"</c:if>>----</option>
	                 	<option value="writer"   <c:if test="${ cri.search_option =='writer' }">selected="selected"</c:if>>아이디</option>
	                 	<option value="username"   <c:if test="${ cri.search_option =='username' }">selected="selected"</c:if>>이름</option>
	                 	<option value="content" <c:if test="${ cri.search_option =='content' }">selected="selected"</c:if>>내용</option>
	                 	<option value="title" <c:if test="${ cri.search_option =='title' }">selected="selected"</c:if>>제목</option>
	                 	<option value="all" <c:if test="${ cri.search_option =='all' }">selected="selected"</c:if>>이름+내용+제목</option>
	                  </select>
	                  
	                  
	                  </div>
	               <div class="col-md-4">
				    <input type="text" class="form-control" name="keyword" value="${cri.keyword }">
				    </div>
				    <div class="col-md-4">
  					<button type="submit" class="btn btn-warning">검색</button>
  					</div>
				 </div>
				 
              </form>
      <c:if test="${ not empty  loginUser}" >      
       <p class="input-group input-group-sm" style="margin-top :10px;"><button id="btnWrite" class="btn btn-primary" >글쓰기</button></p>
	</c:if>            
            </div>
              
             
              <p>  
               <span class="label label-danger"> ${countList }</span> 개의 게시물이 있습니다.
              </p>
              <p>
              
              
              
            </div>
            <!-- /.box-header -->
            <div class="box-body table-responsive no-padding">
              <table class="table table-hover">
                <tbody><tr>
                  <th>번호</th>
                  <th>제목</th>
                  <th>글쓴이</th>
                  <th>등록일</th>
                  <th>조회수</th>
                </tr>
               
       <c:forEach items="${list }"  var="row">

                <tr>
                  <td>${row.bno }</td>
                  <td class="rowTitle"> <a href="/board/view.do${pageMaker.mysqlSearchQuery(cri.page) }&bno=${row.bno }">${row.title }</a></td>
                  <td>${ row.writer }</td>
                  <td><span class="label label-success">
                  <fmt:formatDate value="${ row.regdate }"  pattern="yyyy-MM-dd hh:mm"/></span>
                  </td>
                  <td> <span class="badge bg-red">${ row.viewcnt }</span></td>
                </tr>
       
       </c:forEach>
             
             
              </tbody></table>
            </div>
            <!-- /.box-body -->
          </div>
              
             
                  </div>
                  <div class="row">
                    <div class="col-md-12">
                      <div class="aa-properties-content-bottom">
                        <nav>
                    
                                       
                    
                          <ul class="pagination">
                           
                           <c:if test="${cri.page  > 1 }">
                               <li>
                               <a aria-label="Previous" href="javascript:list('${pageMaker.mysqlSearchQuery(1) }')">
                                <span aria-hidden="true">시작</span>
                              </a>
                              </li>
                           </c:if>
                           
                           <c:if test="${pageMaker.prev }">
                            <li>
                              <a aria-label="Previous" href="javascript:list('${pageMaker.mysqlSearchQuery(pageMaker.startPage-1) }')">
                                <span aria-hidden="true">«</span>
                              </a>
                            </li>
                            </c:if>
   
                          <c:forEach begin="${ pageMaker.startPage }"  end="${ pageMaker.endPage }" var="page">
                           	<c:choose>
                           		<c:when test="${ page ==cri.page }">
                           			<li class="active"> 
                            		  <a href="javascript:list('${pageMaker.mysqlSearchQuery(page) }')" >${page}</a>
                            		</li>
                           		</c:when>
                           		<c:otherwise>
                           			<li > 
                            		  <a href="javascript:list('${pageMaker.mysqlSearchQuery(page) }')" >${page}</a>
                            		</li>	
                           		</c:otherwise>
                           	</c:choose>
                           
                          </c:forEach>
                    
           				<c:if test="${ pageMaker.next && pageMaker.endPage >0 }">
                            <li>
                              <a aria-label="Next" href="javascript:list('${ pageMaker.mysqlSearchQuery(pageMaker.endPage +1) }')">
                                <span aria-hidden="true">»</span>
                              </a>
                            </li>
                        </c:if> 
                        <c:if test="${pageMaker.next && pageMaker.endPage >0 }">
                          <li>
                              <a aria-label="Next" href="javascript:list('${ pageMaker.mysqlSearchQuery(pageMaker.endFinishPage) }')">
                                <span aria-hidden="true">끝</span>
                              </a>
                            </li>
                        </c:if>    
                 
                          </ul>
                      
                      </nav>
                      </div>
                    </div>
                  </div>
                </div>
              
           
              </div>
              <!-- Start blog sidebar -->
              <div class="col-md-4">
                <aside class="aa-blog-sidebar">
                  <!-- Start single sidebar -->
                  <div class="aa-blog-sidebar-single">
                    <form action="">
                      <div class="aa-blog-search">
                        <input class="aa-search-text" type="text" placeholder="Search...">
                        <button class="aa-search-submit" type="submit">
                          <i class="fa fa-search"></i>
                        </button>
                      </div>
                    </form>
                  </div>
                  <!-- Start single sidebar -->
                  <div class="aa-blog-sidebar-single">
                    <h3>Categories</h3>
                    <ul class="aa-blog-catg">
                      <li>Properties <span>15000</span></li>
                      <li>Apartment <span>5000</span></li>
                      <li>Office  <span>3000</span></li>
                      <li>Residential <span>4500</span></li>
                      <li>Commercial <span>1000</span>
                          <ul>
                            <li>category 2.1 <span>50</span></li>
                            <li>category 2.2 <span>100</span></li>
                          </ul>
                      </li>
                      <li>Villa <span>800</span></li>
                      <li>Bungalow  <span>200</span></li>
                      <li>News <span>375</span></li>
                      <li>Reviews <span>458</span></li>
                    </ul>
                  </div>
                  <!-- Start single sidebar -->
                  <div class="aa-blog-sidebar-single">
                    <h3>Tags</h3>
                    <div class="tag-cloud">
                      <a href="#">Apartment</a>
                      <a href="#">Propery</a>
                      <a href="#">Residential</a>
                      <a href="#">Commercial</a>
                      <a href="#">Office</a>
                      <a href="#">Rent</a>
                      <a href="#">Sale</a>
                      <a href="#">Villa</a>
                    </div>                    
                  </div>
                  <!-- Start single sidebar -->
                  <div class="aa-blog-sidebar-single">
                    <h3>Recent Post</h3>
                    <div class="aa-blog-recent-post">
                      <div class="media">
                        <div class="media-left">
                          <a href="#">
                            <img alt="img" src="img/blog-img-3.jpg" class="media-object">
                          </a>
                        </div>
                        <div class="media-body">
                          <h4 class="media-heading"><a href="#">This is Title</a></h4>
                          <p>Lorem ipsum dolor sit amet, consectetur adipisicing elit.</p>                
                          <span>15 April, 16</span>
                        </div>              
                      </div>
                      <div class="media">
                        <div class="media-left">
                          <a href="#">
                            <img alt="img" src="img/blog-img-2.jpg" class="media-object">
                          </a>
                        </div>
                        <div class="media-body">
                          <h4 class="media-heading"><a href="#">This is Title</a></h4>
                          <p>Lorem ipsum dolor sit amet, consectetur adipisicing elit.</p>                
                          <span>15 April, 16</span>
                        </div>              
                      </div>
                      <div class="media">
                        <div class="media-left">
                          <a href="#">
                            <img alt="img" src="img/blog-img-1.jpg" class="media-object">
                          </a>
                        </div>
                        <div class="media-body">
                          <h4 class="media-heading"><a href="#">This is Title</a></h4>
                          <p>Lorem ipsum dolor sit amet, consectetur adipisicing elit.</p>                
                          <span>15 April, 16</span>
                        </div>              
                      </div>
                    </div>                    
                  </div>
                  <!-- Start single sidebar -->
                  <div class="aa-blog-sidebar-single">                    
                    <div class="aa-banner-ads">
                      <a href="#">
                        <img src="img/banner-ads.jpg" alt="banner img">
                      </a>
                    </div>                    
                  </div>
                </aside>
              </div>
            </div>
          </div>
        </div>
      </div>
    </div>
  </section>
  <!-- / Blog  -->



<script>
$(document).ready(function(){
	
	
	$("#btnWrite").click(function(){
		
		
		location.href="/board/write.do";
		
	});
	
	
	
});


function list(page){

	location.href='/board/listAll.do'+page;

}

</script>