Liferay is easy: Connecting to Different Database in Liferay

We already know how to create service builder in Liferay. When we use service builder all our tables goes to default database that is mention in portal-ext.properties or portal-setup-wizard.properties.

But sometimes we need to create tables in a separate database so that all liferay related tables are in default database and our custom tables in another database.So today we will see how to connect Liferay with 2 databases.

So lets start this step by step:-

Step 1:-Create Project with Service.xml
You can create a project and create service.xml inside this.For detail you may refer Service Builder in Liferay. Now open service.xml and paste this content and build service:-

service.xml

	<?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.services" auto-namespace-tables="false">
	<author>aditya.bhardwaj</author>
	<namespace>AnotherData</namespace>

	<entity name="AnotherDatabase" local-service="true" remote-service="false" data-source="secondDataSource" table="anotherdatabase">
	<column name="aid" type="int" primary="true"></column>
	<column name="aname" type="String"></column>
	</entity>
	</service-builder>

view raw service.xml hosted with ❤ by GitHub

Explanation:-
We already know Service Builder in Detail. Here we use one extra attribute data-source which help us to connect with different database.

Step 2:-Create table in database
Normally when we use service builder table is created automatically but when we connect to different database we need to create table manually.You can use this command for table creation:-

	CREATE TABLE `anotherdatabase` (
	`aid` INT(11) NOT NULL,
	`aname` VARCHAR(50) NOT NULL,
	PRIMARY KEY (`aid`)
	)
	ENGINE=InnoDB
	;

view raw anotherdatabase.sql hosted with ❤ by GitHub

Step 3:-Create datasource
Open your META-INF folder and create a file ext-spring.xml as:-

ext-spring.xml

	<?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="secondDataSource" lazy-init="true" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
	<property name="driverClassName" value="com.mysql.jdbc.Driver" />
	<property name="url" value="jdbc:mysql://localhost/aditya2?useUnicode=true"/>
	<property name="username" value="root" />
	<property name="password" value="root" />
	</bean>

	<bean id="liferayHibernateSessionFactory" class="com.liferay.portal.spring.hibernate.PortletHibernateConfiguration">
	<property name="dataSource" ref="secondDataSource" />
	</bean>

	<bean id="liferayTransactionManager" class="org.springframework.orm.hibernate3.HibernateTransactionManager">
	<property name="dataSource" ref="secondDataSource" />
	<property name="globalRollbackOnParticipationFailure" value="false" />
	<property name="sessionFactory" ref="liferayHibernateSessionFactory" />
	</bean>
	</beans>

view raw ext-spring.xml hosted with ❤ by GitHub

Explanation:-
1)Here you can see bean id is secondDataSource which is exactly same as data-source attribute of service.xml. My default database is aditya and second database is aditya2.

2)You can change your database name, url, password etc according to your database.

3)If you want to perform only get operation you can delete:-

<bean id="liferayTransactionManager" class="">
<property name="" ref="" />
<property name="" value="" />
<property name="" ref="" />
</bean>

Note:- When you remove liferayTransactionManager bean you cannot perform add,delete and update operation.

Step 4:-Use the Services
Now simply create you portlet and use the services. Open your controller and paste this:-

DbTest.java

	package com.test;

	import java.io.IOException;
	import javax.portlet.PortletException;
	import javax.portlet.RenderRequest;
	import javax.portlet.RenderResponse;
	import com.liferay.portal.kernel.exception.SystemException;
	import com.liferay.util.bridges.mvc.MVCPortlet;
	import com.services.model.AnotherDatabase;
	import com.services.model.impl.AnotherDatabaseImpl;
	import com.services.service.AnotherDatabaseLocalServiceUtil;

	public class DbTest extends MVCPortlet {
	@Override
	public void doView(RenderRequest renderRequest,RenderResponse renderResponse) throws IOException, PortletException {
	AnotherDatabase another = new AnotherDatabaseImpl();
	another.setAid(1);
	another.setAname("Data goes to another database");

	try {
	AnotherDatabaseLocalServiceUtil.addAnotherDatabase(another);
	System.out.println("Successfully Added.................");

	System.out.println("Data Count ====>"+AnotherDatabaseLocalServiceUtil.getAnotherDatabasesCount());
	} catch (SystemException e) {
	e.printStackTrace();
	}

	super.doView(renderRequest, renderResponse);
	}

	}