Hibernate 6升级后DB2 z/OS查询参数CAST问题求助

AI 小北

2026-6-26

Hibernate 6升级后DB2 z/OS 12移除CAST语句的方法

代码与场景说明

Repository代码

@Repository
public interface FooRepository extends JpaRepository<Foo, Long>, JpaSpecificationExecutor<Foo> {

  @Query(
  """
      SELECT o FROM Foo o 
      JOIN Fee e on o.feeId = e.id 
      JOIN Fii i on e.fiiId = i.id 
      WHERE i.id = :fiiId 
      AND o.id IN (
        SELECT u.id 
        FROM Fuu u  
        JOIN Foo o2 on u.fooId = o2.id 
        WHERE u.something LIKE CONCAT('%', :param1, '_', :param2, '%')
      )""")
  List<OptionRule> findByFiiIdAndFuuLikeSomeComputed(
     Long fiiId, String param1, String param2); // 已将char改为String修复类型转换错误
}

字段定义

u.something字段的实体定义：

@Nullable
@Column(name = "SOMETHING", length = 25000)
private String something;

Hibernate版本差异

升级前（旧版本）

生成的SQL无CAST语句：

select foo0_.ID                 as id1_18_,
       ...
from FOO foo0_
         inner join FEE fee1_ on (foo0_.FEE_ID = fee1_.ID)
         inner join FII fii2_ on (fee1_.FII_ID = fii2_.ID)
where fii2_.ID = ?
and (foo0_.ID in (select foo4_.ID
       from FUU fuu3_
                inner join FOO foo4_
                           on (fuu3_.FOO_ID = fuu4_.ID)
       where foo3.SOMETHING like '%' || ? || '_' || ? || '%'))

升级后（Hibernate 6）

初始错误："Could not convert 'java.lang.Character' to 'java.lang.String' using 'org.hibernate.type.descriptor.java.StringJavaType' to wrap"，已通过将参数类型从char改为String修复。
新问题：生成的SQL包含CAST语句，但DB2 z/OS 12不支持该语法：

select fo1_0.ID,
       ...
from FOO fo1_0
     join FEE fe1_0 on fo1_0.FEE_ID = fe1_0.ID
     join FII fi1_0 on fe1_0.FII_ID = fi1_0.ID
where fi1_0.ID = ?
and fo1_0.ID in (
    select 
      fo2_0.ID
    from FUU fu1_0
      join FOO fo2_0 on fu1_0.FOO_ID = fo2_0.ID
    where fu1_0.SOMETHING like cast('%' || cast(? as varchar(25000)) || '_' || cast(? as varchar(25000)) || '%' as varchar(32672)))

当前尝试的自定义Dialect（未解决CAST问题）

LegacyDb2Dialect.java

import org.hibernate.dialect.DB2zDialect;
import org.hibernate.dialect.pagination.LegacyDB2LimitHandler;
import org.hibernate.dialect.pagination.LimitHandler;
import org.hibernate.engine.spi.SessionFactoryImplementor;
import org.hibernate.sql.ast.SqlAstTranslator;
import org.hibernate.sql.ast.SqlAstTranslatorFactory;
import org.hibernate.sql.ast.spi.StandardSqlAstTranslatorFactory;
import org.hibernate.sql.ast.tree.Statement;
import org.hibernate.sql.exec.spi.JdbcOperation;
import org.hibernate.tool.schema.extract.spi.SequenceInformationExtractor;

public class LegacyDb2Dialect extends DB2zDialect {
  
  @Override
  public LimitHandler getLimitHandler() {
    return LegacyDB2LimitHandler.INSTANCE;
  }

  @Override
  public SqlAstTranslatorFactory getSqlAstTranslatorFactory() {
    return new StandardSqlAstTranslatorFactory() {
      @Override
      protected <T extends JdbcOperation> SqlAstTranslator<T> buildTranslator(
          SessionFactoryImplementor sessionFactory, Statement statement) {
        return new LegacyDb2SqlAstTranslator<>(sessionFactory, statement, getVersion());
      }
    };
  }
}

LegacyDb2SqlAstTranslator.java

import org.hibernate.dialect.DB2zSqlAstTranslator;
import org.hibernate.dialect.DatabaseVersion;
import org.hibernate.engine.spi.SessionFactoryImplementor;
import org.hibernate.sql.ast.tree.Statement;
import org.hibernate.sql.exec.spi.JdbcOperation;

public class LegacyDb2SqlAstTranslator<T extends JdbcOperation> extends DB2zSqlAstTranslator<T> {

  public LegacyDb2SqlAstTranslator(
      SessionFactoryImplementor sessionFactory, Statement statement, DatabaseVersion version) {
    super(sessionFactory, statement, version);
  }

  @Override
  protected boolean supportsOffsetClause() {
    return false;
  }
}

补充发现

使用Specification/CriteriaBuilder处理bigint类型字段时，同样会生成CAST语句。

解决方案

方法1：重写Dialect中的字符串拼接函数逻辑

Hibernate 6生成CAST是为了保证字符串拼接后的类型匹配，针对DB2 z/OS特性，可在自定义Dialect中重写CONCAT函数，避免自动生成CAST：

