Hibernate 6.1.X拼接模式和名称不正确

Question

升级了Spring Boot 2.7。到Spring Boot 3.0.x。对应的Hibernate也从5.1.x升级到6.1.x。

我的Oracle数据库表是:

SELECT * FROM CUST_CENTER.PROD_INST@HUIJK;

对应的Java实体定义为:

@Entity
@Table(schema = "CUST_CENTER", name = "PROD_INST@HUIJK")
public class 产品实例实体 {
@Id
private Long prodInstId;
// ...
}

在升级前工作正常，但升级后我得到这样的错误:

2023-04-03 18:48:10 DEBUG [http-nio-4159-exec-1] org.hibernate.engine.jdbc.spi.SqlStatementLogger.logStatement(at line 128)   : select 产1_0.prod_inst_id,产1_0.acc_num,产1_0.acc_prod_inst_id,产1_0.accept_type_cod,产1_0.account,产1_0.acct_id,产1_0.act_date,产1_0.action_id,产1_0.address_desc,产1_0.address_id,产1_0.area_code,产1_0.basic_state,产1_0.begin_rent_date,产1_0.besp_date,产1_0.besp_symbol,产1_0.bill_date,产1_0.bill_xchg_id,产1_0.busi_mod_date,产1_0.common_region_id,产1_0.create_date,产1_0.create_org_id,产1_0.create_staff,产1_0.cust_id,产1_0.cust_order_id,产1_0.data_aoc_type,产1_0.dev_efp_date,产1_0.dev_exp_date,产1_0.develop_department,产1_0.developer,产1_0.distributor_id,产1_0.eff_date,产1_0.exch_id,产1_0.exp_date,产1_0.ext2_prod_inst_id,产1_0.ext_prod_inst_id,产1_0.ext_state,产1_0.first_finish_date,产1_0.grp_prod_nbr,产1_0.handler,产1_0.handler_latn_id,产1_0.if_ivpn,产1_0.install_addr,产1_0.install_date,产1_0.ip_flag,产1_0.ismp_aoc_type,产1_0.lan_id,产1_0.last_order_id,产1_0.last_order_item_id,产1_0.latn_id,产1_0.lte_flag,产1_0.offer_id,产1_0.owestate_date,产1_0.owner_cust_id,产1_0.pay_cust_id,产1_0.pay_latn_id,产1_0.payment_mode_cd,产1_0.physical_nbr,产1_0.point_owner_id,产1_0.prod_id,产1_0.prod_inst_desc,产1_0.prod_inst_name,产1_0.prod_inst_pwd,产1_0.prod_type_cd,产1_0.prod_use_type,产1_0.region_id,产1_0.remark,产1_0.sms_aoc_type,产1_0.status_cd,产1_0.status_date,产1_0.stop_rent_date,产1_0.update_date,产1_0.update_staff,产1_0.urban_flag,产1_0.urge_cd,产1_0.use_cust_id,产1_0.use_latn_id,产1_0.user_bill_id,产1_0.user_type_id,产1_0.voice_aoc_type from cust_center."prod_inst@huijk" 产1_0 where 产1_0.acc_num=? and 产1_0.status_cd not in(?,?,?)
2023-04-03 18:48:10  WARN [http-nio-4159-exec-1] org.hibernate.engine.jdbc.spi.SqlExceptionHelper.logExceptions(at line 133)  : SQL Error: 942, SQLState: 42000
2023-04-03 18:48:10 ERROR [http-nio-4159-exec-1] org.hibernate.engine.jdbc.spi.SqlExceptionHelper.logExceptions(at line 138)  : ORA-00942: 表或视图不存在
org.springframework.dao.InvalidDataAccessResourceUsageException: JDBC exception executing SQL [select 产1_0.prod_inst_id,产1_0.acc_num,产1_0.acc_prod_inst_id,产1_0.accept_type_cod,产1_0.account,产1_0.acct_id,产1_0.act_date,产1_0.action_id,产1_0.address_desc,产1_0.address_id,产1_0.area_code,产1_0.basic_state,产1_0.begin_rent_date,产1_0.besp_date,产1_0.besp_symbol,产1_0.bill_date,产1_0.bill_xchg_id,产1_0.busi_mod_date,产1_0.common_region_id,产1_0.create_date,产1_0.create_org_id,产1_0.create_staff,产1_0.cust_id,产1_0.cust_order_id,产1_0.data_aoc_type,产1_0.dev_efp_date,产1_0.dev_exp_date,产1_0.develop_department,产1_0.developer,产1_0.distributor_id,产1_0.eff_date,产1_0.exch_id,产1_0.exp_date,产1_0.ext2_prod_inst_id,产1_0.ext_prod_inst_id,产1_0.ext_state,产1_0.first_finish_date,产1_0.grp_prod_nbr,产1_0.handler,产1_0.handler_latn_id,产1_0.if_ivpn,产1_0.install_addr,产1_0.install_date,产1_0.ip_flag,产1_0.ismp_aoc_type,产1_0.lan_id,产1_0.last_order_id,产1_0.last_order_item_id,产1_0.latn_id,产1_0.lte_flag,产1_0.offer_id,产1_0.owestate_date,产1_0.owner_cust_id,产1_0.pay_cust_id,产1_0.pay_latn_id,产1_0.payment_mode_cd,产1_0.physical_nbr,产1_0.point_owner_id,产1_0.prod_id,产1_0.prod_inst_desc,产1_0.prod_inst_name,产1_0.prod_inst_pwd,产1_0.prod_type_cd,产1_0.prod_use_type,产1_0.region_id,产1_0.remark,产1_0.sms_aoc_type,产1_0.status_cd,产1_0.status_date,产1_0.stop_rent_date,产1_0.update_date,产1_0.update_staff,产1_0.urban_flag,产1_0.urge_cd,产1_0.use_cust_id,产1_0.use_latn_id,产1_0.user_bill_id,产1_0.user_type_id,产1_0.voice_aoc_type from cust_center."prod_inst@huijk" 产1_0 where 产1_0.acc_num=? and 产1_0.status_cd not in(?,?,?)]; SQL [n/a]
at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:256)
at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:232)
at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:550)
at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61)
at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:242)
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:152)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:134)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:218)
at jdk.proxy2/jdk.proxy2.$Proxy244.findByAccNumAndStatusCdNotIn(Unknown Source)

