Skip to Content

Wiki source code of velocityHqlExamples

Version 18.1 by Vincent Massol on 2009/09/08
Show last authors
1 {{box cssClass="floatinginfobox" title="**Contents**"}}{{toc start="2" depth="4"/}}{{/box}}
2
3 {{include document="DevGuide.velocityHqlExamplesMacro"/}}
4
5 = Velocity HQL query examples =
6
7 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. \\
8
9 == Public API (searchDocuments) ==
10
11 {{velocity filter="none"}}
12 {{html clean="false" wiki="true"}}
13 #info("With this API the query consist in the WHERE condition.\\
14 Any user with edit rights can use this API (as in write a script using it).\\
15 Any user with view rights can view the result of such a query.")
16 <p/>
17 You can execute queries as follows:
18
19 {{code}}
20 #set($query="where doc.creator='XWiki.VincentMassol'")
21 #set($results = $xwiki.searchDocuments($query, 5, 0))
22 #foreach ($item in $results)
23 * $item
24 #end
25 {{/code}}
26
27 === Simple Query ===
28
29 #set($hql="where doc.creator='XWiki.VincentMassol'")
30 #displayQuery($hql false)
31
32 === Ordered Query ===
33
34 #set($hql="where doc.creator='XWiki.VincentMassol' order by doc.date asc")
35 #displayQuery($hql false)
36
37 === Advanced Query (date & time) ===
38
39 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.
40 <p/>
41 #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")
42 #displayQuery($hql false)
43
44 {{code}}
45 Other examples, documents modified :
46
47 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"
48 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"
49 during current month : "where year(doc.date) = year(current_date()) and month(doc.date) > (month(current_date()) - 1) order by doc.date desc"
50 {{/code}}
51
52 == Privileged API (search : Documents, Objects, Properties, etc) ==
53
54 #warning("Calls to te privileged API are only executed when the calling page has been saved by an Admin. \\
55 The reason is that search can be used to send any HQL command like update, delete, etc.")
56 <p/>
57 You can execute queries as follows:
58
59 {{code}}
60 #set($query="select doc.name from XWikiDocument doc")
61 #set($results = $xwiki.search($query, 5, 0))
62 #foreach ($item in $results)
63 * $item <br/>
64 #end
65 {{/code}}
66
67 === Simple Query ===
68
69 #set($hql="select doc.name from XWikiDocument doc")
70 #displayQuery($hql true)
71
72 === Count Query ===
73
74 {{code}}
75 #set($query="select count(doc) from XWikiDocument doc")
76 #set($results = $xwiki.search($query))
77 ## $xwiki.search returning a list, we get its first element
78 $query result : $results.get(0)
79 {{/code}}
80
81 #set($query="select count(doc) from XWikiDocument doc")
82 #set($results = $xwiki.search($query))
83 $query results : $results.get(0)
84 <p/>
85 <br/>
86
87 === Simple Query with multiple fields ===
88
89 {{code}}
90 #set($results=$xwiki.search("select doc.name, doc.date from XWikiDocument doc", 5, 0))
91 #foreach ($row in $results)
92 #foreach ($col in $row)
93 #if ($velocityCount==1)
94 #set($docName=$col)
95 #elseif ($velocityCount==2)
96 #set($docDate=$col)
97 #end
98 #end
99 $docName : $docDate <br/>
100 #end
101 {{/code}}
102
103 #set($hql="select doc.name, doc.date from XWikiDocument doc")
104 #displayQuery($hql true)
105 <p/>
106 <br/>
107
108 === Getting objects of a specific class ===
109
110 #set($hql="select obj.name from BaseObject obj where obj.className='XWiki.XWikiUsers'")
111 #displayQuery($hql true)
112 <p/>
113 <br/>
114
115 === Getting objects' properties ===
116
117 #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'")
118 #displayQuery($hql true)
119 <p/>
120 <br/>
121
122 === Getting documents where objects' properties equals some value ===
123
124 #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'")
125 #displayQuery($hql true)
126
127 === List users currently editing pages ===
128
129 #set($hql="select distinct lock.userName from XWikiLock lock")
130 #displayQuery($hql true)
131
132 === List attachments of a page ===
133
134 #set($hql="select att.filename from XWikiAttachment att, XWikiDocument doc where doc.fullName='Main.WebHome' and att.docId=doc.id")
135 #displayQuery($hql true)
136
137 == Non-exhaustive list of queryable object fields ==
138
139 #macro(exval $value)Example of value : //$value//#end
140
141
142 === XWikiDocument ===
143
144 * **XWikiDocument.fullName** : full name, including space and page name. #exval("Main.WebHome").
145 * XWikiDocument.author : last editor. #exval("XWiki.Admin").
146 * XWikiDocument.creator : first editor. #exval("XWiki.Admin").
147
148 === BaseObject ===
149
150 * **BaseObject.id** : arbitrary unique id of the object. #exval("123456789").
151 * BaseObject.className : class. #exval("XWiki.XWikiUsers").
152
153 === *Property (StringProperty, etc) ===
154
155 * **Property.id.id** : unique id of the object the property belongs to. #exval("123456789").
156 * Property.name : name of the property. #exval("first_name").
157 * Property.value : value. #exval("John").
158 {{/html}}
159 {{/velocity}}

About

About

Support

Platform

User Guide

Admin Guide

Developer Guide

Projects

XWiki

Extensions

Other

Contribute

Status

Practices

Under the Hood

Get Involved

Get Connected