Releases · pagehelper/Mybatis-PageHelper

17 Dec 05:24

v6.1.0

158f78e

6.1.0 - 2023-12-16 Latest

Latest

发布6.1.0，PageHelper 提供 jsqlparser直接依赖都是中间接口，可以通过SPI替换默认实现
升级jsqlparser版本4.7，重新实现order by，分页，count查询
简化pom.xml配置，去掉shade内嵌jsqlparser方式，改为通过外部依赖选择不同的jsqlparser版本，允许自己SPI扩展
jsqlparser解析不使用线程池，支持SPI扩展覆盖SqlParser实现
SqlServer分页改为SqlServerSqlParser接口，添加参数 sqlServerSqlParser 覆盖默认值
OrderByParser提取OrderBySqlParser接口，增加 orderBySqlParser 参数，可以覆盖默认实现
OrderByParser静态方法改为普通方法，为后续改接口做准备
jdk8+后不再需要JSqlParser接口，移除该接口，文档标记该参数（该参数早期用于支持sqlserver特殊配置）
兼容jsqlparser4.7版本 Rui 2023/12/3 15:15
maven-compiler-plugin固定版本以去除警告，并增加构建稳定性 qxo
gitignore .vscode for vscode ide qxo
修改bug #779 chenyuehui

为了兼容 jsqlparser 4.5 和 4.7，以及后续可能存在的其他版本，新建了一个 pagehelper-sqlparser 项目，目前提供了 4.5 和 4.7 两个实现，
使用时从 pagehelper 排除 jsqlparser，然后选择一个 jsqlparser 实现即可，当前版本默认使用的 4.7 版本的代码，
因此如果想换 4.5 的实现，可以按照下面方式进行配置：

<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper</artifactId>
    <version>6.1.0</version>
    <exclusions>
        <exclusion>
            <groupId>com.github.jsqlparser</groupId>
            <artifactId>jsqlparser</artifactId>
        </exclusion>
    </exclusions>
</dependency>
<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>sqlparser4.5</artifactId>
    <version>6.1.0</version>
</dependency>

SPI 替换默认值的优先级低于 sqlServerSqlParser,orderBySqlParser,countSqlParser 参数指定的实现，不指定时如果存在SPI实现，即可生效，
SPI 可以参考 pagehelper-sqlsource 模块代码。

JSqlParser 默认解析 SQL 会使用临时创建的 Executors.newSingleThreadExecutor()，这里通过 API 跳过了线程池：

CCJSqlParser parser = CCJSqlParserUtil.newParser(statementReader);
parser.withSquareBracketQuotation(true);
return parser.Statement();

JSqlParser 使用线程池的目的是为了防止解析超时，因此如果你遇到过超时的情况，可以引入下面的依赖（通过SPI覆盖了默认实现，超时时间10秒）：

<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>sqlparser-timeout</artifactId>
    <version>6.1.0</version>
</dependency>

Released version 6.1.0, PageHelper provides direct dependency on jsqlparser as intermediate interfaces, allowing default implementation replacement through SPI.
Upgraded jsqlparser version to 4.7, re-implemented order by, pagination, and count queries.
Simplified pom.xml configuration, removed shade-embedded jsqlparser approach, and switched to selecting different jsqlparser versions through external dependencies, allowing self-SPI extension.
jsqlparser parsing no longer uses a thread pool, supporting SPI extension to override SqlParser implementation.
Changed SqlServer pagination to SqlServerSqlParser interface, added parameter sqlServerSqlParser to override the default value.
Extracted OrderByParser to OrderBySqlParser interface, added orderBySqlParser parameter to override the default implementation.
Changed static methods of OrderByParser to regular methods, preparing for future interface changes.
JSqlParser interface is no longer needed after JDK 8+, removed the interface, and marked the parameter in the documentation (This parameter was used in the early stages to support special configuration for SQL Server).
Compatible with jsqlparser 4.7 version. Rui 2023/12/3 15:15.
Fixed maven-compiler-plugin version to remove warnings and improve build stability. qxo
Added .vscode to .gitignore for vscode IDE. qxo
Fixed bug #779. chenyuehui

To ensure compatibility with jsqlparser 4.5, 4.7, and possible future versions,
a new project called pagehelper-sqlparser has been created.
Currently, it provides two implementations: 4.5 and 4.7.
To use it, exclude jsqlparser from pagehelper and select one jsqlparser implementation.
The current version defaults to using the code from version 4.7.
If you want to switch to the 4.5 implementation, follow the configuration steps below:

<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper</artifactId>
    <version>6.1.0</version>
    <exclusions>
        <exclusion>
            <groupId>com.github.jsqlparser</groupId>
            <artifactId>jsqlparser</artifactId>
        </exclusion>
    </exclusions>
</dependency>
<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>sqlparser4.5</artifactId>
    <version>6.1.0</version>
</dependency>

The priority of replacing default values with SPI is lower than the implementations specified by the sqlServerSqlParser, orderBySqlParser, and countSqlParser parameters.
If no specific implementation is specified, the SPI implementation will take effect if available.
You can refer to the code in the pagehelper-sqlsource module for SPI implementation examples.

By default, JSqlParser uses a temporarily created Executors.newSingleThreadExecutor() for parsing SQL.
Here, the thread pool is bypassed through the API:

CCJSqlParser parser = CCJSqlParserUtil.newParser(statementReader);
parser.withSquareBracketQuotation(true);
return parser.Statement();

The purpose of using a thread pool in JSqlParser is to prevent parsing timeouts. Therefore, if you have encountered timeout situations,
you can introduce the following dependency (which overrides the default implementation through SPI with a timeout of 10 seconds):

<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>sqlparser-timeout</artifactId>
    <version>6.1.0</version>
</dependency>

Assets 2

05 Nov 03:52

pagehelper

v6.0.0

8961063

6.0.0 - 2023-11-05

