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 bookId ,
bookname _and _ a uthor 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.

link