JPA 标准构建器:如何按顺序替换字符串并将其转换为数字? [英] JPA criteria builder: how to replace and cast a string to numeric in order-by?

问题描述

有人可以建议我如何使用 JPA Criteria builder API 构建以下查询吗?

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;

请注意我们的版本栏有11.0.2.213"、11.0.2.73"这样的值我们需要修剪字符 '.'在版本中，然后将它们转换为数字，然后按 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.

推荐答案

目前 JPA 没有用于 replace() 和 cast(string as numeric) 的 API.但是，如果数据库可移植性不重要，您可以使用 CriteriaBuilder.function(...) 创建数据库本机函数.

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.

对于 MySQL，您的示例的 order-by 表达式为:

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(...) 不支持诸如 cast(value as type) 或 convert(value, type) 之类的原生函数.所以使用 lpad(...) 来实现相同的 orderBy 结果.

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.

它适用于 Cmobilecom JPA，这是一种适用于 Java 和 Android 的轻量级 JPA 实现.

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

免责声明:我是 Cmobilecom JPA 的开发人员.

Disclaimer: I am a developer of Cmobilecom JPA.

这篇关于JPA 标准构建器:如何按顺序替换字符串并将其转换为数字?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！