引入分页的依赖
<!-- 分页拦截器 -->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>4.1.6</version>
</dependency>
分页的拦截器
package com.wkxhotel.web.waiter.interceptor;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Properties;
import java.util.regex.Pattern;
import org.apache.ibatis.executor.ErrorContext;
import org.apache.ibatis.executor.ExecutorException;
import org.apache.ibatis.executor.statement.BaseStatementHandler;
import org.apache.ibatis.executor.statement.RoutingStatementHandler;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.mapping.ParameterMode;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Plugin;
import org.apache.ibatis.plugin.Signature;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.property.PropertyTokenizer;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.type.TypeHandler;
import org.apache.ibatis.type.TypeHandlerRegistry;
import org.slf4j.LoggerFactory;
import com.wkxhotel.common.module.domain.PageCount;
import com.wkxhotel.common.module.util.ReflectHelperUtil;
import com.wkxhotel.common.module.util.httpclient.CommonUtils;
/**
* @ClassName:PageInterceptor.java
* @ClassDescribe:分页拦截器
* @createPerson:chaibo
* @createDate:2016年8月17日下午6:26:48
* @version
*/
@Intercepts({
@Signature(type =StatementHandler.class,method = "prepare",args = { Connection.class, Integer.class}) })
publicclassPageInterceptor implements Interceptor {
privatestaticfinaltransient org.slf4j.Loggerlog= LoggerFactory.getLogger(PageInterceptor.class);
privatestatic String dialect = ""; // 数据库方言
privatestatic String pageSqlId = ""; // 分页Id,mapper.xml中需要拦截的ID(正则匹配)
privatestatic String scopeId = ""; // 数据权限Id,以*byScope*开头的ID,都会被匹配到
privatestatic Pattern pattern_find= Pattern.compile("((\\{)([^\\{\\}]*?)(\\}))+");
privatestatic Pattern pattern = Pattern.compile("((func\\()([^\\(\\)]*?)(\\)))+");
public Objectintercept(Invocation ivk) throwsThrowable {
if (ivk.getTarget() instanceof RoutingStatementHandler){
RoutingStatementHandlerstatementHandler= null;
try {
statementHandler =(RoutingStatementHandler) ivk.getTarget();
}catch(Exception e) {
e.printStackTrace();
}
BaseStatementHandlerdelegate= (BaseStatementHandler) ReflectHelperUtil
.getValueByFieldName(statementHandler, "delegate");
MappedStatementmappedStatement= (MappedStatement) ReflectHelperUtil.getValueByFieldName(delegate,
"mappedStatement");
if (mappedStatement.getId().matches(pageSqlId)) { // 拦截需要分页的SQL
BoundSqlboundSql= delegate.getBoundSql();
Connectionconnection= null;
ResultSetrs = null;
PreparedStatementcountStmt= null;
ObjectparameterObject= boundSql.getParameterObject();// 分页SQL<select>中parameterType属性对应的实体参数,即Mapper接口中执行分页方法的参数,该参数不得为空
if (parameterObject == null) {
log.error("parameterObject尚未实例化!");
}else{
try {
connection = (Connection) ivk.getArgs()[0];
Stringsql= boundSql.getSql();
StringcountSql= "select count(0) as tmp_count from(" + sql + ") t "; // 记录统计
countStmt = connection.prepareStatement(countSql);
BoundSqlcountBS= newBoundSql(mappedStatement.getConfiguration(),countSql,
boundSql.getParameterMappings(),parameterObject);
setParameters(countStmt, mappedStatement, countBS, parameterObject);
rs = countStmt.executeQuery();
intcount = 0;
if (rs.next()) {
count = rs.getInt(1);
}
PageCountpageCount= null;
if (parameterObjectinstanceof PageCount) { // 参数就是Page实体
pageCount = (PageCount) parameterObject;
pageCount.setEntityOrField(true); //
pageCount.setTotalResult(count);
}else{ // 参数为某个实体,该实体拥有Page属性
FieldpageField= ReflectHelperUtil.getFieldByFieldName(parameterObject, "pageCount");
if (pageField != null) {
pageCount = (PageCount)ReflectHelperUtil.getValueByFieldName(parameterObject,
"pageCount");
if (pageCount == null)
pageCount = new PageCount();
pageCount.setEntityOrField(false);
pageCount.setTotalResult(count);
Fieldfield= ReflectHelperUtil.getFieldByFieldName(parameterObject, "pageCount");
field.set(parameterObject, pageCount);
}else{
log.error(parameterObject.getClass().getName()+ "不存在 pageCount 属性!");
}
}
StringpageSql= generatePageSql(sql,pageCount);
ReflectHelperUtil.setValueByFieldName(boundSql, "sql", pageSql); // 将分页sql语句反射回BoundSql.
}catch(Exception e) {
e.printStackTrace();
log.error("程序出错!");
}finally{
rs.close();
countStmt.close();
}
}
}
}
returnivk.proceed();
}
/**
* 对SQL参数(?)设值
*
* @param ps
* @param mappedStatement
* @param boundSql
* @param parameterObject
* @throws SQLException
*/
privatevoidsetParameters(PreparedStatement ps, MappedStatement mappedStatement, BoundSql boundSql,
ObjectparameterObject)throwsSQLException {
ErrorContext.instance().activity("setting parameters").object(mappedStatement.getParameterMap().getId());
List<ParameterMapping>parameterMappings= boundSql.getParameterMappings();
if (parameterMappings!= null){
Configurationconfiguration= mappedStatement.getConfiguration();
TypeHandlerRegistrytypeHandlerRegistry= configuration.getTypeHandlerRegistry();
MetaObjectmetaObject= parameterObject== null? null: configuration.newMetaObject(parameterObject);
for (inti = 0; i < parameterMappings.size();i++) {
ParameterMappingparameterMapping= parameterMappings.get(i);
if (parameterMapping.getMode() !=ParameterMode.OUT) {
Objectvalue;
StringpropertyName= parameterMapping.getProperty();
PropertyTokenizerprop= newPropertyTokenizer(propertyName);
if (parameterObject == null) {
value = null;
}elseif(typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {
value = parameterObject;
}elseif(boundSql.hasAdditionalParameter(propertyName)) {
value = boundSql.getAdditionalParameter(propertyName);
}elseif(boundSql.hasAdditionalParameter(prop.getName())) {
value = boundSql.getAdditionalParameter(prop.getName());
if (value != null) {
value = configuration.newMetaObject(value)
.getValue(propertyName.substring(prop.getName().length()));
}
}else{
value = metaObject == null ? null : metaObject.getValue(propertyName);
}
TypeHandlertypeHandler= parameterMapping.getTypeHandler();
if (typeHandler == null) {
thrownew ExecutorException("There was no TypeHandler found for parameter " + propertyName
+" of statement " + mappedStatement.getId());
}
typeHandler.setParameter(ps, i + 1, value, parameterMapping.getJdbcType());
}
}
}
}
/**
* @describe:根据数据库方言,生成特定的分页sql
* @createPerson:chaibo
* @createDate: 2016年8月17日下午6:27:26
* @updateDescribe:
* @param sql
* @param page
* @return
*/
private StringgeneratePageSql(String sql, PageCount page){
if (page != null &&!CommonUtils.isEmpty(dialect)) {
StringBufferpageSql= newStringBuffer();
pageSql.append("select * from (");
if ("mysql".equals(dialect)) {
pageSql.append(sql);
pageSql.append(" limit "+ page.getCurrentResult()+ ","+ page.getShowCount());
pageSql.append(") t");
if (!CommonUtils.isEmpty(page.getSortName())) {
pageSql.append(" order by ");
pageSql.append(page.getSortName());
pageSql.append(" ");
pageSql.append(CommonUtils.isEmpty(page.getSortOrder()) ? "asc" : page.getSortOrder());
}
}
if ("greenplum".equals(dialect)) {
pageSql.append(sql);
pageSql.append(" limit "+ page.getShowCount()+ " OFFSET " + page.getCurrentResult());
pageSql.append(") t");
if (!CommonUtils.isEmpty(page.getSortName())) {
pageSql.append(" order by ");
pageSql.append(page.getSortName());
pageSql.append(" ");
pageSql.append(CommonUtils.isEmpty(page.getSortOrder()) ? "asc" : page.getSortOrder());
}
}
returnpageSql.toString();
}else{
returnsql;
}
}
public Object plugin(Object arg0) {
return Plugin.wrap(arg0, this);
}
publicvoidsetProperties(Properties p) {
dialect = p.getProperty("dialect");
pageSqlId = p.getProperty("pageSqlId");
}
}
分页的配置文件 mybatis_config.xml
<?xml version="1.0"encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTDConfig 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<settings>
<setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>
<plugins>
<plugin interceptor="com.wkxhotel.web.waiter.interceptor.PageInterceptor">
<property name="dialect" value="greenplum"/>
<property name="pageSqlId" value=".*findPaged*.*"/><!-- 分页拦截,配置以*getPaged*开始的Id -->
</plugin>
<plugin interceptor="com.github.pagehelper.PageHelper">
<property name="dialect" value="mysql"/>
<!-- 该参数默认为false -->
<!-- 设置为true时,会将RowBounds第一个参数offset当成pageNum页码使用 -->
<!-- 和startPage中的pageNum效果一样 -->
<property name="offsetAsPageNum" value="true" />
<!-- 该参数默认为false -->
<!-- 设置为true时,使用RowBounds分页会进行count查询 -->
<property name="rowBoundsWithCount" value="true" />
</plugin>
</plugins>
</configuration>
创建分页的实体类
publicclassBasePage implementsSerializable {
privatestaticfinallongserialVersionUID= 1L;
public PageCount pageCount = new PageCount();
public PageCountgetPageCount() {
returnpageCount;
}
publicvoid setPageCount(PageCount pageCount) {
this.pageCount = pageCount;
}
}
**
*
*@类名称:PageCount.java
*@类描述:分页参数
*@创建人:chaibo
*@修改备注:
*@version
*/
@XmlRootElement
publicclassPageCount implements Serializable {
privatestaticfinallongserialVersionUID= 1L;
privateintshowCount=15; // 每页显示记录数
privateinttotalPage=0; // 总页数
privateinttotalResult; // 总记录数
privateintcurrentPage; // 当前页
privateintcurrentResult; // 当前记录起始索引
privatebooleanentityOrField; // true:需要分页的地方,传入的参数就是Page实体;false:需要分页的地方,传入的参数所代表的实体拥有Page属性
// private String pageStr; //最终页面显示的底部翻页导航,详细见:getPageStr();
private String sortName;
private String sortOrder;
privateintcurrentResultItem;//当前第几条
private List<?> rows;
public List<?> getRows(){
returnrows;
}
publicvoid setRows(List<?> rows) {
this.rows = rows;
}
publicint getCurrentPage() {
if (currentPage <= 0)
currentPage = 1;
if (currentPage > getTotalPage())
currentPage = getTotalPage();
returncurrentPage;
}
publicvoid setCurrentPage(intcurrentPage) {
this.currentPage = currentPage;
}
publicint getTotalPage() {
if (showCount!=0) {
if (totalResult % showCount == 0)
totalPage = totalResult / showCount;
else
totalPage = totalResult / showCount + 1;
}
returntotalPage;
}
publicvoid setTotalPage(inttotalPage) {
this.totalPage = totalPage;
}
publicint getTotalResult() {
returntotalResult;
}
publicvoid setTotalResult(inttotalResult) {
this.totalResult = totalResult;
}
publicint getShowCount() {
returnshowCount;
}
publicvoid setShowCount(intshowCount) {
this.showCount = showCount;
}
publicint getCurrentResult() {
currentResult =(getCurrentPage() - 1) * getShowCount();
if (currentResult < 0)
currentResult = 0;
returncurrentResult;
}
publicvoid setCurrentResult(intcurrentResult) {
this.currentResult = currentResult;
}
publicboolean isEntityOrField() {
returnentityOrField;
}
publicvoid setEntityOrField(booleanentityOrField) {
this.entityOrField = entityOrField;
}
public String getSortName() {
returnsortName;
}
publicvoid setSortName(String sortName) {
this.sortName = sortName;
}
public String getSortOrder() {
returnsortOrder;
}
publicvoid setSortOrder(String sortOrder) {
this.sortOrder = sortOrder;
}
publicint getCurrentResultItem(){
returncurrentResultItem;
}
publicvoid setCurrentResultItem(intcurrentResultItem){
this.currentResultItem= currentResultItem;
}
}
应用案例
public PageCount getComplete(CompleteRequest request) {
PageCountpageCount= request.getPageCount();
List<CompleteResponse>response= statisticsService.getComplete(request);
pageCount.setRows(response);
returnpageCount;
}