Java web项目创建笔记10 之《Mybatis分页查询（1）》

一、添加分页代码

0、在mybatis-config.xml添加

	<plugins>
		<plugin interceptor="com.study.base.mybatis.page.PaginationInterceptor" />
	</plugins>

1、在webapp2_base模块下添加包com.study.base.mybatis.page

2、在包下建立Dialect.java

package com.study.base.mybatis.page;

public interface Dialect {

    /**
     * 数据库类型
     */
    enum Type {
        ORACLE,
        MYSQL
    }

    /**
     * page sql create
     *
     * @param sql sql
     * @param skipResults 偏移量
     * @param maxResults 最大值
     * @return sql
     */
     String getLimitString(String sql, int skipResults, int maxResults);
}

3、在包下建立MysqlDialect.java

package com.study.base.mybatis.page;

public class MysqlDialect implements Dialect {

    /**
     * page sql create
     *
     * @param sql sql
     * @param offset 偏移量
     * @param limit 最大值
     * @return sql
     */
    @Override
    public String getLimitString(String sql, int offset, int limit) {
        sql = sql.trim();

        //offset表示开始位置, limit表示返回的条数
        return sql + " limit " + offset + " , " + limit;
    }

}

4、在包下建立OracleDialect.java

package com.study.base.mybatis.page;

public class OracleDialect implements Dialect {

    /**
     * page sql create
     *
     * @param sql sql
     * @param offset 偏移量
     * @param limit 最大值
     * @return sql
     */
    @Override
    public String getLimitString(String sql, int offset, int limit) {
        sql = sql.trim();
        return "select * from ( select row_.*, rownum rownum_ from ( " +
                sql +
                " ) row_ ) where rownum_ > " + offset + " and rownum_ <= " + (offset + limit);
    }

}

5、在包下建立Pageable.java

package com.study.base.mybatis.page;

public class Pageable {
    /*当前页码*/
    private int pageNum;
    /*页大小*/
    private int pageSize;
    /*总数*/
    private long rowCount;

    public int getPageSize() {
        return pageSize;
    }

    public void setPageSize(int pageSize) {
        this.pageSize = pageSize;
    }

    public long getRowCount() {
        return rowCount;
    }

    public void setRowCount(long rowCount) {
        this.rowCount = rowCount;
    }

    public int getPageNum() {
        return pageNum;
    }

    public void setPageNum(int pageNum) {
        this.pageNum = pageNum;
    }
}

6、在包下建立Pagination.java

package com.study.base.mybatis.page;

import java.util.Collections;
import java.util.List;

public class Pagination<T> {
    /*每页最大条数*/
    private static final int MAX_PAGE_SIZE=100;
    /*总数*/
    private long totalRows;
    /*页大小*/
    private int pageSize;
    /*当前页码*/
    private int currentPage;
    /*查询结果*/
    private List<T> resultList = Collections.emptyList();

    public Pagination() {
        this(1, MAX_PAGE_SIZE);
    }

    public Pagination(int currentPage) {
        this(currentPage, MAX_PAGE_SIZE);
    }

    public Pagination(int currentPage, int pageSize) {
        setCurrentPage(currentPage);
        setPageSize(pageSize);
    }

    public Pagination(Pageable pageable, List<T> resultList) {
        setCurrentPage(pageable.getPageNum());
        setPageSize(pageable.getPageSize());
        setTotalRows(pageable.getRowCount());
        setList(resultList);
    }

    public void setCurrentPage(int currentPage) {
        if (currentPage < 0) {
            currentPage = 1;
        }
        this.currentPage  = currentPage;
    }

    public void setPageSize(int pageSize) {
        if (pageSize < 1) {
            pageSize = 15;
        }
        this.pageSize = pageSize;
    }


    protected void onSetList() {
        if (resultList == null || resultList.isEmpty()) {
            totalRows = 0;
            currentPage = 1;
        }
    }

    public long getTotalRows() {
        return totalRows;
    }

    public void setTotalRows(long totalRows) {
        this.totalRows = totalRows;
    }

