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 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;
Have you tried subquery ? so it is possible to use limit within the subquery that will fetch the addresses.
– Josh
Philippe, could include mapping the relationship between
User
andAddress
?– Wakim
@Wakim included the mapping and SQL I have today that brings me the result I need.
– Philippe Gioseffi
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.
– Wakim
@Walkim was more or less the same response I received in the How to limit Entity list-Property size with JPQL?
– Philippe Gioseffi
Philippe, which is your JPA provider? Hibernate? Eclipselink?
– Anthony Accioly
@Anthonyaccioly pure JPA.
– Philippe Gioseffi
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.
– Anthony Accioly
Thus, we do not use any framework except the persistence that uses Hibernate for the settings.
– Philippe Gioseffi