Working with Dynamic Query In Liferay

Introduction of Dynamic query:-

Liferay provides several ways by which we can retrieve data from database. One of them is dynamic query. You can easily fire complex query using dynamic query and it will reduce overhead of creating custom finder methods


How to build Dynamic Query:

DynamicQuery dynamicQuery= DynamicQueryFactoryUtil.forClass(Entity_Name.class, PortalClassLoaderUtil.getClassLoader());

OR

DynamicQuery dynamicQuery = DynamicQueryFactoryUtil.forClass(Entity_Name.class, PortletClassLoaderUtil.getClassLoader());

Note 1: If you working with dynamic query inside your custom portlet and trying to access liferay core entity (i.e, database) then use Portal Class Loader.

 Example like :
Dealing with Liferay's built in entities like Users, Organizations, Assets, AssetCategories 
As DynamicQuery API looks for current classloader, for access, but as you are dealing with Portal level
class [User.class], you have to specify portalClassLoader as below.

 ClassLoader portalClassLoader = PortalClassLoaderUtil.getClassLoader();  
 DynamicQuery query =DynamicQueryFactoryUtil.forClass(User.class, portalClassLoader);  

-
Note 2: Dealing with custom entity within portlet.

DynamicQuery dynamicQuery=DynamicQueryFactoryUtil.forClass(Application.class);

example1:-

1.SELECT * FROM User_ WHERE lastName='Bloggs'

Dynamic Query:

DynamicQuer  dynamicQuery  = DynamicQueryFactoryUtil.forClass(User.class, PortalClassLoaderUtil.getClassLoader());

dynamicQuery.add(PropertyFactoryUtil.forName("lastName").eq("Bloggs"));

List<User> userList = UserLocalServiceUtil.dynamicQuery(dynamicQuery);

Example2:-

DynamicQuery dynamicQuery = DynamicQueryFactoryUtil.forClass(MyCustomTable.class); dynamicQuery.add(PropertyFactoryUtil.forName("status").eq("Pending") dynamicQuery.add(PropertyFactoryUtil.forName("userId").eq(10122); Above query will search in table MycustomTable for records which has status as Pending and userId as 10122. If you want to sort your records in particular order that also you can do.  Example3:- 2. SELECT * FROM User_ WHERE lastName like 'ord%' Dynamic Query: DynamicQuery dynamicQuery = DynamicQueryFactoryUtil.forClass(User.class, PortalClassLoaderUtil.getClassLoader()); dynamicQuery.add(PropertyFactoryUtil.forName("lastName").like("ord%")); List<User> userList = UserLocalServiceUtil.dynamicQuery(dynamicQuery); About Dynamic Query: I am assuming that you are aware of Liferay Service Builder and you have basic understanding of persisting data by service builder tool. Consider that you have developed your portlet and you have persisted a lot many records in the database. You are a java developer and you don't have much knowledge on queering database. Liferay Dynamic Query is the rescue in this case. Liferay provides API to query the database just like writing java program. You don't need to bother about low level database query. One point to remember here that Dynamic Query is only to fetch data. Below are the API we will be working with to write dynamic query. com.liferay.portal.kernel.dao.orm.DynamicQueryFactoryUtil com.liferay.portal.kernel.dao.orm.DynamicQuery com.liferay.portal.kernel.dao.orm.Criterion com.liferay.portal.kernel.dao.orm.RestrictionsFactoryUtil com.liferay.portal.kernel.dao.orm.PropertyFactoryUtil com.liferay.portal.kernel.dao.orm.Projection com.liferay.portal.kernel.dao.orm.ProjectionFactoryUtil com.liferay.portal.kernel.dao.orm.ProjectionList com.liferay.portal.kernel.dao.orm.OrderFactoryUtil