    public int getPageSize() {
        return pageSize;
    }

    public List<T> getList() {
        return resultList;
    }

    public void setList(List<T> list) {
        this.resultList = list;
        onSetList();
    }

    public int getCurrentPage() {
        return currentPage;
    }

}

7、在包下建立PaginationInterceptor.java

package com.study.base.mybatis.page;

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 org.apache.ibatis.executor.ErrorContext;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.executor.ExecutorException;
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.mapping.SqlSource;
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.scripting.xmltags.ForEachSqlNode;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.apache.ibatis.type.TypeHandler;
import org.apache.ibatis.type.TypeHandlerRegistry;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

@Intercepts({@Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class})})
public class PaginationInterceptor implements org.apache.ibatis.plugin.Interceptor {

    private static Logger log = LoggerFactory.getLogger(PaginationInterceptor.class);

    /**
     * page  intercept
     *
     * @param invocation
     * @return
     * @throws Throwable
     */
    @Override
    public Object intercept(Invocation invocation) throws Throwable {

        MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[0];
        Object parameter = invocation.getArgs()[1];
        BoundSql boundSql = mappedStatement.getBoundSql(parameter);
        String originalSql = boundSql.getSql().trim();
        RowBounds rowBounds = (RowBounds) invocation.getArgs()[2];

        Object parameterObject = boundSql.getParameterObject();

        if(!(parameterObject instanceof Pageable)){
            return invocation.proceed();
        }

        Configuration configuration = mappedStatement.getConfiguration();
        String dialectStr = configuration.getVariables().getProperty("dialect");
        if (dialectStr == null) {
            throw new RuntimeException("the value of the dialect property in configuration.xml is not defined : "
                    + configuration.getVariables().getProperty("dialect"));
        }
        Dialect.Type databaseType = Dialect.Type.valueOf(dialectStr.toUpperCase());

        Dialect dialect = null;
        switch(databaseType){
            case ORACLE:
                dialect=new OracleDialect();
                break;
            case MYSQL:
            	dialect=new MysqlDialect();
            	break;
             default:
                 dialect=new MysqlDialect();
        }

        Pageable pageable = (Pageable)parameterObject;
        pageable.setRowCount(getTotalCount(mappedStatement,boundSql));
        if (pageable.getRowCount() == 0) {
            return invocation.proceed();
        }

        rowBounds = new RowBounds(pageable.getPageSize() * (pageable.getPageNum() - 1), pageable.getPageSize());

        String pageSql = dialect.getLimitString(originalSql,  rowBounds.getOffset(), rowBounds.getLimit());
        invocation.getArgs()[2] = new RowBounds(RowBounds.NO_ROW_OFFSET, RowBounds.NO_ROW_LIMIT);
        BoundSql newBoundSql = new BoundSql(mappedStatement.getConfiguration(), pageSql, boundSql.getParameterMappings(), boundSql.getParameterObject());
        MappedStatement newMs = copyFromMappedStatement(mappedStatement, new BoundSqlSqlSource(newBoundSql));
        invocation.getArgs()[0] = newMs;

        if (log.isDebugEnabled()) {
            log.debug("生成分页SQL : " + boundSql.getSql());
        }

        return invocation.proceed();

    }

