Using Projection in Dynamic Query in Liferay 7

In my previous blog Dynamic Query in Liferay 7 we have discussed about the basic of Dynamic Query in Liferay. In this blog we will discuss how to use projection in Dynamic queries. In my earlier blogs we discussed about how to retrieve entire entity. We have to use projection if we need a specific column of an entity. For example – If we want only emailAddress from User entity, we need to use projection.

1. Like my last blog, we will use the same liferay project that we created while demonstrating the logger example. i.e. Using Logger in Liferay 7

2. Now we will use Projection to retrieve “emailAddress” column from User entity using dynamic query to retrieve record from User_. (SQL – SELECT user.emailAddress FROM user_ user WHERE user.emailAddress like ‘test%’;)


_log.info("START...Retrieve user information...");
DynamicQuery userQuery = DynamicQueryFactoryUtil.forClass(User.class, "user",
PortalClassLoaderUtil.getClassLoader());
userQuery.add(RestrictionsFactoryUtil.like("user.emailAddress", "test%"));
userQuery.setProjection(PropertyFactoryUtil.forName("user.emailAddress"));

try {
List<Object> userEmailList = UserLocalServiceUtil.dynamicQuery(userQuery);
for (Object userEmail : userEmailList) {
_log.info("Email ID: " + userEmail);
}
} catch (SystemException e) {
_log.error(e);
}
_log.info("STOP...Retrieve user information...");

OUTPUT–


[BlogLoggerPortlet:43] START...Retrieve user information...
[BlogLoggerPortlet:52] Email ID: test@liferay.com
[BlogLoggerPortlet:57] STOP...Retrieve user information...

3. Now we will use Projection to retrieve multiple columns i.e. “userId”,“emailAddress” from User entity using dynamic query to retrieve record from User_. (SQL – SELECT user.userId, user.emailAddress FROM user_ user WHERE user.emailAddress like ‘test%’;)


_log.info("START...Retrieve user information...");
DynamicQuery userQuery = DynamicQueryFactoryUtil.forClass(User.class, "user",
PortalClassLoaderUtil.getClassLoader());
userQuery.add(RestrictionsFactoryUtil.like("user.emailAddress", "test%"));

ProjectionList projectionList = ProjectionFactoryUtil.projectionList();
projectionList.add(PropertyFactoryUtil.forName("user.userId"));
projectionList.add(PropertyFactoryUtil.forName("user.emailAddress"));

userQuery.setProjection(projectionList);

try {
List<Object[]> userInfoList = UserLocalServiceUtil.dynamicQuery(userQuery);
for (Object[] userInfo : userInfoList) {
_log.info("User ID: " + userInfo[0] + " Email ID: " + userInfo[1]);
}
} catch (SystemException e) {
_log.error(e);
}
_log.info("STOP...Retrieve user information...");

OUTPUT –


[BlogLoggerPortlet:45] START...Retrieve user information...
[BlogLoggerPortlet:59] User ID: 20164 Email ID: test@liferay.com
[BlogLoggerPortlet:64] STOP...Retrieve user information...