# Mybatis使用之分頁
### 一：簡介
**注：示例基于mysql數據庫。Oracle可以自行測試。**
?? 使用Mybatis分頁主要有兩種方式、一種是將分頁參數傳遞到配置文件中、在寫sql的時候就做分頁。另一種是使用Mybatis的攔截器攔截需要分頁的sql語句、重新組裝成具有分頁功能的sql語句。
分頁查詢的關鍵在于查詢時需要告訴數據庫從第幾條開始取、取多少條記錄。也就是常用到Page對象（一般是方便傳遞參數、自己構建的實體類）的pageNumer、pageSize兩個主要參數。至于Page對象的總記錄數等是需要帶到前臺構造數據展示表格時使用的參數。
### 二：傳遞參數形式的分頁
### 2.1 關鍵點
?? 傳遞參數形式的分頁就是將分頁信息以參數的形式傳遞到映射文件中、這樣就可以在編寫具體的sql語句時實現分頁功能。這種方式的重點在于一般的查詢語句都會帶有一個或者多個查詢參數、而'select'標簽的parameterType只能接收一個值。所以需要我們將查詢參數與分頁參數組裝成一個Map作為參數傳遞到映射語句中。
不過前臺展示所需要的數據總數還需要另寫一條count查詢語句來獲取。多多少少會有些不便。
### 2.2 具體代碼
?? 映射文件：
~~~
<select id="selectAuthorWithPageParam"resultType="author" parameterType="hashMap">
SELECT
t.id,
t.username,
t.password,
t.email,
t.bio,
t.favourite_section favouriteSection
FROM author t
WHERE t.username = {username} AND t.password = {password} limit {page.dbIndex},{page.dbNumber}
</select>
~~~
?? 映射接口：
~~~
List<Author> selectAuthorWithPageParam(Map<String, Object>map);
~~~
?? 測試代碼：
~~~
@Test
public void testSelectAuthorWithPageParam() throws Exception {
Page page = new Page();
page.count();
Map<String, Object> map = new HashMap<>();
map.put("page", page);
map.put("username", "alien");
map.put("password","alien");
List<Author> authors =this.authorMapper.selectAuthorWithPageParam(map);
Assert.assertEquals(5, authors.size());
}
~~~
### 三：攔截器分頁
### 3.1 關鍵點
?? 攔截器實現分頁的關鍵之處在于、在需要分頁的sql語句執行之前、攔截下來并改造成具有分頁功能的sql語句（還可以查詢一下總數、設置到Page實體類中供前臺展示數據時使用）、然后繼續執行。
3.2 具體代碼
?? 說起來很簡單、但是短短的一句話卻需要理解許多東西才能達到目的。這里只挑一些重點步驟、具體攔截器工作原理、執行過程會在Mybatis深入過程詳細分析。
從使用角度來考慮過程如下（***對所有映射語句id以‘ByPage'結尾的做分頁處理***）：
#### 3.2.1 Page實體類：
~~~
package org.alien.mybatis.samples.entity;
/**
*Created by andy on 5/25/2015.<br>
*Version 1.0-SNAPSHOT<br>
*/
@SuppressWarnings("unused")
public class Page {
/**
* 總條數
*/
private int totalNumber;
/**
* 當前第幾頁
*/
private int currentPage;
/**
* 總頁數
*/
private int totalPage;
/**
* 每頁顯示條數
*/
private int pageNumber = 5;
/**
* 數據庫中limit的參數，從第幾條開始取
*/
private int dbIndex;
/**
* 數據庫中limit的參數，一共取多少條
*/
private int dbNumber;
/**
* Oracle 起始記錄行號
*/
private int rowNum;
/**
* Oracle 結束記錄行號
*/
private int rn;
/**
* 根據當前對象中屬性值計算并設置相關屬性值
*/
public void count() {
// 計算總頁數
int totalPageTemp = this.totalNumber / this.pageNumber;
int plus = (this.totalNumber % this.pageNumber) == 0 ? 0 : 1;
totalPageTemp = totalPageTemp + plus;
if(totalPageTemp <= 0) {
totalPageTemp = 1;
}
this.totalPage = totalPageTemp;
// 設置當前頁數
// 總頁數小于當前頁數，應將當前頁數設置為總頁數
if(this.totalPage < this.currentPage) {
this.currentPage = this.totalPage;
}
// 當前頁數小于1設置為1
if(this.currentPage < 1) {
this.currentPage = 1;
}
// 設置limit的參數
this.dbIndex = (this.currentPage - 1) * this.pageNumber;
this.dbNumber = this.pageNumber;
}
public int getTotalNumber() {
return totalNumber;
}
public void setTotalNumber(int totalNumber) {
this.totalNumber = totalNumber;
this.count();
}
public int getCurrentPage() {
return currentPage;
}
public void setCurrentPage(int currentPage) {
this.currentPage = currentPage;
}
public int getTotalPage() {
return totalPage;
}
public void setTotalPage(int totalPage) {
this.totalPage = totalPage;
}
public int getPageNumber() {
return pageNumber;
}
public void setPageNumber(int pageNumber) {
this.pageNumber = pageNumber;
this.count();
}
public int getDbIndex() {
return dbIndex;
}
public void setDbIndex(int dbIndex) {
this.dbIndex = dbIndex;
}
public int getDbNumber() {
return dbNumber;
}
public void setDbNumber(int dbNumber) {
this.dbNumber = dbNumber;
}
public int getRn() {
return (this.getCurrentPage() + 1) * this.getPageNumber();
}
public void setRn(int rn) {
this.rn = rn;
}
public int getRowNum() {
return this.getCurrentPage() * this.getPageNumber();
}
public void setRowNum(int rowNum) {
this.rowNum = rowNum;
}
~~~
#### 3.2.2 攔截器類：
~~~
packageorg.alien.mybatis.samples.interceptor;
importorg.alien.mybatis.samples.entity.Page;
importorg.apache.ibatis.executor.parameter.ParameterHandler;
importorg.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
importorg.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.plugin.*;
importorg.apache.ibatis.reflection.MetaObject;
importorg.apache.ibatis.reflection.SystemMetaObject;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Map;
import java.util.Properties;
/**
*Page interceptor.
*Support oracle and mysql.
*Created by andy on 5/25/2015.<br>
*Version 1.0-SNAPSHOT<br>
*/
@Intercepts({@Signature(type =StatementHandler.class, method = "prepare", args ={Connection.class})})
public class PageInterceptor implementsInterceptor {
private String dialect;
@Override
public Object intercept(Invocation invocation) throws Throwable {
StatementHandler statementHandler =(StatementHandler) invocation.getTarget();
MetaObject metaObject = MetaObject.forObject(statementHandler,SystemMetaObject.DEFAULT_OBJECT_FACTORY,
SystemMetaObject.DEFAULT_OBJECT_WRAPPER_FACTORY);
MappedStatement mappedStatement = (MappedStatement)metaObject.getValue("delegate.mappedStatement");
String sqlId = mappedStatement.getId();
//intercept select sql witch end with "ByPage".
if (sqlId.matches(".+ByPage$")) {
BoundSql boundSql = (BoundSql)metaObject.getValue("delegate.boundSql");
String sql = boundSql.getSql();
ParameterHandler parameterHandler = (ParameterHandler)metaObject.getValue("delegate.parameterHandler");
Map<?, ?> paramMap = (Map<?, ?>)parameterHandler.getParameterObject();
Page page = (Page) paramMap.get("page");
//set count
Connection connection = (Connection) invocation.getArgs()[0];
String countSql = "select count(1) from (" + sql + ")a";
PreparedStatement preparedStatement =connection.prepareStatement(countSql);
parameterHandler.setParameters(preparedStatement);
ResultSet resultSet = preparedStatement.executeQuery();
if (resultSet.next()) {
page.setTotalNumber(resultSet.getInt(1));
}
//construct record limit sql by dialect
String pageSql;
if ("oracle".equals(dialect.toLowerCase())) {
pageSql = "select * from" +
"(select a.*,rownum rn from (" + sql + ") a where rownum >= " +page.getRowNum() + ") " +
"rn < " +page.getRn();
} else if ("mysql".equals(dialect.toLowerCase())) {
pageSql = sql + " limit" + page.getDbIndex() + "," + page.getDbNumber();
} else {
pageSql = sql;
}
metaObject.setValue("delegate.boundSql.sql", pageSql);
}
return invocation.proceed();
}
@Override
public Object plugin(Object target) {
return Plugin.wrap(target, this);
}
@Override
public void setProperties(Properties properties) {
this.dialect = properties.getProperty("dialect");
}
}
~~~
#### 3.2.3 mybatis.xml總配置文件中注冊攔截器
**注意Mybatis配置文件各個元素的順序！**
~~~
<plugins>
<plugin interceptor="org.alien.mybatis.samples.interceptor.PageInterceptor">
<!--database dialect, only support mysql and oracle-->
<property name="dialect" value="mysql"/>
</plugin>
</plugins>
~~~
3.2.4 映射文件語句
~~~
<select id="selectAuthorByPage" resultType="author"parameterType="hashMap">
SELECT
t.id,
t.username,
t.password,
t.email,
t.bio,
t.favourite_section favouriteSection
FROM author t
WHERE t.username = {username} AND t.password = {password}
</select>
~~~
#### 3.2.4 映射接口方法
~~~
List<Author> selectAuthorByPage(Map<String, Object> map);
~~~
#### 3.2.4 測試方法
~~~
@Test
public void testSelectAuthorByPage() throws Exception {
Page page = new Page();
Map<String, Object> map = new HashMap<>();
map.put("page", page);
List<Author> authors = this.authorMapper.selectAuthorByPage(map);
Assert.assertEquals(5, authors.size());
}
~~~
### 三：補充
? ? ? 更多內容：[Mybatis 目錄](http://blog.csdn.net/crave_shy/article/details/45825599)
?? 篇幅有限、僅僅說明了攔截器的實現過程、原理及代碼都沒有詳細說明、這些會在Mybatis深入中詳細解析。
