Liferay is easy: Custom Sql in Liferay

Today we will discuss Custom Sql/Sql query in Liferay

Liferay Service Builder create basic CRUD method but there are some scenarios when we have to write SQL query. For writing native SQL query we use the concept of Custom Query in Liferay. Before Reading this blog it is highly recommended to read my previous blog on Service Builder in Detail.

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</author>
	<namespace>pm</namespace>
	<entity name="Student" local-service="true" remote-service="false">
	<column name="sid" type="long" primary="true"></column>
	<column name="name" type="String"></column>
	<column name="rollno" type="int"></column>
	<column name="mobileno" type="long"></column>
	<order by="asc">
	<order-column name="mobileno"></order-column>
	</order>
	<finder name="Mobile" return-type="Collection">
	<finder-column name="mobileno"></finder-column>
	</finder>
	</entity>
	</service-builder>

view raw service.xml hosted with ❤ by GitHub

Step 2:-Create xml files for sql Query
Now create a folder custom-sql inside src and create default.xml in it.

default.xml(/WEB-INF/src/custom-sql/default.xml)

	<?xml version="1.0" encoding="UTF-8"?>
	<custom-sql >
	<sql file="custom-sql/student-custom-sql.xml"/>
	</custom-sql >

view raw default.xml hosted with ❤ by GitHub

here we can write our sql query but for good maintanability we create seperate file student-custom-sql.xml and include in default.xml.

student-custom-sql.xml

	<?xml version="1.0" encoding="UTF-8"?>
	<custom-sql>
	<sql id="studentBetweenQuery" >
	<![CDATA[
	SELECT * from pm_student st WHERE st.sid BETWEEN ? AND ?
	]]>
	</sql>
	</custom-sql>

view raw student-custom-sql.xml hosted with ❤ by GitHub

Here we use sql id this is unique for each query.This id is used to fetch the query in method.
No need to write semicolon(;).
You can create one xml file for one entity and then include in default.xml.

Step 3:-Create xxxFinderImpl Class
Now create xxxFinderImpl class inside persistence in our case create StudentFinderImpl that extends BasePersistenceImpl and implements StudentFinder.

StudentFinderImpl.java

	package com.aditya.service.persistence;

	import com.aditya.model.Student;
	import com.liferay.portal.service.persistence.impl.BasePersistenceImpl;

	public class StudentFinderImpl extends BasePersistenceImpl<Student> implements StudentFinder{

	}

view raw StudentFinderImpl.java hosted with ❤ by GitHub

Initally it shows error because there is no StudentFinder interface.

Run Service builder

After this error is gone and StudentFinder interface is created which is blank.

StudentFinder.java


	package com.aditya.service.persistence;

	public interface StudentFinder {
	}

view raw StudentFinder.java hosted with ❤ by GitHub

One more class StudentFinderUtil is also created .

StudentFinderUtil.java


	package com.aditya.service.persistence;

	import com.liferay.portal.kernel.bean.PortletBeanLocatorUtil;
	import com.liferay.portal.kernel.util.ReferenceRegistry;

	/**
	* @author Aditya
	*/
	public class StudentFinderUtil {
	public static StudentFinder getFinder() {
	if (_finder == null) {
	_finder = (StudentFinder)PortletBeanLocatorUtil.locate(com.aditya.service.ClpSerializer.getServletContextName(),
	StudentFinder.class.getName());

	ReferenceRegistry.registerReference(StudentFinderUtil.class,
	"_finder");
	}

	return _finder;
	}

	public void setFinder(StudentFinder finder) {
	_finder = finder;

	ReferenceRegistry.registerReference(StudentFinderUtil.class, "_finder");
	}

	private static StudentFinder _finder;
	}

view raw StudentFinderUtil.java hosted with ❤ by GitHub

Step 4:-Create method in xxxFinderImpl Class
Create method in this class and provide implementation.This is the main task where we fire query and get the result.

