博文参考
MyBatis - 分页之四大方式（数组、SQL、拦截器，RowBounds）
数组分页
数据库查询并返回所有的数据，而我们需要的只是极少数符合要求的数据。
当数据量少时，还可以接受。
当数据库数据量过大时，每次查询对数据库和程序的性能都会产生极大的影响。
@Override
public List<Address> queryAddressByArray(int currPage, int pageSize) {
List<Address> addressList = addressDao.queryAddressByArray();
int firstIndex = (currPage - 1) * pageSize;
int lastIndex = currPage * pageSize;
return addressList.subList(firstIndex, lastIndex);
}
SQL分页
实现了按需查找，每次检索得到的是指定的数据。
每次在分页的时候都需要去编写limit语句，很冗余。
不方便统一管理，维护性较差。
<select id="queryAddressBySql" parameterType="map" resultType="Address">
select * from j_position limit #{currIndex}, #{pageSize}
</select>
拦截器分页
实现了按需查找，且适用于不同类型的数据库。
不再需要在每个语句中单独去配置分页相关的参数了，可以同时适用所有需要分页的场景。
@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;
/**
* 拦截过程
*
* @param invocation
* @return
* @throws Throwable
*/
@Override
public Object intercept(Invocation invocation)
throws Throwable {
...
return invocation.proceed();
}
/**
* 获取代理对象
*
* @param o
* @return
*/
@Override
public Object plugin(Object o) {
return Plugin.wrap(o, this);
}
/**
* 设置代理对象的参数
*
* @param properties
*/
@Override
public void setProperties(Properties properties) {
String limitStr = properties.getProperty("limit", "10");
this.pageSize = Integer.valueOf(limitStr);
this.dbType = properties.getProperty("dbType", "mysql");
}
<plugins>
<plugin interceptor="cn.edu.xidian.ictt.springyk.interceptor.MyPageInterceptor">
<property name="limit" value="10"/>
<property name="dbType" value="mysql"/>
</plugin>
</plugins>
RowBounds分页
和数组分页的原理类似，一次性将数据加载到内存。
<select id="queryAddressByRowBounds" resultType="Address">
select * from j_position
</select>
/**
* RowBounds分页
*
* @return
*/
List<Address> queryAddressByRowBounds(RowBounds rowBounds);
/**
*
* @param currPage
* @param pageSize
* @return
*/
List<Address> queryAddressByRowBounds(int currPage,
int pageSize);
}
@Override
public List<Address> queryAddressByRowBounds(int currPage,
int pageSize) {
return addressDao.queryAddressByRowBounds(
new RowBounds((currPage - 1) * pageSize, pageSize));
}
@ResponseBody
@RequestMapping("/address/RowBounds/{currPage}/{pageSize}")
public List<Address> getAddressByRowBounds(@PathVariable("currPage") int currPage, @PathVariable("pageSize") int pageSize) {
return addressService.queryAddressByRowBounds(currPage, pageSize);
}
Github完整代码
https://github.com/HengYk/springyk
源码分析
RoutingStatementHandler是StatementHandler的默认实现类。
RoutingStatementHandler只是一个中间代理类，负责将StatementHandler导向三个不同的服务对象，这三个服务对象继承自BaseStatementHandler：
SimpleStatementHandler是默认的简单执行器；
PreparedStatementHandler是重用预处理语句的执行器；
CallableStatementHandler是重用语句和批量更新的处理器。
package org.apache.ibatis.executor.statement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import org.apache.ibatis.cursor.Cursor;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.executor.ExecutorException;
import org.apache.ibatis.executor.parameter.ParameterHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
/**
* @author Clinton Begin
*/
public class RoutingStatementHandler implements StatementHandler {
private final StatementHandler delegate;
public RoutingStatementHandler(Executor executor, MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) {
switch (ms.getStatementType()) {
case STATEMENT:
delegate = new SimpleStatementHandler(executor, ms, parameter, rowBounds, resultHandler, boundSql);
break;
case PREPARED:
delegate = new PreparedStatementHandler(executor, ms, parameter, rowBounds, resultHandler, boundSql);
break;
case CALLABLE:
delegate = new CallableStatementHandler(executor, ms, parameter, rowBounds, resultHandler, boundSql);
break;
default:
throw new ExecutorException("Unknown statement type: " + ms.getStatementType());
}
}
...
}
BaseStatementHandler属性分析：
Configuration包含了Mybatis的所有配置信息；
ResultSetHandler用于处理拦截的执行结果；
ParameterHandler用于处理拦截的SQL参数；
Executor是SQL的执行器；
MappedStatement存储了Mybatis中一条SQL语句的所有配置信息；
RowBounds可直接用于分页，封装了offset和limit；
BoundSql用于处理拦截的SQL语句。
public abstract class BaseStatementHandler implements StatementHandler {
protected final Configuration configuration;
protected final ObjectFactory objectFactory;
protected final TypeHandlerRegistry typeHandlerRegistry;
protected final ResultSetHandler resultSetHandler;
protected final ParameterHandler parameterHandler;
protected final Executor executor;
protected final MappedStatement mappedStatement;
protected final RowBounds rowBounds;
protected BoundSql boundSql;
}