[Liferay6.2] Liferay Dynamic Query API Example

Introduced

Liferay provides several ways to define complex queries that are used to retrieve data from a database.

Typically, in each service entity, you can easily meet basic data query operations by defining some ' finder ' methods.

However, there are times when we may encounter some of the following finder queries that are not sufficient:

• Overly complex queries, such as sub-queries
• Some aggregation operations need to be implemented, such as Min, Max, AVG, etc.
• Want 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 do this through the Hibernate dynamic Query API provided by Liferay.

In this article, we'll show you how to build different types of dynamic query and execute them.

Dynamic Query Basic syntax

The code for building a dynamic query basic syntax in Liferay is as follows:

//build Dynamic Query equivalent to select * from Entity_nameDynamicquery dynamicquery = Dynamicqueryfactoryutil.forclass (Entity_name.class);//Dynamicqueryfactoryutil.forclass (Entity_name.class,portalclassloaderutil.getclassloader ());//Set Query Columnsdynamicquery.setprojection (Projection Projection);//Set query CriteriaDynamicquery.add (Criterion Criterion);//Set CollationDynamicquery.addorder (order order);//set the range of returned result setsDynamicquery.setlimit (intStartintend);//Execute dynamic query to get result setEntity_nameLocalserviceutil.dynamicquery (Dynamicquery);

which

Entity_name: The entity name, which is the name of the entities established in Service.xml.

Dynamicquery can also be initialized by Dynamicquery Forclass (class<?> clazz, ClassLoader ClassLoader).

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);d ynamicquery.add (Propertyfactoryutil.forname ("Parentorganizationid"). EQ (0L)); List<Organization> organizations = Organizationlocalserviceutil.dynamicquery (Dynamicquery);

3. Like, >, >=, <, <=, between ...

//SELECT * from Organization_ where name is like ' organizational body% ';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;//1th method (not for OR)Dynamicquery.add (Propertyfactoryutil.forname ("OrganizationId"). GE (21212L));d Ynamicquery.add (Propertyfactoryutil.forname ("OrganizationId"). Le (21224L));//2nd method (for or, using restrictionsfactoryutil.or)Criterion Criterion =NULL; criterion= Restrictionsfactoryutil.ge ("OrganizationId", 21212L); criterion= Restrictionsfactoryutil.and (Criterion, Restrictionsfactoryutil.le ("OrganizationId", 21224L) );d Ynamicquery.add (criterion);//3rd Method (for or, use Restrictionsfactoryutil.disjunction ())Junction Junction = Restrictionsfactoryutil.conjunction ();
Junction.add (Propertyfactoryutil.forname ("OrganizationId"). GE (21212L));
Junction.add (Propertyfactoryutil.forname ("OrganizationId"). Le (21224L) );d Ynamicquery.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"));d Ynamicquery.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"));p Rojectionlist.add (Projectionfactoryutil.property ("Name") );d ynamicquery.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 ();p Rojectionlist.add ( Projectionfactoryutil.property("type"));p rojectionlist.add("Projectionfactoryutil.count" ("name") ) );p Rojectionlist.add (Projectionfactoryutil.groupproperty ("type"));d ynamicquery.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 min, AVG, etc. can refer to recursion.

10. Paging

// take 1th to 10th record dynamicquery.setlimit (0,10);

11. Composite PRIMARY Key

If the entity is a primary key and we want to query through the attribute column in the composite primary key, we need to precede the column name with "PrimaryKey.", as follows:

Dynamicquery.add (Propertyfactoryutil.forname ("Primarykey.organizationid"). GT (21212L));

Summary

These are just some basic examples to solve most of the problems we encounter in our daily development, in addition to the dynamic Query API provides some more advanced extension methods (Eqall, Geall, etc.), these people will explore together, later use to update.

from the example above, we can see that the dynamic Query API provided by Liferay is essentially a set of Java methods that compose SQL statements and execute and get results. Some friends may think that this method is too cumbersome, rather than directly write SQL to facilitate direct. But standing in the context of platform database compatibility, we'll see that this is the right approach. because Liferay supports MySQL, Oracle, DB2 and many other databases, if you write directly to SQL, it is likely to encounter other database syntax is not supported, such as the recursive query in Oracle MySQL is not supported. with the dynamic Query API, we can use a uniform set of syntax to build SQL statements without having to consider the differences in the underlying database, which significantly improves the portability and compatibility of the entire platform.

[Liferay6.2] Liferay Dynamic Query API Example