I'm currently developping a J2EE app.
In this app, I have 2 databases. One MSSQL server that host all "user related data" (user info, subscriptions to my service, ...) and accessed via EJB. No problem.
My second DB (MySQL) contains read-only data that is accessed via JDBC. This read-only DB will support a big load and needs to refreshed once a day. To do that, we need to shutdown the DB and import fresh CSV files.
I also have no problem with that.
My concern is that, during the time the DB is refreshed, my service is not available (which is not very nice...).
So the idea would be to have a second DB, identical, that would be used while the other is updated. When the first one is done, then we do the other one. When both are up to date, then we would balance the queries to the 2 DB.
In an ideal world, this whole thing should be transparent to the application. This even should be provided by the JDBC layer. This leads to my question :
does anyone know if some kind of JDBC wrapper that would do the job exists ?
If not, how complex do you estimate the development of such a wrapper (no need for transaction !!!) ?
Thx for your ideas !
PS : for those who are still reading this boring post, here is some more explanation on what I have in mind ;-)
- define 2 (or more) DB connections, using the same driver and so on, but connected on 2 different DBs... Let's say "java:/DB1" and "java:/DB2"
- define another DB connection (virtual) using some king of JDBC wrapper that would be configured to return a connection from "java:/DB1" or "java:/DB2", depending on some load balancing algorithm.