SQL & Liferay Dynamic Queries Compared...

SQL 1:

select * from mycustomtable where status='Pending' and userId=10122 order by modifiedDate desc, requestId asc;

DQ 1:

 DynamicQuery dynamicQuery = DynamicQueryFactoryUtil.forClass(MyCustomTable.class);  
 dynamicQuery.add(PropertyFactoryUtil.forName("status").eq("Pending");  
 dynamicQuery.add(PropertyFactoryUtil.forName("userId").eq(10122);  
 Order defaultOrder = OrderFactoryUtil.desc("modifiedDate");  
 Order secondOrder = OrderFactoryUtil.asc("requestId");  
 dynamicQuery.addOrder(defaultOrder);  
 dynamicQuery.addOrder(secondOrder);  

SQL 2:

select * from mycustomtable where (subject like '%Test Subject%') and ((create_date between '10/08/2012' and '10/09/2012') or (status='Pending'));

DQ 2:

 DynamicQuery dynamicQuery = DynamicQueryFactoryUtil.forClass(MyCustomTable.class);  
 Criterion criterion = null;  
 criterion = RestrictionsFactoryUtil.like("subject", StringPool.PERCENT + "Test Subject"+ StringPool.PERCENT);  
 criterion = RestrictionsFactoryUtil.and(criterion, RestrictionsFactoryUtil.between("create_date",10/08/2012,10/09/2012));  
 criterion = RestrictionsFactoryUtil.or(criterion, RestrictionsFactoryUtil.eq("status", "Pending"));  
 dynamicQuery.add(criterion);  

SQL 3:

select max(col1) from mycustomtable where status='Pending';

DQ 3:

DynamicQuery dynamicQuery = DynamicQueryFactoryUtil.forClass(MyCustomTable.class);  
 dynamicQuery.add(PropertyFactoryUtil.forName("status").eq("Pending");  
 dynamicQuery.setProjection(ProjectionFactoryUtil.projectionList().add(Projections.max("col1"));

SQL 4:

 select count(entryId) from mycustomtable group by userId;

DQ 4:

 DynamicQuery query = DynamicQueryFactoryUtil.forClass(MyCustomTable.class)  
   .setProjection(ProjectionFactoryUtil.projectionList()  
   .add(ProjectionFactoryUtil.groupProperty("userId"))  
   .add(ProjectionFactoryUtil.count("entryId")));  

SQL 5:

 select count(*) from mycustomtable where status='Pending';

DQ 5:

 DynamicQuery query = DynamicQueryFactoryUtil.forClass(MyCustomTable.class)  
   .add(Property.forName("status").eq('Pending'))  
   .setProjection(Projections.rowCount());  

SQL 6:

 select msct.somecol from myfirstcustomtable mfct, mysecondcustomtable msct  
   where mfct.matchcol1=msct.matchcol1 and mfct.status='Pending'  
   order by othercol desc;  

DQ 6:

 DynamicQuery dq1 = DynamicQueryFactoryUtil.forClass(  
   MyFirstCustomTable.class, "mfct")  
   .setProjection(ProjectionFactoryUtil.property("somecol"))  
   .add(PropertyFactoryUtil.forName("mfct.matchcol1").eqProperty("msct.matchcol1"))  
   .add(PropertyFactoryUtil.forName("mfct.status").eq("Pending"));  
 DynamicQuery dq2 = DynamicQueryFactoryUtil.forClass(  
   MySecondCustomTable.class, "msct")  
   .add(PropertyFactoryUtil.forName("msct.msctPK").in(dq1))  
   .addOrder(OrderFactoryUtil.desc("msct.othercol"));  

Steps for building a custom SQL finder:
1. Create a new finder called CompetenceLevelFinderImpl in the /generated/service/persistence directory.
2. Let this class extend BasePersistence.
3. Now do a 'build-service' on the project.
4. The ServiceBuilder autogenerates the following two extra files : CompetenceLevelFinder.java and CompetenceLevelFinderUtil.java
5. Now open the CompetenceLevelFinderImpl.java file and let this class extend the CompetenceLevelPersistenceImpl class and implement CompetenceLevelFinder. (Assumed that the CompetenceLevel entity is defined in the service.xml and that the needed classes are also autogenerated by ServiceBuilder.)
6. Now add the needed functionality to the CompetenceLevelFinderImpl class and do a build-service.
7. You can now use the added functionality.

Useful Links:
http://www.liferay.com/community/wiki/-/wiki/Main/Service+Builder+Finders