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

	/**
	* select * from states st where st.countryId =
	(select ct.countryId from countries ct where ct.name="India")
	*/
	DynamicQuery countryQuery = DynamicQueryFactoryUtil.forClass(Country.class, "ct", PortletClassLoaderUtil.getClassLoader());
	countryQuery.add(RestrictionsFactoryUtil.eq("ct.name", "India"));
	countryQuery.setProjection(PropertyFactoryUtil.forName("ct.countryId"));
	DynamicQuery stateQuery = DynamicQueryFactoryUtil.forClass(State.class, "st",PortletClassLoaderUtil.getClassLoader());
	stateQuery.add(PropertyFactoryUtil.forName("st.countryId").eq(countryQuery));
	try {
	List<State>stateList = StateLocalServiceUtil.dynamicQuery(stateQuery);
	for (State state : stateList) {
	System.out.println("States===>"+state.getStateId()+" ,"+state.getName()+" ,"+state.getCountryId());
	}
	} catch (SystemException e2) {
	e2.printStackTrace();
	}

view raw Join1.java hosted with ❤ by GitHub

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

	/**
	* SELECT * from states st where st.countryId IN
	(SELECT ct.countryId from countries ct where ct.name IN("India","USA"))
	*/
	DynamicQuery countryQuery = DynamicQueryFactoryUtil.forClass(Country.class, "ct", PortletClassLoaderUtil.getClassLoader());
	List<String> countryName = new ArrayList<String>();
	countryName.add("India");
	countryName.add("USA");
	countryQuery.add(RestrictionsFactoryUtil.in("ct.name", countryName));
	countryQuery.setProjection(PropertyFactoryUtil.forName("ct.countryId"));
	DynamicQuery stateQuery = DynamicQueryFactoryUtil.forClass(State.class, "st",PortletClassLoaderUtil.getClassLoader());
	stateQuery.add(PropertyFactoryUtil.forName("st.countryId").in(countryQuery));
	try {
	List<State>stateList = StateLocalServiceUtil.dynamicQuery(stateQuery);
	for (State state : stateList) {
	System.out.println("===>"+state.getStateId()+" ,"+state.getName()+" ,"+state.getCountryId());
	}
	} catch (SystemException e2) {
	e2.printStackTrace();
	}

view raw Join2.java hosted with ❤ by GitHub

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

	/**
	* SELECT * from states st where st.countryId IN
	(select ct.countryId from countries ct where ct.name ="India" OR ct.name="USA");
	*/
	DynamicQuery countryQuery = DynamicQueryFactoryUtil.forClass(Country.class, "ct", PortletClassLoaderUtil.getClassLoader());
	Disjunction disJunction = RestrictionsFactoryUtil.disjunction();
	disJunction.add(PropertyFactoryUtil.forName("ct.name").eq("India"));
	disJunction.add(PropertyFactoryUtil.forName("ct.name").eq("USA"));
	countryQuery.add(disJunction);
	countryQuery.setProjection(PropertyFactoryUtil.forName("ct.countryId"));
	DynamicQuery stateQuery = DynamicQueryFactoryUtil.forClass(State.class, "st",PortletClassLoaderUtil.getClassLoader());
	stateQuery.add(PropertyFactoryUtil.forName("st.countryId").in(countryQuery));
	try {
	List<State>stateList = StateLocalServiceUtil.dynamicQuery(stateQuery);
	for (State state : stateList) {
	System.out.println("States===>"+state.getStateId()+" ,"+state.getName()+" ,"+state.getCountryId());
	}
	} catch (SystemException e2) {
	e2.printStackTrace();
	}

view raw Join3.java hosted with ❤ by GitHub

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

Introduction to Dynamic Query

Projection in Dynamic Query

Order By Clause using Dynamic Query in Liferay

AND/OR Clause using Dynamic Query in Liferay

Creating Services using Maven

Liferay Message Customization

Jasper Report with Eclipse

Validation in Liferay