Sunday, November 16, 2014

Access multiple Datasources in Liferay 6.2


In this post I’ll show you how to use, in a Service Builder Portlet, an alternative datasource. By example, lets supose that we have two entities: one stored in the same schema than the liferay portal and another one which will be in a diferent database.







To accomplish this, I’ll specify jdbc properties in the portal-ext.properties to access both datasources.

The properties will be the following:

jdbc.default.driverClassName=com.mysql.jdbc.Driver
jdbc.default.url=jdbc:mysql://localhost:3306/liferay_db
jdbc.default.username=
jdbc.default.password=

jdbc.externaldbconfig.driverClassName=com.mysql.jdbc.Driver
jdbc.externaldbconfig.url=jdbc:mysql://localhost:3306/externaldatabase
jdbc.externaldbconfig.username=
jdbc.externaldbconfig.password=


As you can see, I use the prefix “externaldbconfig” to name the properties for the external datasource.

Now, I’ll define the entities of our model in the service.xml file of the portlet.You will notice that I specify the alternative datasource used for the external entities. Entities with no data-source attribute will use the default Liferay datasource.

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE service-builder PUBLIC "-//Liferay//DTD Service Builder 6.2.0//EN" "http://www.liferay.com/dtd/liferay-service-builder_6_2_0.dtd">
<service-builder package-path="com.rlopez.multidatasource">
<namespace>mds</namespace>
<entity name="liferayDSTable" local-service="true" remote-service="true">
<column name="id" type="long" primary="true" />
<column name="description" type="String" />
</entity>
<entity name="externalDSTable" table="externalDSTable"  local-service="true" remote-service="true" data-source="externalDataSource" session-factory="externalSessionFactory">
<column name="id" type="long" primary="true" />
<column name="description" type="String" />
</entity>
</service-builder>


As we are defining a new datasource, well have also to define beans associated to this new externalDataSource. This is done in the ext-spring.xml file, located in the WEB-INF/src/META-INF folder in the portlet.

It’s important to make sure that the ext-spring.xml file is included in the spring configuration section in the service.properties file.


    spring.configs=\
        WEB-INF/classes/META-INF/base-spring.xml,\
        \
        WEB-INF/classes/META-INF/hibernate-spring.xml,\
        WEB-INF/classes/META-INF/infrastructure-spring.xml,\
        \
        WEB-INF/classes/META-INF/cluster-spring.xml,\
        \
        WEB-INF/classes/META-INF/portlet-spring.xml,\
        \
        WEB-INF/classes/META-INF/shard-data-source-spring.xml,\
        \
        WEB-INF/classes/META-INF/ext-spring.xml


The resulting ext-spring.xml file goes as follows:

<?xml version="1.0"?>
<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="externalDataSource"
class="org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy">
<property name="targetDataSource" ref="externalDataSourceWrapper" />
</bean>
<bean id="externalDataSourceImpl"
class="com.liferay.portal.dao.jdbc.spring.DataSourceFactoryBean">
<property name="propertyPrefix" value="jdbc.externaldbconfig." />
</bean>
<bean id="externalDataSourceWrapper" class="com.liferay.portal.dao.jdbc.util.DataSourceWrapper">
<constructor-arg ref="externalDataSourceImpl" />
</bean>
<bean class="com.liferay.portal.dao.jdbc.util.DataSourceSwapper">
<property name="liferayDataSourceWrapper" ref="externalDataSourceWrapper" />
</bean>
<bean id="externalHibernateSessionFactory" 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="externalDataSource" />
</map>
</constructor-arg>
</bean>
<bean id="externalSessionFactory" 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="externalDataSource" />
<entry key="sessionFactoryClassLoader" value-ref="portletClassLoader" />
<entry key="sessionFactoryImplementor" value-ref="externalHibernateSessionFactory" />
</map>
</constructor-arg>
</bean>
</beans>


Something important to take into account is that Liferay will not create the table associated to external datasources, so you’ll have to create it manually. In this case I need to create the table named “externalDSTable”. Liferay will manage tables using the default datasource as usual.

Finally, you’ll have to generate the portlet services by means of the build-service ant target, create the code to check the data from both tables inside a jsp file (for example) and then deploy the portlet.


<%@page import="com.rlopez.multidatasource.service.liferayDSTableLocalServiceUtil"%>
<%@page import="com.rlopez.multidatasource.model.liferayDSTable"%>
<%@page import="com.rlopez.multidatasource.model.externalDSTable"%>
<%@page import="com.rlopez.multidatasource.service.externalDSTableLocalServiceUtil"%><%@ taglib uri="http://java.sun.com/portlet_2_0" prefix="portlet" %>

<portlet:defineObjects />

<h3>Elements in external table: <%=externalDSTableLocalServiceUtil.getexternalDSTablesCount()%></h3>
<h3>Elements in liferay datasource table: <%=liferayDSTableLocalServiceUtil.getliferayDSTablesCount()%></h3>

As you can see it’s very easy to define different datasources in a Service Builder portlet. Regards!


3 comments:

Subscribe to: Post Comments (Atom)