how to run native query by joining 2 tables from different database in Spring data jpa?

Question

I am using Spring boot 1.5.18.RELEASE.

I have followed multiple data sources using following link:

but its working when I have data sources in same server.

Here is my case:

1. datasource1 at one server: server1
2. datasource2 at another server: server2
3. Using native query I have to get the records by joining 2 data sources.

In above case, spring boot application is trying to check for table in server 1 only and as a result I am getting sql exception something like

SqlExceptionHelper - SQL Error: 1146, SQLState: 42S02

SqlExceptionHelper - Table 'datasource2.table2' doesn't exist

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 
'datasource2.table2' doesn't exist

How to resolve this issue ?

Answer 1

No, You can't do it. As each JPARepository belong to only on EntityManager and entity manager relate to a single database.

Answer 2

If your tables are of same server (irrespective of databases (same or differ)) then it is possible to join with eachother. But if they belong different servers then it supports by the MySql as FEDERATED Engine . You can have look to this question for more details.