How to create multiple database connections for different databases in java
Solution 1
As you have not tagged your question with any of this, hibernate
, JPA
, ORM
, I assume you are dealing with plain JDBC.
Having said that, I suggest you to have a DAO layer to deal with underlying databases, and leave the connection details to specific implementations. You can configure your connection strings in some .properties
files, lets say.
[Complement]
You can also make use of DAO factory, an implementation of Abstract Factory or Factory Method pattern, whichever suits here.
[Links]
- A very fine implementation of DAO and DAO Factory, by BalusC
- Core J2EE Patterns -- arguably dated but might provide some idea.
Solution 2
There are multiple ways you can achieve this:
- If you are using any Java EE container which supports distributed transaction then you can use there functionality.
- If you are with plain JDBC then you will have to maintain your own connection for every database.
For JDBC:
- Provide all connection details
- Have an Facade which gives you desired object by calling a abstract generic DAO.
- Have a factory which creates dao based on connection.
- Use ORM tools like Hibernate, where you can use configuration for multiple database. Tutorial.
- If you are using Spring, then you can configure one datasource per database. Docs
Design Patterns:
- Facade Pattern - for hiding the complexity and multiple database usage.
- Factory - In case you manage the database connection yourself.
- Singleton - For datasources
Solution 3
You can handle multiple connections easily using a ORM tool like Hibernate.. You can specify each connection in a separate configuration file and instantiate the required connection by getting a new session factory each time.
Other way would be to use datasource and JNDI : Java connecting to multiple databases
I think you can use a combination of Factory pattern and Singleton pattern for the purpose.
Solution 4
The Ideal way to achieve this is by using a multi-dimensional system like OLAP. But see if you can create a view out of those databases. Then you just need to query the view (i.e. just a single database connection). Also you can still use multiple database connections if you want.
Related videos on Youtube
![Harsha](https://i.stack.imgur.com/7Hb7F.jpg?s=256&g=1)
Harsha
I am a BCS post graduate level student and working as a java programmer in Kandy, Sri Lanka. I've fallen love with java so much.
Updated on May 22, 2020Comments
-
Harsha about 4 years
I have an application which uses four databases in different geographical locations. All the databases contains same tables and only the database name is different according to the location. I have to create some reports in my application which uses data from each database. What would be the proper way to create those database connection from a java application and is there a suitable design pattern for this task which I could use?
-
Ashish K Agarwal about 12 yearsbut then how will you have multiple db connections?
-
Adeel Ansari about 12 years@munna: Every DAO implementation will know its Datasource.