HQL Query Examples in Velocity

Last modified by Danilo Oliveira on 2014/07/17 23:42

HQL Query Examples in Velocity

XWiki allows user to access documents and objects with HQL queries in Velocity scripts.

Public API (searchDocuments)

With this API the query consist in the WHERE condition of a full HQL query. Any user with edit rights can write a script using this API. Any user with view rights can view the result of such a query.

General example showing how to display the first 5 results of a given query:

#set($hql = "<query here>")
#set($results = $xwiki.searchDocuments($hql, 5, 0))
#foreach ($item in $results)
 * $item
#end

The examples below will show you various HQL queries that you can write.

Simple Query

Displays all documents who have been created by the user XWiki.JohnDoe:

#set($hql = "where doc.creator='XWiki.JohnDoe'")

Ordered Query

Displays all documents who have been created by the user XWiki.JohnDoe and sorted by document's last modification date, in ascending order:

#set($hql = "where doc.creator='XWiki.VincentMassol' order by doc.date asc")

Advanced Query (date & time)

Since there is no standard way to calculate dates interval in HQL those queries are a bit unnatural.
#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")

Other examples:

  • Listing all documents modified during the 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
  • Listing all documents modified during the 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
  • Listing all documents modified during the current month: where year(doc.date) = year(current_date()) and month(doc.date) > (month(current_date()) - 1) order by doc.date desc

Privileged API (search)

Warning: Calls to the privileged API are only executed when the calling page has been saved by a user with Programming Rights. The reason is that search can be used to send dangerous HQL command like update, delete, etc.

General example showing how to display the first 5 results of a given query:

#set($hql = "<query here>")
#set($results = $xwiki.search($hql, 5, 0))
#foreach ($item in $results)
 * $item
#end

The examples below will show you various HQL queries that you can write.

Simple Query

#set($hql = "select doc.name from XWikiDocument doc")

Count Query

#set($results = $xwiki.search("select count(doc) from XWikiDocument doc"))
## Since $xwiki.search is returning a list, we get its first element
Count : $results.get(0)

Simple Query with multiple fields

#set($results = $xwiki.search("select doc.name, doc.date from XWikiDocument doc", 5, 0))
#foreach ($row in $results)
  #foreach ($col in $row)
    #if ($velocityCount == 1)
      #set($docName = $col)
    #elseif ($velocityCount == 2)
      #set($docDate = $col)
    #end
  #end
  $docName : $docDate <br/>
#end

Getting objects of a specific class

#set($hql = "select obj.name from BaseObject obj where obj.className='XWiki.XWikiUsers'")

Getting objects' properties

#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'")

Getting documents where objects' properties equals some value

#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'")
#set($hql = ", BaseObject as obj, StringProperty as firstName, StringProperty as lastName where doc.fullName = obj.name and obj.className='XWiki.XWikiUsers' and obj.id=firstName.id.id and firstName.id.name='first_name' and obj.id=lastName.id.id and lastName.id.name='last_name' and firstName.value like 'A%' and lastName.value like 'B%' order by doc.fullName asc")

List users currently editing pages

#set($hql = "select distinct lock.userName from XWikiLock lock")

List attachments of a page

#set($hql = "select att.filename from XWikiAttachment att, XWikiDocument doc where doc.fullName='Main.WebHome' and att.docId=doc.id")

Statistics

Non-exhaustive list of queryable Object Fields

The full list of available fields can be found in the Hibernate mapping files (*.hbm.xml), which can be found inside WEB-INF/lib/xwiki-core-x.y.jar (where x.y is the XWiki version).

XWikiDocument

  • XWikiDocument.fullName : full name, including space and page name. Example value: Main.WebHome
  • XWikiDocument.author : last editor. Example value: XWiki.Admin
  • XWikiDocument.creator : first editor. Example value: XWiki.Admin

BaseObject

  • BaseObject.id : arbitrary unique id of the object. Example value: 123456789
  • BaseObject.className : class. Example value: XWiki.XWikiUsers

*Property (StringProperty, IntegerProperty, etc)

  • Property.id.id : unique id of the object the property belongs to. Example value: 123456789
  • Property.name : name of the property. Example value: first_name
  • Property.value : value. Example value: John
Tags: HQL
Created by jvdrean on 2007/10/09 10:43
    

Get Connected