How to maintain multiple SQL database connections in a single Java file?

Question

I have a project in which I have been using a single database till now. Now there is a requirement to use a new database. I have one Dbconn java file in which I have hard-coded the database name, username, password like shown below:

    con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","pass");

Now my requirement is to have another connection to a new database so that I can access the data from it using the same connection object. In other words, I want to avoid hard coding this and I want a different way where I can accommodate more number of databases in future. Could anyone please let me know how I can proceed about this?

This is actually a pretty good use for DI/IoC. Granted, that DI can get scary in a hurry [especially in Java and XML-centric systems], but IoC practices can be used anyway .. — user2864740
– user2864740, Commented Jan 28, 2014 at 0:53
as in, selecting a single database connection to use throughout a program based on a configuration file, or command-line parameter, or …? — BRPocock
– BRPocock, Commented Jan 28, 2014 at 1:11
When you say 'a new database` did you mean another mysql instance locally ie jdbc:mysql://localhost:3306/anotherdb or a completely different database instance ie jdbc:mysql://someotherhost:3306/anotherdb? — Durandal
– Durandal, Commented Jan 29, 2014 at 7:55

Durandal · Accepted Answer · 2014-01-28 01:20:55Z

3

If the other database is in the same physical MySQL instance you can change from test to another database by calling this: con.setCatalog("otherDBName"). Java Docs for setCatalog

In JDBC a catalog is the same thing as a database(basically a namespace within a server). If it's not the same physical DB you're going to have to create a new Connection object to connect to the other DB server.

edited Jan 28, 2014 at 1:20

answered Jan 28, 2014 at 0:56

Durandal

5,6425 gold badges38 silver badges51 bronze badges

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

Comments

unigeek · Accepted Answer · 2014-01-28 01:29:15Z

Suggest getting away from low-level JDBC APIs and moving towards some kind of useful abstraction for data access--maybe Spring's JdbcTemplate would be appropriate? From the level of detail you gave, all I can guess is that you are wanting to swap in/out various instances of the same database schema (e.g., development, stage, production). If you decide to use Spring, it will be natural to put the various database connection details into Spring configuration files (possibly referencing vanilla properties files) that are external to your application. With Spring, you'll probably want to configure a DataSource rather than a connection, but you will be well-rewarded for the little effort required to learn to use JdbcTemplate. Failing that, you could certainly give yourself a method that returns a connection and in that method you could take care of which connection to return--e.g.

public Enum MyDb { DEV, STAGE, PROD }
...
con = getConnection(MyDb.PROD)

Just a thought..

taz_13 · Accepted Answer · 2022-05-31 08:27:36Z

If you want to do it by hand, you can create an array of connections, and call the appropriate one in your program:

Connection[] conArray = new Connection[10]; // Just an example
String db, user, pwd;
db = "test";
user = "root";
password = "pass";
connArray[0] = DriverManager.getConnection("jdbc:mysql://localhost:3306/" + db, user, pwd);
db = "another_db";
user = "hermie";
pwd = "nutsAndBolts";
connArray[1] = DriverManager.getConnection("jdbc:mysql://localhost:3306/" + db, user, pwd);

Notice that you can build the connection URL in runtime: Simply assign the variables with the appropriate values and use them.

Of course, you can use a collection of Connection objects.

You may call then the appropriate connection on your methods.

You may want to take a look on connection pools.

Hope this helps.

Collectives™ on Stack Overflow

How to maintain multiple SQL database connections in a single Java file?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related