双数据源下Hibernate原生查询无法识别Schema的问题

问题场景 #

单数据源配置时，原生SQL查询可正常执行；新增读写分离双数据源后，Hibernate原生查询报错找不到表my_table，但显式指定Schema（如my_schema.my_table）后查询正常，JPQL/Criteria等非原生查询不受影响。

错误栈核心信息：

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [UPDATE my_table SET ...]; nested exception is org.postgresql.util.PSQLException: ERROR: relation "my_table" does not exist

核心原因 #

1. 路由数据源的子数据源未正确加载或传递默认Schema配置，导致连接的默认搜索路径未包含目标Schema
2. hibernate.prepare_connection=false配置导致Hibernate不会主动为连接设置默认Schema
3. 直接使用JdbcTemplate的组件（如ShedLock）未继承Hibernate的Schema上下文，执行SQL时无默认Schema

解决方案 #

1. 显式为HikariDataSource设置默认Schema #

修改数据源配置类，在构建HikariDataSource时直接指定Schema，确保每个子数据源的连接都默认使用目标Schema：

@Configuration
public class DataSourceConfig {
    // ... 原有DataSourceProperties Bean
    
    @Bean
    @ConfigurationProperties("spring.read-only.datasource.configuration")
    public DataSource readOnlyDataSource(DataSourceProperties readOnlyDataSourceProperties) {
        HikariDataSource dataSource = readOnlyDataSourceProperties.initializeDataSourceBuilder()
                .type(HikariDataSource.class)
                .build();
        dataSource.setSchema("my_schema"); // 强制设置默认Schema
        return dataSource;
    }

    @Bean
    @ConfigurationProperties("spring.read-write.datasource.configuration")
    public DataSource readWriteDataSource(DataSourceProperties readWriteDataSourceProperties) {
        HikariDataSource dataSource = readWriteDataSourceProperties.initializeDataSourceBuilder()
                .type(HikariDataSource.class)
                .build();
        dataSource.setSchema("my_schema"); // 强制设置默认Schema
        return dataSource;
    }
    
    // ... 原有路由数据源和BeanPostProcessor代码
}

2. 调整Hibernate连接准备配置（可选） #

如果希望Hibernate自动为连接设置Schema，可将hibernate.prepare_connection改为true，让Hibernate在获取连接时注入默认Schema：

spring:
  jpa:
    properties:
      hibernate.prepare_connection: true

注意：若连接池已配置Schema，需避免重复设置导致冲突，建议先测试验证。

3. 为第三方JdbcTemplate组件指定Schema #

针对像ShedLock这类直接使用JdbcTemplate的组件，可直接在配置中指定带Schema的表名：

shedlock:
  jdbc:
    table-name: my_schema.shedlock

或者全局配置JdbcTemplate的默认搜索路径：

@Bean
public JdbcTemplate jdbcTemplate(DataSource dataSource) {
    JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
    jdbcTemplate.execute("SET search_path TO my_schema");
    return jdbcTemplate;
}

4. 确保事务切面优先级 #

调整事务切面的执行优先级，保证在JdbcTemplate执行前已设置正确的数据源路由：

@Aspect
@Component
@Slf4j
@Order(1) // 设置更高优先级，确保先于其他切面执行
public class TransactionAspect {
    @Before("@annotation(transactional) && execution(* *(..))")
    public void setTransactionType(Transactional transactional) {
        if (transactional.readOnly()) {
            DataSourceTypeContextHolder.setTransactionType(DataSourceType.READ_ONLY);
        } else {
            DataSourceTypeContextHolder.setTransactionType(DataSourceType.READ_WRITE);
        }
    }

    @AfterReturning("@annotation(transactional) && execution(* *(..))")
    public void clearTransactionType(Transactional transactional) {
        DataSourceTypeContextHolder.clearTransactionType();
    }
}

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