I have configured Spring JPA to work with multiple data sources following this example. Spring JPA – Multiple Databases

It works as expected but since I want to use different data sources but with the same repositories there is an issue. When I try to use "Declarative Transaction Management" on my services and specify which transaction I am going to use the primary transaction or secondary one the transaction annotation is ignoring it. So in this case it is using the second one. However, both of the beans "PlatformTransactionManager" are created but when it comes to using in "@Transactional" I am not able to make the transaction work with the bean I have specified. So it seems that @Transactional is ignoring the bean name since they have the same repositories. Is there any way how can I use declarative transactions as I am trying to do it? As I have seen it can be done with Programmatic Transaction Management but it will cost me to change the whole code on my services since I have been using only declarative transactions.

CodePudding user response：

In our case, we also have two databases, both using JPA and repositories. However, they do use different repositories, but I think our approach should also work in your case. We define an EntityManagerFactory and a TransactionManager per database. I cannot confirm this works, but I believe it should.

Primary Datasource:

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        basePackages = {"ch.sac.data.primary.repository"},
        entityManagerFactoryRef = "entityManagerFactory",
        transactionManagerRef = "transactionManager")
public class ImporterPrimaryDataSourceConfiguration {

    @Primary
    @Bean(name = "dataSourceProperties")
    @ConfigurationProperties("spring.datasource")
    public DataSourceProperties dataSourceProperties() {
        return new DataSourceProperties();
    }

    @Primary
    @Bean(name = "primaryDataSource")
    public DataSource dataSource(final DataSourceProperties dataSourceProperties) {
        return dataSourceProperties
                .initializeDataSourceBuilder()
                .type(HikariDataSource.class)
                .build();
    }

    @Primary
    @Bean
    public Flyway primaryFlyway(
            final DataSource dataSource,
            @Value("${spring.flyway.locations}") final String[] flywayLocations,
            @Value("${spring.flyway.validate-on-migrate:true}")
                    final boolean flywayValidateOnMigrate) {
        final var flyway =
                Flyway.configure()
                        .dataSource(dataSource)
                        .locations(flywayLocations)
                        .validateOnMigrate(flywayValidateOnMigrate)
                        .load();
        flyway.migrate();
        return flyway;
    }

    @Primary
    @Bean
    public LocalContainerEntityManagerFactoryBean entityManagerFactory(
            final EntityManagerFactoryBuilder entityManagerFactoryBuilder,
            final DataSource dataSource) {

        final var jpaProperties =
                Map.ofEntries(
                        Map.entry(
                                "hibernate.dialect",
                                "org.hibernate.spatial.dialect.postgis.PostgisDialect"));

        return entityManagerFactoryBuilder
                .dataSource(dataSource)
                .packages("ch.sac.model.primary.entity")
                .persistenceUnit("dataSource")
                .properties(jpaProperties)
                .build();
    }

    @Primary
    @Bean
    public PlatformTransactionManager transactionManager(
            final EntityManagerFactory entityManagerFactory) {
        return new JpaTransactionManager(entityManagerFactory);
    }
}

Secondary Datasource (SQLite Database, created in-memory, adjust to your use-case):

@Configuration
@EnableJpaRepositories(
        basePackages = {"ch.sac.data.sqlite.repository"},
        entityManagerFactoryRef = "sqliteEntityManagerFactory",
        transactionManagerRef = "sqliteTransactionManager")
public class ImporterSQLiteDataSourceConfiguration {

    @Bean(name = "sqliteDBFilePath")
    public String sqliteDBFilePath() throws IOException {
        return TempFileManager.createEmptyTempFile("metadata.sqlite").toString();
    }

    @Bean(name = "sqliteDataSourceProperties")
    public DataSourceProperties sqliteDataSourceProperties() {
        return new DataSourceProperties();
    }

    @Bean(name = "sqliteDataSource")
    public DataSource sqliteDataSource(
            @Qualifier("sqliteDataSourceProperties")
                    final DataSourceProperties sqliteDataSourceProperties,
            final String sqliteDBFilePath) {
        return sqliteDataSourceProperties
                .initializeDataSourceBuilder()
                .driverClassName("org.sqlite.JDBC")
                .url("jdbc:sqlite:"   sqliteDBFilePath)
                .type(HikariDataSource.class)
                .build();
    }

    @Bean
    public Flyway sqliteFlyway(
            @Qualifier("sqliteDataSource") final DataSource sqliteDataSource,
            @Value("${sqlite.flyway.locations}") final String[] flywayLocations) {
        final var flyway =
                Flyway.configure().dataSource(sqliteDataSource).locations(flywayLocations).load();
        flyway.migrate();
        return flyway;
    }

    @Bean(name = "sqliteEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean sqliteEntityManagerFactory(
            final EntityManagerFactoryBuilder sqliteEntityManagerFactoryBuilder,
            @Qualifier("sqliteDataSource") final DataSource sqliteDataSource) {

        final var sqliteJpaProperties =
                Map.ofEntries(
                        Map.entry(
                                "hibernate.dialect", "org.sqlite.hibernate.dialect.SQLiteDialect"));

        return sqliteEntityManagerFactoryBuilder
                .dataSource(sqliteDataSource)
                .packages("ch.sac.model.sqlite.entity")
                .persistenceUnit("sqliteDataSource")
                .properties(sqliteJpaProperties)
                .build();
    }

    @Bean(name = "sqliteTransactionManager")
    public PlatformTransactionManager sqliteTransactionManager(
            @Qualifier("sqliteEntityManagerFactory")
                    final EntityManagerFactory sqliteEntityManagerFactory) {
        return new JpaTransactionManager(sqliteEntityManagerFactory);
    }
}