mysql 페이징2

select R1.* FROM(
SELECT * FROM tbl_board 
        order by bno desc
) R1
LIMIT 10 OFFSET 0;

 

BoardMapper.xml에  Criteria를 이용하여 적용시켜보자.

 

 

Criteria.java (domain) - DTO 데이터전달용

-int pageNum

-int amount

 

문제점::

 

	<select id="getListWithPaging" resultType="org.moominzero.domain.BoardVO">
		<![CDATA[select R1.* FROM(
			SELECT * FROM tbl_board 
     		   order by bno desc
				) R1
			LIMIT #{amount} OFFSET (#{pageNum}-1) * #{amount};]]>
	
	</select>

 

이런식으로 xml을 작성할경우

테스트

select R1.* FROM( SELECT * FROM tbl_board order by bno desc ) R1 LIMIT 10 OFFSET (2-1) * 10; 

 

구문에서 에러가 난다.       (연산을 못하는듯하다.)

 

 

해결방법

 

criteria 클래스에

skipCount 라는 변수를 선언

 

package org.moominzero.domain;

import lombok.Getter;
import lombok.Setter;
import lombok.ToString;

@Getter
@Setter
@ToString
public class Criteria {

	private int pageNum;
	private int amount;
	
	private int skipCount;
	
	
	public Criteria() {
		this(1,10);
	}
	
	public Criteria(int pageNum, int amount) {
		this.pageNum = pageNum;
		this.amount = amount;
		this.skipCount = (pageNum -1) * amount;
	}
	
	public int getSkipCount() {
	        return (this.pageNum - 1) * this.amount;
	 }
	
}

 

 

테스트::

 

	//페이징 테스트
	@Test
	public void testPaging() {
		Criteria cri = new Criteria();
		//10개씩 2페이지
		cri.setPageNum(2);
		cri.setAmount(10);
		
		List<BoardVO> list = mapper.getListWithPaging(cri);
		
		list.forEach(board -> log.info(board));
		
	}

알맞게 실행되었다!

INFO : jdbc.sqltiming - select R1.* FROM( SELECT * FROM tbl_board order by bno desc ) R1 LIMIT 10 OFFSET 10 
 {executed in 129 msec}