Custom SQL in Liferay 7

Custom SQL in Liferay 7

Liferay service builder generates basic basic CRUD method for single entity.But their are certain scenarios such as fetching data from multiple table, in such cases we have to use custom sql.

If you are not familiar with creating servicebuilder module please refer my blog on Creating ServiceBuilder in liferay 

I am using following service.xml to explain concept of custom sql in liferay 7.

service.xml

<?xml version="1.0"?>

<!DOCTYPE service-builder PUBLIC "-//Liferay//DTD Service Builder 7.0.0//EN" "http://www.liferay.com/dtd/liferay-service-builder_7_0_0.dtd">

<service-builder package-path="com.liferay.product.service">

<namespace>pr</namespace>

<entity local-service="true" name="Product" table="product_details" remote-service="false" uuid="true">

<column name="productId" primary="true" type="long" id-type="increment"/>

<column name="companyId" type="long" />

<column name="productName" type="String" />

<column name="productPrice" type="long" />

</entity>

</service-builder>

Now follow following steps to create custom sql in liferay7

1)Create a custom-sql folder and default.xml file.

• Create custom-sql  folder inside /src/main/resources/META-INF/ of your /product-registration-service-service(here i referproduct-registration-service module that is created in previous post) 

• Create default.xml in custom-sql folder
• Liferay’s CustomSQLUtil class looks up custom SQL from a file called default.xml

Content of default.xml

<?xml version="1.0" encoding="UTF-8"?>
<custom-sql>
   <sql id="getAllProduct"> 
      <![CDATA[ 
           SELECT * from product_details 
           where productPrice=?
]]>
   </sql>
</custom-sql>

• Here sql id uniquely identify your query and it is used to fetch query later in method.

Step 2 Create xxxFinderImpl class in service module's persistence layer that extends BasePersistenceImpl class and implements xxxFinder interface.

For our case we will create ProductFinderImpl class in com.liferay.product.service.service.persistence.impl package


package com.liferay.product.service.service.persistence.impl;

import com.liferay.portal.kernel.service.persistence.impl.BasePersistenceImpl;
import com.liferay.product.service.model.Product;

import com.liferay.product.service.service.persistence.ProductFinder;

public class ProductFinderImpl extends BasePersistenceImpl<Product> implements ProductFinder {


}

This is used to implement finder method to invoke custom-sql

Step 3 Run service builder this will generate xxxFinder interface and xxxUtil for the finder in api module.

For our case it will generate ProductFinder interface and ProductUtilclass in api module.

Step 4 Add following gradle dependecy for CustomSQLUtil inbuild.gradle file of your service module shown below.

compile group: 'com.liferay', name: 'com.liferay.portal.dao.orm.custom.sql', version: '1.0.3'

Step 6 Write click on modules goto Gradle→ click on Refresh Gradle Project.This will resolve CustomSQLUtil that we add is build.gradle file.

Step 7 Write a new method in  ProductFinderImpl class to execute query

package com.liferay.product.service.service.persistence.impl;

import java.util.List;

import com.liferay.portal.dao.orm.custom.sql.CustomSQLUtil;
import com.liferay.portal.kernel.dao.orm.SQLQuery;
import com.liferay.portal.kernel.dao.orm.Session;
import com.liferay.product.service.model.Product;
import com.liferay.product.service.model.impl.ProductImpl;
import com.liferay.product.service.service.persistence.ProductFinder;

import com.liferay.portal.kernel.dao.orm.QueryPos;

public class ProductFinderImpl extends ProductFinderBaseImpl implements ProductFinder{

public List<Product>  getProductByPrice(long price){
Session session=null;
try{
//open ORM Session
                        session=openSession();
                      //get sql query return in default.xml
                        String sql=CustomSQLUtil.get(getClass(),"getAllProduct");
SQLQuery sqlQuery=session.createSQLQuery(sql);
sqlQuery.setCacheable(false);
                       //Add entity to be searched
                        sqlQuery.addEntity("Product",ProductImpl.class);
                       //Replace positional parameters in the query
                        QueryPos queryPos=QueryPos.getInstance(sqlQuery);
queryPos.add(price);
                       //Execute query and return result
                         return (List<Product>)sqlQuery.list();
}catch(Exception e){

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

Step 8 Again goto  gradle task and execute build service task.Then right click on modules goto Gradle→ click on Refresh Gradle Project.

Step 9 Write new method in ProductLocalServiceImpl generated under in package com.liferay.product.service.service.impl and call above method.

package com.liferay.product.service.service.impl;

import aQute.bnd.annotation.ProviderType;

import java.util.List;

import com.liferay.product.service.model.Product;

import com.liferay.product.service.service.base.ProductLocalServiceBaseImpl;

@ProviderType
 public class ProductLocalServiceImpl extends ProductLocalServiceBaseImpl {

public List<Product> getProductByPrice(long price) {
return productFinder.getProductByPrice(price);
}

}

Step 10 Again goto gradle task and execute build service task.Then again right click on modules goto Gradle→ click on Refresh Gradle Project.

Now we are ready to call getProductByPrice like

List<Product> products=ProductLocalServiceUtil.getProductByPrice(500);

Summery

In this post we discuss basic concept of writing custom sql in liferay 7