Liferay 7, Service Builder and External Databases

So I'm a long-time supporter of ServiceBuilder.  I saw its purpose way back on Liferay 4 and 5 and have championed it in the forums and here in my blog.

With the release of Liferay 7, ServiceBuilder has undergone a few changes mostly related to the OSGi modularization.  ServiceBuilder will now create two modules, one API module (comparable to the old service jar w/ the interfaces) and a service module (comparable to the service implementation that used to be part of a portlet).

But at it's core, it still does a lot of the same things.  The service.xml file defines all of the entities, you "buildService" (in gradle speak) to rebuild the generated code, consumers still use the API module and your implementation is encapsualted in the service module.  The generated code and the Liferay ServiceBuilder framework are built on top of Hibernate so all of the same Spring and Hibernate facets still apply.  All of the features used in the past are also supported, including custom SQL, DynamicQuery, custom Finders and even External Database support.

External Database support is still included for ServiceBuilder, but there are some restrictions and setup requirements that are necessary to make them work under Liferay 7.

Examples are a good way to work through the process, so I'm going to present a simple ServiceBuilder component that will be tracking logins in an HSQL database separate from the Liferay database.  That last part is obviously contrived since one would not want to go to HSQL for anything real, but you're free to substitute any supported DB for the platform you're targeting.

The Project

So I'll be using Gradle, JDK 1.8 and Liferay CE 7 GA2 for the project.  Here's the command to create the project:

blade create -t servicebuilder -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 on.
  • sb-extdb-service: The service implementation module.

The Entity

So the first thing we need to define is our entity.  The service.xml file is in the sb-extdb-service module, and here's what we'll start with:

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

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

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

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

    <!-- userId is our primary key. -->
    <column name="userId" type="long" primary="true" />

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

This is a pretty simple entity for tracking user logins.  The user id will be the primary key and we'll track dates, times between logins as well as the user's total logins.

Just as in previous versions of Liferay, we must specify the external data source for our entity/entities.

ServiceBuilder will create and manage tables only for the Liferay DataBase.  ServiceBuilder will not manage the tables, indexes, etc. for any external databases.

In our particular example we're going to be wiring up to HSQL, so I've taken the steps to create the HSQL script file with the table definition as:

CREATE MEMORY TABLE PUBLIC.EXTDB_USERLOGIN(
    USERID BIGINT NOT NULL PRIMARY KEY,
    LASTLOGIN TIMESTAMP,
    TOTALLOGINS BIGINT,
    LONGESTTIMEBETWEENLOGINS BIGINT,
    SHORTESTTIMEBETWEENLOGINS BIGINT);

The Service

The next thing we need to do is build the services.  In the sb-extdb-service directory, we'll need to build the services:

gradle buildService

Eventually we're going to build out our post login hook to manage this tracking, so we can guess that we could use a method to simplify the login tracking.  Here's the method that we'll add to UserLoginLocalServiceImpl.java:

public class UserLoginLocalServiceImpl extends UserLoginLocalServiceBaseImpl {
  private static final Log logger = LogFactoryUtil.getLog(UserLoginLocalServiceImpl.class);

  /**
  * updateUserLogin: Updates the user login record with the given info.
  * @param userId User who logged in.
  * @param loginDate Date when the user logged in.
  */
  public void updateUserLogin(final long userId, final Date loginDate) {
    UserLogin login;

    // first try to get the existing record for the user
    login = fetchUserLogin(userId);

    if (login == null) {
      // user has never logged in before, need a new record
      if (logger.isDebugEnabled()) logger.debug("User " + userId + " has never logged in before.");

      // create a new record
      login = createUserLogin(userId);

      // update the login date
      login.setLastLogin(loginDate);

      // initialize the values
      login.setTotalLogins(1);
      login.setShortestTimeBetweenLogins(Long.MAX_VALUE);
      login.setLongestTimeBetweenLogins(0);

      // add the login
      addUserLogin(login);
    } else {
      // user has logged in before, just need to update record.

      // increment the logins count
      login.setTotalLogins(login.getTotalLogins() + 1);

      // determine the duration time between the current and last login
      long duration = loginDate.getTime() - login.getLastLogin().getTime();

      // if this duration is longer than last, update the longest duration.
      if (duration > login.getLongestTimeBetweenLogins()) {
        login.setLongestTimeBetweenLogins(duration);
      }

      // if this duration is shorter than last, update the shortest duration.
      if (duration < login.getShortestTimeBetweenLogins()) {
        login.setShortestTimeBetweenLogins(duration);
      }

      // update the last login timestamp
      login.setLastLogin(loginDate);

      // update the record
      updateUserLogin(login);
    }
  }
}

