[Solved] Mybatis integrates PageHelper and uses sqlserver paging error

The environment uses mybatis plus, the paging plug-in: PageHelper 5.2.0, and the database uses sqlserver2012 or above

In fact, the paging plug-in is ultimately handled by the mybatis interceptor, so it is equivalent to the mybatis environment.

Using paging

 PageHelper.startPage(1,10); // Pagination
  orderMapper.list(); // Follow the execution

Then an error will be reported

SQL: SELECT  id,product_name,xxxx,xxxxx  FROM product_xxxx  OFFSET ?ROWS FETCH NEXT ?ROWS ONLY
### Cause: com.microsoft.sqlserver.jdbc.SQLServerException: “@P0”There is a grammatical error nearby.
; uncategorized SQLException; SQL state [S0001]; error code [102]; There is a syntax error near "@P0". ; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: There is a syntax error near "@P0".

Finally, after checking, it is found that the paging syntax offset and fetch used by PageHelper are sqlserver’s support for sorting.

Therefore, if you want to use this plug-in to page, you need to add sorting.

PageHelper.startPage(1,10,"xxxx sorted table field name, not attribute name");
orderMapper.list(); // Follow the execution

Or use the default sort field

orderMapper.list(); //Follow up

Read More: