How To Use Multiple Database Connections On SpringMVC + Hibernate

In my experience, I met a rare case which is I should use more than one database connection on my web application. In that case, what I did was create some hibernate configuration file and call it whenever I need it. I have one main database which is use for my web application it self, and some hibernate configuration for another purpose.

Here are some snap of my code. I use oracle for my example.

ApplicationContext.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:p="http://www.springframework.org/schema/p"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xmlns:tx="http://www.springframework.org/schema/tx"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:mvc="http://www.springframework.org/schema/mvc"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.1.xsd
       http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.1.xsd
       http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-3.1.xsd
       http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.1.xsd
       http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.1.xsd">
      
    <context:component-scan base-package="com.ariestania.example" />
    <tx:annotation-driven /> 
 
    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"
          p:driverClassName="oracle.jdbc.OracleDriver"
          p:url="jdbc:oracle:thin:@10.98.10.128:1521:examples"
          p:username="thisisuser"
          p:password="thisispass!"
          p:initialSize="5"
          p:maxActive="10"
          p:maxIdle="5"
          p:minIdle="2"
          p:maxWait="30000"
          p:removeAbandoned="true"
          p:removeAbandonedTimeout="30"
          p:validationQuery="SELECT 1 from dual"/>
    
    <bean id="sessionFactory" class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean"
          p:dataSource-ref="dataSource"
          p:configLocation="classpath:hibernate.cfg.xml">
        <property name="hibernateProperties">
            <props>
                <prop key="hibernate.format_sql">true</prop>
            </props>
        </property>
    </bean>
    
    <bean id="transactionManager" class="org.springframework.orm.hibernate3.HibernateTransactionManager"
          p:sessionFactory-ref="sessionFactory"/>

</beans>

Dispatcher-servlet.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:p="http://www.springframework.org/schema/p"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xmlns:tx="http://www.springframework.org/schema/tx"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:util="http://www.springframework.org/schema/util"
       xmlns:mvc="http://www.springframework.org/schema/mvc"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.1.xsd
       http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.1.xsd
       http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-3.1.xsd
       http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.1.xsd
       http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.1.xsd
       http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-3.1.xsd
       http://www.springframework.org/schema/util  http://www.springframework.org/schema/util/spring-util-3.1.xsd">

    <context:annotation-config/>
    <context:component-scan base-package="com.ariestania.example.controller"/>
    
    <mvc:resources mapping="/js/**" location="/js/"/>
    <mvc:resources mapping="/css/**" location="/css/"/>
    <mvc:resources mapping="/fonts/**" location="/fonts/"/>
    <mvc:resources mapping="/images/**" location="/images/"/>
    <mvc:resources mapping="/template/**" location="/template/" />
   
    <mvc:annotation-driven/>
  
    <bean id="viewResolver"
          class="org.springframework.web.servlet.view.InternalResourceViewResolver"
          p:prefix="/WEB-INF/jsp/"
          p:suffix=".jsp" />
    
    <bean id="multipartResolver"
          class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
    </bean>
    
</beans>

Hibernate configurations
hibernate1.cfg.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN" "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
  <session-factory>
    <property name="hibernate.dialect">org.hibernate.dialect.Oracle10gDialect</property>
    <mapping resource="com/ariestania/example/model/Users.hbm.xml"/>
    <mapping resource="com/ariestania/example/model/UserRoles.hbm.xml"/>
  </session-factory>
</hibernate-configuration>

hibernate1.reveng.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-reverse-engineering PUBLIC "-//Hibernate/Hibernate Reverse Engineering DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-reverse-engineering-3.0.dtd">
<hibernate-reverse-engineering>
  <schema-selection match-schema="thisisuser"/>
  <table-filter match-name="USERS"/>
  <table-filter match-name="USER_ROLES"/>
</hibernate-reverse-engineering>

hibernate2.cfg.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
  <session-factory>
    <property name="hibernate.dialect">org.hibernate.dialect.OracleDialect</property>
    <property name="hibernate.connection.driver_class">oracle.jdbc.OracleDriver</property>
    <property name="hibernate.connection.url">jdbc:oracle:thin:@10.98.10.128:1521:examples</property>
    <property name="hibernate.connection.username">DBEXAMPLETWO</property>
    <property name="hibernate.connection.password">thisispass!</property>
    <mapping resource="com/ariestania/example/conftwo/model/Customer.hbm.xml"/>
  </session-factory>
</hibernate-configuration>

hibernate2.reveng.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-reverse-engineering PUBLIC "-//Hibernate/Hibernate Reverse Engineering DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-reverse-engineering-3.0.dtd">
<hibernate-reverse-engineering>
  <schema-selection match-schema="DBEXAMPLETWO"/>
  <table-filter match-name="CUSTOMER"/>
</hibernate-reverse-engineering>

