Custom Sql With Two Table

Custom sql/sql query with two table 

As discussed in my previous article Custom Sql in Liferay , custom sql is used to write native sql query in liferay. Today we will discuss how to write custom query that belong to two or more table like join query. Before Reading this blog it is highly recommended to read my previous blog on  Service Builder in Detail and custom sql in liferay .

Lets Start this step by step:-

Step 1:-Create service.xml
You can create service as mention in my previous article Service Builder in Detail .

service.xml

	<?xml version="1.0" encoding="UTF-8"?>
	<!DOCTYPE service-builder PUBLIC "-//Liferay//DTD Service Builder 6.2.0//EN" "http://www.liferay.com/dtd/liferay-service-builder_6_2_0.dtd">
	<service-builder package-path="com.aditya">
	<author>aditya.bhardwaj</author>
	<namespace>ad</namespace>
	<entity name="Student" local-service="true" remote-service="false" table="student">
	<column name="studentId" type="int" primary="true"></column>
	<column name="name" type="String"></column>
	<column name="lastname" type="String"></column>
	<column name="std" type="String"></column>
	<column name="phone" type="String"></column>
	<column name="gender" type="int"></column>
	</entity>
	<entity name="StudentAddress" local-service="true" remote-service="false" table="student_address">
	<column name="addressId" type="int" primary="true"></column>
	<column name="city" type="String"></column>
	<column name="country" type="String"></column>
	<column name="studentId" type="int"></column>
	</entity>
	</service-builder>

view raw service.xml hosted with ❤ by GitHub

And Build Service





Step 2:- Fill data in tables
After inserting data tables in data base look like this:-

For our example we like to fetch all the student details that belong to India ie our query is:-

Step 3:- Create our method
Open either any one from:-
a)StudentLocalServiceImpl  b)StudentAddressLocalServiceImpl 
Because we have to fetch the data from both the tables so pick any one .

StudentLocalServiceImpl 

	package com.aditya.service.impl;
	import java.util.List;
	import com.aditya.service.base.StudentLocalServiceBaseImpl;
	import com.liferay.portal.kernel.dao.orm.QueryPos;
	import com.liferay.portal.kernel.dao.orm.SQLQuery;
	import com.liferay.portal.kernel.dao.orm.Session;
	public class StudentLocalServiceImpl extends StudentLocalServiceBaseImpl {
	public List<Object[]> getAllDetailByCountry(String countryName)
	{
	Session session = studentPersistence.openSession();
	SQLQuery query = session.createSQLQuery("Select s.studentId,s.name,s.lastname,s.std,s.phone,s.gender,addr.addressId,addr.city,addr.country "
	+ "FROM student s join student_address addr ON s.studentId = addr.studentId WHERE addr.country = ?");
	QueryPos pos = QueryPos.getInstance(query);
	pos.add(countryName);
	return query.list();
	}
	}

view raw StudentLocalServiceImpl.java hosted with ❤ by GitHub

Explanation:- 

1)List<Object[]> getAllDetailByCountry(String name)
Here we create a method that take String argument and whose return type is List<Object[]> because output result is neither of Employee type and not of Address type it is a combination of both.

2)Session session = studentPersistence.openSession()
Here we create a Session object with studentPersistence

3)SQLQuery query = session.createSQLQuery(" ")
Here we create our query this is not the standard way normally we take query from xml file like i described in Custom sql in Liferay but this approach is sometimes very handy when we have to save time.

4)QueryPos pos = QueryPos.getInstance(query)
Here we pass the query and fill the value of ? with pos like
Ex- pos.add(countryName);
and than finally return the list.

Again Build Service

So that this can be available in Util class.

Step 4:- Call method from util Class
After building services this method is available in StudentLocalServiceUtil use it like:-

	public void doView(RenderRequest renderRequest, RenderResponse renderResponse) throws IOException, PortletException {
	try {
	List <Object[]> data = StudentLocalServiceUtil.getAllDetailByCountry("India");
	for (Object[] objects : data) {
	int studentId = (int)objects[0];
	String name = (String)objects[1];
	String lastName = (String)objects[2];
	String std = (String)objects[3];
	String phone = (String)objects[4];
	int gender= (int)objects[5];
	int addressId = (int)objects[6];
	String city = (String)objects[7];
	String country = (String)objects[8];
	System.out.println("sid=>"+studentId+" Name=>"+name+" Last name==>"+lastName+" Class=>"+std+" Phone=>"+phone+" Gender=>"+gender+" AddressId=>"+addressId+" City=>"+city+" Country=>"+country);
	}
	} catch (Exception e) {
	e.printStackTrace();
	}
	}

view raw Testing.java hosted with ❤ by GitHub

You can download source code from Custom Sql with two table

Hope this will help....

 Related Post:-

Liferay Service Builder in Detail

Custom Sql in Liferay

Many To Many Relationship mapping in Liferay Services

Validation in Liferay

Add Portlet to control Panel

Jasper Report in Detail

Search Container in Liferay

Add page, layout,Portlet Programatically

Service Hook in Liferay

Create Portlet with Maven