Wiki source code of velocityHqlExamples
Version 4.5 by Sergiu Dumitriu on 2008/05/19
Hide last authors
author | version | line-number | content |
---|---|---|---|
![]() |
1.36 | 1 | <style> |
2 | .code { | ||
![]() |
1.37 | 3 | width: 100%; |
![]() |
1.36 | 4 | } |
5 | </style> | ||
6 | |||
![]() |
4.3 | 7 | #includeTopic("DevGuide.velocityHqlExamplesMacro") |
![]() |
1.23 | 8 | |
![]() |
1.34 | 9 | 1 Velocity HQL query examples |
![]() |
1.1 | 10 | |
11 | XWiki allows user to access documents and objects with [hql>http://www.hibernate.org/hib_docs/reference/en/html/queryhql.html] queries in [Velocity>http://jakarta.apache.org/velocity/docs/user-guide.html] scripts. <br/> | ||
12 | |||
![]() |
1.31 | 13 | #toc("" "" "") |
14 | |||
![]() |
1.22 | 15 | 1.1 Public API (searchDocuments) |
![]() |
1.3 | 16 | |
![]() |
1.6 | 17 | #info("With this API the query consist in the WHERE condition.\\ |
![]() |
4.4 | 18 | Any user with edit rights can use this API (as in write a script using it).\\ |
19 | Any user with view rights can view the result of such a query.") | ||
![]() |
1.3 | 20 | |
![]() |
1.1 | 21 | You can execute queries as follows: |
22 | {code} | ||
![]() |
1.22 | 23 | #set($query="where doc.creator='XWiki.VincentMassol'") |
![]() |
1.25 | 24 | #set($results = $xwiki.searchDocuments($query, 5, 0)) |
![]() |
1.3 | 25 | #foreach ($item in $results) |
![]() |
4.5 | 26 | * $item |
![]() |
1.3 | 27 | #end |
28 | {code} | ||
29 | |||
![]() |
1.33 | 30 | 1.1.1 Simple Query |
![]() |
1.22 | 31 | #set($hql="where doc.creator='XWiki.VincentMassol'") |
![]() |
1.3 | 32 | #displayQuery($hql false) |
33 | |||
![]() |
1.35 | 34 | 1.1.1 Ordered Query |
![]() |
1.22 | 35 | #set($hql="where doc.creator='XWiki.VincentMassol' order by doc.date asc") |
![]() |
1.21 | 36 | #displayQuery($hql false) |
37 | |||
![]() |
1.35 | 38 | 1.1.1 Advanced Query (date & time) |
39 | |||
![]() |
4.5 | 40 | Since there is no [standard way to calculate dates interval in HQL>http://opensource.atlassian.com/projects/hibernate/browse/HHH-2434] those queries are a bit unnatural. |
![]() |
1.35 | 41 | |
42 | #set($hql="where year(doc.date) = year(current_date()) and month(doc.date) = month(current_date()) and day(doc.date) = day(current_date()) and hour(doc.date) > (hour(current_time()) - 1) order by doc.date desc") | ||
![]() |
1.21 | 43 | #displayQuery($hql false) |
44 | |||
![]() |
1.35 | 45 | {code} |
46 | Other examples, documents modified : | ||
![]() |
1.21 | 47 | |
![]() |
1.35 | 48 | during current day : "where year(doc.date) = year(current_date()) and month(doc.date) = month(current_date()) and day(doc.date) > (day(current_date()) - 1) order by doc.date desc" |
49 | during current week : "where year(doc.date) = year(current_date()) and month(doc.date) = month(current_date()) and day(doc.date) > (day(current_date()) - 7) order by doc.date desc" | ||
50 | during current month : "where year(doc.date) = year(current_date()) and month(doc.date) > (month(current_date()) - 1) order by doc.date desc" | ||
51 | {code} | ||
52 | |||
53 | |||
![]() |
1.22 | 54 | 1.1 Privileged API (search : Documents, Objects, Properties, etc) |
![]() |
1.3 | 55 | |
![]() |
1.6 | 56 | #warning("Calls to te privileged API are only executed when the calling page has been saved by an Admin. \\ |
![]() |
4.4 | 57 | The reason is that search can be used to send any HQL command like update, delete, etc.") |
![]() |
1.3 | 58 | |
59 | You can execute queries as follows: | ||
60 | {code} | ||
![]() |
1.2 | 61 | #set($query="select doc.name from XWikiDocument doc") |
![]() |
1.25 | 62 | #set($results = $xwiki.search($query, 5, 0)) |
![]() |
1.2 | 63 | #foreach ($item in $results) |
64 | * $item <br/> | ||
65 | #end | ||
![]() |
1.1 | 66 | {code} |
67 | |||
![]() |
1.33 | 68 | 1.1.1 Simply Query |
![]() |
1.1 | 69 | #set($hql="select doc.name from XWikiDocument doc") |
![]() |
1.3 | 70 | #displayQuery($hql true) |
![]() |
1.1 | 71 | |
![]() |
1.33 | 72 | 1.1.1 Count Query |
![]() |
1.19 | 73 | |
74 | {code} | ||
75 | #set($query="select count(doc) from XWikiDocument doc") | ||
![]() |
1.20 | 76 | #set($results = $xwiki.search($query)) |
![]() |
1.21 | 77 | ## $xwiki.search returning a list, we get its first element |
![]() |
1.19 | 78 | $query result : $results.get(0) |
79 | {code} | ||
80 | |||
81 | #set($query="select count(doc) from XWikiDocument doc") | ||
![]() |
1.20 | 82 | #set($results = $xwiki.search($query)) |
83 | $query results : $results.get(0) | ||
![]() |
1.19 | 84 | |
![]() |
1.1 | 85 | <br/> |
![]() |
1.33 | 86 | 1.1.1 Simply Query with multiple fields |
![]() |
1.6 | 87 | |
![]() |
1.1 | 88 | {code} |
![]() |
1.25 | 89 | #set($results=$xwiki.search("select doc.name, doc.date from XWikiDocument doc", 5, 0)) |
![]() |
1.2 | 90 | #foreach ($row in $results) |
91 | #foreach ($col in $row) | ||
92 | #if ($velocityCount==1) | ||
93 | #set($docName=$col) | ||
94 | #elseif ($velocityCount==2) | ||
95 | #set($docDate=$col) | ||
96 | #end | ||
97 | #end | ||
98 | $docName : $docDate <br/> | ||
99 | #end | ||
![]() |
1.1 | 100 | {code} |
101 | |||
102 | #set($hql="select doc.name, doc.date from XWikiDocument doc") | ||
![]() |
1.3 | 103 | #displayQuery($hql true) |
![]() |
1.1 | 104 | |
105 | <br/> | ||
![]() |
1.3 | 106 | 1.1.1 Getting objects of a specific class |
![]() |
1.1 | 107 | #set($hql="select obj.name from BaseObject obj where obj.className='XWiki.XWikiUsers'") |
![]() |
1.3 | 108 | #displayQuery($hql true) |
![]() |
1.1 | 109 | |
110 | <br/> | ||
![]() |
1.3 | 111 | 1.1.1 Getting objects' properties |
![]() |
1.1 | 112 | #set($hql="select obj.name, prop.value from BaseObject obj, StringProperty prop where obj.className='XWiki.XWikiUsers' and prop.id.id=obj.id and prop.name='first_name'") |
![]() |
1.3 | 113 | #displayQuery($hql true) |
![]() |
1.19 | 114 | |
115 | <br/> | ||
![]() |
1.30 | 116 | 1.1.1 Getting documents where objects' properties equals some value |
117 | #set($hql="select doc.fullName from XWikiDocument doc, BaseObject obj, StringProperty prop where doc.fullName=obj.name and obj.className='XWiki.XWikiUsers' and prop.id.id=obj.id and prop.name='first_name' and prop.value='Jean-Vincent'") | ||
![]() |
1.19 | 118 | #displayQuery($hql true) |
119 | |||
120 | |||
![]() |
1.30 | 121 |