JDBC joins across two MySQL databases on the same server?

Question

Basically I need to run the following query through jdbc. Both databases are MySQL and on the same server.

SELECT * FROM DB1.ACCOUNT a
JOIN DB2.ITEM i ON a.AccountID = i.AccountID

My jdbc connection is set up like this:

Class.forName("com.mysql.jdbc.Driver").newInstance();
DB1 = DriverManager.getConnection("jdbc:mysql://serverloc.com:3300/DB1", "username", "password");
DB2 = DriverManager.getConnection("jdbc:mysql://serverloc.com:3300/DB2", "username", "password");

This is where I run into problems. I can now create a statement against DB1 or DB2, but I can't find a way to JOIN against both databases. I've tried running my query against one of the databases (below) but that returns null.

Statement statement = DB1.createStatement();
ResultSet resultSet = statement.executeQuery(" QUERY HERE ");

I've seen that you can use UnityJDBC to run JOIN queries across DB's, but I'm looking for a free/open source option.

Thanks!

paulsm4 · Accepted Answer · 2012-09-28 20:22:54Z

4

1) Yes, you can "join" between two different databases in mySQL.

2) No, you don't need two different connections to do it.

For example:

http://www.webhostingtalk.com/showthread.php?t=476982

p2.upc_code FROM db1.products p1 LEFT OUTER JOIN db2.products p2 ON p1.prod_id=p2.prod_id";

answered Sep 28, 2012 at 20:22

paulsm4

123k23 gold badges175 silver badges245 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

JDBC joins across two MySQL databases on the same server?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related