[错误报告]: Sqlserver2019 dev 分页失败。生成的SQL语句不带分页信息。

[kaizhutongxie]

确认

• [X] 我的版本是最新版本, 我的版本号与 version 相同, 并且项目里无依赖冲突
• [X] 我已经在 issue 中搜索过, 确认问题没有被提出过
• [X] 我已经修改标题, 将标题中的 描述 替换为遇到的问题

当前程序版本

mybatis-plus-boot-starter 3.4.3.4

问题描述

环境： 主机：MacOS mybatis-plus-boot-starter 3.4.3.4 com.microsoft.sqlserver mssql-jdbc 9.4.0.jre8 docker--->Sqlserver2019dev 在执行这段代码时： @ApiOperation("查询视图列表") @GetMapping("list") public JsonResultObject get(@RequestParam(value = "entityid", required = true) UUID entityId, @RequestParam(name = "name", required = false) String name, @RequestParam(name = "getall", required = false) Boolean getall, @RequestParam(name = "page", required = false) Integer page, @RequestParam(name = "pagesize", required = false) Integer pagesize, @RequestParam(value = "sortby", required = false) String sortby, @RequestParam(value = "sortdirection", required = false) String sortdirection) { if (UUIDUtil.isEmpty(entityId)) { return JNotSpecifiedParams("entityid"); } LambdaQueryWrapper lambdaQueryWrapper = getSortedQueryWrapper(sortby, sortdirection); //sortby, sortdirection lambdaQueryWrapper.eq(QueryView::getEntityid, entityId); if (StringUtil.notEmpty(name)) { lambdaQueryWrapper.like(QueryView::getName, name); } if (getall != null && getall) { return JOk(_queryViewFinderService.list(lambdaQueryWrapper)); } else if (page == null || pagesize == null) { return JNotSpecifiedParams("page", "pagesize"); } page = (page <= 0 ? 1 : page); pagesize = (pagesize <= 0 ? 10 : pagesize); IPage datas = _queryViewFinderService.page(new Page<>(page, pagesize), lambdaQueryWrapper); PagedList result = new PagedList<>(datas.getRecords(), page, pagesize, datas.getTotal(), datas.getPages());

    return JOk(result);
}

这里报错： IPage datas = _queryViewFinderService.page(new Page<>(page, pagesize), lambdaQueryWrapper); 提示： nested exception is org.apache.ibatis.exceptions.PersistenceException: ### Error querying database. Cause: com.baomidou.mybatisplus.core.exceptions.MybatisPlusException: Error: Method queryTotal execution error of sql : SELECT COUNT() FROM (SELECT COUNT() FROM (SELECT entityid,[name],[logenabled],[localizedname],[iscustomizable],[authorizationenabled],[entitymask],[duplicateenabled],[workflowenabled],[parententityid],[entitygroups],[createdby],[createdon],[description],[organizationid] FROM dbo.entity ORDER BY createdon DESC) TOTAL) TOTAL 。 这里生成的 SQL 执行失败： 1.2 次SELECT COUNT(*) 2.分页信息丢失了，没有生成分页的 SQL 语句。

详细堆栈日志

No response

[miemieYaho]

先查count再分页

[kaizhutongxie]

先查count再分页

int totalCount = _queryViewFinderService.count(lambdaQueryWrapper); -----totalCount=38 IPage datas = _queryViewFinderService.page(new Page<>(page, pagesize),lambdaQueryWrapper); 报错信息：

Error querying database. Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 'OFFSET'. ### The error may exist in com/xms/queryview/data/QueryviewMapper.java (best guess) ### The error may involve defaultParameterMap ### The error occurred while setting parameters ### SQL: SELECT queryviewid,[name],[description],[isdefault],[fetchconfig],[layoutconfig],[createdon],[createdby],[modifiedon],[modifiedby],[ownerid],[isprivate],[entityid],[entityname],[issimplefilter],[iscustombutton],[custombuttons],[authorizationenabled],[aggregateconfig],[targetformid],[statecode] FROM dbo.queryView WHERE ([entityid] = ?) OFFSET ? ROWS FETCH NEXT ? ROWS ONLY ### Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 'OFFSET'. ; uncategorized SQLException; SQL state [S0001]; error code [102]; Incorrect syntax near 'OFFSET'.; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 'OFFSET'.

[miemieYaho]

更新版本

[kaizhutongxie]

版本信息：

com.baomidou mybatis-plus-boot-starter 3.5.7 com.baomidou mybatis-plus-generator 3.5.7

执行page时报错： 提示

Error querying database. Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '@P0'. ### The error may exist in com/xms/schema/data/EntityMapper.java (best guess) ### The error may involve defaultParameterMap ### The error occurred while setting parameters ### SQL: SELECT [entityid],[name],[logenabled],[localizedname],[iscustomizable],[authorizationenabled],[entitymask],[duplicateenabled],[workflowenabled],[parententityid],[entitygroups],[createdby],[createdon],[description],[organizationid] FROM dbo.entity OFFSET ? ROWS FETCH NEXT ? ROWS ONLY ### Cause: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '@P0'. ; uncategorized SQLException; SQL state [S0001]; error code [102]; Incorrect syntax near '@P0'.; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '@P0'.

[kaizhutongxie]

另外提一嘴，可以断点看到生成T-SQL的代码吗？

[miemieYaho]

什么tsql？你把分页插件指定一下数据库类型为sqlserver高的那个版本试试呢