Liferay Custom Sql

 

Liferay custom SQL is a Service Builder-supported method for performing custom, complex queries against the database by invoking custom SQL from a finder method in your persistence layer. Service Builder helps you generate the interfaces to your finder method. 

The main purpose of Liferay Custom SQL is to execute our own hand written query to fetch data from one table or many tables.

 

Now follow some few steps to implement Liferay Custom Query…

 

Step 1:- Specify your custom SQL.

We already have our project which contains services and other things. Now inside your service META-INF folder create a folder custom-sql and inside this folder create default.xml.

 

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

 

Now paste this content in default.xml

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

Step 2:- Implement your finder method.

After specifying your custom SQL query, you need to implement the finder method to invoke it. This should be done in the service’s persistence layer. Service Builder generates the interface for the finder but you need to create the implementation.

The first step is to create EntityFinderImpl my entity is Register

 So create RegisterFinderImpl inside com.cuapos.service.persistence.impl.

In com.cuapos.service.persistence.impl right click create a class entity name is RegisterFinderImpl.

Here RegisterFinderImpl extends BasePersistanceImpl and implement EntityFinder.

 

Initially this will give error because RegisterFinder is not available.

Now Build Service

When you build service and refresh gradle BasePersistanceImpl become RegisterFinderBaseImpl. You can now import RegisterFinder also. So RegisterFinderImpl become:-

 

 

 

Step 3:- Create a new method in RegisterFinderImpl to execute the query

We need to deicide method return type and also need to decide how many parameters use in implementation method and this should be depends on SQL query positional parameters we used in native SQL query and also make sure parameter data types.

 

Steps in implementing custom SQL method

• Open Session Portlet Session
• Create query object by passing SQL query as a String
• Add entities Model Impl class for query object
• Create Query Position instance to pass positional parameter for the query.
• Use list () method over query object.

 

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

Now Build Service

And refresh Gradle Project.

Step 4:- Create method in LocalServiceImpl

Now create a method in RegisterLocalServiceImpl and call the above method.

 

 

Now Build Service

Now you can deploy your services. And use the method.

Step 5:- Now we are ready to call getRegisterByLastName method like 

List register = RegisterLocalServiceUtil.getRegisterByLastName(“mar”);

Check the method using it in your any JSP of the portlet. 

 

Step 6:- Output

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

---

---