Service Builder’s finder methods facilitate searching for entities by their
attributes–their column values. Add the column as a parameter for the finder in
your service.xml
file, run Service Builder, and it generates the finder method
in your persistence layer and adds methods to your service layer that invoke the
finder. But what if you’d like to do more complicated searches that incorporate
attributes from multiple entities?
For example, consider the Nose-ster Event Listing portlet you’ve been developing
in this chapter. Suppose you want to find an event based on its name,
description, and location name. If you recall, the event entity refers to its
location by the location’s ID, not its name. That is, the event entity table,
Event_Event
, refers to an event’s location by its long integer ID in the
table’s locationId
column. But you need to access the name of the event’s
location. Of course, with SQL you can join the event and location tables to
include the location name. But how would you incorporate custom SQL into your
portlet? And how would you invoke the SQL from your service? Service Builder
lets you do this by specifying the SQL as Liferay custom SQL and invoking it
in your service via a custom finder method.
Liferay custom SQL is a Service Builder-supported method for performing complex and custom queries against the database. Invoking custom SQL from a finder method in your persistence layer is straightforward. And Service Builder helps you generate the interfaces to your finder method. It’s easy to do by following these steps:
-
Specify your custom SQL.
-
Implement your finder method.
-
Access your finder method from your service.
Next, you’ll do exactly this to create and invoke custom SQL in your Event Listing portlet.
Step 1: Specify Your Custom SQL
After you’ve tested your SQL, you must specify it in a particular file for
Liferay to access it. Liferay’s CustomSQLUtil
class looks up custom SQL from a
file called default.xml
in your portlet project’s
docroot/WEB-INF/src/custom-sql/
folder. You must create the custom-sql
folder and create the default.xml
file in that custom-sql
folder.
The default.xml
file must adhere to the following format:
<custom-sql>
<sql id="[fully-qualified class name + method]">
SQL query wrapped in <![CDATA[...]]>
No terminating semi-colon
</sql>
</custom-sql>
You can add a custom-sql
element for every custom SQL query you’d like for
your portlet, as long as each query has a unique ID. The convention we recommend
using for the ID value is the fully-qualified class name of the finder followed
by a dot (.
) character and the name of the finder method. More detail on the
finder class and finder methods is in Step 2.
For this example, you’ll use the following ID value for the query:
com.nosester.portlet.eventlisting.service.persistence.\
EventFinder.findByEventNameEventDescriptionLocationName
Custom SQL must be wrapped in character data (CDATA
) for the sql
element. Importantly, the SQL must not be terminated with a semi-colon.
Following these rules, specify the custom SQL for this query by replacing the
contents of the default.xml
file with the following code:
<?xml version="1.0" encoding="UTF-8"?>
<custom-sql>
<sql id="com.nosester.portlet.eventlisting.service.persistence.EventFinder.\
findByEventNameEventDescriptionLocationName">
SELECT Event_Event.*
FROM Event_Event
INNER JOIN
Event_Location ON Event_Event.locationId = Event_Location.locationId
WHERE
(Event_Event.name LIKE ?) AND
(Event_Event.description LIKE ?) AND
(Event_Location.name LIKE ?)
</sql>
</custom-sql>
Make sure to delete the backslash (\\
) character from the end of the ID so that
the finder method name findByEventNameEventDescriptionLocationName
immediately
follows the package path specified below:
com.nosester.portlet.eventlisting.service.persistence.
Now that you’ve specified some custom SQL, the next step is to implement the
finder method. The method name for the finder should match the ID you just
specified for the sql
element.
Step 2: Implement Your Finder Method
It’s time to implement the finder method to invoke the custom SQL query. This should be done in the service’s persistence layer, since it’s SQL invoked on a relational database. You’ll rely on Service Builder to generate the interface for it. But before you do that, you need to create the implementation of the finder.
The first step is to create a -FinderImpl
class in the service persistence
package. Create a class called EventFinderImpl
in the
com.nosester.portlet.eventlisting.service.persistence.impl
package. Make
the class extend BasePersistenceImpl<Event>
. Your EventFinderImpl.java
file
should now have the following contents:
package com.nosester.portlet.eventlisting.service.persistence;
import com.liferay.portal.service.persistence.impl.BasePersistenceImpl;
import com.nosester.portlet.eventlisting.model.Event;
public class EventFinderImpl extends BasePersistenceImpl<Event> {
}
Run Service Builder to generate the -Finder
interface and the -Util
class
for the finder. Service Builder generates the EventFinder
interface and the
EventFinderUtil
utility class based on the EventFinderImpl
class. Modify
your EventFinderImpl
class to have it implement the EventFinder
interface
you just generated:
public class EventFinderImpl extends BasePersistenceImpl<Event>
implements EventFinder {
}
Now you can create our finder method in your EventFinderImpl
class. Add
the following finder method and static field to the EventFinderImpl
class:
public List<Event> findByEventNameEventDescriptionLocationName(
String eventName, String eventDescription, String locationName,
int begin, int end) {
Session session = null;
try {
session = openSession();
String sql = CustomSQLUtil.get(
FIND_BY_EVENTNAME_EVENTDESCRIPTON_LOCATIONNAME);
SQLQuery q = session.createSQLQuery(sql);
q.setCacheable(false);
q.addEntity("Event_Event", EventImpl.class);
QueryPos qPos = QueryPos.getInstance(q);
qPos.add(eventName);
qPos.add(eventDescription);
qPos.add(locationName);
return (List<Event>) QueryUtil.list(q, getDialect(), begin, end);
} catch (Exception e) {
try {
throw new SystemException(e);
} catch (SystemException se) {
se.printStackTrace();
}
} finally {
closeSession(session);
}
return null;
}
public static final String FIND_BY_EVENTNAME_EVENTDESCRIPTON_LOCATIONNAME =
EventFinder.class.getName() +
".findByEventNameEventDescriptionLocationName";
Remember to import the required classes. These include the following:
java.util.List;
com.liferay.portal.kernel.dao.orm.QueryPos;
com.liferay.portal.kernel.dao.orm.QueryUtil;
com.liferay.portal.kernel.dao.orm.SQLQuery;
com.liferay.portal.kernel.dao.orm.Session;
com.liferay.portal.kernel.exception.SystemException;
com.liferay.portal.service.persistence.impl.BasePersistenceImpl;
com.liferay.util.dao.orm.CustomSQLUtil;
com.nosester.portlet.eventlisting.model.Event;
com.nosester.portlet.eventlisting.model.impl.EventImpl;
The custom finder method opens a new Hibernate session and uses Liferay’s
CustomSQLUtil.get(String id)
method to get the custom SQL to use for the
database query. The FIND_BY_EVENTNAME_EVENTDESCRIPTON_LOCATIONNAME
static
field contains the custom SQL query’s ID. The
FIND_BY_EVENTNAME_EVENTDESCRIPTON_LOCATIONNAME
string is based on the
fully-qualified class name of the -Finder
interface (EventFinder
) and
the name of the finder method (findByEventNameEventDescriptionLocationName
).
Awesome! Custom SQL is in place and your finder method is implemented. Next, you’ll call the finder method from your service.
Step 3: Access Your Finder Method from Your Service
So far, you created a -FinderImpl
class and generated a -FinderUtil
utility class. However, your portlet class should not use the finder utility
class directly; only a local or remote service implementation (i.e.,
-LocalServiceImpl
or -ServiceImpl
) in your plugin project should invoke the
-FinderUtil
class. This encourages a proper separation of concerns: the
portlet classes invoke business logic of the services and the services in turn
access the data model using the persistence layer’s finder classes. So you’ll
add a method in the -LocalServiceImpl
class that invokes the finder method
implementation via the -FinderUtil
class. Then you’ll provide the portlet and
JSPs access to this service method by rebuilding the service.
Add the following method to the EventLocalServiceImpl
class:
public List<Event> findByEventNameEventDescriptionLocationName(String eventName,
String eventDescription, String locationName, int begin, int end)
throws SystemException {
return EventFinderUtil.findByEventNameEventDescriptionLocationName(
eventName, eventDescription, locationName, begin, end);
}
After you’ve added this method, run Service Builder to generate the interface
and make this finder method available in the EventLocalServiceUtil
class.
Now you can indirectly call the finder method from your portlet class or from a
JSP by calling
EventLocalServiceUtil.findByEventNameEventDescriptionLocationName(...)
!
Congratulations on following the 3 step process in developing a custom SQL query and custom finder for your portlet!
Next you’ll tour through the service.properties
file that Service Builder
generates.