Service Builder - External Database

An example of a Service Builder project for Liferay 7.3 Community Edition/DXP that uses an external data source.

This project is a fork of the sb-extdb project realized by David H Nebinger. You can see the blog post for more info: Liferay 7 - Service Builder and External Databases

This fork has been updated to support Liferay 7.3. For more information, I invite you to read this documentation. Connecting Service Builder to External Databases

Warning! Since version 7.2 a little bit has changed. There are two different ways to create the connection:

1. DataSourceProvider: This approach involves implementing a DataSourceProvider ServiceProviderInterface (SPI). This way requires the fewest files and steps and works regardless of whether your Service Builder module uses the ds or spring dependency injector;
2. Spring Beans: Configure the connection using Spring XML files. This approach only works with Service Builder modules that use the spring dependency injection option.

This project uses the second option, the one via Spring Beans.

For this reason I invite you to read these two documents:

1. Connecting the Data Source Using a DataSourceProvider
2. Connecting the Data Source Using Spring Beans

In this documentation, I show the case that the Liferay database is PostgreSQL, while the external Microsoft SQL Server database.

1. Quick Start

The external entity to which we want to access from Liferay has the following structure.

Attribute	Primary	Type	Description
UUID	X	String	Universal Unique Identifier of the Liferay User
Screen Name		String	The Liferay user Screen Name
System Name		String	IP Address, Hostname, Virtual Host or FQDN of the Liferay instance
Last Login		Datetime	We'll track the date of last login
Total Login		Long	We'll track the total number of individual logins for the user
Shortest Time Between Logins		Long	And we'll also track the shortest time between logins
Longest Time Between Logins		Long	Let's also track the longest time between logins

The SQL code below shows the creation of the ExtDB_UserLogin table on the external db, in this case SQL Server.

create table ExtDB_UserLogin
(
    uuid_ VARCHAR(75) not null primary key,
    screenName VARCHAR(75) null,
    systemName VARCHAR(75) null,
    lastLogin DATETIME null,
    totalLogins BIGINT,
    longestTimeBetweenLogins BIGINT,
    shortestTimeBetweenLogins BIGINT
);

The table shows the modules of the project is their purpose.

Module	Type	Description
sb-extdb-api	Service Builder	API module for the external database table
sb-extdb-service	Service Builder	Service implementation for the external database table
sb-extdb-postlogin	Lifecycle Action	Post login action for the tracking the login information into the external database table

The service.xml file shows the definition of the UserLogin entity that mirrors the external database table.

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

<service-builder dependency-injector="spring" package-path="com.liferay.example.servicebuilder.extdb">

	<!-- Define a namespace for our example -->
	<namespace>ExtDB</namespace>

	<!-- Define an entity for tracking login information. -->
	<entity
		cache-enabled="false"
		data-source="extDataSource"
		local-service="true"
		name="UserLogin"
		remote-service="false"
		uuid="false"
	>
		<!-- session-factory="extSessionFactory" tx-manager="extTransactionManager" -->

		<!-- uuid of the user model is our primary key. -->
		<column name="uuid" primary="true" type="String" />

		<!-- screenName of the user model is our primary key. -->
		<column name="screenName" type="String" />

		<!-- SystemName of the user model is our primary key. -->
		<column name="systemName" type="String" />

		<!-- We'll track the date of last login -->
		<column name="lastLogin" type="Date" />

		<!-- We'll track the total number of individual logins for the user -->
		<column name="totalLogins" type="long" />

		<!-- Let's also track the longest time between logins -->
		<column name="longestTimeBetweenLogins" type="long" />

		<!-- And we'll also track the shortest time between logins -->
		<column name="shortestTimeBetweenLogins" type="long" />
	</entity>
</service-builder>

Below the configuration (portal-ext.properties) of the PostgreSQL database used by Liferay, while the external database is SQL Server.

    #
    # PostgreSQL
    #
    jdbc.default.driverClassName=org.postgresql.Driver
    jdbc.default.url=jdbc:postgresql://localhost:5432/lportal_73_ce_ga6_develop
    jdbc.default.username=liferay
    jdbc.default.password=liferay

    #
    # SQL Server 2017 as External DB
    #
    jdbc.ext.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
    jdbc.ext.url=jdbc:sqlserver://localhost;databaseName=lportal_dev_73_external_db
    jdbc.ext.username=liferay
    jdbc.ext.password=lportal@DEV@73

For the Liferay and external databases, I used two docker containers, one for PostgreSQL and one for SQL Server.

Refer to the liferay-portal-database-all-in-one-support project to add SQL Server support to Liferay. For the JDBC driver to use refer to the documentation of the specific database server.

The instructions below show the clone of the project, build and deploy on your Liferay instance 7.3 GA6. I always recommend checking from the compatibility matrix which databases are supported by Liferay.

Replace the $LIFERAY_HOME with your Liferay Home direcory.

$ git clone https://github.com/amusarra/sb-extdb
$ cd sb-extdb
$ ./gradlew clean deploy -Pauto.deploy.dir=$LIFERAY_HOME/deploy

Once the three modules have been deployed, if you try to log in to Liferay, the login action will be stored on the external database.

2020-11-10 11:12:28.870 INFO  [com.liferay.portal.kernel.deploy.auto.AutoDeployScanner][AutoDeployDir:271] Processing com.liferay.example.servicebuilder.extdb.api.jar
2020-11-10 11:12:28.889 INFO  [com.liferay.portal.kernel.deploy.auto.AutoDeployScanner][AutoDeployDir:271] Processing com.liferay.example.servicebuilder.extdb.postlogin.jar
2020-11-10 11:12:31.896 INFO  [com.liferay.portal.kernel.deploy.auto.AutoDeployScanner][AutoDeployDir:271] Processing com.liferay.example.servicebuilder.extdb.service.jar
2020-11-10 11:12:35.995 INFO  [fileinstall-directory-watcher][BundleStartStopLogger:46] STARTED com.liferay.example.servicebuilder.extdb.api_1.1.0 [1357]
2020-11-10 11:12:36.009 INFO  [fileinstall-directory-watcher][BundleStartStopLogger:46] STARTED com.liferay.example.servicebuilder.extdb.postlogin_1.1.0 [1358]
2020-11-10 11:12:42.195 INFO  [fileinstall-directory-watcher][DialectDetector:159] Using dialect com.liferay.portal.dao.orm.hibernate.SQLServer2008Dialect for Microsoft SQL Server 14.0
2020-11-10 11:12:42.401 INFO  [fileinstall-directory-watcher][BundleStartStopLogger:46] STARTED com.liferay.example.servicebuilder.extdb.service_1.1.0 [1359]

g! lb SB
START LEVEL 20
   ID|State      |Level|Name
 1014|Active     |   10|SB External DB API (1.1.0)|1.1.0
 1015|Active     |   10|SB External DB Post Login Hook (1.1.0)|1.1.0
 1016|Active     |   10|SB External DB Service (1.1.0)|1.1.0

The figure below shows the data entered in the external table from the post login hook.

The following readings may be useful:

1. Liferay 7 CE: How to add support for SQL Server DB
2. How to setup Docker container SQL Server 2017 for Liferay Development Environment
3. How to build a Docker Liferay 7.2 image with the SQL Server 2017 Database support
4. How to setup Docker container Oracle Database 12c for Liferay Development Environment
5. How to build a Docker Liferay 7.2 image with the Oracle Database support