« Voltar para Service Builder

Service Builder Finders

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 Anexos
44106 Visualizações
Média (6 Votos)
A média da avaliação é 3.33333333333333 estrelas de 5.
Comentários
Respostas do tópico Autor Data
How do you add permission checking to these... Sampsa Sohlman 22 de Abril de 2010 14:11
Hi Sampsa, That's the million dollar question.... Jorge Ferrer 23 de Abril de 2010 02:02
how to do this(Launch build-service ant target... kan kan 6 de Setembro de 2010 05:34
serviceBuilder doesn't generate the interface... Jakub Liska 27 de Novembro de 2010 13:08
I have the same problem, serviceBuilder doesn't... emanuele granieri 9 de Fevereiro de 2011 01:23
Hi everybody, Same problem... I have tried to... Luis Rodríguez Fernández 22 de Março de 2011 00:32
works until i deploy. then i get : ... Tom Mahy 22 de Agosto de 2011 04:55
Hi, can we customize existing core finderimpl... KK rajput 23 de Agosto de 2011 21:47
Hi, there was an error in the Wiki article. ... Kim Anna Kunc 23 de Novembro de 2011 08:51
This article talks about only one table. Where... sandhya shendre 3 de Abril de 2012 13:40
Hi all, for 6.1 and upper, an correct pattern... Igor Beslic 31 de Maio de 2012 00:20
Hi all. I'm sorry but I can't generate the... Santiago Perez 3 de Agosto de 2012 04:54
I was facing the similar issue under CE 6.1 and... bergkamp sliew 11 de Agosto de 2012 04:04
bergkamp is right. There is a very specific... Ben Carson 31 de Agosto de 2012 08:20

How do you add permission checking to these searches?
Postado em 22/04/10 14:11.
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"
Postado em 23/04/10 02:02 em resposta a 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?
Postado em 06/09/10 05:34 em resposta a 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...
Postado em 27/11/10 13:08 em resposta a kan kan.
I have the same problem, serviceBuilder doesn't generate the interface and the Util class
Postado em 09/02/11 01:23.
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...
Postado em 22/03/11 00:32 em resposta a 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
Postado em 22/08/11 04:55.
Hi,
can we customize existing core finderimpl like if I need to customize journalArticleFinderImpl then how to do??
thanks
kamal
Postado em 23/08/11 21:47 em resposta a 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.
Postado em 23/11/11 08:51.
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
Postado em 03/04/12 13:40 em resposta a 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.
Postado em 31/05/12 00:20 em resposta a 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
Postado em 03/08/12 04:54 em resposta a 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.
Postado em 11/08/12 04:04 em resposta a 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
Postado em 31/08/12 08:20 em resposta a bergkamp sliew.