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

Asked

Viewed 851 times

4

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 that brings me what I want, but I want, if possible, in JPQL to avoid using nonportable code like the rownum of the .

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;
  • Have you tried subquery ? so it is possible to use limit within the subquery that will fetch the addresses.

  • Philippe, could include mapping the relationship between User and Address?

  • @Wakim included the mapping and SQL I have today that brings me the result I need.

  • I’ve been doing some research and I saw that JPA does not support pagination in mapping. You will need to view Jpaquery or use JPQL to search for addresses in favor of using entity mapping. I came to find this question from the EN OS: http://stackoverflow.com/questions/3970681/how-can-i-do-paging-with-onetomany-collections.

  • @Walkim was more or less the same response I received in the How to limit Entity list-Property size with JPQL?

  • Philippe, which is your JPA provider? Hibernate? Eclipselink?

  • @Anthonyaccioly pure JPA.

  • Philippe, there is no pure JPA. JPA is a service specification (think as a set and abstract interfaces and classes), you or your application server must have configured a JPA provider.

  • Thus, we do not use any framework except the persistence that uses Hibernate for the settings.

Show 4 more comments

2 answers

4

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

  • I need all user information and addresses. You can edit your reply and put an example, please?

  • @Philippegioseffi With the query there is no way. Could not make the pagination in the view? There are components that have this facility.

1

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

Browser other questions tagged

You are not signed in. Login or sign up in order to post.