Liferay is easy: Custom Sql in Liferay DXP 7.2

Today we will discuss how to write Custom Sql in Liferay DXP 7.2. We already know how to write Service Builder in Liferay DXP 7.2. So lets start this step by step :-

Step 1:- Create Service Builder
Here i create a small entity Pets that contain only two column:-

service.xml

	<?xml version="1.0"?>
	<!DOCTYPE service-builder PUBLIC "-//Liferay//DTD Service Builder 7.2.0//EN" "http://www.liferay.com/dtd/liferay-service-builder_7_2_0.dtd">

	<service-builder dependency-injector="ds" package-path="com.lie" auto-namespace-tables="false">
	<namespace>FOO</namespace>
	<entity name="Pet" local-service="true" remote-service="false">
	<column name="pid" type="int" primary="true"></column>
	<column name="pname" type="String"></column>
	</entity>
	</service-builder>

view raw service.xml hosted with ❤ by GitHub

Step 2:- Create default.xml

When you build service two projects are created :-
a)project-api
b)project-service

My project name is lie.

Now inside your service META-INF folder create a folder custom-sql and inside this folder create default.xml.

Path of default.xml:-
/lie-service/src/main/resources/META-INF/custom-sql/default.xml

Now paste this content in default.xml

default.xml

	<?xml version="1.0" encoding="UTF-8"?>

	<custom-sql>
	<sql id="getPetsByName">
	<![CDATA[
	SELECT * FROM Pet p where p.pname = ?
	]]>
	</sql>
	</custom-sql>

view raw default.xml hosted with ❤ by GitHub

Explanation:-
Here we use sql id ="getPetsByName" this id should be unique for each sql. So that we can get the query by this id.

Step 3:- Create EntityFinderImpl

Now create EntityFinderImpl my entity is Pet so create PetFinderImpl inside com.lie.service.persistence.impl .
Here PetFinderImpl extends BasePersistanceImpl and implement EntityFinder.

PetFinderImpl

	package com.lie.service.persistence.impl;

	import com.lie.model.Pet;
	import com.liferay.portal.kernel.service.persistence.impl.BasePersistenceImpl;

	public class PetFinderImpl extends BasePersistenceImpl<Pet> implements PetFinder{

	}

view raw PetFinderImpl.java hosted with ❤ by GitHub

Initially this will give error because PetFinder is not available.

Now Build Service

When you build service and refresh BasePersistanceImpl become PetFinderBaseImpl . You can now import PetFinder also . So PetFinderImpl become:-

PetFinderImpl

	package com.lie.service.persistence.impl;

	import com.lie.service.persistence.PetFinder;

	public class PetFinderImpl extends PetFinderBaseImpl implements PetFinder{

	}

view raw PetFinderImpl.java hosted with ❤ by GitHub

Step 4:- Create method

Now inside PetFinderImpl paste this:-

PetFinderImpl

	package com.lie.service.persistence.impl;

	import com.lie.model.Pet;
	import com.lie.model.impl.PetImpl;
	import com.lie.service.persistence.PetFinder;
	import com.liferay.portal.dao.orm.custom.sql.CustomSQL;
	import com.liferay.portal.kernel.dao.orm.QueryPos;
	import com.liferay.portal.kernel.dao.orm.SQLQuery;
	import com.liferay.portal.kernel.dao.orm.Session;
	import java.util.List;
	import org.osgi.service.component.annotations.Component;
	import org.osgi.service.component.annotations.Reference;

	@Component(service = PetFinder.class)
	public class PetFinderImpl extends PetFinderBaseImpl implements PetFinder{
	@Reference
	private CustomSQL _customSQL;

	public List<Pet> getPetsByName(String pName){
	System.out.println("Inside custom sql");
	Session session=null;
	try{
	session=openSession();
	String sql=_customSQL.get(getClass(),"getPetsByName");
	System.out.println("Query==>"+sql);
	SQLQuery sqlQuery=session.createSQLQuery(sql);
	sqlQuery.setCacheable(false);
	sqlQuery.addEntity("Pet",PetImpl.class);
	QueryPos queryPos=QueryPos.getInstance(sqlQuery);
	queryPos.add(pName);
	return (List<Pet>)sqlQuery.list();
	}catch(Exception e){

	}finally {
	closeSession(session);
	}
	return null;
	}
	}

view raw PetFinderImpl.java hosted with ❤ by GitHub

Initially this will give error because we use CustomSQL object so just add the dependency in build.gradle

compileOnly group: "com.liferay", name: "com.liferay.portal.dao.orm.custom.sql.api", version: "1.0.0"

and refresh gradle project.

Note:- Here we use @Component and @Reference annotations.

Now Build Service

and refresh Gradle project.

Step 5:- Create method in LocalServiceImpl

Now create a method in PetLocalServiceImpl so that it will available in PetLocalServiceUtil.

PetLocalServiceImpl

	package com.lie.service.impl;

	import com.lie.service.base.PetLocalServiceBaseImpl;
	import com.liferay.portal.aop.AopService;
	import org.osgi.service.component.annotations.Component;

	@Component(
	property = "model.class.name=com.lie.model.Pet", service = AopService.class
	)
	public class PetLocalServiceImpl extends PetLocalServiceBaseImpl {
	public List<Pet> getPetsByName(String pName){
	return petFinder.getPetsByName(pName);
	}
	}

view raw PetLocalServiceImpl hosted with ❤ by GitHub

Now Build Service

Now you can deploy your services. And use the method as :-

@Reference
private PetLocalService _petLocalService;
List<Pet>pet = _petLocalService.getPetsByName("Dog");

Final Project structure:-