5

I am looking at various libraries that can be used as a templating engine in my project and right now Apache Velocity looks like a good candidate. I have the following doubts regarding its usage:

Is it possible to specify a SQL database query in a template and use the querys' return value to fill a parameter?. I found the following example in the Velocity user guide:

Hello,

#set( $result = $query.criteria("name") )
Your username is $result.

However the guide does not explain much about executing SQL queries. Is it possible to define a SQL SELECT query which returns a value and assign this value to a variable in the template? I am wondering if something like the example below is possible?

Hello,

#set( $result = $executeQuery("SELECT name FROM user") )
Your username is $result.

Would be grateful if you could shed some light on this. Anyone kind enough to provide an example, or point me to a location where I can find additional documentation on this?

Ramishka Dasanayaka
  • 330
  • 1
  • 3
  • 8

3 Answers3

3

I would recommend something like http://velosurf.sourceforge.net/ instead of directly embedding queries.

Nathan Bubna
  • 6,823
  • 2
  • 35
  • 36
2

Velocity is a very lightweight templating engine, it has very little functionality by itself. It works by interpolating variables defined (by you) in a context into a template file. By default, the context is empty, meaning that there are no variables for the template to use. Velocity doesn't allow instantiating new objects of custom classes, all it allows you to use is string and number literals, plus maps and lists:

#set ($n = 5)
#set ($s = 'Hello New World!')
#set ($m = {'four' : 4, 'five' : $n})
#set ($a = ['x', 'y', 'z'])

You're responsible for making Velocity "smart" by populating the context with useful objects before interpolating. As a starting point, you can use some of the Velocity Tools. You can add your own tools, like an SQL tool that lets you run queries. Or you can use a higher level framework that uses velocity and which already offers a rich set of tools available in templates.

So, to answer your question, you can execute any SQL statement you want, as long as you add in the context an object that can execute such statements. You should read more about how to properly use Velocity in their developer guide.

Sergiu Dumitriu
  • 11,455
  • 3
  • 39
  • 62
0

This following is for HQL (you can try with others using this as example)

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

Additonal Info

you may want to look at this link

TheWhiteRabbit
  • 15,480
  • 4
  • 33
  • 57
  • Thanks for your answer. Any idea if I can execute an SQL query here? If possible, where should I configure the database credentials and connector information? I know I can fetch templates from the database using the DataSourceResourceLoader. But is it possible to execute SQL queries and get values inside the template? Thanks in advance. – Ramishka Dasanayaka Jan 17 '13 at 08:40
  • This is tied to XWiki, standalone Velocity by default doesn't have the `$xwiki` object. – Sergiu Dumitriu Jan 18 '13 at 18:06