Wiki source code of velocityHqlExamples
Version 1.29 by Jean-Vincent Drean on 2007/10/09
Show last authors
| author | version | line-number | content |
|---|---|---|---|
| 1 | #################################### | ||
| 2 | ## Macro to display the result of any search query in a table | ||
| 3 | #macro(displayQuery $query $privileged) | ||
| 4 | #################################### | ||
| 5 | #set ($results = "") | ||
| 6 | #if (!$privileged) | ||
| 7 | #set($results = $xwiki.searchDocuments($query,5,0)) | ||
| 8 | *\xwiki.searchDocuments(\"${query}\", 5, 0) returns:* | ||
| 9 | |||
| 10 | #else | ||
| 11 | #set($results = $xwiki.search($query,5,0)) | ||
| 12 | *\xwiki.search(\"${query}\", 5, 0) returns:* | ||
| 13 | |||
| 14 | #end | ||
| 15 | {table} | ||
| 16 | #foreach ($row in $results) | ||
| 17 | #if ($velocityCount==1) ### only once per table | ||
| 18 | ##-------------------------- | ||
| 19 | ## establish num columns: | ||
| 20 | ##-------------------------- | ||
| 21 | #set($numCols=0) | ||
| 22 | #foreach ($col in $row) | ||
| 23 | #set($numCols=$velocityCount) | ||
| 24 | #end | ||
| 25 | ##-------------------------- | ||
| 26 | ## print header: | ||
| 27 | ##-------------------------- | ||
| 28 | #if ($numCols>0) | ||
| 29 | #foreach ($col in [1..$numCols]) field $col | #end | ||
| 30 | |||
| 31 | #else | ||
| 32 | Single field | ||
| 33 | #end | ||
| 34 | #end ### end only once per table | ||
| 35 | ##-------------------------- | ||
| 36 | ## table body: | ||
| 37 | ##-------------------------- | ||
| 38 | #if ($numCols>0) | ||
| 39 | #foreach ($column in $row) {pre}$column{/pre} | #end | ||
| 40 | |||
| 41 | #else | ||
| 42 | {pre}$row{/pre} | ||
| 43 | #end | ||
| 44 | #end | ||
| 45 | {table} | ||
| 46 | #################################### | ||
| 47 | #end | ||
| 48 | #################################### | ||
| 49 | |||
| 50 | 1 Velocity HQL query examples. | ||
| 51 | |||
| 52 | 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/> | ||
| 53 | |||
| 54 | #warning("This page can only be edited by Admins since it contains calls to the privileged API. \\ | ||
| 55 | If you want to make an addition or a correction please post a comment here or send it to the mailing lists.") | ||
| 56 | |||
| 57 | 1.1 Public API (searchDocuments) | ||
| 58 | |||
| 59 | #info("With this API the query consist in the WHERE condition.\\ | ||
| 60 | Any user with edit rights can use this API") | ||
| 61 | |||
| 62 | You can execute queries as follows: | ||
| 63 | {code} | ||
| 64 | #set($query="where doc.creator='XWiki.VincentMassol'") | ||
| 65 | #set($results = $xwiki.searchDocuments($query, 5, 0)) | ||
| 66 | #foreach ($item in $results) | ||
| 67 | * $item <br/> | ||
| 68 | #end | ||
| 69 | {code} | ||
| 70 | |||
| 71 | 1.1.1 Simple Query: | ||
| 72 | #set($hql="where doc.creator='XWiki.VincentMassol'") | ||
| 73 | #displayQuery($hql false) | ||
| 74 | |||
| 75 | <br/> | ||
| 76 | 1.1.1 Simple Ordered Query: | ||
| 77 | #set($hql="where doc.creator='XWiki.VincentMassol' order by doc.date asc") | ||
| 78 | #displayQuery($hql false) | ||
| 79 | |||
| 80 | <br/> | ||
| 81 | 1.1.1 Less Simple Ordered Query: | ||
| 82 | #set($hql="where doc.creator='XWiki.VincentMassol' and doc.date > subdate(curdate(), 30) order by doc.date asc") | ||
| 83 | #displayQuery($hql false) | ||
| 84 | |||
| 85 | |||
| 86 | 1.1 Privileged API (search : Documents, Objects, Properties, etc) | ||
| 87 | |||
| 88 | #warning("Calls to te privileged API are only executed when the calling page has been saved by an Admin. \\ | ||
| 89 | The reason is that search can be used to send any HQL command like update, delete, etc. \\ ") | ||
| 90 | |||
| 91 | You can execute queries as follows: | ||
| 92 | {code} | ||
| 93 | #set($query="select doc.name from XWikiDocument doc") | ||
| 94 | #set($results = $xwiki.search($query, 5, 0)) | ||
| 95 | #foreach ($item in $results) | ||
| 96 | * $item <br/> | ||
| 97 | #end | ||
| 98 | {code} | ||
| 99 | |||
| 100 | 1.1.1 Simply Query: | ||
| 101 | #set($hql="select doc.name from XWikiDocument doc") | ||
| 102 | #displayQuery($hql true) | ||
| 103 | |||
| 104 | 1.1.1 Count Query: | ||
| 105 | |||
| 106 | {code} | ||
| 107 | #set($query="select count(doc) from XWikiDocument doc") | ||
| 108 | #set($results = $xwiki.search($query)) | ||
| 109 | ## $xwiki.search returning a list, we get its first element | ||
| 110 | $query result : $results.get(0) | ||
| 111 | {code} | ||
| 112 | |||
| 113 | #set($query="select count(doc) from XWikiDocument doc") | ||
| 114 | #set($results = $xwiki.search($query)) | ||
| 115 | $query results : $results.get(0) | ||
| 116 | |||
| 117 | <br/> | ||
| 118 | 1.1.1 Simply Query with multiple fields: | ||
| 119 | |||
| 120 | {code} | ||
| 121 | #set($results=$xwiki.search("select doc.name, doc.date from XWikiDocument doc", 5, 0)) | ||
| 122 | #foreach ($row in $results) | ||
| 123 | #foreach ($col in $row) | ||
| 124 | #if ($velocityCount==1) | ||
| 125 | #set($docName=$col) | ||
| 126 | #elseif ($velocityCount==2) | ||
| 127 | #set($docDate=$col) | ||
| 128 | #end | ||
| 129 | #end | ||
| 130 | $docName : $docDate <br/> | ||
| 131 | #end | ||
| 132 | {code} | ||
| 133 | |||
| 134 | #set($hql="select doc.name, doc.date from XWikiDocument doc") | ||
| 135 | #displayQuery($hql true) | ||
| 136 | |||
| 137 | <br/> | ||
| 138 | 1.1.1 Getting objects of a specific class | ||
| 139 | #set($hql="select obj.name from BaseObject obj where obj.className='XWiki.XWikiUsers'") | ||
| 140 | #displayQuery($hql true) | ||
| 141 | |||
| 142 | <br/> | ||
| 143 | 1.1.1 Getting objects' properties | ||
| 144 | #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'") | ||
| 145 | #displayQuery($hql true) | ||
| 146 | |||
| 147 | <br/> | ||
| 148 | 1.1.1 Getting objects' properties | ||
| 149 | #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'") | ||
| 150 | #displayQuery($hql true) |
