0

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?

Leo
  • 6,480
  • 4
  • 37
  • 52
DavidB
  • 73
  • 1
  • 9
  • What exactly is your question? You say it "works in MySQL", so are you using a different DBMS? If yes, which one. If no, then what is the question. And what do you mean with "*without writing everything by myself*"? –  Feb 23 '14 at 13:33
  • I added some more infos. – DavidB Feb 23 '14 at 13:56

1 Answers1

0

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.

Leo
  • 6,480
  • 4
  • 37
  • 52
  • I write an "Sql-Client" that can execute sql-query to any Database that u can find an jdbc driver for so even i dont know which DBMSs that will be.. but i want to start smal and take 2 databases from mysql and make it work with them – DavidB Feb 23 '14 at 14:04
  • then there are two paths here. If your tool is intended to be used in different dbs than mysql, you'll have to use some mediation tool. If you're going to stick with mysql, then you can research what mysql can do in this situation (probably you'll be able to access the same mysql instance databases with the same datasource if you give mysql user permissions to do so). – Leo Feb 23 '14 at 14:08
  • since i dont want to stick with mysql i will have to use something like unityjdbc. Now i only have to find something what is free for private use... or use the trail version.. – DavidB Feb 23 '14 at 14:31
  • unityjdbc guys are so confident in their product that they even give us some directions :-) http://www.unityjdbc.com/doc/multiple/multiplequery.php – Leo Feb 23 '14 at 16:10
  • if you find one, you can also answer this question :-) http://stackoverflow.com/questions/15895148/open-source-alternative-to-unityjdbc-com – Leo Feb 23 '14 at 16:11