手写Mybatis分页插件

落日映苍穹つ 2022-05-30 09:16 385阅读 0赞

之前涉及到mybatis分页时，都是先查询一遍总和，再用Limit,导致了项目中有大量的冗余代码,非常的难看，网上不管是框架，还是人家提到的各种自以为是的解决办法，基本上都是下面两种:

1.先查一下总条数，再用Limt

2.直接查出所有，再用subList截取

就我个人而言，我觉的两种做法都很愚蠢，但是呢我还是没找到一种很好的解决方案，也就只能用框架来解决这种事情，使代码更简洁，总之，相同的事情都可以抽取出来，封装成框架，像我今天所说的这个就是传入分页参数，然后自动帮我们补齐 select count(0) from "sql"和 "sql"+"limit ?,?"，很简单，mybatis为我们提供了拦截器插件，可以方便我们来操作.

Mybatis的配置文件加上这一句

核心拦截类

package com.yayiabc.common.utils;
    
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.util.Collection;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Properties;
    
    
    import org.apache.ibatis.executor.ErrorContext;
    import org.apache.ibatis.executor.ExecutorException;
    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.Signature;
    import org.apache.ibatis.reflection.MetaObject;
    import org.apache.ibatis.reflection.SystemMetaObject;
    import org.apache.ibatis.reflection.property.PropertyTokenizer;
    import org.apache.ibatis.scripting.xmltags.ForEachSqlNode;
    import org.apache.ibatis.session.Configuration;
    import org.apache.ibatis.type.TypeHandler;
    import org.apache.ibatis.type.TypeHandlerRegistry;
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    
    import com.mysql.jdbc.Connection;
    
    @Intercepts(@Signature(type=StatementHandler.class,method="prepare",args={Collection.class}))
    public class MyInterceptor implements Interceptor{
    	
    	private final Logger logger=LoggerFactory.getLogger(MyInterceptor.class);
    
    	
    	/**
    	 * 插件运行的代码，它将代替原有的方法，要重写最重要的intercept方法
    	 */
    	public Object intercept(Invocation invocation) throws Throwable {
    		if(invocation.getTarget() instanceof StatementHandler){
    			//这里我们有一个设定  如果查询方法含有Page 就进行分页 其他方法无视  
                //所以就要获取方法名  
    			StatementHandler statementHandler=(StatementHandler)invocation.getTarget();
    			MetaObject metaObject=SystemMetaObject.forObject(statementHandler);
    			MappedStatement mappedStatement=(MappedStatement)metaObject.getValue("delegate.mappedStatement");
    			String selectId=mappedStatement.getId();
    			if(selectId.matches(".*Page$")){
    				BoundSql boundSql=(BoundSql)metaObject.getValue("delegate.BoundSql");
    				String sql=boundSql.getSql();
    				HashMap<String,Object> hashMap=(HashMap<String, Object>)(boundSql.getParameterObject());
    				MyPage myPage=(MyPage)hashMap.get("page");
    				//重写SQL
    				String countSql="select count(0) from "+sql;
    				String pageSql=sql+"limit "+myPage.getPageBegin()+","+myPage.getPageSize();
    				
    				Connection connection=(Connection)invocation.getArgs()[0];
    				
    				PreparedStatement cStatement=null;
    				ResultSet rs=null;
    				int totalCount=0;
    				try {
    					cStatement=connection.prepareStatement(countSql);
    					setParameters(cStatement,mappedStatement,boundSql,boundSql.getParameterObject());
    					rs=cStatement.executeQuery();
    					if(rs.next()){
    						totalCount=rs.getInt(1);
    					}
    				} catch (Exception e) {
    					logger.info(e.getMessage());
    				}finally {
    					try {    
                            rs.close();    
                            cStatement.close();    
                        } catch (Exception e) {    
                           logger.error("SQLException", e);  
                        }   
    				}
    				metaObject.setValue("delegate.boundSql.sql", pageSql);
    				
    				myPage.setNumCount(totalCount);
    			}
    			
    			
    		}
    		
    		return invocation.proceed();
    	}
    
    	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());  
                    }  
                }  
            }  
    		
    	}
    
    	public Object plugin(Object arg0) {
    		// 
    		return null;
    	}
    
    	public void setProperties(Properties arg0) {
    		// 
    		
    	}
    
    }

分页辅助类 myPage

package com.yayiabc.common.utils;
    
    import java.util.Map;
    
    public class MyPage {
    	//----------分页-----------     
        private int pageSize;//每页显示条数  
        private int pageCurrentPage;//第几页  
        private int pageBegin;//开始位置  
        private int numCount;//总条数  
        private int pageTotal;//总条数  
          
        private String orderField = "";//控制排序页面显示的  
        private String orderDirection = "";  
          
        public MyPage(){  
              
        }  
          
        public MyPage(int pageSize, int pageCurrentPage) {  
            super();  
            this.pageSize = pageSize;  
            this.pageCurrentPage = pageCurrentPage;  
        }  
          
        public MyPage(Map<String, String> map){  
            if(map.get("pageNum")!=null){  
                this.setPageCurrentPage(this.pageCurrentPage = Integer.parseInt(map.get("pageNum")));//要查询的页数  
            }else{  
                this.setPageCurrentPage(1);//设置初始值  
            }  
              
            if(map.get("numPerPage")!=null){  
                this.setPageSize(Integer.parseInt(map.get("numPerPage")));//每页显示条数  
            }else{  
                this.setPageSize(5);//设置初始值  
            }  
              
            if(map.get("orderField")!=null){  
                this.setOrderField(map.get("orderField"));  
            }  
              
            if(map.get("orderDirection")!=null){  
                this.setOrderDirection(map.get("orderDirection"));  
            }  
              
        }  
      
        public int getPageCurrentPage() {  
            return pageCurrentPage;  
        }  
        public void setPageCurrentPage(int pageCurrentPage) {  
            this.pageCurrentPage = pageCurrentPage;  
        }  
        public int getNumCount() {  
            return numCount;  
        }  
        public void setNumCount(int numCount) {  
            this.numCount = numCount;  
        }  
        public int getPageTotal() {  
            return (numCount%pageSize>0)?(numCount/pageSize+1):(numCount/pageSize);  
        }  
        public void setPageTotal(int pageTotal) {  
            this.pageTotal = pageTotal;  
        }  
        public int getPageSize() {  
            return pageSize;  
        }  
        public void setPageSize(int pageSize) {  
            this.pageSize = pageSize;  
        }  
        public int getPageBegin() {  
            return pageSize*(pageCurrentPage-1);  
        }  
        public void setPageBegin(int pageBegin) {  
            this.pageBegin = pageBegin;  
        }  
          
        public String getOrderField() {  
            return orderField;  
        }  
      
        public void setOrderField(String orderField) {  
            this.orderField = orderField;  
        }  
      
        public String getOrderDirection() {  
            return orderDirection;  
        }  
      
        public void setOrderDirection(String orderDirection) {  
            this.orderDirection = orderDirection;  
        }  
      
        public static MyPage getPage(int pageSize, int pageCurrentPage){  
            return new MyPage(pageSize,pageCurrentPage);  
        }  
      
        public static MyPage getPage(Map map){  
            return new MyPage(map);  
        }  
          
    }