Introduction

This post originated from a couple of different client questions...

First was a seemingly simple question from a client: How do I use Service Builder to connect to an external database that has a double for the primary key?

Unfortunately the only answer for this is you can't.

Service Builder only allows Strings, Ints and Longs as primary keys, and Service Builder promotes using Surrogate Keys for its entities instead of Natural keys.

The second question, a client was trying to build custom OSGi services, but when using the API module, they were hitting Class Not Found exceptions. Which wasn't really true, the classes were really there, but there was some bleeding of dependencies that were not exposed and the OSGi class loader couldn't resolve them, and this lead to the CNFEs.

So I decided I should combine these into a post to demonstrate using an alternate ORM to create OSGi services to access an external database featuring a third party library that I like to use, SansORM. That way there's a bunch of different things in here to help a couple of clients solve their separate issues.

SansORM

These days, if you're a Java developer and you're asked to access a database, you reach for JPA... I mean, it's completely natural really. We all got onboard the Hibernate train when it was first available and then JPA when it became part of the platform because they really do simplify object/relational mapping against the database.

The problem with this is, well, if all you have is a hammer in a toolbox, then everything starts to look like a nail.

And in OSGi, getting JPA and/or Hibernate working well is kind of tough. I mean, I've been doing this a long time and I haven't been successful with this, so I typically stay just with Service Builder and let it handle all of the heavy lifting for me.

But there are cases where Service Builder just won't work. The current case of trying to use SB to access an external database with a table that used natural keys (doubles) as the primary keys is one of those. In cases like this, I reach for SansORM...

I've linked to the github repo, but basically SansORM is not a full-blown ORM. SansORM is a very thin façade over direct JDBC access. It does handle simple object/relational mapping, but it doesn't manage all of the parent/child relationships, lazy loading, and other typical ORM features.

It will, however, make accessing our external database easier, handle the grunt work of the Java object/record mapping, and help keep us just above low level JDBC coding.

That's our brief introduction to SansORM, we'll see more of it in action as we build out the solution.

It's also an interesting choice to tackle the issue of how to encapsulate and use SansORM in Liferay since the SansORM artifacts are not OSGi-enabled, so it can't be deployed in Liferay by just dropping into the deploy folder, it will actually need to be embedded in a module jar to use.

Setup

So we need a basic setup for building/testing the solution, so I've set up a PostgreSQL database using the following table:

CREATE TABLE public.doublekey (
	pkey numeric NOT NULL,
	"name" varchar NOT NULL,
	CONSTRAINT doublekey_pk PRIMARY KEY (pkey)
);

Pretty simple, I know, but it will get the job done.

For connectivity, I usually prefer to use JNDI, but to keep things simple (and avoid the class loader concerns with JNDI connections) I added a new database connection to my portal-ext.properties file. These are the lines that I added:

jdbc.testing.driverClassName=org.postgresql.Driver
jdbc.testing.url=jdbc:postgresql://192.168.1.8/Testing
jdbc.testing.username=liferay
jdbc.testing.password=lportal

Don't worry, I'm not exposing anything here, no one can access this system but me ;-)

The last step is of course to create a Liferay workspace to hold my development artifacts, so that was basically just using the blade init sansorm-test command and picking the latest release as the target.

Creating the Modules

Following ServiceBuilder's model, I'm going to create two modules, one for the api and one for the implementation.

In the workspace modules folder, I use the blade command to create my modules:

$ blade create -t api -p com.liferay.example.api example-api
Successfully created project example-api in sansorm-test/modules
$ blade create -t api -p com.liferay.example.internal.impl example-impl
Successfully created project example-impl in sansorm-test/modules

In the API module, I then defined an interface for the model to hold the object:

public interface DoubleKey {
  public Double getPrimaryKey();
  public void setPrimaryKey(Double primaryKey);

  public String getName();
  public void setName(String name);
}

I also needed a service interface for processing the CRUD activities:

public interface DoubleKeyService {
  DoubleKey getDoubleKey(Double primaryKey);

  DoubleKey createDoubleKey(Double primaryKey, String name);

  DoubleKey updateDoubleKey(Double primaryKey, String name);

  void deleteDoubleKey(Double primaryKey);
}

In the attached repository, you'll also find a static util class using a ServiceTracker to find the runtime instance to use the service in a non-OSGi context.

Next I moved onto the implementation module and created my model implementation class:

@Table(name = "doublekey")
public class DoubleKeyImpl implements DoubleKey {
  @Id
  @Column(name = "pkey")
  private Double primaryKey;

  @Column(name = "name")
  private String name;

  public DoubleKeyImpl() {
  }

  public DoubleKeyImpl(Double primaryKey, String name) {
    this.primaryKey = primaryKey;
    this.name = name;
  }
    
  @Override
  public Double getPrimaryKey() {
    return primaryKey;
  }

  @Override
  public void setPrimaryKey(Double primaryKey) {
    this.primaryKey = primaryKey;
  }

  @Override
  public String getName() {
    return name;
  }

  @Override
  public void setName(String name) {
    this.name = name;
  }
}

It is a POJO, but it is also annotated with JPA annotations since SansORM can leverage them for it's own ORM support.

Because the annotations are in the internal implementation class, they will be necessary for the class loader for the implementation module, but they won't leak outside to other class loaders.

