Dynamic Query in Liferay

Today we will discuss about Dynamic Query in Liferay. As we already know Liferay Service Builder provide basic CRUD methods but sometimes we need specific methods with different conditions . For this we have two approaches:-

1)Custom Sql in Liferay.
2)Dynamic Query

The problem with custom query is that we need to build services again and again. But in case of dynamic queries no need to build services.

Note:- 

1)Dynamic Queries is used only to Retrieve Data.
2)If you know Hibernate it is similar to Criteria API.



Now Lets write some basic queries:-

First we decide on which table we need to write query.Their are two cases:-

1)Liferay Generated Table
Liferay automatically generated tables like user_, usergroup etc.
In this case we create dynamic query object as:-

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

view raw DynamicQuery.java hosted with ❤ by GitHub

2)Custom Tables
Tables you create for your requirement .In this case we create dynamic query as object :-

	DynamicQuery query = DynamicQueryFactoryUtil.forClass(Entity_Name.class, "Alias", PortletClassLoaderUtil.getClassLoader());
	Ex:-
	DynamicQuery countryQuery = DynamicQueryFactoryUtil.forClass(Country.class, "ct", PortletClassLoaderUtil.getClassLoader());

view raw DynamicQuery.java hosted with ❤ by GitHub

The basic difference between two statements is Class loader. For Liferay tables we use PortalClassLoaderUtil and for custom tables we use PortletClassLoaderUtil.

In this blog we will write query on user_ table ie User entity.

1)Select query with LIKE operation

	/**
	* * SELECT * FROM user_ user WHERE user.emailAddress like '%adit%';
	*/
	DynamicQuery userQuery = DynamicQueryFactoryUtil.forClass(User.class, "user",PortalClassLoaderUtil.getClassLoader());
	userQuery.add(RestrictionsFactoryUtil.like("user.emailAddress", "%adit%"));
	try {
	List<User> userList = UserLocalServiceUtil.dynamicQuery(userQuery);
	for (User user : userList) {
	System.out.println("user Id=>"+user.getUserId()+" Name=>"+user.getFirstName());
	}
	} catch (SystemException e) {
	e.printStackTrace();
	}

view raw Like.java hosted with ❤ by GitHub

OR

	/**
	* SELECT * FROM user_ user WHERE user.emailAddress like '%adit%';
	*/
	DynamicQuery userQuery = DynamicQueryFactoryUtil.forClass(User.class, "user",PortalClassLoaderUtil.getClassLoader());
	userQuery.add(PropertyFactoryUtil.forName("user.emailAddress").like("%adit%"));
	try {
	List<User> userList = UserLocalServiceUtil.dynamicQuery(userQuery);
	for (User user : userList) {
	System.out.println("user Id=>"+user.getUserId()+" user name=>"+user.getFirstName());
	}
	} catch (SystemException e) {
	e.printStackTrace();
	}

view raw Like2.java hosted with ❤ by GitHub

Explanation:-
Here both methods return the same result .The only difference between two is how we create the Criteria.In first we use RestrictionsFactoryUtil and in second we use PropertyFactoryUtil . Both do the same task you can use any one.



2)Select query with IN operation

	/**
	* SELECT * FROM user_ user WHERE user.userId IN(12206,12225,11374,12232);
	*/
	DynamicQuery userQuery = DynamicQueryFactoryUtil.forClass(User.class, "user",PortalClassLoaderUtil.getClassLoader());
	List<Long> userIds = new ArrayList<Long>();
	userIds.add(12206L);
	userIds.add(12225L);
	userIds.add(11374L);
	userIds.add(12232L);
	userQuery.add(RestrictionsFactoryUtil.in("user.userId", userIds));
	try {
	List<User> userList = UserLocalServiceUtil.dynamicQuery(userQuery);
	for (User user : userList) {
	System.out.println("User Id=>"+user.getUserId()+" User Name=>"+user.getFirstName());
	}
	} catch (SystemException e) {
	e.printStackTrace();
	}

view raw In.java hosted with ❤ by GitHub

Similarly you can try other operation like equal , between ,greater than, less than etc.

In the next blog we will discuss another important topic Projection in Dynamic query.


Hope this will Help....

Related Post:-

Projection in Dynamic Query

Order By Clause using Dynamic Query in Liferay

AND/OR Clause using Dynamic Query

Joins using Dynamic Query in Liferay

Creating Services using Maven

Liferay Message Customization

Jasper Report with Eclipse

Validation in Liferay