JPA criteria builder: how to replace and cast a string to numeric in order-by?

Question

Can someone please suggest me how to build up the following query using JPA Criteria builder API?

SELECT id, name, date, version FROM public.upgradeTable
order by (CAST(replace(version, '.', '')AS numeric)) desc;

Please note our version column have values like "11.0.2.213", "11.0.2.73" We need to trim character '.' inside the version and then cast them as numeric and then sort by desc.

Answer 1

Currently JPA does not have APIs for replace() and cast(string as numeric). But you can use CriteriaBuilder.function(...) to create database native functions if database portability is not critical.

For MySQL, the order-by expression of your example would be:

    Expression<String> replacedValue = criteriaBuilder.function("replace", 
            String.class, root.get("version"), criteriaBuilder.literal("."), 
            criteriaBuilder.literal(""));

    Expression<String> lpadValue = criteriaBuilder.function("lpad", 
            String.class, replacedValue, criteriaBuilder.literal(20),
            criteriaBuilder.literal("0"));

    criteriaQuery.orderBy(criteriaBuilder.desc(lpadValue));

CriteriaBuilder.function(...) does not support such native functions as cast(value as type) or convert(value, type). So use lpad(...) to achieve the same orderBy results.

It works great with Cmobilecom JPA, a ligth-weight JPA implementation for both Java and Android.

Disclaimer: I am a developer of Cmobilecom JPA.