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.

Centerpiece

Firstly that is invalid JPQL, there is no "replace" function in JPQL (or Criteria). Secondly, show what you have tried so far, and what SPECIFIC part you have a problem with – Runnerup

Yes i have searched there not replace inside criteria builder. but our requirement is to sort this version column. As version column is varchar it gives string sorting. so when using jpa we use orderList.add(cb.desc(uf.get("version")) it sorts based on string and lists "11.0.2.73" above "11.0.2.213". to solve this we need to remove "." from versions cast it into number and then sort. Query given in the description is working...on actual postgress database we need to find any way or other way to implement this in JPQL or critera builder in java. Please suggest – Centerpiece

Same this i have explained in #52054594 – Centerpiece

So this is a DUPLICATE of that question. Please do not raise dup questions – Runnerup

Will remove one. But can you suggest if there is way to handle this in criteria builder. – Centerpiece

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.

Heteroplasty

Hey John Thanks for your response it worked at my end. By the time i saw your reply i could figure out replace function. But i was not able to figure out how to implement lpad function. Thanks again for your valuable reply. – Centerpiece

Hi John, you rightly mentioned that CAST is not available. But using lpad was really very good suggestion Thanks – Centerpiece

Hi John we are creating this to compare versions which are in the format of "11.2.2000.3" or "11.2.2000.111" in this case lpad with total 10 is working fine. But it will fail when comparing "11.2.2000.3" and this "4.200.10.10", as ultimately it will string compare 11220003 and 42001010. It will consider "4.200.10.10" bigger. so is there any function or way criteria builder provides to convert this to integer after replacing "." Please suggest – Centerpiece

Even if there is a way to convert string to integer in the Criteria API, converting "11220003" and "42001010" to integer, "4.200.10.10" would be bigger too. If I understand your orderBy version correctly, you should compare them parts by parts starting from the first part. For your example above, the first part "11" is bigger than "4", the version starting with "11" is bigger. In the Criteria API, there are locate() and substring(), but it can be complex to extract 4 parts. Oracle has regexp_substr(). MySQL has substring_index(). You may need to create a function for your database. – Heteroplasty

MySQL substring_index() is not exactly extracting a part from a string. But it can serve your purpose with lpad. – Heteroplasty

Hi John, when i am trying to use Expression<String> majorVer = cb.function("SUBSTRING_INDEX", String.class, uf.get(sortAttr), cb.literal("."), cb.literal(1)); I am getting error as "nested exception is org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet] with root cause org.postgresql.util.PSQLException: ERROR: function substring_index(text) does not exist" – Centerpiece

I think SUBSTRING_INDEX is not provided by criteriabuilder – Centerpiece

substring_index is for MySQL. You need to use the corresponding function for your database. – Heteroplasty

Answer 2

I don't know which JPA version introduced this but I'm in Hibernate 6/JPA 3.1.0 and the criteria builder has toX() methods that result in a SQL cast.

cq.orderBy(cb.desc(cb.toInteger(root.get("version"))))

Should result in this (for SQL Server):

order by cast(version as int) desc

Additional pieces like the the function call for replace could be added as well.

Copywriter