introduce
Liferay provides several ways to define complex queries to retrieve data in a database.
Usually, in each service Entity, basic data query operations can be easily satisfied by defining some'finder'methods.
However, sometimes we may encounter the following situations that finder queries do not satisfy:

• Excessively complex queries, such as sub-queries
• Some aggregation operations need to be implemented, such as min, max, avg, etc.
• Want to get a composite object or tuple instead of a mapped object type
• Query optimization
• Complex data access, such as reports

To achieve this, you need to implement it through the Dynamic Query API of Hibernate provided by Liferay.

In this article, we will demonstrate how to build different types of Dynamic Queries and execute them.

Dynamic Query Basic Grammar

The code for building a basic Dynamic Query grammar in Liferay is as follows:

//Constructing dynamic queries is equivalent to select * from Entity_Name
DynamicQuery dynamicQuery = DynamicQueryFactoryUtil.forClass(Entity_Name.class);
//DynamicQueryFactoryUtil.forClass(Entity_Name.class,PortalClassLoaderUtil.getClassLoader());
//Setting query columns
dynamicQuery.setProjection(Projection projection);
//Setting query conditions
dynamicQuery.add(Criterion criterion);
//Setting collation rules
dynamicQuery.addOrder(Order order);
//Set the scope of the return result set
dynamicQuery.setLimit(int start, int end);
//Execute dynamic queries to get result sets
Entity_NameLocalServiceUtil.dynamicQuery(dynamicQuery);

Among them,

Entity_Name: Entity name, which is defined in service.xml.

DynamicQuery can also be initialized by DynamicQuery for Class (Class <?> clazz, ClassLoader class Loader).

Dynamic Query Application Example

1,select * from organization_;

DynamicQuery dynamicQuery = DynamicQueryFactoryUtil.forClass(Organization.class);
List<Organization> Organizations = OrganizationLocalServiceUtil.dynamicQuery(dynamicQuery);

2,select * from organization_ where parentOrganizationId=0;

DynamicQuery dynamicQuery = DynamicQueryFactoryUtil.forClass(Organization.class);
dynamicQuery.add(PropertyFactoryUtil.forName("parentOrganizationId").eq(0L));
List<Organization> Organizations = OrganizationLocalServiceUtil.dynamicQuery(dynamicQuery);

3,like,>,>=,<,<=,between ... and ... in...

// select * from organization_ where name like 'Organization%';
dynamicQuery.add(PropertyFactoryUtil.forName("parentOrganizationId").like("Organization%"));
// select * from organization_ where organizationId >21212;
dynamicQuery.add(PropertyFactoryUtil.forName("organizationId").gt(21212L));
// select * from organization_ where organizationId >=21212;
dynamicQuery.add(PropertyFactoryUtil.forName("organizationId").ge(21212L));
// select * from organization_ where organizationId <21224;
dynamicQuery.add(PropertyFactoryUtil.forName("organizationId").lt(21224L));
// select * from organization_ where organizationId <=21224;
dynamicQuery.add(PropertyFactoryUtil.forName("organizationId").le(21224L));
// select * from organization_ where organizationId between 21212 and 21224;
dynamicQuery.add(PropertyFactoryUtil.forName("organizationId").between(21212L, 21224L));

4,and / or

// select * from organization_ where organizationId >= 21212 and organizationId <=21224;
// Method 1 (not applicable) or)
dynamicQuery.add(PropertyFactoryUtil.forName("organizationId").ge(21212L));
dynamicQuery.add(PropertyFactoryUtil.forName("organizationId").le(21224L));
// Method 2 (applicable or，Use RestrictionsFactoryUtil.or)
Criterion criterion = null;
criterion = RestrictionsFactoryUtil.ge("organizationId", 21212L);
criterion = RestrictionsFactoryUtil.and(criterion, RestrictionsFactoryUtil.le("organizationId", 21224L));
dynamicQuery.add(criterion);
// Method 3 (applicable or，Use RestrictionsFactoryUtil.disjunction())
Junction junction = RestrictionsFactoryUtil.conjunction();
junction.add(PropertyFactoryUtil.forName("organizationId").ge(21212L));
junction.add(PropertyFactoryUtil.forName("organizationId").le(21224L));
dynamicQuery.add(junction);

