MyBatis Generator分页插件RowBoundsPlugin坑

绝地灬酷狼 2023-07-10 08:40 5阅读 0赞

### 一、Mybatis Generator设置分页 ###

Mybatis Generator没有设置分页，但自带了个RowBoundsPlugin分页插件，故而配置

在mysql数据库进行查询的表分页10条，查很久花费了9s，表的数据也只是十几万行。百思不得其解，查看RowBoundsPlugin生成的代码，如下：

public interface IDomainDao {
    
        //省略其他代码
        @SelectProvider(type=DomainSqlProvider.class, method="selectByExample")
        @Results({
            @Result(column="id", property="id", jdbcType=JdbcType.INTEGER, id=true),
            @Result(column="domain", property="domain", jdbcType=JdbcType.VARCHAR)
        })
        List<Domain> selectByExampleWithRowbounds(DomainExample example, RowBounds rowBounds);
    }

public class DomainSqlProvider {
    
        //省略其他代码
        public String selectByExample(DomainExample example) {
            SQL sql = new SQL();
            if (example != null && example.isDistinct()) {
                sql.SELECT_DISTINCT("id");
            } else {
                sql.SELECT("id");
            }
            sql.SELECT("domain");
      
            sql.FROM("domain");
            applyWhere(sql, example, false);
            
            if (example != null && example.getOrderByClause() != null) {
                sql.ORDER_BY(example.getOrderByClause());
            }
            
            return sql.toString();
        }
    }

selectByExample方法里面没有使用limit进行分页，使用mybatis自带的RowBounds功能进行内存分页。故而查询十条数据，其实是把整张表符合要求的数据都查询出来了，在程序的内存进行截取，故而花费了9s。

### 二、解决方案 ###

对Mysql

（1）使用example.setOrderByClause()方法进行sql注入limit分页

（2）重些插件改造example进行分页

public class MySqlLimitPlugin extends PluginAdapter {
    
        @Override
        public boolean validate(List<String> list) {
            return true;
        }
    
        /**
         * Example类添加offset和limit属性和set、get方法
         */
        @Override
        public boolean modelExampleClassGenerated(TopLevelClass topLevelClass, IntrospectedTable introspectedTable) {
    
            PrimitiveTypeWrapper integerWrapper = FullyQualifiedJavaType.getIntInstance().getPrimitiveTypeWrapper();
    
            Field limit = new Field();
            limit.setName("limit");
            limit.setVisibility(JavaVisibility.PRIVATE);
            limit.setType(integerWrapper);
            topLevelClass.addField(limit);
    
            Method setLimit = new Method();
            setLimit.setVisibility(JavaVisibility.PUBLIC);
            setLimit.setName("setLimit");
            setLimit.addParameter(new Parameter(integerWrapper, "limit"));
            setLimit.addBodyLine("this.limit = limit;");
            topLevelClass.addMethod(setLimit);
    
            Method getLimit = new Method();
            getLimit.setVisibility(JavaVisibility.PUBLIC);
            getLimit.setReturnType(integerWrapper);
            getLimit.setName("getLimit");
            getLimit.addBodyLine("return limit;");
            topLevelClass.addMethod(getLimit);
    
            Field offset = new Field();
            offset.setName("offset");
            offset.setVisibility(JavaVisibility.PRIVATE);
            offset.setType(integerWrapper);
            topLevelClass.addField(offset);
    
            Method setOffset = new Method();
            setOffset.setVisibility(JavaVisibility.PUBLIC);
            setOffset.setName("setOffset");
            setOffset.addParameter(new Parameter(integerWrapper, "offset"));
            setOffset.addBodyLine("this.offset = offset;");
            topLevelClass.addMethod(setOffset);
    
            Method getOffset = new Method();
            getOffset.setVisibility(JavaVisibility.PUBLIC);
            getOffset.setReturnType(integerWrapper);
            getOffset.setName("getOffset");
            getOffset.addBodyLine("return offset;");
            topLevelClass.addMethod(getOffset);
    
            return true;
        }
    
        /**
         * Mapper.xml的selectByExampleWithoutBLOBs添加limit
         */
        @Override
        public boolean sqlMapSelectByExampleWithoutBLOBsElementGenerated(XmlElement element,
                                                                         IntrospectedTable introspectedTable) {
    
            addXmlLimit(element);
    
            return true;
        }
    
        /**
         * Mapper.xml的selectByExampleWithBLOBs添加limit
         */    
        @Override
        public boolean sqlMapSelectByExampleWithBLOBsElementGenerated(XmlElement element,
                                                                      IntrospectedTable introspectedTable) {
    
            addXmlLimit(element);
    
            return true;
        }
    
        private void addXmlLimit(XmlElement element) {
            XmlElement ifLimitNotNullElement = new XmlElement("if");
            ifLimitNotNullElement.addAttribute(new Attribute("test", "limit != null"));
    
            XmlElement ifOffsetNotNullElement = new XmlElement("if");
            ifOffsetNotNullElement.addAttribute(new Attribute("test", "offset != null"));
            ifOffsetNotNullElement.addElement(new TextElement("limit ${offset}, ${limit}"));
            ifLimitNotNullElement.addElement(ifOffsetNotNullElement);
    
            XmlElement ifOffsetNullElement = new XmlElement("if");
            ifOffsetNullElement.addAttribute(new Attribute("test", "offset == null"));
            ifOffsetNullElement.addElement(new TextElement("limit ${limit}"));
            ifLimitNotNullElement.addElement(ifOffsetNullElement);
    
            element.addElement(ifLimitNotNullElement);
        }
    
    
        /**
         * 注解selectByExampleWithBLOBs方法生成分页
         */
        public boolean providerSelectByExampleWithBLOBsMethodGenerated(Method method, TopLevelClass topLevelClass, IntrospectedTable introspectedTable) {
    
            addAnnotationLimit(method, topLevelClass, introspectedTable);
            return super.providerSelectByExampleWithBLOBsMethodGenerated(method, topLevelClass, introspectedTable);
        }
    
        /**
         * 注解selectByExampleWithoutBLOBs方法生成分页
         */
        public boolean providerSelectByExampleWithoutBLOBsMethodGenerated(Method method, TopLevelClass topLevelClass, IntrospectedTable introspectedTable) {
    
            addAnnotationLimit(method, topLevelClass, introspectedTable);
            return super.providerSelectByExampleWithoutBLOBsMethodGenerated(method, topLevelClass, introspectedTable);
        }
    
        private void addAnnotationLimit(Method method, TopLevelClass topLevelClass, IntrospectedTable introspectedTable) {
            
            List<String> limits = Arrays.asList(
                    "if (example != null && example.getLimit() != null) {",
                    "if (example.getOffset() != null) {",
                    "sql.OFFSET(example.getOffset());",
                    "}",
                    "sql.LIMIT(example.getLimit());",
                    "}"
            );
    
            method.addBodyLines(method.getBodyLines().size() - 1, limits);
        }
    
    }