'JPA CRITERIA QUERY with order by joined columns
How to invoke order by on a joined entity? I am trying to achieve the following with:
select * from person p inner join telephone t on p.id=t.person_id join sim s on s.id=t.sim_id order by s.name DESC
@Entity
public class Person implements Serializable{
@Id
private Long id;
@OneToMany(orphanRemoval = true, mappedBy = "person", fetch = FetchType.LAZY, cascade = CascadeType.PERSIST)
private List<Telephone> telephonesNumber;
@Entity
public class Telephone implements Serializable {
@Id
private String number;
@Id
@ManyToOne()
@JoinColumn(name = "person_id")
private Person person;
@Id
@ManyToOne(cascade = {})
@JoinColumn(name = "sim_id")
private Sim sim;
@Entity
public class Sim implements Serializable {
@Id
private Long id;
@Column(unique = true)
private String name;
I use specification interface, in this example sorting is on the field person.id and it works
public class PersonSpecification implements Specification<Person> {
@Override
public Predicate toPredicate(Root<Person> root, CriteriaQuery<?> query, CriteriaBuilder builder) {
List<Predicate> predicates = new ArrayList<>();
// there is many different conditions for example
// if(someCondition!=null) {
// predicates.add(builder.like(root.get("someProperty"), someValue));
// }
query.groupBy(root.get("id"));
//there I want to order by Sim.name i dont know how
query.orderBy(builder.asc(root.get("phone")));//this works
return builder.and((predicates.toArray(new Predicate[predicates.size()])));
}
I want to order by Sim.name but i dont know how.
Solution 1:[1]
In JPA specification you can use:
query.orderBy(builder.asc(root.join("telephonesNumber").get("sim").get("name")));
to sort by sim name.
For more details: https://en.wikibooks.org/wiki/Java_Persistence/Querying#Joining.2C_querying_on_a_OneToMany_relationship
If you using JPA Query:
@Query("select s from Person p
join p.telephonesNumber t
join t.sim s order
by t.sim.id desc")
It will produce this:
select * from person p
inner join telephone t on p.id=t.person_id
inner join sim s on t.sim_id=s.id
order by t.sim_id desc
For more details:
Solution 2:[2]
another way for that would be using Query method:
List<Telephone> findAllByOrderBySimIdAsc();
Look at this findAllByOrderBySimIdAsc
With the code before, you can get all rows from Telephone ordered by Sim Id.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
Solution | Source |
---|---|
Solution 1 | |
Solution 2 | ceduard0 |