基于jdk8适配，6.0开始不支持jdk6和7，如果有需要可以使用5.x版本
增加异步count支持，全局配置asyncCount，默认false，单次设置：PageHelper.startPage(1, 10).enableAsyncCount();
异步使用独立连接（事务）查询，有增删改操作影响查询时不适合开启异步查询。closed #334
JSqlParser默认开启 parser.withSquareBracketQuotation(true)，支持 SqlServer []
feat: 在PageInfo类中新增了用以进行数据对象转换的方法 <E> PageInfo<E> convert(Page.Function<T, E> function) by
codeke
CountSqlParser改为接口，允许通过countSqlParser参数替换为自己的实现，支持 #772
dialectAlias支持简化配置，例如dm=oracle;oracle=oracle9i，直接引用现在的缩写，不用写类全名
countColumn添加注入检测，fixed #686
增加PageParam类，不内嵌对象（会影响使用），如果想用可以继承该对象，closed #562
所有异常信息改为英文提示
放开 setLocalPage，支持 #771
解决sqlserver带union sql解析时处理order by错误的问题，fixed #768
优化total逻辑，解决指定不分页查询，同时指定order by时无效的问题，fixed #641
修改 dialect 实例化逻辑，保证类完成配置后使用，fixed #742
dialectAliasMap改为LinkedHashMap，可以按配置顺序进行匹配，fixed #758
行云数据库分页BUG修复 by maimaitiyaer_bonc

Based on JDK 8 adaptation, JDK 6 and 7 are not supported from 6.0 onwards, and 5.x versions can be used if necessary
Added asynchronous count support, configure asyncCount globally, default false，single activation
by PageHelper.startPage(1, 10).enableAsyncCount();
Asynchronous queries are performed using independent connections (transactions),
and it is not suitable to enable asynchronous queries when the query is affected by addition, deletion, and
modification operations. closed #334
JSqlParser opens parser.withSquareBracketQuotation(true) by default and supports SqlServer []
feat: A new method for data object conversion has been added to the PageInfo,
method: <E> PageInfo<E> convert(Page.Function<T, E> function) by codeke
CountSqlParser is changed to an interface, allowing the countSqlParser parameter to be replaced with your own
implementation, which is supported #772
dialectAlias supports simplified configurations, e.g. dm=oracle;oracle=oracle9i, a direct reference to the current
abbreviation, without writing the full name of the class
countColumnadd injection detection, fixed #686
Add the PageParam class, which does not embed objects (will affect the use), if you want to use, you can inherit the
object,closed #562
All exception messages have been changed to English
open setLocalPage method, support #771
Solve the problem of handling order by error when sqlserver with union sql parsing,fixed #768
Optimized the total logic to solve the problem that the query is not pagination and the order by is invalid. fixed
#641
Modify the dialect instantiation logic to ensure that the class is used after the configuration is completed. fixed
#742
dialectAliasMap change to LinkedHashMap type, support matching in configuration order, fixed #758
fixed the pagination bug of xingyun database by maimaitiyaer_bonc

Assets 2

03 Jun 03:26

abel533

v5.3.3

df1d149

5.3.3 - 2023-06-03

Ignoring unnecessarily generated surefire-report by java-codehunger
支持从URL中解析openGauss 数据库对应的方言 by saxisuer
修复sql注入检验不正确问题 #716 by uyong
支持从url中解析人大金仓kingbase8对应的方言 by univ
添加支持cirrodata的分页 #705 by sxh0570

5.3.3 - 2023-06-03

Ignoring unnecessarily generated surefire-report by java-codehunger
Supports parsing of the dialect corresponding to the openGauss database from the URL by saxisuer
Fixed the issue that SQL injection verification was incorrect #716 by uyong
Support parsing of the dialect corresponding to Kingbase 8 from the URL by univ
Add support for cirrodata #705 by sxh0570

Assets 2

18 Sep 16:07

abel533

v5.3.2

cfc4e0a

5.3.2 - 2022-09-18

使用文档更新，所有参数都包含在内，首页默认文档改为中文。
Add support for kingbase. by HanHuimin001
增加 debug 参数，默认 false，为true时开启debug模式，开始 debug 模式后将记录调用堆栈 by huyingqian
Add 支持count的sql支持hint语法 by zhanliquan
增加 PageProperties 接口，框架内部实例化的扩展类如果实现了这个接口，可以通过这个接口的方法获取分页插件配置。
增加 CountMsIdGen 接口，可以通过 countMsIdGen 配置自定义实现类，该类用于生成查询对应COUNT查询的msId。默认实现还是使用countSuffix
，通过扩展可以实现如 selectByExample 映射到对应的 selectCountByExample 方法。
增加 keepOrderBy 和 keepSubSelectOrderBy 配置。
增加 sqlParser 配置，增加 JSqlParser 接口，解决 jsqlparser 和 jdk 兼容性导致无法额外配置的问题。
测试使用 logback 日志框架，去掉log4j。
解决 dialectKey 为空导致NPE，fixed #656

5.3.2 - 2022-09-18

Use document update, all parameters are included, the default home page document changed to Chinese.
Add support for kingbase. by HanHuimin001
Add 'debug' parameter, default 'false', turn on 'debug' mode when 'true', call stack will be recorded after 'debug'
mode starts. by huyingqian
add supports for count sql hint syntax. by zhanliquan
Add a PageProperties interface, which can be used by the instantiated extension class inside the framework to obtain
the paging plug-in configuration.
To add the CountMsIdGen interface, you can configure a custom implementation class by CountMsIdGen, which is used
to generate the msId for the query corresponding to the COUNT query. The default implementation is still countSuffix
, with extensions like 'selectByExample' mapped to the corresponding 'selectCountByExample' method.
Added keepOrderBy and keepSubSelectOrderBy configurations.
Add the sqlParser configuration and add the JSqlParser interface to solve the problem that JSqlParser and JDK
compatibility cause no additional configuration.
The test uses the Logback logging framework and removes log4j.
Resolve that 'dialectKey' is empty resulting in NPE，fixed #656

