使用AbstractRoutingDataSource时，如何针对不同数据库动态设置JPA/Hibernate方言？

问题根源 #

Hibernate的SessionFactory（由Spring的EntityManagerFactory封装）在初始化阶段会确定并缓存数据库方言，后续不会重新解析。你使用的DialectResolver仅在SessionFactory初始化时被调用一次，因此无法实现运行时动态切换方言。

推荐方案：多EntityManagerFactory + 路由机制 #

为不同数据库分别创建独立的EntityManagerFactory，每个工厂绑定对应数据库的方言，再通过自定义路由类根据当前数据源标识选择对应的工厂。这种方式完全符合Hibernate的设计原则，无兼容性风险。

1. 配置各数据库的EntityManagerFactory #

为MariaDB和SQL Server分别配置独立的实体管理器工厂，指定各自的方言：

@Bean
@Qualifier("mariaDbEntityManagerFactory")
public LocalContainerEntityManagerFactoryBean mariaDbEntityManagerFactory(EntityManagerFactoryBuilder builder) {
    return builder.dataSource(mariaDbDataSource())
            .packages(SomeEntity.class.getPackageName())
            .properties(Map.of("hibernate.dialect", org.hibernate.dialect.MySQL5Dialect.class.getName()))
            .persistenceUnit("mariaDbPU")
            .build();
}

@Bean
@Qualifier("sqlServerEntityManagerFactory")
public LocalContainerEntityManagerFactoryBean sqlServerEntityManagerFactory(EntityManagerFactoryBuilder builder) {
    return builder.dataSource(sqlServerDataSource())
            .packages(SomeEntity.class.getPackageName())
            .properties(Map.of("hibernate.dialect", org.hibernate.dialect.SQLServer2012Dialect.class.getName()))
            .persistenceUnit("sqlServerPU")
            .build();
}

2. 实现路由EntityManagerFactory #

自定义路由类，仿照AbstractRoutingDataSource的逻辑，根据ThreadLocal中的数据源标识选择对应的实体管理器工厂：

public class RoutingEntityManagerFactory extends AbstractEntityManagerFactoryBean {

    private Map<Object, Object> targetEntityManagerFactories;
    private Object defaultEntityManagerFactoryKey;

    @Override
    protected EntityManagerFactory createNativeEntityManagerFactory() {
        return new EntityManagerFactory() {
            @Override
            public EntityManager createEntityManager() {
                return getCurrentEntityManagerFactory().createEntityManager();
            }

            @Override
            public EntityManager createEntityManager(Map map) {
                return getCurrentEntityManagerFactory().createEntityManager(map);
            }

            @Override
            public EntityManager createEntityManager(SynchronizationType synchronizationType) {
                return getCurrentEntityManagerFactory().createEntityManager(synchronizationType);
            }

            @Override
            public EntityManager createEntityManager(SynchronizationType synchronizationType, Map map) {
                return getCurrentEntityManagerFactory().createEntityManager(synchronizationType, map);
            }

            @Override
            public CriteriaBuilder getCriteriaBuilder() {
                return getCurrentEntityManagerFactory().getCriteriaBuilder();
            }

            @Override
            public Metamodel getMetamodel() {
                return getCurrentEntityManagerFactory().getMetamodel();
            }

            @Override
            public boolean isOpen() {
                return getCurrentEntityManagerFactory().isOpen();
            }

            @Override
            public void close() {
                targetEntityManagerFactories.values().stream()
                        .map(EntityManagerFactory.class::cast)
                        .forEach(EntityManagerFactory::close);
            }

            @Override
            public Map<String, Object> getProperties() {
                return getCurrentEntityManagerFactory().getProperties();
            }

            @Override
            public Cache getCache() {
                return getCurrentEntityManagerFactory().getCache();
            }

            @Override
            public PersistenceUnitUtil getPersistenceUnitUtil() {
                return getCurrentEntityManagerFactory().getPersistenceUnitUtil();
            }

            @Override
            public void addNamedQuery(String name, Query query) {
                getCurrentEntityManagerFactory().addNamedQuery(name, query);
            }

            @Override
            public <T> T unwrap(Class<T> cls) {
                return getCurrentEntityManagerFactory().unwrap(cls);
            }

            @Override
            public <T> void addNamedEntityGraph(String graphName, EntityGraph<T> entityGraph) {
                getCurrentEntityManagerFactory().addNamedEntityGraph(graphName, entityGraph);
            }
        };
    }

