Sunday, May 27, 2012

Dynamic Query in Liferay

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. Lets go step by step with easy example. If you want to fire simple AND query then here is the example.

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.


Above query will order records based on Modified Date and RequestId. Now if you want to fire some complex query like combination of or , And , Between and Like then here is the example. By using RestrictionFactoryUtil we can file OR,AND,Like and Between query.


As you can see above query will try to fetch those records which has subject like "Test Subject" and its created_date is in between the above dates or the records which has status as pending. To execute dynamic query.


5 comments:

  1. how can we get the last 3 records from table in liferay by using dynamic query

    ReplyDelete
    Replies
    1. Mahesh,

      Considering any one table in liferay DB. Take it as User_ table in liferay DB.

      If you want to retrieve last three record from user_ table then your mysql query will be as below :-

      SELECT * FROM user_ ORDER BY userId DESC LIMIT 3;

      And if you want to use the same in Dynamic Query of Liferay then it will be as below:-

      DynamicQuery dynamicQuery = DynamicQueryFactoryUtil.forClass(User.class);
      Order defaultOrder = OrderFactoryUtil.desc("userId");
      dynamicQuery.addOrder(defaultOrder);
      dynamicQuery.setLimit(0, 3);
      List users = UserLocalServiceUtil.dynamicQuery(dynamicQuery);

      Hope it will help you.

      Thanks & Regards,
      Amit D.

      Delete
  2. hii,
    I have to sort data by create date, but from two different tables.
    Can I do it by Dynamic query?
    Kindly reply

    ReplyDelete
  3. Thanx a lot.. :(
    Can u explain difference between RestrictionsFactoryUtil and PropertyFactoryUtil

    ReplyDelete
  4. How to use group by clause in liferay dynamic query.
    Ex query:- SELECT * FROM user_ group by userName.

    ReplyDelete