How to use FIELD() function of mysql in JPA

Question

My sql query is something like

Select * from tableA order by FIELD('ID', 3, 5, 2)

How do I implement this in JPA using criteria builder?

EDIT

List<Integer> ordList = new ArrayList<Integer>();
ordList.add(3);
ordList.add(5);
ordList.add(2);
public List<Order> getOrderBys(CriteriaBuilder cb, Root<?> root) { 
     List<Order> orders = new ArrayList<Order>();         
           orders.add(cb.function("FIELD", Integer.class, root.get("id"), ordList));
     return orders; 
}

Above is my function which gives list of oders, I want to add only one order same as above sql query. How can I add/call that function in orders.add() method? The above method gives error.

As all JPQL docs for JPA 2.1 will say, you can use "Function('funcName', args)" to invoke SQL functions ... and lose DB independence — , Dec 03 '15 at 15:08
Nope, we can do as @BillyFrost said but not done for code's sake — Akshay, Oct 25 '16 at 06:50

score 0 · Answer 1 · answered Jul 22 '19 at 12:30

0

We can use with the native query flag. For example.,

@Query(value="select * from table_name where id IN :id ORDER BY FIELD(id,:id)",nativeQuery = true)
List<Entity Name> getByIds(@Param("id") List<Integer> id);

answered Jul 22 '19 at 12:30

Naveen Yalla

141
1
11

Want to use JPA not native query – Akshay Jul 23 '19 at 13:58
Are you still searching for answer?? – Naveen Yalla Jul 30 '19 at 12:37
Better if I get answer. As it turned out it is not possible. – Akshay Jul 30 '19 at 13:29
Even I searched and finally adjusted with native query. Better hope someone will answer it. – Naveen Yalla Jul 30 '19 at 14:54

score 0 · Answer 2 · answered Dec 05 '20 at 14:28

For that simple case I would not use the FIELD function but would sort the result by the sorted list, as described here. E.g.:

List<Integer> ordList = new ArrayList<Integer>();
ordList.add(3);
ordList.add(5);
ordList.add(2);
List<Entity> resultList = query.getResultStream()
    .sort(Comparator.comparing(entity -> ordList.indexOf(entity.getId())))
    .collect(Collectors.toList();

How to use FIELD() function of mysql in JPA

2 Answers2