Search This Blog

Tuesday 10 December 2013

Custom queries in Liferay

Sometimes it is needed to perform joined queries with the Service Builder. It is not possible to do it with dynamic queries - custom queries are necessary.
This article explains how to create custom queries , i faced lot of issues when working on the same for the first time, unware for various minor concepts which were hard to be found at one-place . Thus ,decided to document my understanding of the same along withe the issues faced during implementation alongwith their fixes. Plz don't ignore the various -'Note' throughout the blog.


STEP:1 First of all create a folder with the name - "custom-sql"  under the src folder...
Note: create a folder & NOT a package .(see image below.)



STEP:2 Now create a file- "default.xml" in this custom-sql folder...(Though you can write your sql-querries over here as well , but its always a good practice to write your qurries in
a diff .xml file & map that file int this default.xml)
Add something like this to your default.xml:
---------------------------------------------------------------------------------
 <?xml version="1.0"?>
<custom-sql>
    <sql file="/custom-sql/myProject-portal.xml" />
</custom-sql>
---------------------------------------------------------------------------------

STEP:3 Now open your 'myProject-portal.xml' , and put  all you querries here as below... each identified by a id.
Something like this :
---------------------------------------------------------------------------------
<?xml version="1.0" encoding="UTF-8"?>
<custom-sql>
 
    <sql id="">
        <![CDATA[
Select * from user_ where firstName ="Test"
        ]]>
    </sql>
</custom-sql>

---------------------------------------------------------------------------------
STEP:4 Add the following in ur portal-ext.properties

custom.sql.configs=\
custom-sql/default.xml, \
custom-sql/default-ext.xml

---------------------------------------------------------------------------------

Step:5 Create a FinderImpl class bearing your Entity name that must extends BasePersistenceImpl.
public class CABDisplayFinderImpl extends BasePersistenceImpl<CABDisplay>{ }

Step:6 Now run the 'build-service' task.You will observe that the service-builder has generated two more files for you in '../service/persistence' under the 'docroot/WEB-INF'-namely
-CABDisplayFinder AND
-CABDisplayFinderUtil
  Now go back to your FinderImpl class and add 'implements 'CABDisplayFinder' as below and run the Build-Service task again..
---------------------------------------------------------------------------------
  public class CABDisplayFinderImpl extends BasePersistenceImpl<CABDisplay> implements CABDisplayFinder{

}
---------------------------------------------------------------------------------
Note:
Do Keep Note of these two configuration related points -
[1] Next ,you need to create a finderImpl under the "service/persistence" under the "/docroot/WEB-INF/src" && Not in 'service/persistence' under "/docroot/WEB-INF"
[2] Also , the FinderImpl must start with the name of any defined Entity in your service.xml (say - for entity-'ABC' it would be 'ABCFinderImpl' & not 'MyABCFinderImpl',etc)
This is very imp to mention as i wasted a reasonably good amount of time breaking my head on this issue, being unaware of this concept that your FinderImpl class must bear & start with the name of a defined Entity.
I did the Mistake of creating a FinderImpl with the name 'ABCDisplayFinderImpl' for an entity -'ABCDisplay_Metadata' , whereas it should be 'ABCDisplay_MetadataFinderImpl' which solved the Issue.




No comments:

Post a Comment