1

I need to write a JAVA code to retrieve data as a ResultSet from multiple connections by using one SQL statement.

For example:

SELECT *
FROM       CONNECTION1.DB1.TABLE1 T1
INNER JOIN CONNECTION2.DB2.TABLE2 T2 ON T1.FIELD1 = T2.FIELD2

Is it possible? and how?

Thanks!

Ofir
  • 43
  • 5
  • 1
    So here connection1 and connection2 are separate JDBC connections? Unless the databases can talk to each other and cooperate on the query (e.g. SQL Server can) then no I think you'll have to perform the join yourself. – Rup Aug 09 '18 at 13:23
  • Yes, supperated JDBC connections. I need to build it in Java without being dependent on servers abilities. – Ofir Aug 09 '18 at 13:31
  • @Ofir Without any what? Why are these tables reached through separate connections, could you make the tables possibly exist together within a single database? Like @Rup says, you'd need to select separately from both tables and simulate the "join" in the application, *or* modify things on the database side so that the tables are both accessible in one connection (let the databases talk to each other). A `Connection` is just a way to send commands to a database and if you have two separate connections then they can't really work together. – xtratic Aug 09 '18 at 13:34
  • You can't open two `java.sql.Connection` and send a query using the other one. You can see, based on the database used, the communication layer that can be added, so only one connection will be needed in java, the link will be done in the database. Or you can simply do the join yourself in Java (two selects). But this is not the most efficient solution ! – AxelH Aug 09 '18 at 13:43
  • @Ofir If you are using SQL server and can modify the config on the db then you could maybe do something like [this](https://stackoverflow.com/questions/1144051/selecting-data-from-two-different-servers-in-sql-server) Cross db communication is likely only configurable on the db level. – GurpusMaximus Aug 09 '18 at 13:44
  • I already know about this solution. I can move the cross-connection part to a single database but I need a generic functionality with hundreds of databases. So, the only way to do it is by using a database server that supports it and declares other connections on it? – Ofir Aug 09 '18 at 13:47

1 Answers1

1

It may possible to configure in database level, i thik that no way to do this with two JDBC connections. For example in MySQL you can set up federated tables:

The FEDERATED Storage Engine | FEDERATED Storage Engine Notes and Tips

qmessagebox
  • 26
  • 3