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);
	}