[Liferay6.2]Liferay Dynamic Query API示例

2024-07-21 02:52:08

字體：大中小

供稿：網(wǎng)友

介紹
Liferay提供了幾種方法定義復(fù)雜的查詢用來檢索數(shù)據(jù)庫中的數(shù)據(jù)。
通常情況下，在每個service Entity中，通過定義一些'finder'方法，可以便捷地滿足基本的數(shù)據(jù)查詢操作。
但是，有時候我們可能會遇到以下幾種finder查詢并不能滿足的情況：
過于復(fù)雜的查詢，例如子查詢需要實現(xiàn)一些聚合操作，像min、max、avg等想得到復(fù)合對象或元組而不是映射的對象類型查詢優(yōu)化復(fù)雜的數(shù)據(jù)訪問，像報表等要實現(xiàn)這個目的，就需要通過Liferay提供的Hibernate的Dynamic Query API實現(xiàn)。
在本文中，我們將演示如何構(gòu)建不同類型的Dynamic Query并執(zhí)行它們。
Dynamic Query基本語法
在Liferay中構(gòu)建一個Dynamic Query基本語法的代碼如下：
//構(gòu)建動態(tài)查詢，相當(dāng)于select * from Entity_NameDynamicQuery dynamicQuery = DynamicQueryFactoryUtil.forClass(Entity_Name.class);//DynamicQueryFactoryUtil.forClass(Entity_Name.class,PortalClassLoaderUtil.getClassLoader());//設(shè)置查詢列dynamicQuery.setPRojection(Projection projection);//設(shè)置查詢條件dynamicQuery.add(Criterion criterion);//設(shè)置排序規(guī)則dynamicQuery.addOrder(Order order);//設(shè)置返回結(jié)果集的范圍dynamicQuery.setLimit(int start, int end);//執(zhí)行動態(tài)查詢，得到結(jié)果集Entity_NameLocalServiceUtil.dynamicQuery(dynamicQuery);其中，
Entity_Name：實體名稱，就是service.xml中制定的Entity名稱。
DynamicQuery也可以通過DynamicQuery forClass(Class<?> clazz, ClassLoader classLoader)來初始化。
Dynamic Query應(yīng)用示例
1、select * from organization_;
DynamicQuery dynamicQuery = DynamicQueryFactoryUtil.forClass(Organization.class);List<Organization> Organizations = OrganizationLocalServiceUtil.dynamicQuery(dynamicQuery);2、select * from organization_ where parentOrganizationId=0;
DynamicQuery dynamicQuery = DynamicQueryFactoryUtil.forClass(Organization.class);dynamicQuery.add(PropertyFactoryUtil.forName("parentOrganizationId").eq(0L));List<Organization> Organizations = OrganizationLocalServiceUtil.dynamicQuery(dynamicQuery);3、like、>、>=、<、<=、between ... and ... in...
// select * from organization_ where name like '組織機構(gòu)%';dynamicQuery.add(PropertyFactoryUtil.forName("parentOrganizationId").like("組織機構(gòu)%"));// select * from organization_ where organizationId >21212;dynamicQuery.add(PropertyFactoryUtil.forName("organizationId").gt(21212L));// select * from organization_ where organizationId >=21212;dynamicQuery.add(PropertyFactoryUtil.forName("organizationId").ge(21212L));// select * from organization_ where organizationId <21224;dynamicQuery.add(PropertyFactoryUtil.forName("organizationId").lt(21224L));// select * from organization_ where organizationId <=21224;dynamicQuery.add(PropertyFactoryUtil.forName("organizationId").le(21224L));// select * from organization_ where organizationId between 21212 and 21224;dynamicQuery.add(PropertyFactoryUtil.forName("organizationId").between(21212L, 21224L));4、and / or
// select * from organization_ where organizationId >= 21212 and organizationId <=21224;// 第1種方法（不適用于or）dynamicQuery.add(PropertyFactoryUtil.forName("organizationId").ge(21212L));dynamicQuery.add(PropertyFactoryUtil.forName("organizationId").le(21224L));// 第2種方法（適用于or，使用RestrictionsFactoryUtil.or）Criterion criterion = null;criterion = RestrictionsFactoryUtil.ge("organizationId", 21212L);criterion = RestrictionsFactoryUtil.and(criterion, RestrictionsFactoryUtil.le("organizationId", 21224L));dynamicQuery.add(criterion);// 第3種方法（適用于or，使用RestrictionsFactoryUtil.disjunction()）Junction junction = RestrictionsFactoryUtil.conjunction();junction.add(PropertyFactoryUtil.forName("organizationId").ge(21212L));junction.add(PropertyFactoryUtil.forName("organizationId").le(21224L));dynamicQuery.add(junction);5、order by
// select * from organization_ order by organizationId asc;dynamicQuery.addOrder(OrderFactoryUtil.asc("organizationId"));// select * from organization_ order by organizationId desc;dynamicQuery.addOrder(OrderFactoryUtil.desc("organizationId"));6、子查詢
// select * from organization_ where parentOrganizationId=(select organizationId from organization_ where name='組織機構(gòu)1');DynamicQuery subDynamicQuery = DynamicQueryFactoryUtil.forClass(Organization.class);subDynamicQuery.setProjection(ProjectionFactoryUtil.property("organizationId"));subDynamicQuery.add(PropertyFactoryUtil.forName("name").eq("組織機構(gòu)1"));dynamicQuery.add(PropertyFactoryUtil.forName("parentOrganizationId").in(subDynamicQuery));7、自定義列
// select name from organization_;dynamicQuery.setProjection(ProjectionFactoryUtil.property("name"));List<Object> names = OrganizationLocalServiceUtil.dynamicQuery(dynamicQuery);for(Object name: names){	System.out.println(name);}// select organizationId,name from organization_;ProjectionList projectionList = ProjectionFactoryUtil.projectionList();projectionList.add(ProjectionFactoryUtil.property("organizationId"));projectionList.add(ProjectionFactoryUtil.property("name"));dynamicQuery.setProjection(projectionList);List<Object[]> organizations = OrganizationLocalServiceUtil.dynamicQuery(dynamicQuery);for(Object[] organization: organizations){	System.out.println(organization[0]+":"+organization[1]);}8、distinct
// select distinct name from organization_;Projection projection = ProjectionFactoryUtil.distinct(ProjectionFactoryUtil.property("name"));dynamicQuery.setProjection(projection);9、group by
// select type_,count(type_) from organization_ group by type_;ProjectionList projectionList = ProjectionFactoryUtil.projectionList();projectionList.add(ProjectionFactoryUtil.property("type"));projectionList.add(ProjectionFactoryUtil.count("name"));projectionList.add(ProjectionFactoryUtil.groupProperty("type"));dynamicQuery.setProjection(projectionList);List<Object[]> organizations = OrganizationLocalServiceUtil.dynamicQuery(dynamicQuery);for(Object[] organization: organizations){    System.out.println(organization[0]+":"+organization[1]);}此外，max聚合函數(shù)調(diào)用方法如下：
max:ProjectionFactoryUtil.max(String propertyName)
其他聚合函數(shù)min、avg等可參考遞推。
10、分頁
// 取第1條到第10條記錄dynamicQuery.setLimit(0,10);11、復(fù)合主鍵
如果實體是符合主鍵，我們要通過復(fù)合主鍵中的屬性列進行查詢的話，則需要在列名前面加上"primaryKey."，如下：
dynamicQuery.add(PropertyFactoryUtil.forName("primaryKey.organizationId").gt(21212L));總結(jié)
以上只是一些基本的示例，能夠解決我們在日常開發(fā)中遇到的大部分問題，此外Dynamic Query API也提供了一些更高級的擴展方法（eqAll、geAll等），這些大家就一起探索吧，以后用到再更新。
通過以上示例，我們可以看到Liferay提供的Dynamic Query API，其實就是通過一組java方法來組成SQL語句，執(zhí)行并獲得結(jié)果。 可能有些朋友會覺得這種方法太過于繁瑣，還不如直接寫SQL來得方便直接。 但是站在平臺數(shù)據(jù)庫兼容性的角度考慮，我們就會發(fā)現(xiàn)這種方式非常合適。 因為liferay支持MySQL、Oracle、db2等多種數(shù)據(jù)庫，如果直接寫SQL的話，很可能碰到其他數(shù)據(jù)庫的語法不支持的情況發(fā)生，像oracle中的遞歸查詢mysql就不支持等。 使用Dynamic Query API的話，我們就可以使用一套統(tǒng)一的語法來構(gòu)建SQL語句，而不需要考慮底層數(shù)據(jù)庫的差異，這樣整個平臺的移植性和兼容性就顯著提高了很多。