Hibernate 6生成含IN子句的错误SQL适配Firebird 4问题求助

问题背景 #

在Hibernate 6与Firebird 4环境中，使用标准JPA Criteria API编写包含IN子句的查询时，会触发Firebird报错：

SQL Error [335544652] [21000]: multiple rows in singleton select

原因是Hibernate生成的SQL带有双层括号：

select * from rdb$relations where rdb$relation_id in ((select rdb$relation_id from rdb$relations))

Firebird会将内层的双层括号子查询识别为标量子查询（要求最多返回一行结果），但实际子查询返回多行，因此触发错误。正确的SQL应该是单层括号形式。

根因分析 #

Hibernate 6重构了IN谓词的实现：将原Hibernate 5的InPredicate拆分为InListPredicate（处理IN列表）和InSubQueryPredicate（处理IN子查询）。但负责JPA Criteria API的SqmCriteriaNodeBuilder类在处理子查询时，错误地使用了InListPredicate，导致生成双层括号的SQL。

解决方案（保留标准JPA API） #

1. 升级Hibernate版本到修复后的版本 #

这类数据库兼容性问题通常会被Hibernate团队快速修复，建议检查Hibernate的官方Issue Tracker，确认是否有针对Firebird IN子查询双层括号的修复补丁。如果有，直接升级到对应版本（比如6.1.0.Final或更高）即可解决问题。

2. 自定义Firebird SQL AST转换器 #

通过Hibernate的SPI扩展，重写IN子句的SQL生成逻辑，将双层括号替换为单层。步骤如下：

步骤1：自定义SQL转换器

public class CustomFirebirdSqlAstTranslator extends FirebirdSqlAstTranslator {
    public CustomFirebirdSqlAstTranslator(SessionFactoryImplementor sessionFactory, Statement statement) {
        super(sessionFactory, statement);
    }

    @Override
    protected void visitInListPredicate(InListPredicate predicate) {
        // 检测是否是单个子查询作为IN的参数
        if (predicate.getValues().size() == 1 && predicate.getValues().get(0) instanceof SqmSubQuery<?>) {
            // 按照InSubQueryPredicate的逻辑生成单层括号
            appendSql("in (");
            visit(predicate.getValues().get(0));
            appendSql(")");
        } else {
            // 正常处理普通IN列表
            super.visitInListPredicate(predicate);
        }
    }
}

步骤2：实现转换器工厂

public class CustomFirebirdSqlAstTranslatorFactory extends FirebirdSqlAstTranslatorFactory {
    @Override
    public <T extends Statement> SqlAstTranslator<T> buildTranslator(SessionFactoryImplementor sessionFactory, T statement) {
        return new CustomFirebirdSqlAstTranslator(sessionFactory, statement);
    }
}

步骤3：配置Hibernate使用自定义转换器

在hibernate.properties中添加：

hibernate.sql.ast.translator.factory=com.yourpackage.CustomFirebirdSqlAstTranslatorFactory

或者在Spring Boot等框架的配置类中通过HibernatePropertiesCustomizer配置。

3. 用EXISTS子查询替代IN（纯JPA API） #

如果暂时无法升级或自定义转换器，可以用JPA标准API支持的EXISTS子查询替代IN，绕开括号问题：

原IN查询代码（报错）

CriteriaQuery<RdbRelations> query = cb.createQuery(RdbRelations.class);
Root<RdbRelations> root = query.from(RdbRelations.class);

// 构建IN子查询
Subquery<Integer> subquery = query.subquery(Integer.class);
Root<RdbRelations> subRoot = subquery.from(RdbRelations.class);
subquery.select(subRoot.get("rdbRelationId"));

Predicate inPredicate = cb.in(root.get("rdbRelationId")).value(subquery);
query.where(inPredicate);

修改为EXISTS查询（正常工作）

CriteriaQuery<RdbRelations> query = cb.createQuery(RdbRelations.class);
Root<RdbRelations> root = query.from(RdbRelations.class);

// 构建EXISTS子查询
Subquery<RdbRelations> subquery = query.subquery(RdbRelations.class);
Root<RdbRelations> subRoot = subquery.from(RdbRelations.class);
subquery.select(subRoot);

Predicate existsPredicate = cb.exists(subquery.where(
    cb.equal(root.get("rdbRelationId"), subRoot.get("rdbRelationId"))
));
query.where(existsPredicate);

该方案生成的SQL无括号问题，且完全符合JPA标准API规范。

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