Sample Hibernate Mapping Files

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
<!-- Generated Nov 20, 2014 10:12:59 AM by Hibernate Tools 3.6.0 -->
<hibernate-mapping>
    <class name="com.ariestania.example.model.Users" table="USERS" schema="thisisuser">
        <id name="username" type="string">
            <column name="USERNAME" length="50" />
            <generator class="assigned" />
        </id>
        <property name="password" type="string">
            <column name="PASSWORD" not-null="true" />
        </property>
        <property name="name" type="string">
            <column name="NAME" length="50" />
        </property>
        <property name="enable" type="big_decimal">
            <column name="ENABLE" precision="22" scale="0" not-null="true" />
        </property>
        <set name="userRoles" table="USER_ROLE" inverse="true" lazy="false" fetch="select">
            <key>
                <column name="USERNAME" length="50" not-null="true" />
            </key>
            <one-to-many class="com.ariestania.example.model.UserRole" />
        </set>
    </class>
</hibernate-mapping>

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
<!-- Generated Nov 17, 2014 11:36:57 AM by Hibernate Tools 3.6.0 -->
<hibernate-mapping>
    <class name="com.ariestania.example.conftwo.model.Customer" table="CUSTOMER" schema="DBEXAMPLETWO">
        <composite-id name="id" class="com.ariestania.example.conftwo.model.VaCustId">            
            <key-property name="kodeFitur" type="string">
                <column name="KODE_FITUR" length="6" />
            </key-property>
            <key-property name="idCust" type="string">
                <column name="ID_CUST" length="30" />
            </key-property>
        </composite-id>
        <property name="custNamaAdmin" type="string">
            <column name="CUST_NAMA_ADMIN" length="50" />
        </property>
        <property name="custHpAdmin" type="string">
            <column name="CUST_HP_ADMIN" length="50" />
        </property>
    </class>
</hibernate-mapping>

Sample Models

import java.math.BigDecimal;
import java.util.HashSet;
import java.util.Set;

public class Users  implements java.io.Serializable {

     private String username;
     private String password;
     private String name;
     private BigDecimal enable;
     private Set<UserRole> userRoles = new HashSet<UserRole>(0);

    public Users() {
    }
	
	// constructor .. getter .. setter

}
public class Customer  implements java.io.Serializable {

     private CustomerId id;
     private String custNamaAdmin;
     private String custHpAdmin;

    public Customer() {
    }
	
    public Customer(CustomerId id) {
        this.id = id;
    }
    public Customer(CustomerId id, String custNamaAdmin, String custHpAdmin) {
       this.id = id;
       this.custNamaAdmin = custNamaAdmin;
       this.custHpAdmin = custHpAdmin;
    }
   
	//getter setter
}

Services

import com.ariestania.example.model.UserRole;
import com.ariestania.example.model.Users;
import java.util.ArrayList;
import java.util.List;
import java.util.Set;
import org.apache.commons.lang.StringUtils;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

/**
 *
 * @author ariestania.winda
 */
@Service("userService")
@Transactional
public class UserService extends BaseService {

    @Autowired
    private SessionFactory sessionFactory;

    public void saveOrUpdate(Object user) throws Exception {
        sessionFactory.getCurrentSession().saveOrUpdate(user);
    }

    public List<Users> listUser() {
        return sessionFactory.getCurrentSession().createQuery("from Users").list();
    }


}
import com.ariestania.example.conftwo.model.Customer;
import java.util.ArrayList;
import java.util.List;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;
import org.springframework.stereotype.Service;

/**
 *
 * @author ariestania.winda
 */

@Service("customerService")
public class CustomerService {
    
    SessionFactory sessionFactory = new Configuration().configure("hibernate2.cfg.xml").buildSessionFactory();
    Session session = null;
    Transaction transaction = null;
    Query query = null;
    
    String keycd = "xxx";
        
    public List<Customer> selectAllxxxx(){
        List<Customer> lCustomers = new ArrayList<>();
        try {
            session = sessionFactory.openSession();
            query = session.createQuery("from Customer where SUBSTR(CustomerId,1,3) = :keysearch")
                    .setParameter("keysearch", keycd);
            lCustomers = (ArrayList<Customer>) query.list();
            session.close();
        } catch (HibernateException e) {
            System.out.println("error on Customerservice : " + e.getMessage());
        }
        return lCustomers;
    }
    
}

Controller

@Controller
public class UserController extends BaseController{
    
    @Autowired
    private UserService userService;     
       
    @RequestMapping(value = "admin/listuser", method = RequestMethod.GET)
    public String listUser(ModelMap model){
        model.put("welcome", "Welcome");
        model.put(PARAM_BODY_PAGE, "form-user/listUser.jsp");
        model.put("listusers", userService.listUser());
        model.put(PARAM_USERNAME, BaseController.getNameUser());
        return MAIN_JSPTEMPLATE;
    }
    
    @RequestMapping(value = "admin/viewlog", method = RequestMethod.GET)
    public String viewLog(ModelMap model){
        model.put(PARAM_USERNAME, BaseController.getNameUser());
        model.put(PARAM_BODY_PAGE, "form-user/viewLog.jsp");
        return MAIN_JSPTEMPLATE;
    }
   
@Controller
public class MerchantController {
    
    @Autowired
    private CustomerService customerService;
    
    @RequestMapping(value = "/customerxxxx", method = RequestMethod.GET)
    public @ResponseBody List<Customer> customerxxxx(){
        return customerService.selectAllxxxx();
    }
    
}

Hope this simple article can help 🙂
If some of you have another better way for this case, I hope you can share it with me 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s