myabatis实现物理分页

目采用了拦截器，就是mybaits自带的plus功能。将每次select操作都进行拦截。
cotroller层
 

@RequestMapping(params = ("method=list"))
    public ModelAndView listAll(HttpServletRequest request,HttpServletResponse response) {

        //可以将分页参数获取封装，已达到更好的复用效果。
        //page=2&pageSize=10&totalPages=19&totalRows=188
        String pagec = request.getParameter("page");
        String pageSize = request.getParameter("pageSize");
        String totalPages = request.getParameter("totalPages");
        String totalRows = request.getParameter("totalRows");
        //这里其实有些条条件，例如totalRows 前台是没有办法提供的，还有 totalPages 或其它些参数通过 计算可以获得
        String price = request.getParameter("price");//书的价格
        String name = request.getParameter("name");//书的名称
        Map map = new HashMap();
        if(StringUtils.isNotEmpty(price))
         map.put("price", new Integer(price));
        if(StringUtils.isNotEmpty(price))
        map.put("name", name);

        //方法1：将分页参数直接放到mapper接口函数参数中，也可在对象中定义名字为page的属性，反射一样可以得到
        //后台连接直接获取
        //Page page = new Page();

        //方法2：不用进行map传参，用ThreadLocal进行传参,方便没有侵入性
        PageContext page = PageContext.getContext();

        //请自行验证
        if(null == pagec)
        {
            page.setCurrentPage(1);
            page.setPageSize(10);
        }
        else{
            page.setCurrentPage(Integer.parseInt(pagec));
            page.setPageSize(Integer.parseInt(pageSize));
            page.setTotalPages(Integer.parseInt(totalPages));
            page.setTotalRows(Integer.parseInt(totalRows));
        }
        page.setPagination(true);

//        方法1用
//        Map map = new HashMap();
//        map.put("page", page);
//        HandlerResult rs = userService.list(map);

        //方法2用
        HandlerResult rs = userService.list(map);

        ModelAndView mv = new ModelAndView("/views/show.jsp");
        mv.addObject("userList", rs.getResultObj());
        mv.addObject("page",page);
        return mv;
    }

简要说明：本文采用两种方式将page对象传入到拦截器中。第一种方式是采用参数传值，不管是用map还是在统一参数对象中名称为page的属性都可以在分页拦截器中得到page的值。第二种方式是用ThreadLocal，对service层没有侵入性。比较方便。

---

Service层代码。
 

public class UserService {

    @Autowired
    private UsersMapper usersMappser; 接口中的方法要和myabatis配置文件中配置的查询id要一致


    /**
     * 统一Service出口，方便管理
     * @param map
     * @return
     */
    public HandlerResult list(Map map){

        HandlerResult rs = new HandlerResult();

        rs.setResultObj(usersMappser.list(map));

        return rs;
    }

    /**
     * 采用本地线程的方式分页
     * @return
     */
    public HandlerResult list(){

        HandlerResult rs = new HandlerResult();

        rs.setResultObj(usersMappser.list2());

        return rs;
    }


    public UsersMapper getUsersMappser() {
        return usersMappser;
    }

    public void setUsersMappser(UsersMapper usersMappser) {
        this.usersMappser = usersMappser;
    }
}

3：mybatis接口 配置文件
  

public interface UsersMapper extends SqlMapper{

    public List list(Map para);

    public List list2();
}

 

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
  PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  "">
