My application is using SOAP webservices with Ibatis on Weblogic 8, oracle 10g, but due to some changed in the DB architecture; my application has to go under heavy change, which I want to avoid. The Situation is as follows:
1. The main archive DB, has been spitted in to 10 or more annual databases.
2. Now because of point no. 1, instead of hitting 1 master DB my application have to hit multiple databases based on the search date range and merge the Resultsets. For example in the search date range is 25.12.1999 to 1.1.2001, in this case my service should query database of 1999, 2000 and 2001.
3. My application ready to work with one master DB, but due to this DB architecture change my application has to undergo major changes, which I want to avoid by introducing a layer in between the DAO and DB, which would dispatch the determine to correct DB to hit then trigger the queries in the DBs then merge the resultsets and return to the DAO.
4. This layer could be, programmatically set the datasource in the DAO and trigger the queries to the respective DB sequentially, which would result into performance degradation.
Or alternatively, use a tool such as UnityJDBC, but it's a beta product.
OR, writing the JDBC-Driver wrapper
5. DB-Views can not be used, due to some company policy.
Please some one suggest a good solution with could resolve this problem with minimum changes.