Liferay is easy: Connect Service Builder to External Database in Liferay DXP

Today we will discuss how to connect service builder to external database using spring beans. We already know how to write Service Builder in Liferay DXP 7.2. So lets start this step by step :-

Step 1:- Create Service Builder
Here i create a small entity employee that contain only two column:-

service.xml

	<?xml version="1.0"?>
	<!DOCTYPE service-builder PUBLIC "-//Liferay//DTD Service Builder 7.2.0//EN" "http://www.liferay.com/dtd/liferay-service-builder_7_2_0.dtd">

	<service-builder dependency-injector="spring" package-path="com.test" auto-namespace-tables="false">
	<namespace>second</namespace>
	<entity name="Employee" local-service="true" remote-service="false" table="employee" data-source="secondDatabase">
	<column name="eid" primary="true" type="int"></column>
	<column name="ename" type="String"></column>
	</entity>
	</service-builder>

view raw service.xml hosted with ❤ by GitHub

Here two things are important:-
1)dpenedency-injector="spring" ie we are using spring and not ds
2)data-source="secondDatabase" ie we create another datasource.

When you build the service you see some errors for spring dependency.

For this add the dependency in build.gradle

compileOnly group: "com.liferay", name: "com.liferay.portal.spring.extender.api", version: "4.0.0"

And refresh gradle project.

Here a spring folder is automatically created inside META-INF

Step 2:- Specify the datasource
Now open your portal-ext.properties and enter the database properties:-

portal-ext.properties

	jdbc.ext.driverClassName=com.mysql.jdbc.Driver
	jdbc.ext.username=root
	jdbc.ext.password=root
	jdbc.ext.url=jdbc:mysql://localhost/datasourcetesting?characterEncoding=UTF-8&dontTrackOpenResources=true&holdResultsOpenOverStatementClose=true&serverTimezone=GMT&useFastDateParsing=false&useUnicode=true

view raw portal-ext.properties hosted with ❤ by GitHub

Explanation
Here we add our database properties .The .ext is our new database which we are using for creating connection.

Step 3:- Create Spring Bean
Create a ext-spring.xml file inside the sprint folder and paste this content:-

ext-spring.xml

	<?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 class="com.liferay.portal.dao.jdbc.spring.DataSourceFactoryBean" id="liferayDataSourceFactory" >
	<property name="propertyPrefix" value="jdbc.ext." />
	</bean>

	<bean class="org.springframework.jdbc.datasource.LazyConnectionDataSourceProxy" id="liferayDataSource" >
	<property name="targetDataSource" ref="liferayDataSourceFactory" />
	</bean>

	<alias alias="secondDatabase" name="liferayDataSource" />

	</beans>

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

Explanation:-
1)Here we use DataSourceFactoryBean where value is jdbc.ext. which we already create in out portal-ext.properties(Step 2).
2)alias="secondDatabase" this secondDatabase we already mention in our service.xml

RUN SERVICE BUILDER

Also restart server because we mention new database properties in portal-ext.properties.

Now you can use another database tables .

Final Project Structure