Как правильно подключиться к двум ДБ?

Question

[Starvings]

Доброго дня. Пытаюсь сделать коннект к двум БД.
Проблема на этапе коннекта к MSSQL. При этом, если соединяться только с одной БД проблем нет.
Пробовал разные варианты изменения свойств, но воз и ныне там.

Подскажите, пожалуйста, что и как требуется исправить?

application.properties:

#db settings
spring.jpa.hibernate.ddl-auto=update
spring.jpa.generate-ddl=true
#general
#spring.jpa.properties.hibernate.globally_quoted_identifiers=true
#spring.jpa.show-sql=true

#words datasource (mysql)
spring.datasource.users.jdbc-url=jdbc:mysql://localhost:3306/dr_list
spring.datasource.users.username=fff
spring.datasource.users.password=rrr
spring.datasource.users.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.users.database-platform=org.hibernate.dialect.MySQL5Dialect
server.port = 8083

#contacts datasource (mssql)
spring.datasource.smp.url=jdbc:sqlserver://*****;databaseName=****;encrypt=true;trustServerCertificate=true;
spring.datasource.smp.username=ppp
spring.datasource.smp.password=zzz
spring.datasource.smp.hibernate.dialect= org.hibernate.dialect.SQLServerDialect
spring.datasource.smp.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.datasource.smp.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.datasource.smp.jpa.hibernate.dialect=org.hibernate.dialect.SQLServer2012Dialect
#spring.jpa.hibernate.ddl-auto= update
spring.datasource.smp.jpa.properties.hibernate.globally_quoted_identifiers=true
spring.datasource.smp.jpa.show-sql=true

Config MSSQL:

@Configuration
@EntityScan(basePackages = {"com.example.birthdays.DataBase.smp.Model"})
@EnableJpaRepositories(
        basePackages = {"com.example.birthdays.DataBase.smp.Repository"},
        entityManagerFactoryRef = "smpEntityManagerFactory",
        transactionManagerRef = "smpTransactionManager")
public class JpaSMPConfiguration {

    @Bean
    @Primary
    @ConfigurationProperties(prefix = "spring.datasource.smp")
    public DataSource smpDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean
    @Primary
    public LocalContainerEntityManagerFactoryBean smpEntityManagerFactory(
            EntityManagerFactoryBuilder builder,
            @Qualifier("smpDataSource") DataSource smpDataSource) {
        return builder.dataSource(smpDataSource).packages("com.example.birthdays.DataBase.smp")
                .persistenceUnit("smp").build();
    }

    @Bean
    @Primary
    public PlatformTransactionManager smpTransactionManager(
            @Qualifier("smpEntityManagerFactory") EntityManagerFactory smpEntityManagerFactory) {
        return new JpaTransactionManager(smpEntityManagerFactory);
    }
}

Config MySQL:

@Configuration
@EntityScan(basePackages = {"com.example.birthdays.DataBase.Users.Model"})
@EnableJpaRepositories(
        basePackages = {"com.example.birthdays.DataBase.Users.Repository"},
        entityManagerFactoryRef = "usersEntityManagerFactory",
        transactionManagerRef = "usersTransactionManager")
public class JpaUsersConfiguration {

    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.users")
    public DataSource usersDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean
    public LocalContainerEntityManagerFactoryBean usersEntityManagerFactory(
            EntityManagerFactoryBuilder builder,
            @Qualifier("usersDataSource") DataSource usersDataSource) {
        return builder.dataSource(usersDataSource).packages("com.example.birthdays.DataBase.Users")
                .persistenceUnit("Users").build();
    }

    @Bean
    public PlatformTransactionManager usersTransactionManager(
            @Qualifier("usersEntityManagerFactory") EntityManagerFactory usersEntityManagerFactory) {
        return new JpaTransactionManager(usersEntityManagerFactory);
    }
}

ошибка:

Error creating bean with name 'smpEntityManagerFactory' defined in class path resource [com/example/birthdays/configurationDBconnect/JpaSMPConfiguration.class]: Unable to create requested service [org.hibernate.engine.jdbc.env.spi.JdbcEnvironment] due to: Unable to determine Dialect without JDBC metadata (please set 'jakarta.persistence.jdbc.url' for common cases or 'hibernate.dialect' when a custom Dialect implementation must be provided)

Answer 1

[Орхан Гасанлы]

Добрый день.
Вот, как это делаю я:
1) yaml конфигурация

