Search code examples
javamysqlsqljdbcmulti-database

JDBC:is it possible to execute multi-database Querys in java?

I want to execute an sql-query over 2 databases using java

but have some problems finding out how to do it without writing everything by myself

maybe someone has an idea how to do it.

example:

database1
table1(names): id,Name,zip,something

database2
table2(towns): id,townname,zip

SELECT * 
FROM database1.names, database2.towns
WHERE database1.names.zip = database2.towns.zip

the example works in mysql when i use phpMyAdmin and the User has rights on both databases

edit:

The question is: How do i get Java to execute such a query since i can only connect to one database(?) or: How can I connect to 2 Databases executing an Sql Query that uses tables from both databases using java.

the way i execute sql commands in java looks like:

Connection c = DriverManager.getConnection("jdbc:mysql://localhost/database?user=root&password=");
PreparedStatement pstmt = c.prepareStatement("Select * from something");
pstmt.executeQuery();

but i cant use that to get a Sql Query that uses tables from 2 databases?

Solution

  • Assuming that these databases are not visible from the same datasource, you have to use a mediation software to query on them, such as http://www.unityjdbc.com/doc/multiple/multiplequery.php.

    It's not a trivial problem, since your "SQL" will have to deal with each datasource availability and transaction.

    Some DB vendors provide some sort of dblinks (e.g. http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_5005.htm) that help you a little to deal with heterogeneous DBs.

    So it would be nice if you narrow your question to what DBMSs you are interested.