    /**
     * count all rows
     *
     * @param mappedStatement
     * @param boundSql
     * @return
     * @throws SQLException
     */
    private int getTotalCount(MappedStatement mappedStatement, BoundSql boundSql) throws SQLException {
        int count=0;

        Object parameterObject = boundSql.getParameterObject();
        String originalSql = boundSql.getSql().trim();
        StringBuffer countSql = new StringBuffer(originalSql.length() + 100);
        countSql.append("select count(1) from (").append(originalSql).append(") t");

        Connection connection =null;
        PreparedStatement stmt=null;
        ResultSet rs=null;
        try{
            connection=mappedStatement.getConfiguration().getEnvironment().getDataSource().getConnection();
            stmt = connection.prepareStatement(countSql.toString());
            BoundSql countBS = new BoundSql(mappedStatement.getConfiguration(), countSql.toString(), boundSql.getParameterMappings(), parameterObject);
            setParameters(stmt, mappedStatement, countBS, parameterObject);
            rs = stmt.executeQuery();
            if(rs.next()){
                count=rs.getInt(1);
            }
            rs.close();
            stmt.close();
            connection.close();
        }finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    log.error("close rs Exception",e);
                }
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    log.error("close stmt Exception",e);
                }
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    log.error("close connection Exception",e);
                }
            }
        }
        return count;
    }


    /**
     * 对SQL参数(?)设值,参考org.apache.ibatis.executor.parameter.DefaultParameterHandler
     *
     * @param ps
     * @param mappedStatement
     * @param boundSql
     * @param parameterObject
     * @throws SQLException
     */
    private void setParameters(PreparedStatement ps, MappedStatement mappedStatement, BoundSql boundSql, Object parameterObject) throws SQLException {
        ErrorContext.instance().activity("setting parameters").object(mappedStatement.getParameterMap().getId());
        List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
        if (parameterMappings != null) {
            Configuration configuration = mappedStatement.getConfiguration();
            TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();
            MetaObject metaObject = parameterObject == null ? null : configuration.newMetaObject(parameterObject);
            for (int i = 0; i < parameterMappings.size(); i++) {
                ParameterMapping parameterMapping = parameterMappings.get(i);
                if (parameterMapping.getMode() != ParameterMode.OUT) {
                    Object value;
                    String propertyName = parameterMapping.getProperty();
                    PropertyTokenizer prop = new PropertyTokenizer(propertyName);
                    if (parameterObject == null) {
                        value = null;
                    } else if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {
                        value = parameterObject;
                    } else if (boundSql.hasAdditionalParameter(propertyName)) {
                        value = boundSql.getAdditionalParameter(propertyName);
                    } else if (propertyName.startsWith(ForEachSqlNode.ITEM_PREFIX) && 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);
                    }
                    TypeHandler typeHandler = parameterMapping.getTypeHandler();
                    if (typeHandler == null) {
                        throw new ExecutorException("There was no TypeHandler found for parameter " + propertyName + " of statement " + mappedStatement.getId());
                    }
                    typeHandler.setParameter(ps, i + 1, value, parameterMapping.getJdbcType());
                }
            }
        }
    }

    /**
     * @param ms
     * @param newSqlSource
     * @return
     */
    private MappedStatement copyFromMappedStatement(MappedStatement ms, SqlSource newSqlSource) {
        MappedStatement.Builder builder = new MappedStatement.Builder(ms.getConfiguration(),
                ms.getId(), newSqlSource, ms.getSqlCommandType());
        builder.resource(ms.getResource());
        builder.fetchSize(ms.getFetchSize());
        builder.statementType(ms.getStatementType());
        builder.keyGenerator(ms.getKeyGenerator());
        if (ms.getKeyProperties() != null) {
            for (String keyProperty : ms.getKeyProperties()) {
                builder.keyProperty(keyProperty);
            }
        }
        builder.timeout(ms.getTimeout());
        builder.parameterMap(ms.getParameterMap());
        builder.resultMaps(ms.getResultMaps());
        builder.cache(ms.getCache());
        MappedStatement newMs = builder.build();
        return newMs;
    }

    public static class BoundSqlSqlSource implements SqlSource {
        BoundSql boundSql;

        public BoundSqlSqlSource(BoundSql boundSql) {
            this.boundSql = boundSql;
        }

        @Override
        public BoundSql getBoundSql(Object parameterObject) {
            return boundSql;
        }
    }

    /*
     * (non-Javadoc)
     *
     * @see org.apache.ibatis.plugin.Interceptor#plugin(java.lang.Object)
     */
    @Override
    public Object plugin(Object target) {
        return Plugin.wrap(target, this);
    }

    /*
     * (non-Javadoc)
     *
     * @see
     * org.apache.ibatis.plugin.Interceptor#setProperties(java.util.Properties)
     */
    @Override
    public void setProperties(Properties arg0) {
    	
    }

}