How to dynamically switch databases in a web app?

Folks,
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">
 <parameter>
  <name>factory</name>

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.

Cheers
Angus
LVL 2
amacfarlAsked:
Who is Participating?
 
bloodredsunCommented:
>>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...

e.g.
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) ;
0
 
KuldeepchaturvediCommented:
>>>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


0
 
bloodredsunCommented:
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
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
bloodredsunCommented:
Here's a link an article on the Sun website which deals with DAO's http://java.sun.com/blueprints/corej2eepatterns/Patterns/DataAccessObject.html
0
 
amacfarlAuthor Commented:
All,
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:

      // ESTABLISH DATABASE CONNECTION
             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();

     //EXECUTE STATEMENT
      rs = stmt.executeQuery(.....);

     //PROCESS RESULT
        rs.next();
        if (rs.getInt("TOTAL") == 1)
        ......

     //CLOSE STATEMENT & CONNECTIONS
        rs.close();
        rs = null;
        stmt.close();
        stmt = null;
        connection.close();
        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

views?


0
 
KuldeepchaturvediCommented:
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)


0
 
amacfarlAuthor Commented:
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
Angus
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.