MyBatis實現分頁的方式
1.SQL原生拼接
將currIndex
,pageSize
作為查詢時引數在執行查詢SQL時填充
xml
檔案
<select id="queryStudentsBySql" parameterType="map" resultMap="studentmapper">
select * from student limit #{currIndex} , #{pageSize}
</select>
service
介面
List<Student> queryStudentsBySql(int currPage, int pageSize);
實現類
public List<Student> queryStudentsBySql(int currPage, int pageSize) {
Map<String, Object> data = new HashedMap();
data.put("currIndex", (currPage-1)*pageSize);
data.put("pageSize", pageSize);
return studentMapper.queryStudentsBySql(data);
}
2.藉助MyBabtis
提供的RowBounds
進行分頁查詢
資料量小時,RowBounds
不失為一種好辦法。但是資料量大時,實現攔截器就很有必要了。
mybatis介面加入RowBounds
引數
public List<UserBean> queryUsersByPage(String userName, RowBounds rowBounds);
RowBounds
public class RowBounds {
//預設值為0~~Java最大整數
public static final int NO_ROW_OFFSET = 0;
public static final int NO_ROW_LIMIT = Integer.MAX_VALUE;
public static final RowBounds DEFAULT = new RowBounds();
//偏移量,即從第幾行開始讀取
private final int offset;
//限制,即每頁顯示記錄數量
private final int limit;
public RowBounds() {
this.offset = NO_ROW_OFFSET;
this.limit = NO_ROW_LIMIT;
}
public RowBounds(int offset, int limit) {
this.offset = offset;
this.limit = limit;
}
public int getOffset() {
return offset;
}
public int getLimit() {
return limit;
}
}
Service
@Service
public class OrderInfoService {
@Resource
private OrderInfoMapper orderInfoMapper;
public List<OrderInfo> getOrderInfoList(int pageNow, int pageSize) {
RowBounds rowBounds = new RowBounds(pageNow,pageSize);
return orderInfoMapper.getOrderInfoList(rowBounds);
}
}
Mapper
層
@Mapper
public interface OrderInfoMapper {
List<OrderInfo> getOrderInfoList(RowBounds rowBounds);
}
3.自定義攔截器 Intercepter
自定義攔截器外掛分頁 需要自己定義一個類實現Interceptor
介面,這個介面是Mybatis提供的。任何分頁外掛想要對Mybatis進行分頁就必須實現Interceptor
介面,包括後面PageHelper
分頁外掛。
①、建立MyPageInterceptor
類
/**
* @Intercepts 表示是一個攔截器
* @Signature 攔截器的簽名
* type 攔截的型別 四大物件之一( Executor,ResultSetHandler,ParameterHandler,StatementHandler)
* method 攔截的方法
*/
@Intercepts({@Signature(type=StatementHandler.class,method="prepare",args={Connection.class, Integer.class })})
public class MyPageInterceptor implements Interceptor {
//當前頁碼
private int currPage;
//每頁顯示的條目數
private int pageSize;
//資料庫型別
private String dbType;
@Override
public Object intercept(Invocation invocation) throws Throwable {
System.out.println("plugin is running...");
//獲取StatementHandler,預設是RoutingStatementHandler
StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
//獲取statementHandler包裝類
MetaObject MetaObjectHandler = SystemMetaObject.forObject(statementHandler);
//分離代理物件鏈
while (MetaObjectHandler.hasGetter("h")) {
Object obj = MetaObjectHandler.getValue("h");
MetaObjectHandler = SystemMetaObject.forObject(obj);
}
while (MetaObjectHandler.hasGetter("target")) {
Object obj = MetaObjectHandler.getValue("target");
MetaObjectHandler = SystemMetaObject.forObject(obj);
}
//獲取連線物件
//Connection connection = (Connection) invocation.getArgs()[0];
//object.getValue("delegate"); 獲取StatementHandler的實現類
//獲取查詢介面對映的相關資訊
MappedStatement mappedStatement = (MappedStatement) MetaObjectHandler.getValue("delegate.mappedStatement");
String mapId = mappedStatement.getId();
//statementHandler.getBoundSql().getParameterObject();
//攔截以.ByPage結尾的請求,分頁功能的統一實現
if (mapId.matches(".+ByPage$")) {
//獲取進行資料庫操作時管理引數的handler
ParameterHandler parameterHandler = (ParameterHandler) MetaObjectHandler.getValue("delegate.parameterHandler");
//獲取請求時的引數
Map<String, Object> paraObject = (Map<String, Object>) parameterHandler.getParameterObject();
//也可以這樣獲取
//paraObject = (Map<String, Object>) statementHandler.getBoundSql().getParameterObject();
//引數名稱和在service中設定到map中的名稱一致
currPage = (int) paraObject.get("currPage");
pageSize = (int) paraObject.get("pageSize");
String sql = (String) MetaObjectHandler.getValue("delegate.boundSql.sql");
//也可以透過statementHandler直接獲取
//sql = statementHandler.getBoundSql().getSql();
//構建分頁功能的sql語句
String limitSql;
sql = sql.trim();
limitSql = sql + " limit " + (currPage - 1) * pageSize + "," + pageSize;
//將構建完成的分頁sql語句賦值個體'delegate.boundSql.sql',偷天換日
MetaObjectHandler.setValue("delegate.boundSql.sql", limitSql);
}
//呼叫原物件的方法,進入責任鏈的下一級
return invocation.proceed();
}
//獲取代理物件
@Override
public Object plugin(Object o) {
//生成object物件的動態代理物件
return Plugin.wrap(o, this);
}
//設定代理物件的引數
@Override
public void setProperties(Properties properties) {
//如果專案中分頁的pageSize是統一的,也可以在這裡統一配置和獲取,這樣就不用每次請求都傳遞pageSize引數了。引數是在配置攔截器時配置的。
String limit1 = properties.getProperty("limit", "10");
this.pageSize = Integer.valueOf(limit1);
this.dbType = properties.getProperty("dbType", "mysql");
}
}
②、全域性配置檔案增加plugin設定(注意位置)
<!-- 配置自定義分頁外掛 -->
<plugins>
<plugin interceptor="com.thr.interceptor.MyPageInterceptor"></plugin>
</plugins>
③、介面方法
//分頁查詢所有使用者,透過原生自定義攔截器
List<User> selectAllUserByPage(Map map);
由於攔截器中設定了攔截以ByPage
結尾的方法,所以方法一定要命名正確,
4. PageHelper
外掛
①、引入PageHelper依賴:
<!-- pagehelper分頁外掛 -->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>5.2.0</version></dependency>
</dependency>
②、全域性配置檔案增加plugin設定(注意位置)
<!-- 配置分頁外掛 -->
<plugins>
<!-- PageHelper5版本配置 -->
<plugin` `interceptor="com.github.pagehelper.PageInterceptor"/>
</plugins>
③、測試方法
//分頁查詢所有使用者資訊,透過PageHelper
@Test
public void selectAllUserByPageHelper(){
int currPage = 2;
//當前頁碼 int` `pageSize = 3;
//當前頁記錄數量
//表示獲取第2頁,3條內容,預設會查詢總數count
PageHelper.startPage(currPage,pageSize);
List<User> userList = mapper.selectAllUserByPageHelper();
for(User user : userList)
{ System.out.println(user);
}
}