Search code examples
javaspringspring-data-jpaspring-data

Spring @Query can't pass parameter in to sql native query

@Query(value = "SELECT * FROM register_log limit 50 offset :page",
            countQuery = "SELECT COUNT(*) FROM register_log ",
            nativeQuery = true)
    List<RegisterLog> findRegisterLogsOptimized(@Param("page") int page);

Here page can't be seen by Java.

always have - Hibernate: SELECT * FROM register_log limit 50 offset ?.

here invocation // in service

 public List<RegisterLog> findRegisterLogsOptimized(int page) {

        return registerLogRepository.findRegisterLogsOptimized(page);
    }

// in repository

registerLogService.findRegisterLogsOptimized(50);

enter image description here

Solution

  • I'm assuming you are saying this doesn't work, because it produces some exception.

    Many (most? all?) database don't accept offset and limit as bound parameters. If that is the case you need to remove the offset and limit clauses and instead pass a Pageable as an argument. Spring Data JPA will then create the proper offset and limit clauses for you.