Service Builder with External DB

While Liferay has many wonderful features like DRAC, a docs repository, user self registration, and more, I still would like to store my data for custom portlets/applications in an external database. It’s like, you know, data independence! I found David H Nebinger’s blog for Liferay 7, Service Builder and External Databases. I struggled with a few issues, and I updated some things over Dave’s original post! First, this blog uses DataSourceProvider ServiceProviderInterface or I believe (SPI). Second, I had an issue with my service build referencing the correct table name, so I explicity specify the table name in service.xml. Third, I added a portlet that shows the data! Just be aware, that this portlet has no access controls and is for demo puposes only. I am sure that that could be another blog! And finally, I am not here to pick any bones. This is just the way I did it. Find the source to this writeup on my Github account! Before you start, make sure that you have blade installed! And, if you see errors or omissions or I just plain bungled, post below in the comments! Ok, on with the show!

Setup

Like every good relationship, each party respects the other’s independence. So, let’s create our external database first! We will put our table in, and connect to it using the hsql GUI tool. We will a sample record in it, view it and delete it. This is all before we tie it to Liferay

Put the database in c:\pkg\liferay\database. Adjust the path accordingly to your local system since we will connect to the HSQL server through a netowrk connection. Copy the hsql.jar out of Liferay into your local datatabase folder. Start the database server in a terminal window. Don’t close the terminal!

cd c:\pkg\liferay\database
copy C:\pkg\liferay\liferay-ce-portal-7.3.7-ga8\tomcat-9.0.43\lib\ext\hsql.jar .
java -cp .\hsql.jar org.hsqldb.server.Server --database.0 file:mydb --dbname.0 xdb

Start the HSQL GUI. Start it in a separate terminal window. Don’t close this terminal either!

java -cp .\hsql.jar org.hsqldb.util.DatabaseManager

At the dialog, connect with the following settings. You will put these settings in your portal-ext.properties later!

hsql dialog box
Dialog
HSQL Database Engine Server
url: jdbc:hsqldb:hsql://localhost/xdb
user: sa
password: 

Execute the following SQL to create our table.

Create table using HSQL Gui Manager
Create Table

Press Ctrl-R and you will see the database. Insert a record to test the table. Later, our module will manage this table. So, this may become a stale record later on.

INSERT INTO USERLOGIN VALUES(38302,'2021-10-01 22:14:20.365000',5,1532137,84400);
INSERT INTO USERLOGIN VALUES(40201,'2021-10-01 22:12:13.243000',4,1356464,15582);
INSERT INTO USERLOGIN VALUES(40301,'2021-10-01 22:13:46.836000',2,15150,15150);

Check that the records exist. In our sample records above, we have Liferay user with id 40201. His last login occurred on October 1, 2021. Well, actually, all the users logged in that day. ;-)

Select users using HSQL Gui Manager
Select Users
select * from userlogin;

Since this is not a real record from our Liferay system, let’s delete the records! If you leave them, it won’t hurt!

delete from userlogin;

Your database is ready to go. Remember not to kill the windows that is running org.hsqldb.server.Server. Your liferay server will connect to it. You can connect to this database using Liferay, LibreOffice, or Eclipse’s Data Tools Platform! Did I say data independence?!

Adjust Liferay Config

Now, we are onto the Liferay side of things! We will now update Liferay config. Add the following to portal-ext.properties in LIFERAY_HOME. Mine is in C:\pkg\liferay\liferay-ce-portal-7.3.7-ga8.

# Comments start with the pound character
jdbc.ext.driverClassName=org.hsqldb.jdbcDriver
jdbc.ext.url=jdbc:hsqldb:hsql://localhost/xdb;hsqldb.write_delay=false
jdbc.ext.username=sa
jdbc.ext.password=
# You can add the following too in order to see sql
hibernate.show_sql=true

Restart your Liferay server. If you see error messages, you may have done it wrong! Check that the settings that you used in the HSQL Gui manager are the same that you put in portal-ext.properties.

Create the Service Builder

Create the servicebuilder project. Go to your modules folder. Execute the following blade command.

