Connecting to different Data Sources

Connecting to different Data Sources

Often we use to come across a situation as dozen of application running in any enterprises and each of them meeting some specific need of the organization. Now suddenly we may need to create a portal in which we should not disturb any of the existing legacy applications and databases they connect to. At the same time, our new portal has to fetch information from these different databases originally used by the legacy system.

connecting to different Data Source can be achieved in following steps :

Step 1: put the below code either in portal “portal-setup-wizard.properties” or “portal-ext.properties” under PORTAL_HOME and restart the server.

jdbc.anotherdbconfig.driverClassName=com.mysql.jdbc.Driver jdbc.anotherdbconfig.username=root jdbc.anotherdbconfig.password=root jdbc.anotherdbconfig.url=jdbc:mysql://localhost:3306/anotherdatabase?useUnicode=true&characterEncoding=UTF-8&useFastDateParsing=false

the above entries is use for another data base.

NOTE : you can connect to any kind of data base like sql server, mysql etc. All you have to do is provide the data base configuration in the above mentioned file.

Step 2 : In your portlet create a new file “ext-spring.xml” inside “WEB- INF/src/META-INF” and paste the below contents.

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

Step 3 : Define a new entity in Service.xml which will mapped to another data base but not the default one.

<entity name="TemporaryEmployee" table="TemporaryEmployee" local-service="true" remote-service="false" data-source="anotherDataSource" session-factory="anotherSessionFactory"> <!-- PK fields --> <column name="employeeId" type="long" primary="true" /> <!-- Other fields --> <column name="employeeName" type="String" /> <column name="employeeAddress" type="String" /> </entity>

In the above entity there is some new attribute data-source and session-factory are mandatory. These are the attributes which will look for ext-spring.xml file and fetch the configuration for connecting another data base. Save file and re-generate service layer.

Step 4: Unlike the entities that are mapped to the default data-source, the table for this

entity will not be created automatically. We need to create it manually as i have use mysql data base so accordingly i am writing query here.

CREATE TABLE `anotherdatabase`.`temporaryemployee` (
  `employeeId` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `employeeName` VARCHAR(45) NOT NULL DEFAULT '',
  `employeeAddress` VARCHAR(45) NOT NULL DEFAULT '',
  PRIMARY KEY(`employeeId`)
)
ENGINE = InnoDB;

Step 5 : Now create an action class as below : 

package com.liferay.portlet; import java.io.IOException; import javax.portlet.ActionRequest; import javax.portlet.ActionResponse; import javax.portlet.PortletException; import com.liferay.counter.service.CounterLocalServiceUtil; import com.liferay.portal.kernel.exception.SystemException; import com.liferay.portal.kernel.util.ParamUtil; import com.liferay.slayer.model.TemporaryEmployee; import com.liferay.slayer.model.impl.TemporaryEmployeeImpl; import com.liferay.slayer.service.TemporaryEmployeeLocalServiceUtil; import com.liferay.util.bridges.mvc.MVCPortlet; public class PortletAction extends MVCPortlet { @Override public void processAction(ActionRequest actionRequest, ActionResponse actionResponse) throws IOException, PortletException { String employeeName = ParamUtil.getString(actionRequest, "employeeName"); String employeeAddress = ParamUtil.getString(actionRequest, "employeeAddress"); long employeeId = 0l; try { employeeId = CounterLocalServiceUtil.increment(TemporaryEmployee.class.getName()); } catch (SystemException e) { e.printStackTrace(); } TemporaryEmployee employee = new TemporaryEmployeeImpl(); employee.setEmployeeId(employeeId);; employee.setEmployeeName(employeeName); employee.setEmployeeAddress(employeeAddress); try { TemporaryEmployeeLocalServiceUtil.addTemporaryEmployee(employee); } catch (SystemException e) { e.printStackTrace(); } } }

Step 6 : And jsp should be as follows:

<%@ taglib uri="http://java.sun.com/portlet_2_0" prefix="portlet" %> <%@ taglib uri="http://alloy.liferay.com/tld/aui" prefix="aui" %> <portlet:defineObjects /> <portlet:actionURL var="actionURL"></portlet:actionURL> <aui:form name="fm" action="<%=actionURL.toString()%>"> <aui:input name="employeeName" type="text"></aui:input> <aui:input name="employeeAddress" type="text"></aui:input> <aui:button type="submit" name="submit"></aui:button> </aui:form>

now after building services and deploying portlet try to save a record.

check in your newly create table that record has inserted successfully.

In the same way you can fetch record from already created Table  all you have to do is create entity and give attribute table with same name for the table of which data you want to fetch.This way you can write application code to fetch data from another Data Base table.