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

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

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

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

Step 4:- Create method

Now inside PetFinderImpl paste this:-

PetFinderImpl

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

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