Assets 2

14 Jun 15:08

abel533

v5.3.1

5085330

5.3.1 - 2022-06-14

处理 CVE-2022-28111 漏洞，限制 order by 参数，避免 SQL 注入
Add support for as400. by bluezealot
优化分页结果包装类的泛型参数 by 章福来
规范PostgreSQL分页参数的顺序 by outian

5.3.1

Resolve CVE-2022-28111 vulnerability, limit the order by parameter, avoid SQL injection
Add support for as400. by bluezealot
Optimize generic parameters of Page class **by Zhang Fulai * *
Standardize the order of PostgreSQL paging parameters by outian

Assets 2

08 Oct 03:12

abel533

v5.3.0

4b04846

5.3.0 - 2021-10-07

增加 AutoDialect 接口用于自动获取数据库类型，可以通过 autoDialectClass 配置为自己的实现类，默认使用 DataSourceNegotiationAutoDialect，优先根据连接池获取。
默认实现中，增加针对 hikari,druid,tomcat-jdbc,c3p0,dbcp 类型数据库连接池的特殊处理，直接从配置获取jdbcUrl，当使用其他类型数据源时，仍然使用旧的方式获取连接在读取jdbcUrl。
想要使用和旧版本完全相同方式时，可以配置 autoDialectClass=old。当数据库连接池类型非常明确时，建议配置为具体值，例如使用 hikari 时，配置 autoDialectClass=hikari
，使用其他连接池时，配置为自己的实现类。
支持运行时动态指定使用的 dialect 实现，例如 PageHelper.startPage(1, 10).using("oracle");
或者 PageHelper.startPage(2, 10).using("org.exmaple.CustomDialect");
PageInfo 增加空实例常量属性 PageInfo.EMPTY 以及内容判断 boolean hasContent()。

启动中增加 banner, 需要日志级别 debug，可以通过 -Dpagehelper.banner=false 或者环境变量 PAGEHELPER_BANNER=false 关闭

 DEBUG [main] -
 
 ,------.                           ,--.  ,--.         ,--.                         
 |  .--. '  ,--,--.  ,---.   ,---.  |  '--'  |  ,---.  |  |  ,---.   ,---.  ,--.--.
 |  '--' | ' ,-.  | | .-. | | .-. : |  .--.  | | .-. : |  | | .-. | | .-. : |  .--'
 |  | --'  \ '-'  | ' '-' ' \   --. |  |  |  | \   --. |  | | '-' ' \   --. |  |    
 `--'       `--`--' .`-  /   `----' `--'  `--'  `----' `--' |  |-'   `----' `--'    
 `---'                                   `--'                        is intercepting.

增加 banner 的目的在于，如果你配置了多次分页插件，你会看到 banner 输出多次，你可以在 PageInterceptor 构造方法断点看看那些地方进行了实例化。

完善 Count 查询，当存在 having 时，不在优化查询列。查询列存在有别名的函数或者运算时也不优化查询列，避免 order by 或 having 中使用的别名不存在。
增加判断处理某些数据（如 TDEngine）查询 count 无结果时返回 null
添加 Firebird 数据库支持和 SqlServer2012 分页语法相同。
添加 impala 数据库自动识别。
JSqlParser 升级为 4.2 版本。

距离上次更新3个月左右，这次更新直接让假期少了3天 🏃 ，关了 GitHub 和 Gitee 上的 200 多个issue，不一定所有问题都得到了处理，如果你还有疑问，可以继续提 issue，下个大版本会考虑直接 6.0，计划全部升级到 java 8，功能保持不变。

Add AutoDialect interface to automatically obtain the database type, which can be configured as its own
implementation class through autoDialectClass. By default, DataSourceNegotiationAutoDialect is used, which is
obtained according to the connection pool first. In the default implementation, special processing is added
for hikari,druid,tomcat-jdbc,c3p0,dbcp type database connection pools, and jdbcUrl are obtained directly from the
configuration. When other types of data sources are used, the connection is still obtained in the old way. You can
configure autoDialectClass=old when you want to use exactly the same way as the old version. When the database
connection pool type is very clear, it is recommended to configure it as a specific value. For example, when using
hikari, configure autoDialectClass=hikari, and when using other connection pools, configure it as its own
implementation class.
Enable dynamic designation of dialect implementation at runtime, such
as PageHelper.startPage(1, 10).using("oracle"); Or PageHelper.startPage(2, 10).using("org.exmaple.CustomDialect");
PageInfo adds the empty instance constant attribute PageInfo.EMPTY and the content judgment boolean hasContent()
.

Adding banner to startup requires log level debug, which can be closed by -Dpagehelper.banner=false or environment
variable PAGEHELPER_BANNER=false.

 DEBUG [main] -
 
 ,------.                           ,--.  ,--.         ,--.                         
 |  .--. '  ,--,--.  ,---.   ,---.  |  '--'  |  ,---.  |  |  ,---.   ,---.  ,--.--.
 |  '--' | ' ,-.  | | .-. | | .-. : |  .--.  | | .-. : |  | | .-. | | .-. : |  .--'
 |  | --'  \ '-'  | ' '-' ' \   --. |  |  |  | \   --. |  | | '-' ' \   --. |  |    
 `--'       `--`--' .`-  /   `----' `--'  `--'  `----' `--' |  |-'   `----' `--'    
 `---'                                   `--'                        is intercepting.

The purpose of adding banner is that if you configure paging plug-ins multiple times, you will see banner output
multiple times, and you can see where it has been instantiated at the breakpoint of the PageInterceptor constructor.