import org.hibernate.dialect.DB2zDialect;
import org.hibernate.dialect.function.StandardSQLFunctionTemplate;
import org.hibernate.type.StringType;
import org.hibernate.dialect.pagination.LegacyDB2LimitHandler;
import org.hibernate.dialect.pagination.LimitHandler;
import org.hibernate.engine.spi.SessionFactoryImplementor;
import org.hibernate.sql.ast.SqlAstTranslator;
import org.hibernate.sql.ast.SqlAstTranslatorFactory;
import org.hibernate.sql.ast.spi.StandardSqlAstTranslatorFactory;
import org.hibernate.sql.ast.tree.Statement;
import org.hibernate.sql.exec.spi.JdbcOperation;

public class LegacyDb2Dialect extends DB2zDialect {
    public LegacyDb2Dialect() {
        super();
        // 重写CONCAT函数，直接使用DB2的字符串拼接语法，指定返回类型为StringType
        registerFunction("concat", new StandardSQLFunctionTemplate(StringType.INSTANCE, "?1 || ?2 || ?3 || ?4 || ?5"));
    }

    @Override
    public LimitHandler getLimitHandler() {
        return LegacyDB2LimitHandler.INSTANCE;
    }

    @Override
    public SqlAstTranslatorFactory getSqlAstTranslatorFactory() {
        return new StandardSqlAstTranslatorFactory() {
            @Override
            protected <T extends JdbcOperation> SqlAstTranslator<T> buildTranslator(
                    SessionFactoryImplementor sessionFactory, Statement statement) {
                return new LegacyDb2SqlAstTranslator<>(sessionFactory, statement, getVersion());
            }
        };
    }
}

方法2：在SqlAstTranslator中跳过参数CAST生成

修改自定义的LegacyDb2SqlAstTranslator，重写CAST处理逻辑，当遇到字符串参数的CAST时直接输出参数：

import org.hibernate.dialect.DB2zSqlAstTranslator;
import org.hibernate.dialect.DatabaseVersion;
import org.hibernate.engine.spi.SessionFactoryImplementor;
import org.hibernate.sql.ast.tree.Statement;
import org.hibernate.sql.ast.tree.expression.Cast;
import org.hibernate.sql.ast.tree.expression.JdbcParameter;
import org.hibernate.sql.exec.spi.JdbcOperation;

public class LegacyDb2SqlAstTranslator<T extends JdbcOperation> extends DB2zSqlAstTranslator<T> {

    public LegacyDb2SqlAstTranslator(
            SessionFactoryImplementor sessionFactory, Statement statement, DatabaseVersion version) {
        super(sessionFactory, statement, version);
    }

    @Override
    protected boolean supportsOffsetClause() {
        return false;
    }

    @Override
    protected void visitCast(Cast cast) {
        // 仅当CAST目标为字符串类型且源是参数时，跳过CAST直接输出参数
        if (cast.getTargetJdbcMapping().getJavaTypeDescriptor().getJavaTypeClass() == String.class
                && cast.getExpression() instanceof JdbcParameter) {
            visitExpression(cast.getExpression());
        } else {
            super.visitCast(cast);
        }
    }
}

方法3：调整参数类型映射规则

在自定义Dialect中指定String类型参数的默认JDBC类型映射，让Hibernate直接使用对应类型无需CAST：

import org.hibernate.dialect.DB2zDialect;
import org.hibernate.type.descriptor.java.StringJavaType;
import org.hibernate.type.descriptor.jdbc.VarcharJdbcType;
import org.hibernate.dialect.pagination.LegacyDB2LimitHandler;
import org.hibernate.dialect.pagination.LimitHandler;
import org.hibernate.engine.spi.SessionFactoryImplementor;
import org.hibernate.sql.ast.SqlAstTranslator;
import org.hibernate.sql.ast.SqlAstTranslatorFactory;
import org.hibernate.sql.ast.spi.StandardSqlAstTranslatorFactory;
import org.hibernate.sql.ast.tree.Statement;
import org.hibernate.sql.exec.spi.JdbcOperation;

public class LegacyDb2Dialect extends DB2zDialect {
    public LegacyDb2Dialect() {
        super();
        // 绑定String类型参数时直接映射为VarcharJdbcType
        getTypeConfiguration().getJavaTypeDescriptorRegistry().addDescriptor(
                StringJavaType.INSTANCE,
                (javaTypeDescriptor, sessionFactory) -> VarcharJdbcType.INSTANCE
        );
    }

    @Override
    public LimitHandler getLimitHandler() {
        return LegacyDB2LimitHandler.INSTANCE;
    }

    @Override
    public SqlAstTranslatorFactory getSqlAstTranslatorFactory() {
        return new StandardSqlAstTranslatorFactory() {
            @Override
            protected <T extends JdbcOperation> SqlAstTranslator<T> buildTranslator(
                    SessionFactoryImplementor sessionFactory, Statement statement) {
                return new LegacyDb2SqlAstTranslator<>(sessionFactory, statement, getVersion());
            }
        };
    }
}

验证步骤

将Hibernate配置中的dialect替换为自定义的LegacyDb2Dialect
测试字符串拼接、bigint字段查询等场景，确认CAST语句已移除
验证查询结果与升级前一致，无类型匹配错误

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

Hibernate 6升级后DB2 z/OS查询参数CAST问题求助

代码与场景说明 #

Repository代码 #

字段定义 #

Hibernate版本差异 #

升级前（旧版本） #

升级后（Hibernate 6） #

当前尝试的自定义Dialect（未解决CAST问题） #

LegacyDb2Dialect.java #

LegacyDb2SqlAstTranslator.java #

补充发现 #

解决方案 #

方法1：重写Dialect中的字符串拼接函数逻辑 #

方法2：在SqlAstTranslator中跳过参数CAST生成 #

方法3：调整参数类型映射规则 #

验证步骤 #