Service Builder creates finder methods that retrieve entities by their
attributes: their column values. When you add a column as a parameter for the
finder in your
service.xml file and run Service Builder, it generates the
finder method in your persistence layer and adds methods to your service layer
that invoke the finder. If your queries are simple enough, consider using
Dynamic Query to access
Liferay’s database. If you want to do something more complicated like JOINs, you
can write your own custom SQL queries. Here, you’ll learn how.
The Guestbook application has two tables, one for guestbooks and one
for guestbook entries. The entry entity’s foreign key to its guestbook is the
guestbook’s ID. That is, the entry entity table,
GB_GuestbookEntry, tracks an
entry’s guestbook by its long integer ID in the table’s
guestbookId column. If
you want to find a guestbook entry based on its name, message, and guestbook
name, you must access the name of the entry’s guestbook. Of course, with SQL
you can join the entry and guestbook tables to include the guestbook name.
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.
Using Custom SQL in Service Builder is the same as using dynamic queries; it just takes an additional first step to place the SQL you want to run in an XML file. If you plan to use dynamic queries instead, skip the rest of this tutorial and move on to the next one.
After you’ve tested your SQL, you must specify it in a particular file for
Liferay to access it.
CustomSQL class (from module
retrieves SQL from a file called
default.xml in your service module’s
src/main/resources/META-INF/custom-sql/ folder. You must create the
custom-sql folder and create the
default.xml file in that
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>
custom-sql element for every SQL query you want in your application,
and give each query a unique ID. The recommended convention to use 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 provided in the next step.
For example, in the Guestbook application, you could use the following ID value to specify a query:
Custom SQL must be wrapped in character data (
CDATA) for the
Importantly, do not terminate the SQL with a semi-colon. Following these rules,
default.xml file of the Guestbook application specifies an SQL query that
<?xml version="1.0" encoding="UTF-8"?> <custom-sql> <sql id="com.liferay.docs.guestbook.service.persistence.EntryFinder.findByEntryNameEntryMessageGuestbookName"> <![CDATA[ SELECT GB_GuestbookEntry.* FROM GB_GuestbookEntry INNER JOIN GB_Guestbook ON GB_GuestbookEntry.guestbookId = GB_Guestbook.guestbookId WHERE (GB_GuestbookEntry.name LIKE ?) AND (GB_GuestbookEntry.message LIKE ?) AND (GB_Guestbook.name LIKE ?) ]]> </sql> </custom-sql>
Now that you’ve specified some custom SQL, the next step is to implement a
finder method to invoke it. The method name for the finder should match the ID
you just specified for the
Congratulations on developing a custom SQL query and custom finder for your application!