Retrieve information for the same DTO from two different databases

Question

I tried to make this as simple as possible with a short example.

We have two databases, one in MSSQLServer and other in Progress. We have the user DTO as it follows that we shown in a UI table within a web application.

User

int, id
String, name
String, accountNumber
String, street
String, city
String, country

Now this DTO(Entity) is not stored only in one database, some information (fields) for the same user are stored in one database and some in the other database.

MSsql

Table user
int, id
String, name
String, accountNumber


Table userModel
int, id
String, street
String, city
String, country

As you can see the key is the only piece that link two tables in both databases, as I said before they are not in the same database and not using same database vendor.

We have a requirement for sorting the UI table for each column. Obviously we need to create user dto with the information coming from both databases.

Our proposal at this moment is if user want to apply sorting using street field, we run a query in the Progress database and obtain a page (using pagination) using this resultset and go directly to the MSSQLServer User table with those keys and run another query to extract the missing information and save it to our DTO and transfer it to the UI. With implies run a query in one database then other query based on the returned keys in the second database.

The order of the database could change depending in which column(field) the user wants to apply sorting.

Technically we will create a jparepository that acts as a facade and depending on the field make the process in the correct database.

My question is: There is some kind of pattern that is commonly used in this scenarios, we are using spring, so probably spring have some out of the box features to support this requirement, will be great if this is possible using jparepositories (I have several doubts about it as we will use two different entitymanagers, one for each database).

Note: Move data from one database to another is not an option.

Answer 1

For this, you need to have separate DataSource/EntityManagerFactory/JpaRepository.

There is no out-of-the-box support for this architecture in the Spring framework, but you can easily hide the dual DataSource pair behind a Service layer. You can even configure JTA DataSources for ACID operations.