Reference two databases with Connection in java

Question

I have two local databases I'm trying to connect to using Java's Connection class. It's easy enough to connect to the first database using:

public Connection conn;
conn = DriverManager.getConnection(connectionString);

How can I add a second database to that same connection? They're both on the same server so it should be fairly simple but I can't find the right commands to do it.

Thanks

why do you want to connect two databases from the same connection? to replicate? — Low Flying Pelican, Oct 25 '11 at 07:44
say if you have table1 in db1 and table2 in db2, so you need to run queries joining table1 and table2? — Low Flying Pelican, Oct 25 '11 at 09:05
More like, I have table1 in db1 with columns id1 and id2, and table2 in db2 with columns id1 and id3, and I want to update id3 in table2 conditional on id2 having certain values in table 1 where t1.id2 = t2.id2 — Zain Rizvi, Oct 25 '11 at 09:33

score 9 · Accepted Answer · answered Oct 25 '11 at 07:44

9

A Connection is a session with a specific database. You can't use one Connection to communicate with two different databases; for that, you need two separate Connections.

Connection conn1 =  DriverManager.getConnection(connectionString1);
Connection conn2 =  DriverManager.getConnection(connectionString2);

answered Oct 25 '11 at 07:44

socha23

10,171
2
28
25

That's the workaround I was using but I was hoping there would be a better way to do it – Zain Rizvi Oct 25 '11 at 08:40

score 2 · Answer 2 · answered Oct 25 '11 at 07:44

2

Have you tried:

public Connection conn1;
conn1 = DriverManager.getConnection(connectionString1);
public Connection conn2;
conn2 = DriverManager.getConnection(connectionString2);

answered Oct 25 '11 at 07:44

Rohith

2,043
1
17
42

score 1 · Answer 3 · answered Oct 25 '11 at 07:44

1

Instance members shouldn't be public.
Connection should be a local variable, not an instance member.

You can only connect to one database at a time with a single Connection. Ergo you need another Connection.

answered Oct 25 '11 at 07:44

user207421

305,947
44
307
483

Thanks, but I was already following 1 and 2 in my code, I'd just simplified it for the example here. – Zain Rizvi Oct 26 '11 at 22:47
@Zain I don't see how that simplifies anything. Actually it just wastes your time and mine. – user207421 Oct 26 '11 at 23:11

score 0 · Answer 4 · answered Oct 25 '11 at 09:10

0

I think you have to use J2EE, JTA transaction manager to accomplish this.

answered Oct 25 '11 at 09:10

Low Flying Pelican

5,974
1
32
43

Using 2 connections is not very transaction safe. With JTA transaction manager it is possible to achieve this functionality in transparent manner,http://stackoverflow.com/questions/1961566/how-to-configure-transaction-management-for-working-with-2-different-db-in-sprin – Low Flying Pelican Oct 25 '11 at 09:42
I would have thought using two connections wasn't transaction-safe at all, but that statement isn't the same thing as 'you have to use'. OP has said nothing about transaction safety across the databases being a requirement, nor about being in a J2EE environment where JTA is even available. – user207421 Oct 25 '11 at 09:45
since it says, 'How can I add a second database to that same connection', using of two connections does not seems to be the option as I think. So what left is to access databases in transaction safe manner that is why i suggested to use JTA, as I am not aware of any other mechanism to achieve this. – Low Flying Pelican Oct 25 '11 at 10:58
The other mechanism is to rejectb the terms of the question, as everyone else has done. – user207421 Oct 25 '11 at 22:11

Reference two databases with Connection in java

4 Answers4