8

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 ?

Bohdan Petrenko
  • 997
  • 2
  • 17
  • 34
Krish
  • 1,804
  • 7
  • 37
  • 65
  • 1
    Unfortunately that native query is only able to run against one of the data sources. As described by that link, each `EntityManager` is created against a single data source - this is normal. To run a single native (SQL) query spanning database servers would need a database that allows external or remote tables to be defined. Others may know more, but I'm not aware of this feature in MySQL. A few others (e.g. Oracle and SQL Server) can do this. – df778899 Jul 07 '19 at 10:54
  • You can not do this as for each entity manager has own session. – Akash Jul 08 '19 at 13:45
  • If the tables are in two databases you will have to retrieve separately using two data sources and process them in your code in a way that gives the result of a SQL join. – Johna Jul 11 '19 at 04:03
  • 1
    Please provide us information about the database you're using. In case of SqlServer [tale a look](https://stackoverflow.com/questions/5145637/querying-data-by-joining-two-tables-in-two-database-on-different-servers) – Bohdan Petrenko Jul 11 '19 at 07:41
  • Maybe this can help you: https://stackoverflow.com/questions/44368186/query-across-two-different-database-table-in-spring-jpa – gigili Jul 11 '19 at 07:44

2 Answers2

0

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

Jayant
  • 1
0

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.

Ashutosh
  • 1
  • 2