In Liferay, querying data from the database using the older DynamicQuery method can become inefficient and error-prone, particularly when dealing with complex business logic involving multiple entities like User_, Role_, and custom tables. DynamicQuery struggles with advanced operations such as joins, grouping, and aggregation, and lacks compile-time verification, making it harder to maintain. DSLQuery, on the other hand, offers a more robust solution. It provides a domain-specific language that simplifies writing SQL-like queries, improving readability, error handling, and full support for complex operations. This leads to more efficient and maintainable data queries.
Prerequisite:
- Basic knowledge of Liferay
- Familiarity with Service builder
- Liferay DXP/Portal 7.4+
What is DSLQuery?
DSLQuery stands for Domain-Specific Language Query, a specialized query language designed to make database querying easier in Liferay. Unlike DynamicQuery, which can be complex and lacks type safety, DSLQuery offers a more structured and natural approach to building queries. Integrated into Liferay 7.4, DSLQuery enables developers to write SQL-like queries with compile-time validation, enhancing readability and simplifying the handling of complex operations. Whenever Liferay uses classes from the com.liferay.petra.sql.dsl package, it is using a DSLQuery.
In Liferay 7.4, Service Builder automatically generates DSLQuery classes for each entity, providing direct mappings to the corresponding database tables through Table classes. This makes querying and manipulating data more efficient, supporting operations such as joins, aggregations, and sorting.
Let's Understand DSLQuery
After building the Service Builder, the system generates a EntityTable.java class for every entity defined in the service.xml file. To illustrate this, let's explore the use of DSLQuery with Liferay’s default tables, User_ and Role_.
package com.liferay.portal.kernel.model;
import com.liferay.petra.sql.dsl.Column;
import com.liferay.petra.sql.dsl.base.BaseTable;
import java.sql.Types;
import java.util.Date;
public class UserTable extends BaseTable<UserTable> {
public static final UserTable INSTANCE = new UserTable();
public final Column<UserTable, Long> mvccVersion = createColumn(
"mvccVersion", Long.class, Types.BIGINT, Column.FLAG_NULLITY);
public final Column<UserTable, Long> ctCollectionId = createColumn(
"ctCollectionId", Long.class, Types.BIGINT, Column.FLAG_PRIMARY);
public final Column<UserTable, String> uuid = createColumn(
"uuid_", String.class, Types.VARCHAR, Column.FLAG_DEFAULT);
public final Column<UserTable, String> externalReferenceCode = createColumn(
"externalReferenceCode", String.class, Types.VARCHAR,
Column.FLAG_DEFAULT);
public final Column<UserTable, Long> userId = createColumn(
"userId", Long.class, Types.BIGINT, Column.FLAG_PRIMARY);
public final Column<UserTable, Long> companyId = createColumn(
"companyId", Long.class, Types.BIGINT, Column.FLAG_DEFAULT);
public final Column<UserTable, Date> createDate = createColumn(
"createDate", Date.class, Types.TIMESTAMP, Column.FLAG_DEFAULT);
public final Column<UserTable, Date> modifiedDate = createColumn(
"modifiedDate", Date.class, Types.TIMESTAMP, Column.FLAG_DEFAULT);
public final Column<UserTable, Long> contactId = createColumn(
"contactId", Long.class, Types.BIGINT, Column.FLAG_DEFAULT);
public final Column<UserTable, String> password = createColumn(
"password_", String.class, Types.VARCHAR, Column.FLAG_DEFAULT);
public final Column<UserTable, Boolean> passwordEncrypted = createColumn(
"passwordEncrypted", Boolean.class, Types.BOOLEAN, Column.FLAG_DEFAULT);
public final Column<UserTable, Boolean> passwordReset = createColumn(
"passwordReset", Boolean.class, Types.BOOLEAN, Column.FLAG_DEFAULT);
public final Column<UserTable, Date> passwordModifiedDate = createColumn(
"passwordModifiedDate", Date.class, Types.TIMESTAMP,
Column.FLAG_DEFAULT);
public final Column<UserTable, String> digest = createColumn(
"digest", String.class, Types.VARCHAR, Column.FLAG_DEFAULT);
public final Column<UserTable, String> reminderQueryQuestion = createColumn(
"reminderQueryQuestion", String.class, Types.VARCHAR,
Column.FLAG_DEFAULT);
public final Column<UserTable, String> reminderQueryAnswer = createColumn(
"reminderQueryAnswer", String.class, Types.VARCHAR,
Column.FLAG_DEFAULT);
public final Column<UserTable, Integer> graceLoginCount = createColumn(
"graceLoginCount", Integer.class, Types.INTEGER, Column.FLAG_DEFAULT);
public final Column<UserTable, String> screenName = createColumn(
"screenName", String.class, Types.VARCHAR, Column.FLAG_DEFAULT);
public final Column<UserTable, String> emailAddress = createColumn(
"emailAddress", String.class, Types.VARCHAR, Column.FLAG_DEFAULT);
public final Column<UserTable, Long> facebookId = createColumn(
"facebookId", Long.class, Types.BIGINT, Column.FLAG_DEFAULT);
public final Column<UserTable, String> googleUserId = createColumn(
"googleUserId", String.class, Types.VARCHAR, Column.FLAG_DEFAULT);
public final Column<UserTable, Long> ldapServerId = createColumn(
"ldapServerId", Long.class, Types.BIGINT, Column.FLAG_DEFAULT);
public final Column<UserTable, String> openId = createColumn(
"openId", String.class, Types.VARCHAR, Column.FLAG_DEFAULT);
public final Column<UserTable, Long> portraitId = createColumn(
"portraitId", Long.class, Types.BIGINT, Column.FLAG_DEFAULT);
public final Column<UserTable, String> languageId = createColumn(
"languageId", String.class, Types.VARCHAR, Column.FLAG_DEFAULT);
public final Column<UserTable, String> timeZoneId = createColumn(
"timeZoneId", String.class, Types.VARCHAR, Column.FLAG_DEFAULT);
public final Column<UserTable, String> greeting = createColumn(
"greeting", String.class, Types.VARCHAR, Column.FLAG_DEFAULT);
public final Column<UserTable, String> comments = createColumn(
"comments", String.class, Types.VARCHAR, Column.FLAG_DEFAULT);
public final Column<UserTable, String> firstName = createColumn(
"firstName", String.class, Types.VARCHAR, Column.FLAG_DEFAULT);
public final Column<UserTable, String> middleName = createColumn(
"middleName", String.class, Types.VARCHAR, Column.FLAG_DEFAULT);
public final Column<UserTable, String> lastName = createColumn(
"lastName", String.class, Types.VARCHAR, Column.FLAG_DEFAULT);
public final Column<UserTable, String> jobTitle = createColumn(
"jobTitle", String.class, Types.VARCHAR, Column.FLAG_DEFAULT);
public final Column<UserTable, Date> loginDate = createColumn(
"loginDate", Date.class, Types.TIMESTAMP, Column.FLAG_DEFAULT);
public final Column<UserTable, String> loginIP = createColumn(
"loginIP", String.class, Types.VARCHAR, Column.FLAG_DEFAULT);
public final Column<UserTable, Date> lastLoginDate = createColumn(
"lastLoginDate", Date.class, Types.TIMESTAMP, Column.FLAG_DEFAULT);
public final Column<UserTable, String> lastLoginIP = createColumn(
"lastLoginIP", String.class, Types.VARCHAR, Column.FLAG_DEFAULT);
public final Column<UserTable, Date> lastFailedLoginDate = createColumn(
"lastFailedLoginDate", Date.class, Types.TIMESTAMP,
Column.FLAG_DEFAULT);
public final Column<UserTable, Integer> failedLoginAttempts = createColumn(
"failedLoginAttempts", Integer.class, Types.INTEGER,
Column.FLAG_DEFAULT);
public final Column<UserTable, Boolean> lockout = createColumn(
"lockout", Boolean.class, Types.BOOLEAN, Column.FLAG_DEFAULT);
public final Column<UserTable, Date> lockoutDate = createColumn(
"lockoutDate", Date.class, Types.TIMESTAMP, Column.FLAG_DEFAULT);
public final Column<UserTable, Boolean> agreedToTermsOfUse = createColumn(
"agreedToTermsOfUse", Boolean.class, Types.BOOLEAN,
Column.FLAG_DEFAULT);
public final Column<UserTable, Boolean> emailAddressVerified = createColumn(
"emailAddressVerified", Boolean.class, Types.BOOLEAN,
Column.FLAG_DEFAULT);
public final Column<UserTable, Integer> type = createColumn(
"type_", Integer.class, Types.INTEGER, Column.FLAG_DEFAULT);
public final Column<UserTable, Integer> status = createColumn(
"status", Integer.class, Types.INTEGER, Column.FLAG_DEFAULT);
private UserTable() {
super("User_", UserTable::new);
}
}
The above code shows the generated UserTable.java class for the User_ table. Liferay generates these types of classes within the entity-api module under the model package. A few important points about this class:
- It is specific to the entity and contains a singleton variable INSTANCE.
- Each column in the table is represented as a Column field within the class.
This class serves several purposes, including generating SQL commands for table creation and simplifying the use of DSLQuery-based queries. With this setup, writing queries using DSLQuery becomes more straightforward compared to the older DynamicQuery approach.
Basic DSL query
DSLQuery in Liferay allows us to write database queries similar to SQL, with added advantages such as type safety, readability, and maintainability. Compared to DynamicQuery, DSLQuery is more intuitive and easier to work with. Liferay provides various classes and methods to create complex queries that involve operations like joins, aggregations, and grouping.
Let’s begin by examining a simple DSLQuery example that retrieves all users from the User_ table. You can execute the following Groovy script directly in Liferay’s Control Panel under Server Administration > Script:
Note: I created these scripts for Liferay Portal GA86. The column names and package paths may vary in other versions, so please adjust them according to your server version.
import com.liferay.petra.sql.dsl.DSLQueryFactoryUtil;
import com.liferay.petra.sql.dsl.query.DSLQuery;
import com.liferay.portal.kernel.model.User;
import com.liferay.portal.kernel.model.UserTable;
import com.liferay.portal.kernel.service.UserLocalServiceUtil;
import java.util.List;
DSLQuery dslQuery = DSLQueryFactoryUtil.select().from(UserTable.INSTANCE);
List<User> allUserList = UserLocalServiceUtil.dslQuery(dslQuery);
for (int i = 0; i < allUserList.size(); i++) {
out.println(allUserList.get(i));
}
This script will retrieve all the users from the User_ table. To remove any duplicate values, DSLQuery provides the selectDistinct() method
Output:
Querying Specific Columns
By specifying parameters in the select() method, we can retrieve specific columns from the table. Below is an example that retrieves only the firstName of all users:
import com.liferay.petra.sql.dsl.DSLQueryFactoryUtil;
import com.liferay.petra.sql.dsl.query.DSLQuery;
import com.liferay.portal.kernel.model.UserTable;
import com.liferay.portal.kernel.service.UserLocalServiceUtil;
import java.util.List;
DSLQuery dslQuery = DSLQueryFactoryUtil.select(UserTable.INSTANCE.firstName).from(UserTable.INSTANCE);
List<String> allUserList = UserLocalServiceUtil.dslQuery(dslQuery);
out.println(allUserList.size());
for (int i = 0; i < allUserList.size(); i++) {
out.println(allUserList.get(i));
}
Output:
Count Query
You can also create DSL queries to perform operations like selectDistinct() or count(). Below is an example that counts the total number of users in the User_ table:
import com.liferay.petra.sql.dsl.DSLQueryFactoryUtil;
import com.liferay.petra.sql.dsl.query.DSLQuery;
import com.liferay.portal.kernel.model.UserTable;
import com.liferay.portal.kernel.service.UserLocalServiceUtil;
DSLQuery dslQuery = DSLQueryFactoryUtil.count().from(UserTable.INSTANCE);
out.println(UserLocalServiceUtil.dslQuery(dslQuery));
Output:
Using Where Clauses and Predicates in DSLQuery
In DSLQuery, conditions in queries are handled using predicates, which represent logical conditions for filtering data. Predicates allow you to add conditions to your queries, similar to the WHERE clause in traditional SQL. Liferay provides the where() method in DSLQuery to apply these predicates, making it easy to filter data based on specific criteria.
A predicate in Liferay is essentially an expression that evaluates to true or false, used to define conditional logic. For instance, you can create a predicate to filter users by userId, as shown in the example below.
You can run the following Groovy script to see how predicates work in DSLQuery:
import com.liferay.petra.sql.dsl.DSLQueryFactoryUtil;
import com.liferay.petra.sql.dsl.query.DSLQuery;
import com.liferay.petra.sql.dsl.expression.Predicate;
import com.liferay.portal.kernel.model.User;
import com.liferay.portal.kernel.model.UserTable;
import com.liferay.portal.kernel.service.UserLocalServiceUtil;
import java.util.List;
// Define a predicate to filter by userId
Predicate predicate = UserTable.INSTANCE.userId.eq(20123L);
// Construct the DSLQuery with the where clause
DSLQuery dslQuery = DSLQueryFactoryUtil.select().from(UserTable.INSTANCE).where(predicate);
// Execute the query and retrieve the results
List<String> allUserList = UserLocalServiceUtil.dslQuery(dslQuery);
for (int i = 0; i < allUserList.size(); i++) {
out.println(allUserList.get(i));
}
In this example, the predicate is used to create a condition where the userId equals 20123. The where() method in the DSLQuery applies this condition, which is equivalent to a WHERE clause in an SQL query.
Output:
Joining Tables in DSLQuery
By using DSLQuery, you can achieve joins between multiple tables efficiently. In the following example, we retrieve user information along with the role IDs associated with each user from the Users_Roles table.
import com.liferay.petra.sql.dsl.DSLQueryFactoryUtil;
import com.liferay.petra.sql.dsl.query.DSLQuery;
import com.liferay.petra.sql.dsl.expression.Predicate;
import com.liferay.portal.kernel.model.UserTable;
import com.liferay.portal.kernel.model.Users_RolesTable;
import com.liferay.portal.kernel.service.UserLocalServiceUtil;
import java.util.List;
// Define predicates for joining tables
Predicate userRolesPredicate = UserTable.INSTANCE.userId.eq(Users_RolesTable.INSTANCE.userId);
// Construct the DSLQuery with the inner join
DSLQuery dslQuery = DSLQueryFactoryUtil
.select(
UserTable.INSTANCE.userId,
UserTable.INSTANCE.firstName,
UserTable.INSTANCE.lastName,
Users_RolesTable.INSTANCE.roleId
)
.from(UserTable.INSTANCE)
.innerJoinON(Users_RolesTable.INSTANCE, userRolesPredicate);
// Execute the query and retrieve the results
List<Object[]> resultSet = UserLocalServiceUtil.dslQuery(dslQuery);
for (Object[] result : resultSet) {
String userId = (String) result[0];
String firstName = (String) result[1];
String lastName = (String) result[2];
Long roleId = (Long) result[3]; // Retrieve roleId directly from Users_RolesTable
out.println("UserId: " + userId + ", FirstName: " + firstName + ", LastName: " + lastName + ", RoleId: " + roleId);
}
Output:
This query illustrates how to join the User_ and Users_Roles tables using DSLQuery to efficiently retrieve the user ID, first name, last name, and role ID.
Group By using DSL Query
In Liferay DSL queries, you can group data using the groupBy() method. This allows for efficient aggregation of data, such as grouping users by specific fields like roleId or counting users within each group. To better understand this concept and see the results, run the script below.
import com.liferay.petra.sql.dsl.DSLQueryFactoryUtil;
import com.liferay.petra.sql.dsl.DSLFunctionFactoryUtil;
import com.liferay.petra.sql.dsl.query.DSLQuery;
import com.liferay.portal.kernel.model.Users_RolesTable;
import com.liferay.portal.kernel.model.UserTable;
import com.liferay.portal.kernel.service.UserLocalServiceUtil;
import java.util.List;
// Construct the DSLQuery to group by roleId and count the number of users per role
DSLQuery dslQuery = DSLQueryFactoryUtil
.select(
Users_RolesTable.INSTANCE.roleId,
DSLFunctionFactoryUtil.count(UserTable.INSTANCE.userId).as("userCount")
)
.from(Users_RolesTable.INSTANCE)
.innerJoinON(UserTable.INSTANCE, UserTable.INSTANCE.userId.eq(Users_RolesTable.INSTANCE.userId))
.groupBy(Users_RolesTable.INSTANCE.roleId);
// Execute the query and retrieve the results
List<Object[]> resultSet = UserLocalServiceUtil.dslQuery(dslQuery);
for (Object[] result : resultSet) {
Long roleId = (Long) result[0];
Long userCount = (Long) result[1];
out.println("RoleId: " + roleId + ", UserCount: " + userCount);
}
Output:
This query groups users by roleId and counts the number of users per role.
Aggregation using DSL Query
To demonstrate how you can use aggregation functions like max() and min() in a Liferay DSLQuery, here’s a concise example of retrieving the maximum and minimum userId from the User table:
import com.liferay.petra.sql.dsl.DSLFunctionFactoryUtil;
import com.liferay.petra.sql.dsl.DSLQueryFactoryUtil;
import com.liferay.petra.sql.dsl.query.DSLQuery;
import com.liferay.portal.kernel.model.UserTable;
import com.liferay.portal.kernel.service.UserLocalServiceUtil;
import java.util.List;
// Construct the DSLQuery to get the max and min userId from UserTable
DSLQuery dslQuery = DSLQueryFactoryUtil
.select(
DSLFunctionFactoryUtil.max(UserTable.INSTANCE.userId).as("maxUserId"), // Alias for max userId
DSLFunctionFactoryUtil.min(UserTable.INSTANCE.userId).as("minUserId") // Alias for min userId
)
.from(UserTable.INSTANCE);
// Execute the query and retrieve the results
List<Object[]> resultSet = UserLocalServiceUtil.dslQuery(dslQuery);
for (Object[] result : resultSet) {
Long maxUserId = (Long) result[0];
Long minUserId = (Long) result[1];
out.println("Max UserId: " + maxUserId + ", Min UserId: " + minUserId);
}
Output:
This example uses the DSLFunctionFactoryUtil.max() and DSLFunctionFactoryUtil.min() functions to aggregate user data, allowing you to easily retrieve the highest and lowest user IDs from the User table.
Why DSLQuery is Better than DynamicQuery?
When comparing DSLQuery with DynamicQuery, several advantages make DSLQuery the better option for Liferay developers:
- Type Safety: DSLQuery provides compile-time verification, ensuring that your queries are free from common syntax errors and type mismatches, unlike DynamicQuery, which only verifies at runtime.
- Readability: DSLQuery is more structured and easier to understand, resembling traditional SQL in form. DynamicQuery can become verbose and hard to follow, especially for complex operations.
- Join Support: DSLQuery natively supports SQL-like joins, allowing developers to retrieve data from multiple related tables. This is a more natural and efficient way to handle relational data compared to DynamicQuery.
- Better Performance: With DSLQuery, you can write optimized and direct database queries, reducing the overhead that comes with the abstracted nature of DynamicQuery.
- Aggregation and Grouping: DSLQuery makes it easy to perform operations like group by, max(), min(), and other SQL aggregate functions. In DynamicQuery, achieving similar results requires more complex and less intuitive code.
- Code Maintenance: DSLQuery offers a domain-specific language that is more maintainable over time, as the structure and intent of the query are clearer and better integrated into the codebase.
Conclusion
Incorporating DSLQuery into your Liferay development workflow significantly enhances the efficiency, readability, and performance of your database operations. With features like type safety, native join support, and easy aggregation functions, DSLQuery is a powerful alternative to the older DynamicQuery approach. Whether you're performing simple data retrieval or more advanced queries with joins and groupings, DSLQuery simplifies the process while ensuring better maintainability of your codebase. Adopting DSLQuery in Liferay 7.4 and beyond is a step toward creating more scalable and robust applications.