This is because the interface is clean, it doesn't use or reference any of these kinds of classes, it's limited to the regular Java classes or the clean interfaces the API is exported. The JPA and SansORM classes the implementation module uses, those are hidden in the implementation and they won't bleed out as dependencies into modules that might consume the service.

Speaking of which, next I had to implement the service:

@Component(
  immediate = true,
  service = DoubleKeyService.class
)
public class DoubleKeyServiceImpl implements DoubleKeyService {
  public DoubleKey getDoubleKey(Double primaryKey) {
    return SqlClosureElf.getObjectById(DoubleKeyImpl.class, primaryKey);
  }

  public DoubleKey createDoubleKey(Double primaryKey, String name) {
    return SqlClosureElf.insertObject(new DoubleKeyImpl(primaryKey, name));
  }

  public DoubleKey updateDoubleKey(Double primaryKey, String name) {
    return SqlClosureElf.updateObject(new DoubleKeyImpl(primaryKey, name));
  }

  public void deleteDoubleKey(Double primaryKey) {
    SqlClosureElf.deleteObject(new DoubleKeyImpl(primaryKey, null));
  }

  public List getDoubleKeys() {
    return SqlClosureElf.listFromClause(DoubleKeyImpl.class, null);
  }
}

With SansORM, we need to handle the initialization. We'll do that using a bundle activator so it will apply to all of the possible SansORM service(s) that might eventually find their way into the bundle:

public class SansORMInitializer implements BundleActivator {
  public void initialize() {
    // initialize the SansORM framework with the datasource 
    // using a simple transaction model.
    SansOrm.initializeTxSimple(getDataSource());
  }

  private DataSource getDataSource() {
    // get the properties for the external datasource
    Properties properties = PropsUtil.getProperties("jdbc.testing", true);

    // have liferay load it up
    return DataSourceFactoryUtil.initDataSource(properties);
  }

  @Override
  public void start(BundleContext bundleContext) throws Exception {
    initialize();
  }

  @Override
  public void stop(BundleContext bundleContext) throws Exception {
  }
}

The implementation module itself doesn't need anything special in the bnd.bnd file, but in the build.gradle file we will need to include the SansORM dependency. The file contains:

dependencies {
  compileOnly group: "com.liferay.portal", name: "release.dxp.api"

  compile project(":modules:example-api")
	
  compileInclude group: 'com.zaxxer', name: 'sansorm', version: '3.7'
}

Building and Deploying

So these are regular OSGi modules, so the gw build command will build each of them individually, and deployment is copying the artifacts to the Liferay deploy folder for your target bundle (or gw deploy if you're using a workspace bundle).

They should deploy without error and the component (there is only one) should start just fine.

To test, you could write a java class to do the OSGi component reference injection and use it to fetch instances, create them, etc. Alternatively, you could go to the Groovy console and write a script to test it out too.

Here's the groovy script I whipped up for testing:

import com.liferay.example.api.model.*;
import com.liferay.example.api.service.*;

try {
DoubleKeyServiceUtil.createDoubleKey(1.0, "sandwich");
DoubleKeyServiceUtil.createDoubleKey(2.0, "taco");
DoubleKeyServiceUtil.createDoubleKey(3.0, "salad");

List keys = DoubleKeyServiceUtil.getDoubleKeys();

for (DoubleKey key : keys) {
  out.println(key.toString());
}

} catch (Throwable th) {
  out.println(th.getMessage());
  th.printStackTrace(out);
}

And here is the output it generated when it ran:

DoubleKeyImpl [primaryKey=1.0, name=sandwich]
DoubleKeyImpl [primaryKey=2.0, name=taco]
DoubleKeyImpl [primaryKey=3.0, name=salad]

Conclusion

This is the model that best allows you to leverage third party libraries into your Liferay customizations...

Use a separate module to hold interfaces for models and/or services. This module should be clean and not have any third party dependencies.

An implementation module will leverage the third party dependencies but encapsulate them so they are used but don't leak dependencies to the outer container.

The compileInclude directive will correctly embed the dependency, sometimes transient dependencies will also be included if they are direct dependencies, but optional dependencies will not be included (and may either need to be directly included or excluded in the imported packages defined in the bnd.bnd file.

The benefit of following this pattern is that you can embed and use third party libraries but not expose them as possible dependencies that other modules in the platform need to declare.

Things are not always going to be simple, and this SansORM example actually demonstrates that. SansORM leverages a lot of private static class fields, so if you tried to create two service implementation modules that each used SansORM for accessing the data, you might actually get into some problems dealing with the SansORM classes loaded in multiple class loaders each trying to manage static class fields that would be set to different values...

So this pattern is the way to go, but you should understand going in what kind of idiosyncrasies the dependencies might bring to the table, and you should be prepared to make alterations on how you build your customizations to mitigate these idiosyncrasies. For example, it might be easier rather than having two different implementation jars each trying to leverage SansORM, merging the modules into one would resolve the possible class loader/class static field uses.

Hopefully this blog post will give you some ideas on how to handle ORM without Service Builder, encapsulating non-Liferay dependencies to limit platform exposure, and the ideal way to separate interface from implementations.

If you'd like to see the repo, it's available here: https://github.com/dnebing/sansorm-liferay-test