本文共 6074 字，大约阅读时间需要 20 分钟。

一、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
   
     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
   
     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
    
      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);    }}
    
   

转载地址：https://maokun.blog.csdn.net/article/details/104594791 如侵犯您的版权，请留言回复原文章的地址，我们会给您删除此文章，给您带来不便请您谅解！