<mapper namespace="com.flydreamer.orm.mapper.UsersMapper">

    <resultMap type="com.flydreamer.orm.pojo.Book" id="userResult">
        <result property="id" column="ID" />
        <result property="name" column="NAME" />
        <result property="price" column="PRICE" />
    </resultMap>

    <select id="list" parameterType="map" resultMap="userResult">
            SELECT id,name,price FROM book
        <where>
                       <if test="name !=null">
                        and b.name LIKE CONCAT(CONCAT('%',
                        #{name}),'%')
                       </if>

                       <if test="price !=null">
                        and b.price >= #{price}
                       </if>
        </where>
    </select>

    <select id="list2" resultMap="userResult">
    SELECT id,name,price FROM book
    </select>

</mapper>

4：page的拦截器
 

package com.flydreamer.interceptor;

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.builder.xml.dynamic.ForEachSqlNode;
import org.apache.ibatis.executor.ErrorContext;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.executor.ExecutorException;
import org.apache.ibatis.logging.Log;
import org.apache.ibatis.logging.LogFactory;
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.mapping.MappedStatement.Builder;
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.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.apache.ibatis.type.TypeHandler;
import org.apache.ibatis.type.TypeHandlerRegistry;

import com.flydreamer.page.Db2Dialect;
import com.flydreamer.page.Dialect;
import com.flydreamer.page.MySql5Dialect;
import com.flydreamer.page.OracleDialect;
import com.flydreamer.page.Page;
import com.flydreamer.page.PageContext;
import com.flydreamer.page.ReflectHelper;

//只拦截select部分
@Intercepts({@Signature(type=Executor.class,method="query",args={ MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class })})
public class PaginationInterceptor implements Interceptor{

    private final static Log log = LogFactory.getLog(PaginationInterceptor.class);

    Dialect dialect = new Db2Dialect();

    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(boundSql==null || boundSql.getSql()==null || "".equals(boundSql.getSql()))
            return null;
        //分页参数--上下文传参
        Page page = null;
        PageContext context=PageContext.getContext();

        //map传参每次都将currentPage重置,先判读map再判断context
        if(parameterObject!=null)
            page = (Page)ReflectHelper.isPage(parameterObject,"page");

        //分页参数--context参数里的Page传参
        if(page==null && context.isPagination()==true)
        {
            page = context;
        }

        //后面用到了context的东东
        if(page!=null && page.isPagination()==true)
        {
         int totpage=page.getTotalRows();
         //得到总记录数
         if (totpage==0)
            {
                StringBuffer countSql = new StringBuffer(originalSql.length()+100 );
                countSql.append("select count(1) from (").append(originalSql).append(") t");
                     Connection connection=mappedStatement.getConfiguration().getEnvironment().getDataSource().getConnection() ;
                 PreparedStatement countStmt = connection.prepareStatement(countSql.toString());
                 BoundSql countBS = new BoundSql(mappedStatement.getConfiguration(),countSql.toString(),boundSql.getParameterMappings(),parameterObject);
                 setParameters(countStmt,mappedStatement,countBS,parameterObject);
                 ResultSet rs = countStmt.executeQuery();
                 if (rs.next()) {
                     totpage = rs.getInt(1);
                 }
                 rs.close();
                 countStmt.close();
                 connection.close();
            }

         //分页计算
            page.init(totpage,page.getPageSize(),page.getCurrentPage());

            if(rowBounds == null || rowBounds == RowBounds.DEFAULT){
                rowBounds= new RowBounds(page.getPageSize()*(page.getCurrentPage()-1),page.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;
        }


         return invocation.proceed();

    }
    public static class BoundSqlSqlSource implements SqlSource {
        BoundSql boundSql;

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

        public BoundSql getBoundSql(Object parameterObject) {
            return boundSql;
        }
    }
    public Object plugin(Object arg0) {
         return Plugin.wrap(arg0, this);
    }
    public void setProperties(Properties arg0) {

    }

    /**
     * 对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());
                }
            }
        }
    }

    private MappedStatement copyFromMappedStatement(MappedStatement ms,
             SqlSource newSqlSource) {
            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());
            //builder.keyProperty(ms.getKeyProperties().toString());
            builder.timeout(ms.getTimeout());
             builder.parameterMap(ms.getParameterMap());
            builder.resultMaps(ms.getResultMaps());
            builder.cache(ms.getCache());
            MappedStatement newMs = builder.build();
            return newMs;
            }


}

5：spring 配置

   


?    

   
       
       
       
     

6、拦截器配置