and clause in dynamic query

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

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…

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…