Is it possible to limit the amount of rows of a class attribute that is a list via JPQL?

Question

Is it possible to limit the amount of rows of a class attribute that is a list via JPQL?

Navigation

#1 by (4 votes)
#2 by (1 votes)

4

I have the following query 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, he loads ten times.

I tried using setMaxResults (int) but not I get success, because as it returns only one user I believe that JPQL understands that it did its work, even though when I get the mirrored query in the console and throw it in my database it returns me more than one line.

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

EDITION: Mapping as a request

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
}

Also, I have the sql that brings me What I want, but I want, if possible, in JPQL to avoid using non-portable code like the rownum of 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;

sql java java-ee java-ee-7 jpql

asked by anonymous 25.08.2014 / 19:58

2 answers

Many-to-many relationship with Asp.net MVC and Entity Framework Exceptions.py file

score 4 · Answer 1

Is it really necessary to query under the user?

Performing the query over address you will be able to page with the methods setFirstResult (int) and setMaxResults (int) / a>

To page within the address that is within the user you will need help, as described at Wakim's suggestion

I suggest you change your query to be done from Endereço , doing JOIN with Usuario through ID and paginate with the two methods mentioned

score 1 · Answer 2

Doing the reverse path as suggested by Felipe Fonseca

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

So you can page normally

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

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

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