spring:
  datasource:
    db_1:
      url: jdbc:postgresql://localhost:5432/DB_NAME
      jdbc-url: jdbc:postgresql://localhost:5432/DB_NAME
      username: username
      password: password
      driver-class-name: org.postgresql.Driver
      jpa:
        show-sql: true
        open-in-view: false
        generate-ddl: true
        database-platform: org.hibernate.dialect.PostgreSQL10Dialect
        properties:
          hibernate:
            format_sql: true
            order_inserts: true
            order_updates: true
            jdbc:
              batch_size: 15
            globally_quoted_identifiers: true
            globally_quoted_identifiers_skip_column_definitions: true
            query:
              plan_cache_max_size: 4096
              #fail_on_pagination_over_collection_fetch: true
              in_clause_parameter_padding: true
        hibernate:
          ddl-auto: update
    db_2:
      url: jdbc:postgresql://localhost:5432/DB_NAME_2
      jdbc-url: jdbc:postgresql://localhost:5432/DB_NAME_2
      username: username
      password: password
      driver-class-name: org.postgresql.Driver
      jpa:
        show-sql: true
        open-in-view: false
        generate-ddl: true
        database-platform: org.hibernate.dialect.PostgreSQL10Dialect
        properties:
          hibernate:
            format_sql: true
            order_inserts: true
            order_updates: true
            jdbc:
              batch_size: 15
            globally_quoted_identifiers: true
            globally_quoted_identifiers_skip_column_definitions: true
            query:
              plan_cache_max_size: 4096
              #fail_on_pagination_over_collection_fetch: true
              in_clause_parameter_padding: true
        hibernate:
          ddl-auto: update
  jackson:
    serialization:
      FAIL_ON_EMPTY_BEANS: false

2) Java конфигурация для первой БД

@Configuration
@EnableTransactionManagement
@RequiredArgsConstructor
@EnableJpaRepositories(
        basePackages = {"com.example.db1.repository"},
        entityManagerFactoryRef = "db1EntityManagerFactory",
        transactionManagerRef = "db1EntityManagerFactory"

)
public class DB1JpaConfig {

    private final Environment env;

    @Bean
    @Primary
    @ConfigurationProperties("spring.datasource.db1")
    public DataSourceProperties db1DataSourceProperties() {
        return new DataSourceProperties();
    }


    @Bean(name = "db1DataSource")
    @Primary
    public DataSource db1DataSource() {
        return db1DataSourceProperties()
                .initializeDataSourceBuilder()
                .build();
    }

    @Bean(name = "db1EntityManagerFactory")
    @Primary
    public LocalContainerEntityManagerFactoryBean db1EntityManagerFactory(
            @Qualifier("db1DataSource") DataSource dataSource,
            EntityManagerFactoryBuilder builder) {
        HashMap<String, Object> properties = new HashMap<>();
        properties.putAll(hibernateProperties());
        properties.putAll(enversProperties());

        return builder
                .dataSource(dataSource)
                .properties(properties)
                .packages("com.example.db1.model")
                .build();
    }

    /**
     * Hibernate properties
     * @return Map of properties for Hibernate
     */
    private Map<String, Object> hibernateProperties() {
        HashMap<String, Object> properties = new HashMap<>();
        properties.put("hibernate.hbm2ddl.auto", env.getProperty("spring.datasource.db1.jpa.hibernate.ddl-auto"));
        properties.put("hibernate.dialect", env.getProperty("spring.datasource.db1.jpa.database-platform"));
        properties.put("hibernate.globally_quoted_identifiers", env.getProperty("spring.datasource.db1.jpa.properties.hibernate.globally_quoted_identifiers"));
        properties.put("hibernate.globally_quoted_identifiers_skip_column_definitions", env.getProperty("spring.datasource.db1.jpa.properties.hibernate.globally_quoted_identifiers_skip_column_definitions"));
        properties.put("hibernate.default_schema", env.getProperty("spring.datasource.db1.jpa.properties.hibernate.default_schema"));
        properties.put("hibernate.format_sql", env.getProperty("spring.datasource.db1.jpa.properties.hibernate.format_sql"));
        properties.put("hibernate.show_sql", env.getProperty("spring.datasource.db1.jpa.show-sql"));
        properties.put("hibernate.order_inserts", env.getProperty("spring.datasource.db1.jpa.properties.hibernate.order_inserts"));
        properties.put("hibernate.order_updates", env.getProperty("spring.datasource.db1.jpa.properties.hibernate.order_updates"));
        properties.put("hibernate.jdbc.batch_size", env.getProperty("spring.datasource.db1.jpa.properties.hibernate.jdbc.batch_size"));
        properties.put("spring.jpa.open-in-view", env.getProperty("spring.datasource.db1.jpa.open-in-view"));
        properties.put("spring.jpa.generate-ddl", env.getProperty("spring.datasource.db1.jpa.generate-ddl"));
        return properties;
    }



