How to dynamically set the JPA/Hibernate dialect when using AbstractRoutingDataSource with different database types?

I'm using AbstractRoutingDataSource in a Spring Boot (Data JPA) application to access different databases (Mariadb, SQL Server) depending of a request parameter.

public class DataSourceRouter extends AbstractRoutingDataSource {

    public DataSourceRouter(Map<Object, Object> targetDataSources, DataSource defautDataSource) {
        setTargetDataSources(targetDataSources);
        setDefaultTargetDataSource(defautDataSource);
    }

    @Override
    protected Object determineCurrentLookupKey() {
        return SelectedDataSourceContextHolder.getSelectedDataSource();
    }

}

This datasource (routingDatasource) is configured into the LocalContainerEntityManagerFactoryBean.

    @Bean
    public LocalContainerEntityManagerFactoryBean entityManagerFactory(
            final EntityManagerFactoryBuilder builder) {
        return buildEntityManagerFactory(builder, routingDatasource());
    }

    protected LocalContainerEntityManagerFactoryBean buildEntityManagerFactory(final EntityManagerFactoryBuilder builder, DataSource dataSource) {
        return builder.dataSource(dataSource)
                .packages(SomeEntity.class.getPackageName()).properties(
                        Map.of("hibernate.dialect_resolvers", "xxx.DynamicDialectResolver")
                )
                .build();
    }

The problem with this approach is that we can only configure a single hibernate dialect, which should be dynamic depending on the underlying selected datasource. In order to do so I tried setting up a hibernate.dialect_resolvers:

public class DynamicDialectResolver implements DialectResolver {
    @Override
    public Dialect resolveDialect(DialectResolutionInfo info) {
        Dialect dialect;
        switch (info.getDatabaseName()) {
            case "MariaDB":
                dialect = new org.hibernate.dialect.MySQL5Dialect();
                break;
            case "SQL Server":
                dialect = new org.hibernate.dialect.SQLServer2012Dialect();
                break;
            default:
                dialect = null;
                break;
        }
        return dialect;
    }
}

However this resolver is only invoked once, so the selected dialect with be fixed into the one it selects that first time.

Is there a way to set the dialect dynamically at runtime?

That's not possible. Hibernate generates SQL on bootstrap that is specific to the dialect which is then cached. If you need to support multiple databases, you will need multiple session factories or persistence units.