StudentFinderImpl.java

	package com.aditya.service.persistence;

	import java.util.List;

	import com.aditya.model.Student;
	import com.aditya.model.impl.StudentImpl;
	import com.liferay.portal.kernel.dao.orm.QueryPos;
	import com.liferay.portal.kernel.dao.orm.QueryUtil;
	import com.liferay.portal.kernel.dao.orm.SQLQuery;
	import com.liferay.portal.kernel.dao.orm.Session;
	import com.liferay.portal.service.persistence.impl.BasePersistenceImpl;
	import com.liferay.util.dao.orm.CustomSQLUtil;

	public class StudentFinderImpl extends BasePersistenceImpl<Student> implements StudentFinder {
	public List<Student> getStudentBetweenStudentId(int start, int end) {
	Session session = null;
	try {
	session = openSession();
	String sql = CustomSQLUtil.get("studentBetweenQuery");
	SQLQuery queryObject = session.createSQLQuery(sql);
	queryObject.setCacheable(false);
	queryObject.addEntity("Student", StudentImpl.class);
	QueryPos qPos = QueryPos.getInstance(queryObject);
	qPos.add(start);
	qPos.add(end);
	return (List<Student>) queryObject.list();

	//return (List<Student>) QueryUtil.list(queryObject,getDialect(),start, end);// for pagination feature
	} catch (Exception e) {
	e.printStackTrace();
	} finally {
	closeSession(session);
	}
	return null;
	}

	}

view raw StudentFinderImpl.java hosted with ❤ by GitHub

Explanation:-

1)CustomSQLUtil.get("studentBetweenQuery");
Here we fetch the sql query from xml file with the help of sql id. For different queries different id and method in this class.

2)queryObject.addEntity("Student", StudentImpl.class);
Here we add our entity to queryObject. If sql query belong to multiple tables than we have to add all the entities to query object.

3)return (List<Student>) queryObject.list();
Here we return List<Student> because our query return all records belong to Student table but if our query contain two or more table like in case of join then we return List<Object[]>.

Step 5:-Expose method to LocalServiceUtil Class
We can't directly call method from StudentFinderImpl or StudentFinderUtil . All method are call from xxxLocalServiceUtil
Class so first we create method in xxxLocalServiceImpl

StudentLocalServiceImpl.java

	public class StudentLocalServiceImpl extends StudentLocalServiceBaseImpl {

	public List<Student> getStudentBetweenStudentId(int start, int end) {
	return StudentFinderUtil.getStudentBetweenStudentId(start, end);
	}
	}

view raw StudentLocalServiceImpl.java hosted with ❤ by GitHub

Here this show error because StudentFinderUtil has not contain getStudentBetweenStudentId(start, end).

Run Service builder

Now error is gone coz this will create method in StudentFinder interface and StudentFinderUtil Class also.Now StudentFinder and StudentFinderUtil become:-

StudentFinder.java

	public interface StudentFinder {
	public java.util.List<com.aditya.model.Student> getStudentBetweenStudentId(
	int start, int end);
	}

view raw StudentFinder.java hosted with ❤ by GitHub

StudentFinderUtil.java

	public class StudentFinderUtil {
	public static java.util.List<com.aditya.model.Student> getStudentBetweenStudentId(
	int start, int end) {
	return getFinder().getStudentBetweenStudentId(start, end);
	}

	public static StudentFinder getFinder() {
	if (_finder == null) {
	_finder = (StudentFinder)PortletBeanLocatorUtil.locate(com.aditya.service.ClpSerializer.getServletContextName(),
	StudentFinder.class.getName());

	ReferenceRegistry.registerReference(StudentFinderUtil.class,
	"_finder");
	}

	return _finder;
	}

	public void setFinder(StudentFinder finder) {
	_finder = finder;

	ReferenceRegistry.registerReference(StudentFinderUtil.class, "_finder");
	}

	private static StudentFinder _finder;
	}

view raw StudentFinderUtil.java hosted with ❤ by GitHub

Now Both Contain getStudentBetweenStudentId(int start, int end).

Note:-
Normally we first create method in interface then provide implementation in Impl class but in Liferay we provide implementation in xxxLocalServiceImpl first and Run service builder which create method in Inteface.

Step 6:-Call method from LocalServiceUtil Class
Now from your doView() or from jsp call your method as:-

	public void doView(RenderRequest renderRequest, RenderResponse renderResponse) throws IOException, PortletException {
	try {
	System.out.println("Student==>"+StudentLocalServiceUtil.getStudentBetweenStudentId(101, 104).size());
	}
	catch (SystemException e) {
	e.printStackTrace();
	}
	super.doView(renderRequest, renderResponse);
	}