I am implementing a backend with two database connections. One to a MS Access DB and another one to a MySQL DB. I've setup two configuration classes and the application is running without any error message, but does not create the tables for my entities in mysql db.

Project structure:

my.backend
  |
  --> configuration
  --> controller
  --> exceptions
  --> model
  | |
  | --> mysql
  | --> msaccess
  |
  --> repo
  | |
  | --> mysql
  | --> msaccess
  |
  --> service

My configuration files are looking like this: MySQL Configuration:

imports [...]

@Configuration
@EnableJpaRepositories(
        entityManagerFactoryRef = "mysqlEntityManagerFactory",
        transactionManagerRef = "mysqlTransactionManager",
        basePackages = "my.backend.repo.mysql"
)
public class MySqlJpaConfig {
    @Autowired
    private Environment env;

    @Bean
    @Primary
    @ConfigurationProperties(prefix = "mysql")
    public LocalContainerEntityManagerFactoryBean mysqlEntityManagerFactory() {
        LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
        em.setDataSource(mysqlDataSource());
        em.setPackagesToScan("my.backend.model.mysql");
        em.setPersistenceUnitName("mysql");
        em.setJpaVendorAdapter(new HibernateJpaVendorAdapter());

        HashMap<String, Object> jpaProperties = new HashMap<>();
        jpaProperties.put("hibernate.ddl-auto",
                env.getProperty("mysql.jpa.hibernate.ddl-auto"));
        jpaProperties.put("hibernate.dialect",
                env.getProperty("mysql.jpa.properties.hibernate.dialect"));
        em.setJpaPropertyMap(jpaProperties);

        return em;
    }

    @Primary
    @Bean
    public DataSource mysqlDataSource() {

        DriverManagerDataSource dataSource
                = new DriverManagerDataSource();
        dataSource.setUrl(env.getProperty("mysql.url"));
        dataSource.setUsername(env.getProperty("mysql.username"));
        dataSource.setPassword(env.getProperty("mysql.password"));
        dataSource.setDriverClassName(Objects.requireNonNull(env.getProperty("mysql.driver-class-name")));

        return dataSource;
    }

    @Primary
    @Bean
    public PlatformTransactionManager mysqlTransactionManager() {

        JpaTransactionManager transactionManager
                = new JpaTransactionManager();
        transactionManager.setEntityManagerFactory(
                mysqlEntityManagerFactory().getObject());
        return transactionManager;
    }
}

MS Access Configuration:

@Configuration
@EnableJpaRepositories(
        entityManagerFactoryRef = "msaccessEntityManagerFactory",
        transactionManagerRef = "msaccessTransactionManager",
        basePackages = "my.backend.repo.msaccess"
)
@EnableTransactionManagement
public class MsAccessJpaConfig {
    @Autowired
    private Environment env;

    @Bean
    @ConfigurationProperties(prefix = "mysql")
    public LocalContainerEntityManagerFactoryBean msaccessEntityManagerFactory() {
        LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
        em.setDataSource(msaccessDataSource());
        em.setPackagesToScan("my.backend.model.msaccess");
        em.setPersistenceUnitName("msaccess");
        //em.setPersistenceProvider(new HibernatePersistenceProvider());
        em.setJpaVendorAdapter(new HibernateJpaVendorAdapter());

        HashMap<String, Object> jpaProperties = new HashMap<>();
        jpaProperties.put("hibernate.dialect",
                env.getProperty("msaccess.jpa.properties.hibernate.dialect"));
        em.setJpaPropertyMap(jpaProperties);
        return em;
    }

    @Bean
    public DataSource msaccessDataSource() {

        DriverManagerDataSource dataSource
                = new DriverManagerDataSource();
        dataSource.setUrl(env.getProperty("msaccess.url"));
        dataSource.setDriverClassName(Objects.requireNonNull(env.getProperty("msaccess.driver-class-name")));

        return dataSource;
    }

    @Bean
    public PlatformTransactionManager msaccessTransactionManager() {

        JpaTransactionManager transactionManager
                = new JpaTransactionManager();
        transactionManager.setEntityManagerFactory(
                msaccessEntityManagerFactory().getObject());
        return transactionManager;
    }

}

And my application.properties file:

spring.jpa.show-sql=true
spring.jpa.open-in-view=false
# MySQL config
mysql.url=jdbc:mysql://localhost:3306/my_database
mysql.username=
mysql.password=
mysql.driver-class-name=com.mysql.cj.jdbc.Driver
mysql.jpa.hibernate.ddl-auto=update
mysql.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL8Dialect
# MS Access config
msaccess.url=jdbc:ucanaccess://c:/users/username/documents/test.accdb
msaccess.driver-class-name=net.ucanaccess.jdbc.UcanaccessDriver
msaccess.jpa.properties.hibernate.dialect=org.hibernate.dialect.SQLServerDialect

When I run my application without the custom configuration and only one vanilla database connection (only MySQL) everything works fine. Also if I run this and perform a get request I get a Error that the table does not exist (obviously), so the connection to MySQL is there but the application just does not create the tables I need. Btw MS Access is working the same way, but I do not want a table generation there.

Am I missing some configuration for the connection?

Thanks for your help! Cheers, niklas

CodePudding user response：

I think you have missed some annotations, I suggest you to visit this link Spring JPA – Multiple Databases. Please check the annotation like @PropertySource({ "classpath:persistence-multiple-db.properties" }). You find the above link helpful.

CodePudding user response：

My guess is that you have to use hibernate.hbm2ddl.auto instead of hibernate.ddl-auto in your jpa properties configuration, since that is the name hibernate looks for