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

Issue

@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);

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.

Answered By - Jens Schauder
Answer Checked By - Clifford M. (JavaFixing Volunteer)