使用Liferay DynamicQuery API

lyn1984

从下面的内容中，我们可以清楚了解到如何使用com.liferay.portal.kernel.dao.DynamicQueryInitializer 做查询：

下面的内容来自Liferay wiki：

Liferay provides several ways to define complex queries used in retrieving database data. Each service Entity typically defines several 'finder' methods which form the basis for the default views used throughout the portal.

Introduction

There are several reasons/use cases for wanting to move beyond those existing 'finder' queries:

·         the level of complexity allowed by the service generation is not sufficient for your purposes and/or

·         you need queries which implement aggregate SQL operations such as max, min, avg, etc.

·         you want to return composite objects or tuples rather than the mapped object types

·         you want to access the data in way not originally conceived for whatever reason

·         query optimization

·         complex data access, like reporting

5.1+ Example  

Here is a simple query to find bookmarks associated to a given userId and folderId, returning a list of BookmarksEntry (the start/end are optional):

DynamicQuery query = DynamicQueryFactoryUtil.forClass(BookmarksEntry.class)

      .add(PropertyFactoryUtil.forName("folderId").eq(new Long(folderId)))

      .add(PropertyFactoryUtil.forName("userId").eq(new Long(userId)));

List results = BookmarksEntryLocalServiceUtil.dynamicQuery(query, start, end);

[size=+0]4.3+ Example

[size=+0]For our example, let's consider the BookmarksEntry entity that belongs to the Bookmarks portlet/service.

[size=+0]By default the Bookmarks portlet displays it's content on a per group basis, that is to say it shows all the Bookmarks in a Community (subject to individual permissions of course) all at once. On the other hand, when Bookmarks are created they reference the user who created them. As such, it would be entirely possible to query for the Bookmarks of a given user. If one wanted to create an extended or modified Bookmarks portlet which took the user into account, then this would require queries involving the userId as a parameter.

[size=+0]Assuming we know the folderId in question, the following DetachedCriteria query does just what we want:

[size=+0]  DetachedCriteria query =

[size=+0]    DetachedCriteria.forClass(BookmarksEntry.class)

[size=+0]        .add(Property.forName("folderId").eq(new Long(folderId)))

[size=+0]        .add(Property.forName("userId").eq(new Long(userId)));

[size=+0]If we wanted to order results, for example on createDate:

[size=+0]  DetachedCriteria query =

[size=+0]    DetachedCriteria.forClass(BookmarksEntry.class)

[size=+0]        .add(Property.forName("folderId").eq(new Long(folderId)))

[size=+0]        .add(Property.forName("userId").eq(new Long(userId)))

[size=+0]        .addOrder(Order.asc("createDate"));

[size=+0]If we wanted to get the user's most visited bookmark:

[size=+0]  DetachedCriteria query =

[size=+0]    DetachedCriteria.forClass(BookmarksEntry.class)

[size=+0]        .add(Property.forName("folderId").eq(new Long(folderId)))

[size=+0]        .add(Property.forName("userId").eq(new Long(userId)))

[size=+0]        .setProjection(Projections.max("visits"));

[size=+0]If we wanted to get the number of bookmarks:

[size=+0]  DetachedCriteria query =

[size=+0]    DetachedCriteria.forClass(BookmarksEntry.class)

[size=+0]        .add(Property.forName("folderId").eq(new Long(folderId)))

[size=+0]        .add(Property.forName("userId").eq(new Long(userId)))

[size=+0]        .setProjection(Projections.rowCount());

[size=+0]Subqueries, Associations, Projections, Aliases are all features available through the DetachedCriteria API.

[size=+0]To continue with our example, we should consider the fact that all of the portal/portlet services, having been created through service generation, automatically implement the following two methods:

[size=+0]  public static java.util.List dynamicQuery(

[size=+0]    com.liferay.portal.kernel.dao.DynamicQueryInitializer queryInitializer)

[size=+0]    throws com.liferay.portal.SystemException {

[size=+0]    ...

[size=+0]  }

[size=+0]

[size=+0]  public static java.util.List dynamicQuery(

[size=+0]    com.liferay.portal.kernel.dao.DynamicQueryInitializer queryInitializer,

[size=+0]    int begin, int end) throws com.liferay.portal.SystemException {

[size=+0]    ...

[size=+0]  }

[size=+0]i.e.

[size=+0]  BookmarksEntryLocalServiceUtil.dynamicQuery(DynamicQueryInitializer queryInitializer);

[size=+0]  BookmarksEntryLocalServiceUtil.dynamicQuery(DynamicQueryInitializer queryInitializer, int begin, int end);

[size=+0]They allow us to define and pass along a DetachedCriteria through the DynamicQueryInitializer on to the db session. The basic logic is as follows:

[size=+0]·         Define the DetachedCriteria query

[size=+0]·         Create a DynamicQueryInitializer (dqi) using the query

[size=+0]·         Pass the dqi into the service's dynamicQuery() methods

[size=+0]Let's create a helper class to contain all this logic and our new query methods.

[size=+0]  public class BookmarksQueryUtil {

[size=+0]  }

