V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
jiobanma
V2EX  ›  Java

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

  •  
  •   jiobanma ·
    banmajio · 2023-01-09 17:23:14 +08:00 · 1276 次点击
    这是一个创建于 444 天前的主题,其中的信息可能已经有所发展或是发生改变。

    如题

    报错内容如下:

    ### 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 条回复    2023-01-12 23:12:10 +08:00
    xiang0818
        1
    xiang0818  
       2023-01-09 17:34:52 +08:00
    ### Cause: java.lang.ClassCastException: java.lang.String cannot be cast to java.lang.Integer

    这个明显是 Java 的报错。
    可能是 resultMap 映射的问题。查询出来的数据 字符串类型的数据 用 Integer 类型接收了。
    7911364440
        2
    7911364440  
       2023-01-09 17:36:21 +08:00
    实体类跟数据库字段的数据类型映射错了,检查下实体类字段类型。
    jiobanma
        3
    jiobanma  
    OP
       2023-01-09 17:37:57 +08:00
    @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.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 ?,?\n### Cause: java.lang.ClassCastException: java.lang.String cannot be cast to java.lang.Integer",
    ```
    jiobanma
        4
    jiobanma  
    OP
       2023-01-09 17:39:45 +08:00
    @7911364440
    @xiang0818
    应该不是实体类类型的原因
    实体类如下:
    @Data
    public class ClassErrorWordResponse implements Serializable {
    private Long wordId;
    private String wordSpell;
    private String viewChapterId;
    }

    对应的数据库类型为 bigint varchar varchar
    7911364440
        5
    7911364440  
       2023-01-09 17:56:51 +08:00
    @jiobanma astq.`status` = '0' 这个字段是 varchar 吗?不是的话把引号去掉试试?
    jiobanma
        6
    jiobanma  
    OP
       2023-01-09 17:59:34 +08:00
    @7911364440 数据库之前建表的时候是 char ,这个报错我现在发现的问题就是分页导致的。如果我把最外层的 select 去掉的话,就可以正常了。但是我的需求是需要 union 之后去重的,所以得加外层的 select
    Macolor21
        7
    Macolor21  
       2023-01-09 20:57:23 +08:00
    异常没给你代码行数吗?就算是 MyBatis Plus 的代码,追溯查一下也更快吧。。
    zoyao
        8
    zoyao  
       2023-01-10 14:45:47 +08:00
    看样子是输入参数的原因吧,=号前边是 int 类型,=号后边输入是 string
    vdrapb
        9
    vdrapb  
       2023-01-10 15:22:49 +08:00
    你把你传递的 request 打印出来,应该就能看到哪出问题了
    Pony69
        10
    Pony69  
       2023-01-12 23:12:10 +08:00
    IPage<ClassErrorWordResponse> selectStudentErrorWordInfo(@Param("page") Page<ClassErrorWordResponse> page, @Param("classCode") String classCode, @Param("studentCode") String studentCode, @Param("bookId") Long bookId);

    是不是返回类型有问题?
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   4848 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 23ms · UTC 01:10 · PVG 09:10 · LAX 18:10 · JFK 21:10
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.