Learn how to a build a cloud-first strategyRegister Now


How to dynamically switch databases in a web app?

Posted on 2005-05-12
Medium Priority
Last Modified: 2010-04-01
I am connecting to our production database using the following java code.

      Context ic = new InitialContext();
      DataSource ds = (DataSource) ic.lookup("java:comp/env/jdbc/test");
      Connection conn = ds.getConnection();
      Statement stmt = conn.createStatement();

server.xml is ......

 <Context path="/sc" docBase="../webapps/sc" debug="1" reloadable="true">
  <Resource name="jdbc/test" auth="Container" type="javax.sql.DataSource" />
 <ResourceParams name="jdbc/test">

The database that we are using is read-only and we execute updates on a daily basis.  This process is taking 2 hours per day and during this time the site is down.

Our customer now wishes to have no downtime.  So we think the only solution is to have two databases, one which is updated at some point during the day change read only database to the updated database.

So, my question is how manage this effectively.  We have the following structure:

(1) DB_Customer Data [never updated]
(2) DB_Data                [daily update taking 2 hours]

we are thinking of doing:

(1) DB_Customer Data [never updated]
(2) DB_Data1              
(3) DB_Data2

therefore at any one time only DB_Data1 or DB_Data2 would be live.  The other would be getting updated.

But how to switch between DB_Data1 and DB_Data2.  Would it be best to have two DataSources maintained in Server.xml and then use a table in DB_CustomerData to contain details of which DB to point to?

Yours views on this would be very much apprecaited.

Question by:amacfarl
  • 3
  • 2
  • 2
LVL 19

Expert Comment

ID: 13989008
>>>But how to switch between DB_Data1 and DB_Data2.  Would it be best to have two DataSources maintained in Server.xml and then use a table in DB_CustomerData to contain details of which DB to point to?

Well only way I can think of is to have two Datasources created in your server.xml...
and then in your bean which makes the connection for you have a condition which checks the database to be connect..

this check can be baesed on many thing...

if you know the time of daily updates then you can have a switch baesed in time..

if you do not know the time then you can try to connect to your default databse and if it errors out then connect to the alternative connection

LVL 29

Expert Comment

ID: 13991323
it sounds like you need 2 databases and 2 datasources as Kuldeep has suggested but that you acess them using a DAO (Data Acess Object) which can decide for you which database to use. This also means that you don't have the JNDI code in your presentation layer which means that any change to the database can be done jsut by changing one class rather than multiple servlets and JSPs.

 It's a fairly standard design pattern and means that your webapp code refers to a bean which transparently connects you to the correct database.

2 hours is fairly long for an ETL, but using a second DB would solve your problem
LVL 29

Expert Comment

ID: 13994567
Here's a link an article on the Sun website which deals with DAO's http://java.sun.com/blueprints/corej2eepatterns/Patterns/DataAccessObject.html
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


Author Comment

ID: 14000278
Thanks to everyone for their thorough responses.  As always, I am very impressed by the feedback to my question.

So... we have numerous beans that execute reports and the general code structure is:

             ic     = new InitialContext();
             envCtx = (Context) ic.lookup(CLS_Settings.DB_CONTEXT);
             ds     = (DataSource) envCtx.lookup(CLS_Settings.DB_DATASOURCE);
             connection = ds.getConnection();
             stmt = connection.createStatement();

      rs = stmt.executeQuery(.....);

        if (rs.getInt("TOTAL") == 1)

        rs = null;
        stmt = null;
        connection = null;

Now based on my problem, I will be implementing some logic to determine which Datasource to use.  I like the DAO structure and in the past I tried to implement this design.  However my main issue is what to put in the DAO and what not to include....

For example - do use a DAO to simply connect to the DB, leaving the report bean to close any connection & statements or do I put more logic in the DAO.

My initial thoughts are to only use a DAO bean to connect to the DB and manage everything else in the report Bean.  In the past I tried to use a DAO design, but I feel I put too much logic into it


LVL 29

Accepted Solution

bloodredsun earned 1000 total points
ID: 14000401
>>My initial thoughts are to only use a DAO bean to connect to the DB and manage everything else in the report Bean.  In the past I tried to use a DAO design, but I feel I put too much logic into it

It's up to you. Data access can be as simple as getting the connection to the correct database, to actually returning the data for a particular statement.

As you have all the code already in place for the database logic, I would just make the DAO an object that gives you the connection to the correct database (you may need to pass in the Context as a parameter to the constructor) as that seems simpler.

I would also tend to put the closing connection logic in there...

DAO myDAO = new DAO(new InitialContext());
Connection conn = DAO.getConnection(); //here you have the connection to the correct database
//blah blah SQL code
myDAO.closeConnection (conn) ;
LVL 19

Assisted Solution

Kuldeepchaturvedi earned 1000 total points
ID: 14001360
Normally in My DAO I have following methods..

Constructor( pass the JNDI context)

Connect() //connect to the database

Execute() //takes a string and executes the query

ExecutePrepare// takes a string and object array, makes the prepared statement & execute ( this one sometimes become a bit to complex)..

Close() //return the connection back to pool ( if its not a connection pool then simply close it)


Author Comment

ID: 14005662
Kuldeepchaturvedi & bloodredsun

Thanks all for your help.  I now have the application working perfectly!!

I cant thank you enough!

I have split the points between you.

All the best

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will help to fix the below errors for MS Exchange Server 2016 I. Certificate error "name on the security certificate is invalid or does not match the name of the site" II. Out of Office not working III. Make Internal URLs and Externa…
In the below post we have mentioned the best hosting type for startups. Also, check out some of the superlative web hosting companies that are proposing affordable web hosting solutions to host your startup website.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question