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.
