How to accept @OrderBy of JPA sort value as a dynamic parameter?

Question

I want to sort a nested collection property dynamically based on the query parameter sort.

Suppose i have an entity A as

class A{

@OneToMany(mappedBy="a")
private Set<B> bset;

}

class B{

private LocalDate datefield;
@ManyToOne
private C c;

}

class C
{
private Double quantity;

}

I am calling repository of A with findAll(Specification specification,Pageable page)

From UI, rest controller is called with sort param as below pattern

 url?page=0&size=10&sort=bset_datefield

As it is a nested collection,the above sort is not working. The sortable fields are datefield,quantity.

I know @OrderBy("bset.datefield") will work,but the parameter should be dynamic.

How can i achieve this when invoking find All on A repository ?

The elements of a Set have no order. You will need to use a SortedSet instead. To do the sort dynamic as you want, I think you need to create a custom SQL query or sort the elements after loading them from the database. — Edgar Domingues, Jul 24 '20 at 05:45

score 0 · Accepted Answer · answered Jul 24 '20 at 15:37

0

It cannot be done because when the entityManager is initialized the annotations are evaluated.

The best options are, create a query that comes ordered or once the list is obtained if you use Java 8 from now on, order with the sort method of stream.

answered Jul 24 '20 at 15:37

JLazar0

1,257
1
11
22

score 0 · Answer 2 · answered Jul 24 '20 at 21:49

You cannot do that, it is not possible using only Spring Data.

Conceptually, you are using a JpaSpecificationExecutor and Specifications parameterized in terms on the class A.

In the indicated findAll method you can sort A by any property, even by a nested one, but you are sorting A.

You can instruct the JPA provider to fetch a collection, to sort by a collection field, but you cannot sort the collection itself.

Even in the case that in the Specification body of A you use some kind of subquery which involve the collection that must be sorted, and you establish a sort by the fields of the collection in this subquery, I think - maybe it should be tested - that the JPA provider will return the instances of class A and then initialize the collection according to the provided @OrderBycriteria.

By the way, and semantically, it is always a good practice to use a List instead of a Setwhen using @OrderBy.

So if you want to sort the collection I think the only alternative you have is to process the sort criteria received in the query string and sort the collection in memory by object comparison before returning the data to the client.

score 0 · Answer 3 · answered Jun 22 '21 at 21:25

Following this tutorial: REST Query Language with Spring Data JPA and Querydsl

When you use

MyUserPredicatesBuilder

It does the magic trick.

@Controller
public class UserController {

@Autowired
private MyUserRepository myUserRepository;

@RequestMapping(method = RequestMethod.GET, value = "/myusers")
@ResponseBody
public Iterable<MyUser> search(@RequestParam(value = "search") String search) {
    MyUserPredicatesBuilder builder = new MyUserPredicatesBuilder();

    if (search != null) {
        Pattern pattern = Pattern.compile("(\w+?)(:|<|>)(\w+?),");
        Matcher matcher = pattern.matcher(search + ",");
        while (matcher.find()) {
            builder.with(matcher.group(1), matcher.group(2), matcher.group(3));
        }
    }
    BooleanExpression exp = builder.build();
    return myUserRepository.findAll(exp);
}

}

How to accept @OrderBy of JPA sort value as a dynamic parameter?

3 Answers3

Linked