More than one database connection with liferay

Hi
  Today my post will describe how to connect with more than one database in Liferay. We can connect simultaneously another database too, means at a time we can connect/access lportal the default database and other database.
      

Step 1: First we have to modify our portal-ext.properties file.
The snippet is

 ###  #MS SQL SERVER CONFIGURATION TO CONNECT OTHER DATABASE
    
      jdbc.anotherdbconfig.driverClassName=net.sourceforge.jtds.jdbc.Driver
      jdbc.anotherdbconfig.url=jdbc:jtds:sqlserver://localhost:1433/mydatabase
      jdbc.anotherdbconfig.username=sa
      jdbc.anotherdbconfig.password=Password

Here I am using MS SQL SERVER EXPRESS 2008 r2
default user name is "sa"
and place of Password you have to provide your own password
and as usual after modification in portal-ext.properties file you have to restart the server then you can feel the change.

Step 2: Create one plugin portlet or you can also use your existing one, then create service builder.      Here my service builder code is
service.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE service-builder PUBLIC "-//Liferay//DTD Service Builder 6.1.0//EN" "http://www.liferay.com/dtd/liferay-service-builder_6_1_0.dtd">
<service-builder package-path="webcom.service.layer">
    <author>asif</author>
    <namespace>webcom</namespace>

    <entity name="Asif" local-service="true" remote-service="false" table="asif" data-source="anotherDataSource"  session-factory="anotherSessionFactory">

        <!-- PK fields -->

        <column name="rollId" type="long" primary="true" />

        <!-- Audit fields -->

        <column name="FatherName" type="String" />
        <column name="MotherName" type="String" />
        <column name="Name" type="String" />
   
    </entity>
   
</service-builder>
name="Asif" will describe the name of entity or table class inside your portal and table="asif" is a command to the service builder to create a table of this name only. If you don't use table="somename" then the table name in database is "namespace_entityname".
Then build the service
Now the most important work is to create a file by name ext-spring.xml
the path /docroot/web-inf/src/META-INF/ext-spring.xml
this docroot is the very first folder in your portlet project in which your all class file will exist.

<?xml version="1.0" encoding="UTF-8"?>

<beans
    default-destroy-method="destroy"
    default-init-method="afterPropertiesSet"
    xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd"
>

       <bean id="anotherDataSource" class="org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy">
              <property name="targetDataSource" ref="anotherDataSourceWrapper" />
       </bean>
   
       <bean id="anotherDataSourceImpl" class="com.liferay.portal.dao.jdbc.spring.DataSourceFactoryBean">
              <property name="propertyPrefix" value="jdbc.anotherdbconfig." />
       </bean>
      
       <bean id="anotherDataSourceWrapper" class="com.liferay.portal.dao.jdbc.util.DataSourceWrapper">
              <constructor-arg ref="anotherDataSourceImpl" />
       </bean>
     
       <bean class="com.liferay.portal.dao.jdbc.util.DataSourceSwapper">
              <property name="liferayDataSourceWrapper" ref="anotherDataSourceWrapper" />
       </bean>
      
       <bean id="anotherHibernateSessionFactory" class="com.liferay.portal.kernel.spring.util.SpringFactoryUtil"
              factory-method="newBean">
              <constructor-arg value="com.liferay.portal.spring.hibernate.PortletHibernateConfiguration" />
              <constructor-arg>
                     <map>
                           <entry key="dataSource" value-ref="anotherDataSource" />
                     </map>
              </constructor-arg>
       </bean>
      
       <bean id="anotherSessionFactory" class="com.liferay.portal.kernel.spring.util.SpringFactoryUtil" factory-method="newBean">
              <constructor-arg
                     value="com.liferay.portal.dao.orm.hibernate.PortletSessionFactoryImpl" />
              <constructor-arg>
                     <map>
                           <entry key="dataSource" value-ref="anotherDataSource" />
                           <entry key="sessionFactoryClassLoader" value-ref="portletClassLoader" />
                           <entry key="sessionFactoryImplementor" value-ref="anotherHibernateSessionFactory" />
                     </map>
              </constructor-arg>
       </bean>

</beans>
anotherdbconfig. Is the propertyPrefix which we have mention in ext-spring.xml file

Now we have to provide this information to entity which we have configured in service.xml .

In enity tag we have two attributes from that we can explitly said about datasource and session factory information .

<entity name="TableFromAnotherDataSource" table="TableFromAnotherDataSource"  

 local-service="true" remote-service="true" data-source="anotherDataSource"  

session-factory="anotherSessionFactory">

           <column name="IFADSId" type="long" primary="true" />

           <column name="Description" type="String" />

  </entity>

Note: When we use other data source for plugin portlet when we run service builder then tables creation script is not create so we have to create table manually in database if the table is new.

If you need create table script you can see in the class entityModelImpl.java

public static final String TABLE_SQL_CREATE = "create table TableFromAnotherDataSource (IFADSId LONG not null primary key,Description VARCHAR(75) null)";

You can also find data source and session factory that is used by your Entity class

public static final String DATA_SOURCE = "anotherDataSource";

public static final String SESSION_FACTORY = "anotherSessionFactory"; 

Hope this will helpful for you.