CODEWITHSUNDEEP
javaspring-bootspring-mvcspring-data-jpacriteriaquery
Mahima Tiwary
Mahima Tiwary

Reputation: 11

Order by a list in JPA criteria query

My use case is to order the response from a table as per the order of values in a list. The MySQL query for that which is working as expected is:

SELECT 
    property_uuid,
    micromarket_uuid,
    city_uuid,
    SUM(obligation_booked)
FROM
    projects_service_pluto.rent_calculated
WHERE
    property_uuid IN ('33' , '121')
GROUP BY zone_uuid , city_uuid , micromarket_uuid , property_uuid
ORDER BY find_in_set(property_uuid, "33,121");

Here '33' and '121' are sample values. I need to place an ordered list there. I am unable to translate this into JPA Criteria query. So far, I have come to this:

 Set<String> propertySet = new HashSet<>(propertyUuids);
 String paramAsString = String.join(",",propertySet);
    
 Expression<?> orderColumn = builder.function("FIND_IN_SET", 
                                             List.class, 
                                             rentCalculatedRoot.get("propertyUuid"),
                                             builder.literal(paramAsString));
 query.orderBy(orderColumn); //Here it gives an error saying expected value is Order

Replacing it with this:

query.orderBy(builder.asc(orderColumn));

resolves the compile time error but obviously gives the wrong output. What is the solution here?

TLDR; Is there a way in JPA criteria query to sort the output on the basis of a list of values?

Upvotes: 1

Views: 1467

Answers (1)

user8536145
user8536145

Reputation: 11

We can use orderBy method CriteriaQuery to order the data based on single or multiple columns data.

   CriteriaQuery<Test> criteriaQuery = criteriaBuilder.createQuery(Test.class);
   Root<Test> from = criteriaQuery.from(Test.class);
   CriteriaQuery<Test> select = criteriaQuery.select(Test);
   criteriaQuery.orderBy(criteriaBuilder.asc(from.get("name")));

Please see if this helps.

Upvotes: 1

Related Questions