Connect to Another Datasource/Database Schema from Portlet with service

Hi Folks,

Many developer/client's need to separate the liferay database table and custom database table. so, here is the solution to maintain this approach. for that we maintain two data source/schema for liferay and custom database. we can achieve this by trying to  following steps -

1) portal-ext.properties

Add two connection properties for maintain two differance database source/schema. for example

###### MySQL Connection#######
## Liferay default database connection
jdbc.default.driverClassName=com.mysql.jdbc.Driver
jdbc.default.url=jdbc:mysql://localhost/test?useUnicode=true&characterEncoding=UTF-8&useFastDateParsing=false
jdbc.default.username=root
jdbc.default.password=root

## Your new database connection
jdbc.test.driverClassName=com.mysql.jdbc.Driver
jdbc.test.url=jdbc:mysql://localhost/mytest?useUnicode=true&characterEncoding=UTF-8&useFastDateParsing=false
jdbc.test.username=root
jdbc.test.password=root

2) ext-spring.xml

    create ext-spring.xml in docroot\WEB-INF\src\META-INF\ of you portlet to connect with other database shema default then liferay. copy the below content and paste in newly created file.
   
<<?xml version="1.0"?>
<<beans
    default-destroy-method="destroy"
    default-init-method="afterPropertiesSet"   
    xmlns="http://www.springframework.org/schema/beans"
    xmlns:aop="http://www.springframework.org/schema/aop"
    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"
>

    <<bean id="testDataSource" class="org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy">
        <<property name="targetDataSource">
            <<bean class="com.liferay.portal.dao.jdbc.util.DataSourceFactoryBean">
                <<property name="propertyPrefix" value="jdbc.test."/>
            <</bean>
        <</property>
    <</bean>
   
   
    <<bean id="testHibernateSessionFactory" class="com.liferay.portal.spring.hibernate.PortletHibernateConfiguration" lazy-init="true">
        <<property name="dataSource" ref="testDataSource" />       
    <</bean>
   

    <<bean id="testSessionFactory" class="com.liferay.portal.dao.orm.hibernate.SessionFactoryImpl" lazy-init="true">       
        <<property name="sessionFactoryImplementor" ref="testHibernateSessionFactory" />
    <</bean>
   
    <<bean id="testTransactionManager" class="org.springframework.orm.hibernate3.HibernateTransactionManager" lazy-init="true">
        <<property name="dataSource" ref="testDataSource" />
        <<property name="globalRollbackOnParticipationFailure" value="false"/>
        <<property name="sessionFactory" ref="testHibernateSessionFactory" />
    <</bean>

<</beans>

3) service.xml
    create entity with the parameter data-source,session-factory,tx-manager to connect other then default database. for example....
   
    <<entity name="MyTable" local-service="true" remote-service="false" data-source="testDataSource" session-factory="testSessionFactory" tx-manager="testTransactionManager">


4) service.properties

   check the entry of ext-spring.xml in \docroot\WEB-INF\src\service.properties file(some version of liferay have already this entry). if it is not there then append spring.configs properties with the value "WEB-INF/classes/META-INF/ext-spring.xml"
  
  
 At Last......

 After deploying portlet, custom portlet are able to connect with other then default database source/schema .

 NOTE :
 1)This example is  based on Liferay 6.0 and 6.0.29 tomcat bundle. you have to do specific changes in any of file(.xml) if needed based on Liferay version.
 2)While you connect with other then default liferay database source/schema. service-builder can't create automatically table in database. you have to create table manually. then after you can CURD operation for you custom table same as liferay default database.