query across two different database table in spring JPA

Question

I have three tables. Table1 is in DB1 and both table2 and table3 is in DB2 as mentioned below:

Table1 in DB1: customerId, accountNumber

Table2 in DB2: customerId, accountNumber, id

Table3 in DB2: id, name, country

I want to get name and country from table3 based on customerId and accountNumber from table1. the tables are related as mentioned above. Tables are not having any mapping, just the columns are same.

I am using spring JPA and MySql.

Could anyone please help how to perform a join query on above 3 tables across database in spring JPA (in JPARepository class).

I want to have a join query like in repo class to run:

select distinct d2t3.id from DB1.Table1 d1t1, DB2.Table2 d2t2, DB2.Table3 d2t3
where d1t1.customerid=d2t2.customerid 
and d1t1.accountNumber=d2t2.accountNumber
and d2t2.id=d2t3.id;

Work through some basic tutorials. If you still have problems and a specific question we are eager to help. — Jens Schauder
@JensSchauder, Could you please provide me link for the tutorial, I tried to find but couldnt and hence posted question here... — Manglesh
Don't know if they are any good, but these came up on top when googling the relevant keywords: tutorialspoint.com/jpa tutorialspoint.com/jpa/jpa_jpql.htm spring.io/guides/gs/accessing-data-jpa — Jens Schauder
@JensSchauder, thanks for the URL. I had already gone through those URL but they are not related to joining table across database. I need to join three tables which are from across two database. — Manglesh
@Manglesh did you get any solution for your requirement ? If yes please let me know. Thanks — Krish

Krish Krish · Accepted Answer · 2018-08-11T09:52:12

You can configure the multiple data source configuration using the link :

https://medium.com/@joeclever/using-multiple-datasources-with-spring-boot-and-spring-data-6430b00c02e7

And then you can able to run your query as follows:

@Query(value = "select distinct d2t3 from DB1.Table1 d1t1, DB2.Table2 d2t2, 
DB2.Table3 d2t3 where d1t1.customerid=d2t2.customerid and 
d1t1.accountNumber=d2t2.accountNumber and d2t2.id=d2t3.id", nativeQuery = true)
List<Table3> retrieveByQuery();

query across two different database table in spring JPA

1 Answers