Multiple database connection in Liferay

Liferay allows us to connect to multiple database at the same time. Follow the below steps for that.

1. In portal-ext.properties file make a entry.

--------------------------------------------------------------------

// default entry for default database connection

jdbc.default.driverClassName=com.mysql.jdbc.Driver
jdbc.default.username=root
jdbc.default.password=root
jdbc.default.url=jdbc\:mysql\://localhost/defaultdb?useUnicode\=true&characterEncoding\=UTF-8&useFastDateParsing\=false

// new entries for external db connection it will be connected by giving a entry in ext-spring.xml

jdbc.external.driverClassName=com.mysql.jdbc.Driver
jdbc.external.username=root
jdbc.external.password=root
jdbc.external.url=jdbc:mysql://localhost/externaldb?useUnicode=true&characterEncoding=UTF-8&useFastDateParsing=false

--------------------------------------------------------------------

2) As we are using service-builder, it means that you need new tables other than liferay default DB. So it requires for you to create new plugins project and in that you need to create service.xml under webapps/WEB-INF and with the help of ANT(ant build-service) or MAVEN (mvn liferay:build-service) you will able to create full structure for your service. But still it is pointing to the default DB. 

Note:- In externaldb you have to create tables manualy.


Now you need to create a new file ext-spring.xml under WEB-INF/src/META-INF dir. Inside META-INF folder you will find couple of xml files whose entry will be there in liferay portal.properties.If you notice the order of xml file loading in portal.properties file, then you will find that the last file is ext-spring.xml is loaded. So we will now create ext-spring.xml and putting all transaction,datasource and sessionfactory related changed on that file as below :-

--------------------------------------------------------------------
<?xml version="1.0"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:aop="http://www.springframework.org/schema/aop" xmlns:context="http://www.springframework.org/schema/context"
xmlns:tx="http://www.springframework.org/schema/tx" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.0.xsd http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-2.5.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.0.xsd">

<aop:config>
<aop:pointcut id="transactionOperation" expression="bean(*Service.impl)" />
<aop:advisor advice-ref="transactionAdvice" pointcut-ref="transactionOperation" />
</aop:config>

<bean id="basePersistence" abstract="true">
<property name="dataSource" ref="anotherDataSource" />
<property name="sessionFactory" ref="anotherSessionFactory" />
</bean>

<bean id="transactionAdvice" class="org.springframework.transaction.interceptor.TransactionInterceptor">
<property name="transactionManager" ref="anotherTransactionManager" />
<property name="transactionAttributeSource">
<bean class="org.springframework.transaction.annotation.AnnotationTransactionAttributeSource">
<constructor-arg>
<bean class="com.liferay.portal.spring.annotation.PortalTransactionAnnotationParser" />
</constructor-arg>
</bean>
</property>
</bean>

<bean id="anotherHibernateSessionFactory" class="com.liferay.portal.spring.hibernate.PortletHibernateConfiguration" lazy-init="true">
<property name="dataSource" ref="anotherDataSource" />
</bean>

<bean id="anotherSessionFactory" class="com.liferay.portal.dao.orm.hibernate.SessionFactoryImpl" lazy-init="true">
<property name="sessionFactoryImplementor" ref="anotherHibernateSessionFactory" />
</bean>

<bean id="anotherTransactionManager" class="org.springframework.orm.hibernate3.HibernateTransactionManager" lazy-init="true">
<property name="dataSource" ref="anotherDataSource" />
<property name="globalRollbackOnParticipationFailure" value="false" />
<property name="sessionFactory" ref="anotherHibernateSessionFactory" />
</bean>

<bean id="anotherDataSource" class="org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy">
<property name="targetDataSource">
<bean class="com.liferay.portal.dao.jdbc.util.DataSourceFactoryBean">
<property name="propertyPrefix" value="jdbc.external." />
</bean>
</property>      
    </bean>

</beans>

--------------------------------------------------------------

3) Now you need to modify the existing service.xml as follows :-

---------------------------------------------------------------------------

<service-builder package-path="com.external.liferay">
<namespace>external</namespace>
<entity data-source="anotherDataSource" local-service="true" name="externaldb" remote-service="false" session-factory="anotherSessionFactory" tx-manager="anotherTransactionManager">
......
......
</entity>
</service-builder>

---------------------------------------------------------


The data-source value specifies the data source target that is set to the persistence class. The default value is the Liferay data source. This is used in conjunction with session-factory. 

The session-factory value specifies the session factory that is set to the persistence class. The default value is the Liferay session factory. This is used in conjunction with data-source. 

The tx-manager value specifies the transaction manager that Spring uses. The default value is the Spring Hibernate transaction manager that wraps the Liferay data source and session factory. 

If the local-service value is true, then the service will generate the local interfaces for the service. The default value is false.

If the remote-service value is true, then the service will generate remote interfaces for the service. The default value is true.

You can use the local-service and remote-service attribute according to your needs.