What is DSLQuery and how can it be used?

Introduction

DSLQuery is an acronym for Domain Specific Language Query. 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 more natural than what would have been built for a DynamicQuery implementation.

DSLQuery has been fully integrated into 7.4 and even Service Builder now generates DSLQuery classes for 7.4. Whenever a Liferay code is using a class from the com.liferay.petra.sql.dsl package, DSLQuery is being executed.

Tables

If a new Service Builder project is started, you get the service.xml and it defines the Foo entity. If you go ahead and just build the services, that will bring the first important class.

Check the -api module and go to the generated model package (in the example shown below it 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);
	}

}

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

This class has a number of uses, including being able to [re-]generate the SQL commands to create the table, etc. 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 will 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);

These methods are similar to dynamicQuery() of old, but they take a new object, the DSLQuery.

The next steps will help on how we create a DSLQuery instance.

Writing Queries

This is where we start using the FooTable. Once we have the Table and its contained Columns, we can now start building queries.

Starting 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);
}

The DSLQueryFactoryUtil is used to create the SELECT command. Please note that it also has a selectDistinct() method when duplicates are expected to be removed. There is a number of different methods, the no-arguments used here is basically equivalent to a SELECT *, but there is 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).

In the next step it is indicated the table which we are selecting from, in this case it is the FooTable.INSTANCE.

And finally we add our where clause, in this example it is used the FooTable.INSTANCE.groupId column along with the eq() expression. It is matching the groupId parameter passed in the method.

After building the DSLQuery instance, the dslQuery() method can be used to invoke the query and return the results. In this case, it is expected a list of Foos.

Now it is possible to start dealing with some complex queries. The DSLQuery can handle things like unions, joins, aliases, functions, complex where clauses, "group bys" and "sort bys", all using this type of domain specific language to build queries.

The great part about this is that the IDE will even help you build out your query.

Why is this better?

Less Error Prone

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

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

Here it is getting a DynamicQuery instance, adding a restriction on groupId, then invoking the dynamicQuery() method.

Please note that it 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 take too long to be caught, and the exception it generates may be hard to resolve.

Since DSLQuery is based upon a DSL, the user cannot really enter a poorly-named column string value, the DSL will force the user to use groupId correctly.

Class Loader Concerns

As it is possible to see from the code above, there was no concern about class loader. The DSLQuery 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 the user's custom SB services, all now support the DSLQuery already, so the users do not have to handle their own entities differently from core entities.

Complete Query Building

Although not so evident from the examples above, 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

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

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

Help Center

Productos

Recursos

Seguridad

Soporte

What is DSLQuery and how can it be used?

Dave Nebinger

Introduction