Skip to Content

Wiki source code of velocityHqlExamples

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