cd modules
blade create -t service-builder -p com.liferay.example.servicebuilder.extdb sb-extdb

This will create a ServiceBuilder project with two modules:

  • sb-extdb-api: The API module that consumers will depend upon.
  • sb-extdb-service: The service implementation module.

Switch to the sb-extdb\sb-extdb-service folder. Edit the service.xml with the content below. Note that I explicitly defined the table name with table="UserLogin" for the entity. I recall it attempting to access the table name as ExtDB_UserLogin. Obviously, that didn’t work! I also explicity specifiy dependency-injector="ds".

Leave the following in service.xml. Note that the dtd may be different for your Liferay server version.

Add this. Note that case for table="UserLogin" does not matter for the table name UserLogin. This may not be true for other databases.

Enter the service project folder if you are not already in it, and build the service. We use the blade command that will find our gradlew wrapper command in our workspace.

cd sb-extdb\sb-extdb-service
blade gw buildservice 

You should see output similar to the following.

c:\Users\Brian Lavender\workdirs\blade02\sb-extdb\modules\sb-extdb\sb-extdb-service>blade gw buildservice
Starting a Gradle Daemon, 2 incompatible and 1 stopped Daemons could not be reused, use --status for details

> Task :modules:sb-extdb:sb-extdb-service:buildService
Building UserLogin
Writing src\main\java\com\liferay\example\servicebuilder\extdb\service\persistence\impl\UserLoginPersistenceImpl.java
Writing ..\sb-extdb-api\src\main\java\com\liferay\example\servicebuilder\extdb\service\persistence\UserLoginPersistence.java
Writing ..\sb-extdb-api\src\main\java\com\liferay\example\servicebuilder\extdb\service\persistence\UserLoginUtil.java
Writing src\main\java\com\liferay\example\servicebuilder\extdb\model\impl\UserLoginModelImpl.java
[snip]
BUILD SUCCESSFUL in 23s

Add method to Service Builder

We are now going to add our custom method to our service builder. Place the contents below in the following package com.liferay.example.servicebuilder.extdb.service.impl and the class named UserLoginLocalServiceImpl. This is the same as in Dave’s blog. Note the docs indicate that serviceBuilder will expose this through the API module.

Add the Logger

Add the method to the same class.

Add the dependencies. Press Ctrl-Shift-O to resolve dependencies if you use Eclipse! Choose the following when prompted. You can paste them in if you are using the text editor, or you can check GitHub!

java.util.Date
import com.liferay.portal.kernel.log.Log;
import com.liferay.portal.kernel.log.LogFactoryUtil;

Run service builder again

cd sb-extdb\sb-extdb-service
blade gw buildservice

Build it.

blade gw build

Add the User Model attributes for Service Builder

We now tap into Liferay’s User service so that we can query the object for the following. Notice how we just have the user ID in the database! Liferay has the rest of the meta data!

  1. First Name
  2. Last Name
  3. Login ID

Add the following to UserLoginImpl in the com.liferay.example.servicebuilder.extdb.model.impl package.

You’ll need the following imports.

import com.liferay.portal.kernel.exception.PortalException;
import com.liferay.portal.kernel.model.User;
import com.liferay.portal.kernel.service.UserLocalServiceUtil;

Run service builder again

cd sb-extdb\sb-extdb-service
blade gw buildservice

You will see output like the following.

c:\Users\Brian Lavender\workdirs\blade02\sb-extdb\modules\sb-extdb\sb-extdb-service>blade gw buildservice

> Task :modules:sb-extdb:sb-extdb-service:buildService
Building UserLogin
Writing ..\sb-extdb-api\src\main\java\com\liferay\example\servicebuilder\extdb\model\UserLogin.java
Writing ..\sb-extdb-api\src\main\java\com\liferay\example\servicebuilder\extdb\model\UserLoginWrapper.java
Writing ..\sb-extdb-api\src\main\java\com\liferay\example\servicebuilder\extdb\service\UserLoginLocalService.java
Writing ..\sb-extdb-api\src\main\java\com\liferay\example\servicebuilder\extdb\service\UserLoginLocalServiceUtil.java
Writing ..\sb-extdb-api\src\main\java\com\liferay\example\servicebuilder\extdb\service\UserLoginLocalServiceWrapper.java
Writing src\main\resources\service.properties
BUILD SUCCESSFUL in 4s