我手动执行Hibernate生成的SQL语句select 产1_0.prod_inst_id,产1_0.voice_aoc_type from cust_center."prod_inst@huijk" 产1_0，得到同样的错误，但删除引号后执行(select 产1_0.prod_inst_id,产1_0.voice_aoc_type from cust_center.prod_inst@huijk 产1_0)是正常的

我该如何解决这个问题?谢谢。

Answer 1

临时解决方案:让我先分享一下我的临时解决方案:

// I created custom implementation PhysicalNamingStrategy class
package com.example;
import org.hibernate.boot.model.naming.CamelCaseToUnderscoresNamingStrategy;
import org.hibernate.boot.model.naming.Identifier;
import org.hibernate.engine.jdbc.env.spi.JdbcEnvironment;
public class MyPhysicalNamingStrategy extends CamelCaseToUnderscoresNamingStrategy {
@Override
public Identifier toPhysicalTableName(Identifier logicalName, JdbcEnvironment jdbcEnvironment) {
// Here, I will always set the quoted property to false
return new Identifier(super.toPhysicalTableName(logicalName, jdbcEnvironment).getText(), false);
}
}

然后添加一行spring.jpa.hibernate.naming.physical-strategy: com.example.MyPhysicalNamingStrategy到application.yaml，告诉Hibernate使用自定义类进行物理命名。

注意:此解决方案仅推荐作为临时解决方案，当项目中确实出现特殊字符时，Hibernate的引号转义功能将失败(因为我们重新实现了物理表命名并始终将引号设置为false)

---

其他一些信息:

• Hibernate 6.0迁移指南
个人猜测:我比较了Hibernate5.x和6.x生成的SQL语句。5.x生成的SQL语句默认没有双引号(即表名部分为from cust_center.prod_inst@huijk)，而6.x生成的SQL语句默认使用双引号括住表名(即表名部分为from cust_center."prod_inst@huijk")，后者无法在我的Oracle数据库中执行。由于我使用DBLink, Hibernate可能会错误地认为我的表名(name = "PROD_INST@HUIJK")包含"@"特殊字符，因此它自动转义表名。在这种情况下，我无法通过将globally_quoted_identifiers属性设置为false来阻止它生成双引号。我不确定上述猜测是否正确。