Improve the Count query. When having exists, the query column is not optimized. The query column is not optimized when
there are functions or operations with aliases in the column, so as to avoid that aliases used in order by or having
do not exist.
It is judged that processing some data (such as TDEngine) returns null when there is no result in querying count.
Adding Firebird database support is the same as SqlServer2012 paging syntax.
Add impala database automatic recognition.
Upgrade JSqlParser to version 4.2.

Assets 2

20 Jun 14:42

abel533

v5.2.1

9ea1fb0

5.2.1 - 2021-06-20

Upgrade dependency jsqlparser 4.0, mybatis 3.5.7
Automatically recognize the following databases：
- 虚谷数据库 xugu #599
- 神通数据库 oscar by ranqing
- 瀚高数据库 highgo by ashaiqing
BoundSqlInterceptorChain interceptor index parameter bug, fixed #587
fixed #558
Add PostgreSQL dialect by liym@home
fixed #604, Solve the problem of total loss
Add code comments, fixed #547

升级依赖 jsqlparser 4.0, mybatis 3.5.7
自动识别以下数据库：
- 虚谷数据库 xugu #599
- 神通数据库 oscar by ranqing
- 瀚高数据库 highgo by ashaiqing
BoundSqlInterceptorChain拦截器index参数bug, fixed #587
fixed #558
添加 PostgreSQL 方言 by liym@home
fixed #604, 解决total丢失的问题
规范注释, fixed #547

Assets 2

27 Jul 13:45

abel533

v5.2.0

ed5549c

5.2.0 - 2020-07-26

jsqlparser升级到3.2版本，sql解析更好，对sqlserver支持更好。
修改 sqlserver 方式中的替换正则，现在允许 with( nolock ) 括号中存在空格。
解决 reasonable 和 pageSizeZero,以及 offset 用法中的bug，现在的含义和结果更一致。
分页 SQL 拼接过程中增加换行符，避免原始 SQL 中存在注释导致分页部分无效。
Oracle 和 Db2 中的行号 ROW_ID 别名改为 PAGEHELPER_ROW_ID，避免和常用名称冲突。
解决单个参数ProviderSql使用其他拦截器时的特殊问题（支持 mybatis 3.4.0+）by 罗震宇
支持自动识别 clickhouse，使用 MySQL 方式进行分页。
将 startRow, endRow 类型从 int 改为 long。
Page 增加 public <T> PageInfo<T> toPageInfo(Function<E, T> function) 方法，用于转换查询结果中的数据。

参考 pr#476 提供 ·Oracle9iDialect`，这也是曾经用过的一种分页方式，可以自己测试选择合适的分页方式。

目前提供的两种 Oracle 分页如下：

-- OracleDialect 外层控制范围
WHERE ROW_ID <= ? AND ROW_ID > ?
-- Oracle9iDialect 内外分别控制范围
TMP_PAGE WHERE ROWNUM <= ? ) WHERE ROW_ID > ?

增加分页插件的 BoundSqlInterceptor 拦截器，可以在3个阶段对 SQL 进行处理或者简单读取，
增加参数 boundSqlInterceptors，可以配置多个实现 BoundSqlInterceptor 接口的实现类名，
使用英文逗号隔开。PageHelper调用时，也可以通过类似
PageHelper.startPage(x,x).boundSqlInterceptor(BoundSqlInterceptor boundSqlInterceptor)针对本次分页进行设置。

本次更新最大的变化是增加了 BoundSqlInterceptor，通过该接口可以在运行时拦截分页处理的 SQL（BoundSQL对象）：

/**
 * BoundSql 处理器
 */
public interface BoundSqlInterceptor {
    /**
     * boundsql 处理
     *
     * @param type     类型
     * @param boundSql 当前类型的 boundSql
     * @param cacheKey 缓存 key
     * @param chain    处理器链，通过 chain.doBoundSql 方法继续执行后续方法，也可以直接返回 boundSql 终止后续方法的执行
     * @return 允许修改 boundSql 并返回修改后的
     */
    BoundSql boundSql(Type type, BoundSql boundSql, CacheKey cacheKey, Chain chain);

    enum Type {
        /**
         * 原始SQL，分页插件执行前，先执行这个类型
         */
        ORIGINAL,
        /**
         * count SQL，第二个执行这里
         */
        COUNT_SQL,
        /**
         * 分页 SQL，最后执行这里
         */
        PAGE_SQL
    }

    /**
     * 处理器链，可以控制是否继续执行
     */
    interface Chain {
        Chain DO_NOTHING = new Chain() {
            @Override
            public BoundSql doBoundSql(Type type, BoundSql boundSql, CacheKey cacheKey) {
                return boundSql;
            }
        };

        BoundSql doBoundSql(Type type, BoundSql boundSql, CacheKey cacheKey);
    }
}

接口中包含了 boundSql 接口方法，还有 Type 枚举，和 Chain 接口的定义，自己实现的时候不需要考虑 Chain。

通过 boundSqlInterceptors 参数配置拦截器，执行时存在下面三种情况：

不管当前执行的 SQL 是否会分页，都会执行 Type.ORIGINAL 类型的拦截器方法，配置后一定会执行。
调用分页方法时，拦截器会继续执行 Type.COUNT_SQL 类型的拦截器方法，这个方法只有执行分页并且指定要进行 count 查询时才会执行。
调用分页方法时，如果 count > 0，就会执行 Type.PAGE_SQL 类型的拦截器方法，这个方法只有执行分页时才会执行。

通过 PageHelper.startPage(1, Integer.MAX_VALUE, false).boundSqlInterceptor(BoundSqlInterceptor boundSqlInterceptor)
这种指定的参数时，也能起到不进行分页和count查询，但是可以执行 Type.ORIGINAL 类型的拦截器方法。

当前拦截器在整个分页执行过程中，会执行3次，对应 Type 枚举的 3 个类型，执行顺序也一致。