    private EntityManagerFactory getCurrentEntityManagerFactory() {
        Object lookupKey = SelectedDataSourceContextHolder.getSelectedDataSource();
        EntityManagerFactory emf = (EntityManagerFactory) targetEntityManagerFactories.get(lookupKey);
        if (emf == null) {
            emf = (EntityManagerFactory) targetEntityManagerFactories.get(defaultEntityManagerFactoryKey);
        }
        Assert.notNull(emf, "No EntityManagerFactory found for lookup key: " + lookupKey);
        return emf;
    }

    public void setTargetEntityManagerFactories(Map<Object, Object> targetEntityManagerFactories) {
        this.targetEntityManagerFactories = targetEntityManagerFactories;
    }

    public void setDefaultEntityManagerFactoryKey(Object defaultEntityManagerFactoryKey) {
        this.defaultEntityManagerFactoryKey = defaultEntityManagerFactoryKey;
    }
}

3. 配置路由EntityManagerFactory #

将两个数据库的实体管理器工厂注入到路由类中，并设置默认工厂：

@Bean
public EntityManagerFactory entityManagerFactory(
        @Qualifier("mariaDbEntityManagerFactory") EntityManagerFactory mariaDbEmf,
        @Qualifier("sqlServerEntityManagerFactory") EntityManagerFactory sqlServerEmf) {
    RoutingEntityManagerFactory routingEmf = new RoutingEntityManagerFactory();
    Map<Object, Object> targetEmfs = new HashMap<>();
    targetEmfs.put("mariaDb", mariaDbEmf);
    targetEmfs.put("sqlServer", sqlServerEmf);
    routingEmf.setTargetEntityManagerFactories(targetEmfs);
    routingEmf.setDefaultEntityManagerFactoryKey("mariaDb");
    routingEmf.afterPropertiesSet();
    return routingEmf.getObject();
}

4. 配置事务管理器 #

使用JpaTransactionManager并绑定路由的实体管理器工厂，确保事务使用对应的数据源和方言：

@Bean
public PlatformTransactionManager transactionManager(EntityManagerFactory entityManagerFactory) {
    JpaTransactionManager transactionManager = new JpaTransactionManager();
    transactionManager.setEntityManagerFactory(entityManagerFactory);
    return transactionManager;
}

备选方案：ThreadLocal+自定义DialectResolver（不推荐生产环境） #

通过ThreadLocal传递当前数据源标识，让DialectResolver直接返回对应方言，但需要绕过Hibernate的方言缓存，存在兼容性和性能风险：

public class DynamicDialectResolver implements DialectResolver {
    @Override
    public Dialect resolveDialect(DialectResolutionInfo info) {
        String dataSourceKey = SelectedDataSourceContextHolder.getSelectedDataSource();
        switch (dataSourceKey) {
            case "mariaDb":
                return new org.hibernate.dialect.MySQL5Dialect();
            case "sqlServer":
                return new org.hibernate.dialect.SQLServer2012Dialect();
            default:
                return new org.hibernate.dialect.MySQL5Dialect();
        }
    }
}

注意事项 #

• 需要设置hibernate.dialect为null，强制Hibernate使用DialectResolver
• Hibernate仍可能缓存方言，需自定义SessionFactory强制每次创建Session时重新解析，这种方式在不同Hibernate版本中可能失效

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