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/j
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
I am connecting to our production database using the following java code.
Context ic = new InitialContext();
DataSource ds = (DataSource) ic.lookup("java:comp/env/j
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
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
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
Here's a link an article on the Sun website which deals with DAO's http://java.sun.com/blueprints/corej2eepatterns/Patterns/DataAccessObject.html
ASKER
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_
ds = (DataSource) envCtx.lookup(CLS_Settings
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?
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_
ds = (DataSource) envCtx.lookup(CLS_Settings
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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