如果想获取分页 SQL 执行前的，只需要关注 Type.ORIGINAL，另外两种就是 count 执行前和分页执行前（count=0时分页方法不执行，这里也不会执行）。

以测试代码为例：

public class TestBoundSqlInterceptor implements BoundSqlInterceptor {
    public static final String COMMENT = "\n /* TestBoundSqlInterceptor */\n";

    @Override
    public BoundSql boundSql(Type type, BoundSql boundSql, CacheKey cacheKey, Chain chain) {
        if (type == Type.ORIGINAL) {
            String sql = boundSql.getSql();
            MetaObject metaObject = MetaObjectUtil.forObject(boundSql);
            metaObject.setValue("sql", sql + COMMENT);
        }
        return chain.doBoundSql(type, boundSql, cacheKey);
    }

}

上面这段代码在 sql 执行前先修改原始 SQL，只是在最后增加了一段注释，不影响 SQL 执行，通过下面的方式配置：

<plugin interceptor="com.github.pagehelper.PageInterceptor">
    <!-- 支持通过Mapper接口参数来传递分页参数 -->
    <property name="helperDialect" value="mysql"/>
    <property name="boundSqlInterceptors"
              value="com.github.pagehelper.test.basic.provider.TestBoundSqlInterceptor,com.github.pagehelper.test.basic.provider.TestBoundSqlInterceptor"/>
</plugin>

这里为了说明该参数值可以是多个，因此重复配置了一次，也就是上面的拦截器会执行两次。

这样配置后，上面的 SQL 在分页执行的时候就会修改 SQL。

除了这种配置方式外，还支持 PageHelper.startPage 时临时指定，这种方式会把拦截器放到链头先执行，因此可以控制后续的是否执行，也可以在后续所有执行外，做最后处理再返回。

示例：

PageHelper.startPage(1, 10).boundSqlInterceptor(new BoundSqlInterceptor() {
    @Override
    public BoundSql boundSql(Type type, BoundSql boundSql, CacheKey cacheKey, Chain chain) {
        System.out.println("before: " + boundSql.getSql());
        BoundSql doBoundSql = chain.doBoundSql(type, boundSql, cacheKey);
        System.out.println("after: " + doBoundSql.getSql());
        if (type == Type.ORIGINAL) {
            Assert.assertTrue(doBoundSql.getSql().contains(TestBoundSqlInterceptor.COMMENT));
        }
        return doBoundSql;
    }
});

Upgrading jsqlparser to version 3.2 makes sql parsing better and supports sqlserver better.
Modify the substitution regularity in sqlserver mode, and now allow spaces in with( nolock) brackets.
Solving the bugs in reasonable, pageSizeZero and offset usage, the meaning and result are more consistent now.
In the process of splicing paging SQL, a new line character is added to avoid invalid paging part caused by comments in the original SQL.
ROW_ID alias in Oracle and Db2 is changed to PAGEHELPER_ROW_ID to avoid conflict with common names.
Solve the special problem when using other interceptors with a single parameter ProviderSql (support mybatis 3.4.0+) by Luo Zhenyu
Automatic identification of clickhouse is supported, and paging is performed by MySQL.
Change startRow, endRow type from int to long.
Page adds a public <T> PageInfo<T> toPageInfo(Function<E, T> function) method to convert the data in the query results.
Refer to Oracle9iDialect provided by pr#476, which is also a paging method used before. You can test and select the appropriate paging method by yourself.

At present, there are two kinds of Oracle pagination as follows:
```
-- OracleDialect outer control range
WHERE ROW_ID <= ? AND ROW_ID > ?
-- Oracle9iDialect's internal and external control scope respectively
TMP_PAGE WHERE ROWNUM <= ? ) WHERE ROW_ID > ?
```
Adding BoundSqlInterceptor of PageHelper plug-in can process or simply read SQL in three stages, adding boundSqlInterceptors, and configuring multiple implementation class names that implement BoundSqlInterceptor interface, separated by English commas. PageHelper can also be set for this paging through a PageHelper.startPage(x,x).boundSqlInterceptor(BoundSqlInterceptor boundSqlInterceptor).

The biggest change of this update is the addition of BoundSqlInterceptor, which can intercept the SQL(BoundSQL object) of paging processing at runtime:

/**
 * BoundSql 处理器
 */
public interface BoundSqlInterceptor {
    /**
     * boundsql 处理
     *
     * @param type     类型
     * @param boundSql 当前类型的 boundSql
     * @param cacheKey 缓存 key
     * @param chain    处理器链，通过 chain.doBoundSql 方法继续执行后续方法，也可以直接返回 boundSql 终止后续方法的执行
     * @return 允许修改 boundSql 并返回修改后的
     */
    BoundSql boundSql(Type type, BoundSql boundSql, CacheKey cacheKey, Chain chain);

    enum Type {
        /**
         * 原始SQL，分页插件执行前，先执行这个类型
         */
        ORIGINAL,
        /**
         * count SQL，第二个执行这里
         */
        COUNT_SQL,
        /**
         * 分页 SQL，最后执行这里
         */
        PAGE_SQL
    }

    /**
     * 处理器链，可以控制是否继续执行
     */
    interface Chain {
        Chain DO_NOTHING = new Chain() {
            @Override
            public BoundSql doBoundSql(Type type, BoundSql boundSql, CacheKey cacheKey) {
                return boundSql;
            }
        };

        BoundSql doBoundSql(Type type, BoundSql boundSql, CacheKey cacheKey);
    }
}

The interface includes boundSql interface method, Type enumeration, and the definition of Chain interface, and you don't need to consider Chain when you implement it yourself.

The interceptor is configured by boundSqlInterceptors parameter, and there are three situations when executing:

