« Back to Service Builder

Service Builder Finders

Tags: development

Introduction #

The goal of this article is to show how to build custom sql find methods for your plugins using the service builder. This method allows you to build complex queries to access the data base from your custom plugins.

Description #

The first step is to create a custom MyPortletFinderImpl.java class in /src/path/to/my/custom/portlet/service/persistence and make it extend MyPortletPersistenceImpl.

Launch build-service ant target and this will create (among others) two classes called MyPortletFinder and MyPortletFinderUtil

Edit your custom MyPortletFinderImpl.java class and implement MyPortletFinder interface (that were created in the previous step)

Optional: to follow the way the portal's designed, you can create a myportlet.xml file with the SQL queries.

Also: To add the custom sql, create a folder "custom-sql" in your src folder and declare "default.xml", pointing to your

"myportlet.xml" file with your custom queries.

Exampl for default.xml:

<?xml version="1.0"?>
<custom-sql> 
  <sql file="custom-sql/myportlet.xml" /> 
</custom-sql>

You can read journal.xml for references.

Here you have an excerpt of its content:

<?xml version="1.0"?>

<custom-sql>
	<sql id="com.liferay.portlet.journal.service.persistence.JournalArticleFinder.countByC_G_A_V_T_D_C_T_S_T_D_S_R">
		<![CDATA[
			SELECT
				COUNT(*) AS COUNT_VALUE
			FROM
				JournalArticle
			WHERE
				(companyId = ?) AND
				(groupId = ?) AND
				(
					(articleId LIKE ? [$AND_OR_NULL_CHECK$]) [$AND_OR_CONNECTOR$]
					(version = ?) [$AND_OR_CONNECTOR$]
					(lower(title) LIKE ? [$AND_OR_NULL_CHECK$]) [$AND_OR_CONNECTOR$]
					(description LIKE ? [$AND_OR_NULL_CHECK$]) [$AND_OR_CONNECTOR$]
					(content LIKE ? [$AND_OR_NULL_CHECK$]) [$AND_OR_CONNECTOR$]
					(type_ = ? [$AND_OR_NULL_CHECK$]) [$AND_OR_CONNECTOR$]
					(structureId = ? [$AND_OR_NULL_CHECK$]) [$AND_OR_CONNECTOR$]
					(templateId = ? [$AND_OR_NULL_CHECK$]) [$AND_OR_CONNECTOR$]
					(displayDate >= ? [$AND_OR_NULL_CHECK$]) [$AND_OR_CONNECTOR$]
					(displayDate <= ? [$AND_OR_NULL_CHECK$]) [$AND_OR_CONNECTOR$]
					(
						(status = ?) AND
						(reviewDate <= ? [$AND_OR_NULL_CHECK$])
					)
				)
		]]>
	</sql>

{...}

This file has to be placed in src/custom-sql and it should be declared in default.xml (placed in the same folder) that has this content:

<?xml version="1.0"?>

<custom-sql>
	<sql file="custom-sql/myportlet.xml" />
</custom-sql>

For references about how to use this, read for example JournalArticleFinderImpl. Here you have an excerpt:

{...}
public static String COUNT_BY_C_G_A_V_T_D_C_T_S_T_D_S_R =
		JournalArticleFinder.class.getName() +
			".countByC_G_A_V_T_D_C_T_S_T_D_S_R";
{...}

