I'm trying to query on tables which are in oracle and postgres. here I have used two getconnection methods but while I'm trying to do some join operations it is giving me an error. This error is because of using or querying on a single resultset which has particular(either postgres or oracle) database connection. can we pass two database connections in a single getConnection() method?
note :- Written in scala
JDBC essentially works by sending a query to a database server and presenting the response of the server as a ResultSet to the developer. In other words it does not execute the query but hands it off the database which executes it. Hence JDBC can not magically execute a single query that combines tables from different database servers.
Some databases support linking multiple servers together. In that case you would have to configure the databases to know about each other, and then connect to one of them and send it a query that references the linked server in the correct format (which is different for every vendor). However not all vendors support linked servers, and even less support linking to servers of other vendors.
Another option is using something like spark which has its own query engine. Spark can use JDBC to download the data from both servers to your machine or spark cluster and then execute the query locally.
