Introduction

So I'm going to be putting together a series of blogs on new things that are available in Liferay 7.4, but I realized after starting a blog on Objects that I need to take a step back and instead start with what Liferay calls DSLQuery...

DSLQuery is really an acronym for Domain Specific Language Query, because the foundation is based upon using a domain specific language for the implementation. This is going to make the writing of queries and other logic much more natural than what we would have built for a DynamicQuery implementation.

DSLQuery has been fully integrated into 7.4 and even ServiceBuilder now generates DSLQuery classes for 7.4. Whenever you find Liferay code using a class from the com.liferay.petra.sql.dsl package, you know that DSLQuery is in play...

So what is DSLQuery and how can we use it? That's what we're about to find out...

Tables

So if you start a new Service Builder project, you get the service.xml and it defines the Foo entity. Go ahead and just build the services, that will give us our first important class...

Check in your -api module and go to the generated model package (mine was com.liferay.docs.servicebuilder.model) and open up the FooTable class:

package com.liferay.docs.servicebuilder.model;

import com.liferay.petra.sql.dsl.Column;
import com.liferay.petra.sql.dsl.base.BaseTable;

import java.sql.Types;

import java.util.Date;

/**
 * The table class for the "FOO_Foo" database table.
 *
 * @author Brian Wing Shun Chan
 * @see Foo
 * @generated
 */
public class FooTable extends BaseTable {

	public static final FooTable INSTANCE = new FooTable();

	public final Column uuid = createColumn(
		"uuid_", String.class, Types.VARCHAR, Column.FLAG_DEFAULT);
	public final Column fooId = createColumn(
		"fooId", Long.class, Types.BIGINT, Column.FLAG_PRIMARY);
	public final Column groupId = createColumn(
		"groupId", Long.class, Types.BIGINT, Column.FLAG_DEFAULT);
	public final Column companyId = createColumn(
		"companyId", Long.class, Types.BIGINT, Column.FLAG_DEFAULT);
	public final Column userId = createColumn(
		"userId", Long.class, Types.BIGINT, Column.FLAG_DEFAULT);
	public final Column userName = createColumn(
		"userName", String.class, Types.VARCHAR, Column.FLAG_DEFAULT);
	public final Column createDate = createColumn(
		"createDate", Date.class, Types.TIMESTAMP, Column.FLAG_DEFAULT);
	public final Column modifiedDate = createColumn(
		"modifiedDate", Date.class, Types.TIMESTAMP, Column.FLAG_DEFAULT);
	public final Column field1 = createColumn(
		"field1", String.class, Types.VARCHAR, Column.FLAG_DEFAULT);
	public final Column field2 = createColumn(
		"field2", Boolean.class, Types.BOOLEAN, Column.FLAG_DEFAULT);
	public final Column field3 = createColumn(
		"field3", Integer.class, Types.INTEGER, Column.FLAG_DEFAULT);
	public final Column field4 = createColumn(
		"field4", Date.class, Types.TIMESTAMP, Column.FLAG_DEFAULT);
	public final Column field5 = createColumn(
		"field5", String.class, Types.VARCHAR, Column.FLAG_DEFAULT);

	private FooTable() {
		super("FOO_Foo", FooTable::new);
	}

}

Nothing too crazy in here, right?

The important thing to note is that it is an entity-specific class, it has an INSTANCE singleton variable, and it has a Column field for every column in the table.

Now, this class actually has a number of uses, including being able to [re-]generate the SQL commands to create the table, etc.

But, more importantly for us, we can use it to define DSLQuery-based queries easier than we would using the old DynamicQuery logic.

Queries

If you check the FooLocalService class from the -api module, you'll find a couple of new methods to support DSLQuery:

import com.liferay.petra.sql.dsl.query.DSLQuery;
    
...
	@Transactional(propagation = Propagation.SUPPORTS, readOnly = true)
	public <T> T dslQuery(DSLQuery dslQuery);

	@Transactional(propagation = Propagation.SUPPORTS, readOnly = true)
	public int dslQueryCount(DSLQuery dslQuery);

So these are kind of like the dynamicQuery() methods of old, but they take a new object, the DSLQuery.