Regardless of whether the currently executed SQL will be paged or not, interceptor methods of Type.ORIGINAL will be executed.
When the paging method is called, the interceptor will continue to execute the interceptor method of Type.COUNT_SQL, which will only be executed when paging is executed and count query is specified.
When paging method is called, if count > 0, interceptor method of Type.PAGE_SQL will be executed, which will only be executed when paging is executed.

With the specified parameter PageHelper.startPage(1, Integer.MAX_VALUE, false).boundSqlInterceptor(BoundSqlInterceptor boundSqlInterceptor), it can also play the role of not paging and count query, but can execute interceptor method of Type.ORIGINAL.

If you want to get the page before SQL execution, you only need to pay attention to Type.ORIGINAL, and the other two are before count execution and before page execution (when count=0, the page method will not be executed and will not be executed here).

Take the test code as an example:

public class TestBoundSqlInterceptor implements BoundSqlInterceptor {
    public static final String COMMENT = "\n /* TestBoundSqlInterceptor */\n";

    @Override
    public BoundSql boundSql(Type type, BoundSql boundSql, CacheKey cacheKey, Chain chain) {
        if (type == Type.ORIGINAL) {
            String sql = boundSql.getSql();
            MetaObject metaObject = MetaObjectUtil.forObject(boundSql);
            metaObject.setValue("sql", sql + COMMENT);
        }
        return chain.doBoundSql(type, boundSql, cacheKey);
    }

}

The above code modifies the original sql before SQL execution, but only adds a comment at the end, which does not affect SQL execution. It is configured in the following way:

<plugin interceptor="com.github.pagehelper.PageInterceptor">
    <!-- 支持通过Mapper接口参数来传递分页参数 -->
    <proper...

Assets 2

27 Nov 02:01

abel533

v5.1.11

3aab44f

5.1.11 - 2019-11-26

Added support for Shentong database wangss
Add support for HerdDB - support HerdDB, mostly like MySQL - auto detect HerdDB Enrico Olivelli
fix some typos and grammar issues LHearen

增加神通数据库的支持 wangss
Add support for HerdDB - support HerdDB, mostly like MySQL - auto detect HerdDB Enrico Olivelli
fix some typos and grammar issues LHearen

Assets 2

27 Nov 02:03

abel533

v5.1.10

9dade15

5.1.10 - 2019-06-05

In version 5.1.0 - 2017-08-28. Added ReplaceSql interface for handling sqlServer with (nolock) problem,
add the replaceSql parameters, the optional value is simple and regex, or to achieve the ReplaceSql interface
fully qualified class name. The default value is simple, still using the original way to deal with,
the new regex will be convert with (nolock) to table_PAGEWITHNOLOCK.

This update only changes the default value from simple to regex, which can almost 100% solve the paging problem of sqlServer.

The following are examples from two issue.

issue #76

Original SQL：

SELECT *
FROM
forum_post_info a with(nolock)
LEFT JOIN forum_carcase_tags as b with(nolock) on a.id = b.carcase_id where b.tag_id = 127

Converted Count SQL：

SELECT COUNT(0)
FROM forum_post_info a WITH (NOLOCK)
	LEFT JOIN forum_carcase_tags b WITH (NOLOCK) ON a.id = b.carcase_id
WHERE b.tag_id = 127

Converted paging SQL：

SELECT TOP 10 *
FROM (
	SELECT ROW_NUMBER() OVER (ORDER BY RAND()) AS PAGE_ROW_NUMBER, *
	FROM (
		SELECT *
		FROM forum_post_info a WITH (NOLOCK)
			LEFT JOIN forum_carcase_tags b WITH (NOLOCK) ON a.id = b.carcase_id
		WHERE b.tag_id = 127
	) PAGE_TABLE_ALIAS
) PAGE_TABLE_ALIAS
WHERE PAGE_ROW_NUMBER > 1
ORDER BY PAGE_ROW_NUMBER

issue #398

Original SQL：

Select AUS.ScheduleID, AUS.SystemID, AUS.ClinicID, AUS.DoctorID, AUS.ScheduleDate,
	AUS.StartTime, AUS.EndTime, AUS.Status, AUS.BookBy, AUS.Note, AUS.Remark, AUS.SourceType, CM.CompanyName,
	AU.UserName As DoctorName, AU.UserNumber As DoctorNumber, CC.CodeDesc As ClinicName, CD.Lat, CD.Lng,
	CD.ContactTel, CD.Address, CR.ConsultationStatusID, CR.RegisterStatus,A1.CodeDesc as AreaLevel1, A2.CodeDesc as AreaLevel2
	From ACM_User_Schedule AUS with(nolock)
	Left Join Client_Register CR with(nolock) On AUS.BookBy=CR.ClientID And CR.SourceType='F' And AUS.ClientRegisterNum=CR.ClientRegisterNum
	Inner Join ACM_User AU with(nolock) On AU.UserID = AUS.DoctorID
	Inner Join Code_Clinic CC with(nolock) On AUS.ClinicID=CC.CodeID
	Inner Join Clinic_Detail CD with(nolock) On CC.CodeID = CD.ClinicID
	Inner Join Code_Area A1 with(nolock) On CD.AreaLevel1ID=A1.CodeID
	Inner Join Code_Area A2 with(nolock) On CD.AreaLevel2ID=A2.CodeID
	Inner Join Company_Master CM with(nolock) On CC.SystemID = CM.SystemID
	Where BookBy=1

Converted Count SQL：

SELECT COUNT(0)
FROM ACM_User_Schedule AUS WITH (NOLOCK)
	LEFT JOIN Client_Register CR WITH (NOLOCK)
	ON AUS.BookBy = CR.ClientID
		AND CR.SourceType = 'F'
		AND AUS.ClientRegisterNum = CR.ClientRegisterNum
	INNER JOIN ACM_User AU WITH (NOLOCK) ON AU.UserID = AUS.DoctorID
	INNER JOIN Code_Clinic CC WITH (NOLOCK) ON AUS.ClinicID = CC.CodeID
	INNER JOIN Clinic_Detail CD WITH (NOLOCK) ON CC.CodeID = CD.ClinicID
	INNER JOIN Code_Area A1 WITH (NOLOCK) ON CD.AreaLevel1ID = A1.CodeID
	INNER JOIN Code_Area A2 WITH (NOLOCK) ON CD.AreaLevel2ID = A2.CodeID
	INNER JOIN Company_Master CM WITH (NOLOCK) ON CC.SystemID = CM.SystemID