Build it. Switch up a folder to build both the service and api layers.

cd ..
blade gw build

You now have a service layer. The problem is, it wants to to talk to the Liferay database.

Add the SPI connector for the external database to our service builder

Now we add the SPI service reference in the module sb-extdb\sb-extdb-service that references the external database we created above.

Create the file named com.liferay.portal.kernel.dao.jdbc.DataSourceProvider in META-INF/services folder with the following content.

com.liferay.external.data.source.test.internal.DataSourceProviderImpl

Create the following class that you just referenced above. Note how it references the jdbc.ext that we placed earlier in portal-ext.properties. If you feel like you are going down the Liferay maze, you and I share something in common!

Build everything (sb-extdb-service and sb-extdb-api). Execute the command from the parent folder for the two modules.

cd modules\sb-extdb
blade gw build

You will see output similar to the following. If you don’t, check the github, take a break, etc.

c:\Users\Brian Lavender\workdirs\blade02\sb-extdb\modules\sb-extdb>blade gw build

Deprecated Gradle features were used in this build, making it incompatible with Gradle 7.0.
Use '--warning-mode all' to show the individual deprecation warnings.
See https://docs.gradle.org/6.6.1/userguide/command_line_interface.html#sec:command_line_warnings

BUILD SUCCESSFUL in 3s
7 actionable tasks: 5 executed, 2 up-to-date

Our service is now ready to write data to our external database.

Add the Post Login Hook Service

We now add the service using Blade and connect to the sb-extdb API module. Once again, I took this straight out of Dave’s blog! We will use blade to create our service module. We will put it under the sb-extdb folder, so everything is all on one place!

cd modules\sb-extdb 
blade create -p com.liferay.example.servicebuilder.extdb.event -t service -s com.liferay.portal.kernel.events.LifecycleAction sb-extdb-postlogin

Add sb-extdb-service and sb-extdb-api as dependencies to sb-extdb-postlogin as well as a dependency on SLF4J by editing its build.gradle and adding the following.

compileOnly project(":modules:sb-extdb:sb-extdb-api")
compileOnly project(":modules:sb-extdb:sb-extdb-service")
compileOnly group: 'org.slf4j', name: 'slf4j-api', version: '1.7.26'

Update the class with the service hook. Note that I modified Dave’s original code so now we can directly inject the UserLoginLocalService using the OSGI @Reference annotation!

package com.liferay.example.servicebuilder.extdb.event;

import com.liferay.portal.kernel.events.ActionException;
import com.liferay.portal.kernel.events.LifecycleAction;
import com.liferay.portal.kernel.events.LifecycleEvent;
import com.liferay.portal.kernel.exception.PortalException;
import com.liferay.portal.kernel.model.User;
import com.liferay.portal.kernel.util.PortalUtil;

import com.liferay.example.servicebuilder.extdb.service.UserLoginLocalService;
import org.osgi.service.component.annotations.Component;
import org.osgi.service.component.annotations.Reference;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.util.Date;

/**
 * @author Brian E. Lavender
 * originally by Dave!
 */
@Component(
          immediate = true, property = {"key=login.events.post"},
          service = LifecycleAction.class
        )
public class SbExtdbPostlogin implements LifecycleAction {

    @Override
    public void processLifecycleEvent(LifecycleEvent lifecycleEvent) throws ActionException {
        // okay, we need the user login for the event
        User user = null;
        _log.info("Dude where is my car?");

        try {
          user = PortalUtil.getUser(lifecycleEvent.getRequest());
        } catch (PortalException e) {
          _log.error("Error accessing login user: " + e.getMessage(), e);
        }

        if (user == null) {
            _log.warn("Could not find the logged in user, nothing to track.");

          return;
        }

        // we have the user, let's invoke the service
        _userLoginLocalService.updateUserLogin(user.getUserId(), new Date());

    }

      // use the OSGi service tracker to get an instance of the service when available.
      @Reference
      UserLoginLocalService _userLoginLocalService;
      
