CODEWITHSUNDEEP
javadesign-patternsjdbcdaomultiple-databases
Harsha
Harsha

Reputation: 3578

How to create multiple database connections for different databases in java

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?

Upvotes: 18

Views: 52766

Answers (6)

BHUSHAN PATIL
BHUSHAN PATIL

Reputation: 1

Assuming you are using Spring MVC with Hibernate with XML configurations, follow these steps:

  1. Create beans of all the databases in your spring-servlet file.

    <bean id="dataSource1" Class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.microsoft.sqlserver.jdbc.SQLServerDriver" />
        <property name="url" value="jdbc:sqlserver://localhost:1433;databaseName=database1"/>
        <property name="username" value="abc" />
        <property name="password" value="abc@123" />
    </bean>

  2. Create sessionFactory beans of all the databases you want in the Spring-servlet file.

    <bean id="datasource1SessionFactory" class="org.springframework.orm.hibernate5.LocalSessionFactoryBean">
        <property name="dataSource"
          ref="database1"/>
        <property name="packagesToScan"
          value="com.id4.iprod.entity"/>
        <property name="hibernateProperties">
            <props>
                <prop key="hibernate.hbm2ddl.auto">
                </prop>
                <prop key="hibernate.dialect">
                    org.hibernate.dialect.SQLServer2012Dialect
                </prop>
            </props>
        </property>
    </bean>

  3. Now you just need to open session of the database you want to in DAO and access the desired results from desired database.

    Session datasource1= this.datasource1SessionFactory.openSession();

Upvotes: 0

Adeel Ansari
Adeel Ansari

Reputation: 39887

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]

Upvotes: 7

hekomobile
hekomobile

Reputation: 1388

is very easy :)

1.Create a Data Source to try connection to DB

public DataSource getDataSource(String db) throws Exception {
DataSource dt = null;
InitialContext ic = null;
try {
    if(db.trim().equals("you_database_name")) {
        dt = (DataSource)ic.lookup("jdbc/connection_name");
    } else if(db.trim().equals("you_database_name")) {
        dt = (DataSource) ic.lookup("jdbc/connection_name");
    }
    return dt;
} catch(NamingException n) {
    throw new Exception("Err getDataSource (ServiceLocator) NamingException - " + n.getMessage());
}

2.Create a class DataBase, remember close all connection in this point.

public class DataBases {
public YouNameDataSourceClass dataSrc;
public DataBases() throws Exception {
   super();
   dataSrc = new YouNameDataSourceClass.getDataSource();
}
public Connection getConnectionAS400() throws Exception {
    return locator.getDataSource("you_database_name").getConnection();
}

public Connection getConnectionOracle() throws Exception {
    return locator.getDataSource("you_database_name").getConnection();
}

public Connection getConnectionSQLServer() throws Exception {
    return locator.getDataSource("you_database_name").getConnection();
}
}

Good look.

Upvotes: 0

mprabhat
mprabhat

Reputation: 20323

There are multiple ways you can achieve this:

  1. If you are using any Java EE container which supports distributed transaction then you can use there functionality.
  2. If you are with plain JDBC then you will have to maintain your own connection for every database. For JDBC:
    1. Provide all connection details
    2. Have an Facade which gives you desired object by calling a abstract generic DAO.
    3. Have a factory which creates dao based on connection.
  3. Use ORM tools like Hibernate, where you can use configuration for multiple database. Tutorial.
  4. 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

Upvotes: 4

Hareesh Narayanan
Hareesh Narayanan

Reputation: 47

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.

Upvotes: 2

Ashish K Agarwal
Ashish K Agarwal

Reputation: 1172

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.

Upvotes: 1

Related Questions