如何用数据库无关的JPA查询从字符串列获取唯一数字列表？

现有环境说明 #

数据库表结构 #

ID     | DESCRIPTION
1      | Some value '123' added
2      | Deleted value '333' by 'Someone'

JPA实体类 #

@Entity
@Table(name = "LOG")
public class Log {

    @Id
    @Column(name = "ID")
    private Long id;

    @Column(name = "DESCRIPTION")
    private String description;
    // 省略getter、setter及其他方法
}

原有方案局限 #

原Oracle原生查询依赖Oracle专属的REGEXP_SUBSTR函数，无法适配MySQL、PostgreSQL等其他数据库：

@Query(value = "select distinct REGEXP_SUBSTR( log.DESCRIPTION, '''(\\d+)''',1,1,'',1) from Log log where 1=1 ", nativeQuery = true)
List<String> findObjectIdsInDescription();

数据库无关的纯JPA方案 #

方案1：Java端处理（最简洁跨库） #

先通过JPA查询所有description字段，再在Java代码中提取目标内容并去重，完全不依赖数据库函数：

1. 定义基础查询方法

@Query("select l.description from Log l")
List<String> findAllDescriptions();

2. 业务层处理逻辑

public List<String> getDistinctObjectIds() {
    List<String> descriptions = logRepository.findAllDescriptions();
    // 正则匹配单引号内的首个数字串
    Pattern pattern = Pattern.compile("'(\\d+)'");
    return descriptions.stream()
            .map(desc -> {
                Matcher matcher = pattern.matcher(desc);
                return matcher.find() ? matcher.group(1) : null;
            })
            .filter(Objects::nonNull)
            .distinct()
            .collect(Collectors.toList());
}

方案2：自定义JPA函数映射（适配大数据量场景） #

如果必须在数据库层完成处理，可以通过JPA的自定义函数映射，为不同数据库注册对应的实现逻辑：

1. 定义自定义函数接口

public interface StringFunctions {
    @Function(name = "extract_first_number_in_quotes", functionName = "EXTRACT_FIRST_NUMBER_IN_QUOTES")
    String extractFirstNumberInQuotes(String input);
}

2. 为不同数据库配置函数实现

• Oracle：在数据库中创建函数EXTRACT_FIRST_NUMBER_IN_QUOTES，内部调用REGEXP_SUBSTR实现逻辑
• MySQL：创建同名函数，使用REGEXP_SUBSTR（MySQL 8.0+支持）或自定义字符串截取逻辑
• PostgreSQL：创建同名函数，用substring配合正则表达式实现

3. JPA查询中使用自定义函数

@Query("select distinct function('extract_first_number_in_quotes', l.description) from Log l")
List<String> findObjectIdsInDescription();

这种方式需要为每个目标数据库配置对应的函数实现，但能在查询层完成数据处理，适合大数据量场景。

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