[B -2-23] 페이징 화면 처리 4

마이바티스에서 전체 데이터의 개수 처리

 

전에 임시로 총 레코드 건수를 111 건으로 처리를 했었다. 

임시가 아닌 실제 레코드 건수를 조회할 필요가 있다.

최종적으로는 데이터베이스에 있는 실제 모든 게시물의 수를 구해서 PageDTO를 구성할 때 전달해줘야 한다.

 

---

src/main/java

com.spms.controller

BoardController.java

@GetMapping list()

 

package com.spms.controller;

import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.servlet.mvc.support.RedirectAttributes;

import com.spms.domain.BoardVO;
import com.spms.domain.Criteria;
import com.spms.domain.PageDTO;
import com.spms.service.BoardService;

import lombok.AllArgsConstructor;
import lombok.extern.log4j.Log4j;

@Controller
@Log4j
@RequestMapping("/board/*")
@AllArgsConstructor
public class BoardController {

	private BoardService boardService;

	@GetMapping("/list")
	public void list(Criteria cri, Model model) {
		log.info("list" + cri);
		boardService.getList(cri).forEach(board -> log.info(board));
		model.addAttribute("list", boardService.getList(cri));
		 
		int total = boardService.getTotal(cri); 
		log.info("total : " + total);
		model.addAttribute("pageMaker", new PageDTO(cri, total));

	}

	@GetMapping("/register")
	public void register() {

	}

	@PostMapping("/register")
	public String register(BoardVO board, RedirectAttributes rttr) {

		log.info("register : " + board);
		boardService.register(board);
		rttr.addFlashAttribute("result", board.getBno());
		return "redirect:/board/list";
	}

	@GetMapping({ "/get", "/modify" })
	public void get(@RequestParam("bno") Long bno, @ModelAttribute("cri") Criteria cri, Model model) {
		log.info("/get or modify");
		model.addAttribute("board", boardService.get(bno));
	}

	@PostMapping("/modify")
	public String modify(BoardVO board, @ModelAttribute("cri") Criteria cri, RedirectAttributes rttr) {
		log.info("modify:" + board);

		if (boardService.modify(board)) {
			rttr.addFlashAttribute("result", "success");
		}

		rttr.addAttribute("pageNum", cri.getPageNum());
		rttr.addAttribute("amount", cri.getAmount());

		return "redirect:/board/list";
	}

	@PostMapping("/remove")
	public String remove(@RequestParam("bno") Long bno, @ModelAttribute("cri") Criteria cri, RedirectAttributes rttr) {
		log.info("remove..." + bno);
		if (boardService.remove(bno)) {
			rttr.addFlashAttribute("result", "success");
		}

		rttr.addAttribute("pageNum", cri.getPageNum());
		rttr.addAttribute("amount", cri.getAmount());

		return "redirect:/board/list";
	}

}

 

---

src/java/main

com.spms.service

BoardService.java 인터페이스

getTotal()

package com.spms.service;

import java.util.List;

import com.spms.domain.BoardVO;
import com.spms.domain.Criteria;

public interface BoardService {
	public int getTotal(Criteria cri);
	public List<BoardVO> getList(Criteria cri);
	public void register(BoardVO board);
	public BoardVO get(Long bno);
	public boolean modify(BoardVO board);
	public boolean remove(Long bno);
}

 

---

src/java/main

com.spms.service

BoardServiceImpl.java

getTotal()

 

package com.spms.service;

import java.util.List;

import org.springframework.stereotype.Service;

import com.spms.domain.BoardVO;
import com.spms.domain.Criteria;
import com.spms.mapper.BoardMapper;

import lombok.AllArgsConstructor;
import lombok.extern.log4j.Log4j;

@Log4j
@Service
@AllArgsConstructor
public class BoardServiceImpl implements BoardService {
	
	private BoardMapper boardMapper;

	@Override
	public int getTotal(Criteria cri) {
		log.info("get total count");
		return boardMapper.getTotalCount(cri);
	}
	
	@Override
	public List<BoardVO> getList(Criteria cri) {
		log.info("get List with Criteria : " + cri);
		return boardMapper.getListWithPaging(cri);
	}
	
	@Override
	public void register(BoardVO board) {
		log.info("register......" + board);
		boardMapper.insertSelectKey(board);
	}

	@Override
	public BoardVO get(Long bno) {
		log.info("get......" + bno);
		return boardMapper.read(bno);
	}

	@Override
	public boolean modify(BoardVO board) {
		log.info("modify......" + board);
		return boardMapper.update(board) == 1;
	}

	@Override
	public boolean remove(Long bno) {
		log.info("remove...." + bno);
		return boardMapper.delete(bno) == 1;
	}

}

 

---

src/java/main

com.spms.service

BoardMapper.java 인터페이스

getTotalCount()

 

package com.spms.mapper;

import java.util.List;
import com.spms.domain.BoardVO;
import com.spms.domain.Criteria;

public interface BoardMapper {
	public List<BoardVO> getListWithPaging(Criteria cri);
	public Long insertSelectKey(BoardVO board);
	public BoardVO read(Long bno);
	public int update(BoardVO board);
	public int delete(Long bno);
	public int getTotalCount(Criteria cri);
}

 

---

src/main/resources

com

spms

mapper

BoardMapper.xml

getTotalCount()

 

<?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="com.spms.mapper.BoardMapper">

	<!-- 페이징 목록 조회 -->
	<select id="getListWithPaging" resultType="com.spms.domain.BoardVO">
	<![CDATA[
		select 
			BNO, TITLE, "CONTENT", WRITER, REGDATE, UPDATEDATE
		from (
		    select /*+ INDEX_DESC(tbl_board pk_board) */
		    	rownum RNUM, BNO, TITLE, "CONTENT", WRITER, REGDATE, UPDATEDATE
		    from TBL_BOARD
		    where rownum <= #{pageNum} * #{amount}
		)
		where RNUM > (#{pageNum} - 1) * #{amount}
	]]>
	</select>
	
	<!-- 전체 레코드 구하기 -->
	<select id="getTotalCount" resultType="int">
		select count(*)
		from TBL_BOARD
		where bno > 0
	</select>
	
	<!-- 등록 (선택키 활용) -->
	<insert id="insertSelectKey">
		<selectKey keyProperty="bno" order="BEFORE" resultType="long">
			select SEQ_BOARD.nextval 
			from DUAL
		</selectKey>
		insert into TBL_BOARD (
			BNO, TITLE, CONTENT, WRITER
		)
		values (
			#{bno}, #{title}, #{content}, #{writer}
		)
	</insert>
	
	<!-- 조회 -->
	<select id="read" resultType="com.spms.domain.BoardVO">
		select * 
		from TBL_BOARD 
		where BNO = #{bno}
	</select>
	
	<!-- 수정 -->
	<update id="update">
		update TBL_BOARD set 
				TITLE = #{title},
				CONTENT = #{content},
				WRITER = #{writer},
				UPDATEDATE = SYSDATE
		where BNO = #{bno}
	</update>
	
	<!-- 삭제 -->
	<delete id="delete">
		delete TBL_BOARD
		where BNO = #{bno}
	</delete>
	
</mapper>

 

select count(*)
from TBL_BOARD
where bno > 0

인덱스를 활용해서 전체 레코드를 구한다.

 

---

이상으로 게시물의 등록, 수정, 조회, 페이징 처리가 완료되었다.