교육 55일차

검색,페이지 M으로 실습

추가 조건 글 5개씩 출력, 페이지도 5개

TestMController

package com.spring.sample.web.test.controller;

import java.util.HashMap;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.servlet.ModelAndView;

import com.spring.sample.common.bean.PagingBean;
import com.spring.sample.common.service.IPagingService;
import com.spring.sample.web.test.service.ITestMService;

@Controller
public class TestMController {
	//객체를 주입 받겠다.
	@Autowired
	public IPagingService iPagingService;

	@Autowired
	public ITestMService iTestMService;
	
	@RequestMapping(value="/testMList")
	public ModelAndView testMService(
			@RequestParam HashMap<String, String> params,
			ModelAndView mav) throws Throwable{
		System.out.println("궁금"+params);
		//현재 페이지
		int page = 1;
		if(params.get("page") != null) {
				page = Integer.parseInt(params.get("page"));
		}
		
		//총 게시글 수
		int maxCount = iTestMService.getMCnt(params);
		int viewCnt= 5;
		int pageCnt= 5;
		//페이징 정보 취득
		PagingBean pb = iPagingService.getPagingBean(page, maxCount, viewCnt, pageCnt);
		
		//글번호 p 없으면 글번호
		// 게시글 시작번호, 종료번호 할당
		//빈형식으로 취득
		params.put("startCnt",Integer.toString(pb.getStartCount()));
		params.put("endCnt",Integer.toString(pb.getEndCount()));
		

		System.out.println(params);
		List<HashMap<String, String>>list
					= iTestMService.getMList(params);
		mav.addObject("list1", list);
		mav.addObject("pb", pb);
		mav.addObject("page", page);
		mav.addObject("maxCount", maxCount);
		
		
		mav.setViewName("test/testMList");
			
			return mav;
	}
	
	@RequestMapping(value="/testMDetail")
	public ModelAndView testm2(
			@RequestParam HashMap<String, String> params,
			ModelAndView mav) throws Throwable{
		if(params.get("mNo") != null) {
			HashMap<String, String> data
						= iTestMService.getM(params);
			
		mav.addObject("data", data);
		
		mav.setViewName("test/testMDetail");
		} else {
			mav.setViewName("redirect:testMList");
		}
		return mav;
		
	}
	
	@RequestMapping(value="/testMAdd")
	public ModelAndView testMAdd(ModelAndView mav) {
		mav.setViewName("test/testMAdd");
		
		return mav;
	}
	
	@RequestMapping(value="/testMAdds")
	public ModelAndView testMAdds(
			@RequestParam HashMap<String, String> params,
			ModelAndView mav){
				
	
		try {
			int cnt = iTestMService.addM(params);
			
			if(cnt > 0) {
				mav.setViewName("redirect: testMList");
			} else {
				mav.addObject("msg", "문제발생");
				mav.setViewName("redirect: testMAdds");
			}
		} catch (Throwable e) {
			e.printStackTrace();
			mav.addObject("msg", "오류발생");
			mav.setViewName("test/testMAdds");
			
			
		}
		
		
		return mav;
		 
	}
	@RequestMapping(value="/testMUpdate")
	public ModelAndView testMUpdate(
			@RequestParam HashMap<String, String> params,
			ModelAndView mav) throws Throwable{
		if(params.get("mNo") != null) {
			HashMap<String, String> data
						= iTestMService.getM(params);
			
		mav.addObject("data", data);
		
		mav.setViewName("test/testMUpdate");
		} else {
			mav.setViewName("redirect:testMList");
		}
		return mav;
		
	}
	
