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.
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