Dynamic Query in Liferay 7

Liferay provides many ways to fetch data from database. Below are the two commonly used ways,

  1. Custom SQL – This process is service builder dependent and can address all kind of requirement if we like write custom SQL queries. The only problem is we have to build service again and again.
  2. Dynamic Query – This is a super feature provided by Liferay. There is no need for the liferay developer to know SQL queries. To fetch data Liferay provides some predefined classes. This is very similar to Hibernate Query Language (HQL).

Today we will go through the basics of Dynamic Queries in Liferay 7.

1. We will use the same liferay project that we created while demonstrating the logger example. i.e. Using Logger in Liferay 7

4-1

2. To use Dynamic Query, the first task is to determine what kind table we need to query on. By what kind of table, we mean “Liferay Generated Table” or “Custom Table”.

i. Liferay Generated Table (For example, user_, group_ etc.) –

DynamicQuery dynamicQuery = DynamicQueryFactoryUtil.forClass(<EntityName.class>,<alias> , PortalClassLoaderUtil.getClassLoader());

Ex:-
DynamicQuery dynamicQuery = DynamicQueryFactoryUtil.forClass(User.class, "user",PortalClassLoaderUtil.getClassLoader());

ii. Custom Table (Tables that we create to achieve custom requirements using service builder) –

DynamicQuery dynamicQuery = DynamicQueryFactoryUtil.forClass(<EntityName.class>,<alias> , PortletClassLoaderUtil.getClassLoader());

Ex:-
DynamicQuery dynamicQuery = DynamicQueryFactoryUtil.forClass(CustomNotification.class, "notification",PortletClassLoaderUtil.getClassLoader());

 

3. Now we will use dynamic query to retrieve record from User_. Let’s retrieve all records from USER_ table. (SQL – SELECT * FROM user_ user)

_log.info("START...Retrieve user information..."); 
    	
DynamicQuery userQuery = DynamicQueryFactoryUtil.forClass(User.class, "user",
			PortalClassLoaderUtil.getClassLoader());
try {
	List userList = UserLocalServiceUtil.dynamicQuery(userQuery);
	for (User user : userList) {
		_log.info("ID: " + user.getUserId() + " Name: " + user.getFirstName() + " Email ID: " + user.getEmailAddress());
	}
} catch (SystemException e) {
	_log.error(e);
}
_log.info("STOP...Retrieve user information...");

OUTPUT-

[BlogLoggerPortlet:39] START…Retrieve user information…

[BlogLoggerPortlet:46] ID: 20120 Name:  Email ID: default@liferay.com

[BlogLoggerPortlet:46] ID: 20164 Name: Test Email ID: test@liferay.com

[BlogLoggerPortlet:51] STOP…Retrieve user information…

4-2

4. Let’s retrieve records from USER_ table using LIKE on EMAILADDRESS. (SQL – SELECT * 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%"));
try {
	List userList = UserLocalServiceUtil.dynamicQuery(userQuery);
	for (User user : userList) {
		_log.info("ID: " + user.getUserId() + " Name: " + user.getFirstName() + " Email ID: " + user.getEmailAddress());
	}
} catch (SystemException e) {
	_log.error(e);
}
_log.info("STOP...Retrieve user information...");

OUTPUT-

[BlogLoggerPortlet:39] START…Retrieve user information…

[BlogLoggerPortlet:47] ID: 20164 Name: Test Email ID: test@liferay.com

[BlogLoggerPortlet:52] STOP…Retrieve user information…

4-3

5. Let’s retrieve records from USER_ table using IN operator on USERID. (SQL – SELECT * FROM user_ user WHERE user.userId IN (20120, 20164))


_log.info("START...Retrieve user information..."); 
    	
List userIdList = new ArrayList();
userIdList.add(20120L);
userIdList.add(20164L);

DynamicQuery userQuery = DynamicQueryFactoryUtil.forClass(User.class, "user",
		PortalClassLoaderUtil.getClassLoader());
userQuery.add(RestrictionsFactoryUtil.in("user.userId", userIdList));
try {
	List userList = UserLocalServiceUtil.dynamicQuery(userQuery);
	for (User user : userList) {
		_log.info("ID: " + user.getUserId() + " Name: " + user.getFirstName() + " Email ID: " + user.getEmailAddress());
	}
} catch (SystemException e) {
	_log.error(e);
}
_log.info("STOP...Retrieve user information...");

OUTPUT-

[BlogLoggerPortlet:40] START…Retrieve user information…

[BlogLoggerPortlet:52] ID: 20120 Name:  Email ID: default@liferay.com

[BlogLoggerPortlet:52] ID: 20164 Name: Test Email ID: test@liferay.com

[BlogLoggerPortlet:57] STOP…Retrieve user information…

4-4

There are lots of operators available in Liferay (i.e. in RestrictionsFactoryUtil class) like greaterThan, lessThan, equals etc.

 

Happy Coding… 🙂
Rupal Chatterjee
Associate Projects, Cognizant Technology Solutions
https://www.cognizant.com
Connect me on Google, Facebook