MyBatis分页

本方法是通过拦截mybatis底层的数据库操作达到分页目的

//分页实体类

public   class  Page {  
    private   int  showCount =  10 ;  //每页显示记录数   
    private   int  totalPage;       //总页数   
    private   int  totalResult;     //总记录数   
    private   int  currentPage;     //当前页   
    private   int  currentResult;   //当前记录起始索引   
    private   boolean  entityOrField;   //true:需要分页的地方，传入的参数就是Page实体；false:需要分页的地方，传入的参数所代表的实体拥有Page属性   
    private  String pageStr;      //最终页面显示的底部翻页导航，详细见：getPageStr();   
    public   int  getTotalPage() {  
        if (totalResult%showCount== 0 )  
            totalPage = totalResult/showCount;  
        else   
            totalPage = totalResult/showCount+1 ;  
        return  totalPage;  
    }  
    public   void  setTotalPage( int  totalPage) {  
        this .totalPage = totalPage;  
    }  
    public   int  getTotalResult() {  
        return  totalResult;  
    }  
    public   void  setTotalResult( int  totalResult) {  
        this .totalResult = totalResult;  
    }  
    public   int  getCurrentPage() {  
        if (currentPage<= 0 )  
            currentPage = 1 ;  
        if (currentPage>getTotalPage())  
            currentPage = getTotalPage();  
        return  currentPage;  
    }  
    public   void  setCurrentPage( int  currentPage) {  
        this .currentPage = currentPage;  
    }  
    public  String getPageStr() {  
        StringBuffer sb = new  StringBuffer();  
        if (totalResult> 0 ){  
            sb.append(" <ul>\n" );  
            if (currentPage== 1 ){  
                sb.append(" <li class=\"pageinfo\">首页</li>\n" );  
                sb.append(" <li class=\"pageinfo\">上页</li>\n" );  
            }else {    
                sb.append(" <li><a href=\"#@\" onclick=\"nextPage(1)\">首页</a></li>\n" );  
                sb.append(" <li><a href=\"#@\" onclick=\"nextPage(" +(currentPage- 1 )+ ")\">上页</a></li>\n" );  
            }  
            int  showTag =  3 ;     //分页标签显示数量   
            int  startTag =  1 ;  
            if (currentPage>showTag){  
                startTag = currentPage-1 ;  
            }  
            int  endTag = startTag+showTag- 1 ;  
            for ( int  i=startTag; i<=totalPage && i<=endTag; i++){  
                if (currentPage==i)  
                    sb.append("<li class=\"current\">" +i+ "</li>\n" );  
                else   
                    sb.append(" <li><a href=\"#@\" onclick=\"nextPage(" +i+ ")\">" +i+ "</a></li>\n" );  
            }  
            if (currentPage==totalPage){  
                sb.append(" <li class=\"pageinfo\">下页</li>\n" );  
                sb.append(" <li class=\"pageinfo\">尾页</li>\n" );  
            }else {  
                sb.append(" <li><a href=\"#@\" onclick=\"nextPage(" +(currentPage+ 1 )+ ")\">下页</a></li>\n" );  
                sb.append(" <li><a href=\"#@\" onclick=\"nextPage(" +totalPage+ ")\">尾页</a></li>\n" );  
            }  
            sb.append(" <li class=\"pageinfo\">第" +currentPage+ "页</li>\n" );  
            sb.append(" <li class=\"pageinfo\">共" +totalPage+ "页</li>\n" );  
            sb.append("</ul>\n" );  
            sb.append("<script type=\"text/javascript\">\n" );  
            sb.append("function nextPage(page){" );  
            sb.append(" if(true && document.forms[0]){\n" );  
            sb.append("     var url = document.forms[0].getAttribute(\"action\");\n" );  
            sb.append("     if(url.indexOf('?')>-1){url += \"&" +(entityOrField? "currentPage" : "page.currentPage" )+ "=\";}\n" );  
            sb.append("     else{url += \"?" +(entityOrField? "currentPage" : "page.currentPage" )+ "=\";}\n" );  
            sb.append("     document.forms[0].action = url+page;\n" );  
            sb.append("     document.forms[0].submit();\n" );  
            sb.append(" }else{\n" );  
            sb.append("     var url = document.location+';\n" );  
            sb.append("     if(url.indexOf('?')>-1){\n" );  
            sb.append("         if(url.indexOf('currentPage')>-1){\n" );  
            sb.append("             var reg = /currentPage=\\d*/g;\n" );  
            sb.append("             url = url.replace(reg,'currentPage=');\n" );  
            sb.append("         }else{\n" );  
            sb.append("             url += \"&" +(entityOrField? "currentPage" : "page.currentPage" )+ "=\";\n" );  
            sb.append("         }\n" );  
            sb.append("     }else{url += \"?" +(entityOrField? "currentPage" : "page.currentPage" )+ "=\";}\n" );  
            sb.append("     document.location = url + page;\n" );  
            sb.append(" }\n" );  
            sb.append("}\n" );  
            sb.append("</script>\n" );  
        }  
        pageStr = sb.toString();  
        return  pageStr;  
    }  
    public   void  setPageStr(String pageStr) {  
        this .pageStr = pageStr;  
    }  
    public   int  getShowCount() {  
        return  showCount;  
    }  
    public   void  setShowCount( int  showCount) {  
        this .showCount = showCount;  
    }  
    public   int  getCurrentResult() {  
        currentResult = (getCurrentPage()-1 )*getShowCount();  
        if (currentResult< 0 )  
            currentResult = 0 ;  
        return  currentResult;  
    }  
    public   void  setCurrentResult( int  currentResult) {  
        this .currentResult = currentResult;  
    }  
    public   boolean  isEntityOrField() {  
        return  entityOrField;  
    }  
    public   void  setEntityOrField( boolean  entityOrField) {  
        this .entityOrField = entityOrField;  
    }  
}

    