public int countByC_G_A_V_T_D_C_T_S_T_D_S_R(
			long companyId, long groupId, String[] articleIds, Double version,
			String[] titles, String[] descriptions, String[] contents,
			String type, String[] structureIds, String[] templateIds,
			Date displayDateGT, Date displayDateLT, int status, Date reviewDate,
			boolean andOperator)
		throws SystemException {

		articleIds = CustomSQLUtil.keywords(articleIds, false);
		titles = CustomSQLUtil.keywords(titles);
		descriptions = CustomSQLUtil.keywords(descriptions, false);
		contents = CustomSQLUtil.keywords(contents, false);
		structureIds = CustomSQLUtil.keywords(structureIds, false);
		templateIds = CustomSQLUtil.keywords(templateIds, false);
		Timestamp displayDateGT_TS = CalendarUtil.getTimestamp(displayDateGT);
		Timestamp displayDateLT_TS = CalendarUtil.getTimestamp(displayDateLT);
		Timestamp reviewDate_TS = CalendarUtil.getTimestamp(reviewDate);

		Session session = null;

		try {
			session = openSession();

			String sql = CustomSQLUtil.get(COUNT_BY_C_G_A_V_T_D_C_T_S_T_D_S_R);

			if (groupId <= 0) {
				sql = StringUtil.replace(sql, "(groupId = ?) AND", "");
			}

			sql = CustomSQLUtil.replaceKeywords(
				sql, "articleId", StringPool.LIKE, false, articleIds);

			if (version == null) {
				sql = StringUtil.replace(
					sql, "(version = ?) [$AND_OR_CONNECTOR$]", "");
			}

			sql = CustomSQLUtil.replaceKeywords(
				sql, "lower(title)", StringPool.LIKE, false, titles);
			sql = CustomSQLUtil.replaceKeywords(
				sql, "description", StringPool.LIKE, false, descriptions);
			sql = CustomSQLUtil.replaceKeywords(
				sql, "content", StringPool.LIKE, false, contents);
			sql = CustomSQLUtil.replaceKeywords(
				sql, "structureId", StringPool.EQUAL, false, structureIds);
			sql = CustomSQLUtil.replaceKeywords(
				sql, "templateId", StringPool.EQUAL, false, templateIds);

			if (status == StatusConstants.ANY) {
				sql = StringUtil.replace(sql, "(status = ?) AND", "");
			}

			sql = CustomSQLUtil.replaceAndOperator(sql, andOperator);

			SQLQuery q = session.createSQLQuery(sql);

			q.addScalar(COUNT_COLUMN_NAME, Type.LONG);

			QueryPos qPos = QueryPos.getInstance(q);

			qPos.add(companyId);

			if (groupId > 0) {
				qPos.add(groupId);
			}

			qPos.add(articleIds, 2);

			if (version != null) {
				qPos.add(version);
			}

			qPos.add(titles, 2);
			qPos.add(descriptions, 2);
			qPos.add(contents, 2);
			qPos.add(type);
			qPos.add(type);
			qPos.add(structureIds, 2);
			qPos.add(templateIds, 2);
			qPos.add(displayDateGT_TS);
			qPos.add(displayDateGT_TS);
			qPos.add(displayDateLT_TS);
			qPos.add(displayDateLT_TS);

			if (status != StatusConstants.ANY) {
				qPos.add(status);
			}

			qPos.add(reviewDate_TS);
			qPos.add(reviewDate_TS);

			Iterator<Long> itr = q.list().iterator();

			if (itr.hasNext()) {
				Long count = itr.next();

				if (count != null) {
					return count.intValue();
				}
			}

			return 0;
		}
		catch (Exception e) {
			throw new SystemException(e);
		}
		finally {
			closeSession(session);
		}
	}

{...}

Finally you can call these methods from your XLocalServiceImpl.java class this way:

	public int searchCount(
			long companyId, long groupId, String articleId, Double version,
			String title, String description, String content, String type,
			String structureId, String templateId, Date displayDateGT,
			Date displayDateLT, int status, Date reviewDate,
			boolean andOperator)
		throws SystemException {

		return journalArticleFinder.countByC_G_A_V_T_D_C_T_S_T_D_S_R(
			companyId, groupId, articleId, version, title, description, content,
			type, structureId, templateId, displayDateGT, displayDateLT,
			status, reviewDate, andOperator);
	}
