CODEWITHSUNDEEP
javamysqlsqldatabasejdbc
Srikanth Kandalam
Srikanth Kandalam

Reputation: 1045

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

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?

Upvotes: 0

Views: 2337

Answers (3)

Barranka
Barranka

Reputation: 21057

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.

Upvotes: 1

unigeek
unigeek

Reputation: 2826

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..

Upvotes: 1

Durandal
Durandal

Reputation: 5663

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.

Upvotes: 3

Related Questions