通过拦截StatementHandler的prepare方法的分页插件类

@Intercepts ({ @Signature (type=StatementHandler. class ,method= "prepare" ,args={Connection. class })})  
public   class  PagePlugin  implements  Interceptor {  
  
    private   static  String dialect =  "" ;  //数据库方言   
    private   static  String pageSqlId =  "" ;  //mapper.xml中需要拦截的ID(正则匹配)   
      
    public  Object intercept(Invocation ivk)  throws  Throwable {  
        // TODO Auto-generated method stub   
        if (ivk.getTarget()  instanceof  RoutingStatementHandler){  
            RoutingStatementHandler statementHandler = (RoutingStatementHandler)ivk.getTarget();  
            BaseStatementHandler delegate = (BaseStatementHandler) ReflectHelper.getValueByFieldName(statementHandler, "delegate" );  
            MappedStatement mappedStatement = (MappedStatement) ReflectHelper.getValueByFieldName(delegate, "mappedStatement" );  
              
            if (mappedStatement.getId().matches(pageSqlId)){  //拦截需要分页的SQL   
                BoundSql boundSql = delegate.getBoundSql();  
                Object parameterObject = boundSql.getParameterObject();//分页SQL<select>中parameterType属性对应的实体参数，即Mapper接口中执行分页方法的参数,该参数不得为空   
                if (parameterObject== null ){  
                    throw   new  NullPointerException( "parameterObject尚未实例化！" );  
                }else {  
                    Connection connection = (Connection) ivk.getArgs()[0 ];  
                    String sql = boundSql.getSql();  
                    String countSql = "select count(0) from ("  + sql+  ") as tmp_count" ;  //记录统计   
                    PreparedStatement countStmt = connection.prepareStatement(countSql);  
                    //BoundSql countBS = new  BoundSql(mappedStatement.getConfiguration(),countSql,boundSql.getParameterMappings(),parameterObject);  
                    setParameters(countStmt,mappedStatement,boundSql,parameterObject);  
                    ResultSet rs = countStmt.executeQuery();  
                    int  count =  0 ;  
                    if  (rs.next()) {  
                        count = rs.getInt(1 );  
                    }  
                    rs.close();  
                    countStmt.close();  
                    //System.out.println(count);   
                    Page page = null ;  
                    if (parameterObject  instanceof  Page){     //参数就是Page实体   
                         page = (Page) parameterObject;  
                         page.setEntityOrField(true );     //见com.flf.entity.Page.entityOrField 注释   
                        page.setTotalResult(count);  
                    }else {   //参数为某个实体，该实体拥有Page属性   
                        Field pageField = ReflectHelper.getFieldByFieldName(parameterObject,"page" );  
                        if (pageField!= null ){  
                            page = (Page) ReflectHelper.getValueByFieldName(parameterObject,"page" );  
                            if (page== null )  
                                page = new  Page();  
                            page.setEntityOrField(false );  //见com.flf.entity.Page.entityOrField 注释   
                            page.setTotalResult(count);  
                            ReflectHelper.setValueByFieldName(parameterObject,"page" , page);  //通过反射，对实体对象设置分页对象   
                        }else {  
                            throw   new  NoSuchFieldException(parameterObject.getClass().getName()+ "不存在 page 属性！" );  
                        }  
                    }  
                    String pageSql = generatePageSql(sql,page);  
                    ReflectHelper.setValueByFieldName(boundSql, "sql" , pageSql);  //将分页sql语句反射回BoundSql.   
                }  
            }  
        }  
        return  ivk.proceed();  
    }  
  
      
    /**  
     * 对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());  
                }  
            }  
        }  
    }  
      
    /**  
     * 根据数据库方言，生成特定的分页sql  
     * @param sql  
     * @param page  
     * @return  
     */   
    private  String generatePageSql(String sql,Page page){  
        if (page!= null  && Tools.notEmpty(dialect)){  
            StringBuffer pageSql = new  StringBuffer();  
            if ( "mysql" .equals(dialect)){  
                pageSql.append(sql);  
                pageSql.append(" limit " +page.getCurrentResult()+ "," +page.getShowCount());  
            }else   if ( "oracle" .equals(dialect)){  
                pageSql.append("select * from (select tmp_tb.*,ROWNUM row_id from (" );  
                pageSql.append(sql);  
                pageSql.append(") as tmp_tb where ROWNUM<=" );  
                pageSql.append(page.getCurrentResult()+page.getShowCount());  
                pageSql.append(") where row_id>" );  
                pageSql.append(page.getCurrentResult());  
            }  
            return  pageSql.toString();  
        }else {  
            return  sql;  
        }  
    }  
      