      private static final Logger _log = LoggerFactory.getLogger(SbExtdbPostlogin.class);

}

Build the code.

blade gw build

If all goes well, you should get a clean build. You now have a hook that will record logis. Let’s deploy and see what happens.

c:\Users\Brian Lavender\workdirs\blade02\sb-extdb\modules\sb-extdb>blade gw clean build deploy

You should see some output similar to the following

> Task :modules:sb-extdb:sb-extdb-api:deploy
Files of project ':modules:sb-extdb:sb-extdb-api' deployed to c:\pkg\liferay\liferay-ce-portal-7.3.7-ga8\osgi\modules

> Task :modules:sb-extdb:sb-extdb-postlogin:deploy
Files of project ':modules:sb-extdb:sb-extdb-postlogin' deployed to c:\pkg\liferay\liferay-ce-portal-7.3.7-ga8\osgi\modules

> Task :modules:sb-extdb:sb-extdb-service:deploy
Files of project ':modules:sb-extdb:sb-extdb-service' deployed to c:\pkg\liferay\liferay-ce-portal-7.3.7-ga8\osgi\modules

Now log in and out a few times and check the hsql GUI manager. You will see the counter updated for each users who logged in.

Portal Display

We will not create an MVC portal showing the login data, but we will use the User service reference that we placed into our service reference to show data about the user(s).

First, go into modules\sb-extdb and use blade to create the mvc web portal.

blade create -t mvc-portlet -p com.liferay.example.servicebuilder.extdb.portlet -c LoggerPortlet sb-extdb-web

Add the following to build.gradle for our sb-extdb-web portlet.

compileOnly project(":modules:sb-extdb:sb-extdb-api")
compileOnly project(":modules:sb-extdb:sb-extdb-service")
compileOnly group: 'org.slf4j', name: 'slf4j-api', version: '1.7.26'

Add the following to init.jsp under src\main\resources\META-INF\resources

<%@ page import="com.liferay.portal.kernel.dao.search.SearchEntry" %>
<%@ page import="com.liferay.portal.kernel.dao.search.ResultRow" %>

<%@ page import="com.liferay.example.servicebuilder.extdb.service.UserLoginLocalServiceUtil" %>

<%@ page import="com.liferay.example.servicebuilder.extdb.model.UserLogin" %> 
<%@ page import="com.liferay.example.servicebuilder.extdb.model.UserLoginModel" %> 

In the jsp file view.jsp replace the following text.

<p>
    <b><liferay-ui:message key="logger.caption"/></b>
</p>

with this

<p>
    <b><liferay-ui:message key="logincounter.caption"/></b>
    <liferay-ui:search-container total="<%= UserLoginLocalServiceUtil.getUserLoginsCount() %>">


    <liferay-ui:search-container-results
        results="<%= UserLoginLocalServiceUtil.getUserLogins( searchContainer.getStart(),
                searchContainer.getEnd()) %>" />
                    
        <liferay-ui:search-container-row
            className="com.liferay.example.servicebuilder.extdb.model.UserLogin" >
            <liferay-ui:search-container-column-text property="login" name="ID"/>
            <liferay-ui:search-container-column-text property="firstName" name="First Name"/>
            <liferay-ui:search-container-column-text property="lastName" name="Last Name"/>
            <liferay-ui:search-container-column-text property="totalLogins" name="total Logins"/>
            <liferay-ui:search-container-column-date property="lastLogin" name="Last Login"/>

    </liferay-ui:search-container-row>
    <liferay-ui:search-iterator />
    </liferay-ui:search-container>
</p>

Deploy the module

cd modules\sb-extdb
blade gw clean build deploy

Add a page and view the portlet. You will now see the portlet shows data from the database and some additional info about each user. Note, there are no access controls on this portlet. You will see that the data in the database correlates to what shows on the portlet.

portlet page showing logins
​​​​​​
 

Conclusion

Hopefully this has provided a good whirlwhind tour for tying your service build to an external database amongst accessing it using an MVC portlet. Obviously, you can replace the login hook with whatever you desire.