0

My data consists of strings that can be numbers or strings ("12" should be seen as a number while "12REF" should be seen as a string).

I am looking to implement an order by in my criteriaBuilder that sorts the strings by numbers first, and puts the strings sorted alphabetically at the end.

Correctly sorted example: <[["1", "2", "10", "A", "AB", "B", "DUP", "LNE", "NUL"]]>

Currently my code looks like this (just sorting by asc, using the CriteriaQuery's orderBy).

.orderBy(QueryUtils.toOrders(
                            Sort.by(Sort.Direction.ASC, selection.getAlias()), root,
                            criteriaBuilder)
                            );

which results in: <[["1", "10", "2", "A", "AB", "B", "DUP", "LNE", "NUL"]]>

How can I implement a custom ordering here?

Edit: the data is stored in a MySQL database

huyhuyhuy
  • 63
  • 5
  • Which database? You will most likely need to use a database-specific function to determine whether a string is a number or not – crizzis Mar 30 '21 at 12:31
  • Oh apologies, that's MySQL – huyhuyhuy Mar 30 '21 at 12:58
  • Is the result set large? Do you use paging? If "no" for both then it will be much easier to sort results in memory after fetching. It seems to be pretty complicated doing it in Criteria API way. But anyway you should start with writing pure MySQL query that produces desired ordering and then try to translate it to Criteria API using `criteriaBuilder.function(...)` – Nikolai Shevchenko Mar 30 '21 at 14:25

0 Answers0