Dynamic Query in Liferay 6.1

Dynamic Query :

Portlet that retrieves a lot of data from liferay data table, and eventually combines them by filtering out Users, Roles or Groups by certain criterias you will certainly come to a point, where the Standard "LocalServideUtil" - Methods are just too slow.
The best way to get around all that Java Reflection based Stuff is to create dynamic queries.

Liferay offers several factory classes helping with the creation of Dynamic queries right in your portlet.

They are in the package com.liferay.portal.kernel.dao.orm:

1. DynamicQueryFactoryUtil
2. OrderFactoryUtil
3. ProjectionFactoryUtil
4. PropertyFactoryUtil and
5. RestrictionsFactoryUtil

Inspecting those 5 should be enough for most of your needs.

So ...lets get started with an example.



DynamicQuery query = DynamicQueryFactoryUtil.forClass(Model.class, PortletClassLoaderUtil.getClassLoader("test_WAR_testportlet"));


// test_WAR_testportlet is portlet id in Portlet table


query.add(RestrictionsFactoryUtil.eq("primaryKey.empID",101);
query.add(RestrictionsFactoryUtil.eq("eventId",10101);

List<EmpModel> resultList = EmpLocalSerciceUtil.dynamicQuery(query);

Emp empModel  = (Emp) resultList.get(0);

empModel.setEventStatus("CLOSE");

EmpLocalServiceUtil.updateEmp(empModel);


Creating a DynamicQuery always starts with determining the Entity you want to retrieve.
For example : If you want to retrieve all Users, the class you should retrieve is com.liferay.portal.model.User

create a DynamicQuery object :

DynamicQuery UserQuery = DynamicQueryFactoryUtil.forClass(
User.class, PortletClassloaderUtil.getClassLoader());

This creates a dynamic query that goes straight for the User class.
The Portletclassloader makes sure, that the implementation class can be loaded (UserImpl.java).

So - as in SQL you have the possibility to order your result, to create a projection f.e. only returning the id of the user and you can add restrictions, for example to a property.

at first - lets try out to only retrieve users, that have the first name "Alfred".

to do so, we use the PropertyFactoryUtil :

Criterion alfredsName = PropertyFactoryUtil.forName("firstName").eq("Alfred");

PropertyFactoryUtil doesn´t offer much more than this forName factory method. But this method returns a Property instance that allows us to create Criterion objects and to assign them to the DynamicQuery.

The Property Factory allows to test for equality, if certain properties are greater or lower and to test if values are in a list provided.

There are about 30 - 40 different methods that should cover most of your needs.

We will cover only eq, in and like here. The rest will follow in a later post.

eq : EQ tests for equality - very fast ! Note that "Alfred" is notthe same as "alfred" - depending on the System the database runs on.

in : Very powerful, because it allows to take another DynamicQuery allowing the database to optimize the Query and to returns results very fast.

like : Typical SQL "like" ... is case sensitive on case sensitive systems.

But what do we do if we need to test for a name, without looking for the case ... what about "aLfreD" ?

In this case, we need to use RestrictionsFactoryUtil. It has a method called "ilike" :

ilke: Case insensitive SQL "like" and also the only possibility to retrieve all "alFreD"s in the Database - no matter how they are written. ilike also allows us to specify the "%" character we need if we want to retrieve "Walfred".

So much about the PropertyFactoryUtil. Here is the code to retrieve all Alfred - Users with the last name "Einstein":

DynamicQuery userQuery = DynamicQueryFactoryUtil.forClass(
User.class, PortletClassloaderUtil.getClassLoader());


userQuery.add(PropertyFactoryUtil.forName("lastName").eq("Einstein");
userQuery.add(RestrictionsFactoryUtil.ilike("firstName","%Alfred%"));

When working with dynamic queries it is sometimes necessary not to retrieve the complete Entities but only to retrieve certain properties - like the user id.

This is the point where the ProjectionFactoryUtil comes into place.

Projections allow (for example) to use distincts, counts, sums or to just return a single property.

This is what we will do next.

To create a Projection, just use the ProjectionFactoryUtil like this:

Projection projection = ProjectionFactoryUtil.property("userId");

That´s all to limit the result to the userId.

You can then set the projection to the dynamic query like this only returning distinct userIds like this:

userQuery.setProjection(ProjectionFactoryUtil
.distinct(ProjectionFactoryUtil.property("userId")));

Note: Of course there can be only one Projection for a query. But Projections can be nested as shown in the previous example.

Restrictions:

Restrictions basically allow to do the same as Property. There are methods to test for equality, to test whether properties are in a certain range, to test several properties at once ("and") or to test a property against a map.
For example, it allows to test whether first and last name equal for certain persons:

Criterion equalNames = RestrictionsFactoryUtil.eqProperty("firstName","lastName");

Order

The OrderFactoryUtil allows to order the results of a query by a property - ascending and descending.