After adding the method, we'll need to build services again for the method to get into the API.

Defining The Data Source Beans

So we now need to define our data source beans for the external data source.  We'll create an XML file, ext-db-spring.xml, in the sb-extdb-service/src/main/resources/META-INF/spring directory.  When our module is loaded, the Spring files in this directory will get processed automatically into the module's Spring context.

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

  <!--
    NOTE: Current restriction in LR7's handling of external data sources requires us to redefine the
    liferayDataSource bean in our spring configuration.

    The following beans define a new liferayDataSource based on the jdbc.ext. prefix in portal-ext.properties.
   -->
  <bean class="com.liferay.portal.dao.jdbc.spring.DataSourceFactoryBean" id="liferayDataSourceImpl">
    <property name="propertyPrefix" value="jdbc.ext." />
  </bean>

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

  <!--
    So our entities are all appropriately tagged with the extDataSource, we'll alias the above
    liferayDataSource so it matches the entities.
   -->

  <alias alias="extDataSource" name="liferayDataSource" />
</beans>

These bean definitions are a big departure from the classic way of using an external data source.  Previously we would define separate data source beans from the Liferay Data Source beans, but under Liferay 7 we must redefine the Liferay Data Source to point at our external data source.

This has a couple of important side effects:

Only one data source can be used in a single ServiceBuilder module.  If you have three different external data sources, you must create three different ServiceBuilder modules, one for each data source.
The normal Liferay transaction management limits the scope of transactions to the current module.  To manage transactions that cross ServiceBuilder modules, you must define and use XA transactions.
When building modules for Service Builder / Spring Extender, classes referenced in Spring xml files will not be declared as OSGi import requirements leading to ClassNotFoundExceptions.  The packages, such as org.springframework.jdbc.datasource and com.liferay.portal.dao.jdbc.spring must be manually imported in the bnd.bnd file.

The last line, the alias line, this line defines a Spring alias for the liferayDataSource as your named data source in your service.xml file.

So, back to our example.  We're planning on writing our records into HSQL, so we need to add the properties to the portal-ext.properties for our external datasource connection:

# Connection details for the HSQL database
jdbc.ext.driverClassName=org.hsqldb.jdbc.JDBCDriver
jdbc.ext.url=jdbc:hsqldb:${liferay.home}/data/hypersonic/logins;hsqldb.write_delay=false
jdbc.ext.username=sa
jdbc.ext.password=

The Post Login Hook

So we'll use blade to create the post login hook.  In the sb-extdb main directory, run blade to create the module:

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

Since blade doesn't know we're really adding a sub module, it has created a full standalone gradle project.  While not shown here, I modified a number of the gradle project files to make the postlogin module a submodule of the project.

We'll create the com.liferay.example.servicebuilder.extdb.event.UserLoginTrackerAction with the following details:

/**
 * class UserLoginTrackerAction: This is the post login hook to track user logins.
 *
 * @author dnebinger
 */
@Component(
  immediate = true, property = {"key=login.events.post"},
  service = LifecycleAction.class
)
public class UserLoginTrackerAction implements LifecycleAction {

  private static final Log logger = LogFactoryUtil.getLog(UserLoginTrackerAction.class);

  /**
   * processLifecycleEvent: Invoked when the registered event is triggered.
   * @param lifecycleEvent
   * @throws ActionException
   */
  @Override
  public void processLifecycleEvent(LifecycleEvent lifecycleEvent) throws ActionException {

    // okay, we need the user login for the event
    User user = null;

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

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

      return;
    }

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

    // alternatively we could just use the local service util:
    // UserLoginLocalServiceUtil.updateUserLogin(user.getUserId(), new Date());
  }

  /**
   * getService: Returns the user tracker service instance.
   * @return UserLoginLocalService The instance to use.
   */
  public UserLoginLocalService getService() {
    return _serviceTracker.getService();
  }

  // use the OSGi service tracker to get an instance of the service when available.
  private ServiceTracker<UserLoginLocalService, UserLoginLocalService> _serviceTracker =
    ServiceTrackerFactory.open(UserLoginLocalService.class);
}

