How to use IN parameter in Custom SQL in Liferay DXP

After doing multiple rounds of search over the internet about using IN parameter in Custom SQL in Liferay but without any satisfactory results, I assumed probably this could never be achieved in Liferay. However a few days back when I was looking at the code I thought about a tweak that could be applied if we have a use case that pertains to the mentioned scenario. I applied the same and lo! It worked. Hence sharing with my fellow developers a brief description of the steps that I followed to implement IN operator/clause in my custom SQL query in Liferay.

Step 1: 

Create Service Builder

Here I have created book management as a service module with bookIdbookname and author as fields. Other fields are left as default.

Service.xml

 


Build Service. After building the service entities are generated.

 

Step 2

Create default.xml

While working with Custom SQL, default.xml is an important file as it contains the SQL query that is to be executed to fetch the result set from server. It is created under the path META-INF/custom-sql folder in service module. In my case I have named the service builder project as book-management. Therefore the path for default.xml will be book-management-service/src/main/resources/META-INF/custom-sql/default.xml as illustrated below :

 

Paste the contents inside default.xml as below

Run build service and click gradle refresh for entities to be generated.

Step 3

Create EntityFinderImpl

Next step is to create an EntityFinderImpl class. In my case I have named entity as book, hence the entity finder class would be BookFinderImpl.java class. This class should be created in com.sample.book.service.persistence.impl package. 

Here the BookFinderImpl class should extend BookPeristenceImpl class as illustrated below

Next run service builder and click gradle refresh. Post building the service we can observe that there are additional entities that are created inside com.sample.book.service.persistence.impl class.

 

Now change the BookFinderImpl to extend BookFinderBaseImpl and implement BookFinder interface. Next add the component reference for service class @Component(service = BookFinder.class) so that the BookFinderImpl becomes available as a service.

Build service.

Step 4 

Create method in EntityFinderImpl

Now create a method inside BookFinderImpl class as illustrated below

 

Here as we can see I have firstly injected the reference of Custom SQL entity. For the Custom SQL reference to be available in service module add the following in the build.gradle file

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

Next I have created a method that returns List of objects as return type of the method. Here I have created session object to handle session parameters for the query. Next I have created a String object that will store the query by fetching the SQL id from default.xml.

Further I have created a StringBuffer object that will append the IN parameter to the SQL query. The values for parameter is set as DTO bean entities and accessed using getter methods. If there are any positional parameters in our SQL query they can be accessed by creating QueryPos objects and passing appropriate values to the parameters.

Run build service and click on gradle refresh

Step 5

Create a method in EntityLocalServiceImpl class 

Next create a method in BookLocalServiceImpl class that will access the method created in FinderImpl as illustrated below. Pass the required parameters to the method so that it can be accessed when calling the service module.

This is the final step in our Custom SQL configuration. Now the method created in LocalserviceImpl can be accessed from any controller class that consumes the service APIs to generate result set when used with IN parameter in SQL query.