« Volver a Service Builder

Service Builder Finders

Etiquetas: 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 archivos adjuntos
76192 Accesos
Promedio (6 Votos)
La valoración media es de 3.33333333333333 estrellas de 5.
Comentarios
Respuestas anidadas Autor Fecha
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 2:02
how to do this(Launch build-service ant target... kan kan 6 de septiembre de 2010 5:34
serviceBuilder doesn't generate the interface... Jakub Liska 27 de noviembre de 2010 13:08
I have the same problem, serviceBuilder doesn't... emanuele granieri 9 de febrero de 2011 1:23
Hi everybody, Same problem... I have tried to... Luis Rodríguez Fernández 22 de marzo de 2011 0:32
works until i deploy. then i get : ... Tom Mahy 22 de agosto de 2011 4: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 Kunc 23 de noviembre de 2011 8: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 mayo de 2012 0:20
Hi all. I'm sorry but I can't generate the... Santiago Pérez de la Cámara 3 de agosto de 2012 4:54
I was facing the similar issue under CE 6.1 and... bergkamp sliew 11 de agosto de 2012 4:04
bergkamp is right. There is a very specific... Ben Carson 31 de agosto de 2012 8:20

How do you add permission checking to these searches?
Publicado el día 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"
Publicado el día 23/04/10 2:02 en respuesta 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?
Publicado el día 6/09/10 5:34 en respuesta 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...
Publicado el día 27/11/10 13:08 en respuesta a kan kan.
I have the same problem, serviceBuilder doesn't generate the interface and the Util class
Publicado el día 9/02/11 1: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...
Publicado el día 22/03/11 0:32 en respuesta 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
Publicado el día 22/08/11 4:55.
Hi,
can we customize existing core finderimpl like if I need to customize journalArticleFinderImpl then how to do??
thanks
kamal
Publicado el día 23/08/11 21:47 en respuesta 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.
Publicado el día 23/11/11 8: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
Publicado el día 3/04/12 13:40 en respuesta 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.
Publicado el día 31/05/12 0:20 en respuesta 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
Publicado el día 3/08/12 4:54 en respuesta 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.
Publicado el día 11/08/12 4:04 en respuesta 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
Publicado el día 31/08/12 8:20 en respuesta a bergkamp sliew.