Dynamic Database Connection

[Jagadish Jain]

Hello,

Im developing a project where in the connection to DataBase is static i.e Im hard coding the details of the database in createConnection() method.
Now Im required to dynamically connect to a database i.e in the first page of my project, the user enters the database details(servername,port,username,passowrd,servicename) and using this details my project should be connected to respective database.

One solution to this is to have my createConnection() method as createConnection(String serverName,String portNumber,
String userName,String pword,String s_id).
But my project is very vast and everywhere I have used createConnection() to connect to database, changing this method to createConnection(String serverName,String portNumber,
String userName,String pword,String s_id) everywhere is not possible.
So im looking for an alternative solution.
Thanks in Advance :-)

[Wendy L Gibbons]

Welcome to the ranch jagadish.

i would make a class held somewhere god like that contains the connection details, so getConnection() searches for one of them, uses the details, if not carrys on as now.

just one question... will they actually be able to change the dbms? so could it be mysql/oracle/sql server?

[Wendy L Gibbons]

in case i confused you

God object

[Joe Ess]

Wendy Gibbons wrote:

God object

Wendy, I don't think you mean a god object. That's an antipattern (a bad idea) where one puts all their functionality in One Big Object.
We have solved this problem a couple of different ways. The simplest is to extend java.util.Properties to append the user name or host name to the property that one asks for. The default case is the name of the property. So your property file looks like:

# 
database.server.name=localhost
database.server.name.test_user=some_test_host
database.server.name.production_user=some_production_host
#
database.server.port=1234
database.server.port.test_user=5678
database.server.port.production_user=9012
# ...

If one logs in to a system as test_user (which would presumably only happen on the test server), they'd get a database server name as "some_test_host" and a port of 5678. If one logs in as production_user, they'd get "some_production_host" and 9012. Any other user would get the default values of "localhost" and 1234 (in our case, the default is our development workstations).
Of course, I do mostly web applications, so we use the container to manage our database settings and connection pool. The above example is used for other configuration information.

[William P O'Sullivan]

Joe is right.

Never, ever, ever embed connection details in the code.

Passwords expire, ports change etc..

You should always put these values outside the application, outside of the war/ear also!

Pat.

[Joe Ess]

William P O'Sullivan wrote:
You should always put these values outside the application, outside of the war/ear also!

I would say that depends on your requirements regarding downtime. A configuration change often has accompanying code changes, so you are going to have to redeploy the app anyway. The Properties-based solution I propose would have to be redeployed if a change were made no matter where the config file was. I believe Apache Commons Configuration watches for changes and reloads if it detects them.

[Wendy L Gibbons]

Joe Ess wrote:

Wendy Gibbons wrote:

God object

Wendy, I don't think you mean a god object. That's an antipattern (a bad idea) where one puts all their functionality in One Big Object.
We have solved this problem a couple of different ways. The simplest is to extend java.util.Properties to append the user name or host name to the property that one asks for. The default case is the name of the property. So your property file looks like:

# 
database.server.name=localhost
database.server.name.test_user=some_test_host
database.server.name.production_user=some_production_host
#
database.server.port=1234
database.server.port.test_user=5678
database.server.port.production_user=9012
# ...

If one logs in to a system as test_user (which would presumably only happen on the test server), they'd get a database server name as "some_test_host" and a port of 5678. If one logs in as production_user, they'd get "some_production_host" and 9012. Any other user would get the default values of "localhost" and 1234 (in our case, the default is our development workstations).
Of course, I do mostly web applications, so we use the container to manage our database settings and connection pool. The above example is used for other configuration information.

true it is an anti-pattern but have you ever worked on a leagacy sytem that didn't already have one?

[Wendy L Gibbons]

Joe Ess wrote:

Wendy Gibbons wrote:

God object

Wendy, I don't think you mean a god object. That's an antipattern (a bad idea) where one puts all their functionality in One Big Object.
We have solved this problem a couple of different ways. The simplest is to extend java.util.Properties to append the user name or host name to the property that one asks for. The default case is the name of the property. So your property file looks like:

# 
database.server.name=localhost
database.server.name.test_user=some_test_host
database.server.name.production_user=some_production_host
#
database.server.port=1234
database.server.port.test_user=5678
database.server.port.production_user=9012
# ...

If one logs in to a system as test_user (which would presumably only happen on the test server), they'd get a database server name as "some_test_host" and a port of 5678. If one logs in as production_user, they'd get "some_production_host" and 9012. Any other user would get the default values of "localhost" and 1234 (in our case, the default is our development workstations).
Of course, I do mostly web applications, so we use the container to manage our database settings and connection pool. The above example is used for other configuration information.

And he has been asked to let the users type thier details into a login screen, how is a config file going to help for this?

[Jagadish Jain]

Let me put my problem statement in more clear way........

My web application connects to a database, I have a DAOFactory class where in I have createConnection() method that connects to the database. I have many classes in my project which uses this method to connect to the database.

Now I have been given a requirement like, my application should connect to the database to which the user wants to connect. So now the first page of my project is a form wherein the user will enter the Database information(servername,port,username,passowrd,sid).
Depending on this info provided by the user my project should work on this DB which the user has given the details about.

Now, since I have to connect to database to which user wants, I need to have the createConnection() method as
createConnection(String serverName,String portNumber,String userName,String pword,String s_id)
but replacing the former with the latter in the whole project is not possible. So im looking for a solution for this which should work even when multiple users acces my application.


P.S: Static variables cannot be used to store the servername,port,username,passowrd and sid since this case would fail when multile users acces my application.

[Nauman Hasan]

Just curious if you are writing a DB tool? Unless you are doing so I don't know of a good reason why your users would be exposed to the DB details...

The better approach would be to do a mapping between a logical name the user knows about for instance West-End-Branch to all the relevant DB details which you have mentioned above.

Hope this helps...

~Nauman

[Jagadish Jain]

We guys actually have a software product, so we usually show our product demo to the clients using our database. We are now planning to show the client the product demo using their databse. So the first page of the project is for the client where he will be providing his db details.

[Wendy L Gibbons]

Nauman Hasan wrote:Just curious if you are writing a DB tool? Unless you are doing so I don't know of a good reason why your users would be exposed to the DB details...

The better approach would be to do a mapping between a logical name the user knows about for instance West-End-Branch to all the relevant DB details which you have mentioned above.

Hope this helps...

~Nauman

I would stick this in a config file which can be created in front of the users, and then connected to their database.

A previous employer used to do demos like this, and this was how it was done.

Be very very careful about database independance. Test your product against the customer database vendor before you go out there, you wouldnd't believe the vendor specific gotchas

[Nauman Hasan]

Wendy Gibbons wrote:

I would stick this in a config file which can be created in front of the users, and then connected to their database.

A previous employer used to do demos like this, and this was how it was done.

Be very very careful about database independance. Test your product against the customer database vendor before you go out there, you wouldnd't believe the vendor specific gotchas

Agreed, doing demos like these seem like a recipe for things to go wrong...

Jagadish: Are you trying to create a connection each time the user does a query to the DB?

~Nauman