0 Attachments
50845 Views
Average (6 Votes)
The average rating is 3.33333333333333 stars out of 5.
Comments
Threaded Replies Author Date
How do you add permission checking to these... Sampsa Sohlman April 22, 2010 2:11 PM
Hi Sampsa, That's the million dollar question.... Jorge Ferrer April 23, 2010 2:02 AM
how to do this(Launch build-service ant target... kan kan September 6, 2010 5:34 AM
serviceBuilder doesn't generate the interface... Jakub Liska November 27, 2010 1:08 PM
I have the same problem, serviceBuilder doesn't... emanuele granieri February 9, 2011 1:23 AM
Hi everybody, Same problem... I have tried to... Luis Rodríguez Fernández March 22, 2011 12:32 AM
works until i deploy. then i get : ... Tom Mahy August 22, 2011 4:55 AM
Hi, can we customize existing core finderimpl... KK rajput August 23, 2011 9:47 PM
Hi, there was an error in the Wiki article. ... Kim Anna Kunc November 23, 2011 8:51 AM
This article talks about only one table. Where... sandhya shendre April 3, 2012 1:40 PM
Hi all, for 6.1 and upper, an correct pattern... Igor Beslic May 31, 2012 12:20 AM
Hi all. I'm sorry but I can't generate the... Santiago Perez August 3, 2012 4:54 AM
I was facing the similar issue under CE 6.1 and... bergkamp sliew August 11, 2012 4:04 AM
bergkamp is right. There is a very specific... Ben Carson August 31, 2012 8:20 AM

How do you add permission checking to these searches?
Posted on 4/22/10 2:11 PM.
Hi Sampsa,

That's the million dollar question. And there is no easy answer, but Ray has been doing a lot of research and improvings to the permission systems in the last months and he's very closed to an answer. It should be included in Liferay 6. Keep an eye for posts or blogs from him about "inline permissions"
Posted on 4/23/10 2:02 AM in reply to Sampsa Sohlman.
how to do this(Launch build-service ant target and this will create (among others) two classes called MyPortletFinder and MyPortletFinderUtil) in Liferay6.0.X IDE?
Posted on 9/6/10 5:34 AM in reply to Jorge Ferrer.
serviceBuilder doesn't generate the interface and *Util class if I create the finderImpl class in persistence package... It doesn't notice of it as it was not there...
Posted on 11/27/10 1:08 PM in reply to kan kan.
I have the same problem, serviceBuilder doesn't generate the interface and the Util class
Posted on 2/9/11 1:23 AM.
Hi everybody,

Same problem... I have tried to solve it declaring my methods in the interfaces of the services packages (service and persistence), but when I do the ant-compile it says me that it doesn't recognized them...

The main problem is getting the hibernate session. Is there any other possibility of doing it without using classes that are on the portal-impl.jar?

Thanks in advance,

Luis

ps: I think that could be another nasty way of doing this, declare a finder on the service.xml, call the service-builder ant task and replace the code of the implementation, uffff, sounds weird...
Posted on 3/22/11 12:32 AM in reply to emanuele granieri.
works until i deploy.
then i get :

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'com.service.MyPortletLocalService': Could not inject BeanReference fields; nested exception is java.lang.IllegalArgumentException: Can not set com.service.MyPortletLocalService field com.service.base.MyPortletServiceBaseImpl.myportletLocalService to $Proxy338
Posted on 8/22/11 4:55 AM.
Hi,
can we customize existing core finderimpl like if I need to customize journalArticleFinderImpl then how to do??
thanks
kamal
Posted on 8/23/11 9:47 PM in reply to Tom Mahy.
Hi, there was an error in the Wiki article.

The "MyPortletFinderImpl" has to extend "MyPortletPersistenceImpl" not "BasePersistenceImpl" ("MyPortletPersistenceImpl" is generated in the "persistence" package the first time you run Service Builder)
Otherwise Service Builder will not generate *Util class or the interface.
Posted on 11/23/11 8:51 AM.
This article talks about only one table. Where is the other table? How to write custom-sql to join two tables of different portlets? first-portlet-servie.jar is used in second-portlet. Now I want to fetch the data from table1(created by first-portlet) and table2 (created by secocd-portlet) using custom-sql.
How to write custom-sql for 2 tables when one table is from services built of other portlet?

I hope someone will reply me.
Please help me to solve this.

Regards,
Sandhya
Posted on 4/3/12 1:40 PM in reply to Kim A Kunc.
Hi all, for 6.1 and upper, an correct pattern for using finders and custom SQLs are described here http://www.liferay.com/en/community/wiki/-/wiki/1071674/Custom+queries+in+Lifera­y+5.2
Seams that in this article something is messed up since if finder extends from MyPortletPersistenceImpl service builder would be confused and generated code wont comile. You should make finder to extend com.liferay.portal.service.persistence.impl.BasePersistenceImp<JournalArticle> and after service builder gets jobe done, define that finder implements MyPortletFinder.
Posted on 5/31/12 12:20 AM in reply to sandhya shendre.
Hi all.
I'm sorry but I can't generate the interface and the *Util class. I tried the 2 options (extending MyPortletPersistenceImpl or BasePersistenceImpl).

Does anyone know how to do it ??? It's impossible... I have tried many ways without result (included creating the classes "by hand").

Thanks,
Santiago
Posted on 8/3/12 4:54 AM in reply to Igor Beslic.
I was facing the similar issue under CE 6.1 and after a couple of experiments, managed to breakthrough it. What works for me is to have same entity name within the new persistence classname and its interface name. On top of that, keyword "Finder" is a must (weird, huh?). I tried putting other names like "Find", "F", etc. but that brought me back to the same compilation problem.

Below is one of my class definition where "Employee" is an entity in my ServiceBuilder setting. Hope this helps.
...
public class EmployeeFinderImpl extends BasePersistenceImpl
implements EmployeeFinder {
...

I guess that's the curse of using framework.
Posted on 8/11/12 4:04 AM in reply to Santiago Perez.
bergkamp is right. There is a very specific naming convention that must be followed for Finders. David Nebinger explains it better than I can in this post: http://www.liferay.com/en/community/forums/-/message_boards/message/15967926
Posted on 8/31/12 8:20 AM in reply to bergkamp sliew.