我有个问题。我尝试用时间戳中的日期过滤来进行自定义查询，但我使用pageable，它在逗号id desc limit之后添加到查询中，并导致如下错误。我不知道如何解决这个问题。

select * from lights Where timestamp BETWEEN ? AND ? ORDER BY id LIMIT 999999, id desc limit ?
2021-03-05 15:49:59.545 TRACE 896 --- [nio-8080-exec-1] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [VARCHAR] - ['2021-03-04 15:27:07']
2021-03-05 15:49:59.545 TRACE 896 --- [nio-8080-exec-1] o.h.type.descriptor.sql.BasicBinder      : binding parameter [2] as [VARCHAR] - ['2999-12-31 23:59:59']
2021-03-05 15:49:59.556  WARN 896 --- [nio-8080-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 1064, SQLState: 42000
2021-03-05 15:49:59.556 ERROR 896 --- [nio-8080-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'desc limit 300' at line 1

自定义查询

@Query(nativeQuery = true, value = "select * from lights Where timestamp BETWEEN :startDate AND :endDate ORDER BY id LIMIT 999999")
    Page<Lights> findBetweenDates(@Param("startDate") String startDate, @Param("endDate") String endDate, Pageable pageable);

conrtoller类

@GetMapping("/lights")
    public String viewHomePage(Model model, String startDate , String endDate) {
        startDate = "'2021-03-04 15:27:07'";
        endDate = "'2999-12-31 23:59:59'";
        return findPaginated(1, "id", "desc",startDate,endDate, model);
    }

    @RequestMapping({"/lights/page/{pageNo}"})
    public String findPaginated(@PathVariable(value = "pageNo") int pageNo,
                                @RequestParam("sortField" ) String sortField,
                                @RequestParam("sortDir") String sortDir,
                                @RequestParam(value = "startDate", defaultValue = "'2021-03-04 15:27:07'") String startDate,
                                @RequestParam(value = "endDate", defaultValue = "'2999-12-31 23:59:59'") String endDate,
                                Model model){
        int pageSize = 200;

        Page<Lights> page = lightsMapService.findPaginated(pageNo,pageSize,sortField,sortDir,startDate,endDate);
        List<Lights> lightsList = page.getContent();

        model.addAttribute("currentPage", pageNo );
        model.addAttribute("totalPages", page.getTotalPages());
        model.addAttribute("totalItems",page.getTotalElements() );

        model.addAttribute("sortField", sortField );
        model.addAttribute("sortDir", sortDir );
        model.addAttribute("startDate",startDate);
        model.addAttribute("endDate", endDate);
        model.addAttribute("reverseSortDir", sortDir.equals("asc")?"desc":"asc");

        model.addAttribute("lightsList", lightsList );

        return "sensorsPages/lights";

    }

从查询中删除limit99999后，我没有得到错误enymore，但得到了这个结果，并且它不起作用：

2021-03-05 16:09:47.789 DEBUG 3540 --- [nio-8080-exec-7] org.hibernate.SQL                        : select * from lights Where timestamp BETWEEN ? AND ? ORDER BY id, id desc limit ?
2021-03-05 16:09:47.789 TRACE 3540 --- [nio-8080-exec-7] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [VARCHAR] - ['2021-03-04 15:27:07']
2021-03-05 16:09:47.789 TRACE 3540 --- [nio-8080-exec-7] o.h.type.descriptor.sql.BasicBinder      : binding parameter [2] as [VARCHAR] - ['2999-12-31 23:59:59']