[size=+0]To fulfill our contract with the default business logic of the portal, we usually require three types of methods, one providing a count of the objects, a second for retrieving all the objects, and one for paginating through the objects.

[size=+0]First the object count:

[size=+0]  public class BookmarksQueryUtil {

[size=+0]

[size=+0]    public static int getEntryCount(long folderId, long userId) {

[size=+0]     

[size=+0]      DetachedCriteria query =

[size=+0]        DetachedCriteria.forClass(BookmarksEntry.class)

[size=+0]          .add(Property.forName("folderId").eq(new Long(folderId)))

[size=+0]          .add(Property.forName("userId").eq(new Long(userId)))

[size=+0]          .setProjection(Projections.rowCount());

[size=+0]   

[size=+0]      DynamicQueryInitializer dqi = new DynamicQueryInitializerImpl(query);

[size=+0]     

[size=+0]      int count = 0;

[size=+0]     

[size=+0]      try {

[size=+0]        Iterator resultsItr =

[size=+0]          BookmarksEntryLocalServiceUtil.dynamicQuery(dqi).iterator();

[size=+0]      

[size=+0]        if (resultsItr.hasNext()) {

[size=+0]          count = ((Integer)resultsItr.next()).intValue();

[size=+0]        }

[size=+0]      }

[size=+0]      catch (SystemException se) {

[size=+0]        _log.error(se.getMessage(), se);

[size=+0]      }

[size=+0]     

[size=+0]      return count;

[size=+0]    }

[size=+0]   

[size=+0]    ...

[size=+0]

[size=+0]  }

[size=+0]Next, all the objects:

[size=+0]  public class BookmarksQueryUtil {

[size=+0]

[size=+0]    ...

[size=+0]   

[size=+0]    public static List getEntries(long folderId, long userId) {

[size=+0]

[size=+0]      DetachedCriteria query =

[size=+0]        DetachedCriteria.forClass(BookmarksEntry.class)

[size=+0]            .add(Property.forName("folderId").eq(new Long(folderId)))

[size=+0]            .add(Property.forName("userId").eq(new Long(userId)));

[size=+0]

[size=+0]      DynamicQueryInitializer dqi = new DynamicQueryInitializerImpl(query);

[size=+0]

[size=+0]      List results = new ArrayList();

[size=+0]

[size=+0]      try {

[size=+0]        results = BookmarksEntryLocalServiceUtil.dynamicQuery(dqi);

[size=+0]      }

[size=+0]      catch (SystemException se) {               

[size=+0]        _log.error(se.getMessage(), se);

[size=+0]      }

[size=+0]      

[size=+0]      return results;

[size=+0]    }

[size=+0]   

[size=+0]    ...

[size=+0]

[size=+0]  }

[size=+0]Last, the paginator:

[size=+0]  public class BookmarksQueryUtil {

[size=+0]

[size=+0]    ...

[size=+0]   

[size=+0]    public static List getEntries(long folderId, long userId, int start,

[size=+0]      int end) {

[size=+0]

[size=+0]      DetachedCriteria query =

[size=+0]        DetachedCriteria.forClass(BookmarksEntry.class)

[size=+0]            .add(Property.forName("folderId").eq(new Long(folderId)))

[size=+0]            .add(Property.forName("userId").eq(new Long(userId)));

[size=+0]

[size=+0]      DynamicQueryInitializer dqi = new DynamicQueryInitializerImpl(query);

[size=+0]

[size=+0]      List results = new ArrayList();

[size=+0]

[size=+0]      try {

[size=+0]        results =

[size=+0]          BookmarksEntryLocalServiceUtil.dynamicQuery(dqi, start, end);

[size=+0]      }

[size=+0]      catch (SystemException se) {               

[size=+0]        _log.error(se.getMessage(), se);

[size=+0]      }

[size=+0]

[size=+0]      return results;

[size=+0]    }

[size=+0]

[size=+0]    ...

[size=+0]

[size=+0]  }

[size=+0]Conclusion

[size=+0]The 'DynamicQuery API' provides an elegant way to define complex queries without complex setup or a stiff and abstract learning curve. This abstracts away the SQL grammar, making it DB agnostic, without giving up all of the power. There are no configuration files and no abhorrent embedded SQL strings. And, since it creates the query without the immediate need of a db session the queries can be assembled through business logic, making them even more flexible

lyn1984

遨豪一直专注于Liferay门户本地化服务,致力于为中国用户提供最佳的Liferay门户解决方案，其主要服务包括：
1. Liferay门户二次开发服务（基于客户需求）
2. Liferay门户技术支持服务 （现场+远程）
3. Liferay门户定制培训服务 （现场+远程）
4. Liferay门户企业版服务  （提供liferay企业版+专业技术支持）
------------------------------------------------------------
遨豪(大连)科技有限公司
Liferay 中国合作伙伴
-------------------------------------
市场部: Mr.Luo
-------------------------------------
电话:411-8405-2127
传真:411-8489-8263
MSN: liferayjw@hotmail.com
QQ:  1209462980
email:jiajia6f@163.com
-------------------------------------
地址：大连高新技术产业园区
-------------------------------------

lyn1984

遨豪科技的网址:www.aukcell.com