支持连表等复杂查询的PageHelper——PageHelper5.1.4 Mybatis3.4.6
2020-02-05 暂无标签

一、PageHelper中分页存在的问题:

  • 主表结构如下:
    provider_order表,订单主表。我们这里简化处理,它只有一个字段,即id。

VARCHAR  |  po_id   |    订单表id

  • 从表结构如下:
    order_info表,订单从表。它负责记录订单的一些信息,与主表是一对多的关系。

VARCHAR  |  oi_id  |  订单信息id VARCHAR  |  po_id   |    订单表id TEXT    |  info   |   信息详情

  • 现在我们需要一个简单的连表查询操作,sql语句如下:
<select id="getProviderOrderWithOrderInfoTest" resultMap="ResultMapWithOrderInfo" parameterType="java.lang.String">
        SELECT *
        FROM (SELECT * FROM provider_order WHERE po_id LIKE #{po_id}) AS limitable
            LEFT JOIN order_info ON limitable.po_id = order_info.provider_order_id
    </select>
  • 进行PageHelper的分页操作,查询前十条记录:
 Page page = PageHelper.startPage(0, 10);
        List<ProviderOrder> providerOrderList = testService.getProviderOrderWithOrderInfoTest("%-%");
        PageInfo<ProviderOrder> pageInfo = new PageInfo<>(providerOrderList);

        System.out.println(pageInfo);
  • 目前我们的主表有两条记录,从表有二十条记录。

请输入图片描述

我们会发现,查询出来的总数是20,然后查询结果是两个list(各5条从表信息),不仅如此,分页数据也很奇怪,我们只有两条主表记录,但却被分成了两页(pages = 2),并且hasNextPage = true。这并不是我们要的结果。我们想要查询的效果是:

前十条 订单 记录,然后连表查询出该十条订单记录下的所有 订单信息
也就是说,结果集中,首先我们应该得到两个list,list中各10条从表信息,而且也不该有下一页,总页数应为1,总数据数应为2。

查看PageHelper的源码我们来看看,它的count,以及limit,到底是怎么回事。

PageHelper的count实现

 private Long executeAutoCount(Executor executor, MappedStatement countMs, Object parameter, BoundSql boundSql, RowBounds rowBounds, ResultHandler resultHandler) throws IllegalAccessException,
        SQLException {
        Map<String, Object> additionalParameters = (Map) this.additionalParametersField.get(boundSql);
        CacheKey countKey = executor.createCacheKey(countMs, parameter, RowBounds.DEFAULT, boundSql);
        String countSql = this.dialect.getCountSql(countMs, boundSql, parameter, rowBounds, countKey);
        BoundSql countBoundSql = new BoundSql(countMs.getConfiguration(), countSql, boundSql.getParameterMappings(), parameter);
        Iterator var11 = additionalParameters.keySet()
                                             .iterator();

        while (var11.hasNext()) {
            String key = (String) var11.next();
            countBoundSql.setAdditionalParameter(key, additionalParameters.get(key));
        }

        Object countResultList = executor.query(countMs, parameter, RowBounds.DEFAULT, resultHandler, countKey, countBoundSql);
        Long count = (Long) ((List) countResultList).get(0);

        // 获取count
        return count;
    }

我们很容易看出来,count的sql语句,在这里经过了处理,这里调用了dialect的getCountSql方法。 它的实现如下:

 public String getSmartCountSql(String sql, String name) {
        Statement stmt = null;
        if (sql.indexOf("/*keep orderby*/") >= 0) {
            return this.getSimpleCountSql(sql);
        } else {
            try {
                stmt = CCJSqlParserUtil.parse(sql);
            } catch (Throwable var8) {
                return this.getSimpleCountSql(sql);
            }

            Select select = (Select)stmt;
            SelectBody selectBody = select.getSelectBody();

            try {
                this.processSelectBody(selectBody);
            } catch (Exception var7) {
                return this.getSimpleCountSql(sql);
            }

            this.processWithItemsList(select.getWithItemsList());
            this.sqlToCount(select, name);
            String result = select.toString();
            return result;
        }
    }

    public String getSimpleCountSql(String sql) {
        return this.getSimpleCountSql(sql, "0");
    }

    public String getSimpleCountSql(String sql, String name) {
        StringBuilder stringBuilder = new StringBuilder(sql.length() + 40);
        stringBuilder.append("select count(");
        stringBuilder.append(name);
        stringBuilder.append(") from (");
        stringBuilder.append(sql);
        stringBuilder.append(") tmp_count");
        return stringBuilder.toString();
    }

简单来说,就是在sql外面套一层select count语句,我们的sql语句变成了 SELECT count(0) FROM (我们的sql语句)(真实情况没有这么简单,这只是其中一种情况。)这样就很容易得知,为什么最后的total是20(所有数据数),而不是2(主表数据数)了。

请输入图片描述

三、 PageHelper的分页实现

与上面相同,这里以dialect(为了适配各种数据库)为mysql为例,我们可以看到,如果你没有在sql语句里面写limit,就会在sql语句的最后面,添加limit语句。

public String getPageSql(String sql, RowBounds rowBounds, CacheKey pageKey) {
        StringBuilder sqlBuilder = new StringBuilder(sql.length() + 14);
        sqlBuilder.append(sql);
        if (rowBounds.getOffset() == 0) {
            sqlBuilder.append(" LIMIT ");
            sqlBuilder.append(rowBounds.getLimit());
        } else {
            sqlBuilder.append(" LIMIT ");
            sqlBuilder.append(rowBounds.getOffset());
            sqlBuilder.append(",");
            sqlBuilder.append(rowBounds.getLimit());
            pageKey.update(rowBounds.getOffset());
        }

        pageKey.update(rowBounds.getLimit());
        return sqlBuilder.toString();
    }

程序运行得到sql语句如下,PageHelper对查询的总结果集(包括主表数据与从表数据),进行了分页

在得知PageHelper在这两个步骤的原理后,我们开始对PageHelper进行改造。

请输入图片描述

四、 count结果修正

在上面的count源码中,我们可以看到这么一段代码:

  public String getSmartCountSql(String sql, String name) {
        Statement stmt = null;
        if (sql.indexOf("/*keep orderby*/") >= 0) {
            return this.getSimpleCountSql(sql);
        } else {
            try {
                stmt = CCJSqlParserUtil.parse(sql);
            } catch (Throwable var8) {
                return this.getSimpleCountSql(sql);
            }

            Select select = (Select)stmt;
            SelectBody selectBody = select.getSelectBody();

            try {
                this.processSelectBody(selectBody);
            } catch (Exception var7) {
                return this.getSimpleCountSql(sql);
            }

            this.processWithItemsList(select.getWithItemsList());
            this.sqlToCount(select, name);
            String result = select.toString();
            return result;
        }
    }


    public void sqlToCount(Select select, String name) {
        SelectBody selectBody = select.getSelectBody();
        List<SelectItem> COUNT_ITEM = new ArrayList();
        COUNT_ITEM.add(new SelectExpressionItem(new Column("count(" + name + ")")));
        if (selectBody instanceof PlainSelect && this.isSimpleCount((PlainSelect)selectBody)) {
            ((PlainSelect)selectBody).setSelectItems(COUNT_ITEM);
        } else {
            PlainSelect plainSelect = new PlainSelect();
            SubSelect subSelect = new SubSelect();
            subSelect.setSelectBody(selectBody);
            subSelect.setAlias(TABLE_ALIAS);
            plainSelect.setFromItem(subSelect);
            plainSelect.setSelectItems(COUNT_ITEM);
            select.setSelectBody(plainSelect);
        }

    }

实际上PageHelper是有提供给我们一套指定colum查询的入口的。我们将查询代码做如下改造:

   Page page = PageHelper.startPage(0, 10);
        page.setCountColumn("DISTINCT po_id");
        List<ProviderOrder> providerOrderList = testService.getProviderOrderWithOrderInfoTest("%-%");

        PageInfo<ProviderOrder> pageInfo = new PageInfo<>(providerOrderList);

        System.out.println(pageInfo);
请输入代码

我们会发现,我们需要的分页数据正常了,总页码(pages:1),total:2。

请输入链接描述
五、 查询结果集修正

我们在三中,发现了PageHelper limit添加的位置不正确,我们需要将其放在正确的位置下。所以新建一个类如下:

/**
 * Created by Anur IjuoKaruKas on 2018/6/8
 */
public class PageSqlResolver {

    public static final String SQL_SIGN = "AS limitable";

    public static final String LIMIT_SIGN = "LIMIT ?";

    public static final String LIMIT_SIGN_EX = "LIMIT ?, ?";

    public static String resolveLimit(String pageSql) {
        if (pageSql == null) {
            return null;
        }

        if (pageSql.contains(SQL_SIGN)) {// 如果需要特殊分页
            String changer = "";

            if (pageSql.contains(LIMIT_SIGN_EX)) {
                changer = LIMIT_SIGN_EX;
            } else if (pageSql.contains(LIMIT_SIGN)) {
                changer = LIMIT_SIGN;
            }

            pageSql = pageSql.replace(changer, "");
            StringBuilder sqlBuilder = new StringBuilder(pageSql);

            StringBuilder mae = new StringBuilder(sqlBuilder.substring(0, sqlBuilder.indexOf(SQL_SIGN)));// mae 截止sql语句到 limitable
            StringBuilder uShiRo = new StringBuilder(sqlBuilder.substring(sqlBuilder.indexOf(SQL_SIGN), sqlBuilder.length()));// 剩余的

            mae.insert(mae.lastIndexOf(")"), String.format(" %s", changer));

            return mae.append(uShiRo)
                      .toString();
        } else {
            return pageSql;
        }
    }
}

这段代码的含义是:将AS limitable,也就是主表,标记为真正分页的地方,并向其添如limit ? ,?

SELECT *
        FROM (SELECT * FROM provider_order WHERE po_id LIKE ?) AS limitable
            LEFT JOIN order_info ON limitable.po_id = order_info.provider_order_id LIMIT ?

sql语句从上面变成了:

SELECT *
        FROM (SELECT * FROM provider_order WHERE po_id LIKE ? LIMIT ?) AS limitable
            LEFT JOIN order_info ON limitable.po_id = order_info.provider_order_id  

我们发现这就是我们想要的结果,它的分页是针对主表进行的,而不是针对结果集。

请输入图片描述

解析到这里就结束了
https://github.com/anurnomeru/page-helper-ext demo示例请点击这里

六、改造步骤详解

1、引入依赖 这里使用了mybatis 3.4.6(tk.mybatis 4.0.2),pagehelper5.1.4(pagehelper-spring-boot-starter1.2.5),你可以引入原依赖,也可以用这里使用的tkMybatis,以及pagehelper-spring-boot-starter。


<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.anur</groupId>
    <artifactId>page-helper</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>jar</packaging>

    <name>page-helper</name>
    <description>Demo project for Spring Boot</description>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.0.2.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <!-- page - helper -->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.2.5</version>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

        <!-- codeGen -->
        <dependency>
            <groupId>org.mybatis.generator</groupId>
            <artifactId>mybatis-generator-core</artifactId>
            <version>1.3.6</version>
        </dependency>

        <!-- tk - mybatis -->
        <dependency>
            <groupId>tk.mybatis</groupId>
            <artifactId>mapper</artifactId>
            <version>4.0.2</version>
        </dependency>

        <!-- mysql -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>

        <!-- https://mvnrepository.com/artifact/com.alibaba/fastjson -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.47</version>
        </dependency>

    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <configuration>
                    <proc>none</proc>
                </configuration>
            </plugin>
        </plugins>
    </build>


</project>

2、新增两个类,用于修正查询时sql语句

package com.anur.pagehelper.page;

//
// Source code recreated from a .class file by IntelliJ IDEA
// (powered by Fernflower decompiler)
//

import com.github.pagehelper.Dialect;
import com.github.pagehelper.PageException;
import com.github.pagehelper.cache.Cache;
import com.github.pagehelper.cache.CacheFactory;
import com.github.pagehelper.util.MSUtils;
import com.github.pagehelper.util.StringUtil;
import java.lang.reflect.Field;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import org.apache.ibatis.cache.CacheKey;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
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.session.Configuration;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;

/**
 * Created by Anur IjuoKaruKas on 2018/6/8
 */

@Intercepts({
    @Signature(
        type = Executor.class,
        method = "query",
        args = {
            MappedStatement.class,
            Object.class,
            RowBounds.class,
            ResultHandler.class
        }
    ),
    @Signature(
        type = Executor.class,
        method = "query",
        args = {
            MappedStatement.class,
            Object.class,
            RowBounds.class,
            ResultHandler.class,
            CacheKey.class,
            BoundSql.class
        }
    )
})
public class CustomPageInterceptor implements Interceptor {

    protected Cache<String, MappedStatement> msCountMap = null;

    private Dialect dialect;

    private String default_dialect_class = "com.github.pagehelper.PageHelper";

    private Field additionalParametersField;

    private String countSuffix = "_COUNT";

    public CustomPageInterceptor() {
    }

    /**
     * 拦截
     */
    public Object intercept(Invocation invocation) throws Throwable {
        try {
            Object[] args = invocation.getArgs();

            // 对应一个Mapper节点,描述一条sql语句
            MappedStatement ms = (MappedStatement) args[0];

            // 参数
            Object parameter = args[1];

            // mybatis自带分页
            RowBounds rowBounds = (RowBounds) args[2];

            // 结果集处理器
            ResultHandler resultHandler = (ResultHandler) args[3];

            // 执行器
            Executor executor = (Executor) invocation.getTarget();
            CacheKey cacheKey;
            BoundSql boundSql;

            if (args.length == 4) {
                boundSql = ms.getBoundSql(parameter);
                cacheKey = executor.createCacheKey(ms, parameter, rowBounds, boundSql);
            } else {
                cacheKey = (CacheKey) args[4];
                boundSql = (BoundSql) args[5];
            }

            List resultList;
            if (this.dialect.skip(ms, parameter, rowBounds)) {
                resultList = executor.query(ms, parameter, rowBounds, resultHandler, cacheKey, boundSql);
            } else {
                String msId = ms.getId();
                Configuration configuration = ms.getConfiguration();
                Map<String, Object> additionalParameters = (Map) this.additionalParametersField.get(boundSql);
                if (this.dialect.beforeCount(ms, parameter, rowBounds)) {
                    String countMsId = msId + this.countSuffix;
                    MappedStatement countMs = this.getExistedMappedStatement(configuration, countMsId);
                    Long count;
                    if (countMs != null) {
                        count = this.executeManualCount(executor, countMs, parameter, boundSql, resultHandler);
                    } else {
                        countMs = (MappedStatement) this.msCountMap.get(countMsId);
                        if (countMs == null) {
                            countMs = MSUtils.newCountMappedStatement(ms, countMsId);
                            this.msCountMap.put(countMsId, countMs);
                        }

                        // 这里是count,可能要改
//                        boundSql = PageSqlResolver.resolveCount(configuration, boundSql, parameter);
                        count = this.executeAutoCount(executor, countMs, parameter, boundSql, rowBounds, resultHandler);
                    }

                    if (!this.dialect.afterCount(count, parameter, rowBounds)) {
                        Object var24 = this.dialect.afterPage(new ArrayList(), parameter, rowBounds);
                        return var24;
                    }
                }

                if (!this.dialect.beforePage(ms, parameter, rowBounds)) {
                    resultList = executor.query(ms, parameter, RowBounds.DEFAULT, resultHandler, cacheKey, boundSql);
                } else {
                    parameter = this.dialect.processParameterObject(ms, parameter, boundSql, cacheKey);
                    String pageSql = this.dialect.getPageSql(ms, boundSql, parameter, rowBounds, cacheKey);

                    // 对sql进行改造
                    pageSql = PageSqlResolver.resolveLimit(pageSql);

                    BoundSql pageBoundSql = new BoundSql(configuration, pageSql, boundSql.getParameterMappings(), parameter);
                    Iterator var17 = additionalParameters.keySet()
                                                         .iterator();

                    while (true) {
                        if (!var17.hasNext()) {
                            resultList = executor.query(ms, parameter, RowBounds.DEFAULT, resultHandler, cacheKey, pageBoundSql);
                            break;
                        }

                        String key = (String) var17.next();
                        pageBoundSql.setAdditionalParameter(key, additionalParameters.get(key));
                    }
                }
            }

            Object var22 = this.dialect.afterPage(resultList, parameter, rowBounds);
            return var22;
        } finally {
            this.dialect.afterAll();
        }
    }

    private Long executeManualCount(Executor executor, MappedStatement countMs, Object parameter, BoundSql boundSql, ResultHandler resultHandler) throws IllegalAccessException, SQLException {
        CacheKey countKey = executor.createCacheKey(countMs, parameter, RowBounds.DEFAULT, boundSql);
        BoundSql countBoundSql = countMs.getBoundSql(parameter);
        Object countResultList = executor.query(countMs, parameter, RowBounds.DEFAULT, resultHandler, countKey, countBoundSql);
        Long count = ((Number) ((List) countResultList).get(0)).longValue();
        return count;
    }

    private Long executeAutoCount(Executor executor, MappedStatement countMs, Object parameter, BoundSql boundSql, RowBounds rowBounds, ResultHandler resultHandler) throws IllegalAccessException,
        SQLException {
        Map<String, Object> additionalParameters = (Map) this.additionalParametersField.get(boundSql);
        CacheKey countKey = executor.createCacheKey(countMs, parameter, RowBounds.DEFAULT, boundSql);
        String countSql = this.dialect.getCountSql(countMs, boundSql, parameter, rowBounds, countKey);
        BoundSql countBoundSql = new BoundSql(countMs.getConfiguration(), countSql, boundSql.getParameterMappings(), parameter);
        Iterator var11 = additionalParameters.keySet()
                                             .iterator();

        while (var11.hasNext()) {
            String key = (String) var11.next();
            countBoundSql.setAdditionalParameter(key, additionalParameters.get(key));
        }

        Object countResultList = executor.query(countMs, parameter, RowBounds.DEFAULT, resultHandler, countKey, countBoundSql);
        Long count = (Long) ((List) countResultList).get(0);

        // 获取count
        return count;
    }

    private MappedStatement getExistedMappedStatement(Configuration configuration, String msId) {
        MappedStatement mappedStatement = null;

        try {
            mappedStatement = configuration.getMappedStatement(msId, false);
        } catch (Throwable var5) {
            ;
        }

        return mappedStatement;
    }

    public Object plugin(Object target) {
        return Plugin.wrap(target, this);
    }

    public void setProperties(Properties properties) {
        this.msCountMap = CacheFactory.createCache(properties.getProperty("msCountCache"), "ms", properties);
        String dialectClass = properties.getProperty("dialect");
        if (StringUtil.isEmpty(dialectClass)) {
            dialectClass = this.default_dialect_class;
        }

        try {
            Class<?> aClass = Class.forName(dialectClass);
            this.dialect = (Dialect) aClass.newInstance();
        } catch (Exception var6) {
            throw new PageException(var6);
        }

        this.dialect.setProperties(properties);
        String countSuffix = properties.getProperty("countSuffix");
        if (StringUtil.isNotEmpty(countSuffix)) {
            this.countSuffix = countSuffix;
        }

        try {
            this.additionalParametersField = BoundSql.class.getDeclaredField("additionalParameters");
            this.additionalParametersField.setAccessible(true);
        } catch (NoSuchFieldException var5) {
            throw new PageException(var5);
        }
    }
}

package com.anur.pagehelper.page;

/**
 * Created by Anur IjuoKaruKas on 2018/6/8
 */
public class PageSqlResolver {

    public static final String SQL_SIGN = "AS limitable";

    public static final String LIMIT_SIGN = "LIMIT ?";

    public static final String LIMIT_SIGN_EX = "LIMIT ?, ?";

    public static String resolveLimit(String pageSql) {
        if (pageSql == null) {
            return null;
        }

        if (pageSql.contains(SQL_SIGN)) {// 如果需要特殊分页
            String changer = "";

            if (pageSql.contains(LIMIT_SIGN_EX)) {
                changer = LIMIT_SIGN_EX;
            } else if (pageSql.contains(LIMIT_SIGN)) {
                changer = LIMIT_SIGN;
            }

            pageSql = pageSql.replace(changer, "");
            StringBuilder sqlBuilder = new StringBuilder(pageSql);

            StringBuilder mae = new StringBuilder(sqlBuilder.substring(0, sqlBuilder.indexOf(SQL_SIGN)));// mae 截止sql语句到 limitable
            StringBuilder uShiRo = new StringBuilder(sqlBuilder.substring(sqlBuilder.indexOf(SQL_SIGN), sqlBuilder.length()));// 剩余的

            mae.insert(mae.lastIndexOf(")"), String.format(" %s", changer));

            return mae.append(uShiRo)
                      .toString();
        } else {
            return pageSql;
        }
    }
}

3、新增配置文件如下 注意,这里配置了我们的CustomPageInterceptor(2步骤里的那个类)作为插件,如果不进行此处配置,我们的修改是不起作用的。

/**
 * Created by Anur IjuoKaruKas on 2017/12/13.
 */
@Configuration
public class MybatisConfiguration {

    @Bean
    public SqlSessionFactory sqlSessionFactoryBean(@SuppressWarnings("SpringJavaInjectionPointsAutowiringInspection") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean factory = new SqlSessionFactoryBean();
        factory.setDataSource(dataSource);
        factory.setTypeAliasesPackage(Constant.MODEL_PACKAGE);

        // 配置分页插件,详情请查阅官方文档
        PageHelperProperties properties = new PageHelperProperties();
        properties.setPageSizeZero("true");// 分页尺寸为0时查询所有纪录不再执行分页
        properties.setReasonable("true");// 页码<=0 查询第一页,页码>=总页数查询最后一页
        properties.setSupportMethodsArguments("true");// 支持通过 Mapper 接口参数来传递分页参数

        CustomPageInterceptor pageInterceptor = new CustomPageInterceptor();
        pageInterceptor.setProperties(properties.getProperties());
        //  添加插件
        factory.setPlugins(new Interceptor[]{pageInterceptor});

        // 添加XML目录
        ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        factory.setMapperLocations(resolver.getResources("classpath:mapper/*.xml"));
        return factory.getObject();
    }
}

4、sql语句注意事项 在需要被查询的主表上,你需要手动包装一层select,并将其别名设置为limitable。AS必须大写。

SELECT *
FROM (SELECT *
      FROM provider_order) AS limitable
  LEFT JOIN order_info ON limitable.po_id = order_info.provider_order_id

这是原语句:

SELECT *
      FROM provider_order limitable
  LEFT JOIN order_info ON limitable.po_id = order_info.provider_order_id

5、查询时注意事项 在查询之前,我们需要将主表的主键(必须唯一)设置到countColumn中。

 Page page = PageHelper.startPage(0, 10);
        page.setCountColumn("DISTINCT po_id");
        List<ProviderOrder> providerOrderList = testService.getProviderOrderWithOrderInfoTest("%-%");

如果已经确保完成了上面的五个步骤,执行查询即可,我们将得到正确的连表查询结果。

Leave a Reply

如果您是第一次在本站留言,需要审核后才会显示内容!