Hibernate configuring multiple datasources and multiple session factories
<!-- configure hibernate session factory for FirstDB -->
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
destroy-method="close" p:driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
p:url="jdbc:sqlserver://${FirstDB_DB_HosName}\${FirstDB_DB_instanceName}:${FirstDB_DB_PortNumber};databaseName=${FirstDB_DB_DatabaseName};username=${FirstDB_DB_UserName};password=${FirstDB_DB_Password};" />
<bean id="sessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
<property name="dataSource" ref="dataSource" />
<property name="configLocation">
<value>classpath:hibernate.cfg.xml</value>
</property>
<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">org.hibernate.dialect.SQLServerDialect</prop>
<prop key="hibernate.current_session_context_class">thread</prop>
<prop key="show_sql">false</prop>
</props>
</property>
</bean>
<!-- configure hibernate session factory for SecondDB database -->
<bean id="SecondDBdataSource" class="org.apache.commons.dbcp.BasicDataSource"
destroy-method="close" p:driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
p:url="jdbc:sqlserver://${SecondDB_DB_HOST}\${SecondDB_DB_INSTANCE}:${SecondDB_DB_PORT};databaseName=${SecondDB_DB_DATABASENAME};username=${SecondDB_DB_USERNAME};password=${SecondDB_DB_PASSWORD};" />
<bean id="secondDBSessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
<property name="dataSource" ref="SecondDBdataSource" />
<property name="configLocation">
<value>classpath:hibernate-SecondDB.cfg.xml</value>
</property>
<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">org.hibernate.dialect.SQLServerDialect</prop>
<prop key="hibernate.current_session_context_class">thread</prop>
<prop key="show_sql">false</prop>
</props>
</property>
</bean>
In the Hibernate DAO we can use @Qualifier annotation as below to wire the 2 session factories
/**
* Basic DAO operations dependent with Hibernate's specific classes
* @see SessionFactory
*/
@Transactional(propagation= Propagation.REQUIRED, readOnly=false)
public class HibernateDao<E, K extends Serializable> implements GenericDao<E, K> {
@Autowired
@Qualifier(value="sessionFactory")
private SessionFactory sessionFactory;
@Autowired
@Qualifier(value="secondDBSessionFactory")
private SessionFactory secondDBSessionFactory;
protected Class<? extends E> daoType;
public HibernateDao() {
daoType = (Class<E>) ((ParameterizedType) getClass().getGenericSuperclass())
.getActualTypeArguments()[0];
}
//Remaining Code
}
You can show that simple configuration in my project
<context:component-scan base-package="it.dommy.portmaga" />
<context:property-placeholder location="default.properties" />
<!-- Enable annotation style of managing transactions -->
<tx:annotation-driven />
<context:annotation-config/>
<bean id="dataSource"class="com.mchange.v2.c3p0.ComboPooledDataSource"
destroy-method="close" >
<property name="driverClass" value="${db.driverClassName}" />
<property name="jdbcUrl" value="${db.url}" />
<property name="user" value="${db.username}" />
<property name="password" value="${db.password}" />
</bean>
<bean id="sessionFactory" class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
<property name="dataSource" ref="dataSource"/>
<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">${hibernate.dialect}</prop>
<prop key="hibernate.hbm2ddl.auto">${hibernate.hbm2ddl.auto}</prop>
<prop key="hibernate.show_sql">${hibernate.show_sql}</prop>
<prop key="hibernate.format_sql">${hibernate.format_sql}</prop>
<prop key="hibernate.hbm2ddl.auto">create-drop</prop>
<!--<prop key="hibernate.use_sql_comments">${hibernate.use_sql_comments}</prop>-->
<!--<prop key="hibernate.validator.apply_to_ddl">${hibernate.validator.apply_to_ddl}</prop>-->
<!--<prop key="hibernate.max_fetch_depth">${hibernate.max_fetch_depth}</prop>-->
<!--<prop key="hibernate.default_batch_fetch_size">${hibernate.default_batch_fetch_size}</prop>-->
<!--<prop key="hibernate.jdbc.batch_size">${hibernate.jdbc.batch_size}</prop>-->
<!--<prop key="hibernate.validator.autoregister_listeners">true</prop>-->
<!-- This will drop our existing database and re-create a new one. -->
</props>
</property>
<property name="packagesToScan">
<list>
<value>it.dommy.portmaga.model.mysql</value>
</list>
</property>
</bean>
<bean id="secondDBdataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"
destroy-method="close">
<property name="driverClass" value="${dba.driverClassName}" />
<property name="jdbcUrl" value="${dba.url}" />
<property name="user" value="${dba.username}" />
<property name="password" value="${dba.password}" />
</bean>
<bean id="secondSessionFactory" class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
<property name="dataSource" ref="secondDBdataSource" />
<property name="hibernateProperties">
<props>
<prop key="hibernate.dialect">${hibernate.a.dialect}</prop>
<prop key="hibernate.show_sql">${hibernate.show_sql}</prop>
</props>
</property>
<property name="packagesToScan">
<list>
<value>it.dommy.portmaga.model.access</value>
</list>
</property>
</bean>
<bean id="transactionManager" class="org.springframework.orm.hibernate4.HibernateTransactionManager">
<property name="sessionFactory" ref="secondSessionFactory" />
</bean>
if you wont show all project go here: https://bitbucket.org/dgabriele/portmaga
dispatcher-servlet.xml
<bean id="full-license-db-dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="${db.driver}" />
<property name="url" value="${db.full.url}" />
<property name="username" value="${db.full.username}" />
<property name="password" value="${db.full.password}" />
</bean>
<bean id="sessionFactoryFull"
class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
<property name="packagesToScan" value="ci.xyz.license.model.entity.full"/>
<property name="dataSource" ref="full-license-db-dataSource" />
<property name="hibernateProperties">
<props>
<prop key="hibernate.hbm2ddl.auto">${hibernate.hbm2ddl.auto} </prop>
<prop key="hibernate.dialect">${hibernate.dialect}</prop>
<prop key="hibernate.show_sql">${hibernate.show_sql}</prop>
</props>
</property>
</bean>
<bean id="trial-license-db-dataSource"
class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="${db.driver}" />
<property name="url" value="${db.trial.url}" />
<property name="username" value="${db.trial.username}" />
<property name="password" value="${db.trial.password}" />
</bean>
<bean id="sessionFactoryTrial"
class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
<property name="dataSource" ref="trial-license-db-dataSource" />
<property name="packagesToScan" value="ci.xyz.license.model.entity.trial"/>
<property name="hibernateProperties">
<props>
<prop key="hibernate.hbm2ddl.auto">${hibernate.hbm2ddl.auto}</prop>
<prop key="hibernate.dialect">${hibernate.dialect}</prop>
<prop key="hibernate.show_sql">${hibernate.show_sql}</prop>
</props>
</property>
</bean>
<bean id="transactionManagerTrial"
class="org.springframework.orm.hibernate4.HibernateTransactionManager">
<property name="sessionFactory" ref="sessionFactoryTrial" />
</bean>
<bean id="transactionManagerFull" class="org.springframework.orm.hibernate4.HibernateTransactionManager">
<property name="sessionFactory" ref="sessionFactoryFull" />
</bean>
<tx:annotation-driven transaction-manager="transactionManagerFull" />
<tx:annotation-driven transaction-manager="transactionManagerTrial" />
Web.xml
<filter>
<filter-name>hibernateFilterTrial</filter-name>
<filter-class>org.springframework.orm.hibernate4.support.OpenSessionInViewFilter</filter-class>
<init-param>
<param-name>sessionFactoryBeanName</param-name>
<param-value>sessionFactoryTrial</param-value>
</init-param>
</filter>
<filter-mapping>
<filter-name>hibernateFilterTrial</filter-name>
<url-pattern>/*</url-pattern>
<dispatcher>REQUEST</dispatcher>
<dispatcher>FORWARD</dispatcher>
</filter-mapping>
<filter>
<filter-name>hibernateFilterFull</filter-name>
<filter-class>org.springframework.orm.hibernate4.support.OpenSessionInViewFilter</filter-class>
<init-param>
<param-name>sessionFactoryBeanName</param-name>
<param-value>sessionFactoryFull</param-value>
</init-param>
</filter>
<filter-mapping>
<filter-name>hibernateFilterFull</filter-name>
<url-pattern>/*</url-pattern>
<dispatcher>REQUEST</dispatcher>
<dispatcher>FORWARD</dispatcher>
</filter-mapping>
Java Code of Dao for quick reference
@Repository
@Scope(BeanDefinition.SCOPE_PROTOTYPE)
public abstract class AbstractHibernateDao implements IBaseDao {
private static final Log logger = LogFactory.getLog(AbstractHibernateDao.class);
@Autowired
protected SessionFactory sessionFactoryFull;
@Autowired
protected SessionFactory sessionFactoryTrial;
protected Class<T> persistentClass;