Service Builder, Joins and External Tables

A Solution Proposal

I'm not a Liferay Legend like others who helped me in the forums while digging my way through the Service Builder Forest. That's why I thought that sharing my experiences  could help others not getting lost.

Recently I wanted to work with an external database within Liferay and quickly I discovered Service Builder.

BTW: I work with Liferay 7.3.2 GA and use the liferay-workspace IDE with Eclipse.

When you create your Service Builder module it's a good idea to set the
liferay.workspace.target.platform.version in gradle.properties to the correct target. That might save you from some gradle build version horror.

Anyway I had to add
compileOnly group: "com.liferay", name: "com.liferay.petra.sql.dsl.api"
to the build.gradle files of the service and the api to have my code generated by building the service.

Well, I have an external DB, so I found this repo that explains how to use Service Builder with external data. Although it's working, it was not the best approach. Because it's based on the "old" Spring injection. (An attribute in service.xml)

You will notice that at the latest when the "new" Declarative Services injector  (DS)  does NOT inject components you might want to use the recommended way. Like for example:

Dynamic Queries or Custom SQL

The recommended way to use these "in OSGI times" is to add references to your implementation code like

@Reference
private CustomSQL _customSQL;

So you better go for the DataSourceProvider approach that is explained in the Liferay help as it allows you to stay with declarative services.

To make the CustomSQL available for injection I had to add it to my service gradle.build like this:
compileOnly group: "com.liferay", name: "com.liferay.portal.dao.orm.custom.sql.api"

First I was eager to use dynamic queries to implement by DB logic... until I realized that they are not your friend when it comes to table joins or to composite keys.
Although using composite keys (multiple primary keys in an entity) may not be a good DB design, I went for CustomSQL.

But, if you do it like that, you have still to cope with...

Three major challenges

1. Service Builder creates models for single entities only. But a joined table query typically returns columns from more then one table. So.. how to get them in a model class with getters and setters?

2. The Hibernate version in Liferay is clearly outdated! I'm not quite sure, but I think it's 3.x. This get's you into trouble because the native SQL features of Hibernate 4 or 5 clearly were extended regarding for example duplicate column names in joined data collections or defining joins as such.

3. Using custom sql you'll have something like
SQLQuery q = session.createSQLQuery(sql)
and all over the Internet I found examples that finally execute the query with
q.list() which will return a Java List object with an object array.
For a reason! Because the Lifray way
QueryUtil.list(q, getDialect()...) may be more DB agnostic but leaves you with a List<?> . Thats a list of unknown objects.  And I simply could not find out how to cast that into something useful when it comes to access the returned data.

Solutions (Proposal)

For 1.:
I created a custom model class on the MVC Portlet level, with the getters and setters for my joined "entities".

For 2.:
Be sure to use aliases in your custom SQL to solve the duplicate naming problems.
SELECT Manufacturer.name as manufacturerName, Device.name as deviceName
and add scalars to the query.
q.addScalar("manufacturerName", com.liferay.portal.kernel.dao.orm.Type.STRING);

For 3.:
Jackson is very helpful when you need object mapping.  Read this Blogpost about Liferay and Jackson. It also tells you how to install it.

Turns out that Jackson can be used like this
HashMap<String, Object> device = new HashMap<String, Object>();
ArrayList<Object> deviceColumns= mapper.convertValue(row, ArrayList.class);
device.put("manufacturerName", mapper.convertValue(deviceColumns.get(0), String.class));

No more hassle with unknown objects, thanks Jackson!

On the Portlet level...

... your custom finder method can than be made available by injecting the [MyEntity]LocalService into your portlet.

ArrayList<HashMap<String, Object>> joinedDevicesArray = _deviceLocalService.findJoinedDevices(firstCategory);

Thanks to the HashMap, you can access each property by accessing it by it's name while iterating over the rows to create your custom joined model instances.
joinedDevice.setManufacturerName((String) joineddeviceEntry.get("manufacturerName"));
joinedDevices.add(joinedDevice);

Finally that provides you with the list you're looking for:
List<JoinedDevice> joinedDevices = new ArrayList<JoinedDevice>();

And the best of all: This result can be passed to the JSP page as a attribute in the (for example) RenderRequest and be stuffed directly into the Liferay search container.
<% ArrayList<HashMap<String, Object>> devices = (ArrayList<HashMap<String, Object>>) renderRequest.getAttribute("joinedDevices"); %>
<liferay-ui:search-container total="<%=devices.size()%>">

Feedback welcome

As I said, I'm not a Liferay Legend. I appreciate the feedback from the Liferay community and will accept any proposal how this can be solved in a better way.

BTW: The picture at the beginning of the article was taken from a little open source dbdesigner tool I have on github.
 

I updated it recently to support the generation of the Service Builder entities.