WHERE BookBy = 1

Converted paging SQL：

SELECT TOP 10 ScheduleID, SystemID, ClinicID, DoctorID, ScheduleDate
	, StartTime, EndTime, Status, BookBy, Note
	, Remark, SourceType, CompanyName, DoctorName, DoctorNumber
	, ClinicName, Lat, Lng, ContactTel, Address
	, ConsultationStatusID, RegisterStatus, AreaLevel1, AreaLevel2
FROM (
	SELECT ROW_NUMBER() OVER (ORDER BY RAND()) AS PAGE_ROW_NUMBER, ScheduleID, SystemID, ClinicID, DoctorID
		, ScheduleDate, StartTime, EndTime, Status, BookBy
		, Note, Remark, SourceType, CompanyName, DoctorName
		, DoctorNumber, ClinicName, Lat, Lng, ContactTel
		, Address, ConsultationStatusID, RegisterStatus, AreaLevel1, AreaLevel2
	FROM (
		SELECT AUS.ScheduleID, AUS.SystemID, AUS.ClinicID, AUS.DoctorID, AUS.ScheduleDate
			, AUS.StartTime, AUS.EndTime, AUS.Status, AUS.BookBy, AUS.Note
			, AUS.Remark, AUS.SourceType, CM.CompanyName, AU.UserName AS DoctorName, AU.UserNumber AS DoctorNumber
			, CC.CodeDesc AS ClinicName, CD.Lat, CD.Lng, CD.ContactTel, CD.Address
			, CR.ConsultationStatusID, CR.RegisterStatus, A1.CodeDesc AS AreaLevel1, A2.CodeDesc AS AreaLevel2
		FROM ACM_User_Schedule AUS WITH (NOLOCK)
			LEFT JOIN Client_Register CR WITH (NOLOCK)
			ON AUS.BookBy = CR.ClientID
				AND CR.SourceType = 'F'
				AND AUS.ClientRegisterNum = CR.ClientRegisterNum
			INNER JOIN ACM_User AU WITH (NOLOCK) ON AU.UserID = AUS.DoctorID
			INNER JOIN Code_Clinic CC WITH (NOLOCK) ON AUS.ClinicID = CC.CodeID
			INNER JOIN Clinic_Detail CD WITH (NOLOCK) ON CC.CodeID = CD.ClinicID
			INNER JOIN Code_Area A1 WITH (NOLOCK) ON CD.AreaLevel1ID = A1.CodeID
			INNER JOIN Code_Area A2 WITH (NOLOCK) ON CD.AreaLevel2ID = A2.CodeID
			INNER JOIN Company_Master CM WITH (NOLOCK) ON CC.SystemID = CM.SystemID
		WHERE BookBy = 1
	) PAGE_TABLE_ALIAS
) PAGE_TABLE_ALIAS
WHERE PAGE_ROW_NUMBER > 1
ORDER BY PAGE_ROW_NUMBER

SQL is formatted by https://tool.oschina.net/codeformat/sql

在 5.1.0 - 2017-08-28 版本中，增加 ReplaceSql 接口用于处理 sqlServer 的 with(nolock) 问题，增加了针对性的 replaceSql 参数，
可选值为 simple 和 regex，或者是实现了ReplaceSql接口的全限定类名。默认值为 simple，仍然使用原来的方式处理，
新的 regex 会将如 table with(nolock) 处理为 table_PAGEWITHNOLOCK。

本次更新仅仅是把默认值从 simple 改为了 regex，使用 regex 方式几乎能 100% 解决 sqlServer 的分页问题。

下面是两个 issue 中的示例。

示例 SQL #76

原始 SQL：

SELECT *
FROM
forum_post_info a with(nolock)
LEFT JOIN forum_carcase_tags as b with(nolock) on a.id = b.carcase_id where b.tag_id = 127

转换的 Count SQL：

SELECT COUNT(0)
FROM forum_post_info a WITH (NOLOCK)
	LEFT JOIN forum_carcase_tags b WITH (NOLOCK) ON a.id = b.carcase_id
WHERE b.tag_id = 127

转换的分页 SQL：

SELECT TOP 10 *
FROM (
	SELECT ROW_NUMBER() OVER (ORDER BY RAND()) AS PAGE_ROW_NUMBER, *
	FROM (
		SELECT *
		FROM forum_post_info a WITH (NOLOCK)
			LEFT JOIN forum_carcase_tags b WITH (NOLOCK) ON a.id = b.carcase_id
		WHERE b.tag_id = 127
	) PAGE_TABLE_ALIAS
) PAGE_TABLE_ALIAS
WHERE PAGE_ROW_NUMBER > 1
ORDER BY PAGE_ROW_NUMBER

示例 SQL #398

原始 SQL：

