Multiple datasources support [DATAJDBC-321]

[spring-projects-issues]

Marek Šabo opened DATAJDBC-321 and commented

As mentioned in the SO answer, spring-data-jdbc currently doesn't support multiple datasources. It would be great to add this support in future releases.

Immediate help for advanced spring users would be making JdbcRepositoryFactoryBean constructor public. That way we could provide our own dependencies based on the context of datasource.

Next step could extending the @EnableJdbcRepositories annotation with refs/links to dependencies similar way the @EnableJpaRepositories does.

(In our multi-ds JPA project we use similar configuration to one outlined in this article: https://medium.com/@joeclever/using-multiple-datasources-with-spring-boot-and-spring-data-6430b00c02e7)

---

Affects: 1.1 M1 (Moore), 1.0.4 (Lovelace SR4)

Reference URL: https://stackoverflow.com/a/50197148/303559

Issue Links:

• DATAJDBC-499 Register custom converters in two independent modules ("is duplicated by")
• DATAJDBC-622 Add support for transactionManagerRef in EnableJdbcRepository

14 votes, 15 watchers

[spring-projects-issues]

AndreyChukhlebov commented

Jens Schauder  Hello! I understand that the problem is not very common, but are there any improvements planned?)

[AndreyChukhlebov]

problem solution for postgress https://github.com/AndreyChukhlebov/spring-data-mongo-web crutch)

[archanamarathe]

@AndreyChukhlebov The solution worked perfectly! Thanks much!!!

[kota65535]

Thanks to @AndreyChukhlebov, I've created an example for multiple RDB (MySQL & PostgreSQL) repositories. https://github.com/kota65535/spring-data-jdbc-multi-repos

We can use jdbcOperationsRef, transactionManagerRef and dataAccessStrategyRef params of @EnableJdbcRepositories to specify different dependent beans for repositories of each datasource. But as for JdbcConverter, which is the dependency of JdbcRepositoryFactoryBean, currently we don't have an option to specify ref like above, and therefore we have to use the single JdbcConverter bean for each repository. Due to this limitation, we cannot use multiple SQL dialects (MySQL, PostgreSQL, Oracle, etc...) at the same time.

[SimSonic]

Hi all!

I'm implementing the same solutions from this thread

• https://github.com/AndreyChukhlebov/spring-data-mongo-web
• https://github.com/kota65535/spring-data-jdbc-multi-repos

but experiencing these problems with JdbcRepositoryFactoryBean:

Parameter 0 of method setConverter in org.springframework.data.jdbc.repository.support.JdbcRepositoryFactoryBean required a single bean, but 2 were found:
    - db1JdbcConverter: defined by method 'db1JdbcConverter' in class path resource [.../JdbcDb1Config.class]
    - db2JdbcConverter: defined by method 'db2JdbcConverter' in class path resource [.../JdbcDb2Config.class]

How do you solve this?

Spring Boot 2.5.2. Both datasources to Oracle.

[kota65535]

Hi @SimSonic, your problem seems to be related to this issue, and you should annotate @Primary to the one of the JdbcConverter bean to specify which one you use like this.

[SimSonic]

Hi @SimSonic, your problem seems to be related to this issue, and you should annotate @Primary to the one of the JdbcConverter bean to specify which one you use like this.

Thanks a lot. I've found both of my configurations were extending AbstractJdbcConfiguration. Removed that and marked some of beans @Primary and it started to work.

[holmofy]

I also encountered this problem. In which version is this problem solved?

[ghostGuiggs]

@kota65535 thanks for the repo

[isimonov]

Hi! Are there plans to implement full support for multiple data sources? This functionality is very lacking.

[wavesrcomn]

I'm trying to implement workaround solutions from @AndreyChukhlebov and @kota65535. But I don't use Spring Boot, only spring-context and spring-data-jdbc. How to implement this whithout Spring Boot?

@EnableAutoConfiguration(
    exclude = {DataSourceAutoConfiguration.class, JdbcRepositoriesAutoConfiguration.class}
)

My sample project is here and description to it is here #1289

[pauljonesnz]

To specify a JdbcConverter for a particular repository, is there any reason I shouldn't override the repositoryFactoryBeanClass by doing the following?

@EnableJdbcRepositories(
        repositoryFactoryBeanClass = MyRepositoryFactoryBean.class,
        basePackages = "com.my.pacakges.repository",
        transactionManagerRef = "myTransactionManager",
        jdbcOperationsRef = "myJdbcOperations",
        dataAccessStrategyRef = "myDataAccessStrategy"
)

public static class MyRepositoryFactoryBean<T extends Repository<S, ID>, S, ID extends Serializable>
            extends JdbcRepositoryFactoryBean<T, S, ID>  {

        // constructor...

        @Override
        public void setConverter(JdbcConverter notUsed) {
            JdbcConverter myConverter = MyConfig.getBean("myJdbcConverter", JdbcConverter.class);
            super.setConverter(myConverter);
        }
    }

I also use this same method to specify the dialect, as it seems to pick up the wrong dialect even though it is set correctly in the dataAccessStrategy.

This works well for me, but I'm just concerned about what the other consequences might be.

[jtkb]

Thanks to @AndreyChukhlebov, I've created an example for multiple RDB (MySQL & PostgreSQL) repositories. https://github.com/kota65535/spring-data-jdbc-multi-repos

We can use jdbcOperationsRef, transactionManagerRef and dataAccessStrategyRef params of @EnableJdbcRepositories to specify different dependent beans for repositories of each datasource. But as for JdbcConverter, which is the dependency of JdbcRepositoryFactoryBean, currently we don't have an option to specify ref like above, and therefore we have to use the single JdbcConverter bean for each repository. Due to this limitation, we cannot use multiple SQL dialects (MySQL, PostgreSQL, Oracle, etc...) at the same time.

@kota65535 Unfortunately your solution no longer works with with Spring-Boot 3.3.0 or 3.2.0. The application fails with:

Parameter 0 of constructor in com.kota65535.controller.UsersController required a single bean, but 2 were found:

• jdbcMappingContextDb1: defined by method 'jdbcMappingContextDb1' in class path resource [com/kota65535/config/Db1Config.class]
• jdbcMappingContextDb2: defined by method 'jdbcMappingContextDb2' in class path resource [com/kota65535/config/Db2Config.class]

[kota65535]

@jtkb Yes, my solution does not work from 3.2.0 as I mentioned in the issue https://github.com/spring-projects/spring-data-relational/issues/1650 😢 I've created a PR https://github.com/spring-projects/spring-data-relational/pull/1704 that enables us to configure multiple datasources another way, but it is not merged yet.