5,order by

// select * from organization_ order by organizationId asc;
dynamicQuery.addOrder(OrderFactoryUtil.asc("organizationId"));
// select * from organization_ order by organizationId desc;
dynamicQuery.addOrder(OrderFactoryUtil.desc("organizationId"));

6. Sub query

// select * from organization_ where parentOrganizationId=(select organizationId from organization_ where name='Organization 1');
DynamicQuery subDynamicQuery = DynamicQueryFactoryUtil.forClass(Organization.class);
subDynamicQuery.setProjection(ProjectionFactoryUtil.property("organizationId"));
subDynamicQuery.add(PropertyFactoryUtil.forName("name").eq("Organization 1"));
dynamicQuery.add(PropertyFactoryUtil.forName("parentOrganizationId").in(subDynamicQuery));

7, custom columns

// select name from organization_;
dynamicQuery.setProjection(ProjectionFactoryUtil.property("name"));
List<Object> names = OrganizationLocalServiceUtil.dynamicQuery(dynamicQuery);
for(Object name: names){
	System.out.println(name);
}
// select organizationId,name from organization_;
ProjectionList projectionList = ProjectionFactoryUtil.projectionList();
projectionList.add(ProjectionFactoryUtil.property("organizationId"));
projectionList.add(ProjectionFactoryUtil.property("name"));
dynamicQuery.setProjection(projectionList);
List<Object[]> organizations = OrganizationLocalServiceUtil.dynamicQuery(dynamicQuery);
for(Object[] organization: organizations){
	System.out.println(organization[0]+":"+organization[1]);
}

8,distinct

// select distinct name from organization_;
Projection projection = ProjectionFactoryUtil.distinct(ProjectionFactoryUtil.property("name"));
dynamicQuery.setProjection(projection);

9,group by

// select type_,count(type_) from organization_ group by type_;
ProjectionList projectionList = ProjectionFactoryUtil.projectionList();
projectionList.add(ProjectionFactoryUtil.property("type"));
projectionList.add(ProjectionFactoryUtil.count("name"));
projectionList.add(ProjectionFactoryUtil.groupProperty("type"));
dynamicQuery.setProjection(projectionList);
List<Object[]> organizations = OrganizationLocalServiceUtil.dynamicQuery(dynamicQuery);
for(Object[] organization: organizations){
    System.out.println(organization[0]+":"+organization[1]);
}

In addition, the max aggregate function call method is as follows:

max:ProjectionFactoryUtil.max(String propertyName)

Other aggregation functions, such as min and avg, can be referred to as recursion.

10, paging

// Records from Articles 1 to 10
dynamicQuery.setLimit(0,10);

11. Composite primary key

If the entity matches the primary key, we need to add "primaryKey." before the column name if we want to query through the attribute column in the composite primary key, as follows:

dynamicQuery.add(PropertyFactoryUtil.forName("primaryKey.organizationId").gt(21212L));

summary

These are just some basic examples that can solve most of the problems we encounter in our daily development. In addition, the Dynamic Query API also provides some more advanced extension methods (eqAll, geAll, etc.). Let's explore them together and update them later.

From the above examples, we can see that the Dynamic Query API provided by Liferay is actually a set of java methods to compose SQL statements, execute and obtain results. Some friends may think that this method is too cumbersome to write SQL directly. However, from the perspective of platform database compatibility, we will find that this approach is very suitable. Because Liferay supports mysql, oracle, db2 and other databases, if you write SQL directly, you may encounter situations where the grammar of other databases does not support, such as recursive query in Oracle does not support mysql. Using the Dynamic Query API, we can use a unified grammar to construct SQL statements without considering the differences between the underlying databases, so that the portability and compatibility of the entire platform are greatly improved.