Is it possible to limit the number of lines in a class attribute that is a list via JPQL?

Question

I have the query below in JPQL

FROM User u INNER JOIN FETCH u.enderecos e WHERE u.id =:id ORDER BY e.id DESC

A user can have more than ten addresses, so we want to bring only ten addresses and if the user wants, it loads ten by ten.

I tried to use setMaxResults(int) but I did not succeed, because as it returns only one user I believe that JPQL understands that it has done its job, even if by taking the mirror query on the console and throwing it in my bank it returns me more than one line.

Is it possible to do what I need? If so, how?

EDITION: Mapping as requested

public class Usuario implements Serializable {  
    private static final long serialVersionUID = 4769740907794027841L;

    // Outros atributos

    @OneToMany(fetch = FetchType.LAZY, cascade = CascadeType.ALL, mappedBy = "usuario")
    private Set<Endereco> enderecos;

    // Sets e gets
}

public class Endereco implements Serializable { 
    private static final long serialVersionUID = -6380840893466300379L;

    // Outros atributos

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "CD_USUARIO", nullable = false, referencedColumnName = "CD_USUARIO")
    private Usuario usuario;

    // Sets e gets
}

Besides, I have the remote sql that brings me what I want, but I want, if possible, in JPQL to avoid using nonportable code like the rownum of the oracle.

SELECT *
FROM user u INNER JOIN endereco e ON u.id_user = e.id_user
WHERE u.id_user = '123' AND ROWNUM = 1
ORDER BY e.id_address desc;

Answer 1

It is really necessary to make the query under user?

By performing the query on top of address you will be able to paginate with the methods setFirstResult(int) and setMaxResults(int)

To paginate within the address inside the user you will need help, as described at the suggestion of Wakim

It is advisable to change your query to be performed from Endereço, performing JOIN with Usuario through the ID and paginate with the above two methods

Answer 2

Doing the reverse way as suggested by Felipe Fonseca

SELECT e
FROM Endereco e
WHERE e.usuario.id = :id
ORDER BY e.id DESC

Then you can page normally

em.createQuery(query)
    .setParameter("id", id)
    .setMaxResults(10)
    .getResultList();

If you want it is even possible to return the user together with the address (Result of type Object[]), however, I find it easier to search for the user and separate addresses:

SELECT e, u
FROM Endereco e 
JOIN e.usuario u
WHERE u.id = :id
ORDER BY e.id DESC