如何用Criteria Builder构建自定义Money类型的薪资范围查询？

问题根源 #

你的salary字段通过@Convert映射到数据库的单字符串列，Hibernate的Converter仅在Java对象与数据库列之间做转换，数据库层面仍然是字符串存储：

• 直接用between是对字符串做字典序比较（比如"1000 EUR"和"200 EUR"的字符串比较结果不符合数值逻辑）；
• salary属于Basic类型（单列映射），无法直接dereference访问其amount属性，所以会抛出Basic paths cannot be dereferenced异常。

以下是无需修改数据库Schema的可行解决方案：

方案1：利用数据库字符串函数拆分数值与货币 #

通过数据库内置函数拆分薪资字符串的数值和货币部分，转成数值后做范围比较，同时必须添加货币匹配条件（不同货币的薪资无比较意义）。

示例代码（以MySQL为例） #

Money minSalary = new Money(1000, "EUR");
Money maxSalary = new Money(30000, "EUR");

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Employee> query = cb.createQuery(Employee.class);
Root<Employee> root = query.from(Employee.class);

// 提取薪资数值部分并转为Decimal类型
Expression<Double> salaryAmount = cb.function(
    "CAST", Double.class,
    cb.function("SUBSTRING_INDEX", String.class,
        root.get(Employee_.salary), cb.literal(" "), cb.literal(1)
    ),
    cb.literal("DECIMAL(10,2)")
);

// 提取薪资货币部分
Expression<String> salaryCurrency = cb.function(
    "SUBSTRING_INDEX", String.class,
    root.get(Employee_.salary), cb.literal(" "), cb.literal(-1)
);

// 组合条件：货币匹配 + 数值在范围内
Predicate currencyMatch = cb.equal(salaryCurrency, minSalary.getCurrency());
Predicate amountBetween = cb.between(salaryAmount, minSalary.getAmount(), maxSalary.getAmount());

query.where(cb.and(currencyMatch, amountBetween));
List<Employee> result = entityManager.createQuery(query).getResultList();

适配不同数据库 #

• PostgreSQL：将SUBSTRING_INDEX替换为SPLIT_PART（比如SPLIT_PART(e.salary, ' ', 1)提取数值）
• Oracle：用SUBSTR和INSTR组合拆分字符串（比如SUBSTR(e.salary, 1, INSTR(e.salary, ' ')-1)提取数值）

方案2：使用@NamedQuery直接编写JPQL #

通过命名查询直接编写包含函数逻辑的JPQL，比Criteria Builder更简洁：

步骤1：在Employee实体上定义NamedQuery #

@Entity
@NamedQuery(
    name = "Employee.findBySalaryRange",
    query = "SELECT e FROM Employee e " +
            "WHERE SUBSTRING_INDEX(e.salary, ' ', -1) = :currency " +
            "AND CAST(SUBSTRING_INDEX(e.salary, ' ', 1) AS DECIMAL) BETWEEN :minAmount AND :maxAmount"
)
public class Employee {
    // 实体字段...
}

步骤2：调用查询 #

List<Employee> result = entityManager.createNamedQuery("Employee.findBySalaryRange", Employee.class)
    .setParameter("currency", "EUR")
    .setParameter("minAmount", 1000)
    .setParameter("maxAmount", 30000)
    .getResultList();

方案3：注册Hibernate自定义函数（跨数据库适配） #

如果需要适配多种数据库，可以注册自定义函数封装拆分逻辑，避免在查询中写数据库特定的SQL：

步骤1：定义函数贡献者 #

public class SalaryFunctionContributor implements MetadataBuilderContributor {
    @Override
    public void contribute(MetadataBuilder metadataBuilder) {
        // 注册提取薪资数值的函数
        metadataBuilder.applySqlFunction(
            "salary_amount",
            new SQLFunctionTemplate(StandardBasicTypes.DOUBLE,
                // MySQL实现，其他数据库替换为对应逻辑
                "CAST(SUBSTRING_INDEX(?1, ' ', 1) AS DECIMAL)"
            )
        );
        // 注册提取薪资货币的函数
        metadataBuilder.applySqlFunction(
            "salary_currency",
            new SQLFunctionTemplate(StandardBasicTypes.STRING,
                "SUBSTRING_INDEX(?1, ' ', -1)"
            )
        );
    }
}

步骤2：配置Hibernate使用该贡献者 #

在application.properties中添加：

spring.jpa.properties.hibernate.metadata_builder_contributor=com.yourpackage.SalaryFunctionContributor

步骤3：在Criteria中使用自定义函数 #

Expression<Double> salaryAmount = cb.function("salary_amount", Double.class, root.get(Employee_.salary));
Expression<String> salaryCurrency = cb.function("salary_currency", String.class, root.get(Employee_.salary));

Predicate currencyMatch = cb.equal(salaryCurrency, "EUR");
Predicate amountBetween = cb.between(salaryAmount, 1000d, 30000d);

query.where(cb.and(currencyMatch, amountBetween));

关键注意事项 #

1. 必须匹配货币：无论哪种方案，都要先过滤相同货币的记录，否则数值比较无意义；
2. 精度处理：转换数值时要注意数据库的精度设置，避免小数丢失；
3. 格式稳定性：确保数据库中薪资字符串格式严格统一（数值+空格+货币），否则函数拆分会失效。

内容的提问来源于stack exchange，提问作者denstran