Liferay Dynamic Queries

Liferay service builder is a powerful tool for creating Local and Remote service and
interact with database.The finder method is used to fetch data of  a table
using some column values.

There are several reasons/use cases for wanting to move beyond those existing 'finder' queries:

• the level of complexity allowed by the service generation is not sufficient for your purposes and/or
• you need queries which implement aggregate SQL operations such as max, min, avg, etc.
• you want to return composite objects or tuples rather than the mapped object types
• you want to access the data in way not originally conceived for whatever reason
• query optimization
• complex data access, like reporting. 

 sample service.xml and dynamic queries are below.                                                                                               

<entity name="Banners" local-service="true" remote-service="true" table="T_BANNERS">
        <!-- PK fields -->
        <column name="bannerId" type="long" db-name="BANNER_ID" primary="true"></column>

<column name="bannerName" type="String" db-name="BANNER_NAME"></column>
<column name="priorityId" type="long" db-name="PRIORITY_ID"></column>
 </entity>

Collection<Object> bannerIdArray= new ArrayList<Object>();
bannerIdArray.add(1L);
bannerIdArray.add(2L);
bannerIdArray.add(3L);

DynamicQuery dynamicQuery = DynamicQueryFactoryUtil .forClass(Banners.class)
                .add(PropertyFactoryUtil.forName("bannerId").in(bannerIdArray))
                .addOrder(OrderFactoryUtil.desc("priorityId"));

 List<Banners> banners = BannersLocalServiceUtil.dynamicQuery(dynamicQuery);

 Here i am fetching all the banner with id which contains in the bannerIdArray ( ie 1,2 and 3)

with descending order of their priority.