Saturday, February 27, 2016

Joins using Dynamic Query in Liferay


Today we will discuss about Joins in Dynamic Query . Before reading this blog it is highly recommended to read my previous blog on introduction of dynamic query , how to use AND/OR in dynamic query and Projection in dynamic query. In this blog i am using my own custom tables states and countries. The primary key of countries ie countryId is used as foreign key in states table.




In this blog we try to join the two table and fetch the result.So lets start with the example:-




Example 1:-


Explanation:-

1)In the first part of code(line 5 to 7) we use projection that give us the countryId where name is India ie 1. This means it will give the result of this query:-
select ct.countryId from countries ct where ct.name="India";

2)The second part of the code(line 10) take output of part 1 (countryId) as input and provide the result.This means it will give the result of this query:-
select * from states st where st.countryId =1;

Output:-
                         
                         States===>1  ,Delhi  ,1
                         States===>2  ,Gujrat,1



Example 2:-


Explanation:- 
Same as Example one output of half query goes as input to other.

Output:-
                         
                         States===>1  ,Delhi  ,1
                         States===>2  ,Gujrat,1
                         States===>5  ,California,2
                         States===>6  ,Florida,2


Example 3:-


Explanation:- 
Same as Example two just using OR clause in place of IN.

Output:-
                         
                         States===>1  ,Delhi  ,1
                         States===>2  ,Gujrat,1
                         States===>5  ,California,2
                         States===>6  ,Florida,2


Hope this will Help....

Related Post:-

No comments:

Post a Comment

Total Pageviews

Like Us on Facebook

Number Of Unique Visitor

Free counters!