Tag Archives: Dynamic Query in Liferay 7

Using Projection in Dynamic Query in Liferay 7

Posted on by
Reply

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

5-1

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...

7-1

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...

7-2

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

Using AND / OR clause in Dynamic Query in Liferay 7

Posted on by
Reply

In my last 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 AND / OR operator in Dynamic queries.

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

5-1

2. Now we will use AND clause (Conjunction) in dynamic query to retrieve record from User_. (SQL – SELECT * FROM user_ user WHERE user.userId = 20164 AND user.emailAddress like ‘test%’;)

_log.info("START...Retrieve user information...");     	    	
DynamicQuery userQuery = DynamicQueryFactoryUtil.forClass(User.class, "user",
		PortalClassLoaderUtil.getClassLoader());

Conjunction conjunctionQuery = RestrictionsFactoryUtil.conjunction();
conjunctionQuery.add(RestrictionsFactoryUtil.eq("user.userId", 20164L));
conjunctionQuery.add(RestrictionsFactoryUtil.like("user.emailAddress", "test%"));

userQuery.add(conjunctionQuery);

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: 20164 Name: Test Email ID: test@liferay.com

[BlogLoggerPortlet:51] STOP…Retrieve user information…

5-2

3. Now we will use OR clause (Disjunction) in dynamic query to retrieve record from User_. (SQL – SELECT * FROM user_ user WHERE user.userId = 20120 OR user.emailAddress like ‘test%’;)

_log.info("START...Retrieve user information...");     	    	
DynamicQuery userQuery = DynamicQueryFactoryUtil.forClass(User.class, "user",
		PortalClassLoaderUtil.getClassLoader());

Disjunction disjunctionQuery = RestrictionsFactoryUtil.disjunction();
disjunctionQuery.add(RestrictionsFactoryUtil.eq("user.userId", 20120L));
disjunctionQuery.add(RestrictionsFactoryUtil.like("user.emailAddress", "test%"));

userQuery.add(disjunctionQuery);

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:41] START…Retrieve user information…

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

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

[BlogLoggerPortlet:59] STOP…Retrieve user information…

5-3

 

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

Dynamic Query in Liferay 7

Posted on by
4

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