Select AUS.ScheduleID, AUS.SystemID, AUS.ClinicID, AUS.DoctorID, AUS.ScheduleDate,
	AUS.StartTime, AUS.EndTime, AUS.Status, AUS.BookBy, AUS.Note, AUS.Remark, AUS.SourceType, CM.CompanyName,
	AU.UserName As DoctorName, AU.UserNumber As DoctorNumber, CC.CodeDesc As ClinicName, CD.Lat, CD.Lng,
	CD.ContactTel, CD.Address, CR.ConsultationStatusID, CR.RegisterStatus,A1.CodeDesc as AreaLevel1, A2.CodeDesc as AreaLevel2
	From ACM_User_Schedule AUS with(nolock)
	Left Join Client_Register CR with(nolock) On AUS.BookBy=CR.ClientID And CR.SourceType='F' And AUS.ClientRegisterNum=CR.ClientRegisterNum
	Inner Join ACM_User AU with(nolock) On AU.UserID = AUS.DoctorID
	Inner Join Code_Clinic CC with(nolock) On AUS.ClinicID=CC.CodeID
	Inner Join Clinic_Detail CD with(nolock) On CC.CodeID = CD.ClinicID
	Inner Join Code_Area A1 with(nolock) On CD.AreaLevel1ID=A1.CodeID
	Inner Join Code_Area A2 with(nolock) On CD.AreaLevel2ID=A2.CodeID
	Inner Join Company_Master CM with(nolock) On CC.SystemID = CM.SystemID
	Where BookBy=1

转换的 Count SQL：

SELECT COUNT(0)
FROM ACM_User_Schedule AUS WITH (NOLOCK)
	LEFT JOIN Client_Register CR WITH (NOLOCK)
	ON AUS.BookBy = CR.ClientID
		AND CR.SourceType = 'F'
		AND AUS.ClientRegisterNum = CR.ClientRegisterNum
	INNER JOIN ACM_User AU WITH (NOLOCK) ON AU.UserID = AUS.DoctorID
	INNER JOIN Code_Clinic CC WITH (NOLOCK) ON AUS.ClinicID = CC.CodeID
	INNER JOIN Clinic_Detail CD WITH (NOLOCK) ON CC.CodeID = CD.ClinicID
	INNER JOIN Code_Area A1 WITH (NOLOCK) ON CD.AreaLevel1ID = A1.CodeID
	INNER JOIN Code_Area A2 WITH (NOLOCK) ON CD.AreaLevel2ID = A2.CodeID
	INNER JOIN Company_Master CM WITH (NOLOCK) ON CC.SystemID = CM.SystemID
WHERE BookBy = 1

转换的分页 SQL：

SELECT TOP 10 ScheduleID, SystemID, ClinicID, DoctorID, ScheduleDate
	, StartTime, EndTime, Status, BookBy, Note
	, Remark, SourceType, CompanyName, DoctorName, DoctorNumber
	, ClinicName, Lat, Lng, ContactTel, Address
	, ConsultationStatusID, RegisterStatus, AreaLevel1, AreaLevel2
FROM (
	SELECT ROW_NUMBER() OVER (ORDER BY RAND()) AS PAGE_ROW_NUMBER, ScheduleID, SystemID, ClinicID, DoctorID
		, ScheduleDate, StartTime, EndTime, Status, BookBy
		, Note, Remark, SourceType, CompanyName, DoctorName
		, DoctorNumber, ClinicName, Lat, Lng, ContactTel
		, Address, ConsultationStatusID, RegisterStatus, AreaLevel1, AreaLevel2
	FROM (
		SELECT AUS.ScheduleID, AUS.SystemID, AUS.ClinicID, AUS.DoctorID, AUS.ScheduleDate
			, AUS.StartTime, AUS.EndTime, AUS.Status, AUS.BookBy, AUS.Note
			, AUS.Remark, AUS.SourceType, CM.CompanyName, AU.UserName AS DoctorName, AU.UserNumber AS DoctorNumber
			, CC.CodeDesc AS ClinicName, CD.Lat, CD.Lng, CD.ContactTel, CD.Address
			, CR.ConsultationStatusID, CR.RegisterStatus, A1.CodeDesc AS AreaLevel1, A2.CodeDesc AS AreaLevel2
		FROM ACM_User_Schedule AUS WITH (NOLOCK)
			LEFT JOIN Client_Register CR WITH (NOLOCK)
			ON AUS.BookBy = CR.ClientID
				AND CR.SourceType = 'F'
				AND AUS.ClientRegisterNum = CR.ClientRegisterNum
			INNER JOIN ACM_User AU WITH (NOLOCK) ON AU.UserID = AUS.DoctorID
			INNER JOIN Code_Clinic CC WITH (NOLOCK) ON AUS.ClinicID = CC.CodeID
			INNER JOIN Clinic_Detail CD WITH (NOLOCK) ON CC.CodeID = CD.ClinicID
			INNER JOIN Code_Area A1 WITH (NOLOCK) ON CD.AreaLevel1ID = A1.CodeID
			INNER JOIN Code_Area A2 WITH (NOLOCK) ON CD.AreaLevel2ID = A2.CodeID
			INNER JOIN Company_Master CM WITH (NOLOCK) ON CC.SystemID = CM.SystemID
		WHERE BookBy = 1
	) PAGE_TABLE_ALIAS
) PAGE_TABLE_ALIAS
WHERE PAGE_ROW_NUMBER >...

Assets 2

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

5.3.3 - 2023-06-03

5.3.3 - 2023-06-03

5.3.2 - 2022-09-18

5.3.2 - 2022-09-18

5.3.1 - 2022-06-14

5.3.1

5.2.1 - 2021-06-20

issue #76

issue #398

示例 SQL #76

示例 SQL #398

Releases: pagehelper/Mybatis-PageHelper

6.1.0 - 2023-12-16

6.0.0 - 2023-11-05

5.3.3 - 2023-06-03

5.3.3 - 2023-06-03

5.3.3 - 2023-06-03

5.3.2 - 2022-09-18

5.3.2 - 2022-09-18

5.3.2 - 2022-09-18

5.3.1 - 2022-06-14

5.3.1 - 2022-06-14

5.3.1

5.3.0 - 2021-10-07

5.2.1 - 2021-06-20

5.2.1 - 2021-06-20

5.2.0 - 2020-07-26

5.1.11 - 2019-11-26

5.1.10 - 2019-06-05

issue #76

issue #398

示例 SQL #76

示例 SQL #398