    public  Object plugin(Object arg0) {  
        // TODO Auto-generated method stub   
        return  Plugin.wrap(arg0,  this );  
    }  
  
    public   void  setProperties(Properties p) {  
        dialect = p.getProperty("dialect" );  
        if  (Tools.isEmpty(dialect)) {  
            try  {  
                throw   new  PropertyException( "dialect property is not found!" );  
            } catch  (PropertyException e) {  
                // TODO Auto-generated catch block   
                e.printStackTrace();  
            }  
        }  
        pageSqlId = p.getProperty("pageSqlId" );  
        if  (Tools.isEmpty(pageSqlId)) {  
            try  {  
                throw   new  PropertyException( "pageSqlId property is not found!" );  
            } catch  (PropertyException e) {  
                // TODO Auto-generated catch block   
                e.printStackTrace();  
            }  
        }  
    }  
      

}  

 

该类中定义了两个私有静态变量：dialect、pageSqlId，这两个变量是在该类初始化时，即调用setProperties方法时初始化的，相关的值是在MyBatis配置文件中配置的，如下：

< plugins >   
    < plugin   interceptor = "com.flf.plugin.PagePlugin" >   
        < property   name = "dialect"   value = "mysql" />   
        < property   name = "pageSqlId"   value = ".*listPage.*" />   
    </ plugin >   

</ plugins >   

 

上面的示例表明数据库方言为mysql，拦截所有mapper.xml映射文件中id包含有listPage的SQL。

反射帮助类：

/**  
 * @author Administrator  
 *  反射工具  
 */   
public   class  ReflectHelper {  
    /**  
     * 获取obj对象fieldName的Field  
     * @param obj  
     * @param fieldName  
     * @return  
     */   
    public   static  Field getFieldByFieldName(Object obj, String fieldName) {  
        for  (Class<?> superClass = obj.getClass(); superClass != Object. class ; superClass = superClass  
                .getSuperclass()) {  
            try  {  
                return  superClass.getDeclaredField(fieldName);  
            } catch  (NoSuchFieldException e) {  
            }  
        }  
        return   null ;  
    }  
  
    /**  
     * 获取obj对象fieldName的属性值  
     * @param obj  
     * @param fieldName  
     * @return  
     * @throws SecurityException  
     * @throws NoSuchFieldException  
     * @throws IllegalArgumentException  
     * @throws IllegalAccessException  
     */   
    public   static  Object getValueByFieldName(Object obj, String fieldName)  
            throws  SecurityException, NoSuchFieldException,  
            IllegalArgumentException, IllegalAccessException {  
        Field field = getFieldByFieldName(obj, fieldName);  
        Object value = null ;  
        if (field!= null ){  
            if  (field.isAccessible()) {  
                value = field.get(obj);  
            } else  {  
                field.setAccessible(true );  
                value = field.get(obj);  
                field.setAccessible(false );  
            }  
        }  
        return  value;  
    }  
  
    /**  
     * 设置obj对象fieldName的属性值  
     * @param obj  
     * @param fieldName  
     * @param value  
     * @throws SecurityException  
     * @throws NoSuchFieldException  
     * @throws IllegalArgumentException  
     * @throws IllegalAccessException  
     */   
    public   static   void  setValueByFieldName(Object obj, String fieldName,  
            Object value) throws  SecurityException, NoSuchFieldException,  
            IllegalArgumentException, IllegalAccessException {  
        Field field = obj.getClass().getDeclaredField(fieldName);  
        if  (field.isAccessible()) {  
            field.set(obj, value);  
        } else  {  
            field.setAccessible(true );  
            field.set(obj, value);  
            field.setAccessible(false );  
        }  
    }  

}  

 

PS：分页插件类PagePlugin只实现了mysql和oracle的分页，至于其他的数据库，可以修改该类的generatePageSql方法。