11/29/2010

DynamicQuery APi in Liferay

DynamicQuery API


If you want to build a 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.

So - how are they created ?

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:

DynamicQueryFactoryUtil
OrderFactoryUtil
ProjectionFactoryUtil
PropertyFactoryUtil  and
RestrictionsFactoryUtil

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

So ...lets get started.


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

So - at first, 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.

This is described in detail here:

http://liferay-blogging.blogspot.com/2011/01/adding-order-to-dynamicquery-in-liferay.html



If you liked this tutorial it would be very nice, if you could click on some of the google ads you see on the right side. It helps me run this block and motivates me ;)

If you have any questions, feel free to leave a comment.


11/21/2010

How to do a Subquery in Liferay using DynamicQuery (1)

So ... first time in english ... need more visitors ;)

Problem: Loading all Users together with their GroupRoles is not that easy. 

Using
  • UserLocalServieUtil.getUserById and 
  • UserGroupRoleLocalServiceUtil.hasUserGroupRole
is way to slow. We had the case where we were inspecting 2000 Users and it took more than 15 seconds to do that.

Solution: Use dynamic queries !!

DynamicQueries are the preferred way to specific Statements in Liferay that are translated directly into vendor - specific SQL, executed super-fast and return only what you need. No hidden lazy - loading afterwards, no need to verify roles one after another.

The good thing about the DynamicQueries is that they can be specified outside ext environment which makes it possible to define them in the portlet directly and to modify them without having to restart the Server (Liferay 5.x).

So here is a simple example:



DynamicQuery userGroupRoleQuery = DynamicQueryFactoryUtil.forClass(UserGroupRole.class, "userGroupRole",
PortalClassLoaderUtil.getClassLoader());
userGroupRoleQuery.setProjection(
ProjectionFactoryUtil.property("primaryKey.userId"));
UserGroupRoleLocalServiceUtil.dynamicQuery(userGroupRoleQuery );

So ... what do we do here ? First we specify to access the table for the Entity that referrs to the class UserGroupRole. We use the PortalClassLoader to gain access to the UserGroupRoleImpl class, otherwise a NullPointer will be the result.

Next - we use a Projection to change the return type from UserGroupRole objects to just the userId (of type long). We need to specify "primaryKey" because Liferay internally maps the userId, the groupId and the roleId to the primary key. This can be verified by taking a look at the hibernate configuration file at portal-hbm.xml in the portal sourcen under portal-impl/META-INF.

This query can now be executed and returns all userIds.

To connect this DynamicQuery with another DynamicQuery retrieving all Users we first remove the line where we execute the DynamicQuery and add the following :


DynamicQuery userQuery = DynamicQueryFactoryUtil.forClass(User.class,"user", PortalClassLoaderUtil.getClassLoader());
userQuery.add(PropertyFactoryUtil.forName("userId").in( userGroupRoleQuery));


This sends both Queries, the UserGroupRole query as subquery to the Database and lets the database handle the retrieval and optimization. For cases where databases only accept a limited number of entries in an "in" clause (Oracle accepts only 1000), this is also an optimization because it doesn´t matter how much results the the userGroupRoleQuery has - the database optimizes it.

In our case we gained a performance boost of about 1000% (10 times faster).

This was part 1 ... in part 2 we will handle several specialities gaining access to spacial database entries.


If you like this tutorial it would be very nice, if you could click on some of the google ads you see on the right side. It helps me run this block and motivates me ;)

If you have any questions, feel free to leave a comment.