So we know how to invoke a query, but how do we create a DSLQuery instance?

Writing Queries

Well, this is where our FooTable class comes into play...

See, once we have the Table and its contained Columns, we can now start building queries.

Let's start with a simple one, all of the Foos that are in a specific group ID:

public List getFoosByGroupId(final long groupId) {

  DSLQuery query = DSLQueryFactoryUtil
    .select()
    .from(FooTable.INSTANCE)
    .where(FooTable.INSTANCE.groupId.eq(groupId));

  return dslQuery(query);
}

So here we're using the DSLQueryFactoryUtil to create our SELECT command. Note that it also has a selectDistinct() method when you expect duplicates to be removed. There's a number of different methods, the no-arguments one I used is basically equivalent to a SELECT *, but there's a method to take one or more expressions so you can handle aggregates and things of that nature (check out com.liferay.petra.sql.dsl.DSLFunctionFactoryUtil for supported expressions).

Next we indicate the table we're selecting from, in this case it's the FooTable.INSTANCE.

And finally we add our where clause, and here I'm using the FooTable.INSTANCE.groupId column along with the eq() expression and I'm matching the groupId parameter passed in the method.

After building my DSLQuery instance, I can then use the dslQuery() method to invoke the query and return the results, in this case I expect a list of Foos.

Now, once you know these basics, you can really start whipping up some complex queries because you soon find out that the DSLQuery can handle things like unions, joins, aliases, functions, complex where clauses, group bys and sort bys, all using this kind of domain specific language to build queries.

And the great part about this is that the IDE will even help you build out your query as a result:

You can see here that not only could I pick a WHERE clause, but I can also do various joins and unions, support group by and sorting, limits, ...

Why Is This Better?

As I said, this is a better implementation than the old DynamicQuery framework, but you'll need more than my word for it to be true...

Less Error Prone

First and foremost, the DSLQuery is less error-prone than DynamicQuery. If we were going to build a DQ implementation of my method above, it would be:

public List getFoosByGroupId(final long groupId) {
  DynamicQuery dq = dynamicQuery();
		
  dq.add(RestrictionsFactoryUtil.eq("groupid", groupId));
		
  return dynamicQuery(dq);
}

So here we're getting a DynamicQuery instance, adding a restriction on groupId, then invoking the dynamicQuery() method to do the work.

But did you catch what I did there? I used a bad column name, "groupid" instead of "groupId" (note the change in capitalization of the letter I). Since the column name is just a string, this bug can be sitting in the code for who knows how long until it gets hit, and the exception it generates may be hard to resolve.

Since DSLQuery is based upon a DSL, I can't really enter a poorly-named column string value, the DSL will force me to use groupId correctly.

Class Loader Concerns

So the biggest hangup with the old DynamicQuery logic, you really had to be concerned about class loaders... So much so that when you check my blogs, I say things like "Don't use DynamicQueryFactoryUtil to create a DQ instance, use FooLocalService's dynamicQuery() method to create the instance and avoid the class loader issues".

As you can see from the code above, I didn't really have that concern. The DSLQuery guy operates in a different way (DynamicQuery was really an overlay on a similar Hibernate feature, so it was Hibernate and its proxies that caused the class loader issue), so the class loader concern is no longer an issue.

Full Service Builder Support

The new DSLQuery support is implemented at the Service Builder level; that means that all SB code, from the portal's services through to your custom SB services, all now support the DSLQuery stuff already, so you don't need to handle your own entities differently from core entities.

Complete Query Building

Although not so evident from my examples, but the screenshot should give you the idea that it is now possible to build a complete query, even complex queries, using DSLQuery and thus avoid having to build custom SQL strings which themselves can be error prone...

Compile Time Verification

And why is DSLQuery less error prone? Since it is implemented in Java classes and interfaces, the queries can now be validated at compile time instead of left to possibly fail at runtime.

Conclusion

So DSLQuery is now the best way to handle custom queries for all of the Service Builder entities. Problems we could have encountered via DynamicQuery or custom query implementations have been mitigated.

But we're not yet done... In my next blog, we'll see DSLQuery come back again as a key technique for performing queries that we otherwise might not have been able to complete...

I'll just leave you hanging until then...