一、包含子查询的分页查询异常

  • 异常表现：

总数量查询是错误的子查询sql；
提示参数位置异常；

  • 异常原因：

没写countQuery；
countQuery的sql编写不完整，参数引用与value的sql不一致；

  • 解决方案：

在@Query中编写countQuery=”“；countQuery的sql参数引用必须与value的sql的参数一致

正确示例：

  1. /**
  2. * 直播分页查询
  3. * @param  searchTxt 搜索内容 
  4. * @param  startDate 开始时间 
  5. * @param  endDate 结束时间 
  6. * @param  pageable 分页参数 
  7. * @author  chaiin 
  8. * @date  2020/06/18 15:11:00 
  9. */
  10. @Query(value  = "select ls.id, " + 
  11. " res.url AS picOrPreviewVideoUrl, " +
  12. " ls.live_url AS liveUrl, " +
  13. " ls.title, " +
  14. " concat(ls.start_time,'') AS liveTime, " +
  15. " concat(bc.campus_name, '', bc.building_name, '', bc.classroom_name) AS classroomName, " +
  16. " au.user_name AS anchorName, " +
  17. " ifnull(rr.ftp_url, rr.url) replayUrl, " +
  18. " case ls.status " +
  19. "  when 1 then '未开始' " +
  20. "  when 2 then '直播中' " +
  21. "  when 3 then '已结束' " +
  22. "  when 4 then '已取消' " +
  23. " end AS status, " +
  24. " case ls.status " +
  25. "  when 1 then 2 " +
  26. "  when 2 then 1 " +
  27. "  else ls.status " +
  28. " end AS statusSort, " +
  29. " if(ls.power = 2, 1, 0) AS power " +
  30. " from " +
  31. " ( " +
  32. "   select * from live_seeding " +
  33. "   where is_deleted = 0 " +
  34. "   and status in (1, 2, 3) " +
  35. "   and state = 2 " +
  36. "   and IF(?2!=null, start_time between ?2 and ?3, '1=1' ) " +
  37. " ) ls " +
  38. " left join resources res on ls.resource_id = res.id " +
  39. " left join authority_user au on ls.user_id = au.id " +
  40. " left join base_classroom bc on ls.room_id = bc.id " +
  41. " left join recording_replay rr on ls.id = rr.live_id " +
  42. " where IF(" +
  43. "   ?1!='', " +
  44. "   (locate(?1, ls.title) > 0 " +
  45. "       or locate(?1, ls.course_remark) > 0 " +
  46. "       or locate(?1, au.user_name) > 0 " +
  47. "       or locate(?1, concat(bc.campus_name, '', bc.building_name, '', bc.classroom_name)) > 0), " +
  48. "   '1=1'" +
  49. " ) " +
  50. " order by statusSort asc, liveTime desc",
  51. countQuery = "select count(*)" +
  52. " from " +
  53. " ( " +
  54. "   select * from live_seeding " +
  55. "   where is_deleted = 0 " +
  56. "   and status in (1, 2, 3) " +
  57. "   and state = 2 " +
  58. "   and IF(?2!=null, start_time between ?2 and ?3, '1=1' ) " +
  59. " ) ls " +
  60. " left join resources res on ls.resource_id = res.id " +
  61. " left join authority_user au on ls.user_id = au.id " +
  62. " left join base_classroom bc on ls.room_id = bc.id " +
  63. " left join recording_replay rr on ls.id = rr.live_id " +
  64. " where IF(" +
  65. "   ?1!='', " +
  66. "   (locate(?1, ls.title) > 0 " +
  67. "       or locate(?1, ls.course_remark) > 0 " +
  68. "       or locate(?1, au.user_name) > 0 " +
  69. "       or locate(?1, concat(bc.campus_name, '', bc.building_name, '', bc.classroom_name)) > 0), " +
  70. "   '1=1'" +
  71. " ) ",
  72. nativeQuery = true)
  73. // 时间条件错误 
  74. /* Page<Map<String,Object>> pageLivePaly(String searchTxt, Date startDate, Date endDate, Pageable pageable); */
  75. // 时间条件正确
  76. Page<Map<String,Object>> pageLivePaly(String searchTxt, String startDate, String endDate, Pageable pageable);

二、查询操作时间条件无效

时间条件应使用String类型