postgre uses pagehelper to query paging and reports an error: ERROR: LIMIT #,# syntax is not supported Suggestion: Use separate LIMIT and OFFSET clauses
? Hello friends~ I recently worked on a project, which is a regular SpringBoot + Mybatis architecture. The database uses PostgreSQL, and there are some scenarios where paging queries need to be used. It is natural to use the paging tool PageHelper. When using it Most of the code is the same as mysql, but there is a pitfall in paging that needs attention.
? Normally, to paging the query list, you need to add a startPage(); before the method. But for postgre, after doing this, there is no problem on the first page and after turning to the second page, an error is reported: ERROR: LIMIT #,# syntax is not supported. Suggestion: Use separate LIMIT and OFFSET clauses.
Let’s talk about the solution first
1. This is a bug in the earlier version. It can be fixed by changing the bug in the source code or upgrading the mybatis version.
2. Change the configuration file. Let mybatis default to postgre when paging, or auto recognize it automatically.
3. Write your own paging without using pagehelper’s paging.
Here we focus on the first and second types
Method 1: Correct the bug in the source code or upgrade the mybatis version to fix it
This method is excerpted from https://twelvet.cn/news/100.html The author used to increase the data permissions and then the paging failed. This article talks about the paging failure after mysql was replaced with postgre. The causes of the two problems are different, but the analysis ideas and solutions The problem method is the same:
debug
After adding data permissions, the paging query statement changed as follows:
Why is there only limit left? ! Offset was eaten? !
Calm down, there’s something wrong with this paging statement.
It needs to be explained here that the way of writing paging statements in postgreSQL is slightly different from that of MySQL. In MySQL, the way of writing paging statements is:
-- MySQL paging syntax
SELECT * from tableName where 1=1 limit 10 offset 10;
SELECT * from tableName where 1=1 limit 0, 10;
SELECT * from tableName where 1=1 limit 10;
But the paging syntax of postgreSQL is like this:
SELECT * FROM t_privilege_role limit 10 offset 10;
SELECT * FROM t_privilege_role offset 10 limit 10;
At first glance they seem to be the same, but upon closer inspection there are differences.
The paging statement of postgreSQL must have limit and offset, and the positions can be interchanged. The official standard is limit ? offset ? writing method;
The MySQL paging statement can omit offset. If not omitted, the limit must be in front and the offset in the back;
So the paging condition of the SQL statement in the debug above is OFFSET ? LIMIT ?.
Because I am using the PageHelper plug-in, this condition is automatically added by the plug-in for me.
So back to the phenomenon in debug, why is there no offset condition? Is there something wrong when Druid’s SQLUtils parses SQL? Being skeptical, I debugged again and found that after SQLUtils parsed the SQL, the paging conditions had changed.
In other words, SQLUtils does not parse the offset ? in offset ? limit ? because SQLUtils parses it according to the official standard paging syntax.
Can the paging of PageHelper be changed?
So how does PageHelper add paging conditions to the query statement? Can the paging condition be changed to limit? offset
The principle of PageHelper adding paging conditions is actually using the interceptor function of MyBatis. In the interceptor, paging conditions are added according to the paging parameters set by the user.
Because the paging statement syntax of different databases is different, you need to specify which database dialect PageHelper uses. We usually specify it in the configuration file.
// pagehelper dialect, mysql, oracle, postgresql, etc.
pagehelper.helperDialect=postgresql
This dialect parameter finally has a specific implementation class in the code.
As shown above, these databases are supported by default in PageHelper. Let’s take a look at how postgreSQL is implemented.
It can be seen that PostgreSqlDialect inherits MySqlDialect, and if the paging parameter is not 0, offset ? limit ? is used. Let’s take a look at how MySqlDialect is implemented.
It can be seen that MySqlDialect is implemented according to MySQL syntax, and when the start page is not 0, the limit?,? syntax is used.
From this, I tentatively judge that it should be the developer of pageHelper who supported postgreSQL in the later period. He was lazy and directly inherited MySqlDialect and adopted another paging syntax of postgreSQL: offset? limit?.
In order to confirm my judgment, I went to pageHelper’s github to see if anyone had mentioned a similar issue. Don’t tell me, I actually found it.
When some people use mybatis-plus, this paging syntax problem will also cause problems with the paging function.
And this outian friend has submitted modified code to solve this problem in version v5.3.1.
Solution
If the pageHelper you are using is version below v5.3.1, and the database you are using is postgreSQL, then you should be careful and check to see if you have stepped on this pit.
So how to solve it? There are two ways:
- Upgrade PageHelper version to v5.3.1 + version;
- Custom PostgreSqlDialect;
Of course, both of these methods are easy to use now. I’m not sure if there will be any new problems with the upgraded version. If you are bolder, you can upgrade it and do functional testing;
Here we mainly talk about the second method, which is actually basically the same as the friend above who solved this problem.
First, customize a dialect class and inherit AbstractHelperDialect;
Then when the class is loaded, register this dialect class into the PageHelper dialect;
Finally, configure this dialect into the configuration file pagehelper.helperDialect=customerpostgresql.
The official repair method is to modify postgreSQLDialect in this way.
Finally
Finally, to briefly summarize, when using a postgreSQL database and using PageHelper for paging, if the version of PageHelper is lower than 5.3.1, there will be problems in the interceptor scenario, which need to be solved by upgrading or transforming postgreSQLDialect. If your project is being used, you can check it in advance to avoid problems.
Method 2: Change the configuration file:
If the mybatis version is not an old version and this problem still occurs, you can change the configuration (I haven’t tested it yet, you can give it a try):
Two methods are available for reference
Method 1. Solution: Comment out the dialect MySQL
# PageHelper paging plug-in pagehelper: # helperDialect: mysql
Refer to the configuration below:
# Multiple data source pagehelper configuration # The paging plug-in will automatically detect the current database link and automatically select the appropriate paging method. pagehelper.auto-dialect=true # The default value is false. When set to true, allows automatic identification of pagination for corresponding dialects based on multiple data sources at runtime. pagehelper.auto-runtime-dialect=true
Method 2.yml configuration
##Print sql
mybatis:
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
#Paging settings
pagehelper:
helper-dialect: postgresql
reasonable: true
support-methods-arguments: true
params: count=countsql
Or add configuration to application.yml
##pageHelper paging plug-in pagehelper.helper-dialect=postgresql pagehelper.reasonable=true pagehelper.support-methods-arguments=true pagehelper.params=count=countSql
Method 3. No need for Page helper
n.yml add configuration
##pageHelper paging plug-in pagehelper.helper-dialect=postgresql pagehelper.reasonable=true pagehelper.support-methods-arguments=true pagehelper.params=count=countSql
Method 3. No need for Page helper
The third method will definitely succeed, that is, you don’t need Pagehelper and write your own paging statements! Add offset and limit manually! Baidu has many paging methods, so I won’t introduce them here.