	@RequestMapping(value="/testMUpdates")
	public ModelAndView testMUpdates(
			@RequestParam HashMap<String, String> params,
			ModelAndView mav){
				
	
		try {
			int cnt = iTestMService.updateM(params);
			
			mav.addObject("cnt", cnt);
				
			
		} catch (Throwable e) {
			e.printStackTrace();
			mav.addObject("msg", "오류발생");
			
		}
		mav.setViewName("test/testMUpdates");
		
		
		return mav;
	}
	
	
	@RequestMapping(value="/testMDeletes")
	public ModelAndView testMdeltes(
			@RequestParam HashMap<String, String> params,
			ModelAndView mav){
				
	
		try {
			int cnt = iTestMService.deleteM(params);
			
			if(cnt > 0) {
				mav.setViewName("redirect: testMList");
			} else {
				mav.addObject("msg", "문제발생");
				mav.setViewName("redirect: testMDeletes");
			}
		} catch (Throwable e) {
			e.printStackTrace();
			mav.addObject("msg", "오류발생");
			mav.setViewName("test/testMDeletes");
			
			
		}
		
		return mav;
		 
	}
}//class end

ITestMService

package com.spring.sample.web.test.service;

import java.util.HashMap;
import java.util.List;

public interface ITestMService {

	public List<HashMap<String, String>> getMList(HashMap<String, String> params) throws Throwable;

	public HashMap<String, String> getM(HashMap<String, String> params) throws Throwable;

	public int addM(HashMap<String, String> params) throws Throwable;

	public int updateM(HashMap<String, String> params) throws Throwable;

	public int deleteM(HashMap<String, String> params) throws Throwable;

	public int getMCnt(HashMap<String, String> params) throws Throwable;

}

TestMService

package com.spring.sample.web.test.service;

import java.util.HashMap;
import java.util.List;

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

import com.spring.sample.web.test.dao.ITestMDao;

@Service
public class TestMService implements ITestMService {
	
	@Autowired
	public ITestMDao iTestMDao;
	
	@Override
	public List<HashMap<String, String>> getMList(HashMap<String, String> params) throws Throwable {
		
		return iTestMDao.getMList(params);
	}

	@Override
	public HashMap<String, String> getM(HashMap<String, String> params) throws Throwable {
		
		return iTestMDao.getM(params);
	}

	@Override
	public int addM(HashMap<String, String> params) throws Throwable {
		return iTestMDao.addM(params);
	}

	@Override
	public int updateM(HashMap<String, String> params) throws Throwable {
		return iTestMDao.updateM(params);
	}

	@Override
	public int deleteM(HashMap<String, String> params) throws Throwable {
		return iTestMDao.deleteM(params);
	}


	@Override
	public int getMCnt(HashMap<String, String> params) throws Throwable {
		return iTestMDao.getMCnt(params);
	}

}

ITestMDao

package com.spring.sample.web.test.dao;

import java.util.HashMap;
import java.util.List;

public interface ITestMDao {

	public List<HashMap<String, String>> getMList(HashMap<String, String> params) throws Throwable;

	public HashMap<String, String> getM(HashMap<String, String> params) throws Throwable;

	public int addM(HashMap<String, String> params) throws Throwable;

	public int updateM(HashMap<String, String> params) throws Throwable;

	public int deleteM(HashMap<String, String> params)  throws Throwable;

	public int getMCnt(HashMap<String, String> params)throws Throwable;

}

TestMDao

package com.spring.sample.web.test.dao;

import java.util.HashMap;
import java.util.List;

import org.apache.ibatis.session.SqlSession;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

@Repository
public class TestMDao implements ITestMDao {

	@Autowired
	public SqlSession sqlSession;
	
	@Override
	public List<HashMap<String, String>> getMList(HashMap<String, String> params) throws Throwable {
		
		return sqlSession.selectList("M.getMList",params);
	}

	@Override
	public HashMap<String, String> getM(HashMap<String, String> params) throws Throwable {
		
		return sqlSession.selectOne("M.getM", params);
	}

	@Override
	public int addM(HashMap<String, String> params) throws Throwable {
		
		return sqlSession.insert("M.addM", params);
	}

	@Override
	public int updateM(HashMap<String, String> params) throws Throwable {
		return sqlSession.update("M.updateM", params);
	}

	@Override
	public int deleteM(HashMap<String, String> params) throws Throwable {
		return sqlSession.delete("M.deleteM", params);
	}

