mybatis 物理分页 mysql_MyBatis物理分页的代码实现

@Intercepts({@Signature(type=StatementHandler.class, method = "prepare", args={Connection.class, Integer.class})})public class PageInterceptor implementsInterceptor {private String sqlRegEx = ".*Page";public Object intercept(Invocation invocation) throwsThrowable {

RoutingStatementHandler handler=(RoutingStatementHandler)invocation.getTarget();

StatementHandler delegate= (StatementHandler) ReflectUtil.getFieldValue(handler, "delegate");

BoundSql boundSql=delegate.getBoundSql();

MappedStatement mappedStatement= (MappedStatement)ReflectUtil.getFieldValue(delegate, "mappedStatement");//获取参数

Object parameterObject =boundSql.getParameterObject();//判断是否分页

if(mappedStatement.getId().matches(sqlRegEx)) {

Page page= (Page) ((Map, ?>) parameterObject).get("page");if (page != null) {

Connection connection= (Connection) invocation.getArgs()[0];//获取mapper映射文件中对应的sql语句

String sql =boundSql.getSql();//给当前page参数设置总记录数

this.setPageParameter(mappedStatement, connection, boundSql, page);//获取分页sql语句

String pageSql = this.getPageSql(page, sql);

ReflectUtil.setFieldValue(boundSql,"sql", pageSql);

}

}returninvocation.proceed();

}/*** 从数据库里查询总的记录数并计算总页数，回写进分页参数page

*@parammappedStatement

*@paramconnection

*@paramboundSql

*@parampage*/

private voidsetPageParameter(MappedStatement mappedStatement, Connection connection, BoundSql boundSql, Page page) {//获取mapper映射文件中对应的sql语句

String sql =boundSql.getSql();//获取计算总记录数的sql语句

String countSql = this.getCountSql(sql);//获取BoundSql参数映射

List parameterMappinglist =boundSql.getParameterMappings();//构造查询总量的BoundSql

BoundSql countBoundSql = newBoundSql(mappedStatement.getConfiguration(), countSql, parameterMappinglist, boundSql.getParameterObject());

ParameterHandler parameterHandler= newDefaultParameterHandler(mappedStatement, boundSql.getParameterObject(), countBoundSql);

PreparedStatement pstmt= null;

ResultSet rs= null;try{//通过connection建立countSql对应的PreparedStatement对象

pstmt =connection.prepareStatement(countSql);

parameterHandler.setParameters(pstmt);//执行countSql语句

rs =pstmt.executeQuery();if(rs.next()) {int totalRecord = rs.getInt(1);

page.setTotalRecord(totalRecord);

page.setTotalPage(totalRecord/page.getPageSize() + (totalRecord % page.getPageSize() == 0? 0: 1));

}

}catch(SQLException e) {

e.printStackTrace();

}

}/*** 根据源sql语句获取对应的查询总记录数的sql语句

*@paramsql

*@return

*/

privateString getCountSql(String sql) {int index = sql.indexOf("from");return "select count(*) " +sql.substring(index);

}/*** 获取MySql数据库的分页查询语句

*@parampage

*@paramsql

*@return

*/

private String getPageSql(Page>page, String sql) {

StringBuffer sqlBuffer= newStringBuffer(sql);int offset = (page.getPageNum() - 1) *page.getPageSize();

sqlBuffer.append(" limit ").append(offset).append(",").append(page.getPageSize());returnsqlBuffer.toString();

}/*** 只处理StatementHandler类型

*@paramo

*@return

*/

publicObject plugin(Object o) {if (o instanceofStatementHandler) {return Plugin.wrap(o, this);

}else{returno;

}

}/*** 拦截器属性设定

*@paramproperties*/

public voidsetProperties(Properties properties) {

}publicString getSqlRegEx() {returnsqlRegEx;

}public voidsetSqlRegEx(String sqlRegEx) {this.sqlRegEx =sqlRegEx;

}

}