Checkpoint: Testing

At this point we should be able to build and deploy the api module, the service module and the post login hook module.  We'll use the gradle command:

gradle build

In each of the submodules you'll find a build/libs directory where the bundle jars are.  Fire up your version of LR7CEGA2 (make sure the jdbc.ext properties are in portal-ext.properties file before starting) and put the jars in the $LIFERAY_HOME/deploy folder.  Liferay will pick them up and deploy them.

Drop into the gogo shell and check your modules to ensure they are started.

Log into the portal a few times and you should be able to find the database in the data directory and browse the records to see what it contains.

Conclusion

Using external data sources with Liferay 7's ServiceBuilder is still supported.  It's still a great tool for building a db-based OSGi module, still allows you to generate a bulk of the DB access code while encapsulating behind an API in a controlled manner.

We reviewed the new constraints on ServiceBuilder imposed by Liferay 7:

  • Only one (external) data source per Service Builder module.
  • The external data source objects, the tables, indexes, etc., must be manually managed.
  • For a transaction to span multiple Service Builder modules, XA transactions must be used.

You can find the GitHub project code for this blog here: https://github.com/dnebing/sb-extdb

Blogs
Two questions:
1). Can one serviceBuilder define multiple entities/tables like in Liferay 6?
2). If I have done everything, how can I see these tables. What are the steps in gradle? I simply cannot force LR7 to create the actual tables in MySQL database, although I have all the stuff ready, but still missing the table.
What version of Liferay are you using? Yes, SB will create the tables but you should check to see if maybe they weren't already created in the portal database (not your external database). I do believe, however, there may have been some initial bugs preventing the tables from being created. If you still have a problem in the latest GA, I would report it on issues.liferay.com and possibly take it up in the forums also.
In fact, I am trying to create tables inside the portal's database. I managed to create one custom entity, but I am having problems adding another. I have 2 entities defined in service.xml, but I am not sure what's the right way of making them instantiated in database. I see the first table, but not the second one. And when I try to insert a record, I am getting error table doesn't exist. I am using LR 7.0 CE GA3. So, how is supposed to create these tables physically?
Hello DAVID H NEBINGER,
I just want to change database name only. for example my portal using postgre database with name: person. then I want the service builder to use the postgre with the name changed to personNew.
Can you help me in this case?
Many thanks?
Hello DAVID H NEBINGER,
I just want to change database name only. for example my portal using postgre database with name: person. then I want the service builder to use the postgre with the name changed to personNew.
Can you help me in this case?
Many thanks?
Hi David,
Is there any change Liferay 7.0 service builder when writing custom SQL? when I run service builder after writing FinderImpl it's not generating FinderUtil class. it does generated LocalServiceImpl. Also my FinderImpl extends BasePersistenceImpl<Event> replaced by extends EventFinderBaseImpl.
In Liferay 7 you should never be using the Util classes. Instead you declare a variable and use @Reference to have OSGi inject it.

Also, a custom SQL through the finder, you should be accessing through the member variable injected into your XxxLocalServiceImpl class anyway. External classes should never be using the finder directly because it really violates the core concepts of OOP encapsulation.

So I guess I don't know if the finderutil thing is a bug or not, but since you should not be using it and should not be exposing it, it doesn't quite matter.
Thanks David. Till 6.2 FinderUtils created and missing now. I will try to access FinderImpl methods through LocalServiceImpl. I use Util classes when I access from JSPs (Ex: when I use Search Container)..Inside Portlet classes I will use @Reference.
You get the same outcome if you add a method to the XxxLocalServiceImpl class; the XxxLocalServiceUtil will have the exposed method.

BTW, check out the blade samples, specifically the service builder web example. In the pattern laid out there, the portlet class' render() method is used to inject references into the render attributes and extract them in the init.jsp file.

That way the Xxx[Local]ServiceUtil classes can be avoided in a pure OSGi implementation.
Hi David,

I have followed same steps as mentioned in your blog. However there is one small change at my side and its not working for me.

Changes
1) portal-ext.properties has JNDI entry to connect with Liferay Database.
2) I would like to connect OSGI service builder with external database using JNDI.

So I have done following change in ext-spring.xml and rest is same as you have mentioned.