	@Override
	public int getMCnt(HashMap<String, String> params) throws Throwable {
		return sqlSession.selectOne("M.getMCnt",params);
	}

}

M_SQL

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="M"><!-- namespace: 클래스명과동일 -->
	<!-- id: 메소드명과 동일 -->
	<!-- resultType: row 1줄의 형태를 지정 -->
	<!-- 쿼리 작성 시 ; 이 들어가면 실행 되지 않음 -->
	<select id="getMCnt" parameterType="hashmap" resultType="Integer">
		SELECT COUNT(*) AS CNT
		FROM M
		WHERE 1 = 1 
		<if test="searchTxt != null and searchTxt != ''">
			<choose>
		 		<when test="searchGbn ==0">
		 			AND M_NO LIKE '%' || #{searchTxt} ||'%'
				</when>
			</choose>
		</if>
	</select>
	
	
	<select id="getMList" parameterType="hashmap" resultType="hashmap">
		SELECT  M.M_NO,M.M_ID,M.M_NM,M.BIRTH,M.DT
		FROM (SELECT M_NO,M_ID,M_NM,TO_CHAR(M_BIRTH,'YY-MM-DD') AS BIRTH, TO_CHAR(M_JOINDT, 'YY-MM-DD') AS DT,
                ROW_NUMBER() OVER(ORDER BY M_NO DESC) AS RNUM
	    	  FROM M
	      	  WHERE 1=1
	      	  <if test="searchTxt != null and searchTxt != ''">
	       	  	<choose>
	       	  		<when test="searchGbn == 0">
	       	  				AND M_NO LIKE '%' || #{searchTxt} ||'%'
	       	   		</when>
	       	   </choose>
				<choose>
	       	  		<when test="searchGbn == 1">
	       	  				AND M_ID LIKE '%' || #{searchTxt} ||'%'
	       	   		</when>
	       	   </choose>
	       	   	<choose>
	       	  		<when test="searchGbn == 2">
	       	  				AND M_NM LIKE '%' || #{searchTxt} ||'%'
	       	   		</when>
	       	   </choose>
			 </if>
	        ) M
	 	WHERE M.RNUM BETWEEN #{startCnt} AND #{endCnt}
	</select>
	
	<select id="getM" parameterType="hashmap" resultType="hashmap">
	SELECT M_NO,M_ID,M_PW,M_NM,M_BIRTH,M_JOINDT
	FROM M
	WHERE M_NO = #{mNo}
	</select>
	<select id="addM" parameterType="hashmap">
	INSERT INTO M(M_NO, M_ID,M_PW, M_NM, M_BIRTH,M_JOINDT)
	VALUES(M_SEQ.NEXTVAL, #{mId}, #{mPw},#{mNm}, #{mBr}, SYSDATE)
	</select>
	
	<update id="updateM" parameterType="hashmap">
	UPDATE M SET M_PW = #{mPw}, M_NM = #{mNm}
	WHERE M_NO = #{mNo}
	</update>
	
	<delete id="deleteM" parameterType="hashmap">
	DELETE FROM M
	WHERE M_NO = #{mNo}
	</delete>
</mapper>

testMList

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib prefix="c"  uri="http://java.sun.com/jsp/jstl/core"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<script type="text/javascript"
		src="resources/script/jquery/jquery-1.12.4.min.js"></script>
<script type="text/javascript">
$(document).ready(function(){
	$("tbody").on("click", "tr", function(){
		$("#mNo").val($(this).attr("name"));
		$("#goForm").submit();
	}); // tbody end
	
	$("#addBtn").on("click",function(){
		location.href = "testMAdd";
	}); // addBtn end
	
	$("#searchBtn").on("click",function(){
		$("#page").val(1);
		$("#searchForm").submit();
	}); //search end
	
	$("#pagingWrap").on("click","span",function(){
		$("#page").val($(this).attr("name"));
		$("#searchForm").submit();
	}); //pagingWrap end
	
	//스크립트은 사용자에게 보여진다. 코어태그는 사용자가 확인이 어려움 
	if("${param.searchGbn}" !="") {
		$("#searchGbn").val("${param.searchGbn}");
	}
	
}); //ready end
</script>
</head>
<body>
<form action="testMDetail" id="goForm" method="post">
	<input type="hidden" id="mNo" name="mNo"/>
</form>
<form action="testMList" id="searchForm" method="post">
<input type="hidden" id="page" name="page" value="${page}">
<!--name은 컨트롤러에 넘겨 줄 때 키값 -->
<select name="searchGbn" id="searchGbn"> 
		<option value="0">회원번호</option>
		<option value="1">아이디</option>
		<option value="2">이름</option>
</select>
<input type="text" name="searchTxt" value="${param.searchTxt}">
<input type="button" value="검색" id="searchBtn"/>
<input type="button" value="작성" id="addBtn"/>
</form>
<table id="mTable">
	<thead>
		<tr>
			<th>회원번호</th>
			<th>아이디</th>
			<th>비밀번호</th>
			<th>이름</th>
			<th>생년월일</th>
			<th>가입일</th>
		</tr>
	</thead>
	<tbody>
		<c:forEach var="data" items="${list1}">
			<tr name="${data.M_NO}">
				<td>${data.M_NO}</td>
				<td>${data.M_ID}</td>
				<td>${data.M_PW}</td>
				<td>${data.M_NM}</td>
				<td>${data.M_BIRTH}</td>
				<td>${data.M_JOINDT}</td>
			</tr>
		</c:forEach>
	</tbody>
</table>
<div id="pagingWrap">
<span name="1">처음</span>
<!-- 이전 페이지 -->
<c:choose>
	<c:when test="${page eq 1}">
		<span name="1">이전</span>
	</c:when>
	<c:otherwise>
		<span name="${page-1}">이전</span>
	</c:otherwise>
</c:choose>
<!-- 페이지들 -->
<c:forEach var="i" begin="${pb.startPcount}" end="${pb.endPcount}" step="1">
	<!-- 현재페이지인 경우 볼드 처리 -->
	<c:choose>
		<c:when test="${i eq page}">
			<span name="${i}"><b>${i}</b></span>
		</c:when>
		<c:otherwise>
			<span name="${i}">${i}</span>
		</c:otherwise>
	</c:choose>
</c:forEach>
<!-- 다음페이지 -->
<c:choose>
	<c:when test="${page eq pb.maxPcount}">
				<span name="${pb.maxPcount}"><b>다음</b></span>
	</c:when>
	<c:otherwise>
			<span name="${page +1}">다음</span>
	</c:otherwise>
</c:choose>
<!-- 마지막 페이지 -->
<span name="${pb.maxPcount}">마지막</span>
</div>
</body>
</html>

DB

INSERT INTO M(M_NO,M_ID,M_PW,M_NM,M_BIRTH,M_JOINDT)
VALUES (2,'SPRING','1234','김태리',SYSDATE,SYSDATE)
;
SELECT  M.M_NO,M.M_ID,M.M_NM,M.BIRTH,M.DT
FROM (SELECT M_NO,M_ID,M_NM,TO_CHAR(M_BIRTH,'YY-MM-DD') AS BIRTH, TO_CHAR(M_JOINDT, 'YY-MM-DD') AS DT,
                ROW_NUMBER() OVER(ORDER BY M_NO DESC) AS RNUM
      FROM M
      WHERE 1=1
        -- AND M_ID LIKE '%' || 'P' ||'%'
        -- AND M_ID LIKE '%' || 'P' ||'%'
        -- AND M_NM LIKE '%' || 'P' ||'%' 
        ) M
WHERE M.RNUM BETWEEN 1 AND 20
;

SELECT COUNT(*)
FROM M
;

COMMIT;

COMMIT 필수 !!!

출력

집가서 S 테이블로 실습하자 !! 할 수있다.
오늘의 수확 커스텀가능한 페이징으로 시도해서 성공했다.