SrikanthLiferay: Connecting to different database using Liferay Service Builder

Using ServiceBuilder functionality ,But Persistening in a Different DB other then Liferay Portal Default one.

Generally when we create a Custom portlet using Service builder or use Service Builder to generate code for persistence and service/model layers. The logic we use is persisting the data in Liferay DB .i.e... DB configured in portal-ext. properties.

But in my case I had a requirement where in, Client had a separate DB schema and he wanted complete separation of logic from Liferay DB. All he needed is Crud operations on those tables.

We also have requirement that Client wants there Data on Separate Schema or Separate DB.
But In Liferay how we can connect with different DB?

After searching and analyzing different blogs, I came to conclusion and starting implementing them.

As we are using service-builder, it means that you need code for 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.

So let’s start our code with basic data model as shown below.

Step 1: Service.xml :

<finder-column name="organizationId"></finder-column>

</finder>

</entity>

Once you run the build service, it will generate all the code required for model, service and persistence layer as shown below.

create table ORGANIZATION (

id_ LONG not null primary key,

organizationId VARCHAR(75) null,

name VARCHAR(75) null,

addrId LONG,

created DATE null,

trash VARCHAR(75) null

);

Step 2:

As we define the jdbc properties for the liferayportal in portal-ext.properties, same way add for the external DB or separate schema as shown below:-

########## Liferay Portal MySQL Database Configuration ##########

jdbc.default.driverClassName=com.mysql.jdbc.Driver

jdbc.default.url=jdbc:mysql://localhost/liferay6_1_2?useUnicode=true&characterEncoding=UTF-8&useFastDateParsing=false

jdbc.default.username=root

jdbc.default.password=root

########## External DB MySQL Database Configuration ##########

#jdbc.tcs.driverClassName=com.mysql.jdbc.Driver

#jdbc.tcs.url=jdbc:mysql://localhost/tcsdb?useUnicode=true&characterEncoding=UTF-8&useFastDateParsing=false

#jdbc.tcs.username=root

#jdbc.tcs.password=root

########### External DB Oracle Database Configuration ##########

jdbc.tcs.driverClassName=oracle.jdbc.driver.OracleDriver

jdbc.tcs.url=jdbc:oracle:thin:@localhost:1521:mdb

jdbc.tcs.username=SYSTEM

jdbc.tcs.password=Liferay61

Explanation :-

Here based on your external DB type, add the DB configuration in above property file.

In my case, I used Oracle DB as shown above.
The first four line starts with the jdbc.default, it means that it was associated with the Default Data Source. So it will connect to the Default Liferay DB.

Last four line line starts with the jdbc.tcs, which does not belong to the Default DB. It will be connected to some other DB whose entry will be in ext-spring.xml.

Note: I have changed the DB configuration attribute from “default” to “tcs”.

Step 3:

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 file as shown below :-

spring.configs=\

META-INF/base-spring.xml,\

META-INF/hibernate-spring.xml,\

META-INF/infrastructure-spring.xml,\

META-INF/management-spring.xml,\

META-INF/util-spring.xml,\

META-INF/jpa-spring.xml,\

META-INF/executor-spring.xml,\

META-INF/audit-spring.xml,\

META-INF/cluster-spring.xml,\

META-INF/editor-spring.xml,\

META-INF/jcr-spring.xml,\

META-INF/ldap-spring.xml,\

META-INF/messaging-core-spring.xml,\

META-INF/messaging-misc-spring.xml,\

META-INF/mobile-device-spring.xml,\

META-INF/notifications-spring.xml,\

META-INF/poller-spring.xml,\

META-INF/rules-spring.xml,\

META-INF/scheduler-spring.xml,\

META-INF/scripting-spring.xml,\

META-INF/search-spring.xml,\

META-INF/workflow-spring.xml,\

META-INF/counter-spring.xml,\

META-INF/mail-spring.xml,\

META-INF/portal-spring.xml,\

META-INF/portlet-container-spring.xml,\

META-INF/staging-spring.xml,\

META-INF/virtual-layouts-spring.xml,\

#META-INF/dynamic-data-source-spring.xml,\

#META-INF/shard-data-source-spring.xml,\

#META-INF/memcached-spring.xml,\

#META-INF/monitoring-spring.xml,\

META-INF/ext-spring.xml

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 shown below :-

This is the structure were we need to create the ext-spring.xml file.

Now ext-spring.xml contains below lines.

<?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">

</bean>

<bean id="tcsHibernateSessionFactory" class="com.liferay.portal.spring.hibernate.PortletHibernateConfiguration"

lazy-init="true">

</bean>

</bean>

</bean>

</property>

</bean>

</beans>

Note: Here data-source, session-factory, tx-manager has same ID or Name which we will defined in service.xml file under <Entity>.

Step 4:

Now the last and the final step you need to do is that you need to modify the existing service.xml as follows

And re-build the services .

Explaination:

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.

You are now connected with your external DB.