Liferay Service Builder Finder Query

---

Finder Query is used for finding something in database. Liferay Service builder tool provides a nice way to build our own Finder Query. In Liferay perspective Finder Query is simply finder methods which are written by developers. For example say we have customer information in our database and we want to retrieve all the data by first name of the customers. In this case we will write our own finder method (i.e., by means by Finder Query explaining in shortly) where firstName will be the parameter. We can call the finder method by passing firstName of the customer and get all the data.  Therefore Finder Query or Finder Method is used for retrieving data based on some parameters. We will explain how Liferay Service builder tool can be used to build our own finder method or finder query.

---

How to write Finder Query:

At first the finder information are written in service.xml file by finder tag. The finder tag can have below attributes 

i) name (mandatory)
ii) return-type (mandatory)
iii) unique (optional)
iv) where (optional)
v) db-index (optional)

Under finder tag there must be finder-column tag. See the complete service.xml with a finder 

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE service-builder PUBLIC "-//Liferay//DTD Service Builder 6.2.0//EN" "http://www.liferay.com/dtd/liferay-service-builder_6_2_0.dtd">
<service-builder package-path="com.proliferay.sbuilder.example.crud">
	<author>Hamidul Islam</author>
	<namespace>Crud</namespace>
	<entity name="Book" table="CRUD_BOOK_PORTLET" local-service="true" 	remote-service="true">
		<column name="bookId" type="long" primary="true" id-type="increment" />
		<column name="bookName" type="String" />
		<column name="description" type="String" />
		<column name="authorName" type="String" />
		<column name="isbn" type="int" />
		<column name="price" type="int" />

		<finder return-type="Collection" name="Author">
			<finder-column name="authorName" />
		</finder>

	</entity>
</service-builder>

After building the service a new method will be inserted in BookUtil class. Look at the below 

 

---

i) First write the finder in service.xml.

ii) Build the service by ant build-service.

iii) Verify that the finder method is generated in BookUtil class.

iv) Write a method (e.g., getBookByAuthor) in your BooklocalServiceImpl class and call the finder method. 

v) Build the service again.

vi) New method written in BookLocalServiceImpl will be available in BookLocalServiceUtil.

vii) Call your finder like BookLocalServiceUtil.getBookByAuthor(“author name”) from anywhere of your portlet. 

---

[focus]

After building the service the finder methods are available in ${EntityName}Util class.  If the entity name is Book (see the service.xml) then util class would be BookUtil, if the Entity name is Customer then CustomerUtil and so on. However we should never call finder directly like BookUtil.findByAuthor in our JSP or portlet class. We should call the finder from either ${EntityName}LocalServiceImpl or from  ${EntityName}ServiceImpl. For example we should call BookUtil.findByAuthor from either BookLocalServiceImpl or from BookServiceImpl. 

[/focus]

Example 1: Finder by multiple columns 

<finder return-type="Collection" name="AuthorPrice">
	<finder-column name="authorName" />
	<finder-column name="price" />
</finder>

Generated Method: BookUtil.findByAuthorPrice(String authorName, int price)

Equivalent SQL : select  bookId,  bookName,  description,  authorName,  isbn,  price from CRUD_BOOK_PORTLET where ( authorName=? ) AND ( price=? )

Therefore AND operator will be used for multiple column. 

Example 2: Using arrayable-operator

<finder return-type="Collection" name="Price">
	<finder-column name="price" arrayable-operator="OR" />
</finder>

The attribute arrayable-operator takes in the values AND or OR and will generate an additional finder where this column’s parameter takes an array instead of a single value. Every value in this array will be compared with the column using the comparator, and the conditions will be combined with either an AND or OR operator. For example, a finder column with the = comparator and an arrayable-operator of OR will act like an IN clause.

Generated Method: 

BookUtil.findByPrice(int price)
BookUtil.findByPrice(int[] prices)

Equivalent SQL :

Say the length of input array is 3. For example int[] prices = {100,200,300} and the call findByPrice(prices) will fire the below query

select  bookId,  bookName,  description,  authorName,  isbn ,  price  from CRUD_BOOK_PORTLET  where ((( price=? ))OR(( price=? ))OR(( price=? )))

Example 3: Using comparator 

<finder return-type="Collection" name="BookName">
	<finder-column name="bookName" comparator="LIKE"/>
</finder>

The attribute comparator takes in the values =, !=, <, <=, >, >=, or LIKE and is used to compare this column.
Generated Method:
BookUtil.findByBookName(String bookName) // Must be called this method using a string with % for comparator LIKE
Equivalent SQL :
select  bookId,  bookName ,  description,  authorName,  isbn,  price from CRUD_BOOK_PORTLET  where ( bookName LIKE ? ) order by   bookId

Example 4: Using where in the finder query

<finder return-type="Collection" name="Author" where="price = 100">
	<finder-column name="authorName" />
</finder>

Equivalent SQL :

select  bookId,  bookName,  description ,  authorName,  isbn,  price from CRUD_BOOK_PORTLET where ( authorName=? )AND( price=100 )

Example 5: Case Sensitive finder query 

	<finder return-type="Collection" name="Author">
		<finder-column name="authorName" case-sensitive="true"/>
	</finder>

The attribute case-sensitive is a boolean value and is only used if the column is a String value. The generated method and equivalent query are the same as explained before. The database must support case sensitive query. Otherwise there will not be any effect of the case sensitive finder query. 

Example 6: DB Index 

<finder return-type="Collection" name="Author" db-index="true">
	<finder-column name="authorName" case-sensitive="true"/>
</finder>

If the db-index value is true, then the service will automatically generate a SQL index for this finder. The default value is true.