CODEWITHSUNDEEP
javasql-serverspringhibernatejpa
g4l.rushhour
g4l.rushhour

Reputation: 111

Spring Boot + Hibernate not using correct dialect for multiple datasources

I am setting up a Spring Boot (2.1.6) (with Spring-Data-Jpa) backend that uses 2 datasources of different types - Microsoft SQLServer and MySql. It doesnt use the correct dialect, even though specified when configuring the Entity Manager.

At the startup, Hibernate tries to connect to the first datasource without any dialect, even though I specified one. After it can't connect, it tries to connect again to the same datasource - this time with the correct dialect.

For MySQL, the correct dialect is used right away.

MSSQL-Configuration:

@Configuration
@EnableJpaRepositories(
  entityManagerFactoryRef = "gameserverEntityManagerFactory",
  transactionManagerRef = "gameserverTransactionManager",
  basePackages = { "com.me.repository.mssql" }
)
public class GameserverDataSourceConfig {

  @Value("${gameserver.jpa.properties.hibernate.dialect}")
  private String hibernateDialect;
  ...

  @Bean(name = "gameserverDataSource")
  @ConfigurationProperties(prefix = "gameserver.datasource")
  public DataSource gameserverDataSource() {
    DriverManagerDataSource dataSource = new DriverManagerDataSource();
    dataSource.setUrl(url);
    dataSource.setUsername(username);
    dataSource.setPassword(password);
    dataSource.setDriverClassName(driverClassName);
    return dataSource;
  }

  @Bean(name = "gameserverEntityManagerFactory")
  public LocalContainerEntityManagerFactoryBean
  gameserverEntityManagerFactory(EntityManagerFactoryBuilder builder,
                                 @Qualifier("gameserverDataSource") DataSource dataSource) {

    HashMap<String, Object> props = new HashMap<>();
    props.put("hibernate.dialect", hibernateDialect);

    return builder
        .dataSource(dataSource)
        .properties(props)
        .packages("com.me.domain.mssql")
        .persistenceUnit("gameDB")
        .build();
  }
  ...

The MySQL config is analog to this.

application.properties:

#MSSQL
#also tried: jdbc:sqlserver://localhost:49170;databaseName=xyz
gameserver.datasource.url=jdbc:sqlserver://localhost\\xyz:49170
gameserver.datasource.username=root
gameserver.datasource.password=root
gameserver.datasource.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
gameserver.jpa.properties.hibernate.dialect=org.hibernate.dialect.SQLServerDialect

#MySQL
webserver.datasource.url=jdbc:mysql://localhost:3306/zyx?useSSL=false&serverTimezone=Europe/Berlin
webserver.datasource.username=root
webserver.datasource.password=root
webserver.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
webserver.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQLDialect
...

Log Output:

2019-07-15 12:48:06.692  INFO 7200 --- [           main] o.hibernate.jpa.internal.util.LogHelper  : HHH000204: Processing PersistenceUnitInfo [
        name: gameDB
        ...]
2019-07-15 12:48:06.850  INFO 7200 --- [           main] org.hibernate.Version                    : HHH000412: Hibernate Core {5.3.10.Final}
2019-07-15 12:48:06.858  INFO 7200 --- [           main] org.hibernate.cfg.Environment            : HHH000206: hibernate.properties not found
2019-07-15 12:48:07.190  INFO 7200 --- [           main] o.hibernate.annotations.common.Version   : HCANN000001: Hibernate Commons Annotations {5.0.4.Final}
2019-07-15 12:48:21.859  WARN 7200 --- [           main] o.h.e.j.e.i.JdbcEnvironmentInitiator     : HHH000342: Could not obtain connection to query metadata : The TCP/IP connection to the host localhost, port 49170 has failed. Error: "Connection refused: connect. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.".
2019-07-15 12:48:21.902  INFO 7200 --- [           main] org.hibernate.dialect.Dialect            : HHH000400: Using dialect: org.hibernate.dialect.SQLServerDialect
2019-07-15 12:48:21.957  INFO 7200 --- [           main] o.h.e.j.e.i.LobCreatorBuilderImpl        : HHH000422: Disabling contextual LOB creation as connection was null
2019-07-15 12:48:23.488  INFO 7200 --- [           main] j.LocalContainerEntityManagerFactoryBean : Initialized JPA EntityManagerFactory for persistence unit 'gameDB'
2019-07-15 12:48:37.749  INFO 7200 --- [           main] o.hibernate.jpa.internal.util.LogHelper  : HHH000204: Processing PersistenceUnitInfo [
        name: webDB
        ...]
2019-07-15 12:48:37.818  INFO 7200 --- [           main] org.hibernate.dialect.Dialect            : HHH000400: Using dialect: org.hibernate.dialect.MySQLDialect
2019-07-15 12:48:38.171  INFO 7200 --- [           main] j.LocalContainerEntityManagerFactoryBean : Initialized JPA EntityManagerFactory for persistence unit 'webDB'

Upvotes: 7

Views: 13695

Answers (2)

g4l.rushhour
g4l.rushhour

Reputation: 111

Figured out myself. I was using a different port than in my SQLServer-only .properties... Ouch.

Upvotes: 4

Qingfei Yuan
Qingfei Yuan

Reputation: 1212

If you have defined 2 DataSource bean in your context. You can use @Primary to specify which one you want in your application before you compile and deploy.

Upvotes: -2

Related Questions