如何在Java StringBuilder中使用Oracle REGEXP_SUBSTR构建动态SQL并解决HQL报错

我在Java代码中用StringBuilder构建动态查询，需要在IN子句里追加包含REGEXP_SUBSTR表达式的SELECT子查询，代码如下：

StringBuilder criteria = new StringBuilder();

criteria.append(" IN (SELECT REGEXP_SUBSTR((select tableA.mdn_list from tableA where tableA.id = '1'), '[^,]+', 1, level)  FROM dual CONNECT BY REGEXP_SUBSTR((select tableA.mdn_list from tableA where tableA.id = '1'), '[^,]+', 1, level) IS NOT NULL))");

运行后终端报错：

Caused by: org.hibernate.hql.ast.QuerySyntaxException: unexpected token: BY near line 3, column 19 .....

请问如何在代码中正确添加REGEXP_SUBSTR？

关联问题内容：如何将逗号分隔的字符串值作为项列表添加到SQL的IN子句中（使用子查询）？

报错原因 #

你使用的是HQL（Hibernate查询语言），但HQL不支持Oracle原生的CONNECT BY语法，这是导致语法解析错误的核心原因。REGEXP_SUBSTR本身是Oracle的函数，但HQL在解析时无法识别原生SQL的特定扩展语法。

方法1：改用原生SQL查询 #

直接使用Hibernate的原生SQL查询API，完全兼容Oracle的语法规则：

// 构建完整的原生SQL语句
StringBuilder sql = new StringBuilder();
sql.append("SELECT t.* FROM your_target_table t ");
sql.append("WHERE t.mdn IN (");
sql.append("    SELECT REGEXP_SUBSTR((SELECT mdn_list FROM tableA WHERE id = '1'), '[^,]+', 1, level) ");
sql.append("    FROM dual ");
sql.append("    CONNECT BY REGEXP_SUBSTR((SELECT mdn_list FROM tableA WHERE id = '1'), '[^,]+', 1, level) IS NOT NULL");
sql.append(")");

// 执行原生SQL查询，如需映射实体类可调用addEntity(YourEntity.class)
SQLQuery query = session.createSQLQuery(sql.toString());
List<?> results = query.list();

方法2：先拆分字符串再传入HQL的IN子句 #

如果必须使用HQL，可以先通过原生SQL拆分逗号分隔的字符串为列表，再将列表作为参数传入HQL：

// 第一步：用原生SQL拆分MDN列表
String splitSql = "SELECT REGEXP_SUBSTR((SELECT mdn_list FROM tableA WHERE id = '1'), '[^,]+', 1, level) " +
                  "FROM dual " +
                  "CONNECT BY REGEXP_SUBSTR((SELECT mdn_list FROM tableA WHERE id = '1'), '[^,]+', 1, level) IS NOT NULL";
SQLQuery splitQuery = session.createSQLQuery(splitSql);
List<String> mdnList = splitQuery.list();

// 第二步：构建HQL并传入列表参数
String hql = "FROM YourTargetEntity WHERE mdn IN (:mdnList)";
Query hqlQuery = session.createQuery(hql);
hqlQuery.setParameterList("mdnList", mdnList);
List<YourTargetEntity> results = hqlQuery.list();

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