mybatis-plus 使用 IPage 分页，手写 sql 报错的问题求指教

This topic created in 1219 days ago, the information mentioned may be changed or developed.

如题

报错内容如下：

### The error may involve defaultParameterMap ### The error occurred while setting parameters ### SQL: SELECT * FROM( SELECT bwi.id AS word_id, bwi.word_spell, bwi.view_chapter_id FROM auto_student_test_question astq LEFT JOIN base_word_info bwi ON astq.word_id = bwi.id WHERE astq.del_flag = '0' AND astq.`status` = '0' AND astq.class_code = ? AND astq.student_code = ? AND astq.book_id = ? AND astq.test_result = '2' UNION SELECT bwi.id AS word_id, bwi.word_spell, bwi.view_chapter_id FROM word_student_note wsn LEFT JOIN base_word_info bwi ON wsn.word_id = bwi.id WHERE wsn.del_flag = '0' AND wsn.`status` = '0' AND wsn.class_code = ? AND wsn.student_code = ? AND wsn.book_id = ? AND wsn.error_count != 0) unionTable LIMIT ?,? ### Cause: java.lang.ClassCastException: java.lang.String cannot be cast to java.lang.Integer

1.service 代码：

List<ClassErrorWordResponse> words = autoClassNoteService.selectStudentErrorWordInfo(request.getPage(), request.getClassCode(), request.getStudentCode(), request.getBookId());

2.Mapper.java 代码

List<ClassErrorWordResponse> selectStudentErrorWordInfo(@Param("page") Page<ClassErrorWordResponse> page, @Param("classCode") String classCode, @Param("studentCode") String studentCode, @Param("bookId") Long bookId);

3.mapper.xml 代码

SELECT DISTINCT a.word_id, a.word_spell, a.view_chapter_id FROM( SELECT bwi.id AS word_id, bwi.word_spell, bwi.view_chapter_id FROM auto_student_test_question astq LEFT JOIN base_word_info bwi ON astq.word_id = bwi.id WHERE astq.del_flag = '0' AND astq.`status` = '0' AND astq.class_code = #{classCode} AND astq.student_code = #{studentCode} AND astq.book_id = #{bookId} AND astq.test_result = '2' UNION SELECT bwi.id AS word_id, bwi.word_spell, bwi.view_chapter_id FROM word_student_note wsn LEFT JOIN base_word_info bwi ON wsn.word_id = bwi.id WHERE wsn.del_flag = '0' AND wsn.`status` = '0' AND wsn.class_code = #{classCode} AND wsn.student_code = #{studentCode} AND wsn.book_id = #{bookId} AND wsn.error_count != 0) a

各种尝试感觉是因为最外层的子查询导致的，sql 是没问题的，打印出来的 sql 丢 navicat 里正常运行，并且如果不分页的话 sql 也正常，只要带着分页就会报错。大家有遇到过的吗？

10 replies 2023-01-12 23:12:10 +08:00

xiang0818

Jan 9, 2023

### Cause: java.lang.ClassCastException: java.lang.String cannot be cast to java.lang.Integer

这个明显是 Java 的报错。
可能是 resultMap 映射的问题。查询出来的数据字符串类型的数据用 Integer 类型接收了。

7911364440

Jan 9, 2023

实体类跟数据库字段的数据类型映射错了，检查下实体类字段类型。

jiobanma

Jan 9, 2023

@xiang0818 我没截全异常信息
```
"msg": "nested exception is org.apache.ibatis.exceptions.PersistenceException: \n### Error querying database. Cause: java.lang.ClassCastException: java.lang.String cannot be cast to java.lang.Integer\n### The error may exist in file [/Users/banmajio/Project/xdfcloud-word-fast/xdfcloud-word-learn/target/classes/mapper/AutoClassNoteMapper.xml]\n### The error may involve defaultParameterMap\n### The error occurred while setting parameters\n### SQL: SELECT * FROM( SELECT bwi.id AS word_id, bwi.word_spell, bwi.view_chapter_id FROM auto_student_test_question astq LEFT JOIN base_word_info bwi ON astq.word_id = bwi.id WHERE astq.del_flag = '0' AND astq.`status` = '0' AND astq.class_code = ? AND astq.student_code = ? AND astq.book_id = ? AND astq.test_result = '2' UNION SELECT bwi.id AS word_id, bwi.word_spell, bwi.vie_chapter_id FROM word_student_note wsn LEFT JOIN base_word_info bwi ON wsn.word_id = bwi.id WHERE wsn.del_flag = '0' AND wsn.`status` = '0' AND wsn.class_code = ? AND wsn.student_code = ? AND wsn.book_id = ? AND wsn.error_count != 0) unionTable LIMIT ?,?\n### Cause: java.lang.ClassCastException: java.lang.String cannot be cast to java.lang.Integer",
```

jiobanma

Jan 9, 2023

@7911364440
@xiang0818
应该不是实体类类型的原因
实体类如下：
@Data
public class ClassErrorWordResponse implements Serializable {
private Long wordId;
private String wordSpell;
private String viewChapterId;
}

对应的数据库类型为 bigint varchar varchar

7911364440

Jan 9, 2023

@jiobanma astq.`status` = '0' 这个字段是 varchar 吗？不是的话把引号去掉试试？

jiobanma

Jan 9, 2023

@7911364440 数据库之前建表的时候是 char ，这个报错我现在发现的问题就是分页导致的。如果我把最外层的 select 去掉的话，就可以正常了。但是我的需求是需要 union 之后去重的，所以得加外层的 select

Macolor21

Jan 9, 2023

异常没给你代码行数吗？就算是 MyBatis Plus 的代码，追溯查一下也更快吧。。

zoyao

Jan 10, 2023

看样子是输入参数的原因吧，=号前边是 int 类型，=号后边输入是 string

vdrapb

Jan 10, 2023

你把你传递的 request 打印出来，应该就能看到哪出问题了

Pony69

Jan 12, 2023

IPage<ClassErrorWordResponse> selectStudentErrorWordInfo(@Param("page") Page<ClassErrorWordResponse> page, @Param("classCode") String classCode, @Param("studentCode") String studentCode, @Param("bookId") Long bookId);

是不是返回类型有问题？