DynamicQuery一张或者多张表

 

public List<TagsAsset> getMostPopularArticles(String companyId,
			String groupId, String type, int limit) {
		List<TagsAsset> results = Collections
				.synchronizedList(new ArrayList<TagsAsset>());
		DynamicQuery dq0 = DynamicQueryFactoryUtil.forClass(
				JournalArticle.class, "journalarticle").setProjection(
				ProjectionFactoryUtil.property("resourcePrimKey")).add(
				PropertyFactoryUtil.forName("journalarticle.companyId")
						.eqProperty("tagsasset.companyId")).add(
				PropertyFactoryUtil.forName("journalarticle.groupId")
						.eqProperty("tagsasset.groupId")).add(
				PropertyFactoryUtil.forName("journalarticle.type").eq(
						"article-content"));
		DynamicQuery query = DynamicQueryFactoryUtil.forClass(TagsAsset.class,
				"tagsasset").add(
				PropertyFactoryUtil.forName("tagsasset.classPK").in(dq0))
				.addOrder(OrderFactoryUtil.desc("tagsasset.viewCount"));
		try {
			List<Object> assets = TagsAssetLocalServiceUtil.dynamicQuery(query);
			int index = 0;
			for (Object obj : assets) {
				TagsAsset asset = (TagsAsset) obj;
				results.add(asset);
				index++;
				if (index == limit)
					break;
			}
		} catch (Exception e) {
			_log.debug("Error: " + e.getMessage());
			return results;
		}
		return results;
	}

 

1、多张表

我自己的：页面传入feed_provider_type_id,在feed_provider_content_type中找出所有的feed_content_type_id,然后关联feed_content_type.

类似：

select fct.* from 
feed_content_type fct,FEED_PROVIDER_CONTENT_TYPE fpct 
where fct.feed_content_type_id = fpct.feed_content_type_id and fpct.feed_provider_type_id = 1;

或：

select fct.* from feed_content_type fct where fct.feed_content_type_id in 
      (select fpct.feed_content_type_id from FEED_PROVIDER_CONTENT_TYPE fpct where fpct.feed_provider_type_id = 1);

表结构：

CREATE TABLE `FEED_PROVIDER_CONTENT_TYPE` (
	`FEED_CONTENT_TYPE_ID`   	INT NOT NULL,
	`FEED_PROVIDER_TYPE_ID` 	INT NOT NULL
)

 

CREATE TABLE `FEED_CONTENT_TYPE` (
	`FEED_CONTENT_TYPE_ID`   	INT NOT NULL,
	`FEED_CONTENT_TYPE_NAME` 	VARCHAR(32) NOT NULL,
	`FEED_CONTENT_TYPE_DESC`        VARCHAR(255)
)

 java代码：

public List<FeedContentType> getFeedContentTypeByFeedProviderTypeId(long feedProviderTypeId) throws SystemException {
		List<FeedContentType> feedContentTypes = null;
		if (feedProviderTypeId > 0) {
			DynamicQuery dqFeedProviderContentType = DynamicQueryFactoryUtil
					.forClass(FeedProviderContentType.class, "fpc")
					.add(PropertyFactoryUtil.forName("fpc.primaryKey.feedProviderTypeId").eq(new Long(feedProviderTypeId)))
					.setProjection(ProjectionFactoryUtil.property("fpc.primaryKey.feedContentTypeId"));
			DynamicQuery dqFeedContentType = DynamicQueryFactoryUtil.forClass(FeedContentType.class, "fct")
					.add(PropertyFactoryUtil.forName("fct.feedContentTypeId").in(dqFeedProviderContentType));
			feedContentTypes = this.dynamicQuery(dqFeedContentType);
		}
		return feedContentTypes;
	}

注意：一个表有多个主键，则要用:primaryKey.feedContentTypeId.

API: http://www.liferay.com/community/wiki/-/wiki/Main/Dynamic+Query+API#section-Dynamic+Query+API-Related+Articles 

 

2、groupby

 

DynamicQuery query = DynamicQueryFactoryUtil.forClass(TagsEntry.class)
        .setProjection(ProjectionFactoryUtil.projectionList()
	    .add(ProjectionFactoryUtil.groupProperty("userName"))
       	.add(ProjectionFactoryUtil.count("entryId")));

 

分析：这个语句类似

select userName,count(entryId) FROM TagsEntry group by userName;

 注意：setProjection 中的都是结果。

接下来取出结果：

List<Object[]> list = TagsEntryServiceUtil.dynamicQuery(query);
for (Object[] object : list) {
	String userName = (String)object[0];
	int count = (Integer)object[1];
}

 3、ClassNotFoundException

如果在.forClass(AssetCategory.class)时候报错，说ClassNotFoundException。

可以这样解决：

ClassLoader loader = PortalClassLoaderUtil.getClassLoader();//加上这个
DynamicQuery query = DynamicQueryFactoryUtil
	.forClass(AssetCategory.class, loader)
	.add(PropertyFactoryUtil.forName("groupId").eq(
        new Long(groupId)))
	.add(PropertyFactoryUtil.forName("name").eq(categoryName));
List results = AssetCategoryLocalServiceUtil.dynamicQuery(query);

4、like

protected DynamicQuery getCategoryDQ(long groupId, String name) {
	     DynamicQuery dq = DynamicQueryFactoryUtil.forClass(
	         AssetCategory.class, PortalClassLoaderUtil.getClassLoader());
	
	     dq.add(PropertyFactoryUtil.forName("groupId").eq(groupId));
	     // category name case insensitive match
	     dq.add(RestrictionsFactoryUtil.ilike("name", name));
	
	     return dq;
	 }