I have 2 different databases one is MYSQL other is Oracle.Each have 1 table with different name and different columns name.Now I have to perform some db opeartions on each db from a single java application.Suppose for MYSQL db I have Emp table with columns Id,Name,Dept and for Oracle db I have Student table with StudentName and StudentDept.Now without changing code how can I manage 2 dbs?If I mention all db connection related data(connection url,username,password) in a properties file but to execute query I have to mention table name and column name in code.How can I manage it dynamically without altering the code so that in future any new db with different table name and column name is added I can only add the new one in properties file and no need to touch the code.Please suggest.
1 Answer
This might not be the prettiest, but one way to do this:
- On application launch, parse properties files to get all DB connections. Store these however you want...List of connection pools, list of single connections, list of connection strings, etc...it doesn't matter.
Run a predefined stored procedure or select query to retrieve all table names from each database found in step 1. In sybase you can do this with
select name from sysobjects where type = 'U'Build a Map where the key is the table name and the value is either the DB name, connection, connection string, or whatever you are using to manage your DB connections from the result set of #2. Anything that can be passed to your DB connection manager to identify which database it should connect to will work as the value.
- In code, when table name is passed, lookup the required DB in the map
- Execute query on returned DB Info in the map you created in step 3
As long as the tables are distinct in each DB this will work. Once this is setup, new DBs can be added to the properties file and the cache can be refreshed with an application restart. However, if new tables/columns are being sent to the code, how are these being passed without any code change?
DatabaseMetaData