Feature request: Add support for multiple and polyglot data sources by extending @EnableJdbcRepositories annotation with all refs

[djechelon]

Related: #994, #544
Related: https://stackoverflow.com/questions/74603720/spring-data-jdbc-with-multiple-data-sources-and-multiple-dialects

This feature request addresses an issue with regards to running Spring Data JDBC in a multi-data-source environment. I will take some time provide a MRE in a POC repository, let's start discussion first. Please also note I am not allowed to copy&paste anything from my customer's VDI. I will be happy to rewrite the code in a POC soon.

My request is to extend the EnableJdbcRepositories with attributes for setting all collaborators of JdbcRepositoryFactoryBean, so I can declare them as beans and reference them directly in the annotation to ensure that different data sources use different dialects, converters, mapping contexts, etc.

Context

We are porting an old monolythic application to Spring Boot 3.x (currently 3.1, 3.2 is blocked by this).
This old application has 3 data sources and is being upgraded to support H2 for JUnit tests.

• "Main" database: PROD is Oracle Exadata
• "Masterdata" database: PROD is Oracle 19c, and is like a lookup database
• "DB2 data": PROD is a DB2 on AS400 to my knowledge

As of today (28th February 2024) we have converted the old application from Mybatis and hardcoded queries to Spring Data Relational JDBC using Repositories and Entities (actually, @Table-annotated POJOs), and all tests were executed in H2 environment where all data sources point to the same in-memory DB.

During upgrade to Spring Boot 3.2, I have discovered that it is not easy to tell RepositoryFactoryBean which dialect to use

Arrangement of data layer

I have designed the data layer in my project to have three different packages (in order to invoke the Repository on the correct corresponding DataSource)

• com.example.myapp.data (and .myapp.data_mdb, and .myapp.data_db2)
  • configuration
  • entity
  • enums
  • repository
  • dto
  • manager

Forget about manager, enum and dto for a while. It's pretty self-explanatory.

In all of the configuration packages, I have declared a configuration class that declares the following beans:

@Configuration
@EnableAutoConfiguration(exclude = {DataSourceAutoConfiguration.class, JdbcRepositoriesAutoConfiguration.class}) //I will configure all myself
@EnableJdbcRepositories(value = "...", transactionManagerRef = "[one of tx managers]", jdbcOperationsRef = "[one of them]", repositoryFactoryBeanClass = MyCustomRepositoryFactoryBean.class)
public MyDataConfiguration {

    @Bean
    public DataSource dataSource(){ ... } //I will comment about bean name later, please note that I omitted a "conditional" configuration for EmbeddedDataSource which is not relevant
    /*
    I will describe other beans in the next part
   */
   
}

Each Configuration beans declares the following beans. I have engineered the three configuration classes (DataConfiguration, MdbConfiguration, Db2Configuration) to create beans of different names. And of course this means we have three beans of type DataSource with different name, which is desirable.

• DataSource
• PlatformTransactionManager, depending on dataSource
• NamedParameterJdbcOperations, depending on dataSource
• JdbcTemplate, depending on dataSource
• Dialect (!!!!), depending on dataSource
• JdbcConverter, depending on jdbcOperations and dialect
• JdbcMappingContext

Note that in the Spring 3.1 version of the application, I didn't have to declare the repositoryFactoryBeanClass, we'll be there soon.

My expectation

My expectation for the entire project is that when I make a query to, say, com.example.myapplication.data.repository.CatRepository#findByName I run an Oracle-dialectized query (e.g. WHERE ROWNUM clause). When instead I run a query from com.example.myapplication.data_db2.repository.DogRepository#findByName the query is customized for DB2 (e.g. LIMIT clause)

That is not going to work, IMO

My finding

During upgrade to Spring Boot 3.2, I discovered that repositories could not be created because Dialect bean is not unique!

I have then followed the indications from the linked SO post as a porkaround but in my large project (maybe not in a small POC) I am not able to retrieve the ApplicationContext from the singleton.
I am not sharing the code for my repository factory bean classes because I mostly followed the SO post implementation

While I could work for fixing the porkaround, here are my...

Conclusions

As noted in the linked posts, it would be easier from the consumer side to declare all collaborator bean names in the annotation instead of using dirty workarounds that makes code less robust and harder to understand.

Please have a read about my findings, and if a POC is really required I'll be working on it. At least I can provide a more generic example with containers, SQL Server, Oracle and Postgres

[mp911de]

The infrastructure behind @EnableJdbcRepositories currently losely couples a lot of components. The ideal scenario is adding a jdbcAggregateTemplateRef reference to JdbcAggregateTemplate instead of having a multitude of individual beans. For this to work, we first need to migrate the repository query execution into JdbcAggregateTemplate. Once that has happened, everything else falls into place. There's no need to configure a converter or DataSource afterward.

With #994 and #1610 we are well aware of these requirements. I would like to close this ticket as duplicate. Please let me know whether there's something missing in the other tickets so that we don't lose any further context.