20일차 3

 

Paging 2

 

쿼리문을 boardQuery.xml에 적용해야지.

<?xml version="1.0" encoding="UTF-8" ?>

<!DOCTYPE sqlMap      
    PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"      
    "http://ibatis.apache.org/dtd/sql-map-2.dtd">

<sqlMap>
	<!-- resultClass,parameterClass에 매번 "board.model.BoardDto"쓰기 귀찮아 -->
	<typeAlias alias="boardDto" type= "board.model.BoardDto"/>
	
	<cacheModel id="boardcache" type="LRU"> <!-- LRU알고리즘사용 -->
		<flushInterval hours="24" /> <!-- 기존의 cache를 24시간 유지 -->
		<property name="reference-type" value="WEAK" />	
		
		<!--  데이터가 변경 될 경우 메모리를 비우겠다  -->
		<flushOnExecute statement="insertBoard" />		
		<flushOnExecute statement="updateBoard" />		
		<flushOnExecute statement="deleteBoard" />		
		
	</cacheModel>	
	
	<select id="getList" resultClass="boardDto" cacheModel="boardcache"> 
	<![CDATA[
		SELECT A.*
		FROM (	SELECT 	ROWNUM AS rnum, seq, title, name, 
						TO_CHAR(writeday,'YYYY/MM/DD') AS writeday, 
						readcount 
				FROM board 
				ORDER BY seq DESC) A
		WHERE #startRow# <= rnum AND rnum <= #endRow#	
	]]>
	</select>
	
	<insert id="insertBoard" parameterClass="boardDto" >
		INSERT INTO board(seq, title, name, password, content)
		VALUES(boardseq.nextval, #title#,#name#, #password#, #content#)
		<!-- #name# : iBatis에서 자체적으로 getter를 호출하는거야 -->
		<!-- 주의! 쿼리문 마지막에 ';'넣으면 에러남 -->
	</insert>
	
	<select id="getView" resultClass="boardDto" parameterClass="int">
		SELECT seq, title, name, writeday, readcount, content 
		FROM board 
		WHERE seq = #seq#
	</select>
	
	<update id="updateBoard" parameterClass="boardDto" >
		UPDATE board SET 
				title=#title#, name=#name#, content=#content#
		WHERE seq=#seq# AND password=#password# 
	</update>
	
	<update id="updateReadCount" parameterClass="int" >
		UPDATE board SET 
				readcount = readcount+1 
		WHERE seq = #seq#
	</update>
	
	<delete id="deleteBoard" parameterClass="boardDto">
		DELETE FROM board 
		WHERE seq=#seq# AND password=#password# 
	</delete>
	
	<select id="isView" parameterClass="int" resultClass="int">
	<![CDATA[
		SELECT count(*) 
		FROM board 
		WHERE seq=#seq#
	]]>
	</select>	
</sqlMap>

 

iBatis에서 두개 이상의 값을 넘길 때는 클래스에 집어넣으랬잖아. startRow, endRow넘기기 위한 Page class 만들어

package ibatis;

public class Page {
	
	private int startRow;	//실전에선 long으로 바꾸는게 낫겠지.
	private int endRow;
	
	public int getStartRow() {
		return startRow;
	}
	public void setStartRow(int startRow) {
		this.startRow = startRow;
	}
	public int getEndRow() {
		return endRow;
	}
	public void setEndRow(int endRow) {
		this.endRow = endRow;
	}

}

 

BoardAction클래스 Paging 기능 구현

get방식으로 받아 온 pg로 startRow, endRow생성 후 board.getList에 넘겨줘.

package controller;

import ibatis.Page;

import java.util.List;

import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.servlet.ModelAndView;

import board.model.BoardDao;
import board.model.BoardDto;

@Controller
public class BoardAction {
	
	private BoardDao boardDao;
	
	public void setBoardDao(BoardDao boardDao) {
		this.boardDao = boardDao;
	}
	
	@RequestMapping("BoardList.do")
	public  ModelAndView boardList(@RequestParam(value="pg", defaultValue="1") int pg) {
					//get방식으로 넘겨받기. pg값이 안넘어오면 에러가 나는것을 
					//전에는 request.getParameter로 받아서 값이 안넘어오면 처리하는 부분을 구현했지만
					//이제 이렇게 defaultValue를 이용해서 간단하게 처리할 수도 있어.
		
		//Page구현
		int pageSize = 10;
		int startRow = (pg-1)*pageSize + 1;
		int endRow = pg*pageSize;
		
		//iBatis에서 두개 이상의 값을 넘길 때는 클래스에 집어넣으라고 했지.
		//그래서 만든게 Page클래스야
		Page p = new Page();
		p.setStartRow(startRow);
		p.setEndRow(endRow);

		List<BoardDto> list = boardDao.getList(p);
		
		ModelAndView mav = new ModelAndView();
		mav.setViewName("view/list");
		mav.addObject("list", list);
		
		return mav;		
	}
	
/* 생략 */	

 

연쇄적으로 BoardDao, QueryHandler, boardQuery.xml(parameterClass추가)까지 수정

package board.model;

import ibatis.Page;
import ibatis.QueryHandler;

import java.sql.SQLException;
import java.util.List;

public class BoardDao {

	public boolean insertBoard(BoardDto boardDto) {

		try {
			QueryHandler.insertBoard(boardDto); // insert는 에러나면 예외처리로 빠지겠지.
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}

	}

	public List<BoardDto> getList(Page p) {

		try {
			return QueryHandler.getList(p);  
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
	}

/* 생략 */

package ibatis;

import java.io.IOException;
import java.io.Reader;
import java.sql.SQLException;
import java.util.List;

import user.model.UserDto;
import board.model.BoardDto;

import com.ibatis.common.resources.Resources;
import com.ibatis.sqlmap.client.SqlMapClient;
import com.ibatis.sqlmap.client.SqlMapClientBuilder;


public class QueryHandler {

	private static SqlMapClient sqlMapper;

	static {
		try {
			Reader reader = Resources
					.getResourceAsReader("ibatis/SqlMapConfig.xml");
			// 파일스트림으로 파일을 읽어 저장하고있어.

			sqlMapper = SqlMapClientBuilder.buildSqlMapClient(reader);
			// 자원을 파싱해서 sqlMapper가 가지고 있어.

			reader.close();
		} catch (IOException e) {
			// Fail fast.
			throw new RuntimeException(
					"Something bad happened while building the SqlMapClient instance."
							+ e, e);
		}
	}

	@SuppressWarnings("unchecked")
	public static List<BoardDto> getList(Page p) throws SQLException {
		// TODO Auto-generated method stub
		return sqlMapper.queryForList("getList", p); // select는 queryForList 
	}

	public static void insertBoard(BoardDto boardDto) throws SQLException {
		// TODO Auto-generated method stub
		sqlMapper.insert("insertBoard", boardDto);

	}
	
/* 생략 */

<?xml version="1.0" encoding="UTF-8" ?>

<!DOCTYPE sqlMap      
    PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"      
    "http://ibatis.apache.org/dtd/sql-map-2.dtd">

<sqlMap>
	<!-- resultClass,parameterClass에 매번 "board.model.BoardDto"쓰기 귀찮아 -->
	<typeAlias alias="boardDto" type= "board.model.BoardDto"/>
	
	<cacheModel id="boardcache" type="LRU"> <!-- LRU알고리즘사용 -->
		<flushInterval hours="24" /> <!-- 기존의 cache를 24시간 유지 -->
		<property name="reference-type" value="WEAK" />	
		
		<!--  데이터가 변경 될 경우 메모리를 비우겠다  -->
		<flushOnExecute statement="insertBoard" />		
		<flushOnExecute statement="updateBoard" />		
		<flushOnExecute statement="deleteBoard" />		
		
	</cacheModel>	
	
	<select id="getList"  
			parameterClass="ibatis.Page" 
			resultClass="boardDto" 
			cacheModel="boardcache"> 
	<![CDATA[
		SELECT A.*
		FROM (	SELECT 	ROWNUM AS rnum, seq, title, name, 
						TO_CHAR(writeday,'YYYY/MM/DD') AS writeday, 
						readcount 
				FROM board 
				ORDER BY seq DESC) A
		WHERE #startRow# <= rnum AND rnum <= #endRow#	
	]]>
	</select>
	
	<insert id="insertBoard" parameterClass="boardDto" >
		INSERT INTO board(seq, title, name, password, content)
		VALUES(boardseq.nextval, #title#,#name#, #password#, #content#)
		<!-- #name# : iBatis에서 자체적으로 getter를 호출하는거야 -->
		<!-- 주의! 쿼리문 마지막에 ';'넣으면 에러남 -->
	</insert>
<!-- 생략 -->

 

http://localhost/spring3ibatisboard/BoardList.do

http://localhost/spring3ibatisboard/BoardList..do?pg=3

http://localhost/spring3ibatisboard/BoardList..do?pg=40

이런 식으로 테스트해 볼 수 있다.

.

.

.

.

.