CODEWITHSUNDEEP
javaspringspring-bootjparepository
SadException
SadException

Reputation: 13

JPA Unknow column in table aliases with Pagination/Page in Spring

In spring implementing pagination with a custom query I request:

{{host}}:8080/list?page=0&size=2 and the result is OK
{{host}}:8080/list?page=0&size=3 and the result is OK
{{host}}:8080/list?page=0&size=4 and the result is OK
{{host}}:8080/list?page=0&size=1 and the result is NOT OK
{{host}}:8080/list?page=1&size=1 and the result is NOT OK
{{host}}:8080/list?page=1&size=2 and the result is NOT OK
{{host}}:8080/list?page=1&size=3 and the result is NOT OK

Controller:

@GetMapping(value = "/list")
public Page<User> list(Pageable pageable) {
    try {
        return userRepository.findUser(pageable);
    } catch (Exception e) {
        logger.error("Ex: {}", e);
        return null;
    }
}

Repository:

@Query(value = "select U.*, M.local as LocalM from user U inner join Morada M on M.idmorada = U.morada", nativeQuery= true)
public Page<User> findUser(Pageable pageable);

What happens when the response is not ok:

2020-01-03 11:34:01.659  WARN 9652 --- [nio-8080-exec-3] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 1054, SQLState: 42S22
2020-01-03 11:34:01.659 ERROR 9652 --- [nio-8080-exec-3] o.h.engine.jdbc.spi.SqlExceptionHelper   : Unknown column 'U' in 'field list'

Why does the pagination properties size and page only work in certain scenarios with the nativeQuery?

Upvotes: 0

Views: 2228

Answers (1)

dexter_
dexter_

Reputation: 381

You also need a count query for the pagination to work, like below - 

@Query(
  value = "select U.*, M.local as LocalM from user U inner join Morada M on M.idmorada = U.morada", 
  countQuery = "select count(*) from user U inner join Morada M on M.idmorada = U.morada", 
  nativeQuery = true)
Page<User> findUser(Pageable pageable);

For Spring JPA ver prior to 2.4, you need a workaround in sql stmt like - 

value = "select U.*, M.local as LocalM from user U inner join Morada M on M.idmorada = U.morada order by U.id \n-- #pageable\n"

The #pageable placeholder tells Spring Data JPA how to parse the query and inject the pageable parameter. As far as projection is concerned you could use a interface to map your resultset like - 

public interface IUser {
public getId();
... getter methods from User entity
public getLocalM();
}

Upvotes: 3

Related Questions