    @Bean(name = "db1EntityManagerFactory")
    @Primary
    public PlatformTransactionManager db1EntityManagerFactory(
            @Qualifier("db1EntityManagerFactory") LocalContainerEntityManagerFactoryBean db1EntityManagerFactory) {
        return new JpaTransactionManager(Objects.requireNonNull(db1EntityManagerFactory.getObject()));
    }
}

Java конфигурация для 2-й БД (аналогична первому практически)

@Configuration
@EnableTransactionManagement
@RequiredArgsConstructor
@EnableJpaRepositories(
        basePackages = {"com.example.db2.repository"},
        entityManagerFactoryRef = "db2EntityManagerFactory",
        transactionManagerRef = "db2EntityManagerFactory"

)
public class DB2JpaConfig {

    private final Environment env;

    @Bean
    @Primary
    @ConfigurationProperties("spring.datasource.db2")
    public DataSourceProperties db2DataSourceProperties() {
        return new DataSourceProperties();
    }


    @Bean(name = "db2DataSource")
    @Primary
    public DataSource db2DataSource() {
        return db2DataSourceProperties()
                .initializeDataSourceBuilder()
                .build();
    }

    @Bean(name = "db2EntityManagerFactory")
    @Primary
    public LocalContainerEntityManagerFactoryBean db2EntityManagerFactory(
            @Qualifier("db2DataSource") DataSource dataSource,
            EntityManagerFactoryBuilder builder) {
        HashMap<String, Object> properties = new HashMap<>();
        properties.putAll(hibernateProperties());
        properties.putAll(enversProperties());

        return builder
                .dataSource(dataSource)
                .properties(properties)
                .packages("com.example.db2.model")
                .build();
    }

    /**
     * Hibernate properties
     * @return Map of properties for Hibernate
     */
    private Map<String, Object> hibernateProperties() {
        HashMap<String, Object> properties = new HashMap<>();
        properties.put("hibernate.hbm2ddl.auto", env.getProperty("spring.datasource.db2.jpa.hibernate.ddl-auto"));
        properties.put("hibernate.dialect", env.getProperty("spring.datasource.db2.jpa.database-platform"));
        properties.put("hibernate.globally_quoted_identifiers", env.getProperty("spring.datasource.db2.jpa.properties.hibernate.globally_quoted_identifiers"));
        properties.put("hibernate.globally_quoted_identifiers_skip_column_definitions", env.getProperty("spring.datasource.db2.jpa.properties.hibernate.globally_quoted_identifiers_skip_column_definitions"));
        properties.put("hibernate.default_schema", env.getProperty("spring.datasource.db2.jpa.properties.hibernate.default_schema"));
        properties.put("hibernate.format_sql", env.getProperty("spring.datasource.db2.jpa.properties.hibernate.format_sql"));
        properties.put("hibernate.show_sql", env.getProperty("spring.datasource.db2.jpa.show-sql"));
        properties.put("hibernate.order_inserts", env.getProperty("spring.datasource.db2.jpa.properties.hibernate.order_inserts"));
        properties.put("hibernate.order_updates", env.getProperty("spring.datasource.db2.jpa.properties.hibernate.order_updates"));
        properties.put("hibernate.jdbc.batch_size", env.getProperty("spring.datasource.db2.jpa.properties.hibernate.jdbc.batch_size"));
        properties.put("spring.jpa.open-in-view", env.getProperty("spring.datasource.db2.jpa.open-in-view"));
        properties.put("spring.jpa.generate-ddl", env.getProperty("spring.datasource.db2.jpa.generate-ddl"));
        return properties;
    }



    @Bean(name = "db2EntityManagerFactory")
    @Primary
    public PlatformTransactionManager db2EntityManagerFactory(
            @Qualifier("db2EntityManagerFactory") LocalContainerEntityManagerFactoryBean db2EntityManagerFactory) {
        return new JpaTransactionManager(Objects.requireNonNull(db2EntityManagerFactory.getObject()));
    }
}

Когда делаете запрос к БД используйте эту аннотацию:

import org.springframework.transaction.annotation.Transactional;

Для первой БД (например, на уровне сервиса):

@Transactional(transactionManager = "db1TransactionManager")

или для второй БД

@Transactional(transactionManager = "db2TransactionManager")