<bean class="com.liferay.portal.spring.jndi.JndiObjectFactoryBean" id="liferayDataSourceImpl">
<property name="jndiName" value="jdbc/BaseDBPool"/>
</bean>

When I invoke any getXXX() method from web module its throwing NPE. I did further analysis and found that when I am using JNDI as datasource its not even registering OSGI service.

Your help is very much appreciated.
Hi David,
what about SessionFactory and TxManager configuration?
I have Liferay (DXP SP12) on a MySQL database and I need to read some data to an external SQL Server database.
If I don't specify the session-factory parameters in service.xml, Liferay look for the external table inside the Liferay database and it doesn't work.
But specifying the session-factory parameters (but I'm not sure how to configure the ext-spring.xml correctly) I always get an Hibernate error when I call the "count" method: org.hibernate.QueryException: in expected: mytable [SELECT COUNT(*) FROM Mytable mytable].

Bye
Marco
Maybe I found the error: I must create a service module ONLY for the external tables.
What I'm doing was just to add the external entity inside an existing service.xml file.
Yeah, I did include a note about that, you need a separate SB implementation for each database that you're going to.
To create the project, blade:
create -t servicebuilder -p com.liferay.example.servicebuilder.extdb sb-extdb
It's not worknig, now must use:
blade create -t service-builder -p com.liferay.example.servicebuilder.extdb sb-extdb

Hello David,

 

Just wanted to thank you for such great tutorial. I was able to set this up using a Maven based project using SQL Server on Liferay CE 7.0.4. 

I am very interested in learning more about the Services event hooks and all their capabilities.

Specifically, I want to know the magic behind identifying how to call post events on Apps from the Marketplace such as the the Liferay's Web Form or SmartForms. 

I am fairly new to the Liferay development and I am not sure if Service events or Workflows would be the easier path to listen for events within Liferay. 

Any hints, advice, or link to one of your tutorials somewhere would be very beneficial. 

Thanks again!

 

Camilo Silva.

Hello David,

 

Just wanted to thank you for such great tutorial. I was able to set this up using a Maven based project using SQL Server on Liferay CE 7.0.4. 

I am very interested in learning more about the Services event hooks and all their capabilities.

Specifically, I want to know the magic behind identifying how to call post events on Apps from the Marketplace such as the the Liferay's Web Form or SmartForms. 

I am fairly new to the Liferay development and I am not sure if Service events or Workflows would be the easier path to listen for events within Liferay. 

Any hints, advice, or link to one of your tutorials somewhere would be very beneficial. 

Thanks again!

 

Camilo Silva.

Hi David. 

Firstly, just wanna say that I really appreciate all your contributions to the community ;D

The point is that we are migrating from 6.1 to 7.0 and are stuck in an old Servicebuilder portlet that uses db2 as his database.

As it seems, you need to configure the sessionFactory for this "Out of the box" cases in which  use enterprise databases.

¿Is there an example that defines this sessionFactory and transactionManager for this kinds of databases (db2 in articular)?

 

 

Unfortunately there is no SB support for enterprise databases in Liferay CE 7.0.  However, there is a project where a community member has added support back in: https://www.dontesta.it/en/2016/04/13/liferay-7-ce-how-to-add-support-for-oracle-db/ Although Antonio has only tackled oracle, I think you can use it as a template for DB2 support. If you can enable the SB DB2 support, then this post can help you get access to the external database.

I try to avoid recommending direct Hibernate and/or JPA use because it can be difficult getting everything right, especially in OSGi w/ the various class loaders in play...

Thanks for the quick response David.

We already tried that .jar and the db2 datasource seems to work fine.

Our problem is with the session factory config files in the "spring" directory of the service proyect.

The same configuration in the ext-db-spring.xml does not work in L7 since SessionFactoryImpl has change.

The classic:

<bean id="externalHibernateSessionFactory" class="com.liferay.portal.spring.hibernate.PortletHibernateConfiguration" lazy-init="true">                          <property name="dataSource" ref="externalDataSource" />     </bean>

    <bean id="externalSessionFactory" class="com.liferay.portal.dao.orm.hibernate.SessionFactoryImpl" lazy-init="true">         <property name="sessionFactoryImplementor" ref="externalHibernateSessionFactory" />     </bean>

 does not work anymore since it is given a conversion type error: 

Failed to convert property value of type [com.liferay.portal.dao.orm.hibernate.SessionFactoryImpl] to required type [org.hibernate.SessionFactory]

 

Im looking for any example of this "externalSessionFactory" definition but i am not able to find any.

 

Hi David,

 

we have trouble with a service module with external datasource (JNDI) that worked fine in 7.1.0 GA1 and failed in 7.1.1 GA2 :

Despite my configuration, the service is using the INTERNAL datasource (lportal database) instead of the external one.

 

I followed the official documentation (https://dev.liferay.com/fr/develop/reference/-/knowledge_base/7-1/service-builder-application-using-external-database-via-jndi ) and raised a jira issue (https://issues.liferay.com/browse/LPS-88444 ) . I'm not the only one with that trouble (https://community.liferay.com/forums/-/message_boards/message/111742415  ).

 

I suspect a ClassLoader trouble as between GA1 and GA2 there was a deprecation and replacement of ClassLoaderUtil by PortalClassLoaderUtil.

 

Do you have any clue or advice for us please ?

 

Christophe

 

 

Thanks again for writing up these blog posts! By any chance, do you happen to have the hypersonic script file that you used?

There's no script file, just the "CREATE MEMORY TABLE..." above...

I think I had messed up the code in my external hsql script file, still not sure what I did wrong, in the end, I downloaded a GUI which generated the script file and everything works now.

 

Btw, in case anyone is trying this in 7.1 DXP, this is currently broken (from fixpack-3 to the current fixpack-6).  I made a ticket for this, but in the meantime, you can test out your code with fixpack 1 or 2, or just plain GA1.

Hi David,

 

I am using Liferay 7.1 CE GA2.

 

I have created two modules project in a single workspace project. One for a database (service builder) and second for consuming those database things. Now If I need to use that service builder in same works[ace I need to write compile project (":modules:FirstModuleDatabase:FirstModuleDatabase-api") line in build.gradle.

 

But if I want to use same service builder in another workspace project what I should have to do?

 

Like in 6.2, If I want to use any service builder generated tables in any portlet I copy jar file of that service builder from Lib file and past in required portlet's Lib folder and I am able to use all classes and interfaces of service builder in that portlet. Please let me know How I can achieve it in Liferay 7.1 CE GA2

Hi David,

 

thank you for your article.

I tried to follow your instructions and the ones written in the subsequent articles, but it seems something has changed in the 7.2-ga1 Liferay's version.

 

The articles that I've read:

https://portal.liferay.dev/docs/7-1/tutorials/-/knowledge_base/t/connecting-service-builder-to-external-databases

https://portal.liferay.dev/docs/7-1/reference/-/knowledge_base/r/service-builder-application-using-external-database-via-jdbc

 

It seems that service builder is totally ignoring the ext-spring.xml file.

Furthermore when i try to put the spring-module.xml file under the  META-INF/spring path the service builder removes it.

 

Can you help me, please?

Thanks in advance.

Hi, David. I forked your repository (https://github.com/amusarra/sb-extdb) and updated for Liferay 7.1 GA3. I did the update to respond to a reader of my blog who encountered some difficulties on Liferay 7.1 and SQL Server as an external database.

 

Thanks for your work.

Hi David,

In Liferay DXP 7.2 i am trying to connect external DB thorough Hibernate session factory.

But getting below error.

 

Cannot convert value of type 'org.hibernate.impl.SessionFactoryImpl' to required type 'com.liferay.portal.kernel.dao.orm.SessionFactory' for property 'sessionFactory': no matching editors or conversion strategy found

 

Here is my config.

 

<bean class="com.liferay.portal.spring.hibernate.PortalHibernateConfiguration" id="customSessionFactory">         <property name="dataSource" ref="liferayDataSource" />     </bean>     <bean class="com.liferay.portal.spring.transaction.TransactionManagerFactory" factory-method="createTransactionManager" id="crmTransaction">         <constructor-arg ref="liferayDataSource" />         <constructor-arg ref="customSessionFactory" />     </bean> Service.xml<entity local-service="true" name="Foo" remote-service="true" uuid="true" data-source="extDataSource" session-factory="customSessionFactory" tx-manager="crmTransaction" >

 

Is there any example or documentation for this how we  can open hibernate session factory in DXP 7.2

Hi Pakaj.

You could see my fork for Liferay 7.2 on my GitHub repository  http://bit.ly/2BwEyM4