Using the Velocity variable that references the Velosurf tool in the Velocity context, template writers can access all (or a subset of) the tables of a database, that are called Velosurf entities. Entities properties are attributes (that can be table columns as well as customized queries). Entities methods that trigger database update/delete/insert queries are actions. Each realization of an entity is an instance.

Velosurf uses an XML configuration file that:

All queries are done via a pool of prepared statements automatically handled by Velosurf.

Entities, Instances, Attributes and Actions

What does the syntax look like?

Exampe #1: fetch a single value of the database and display it

Assuming that you've got the following table, called "message", in your database:

id text
1 "Hello World!"
2 "How are you?"

then you can write the following VTL expression:

Or, to be more explicit:

and this would produce:

Example #2: fetch multiple rows of a single column and display them in sequential order

Still with the same table:

and this would produce:

Example #3: update or insert a value

Note: it's not advised to issue updates and inserts from inside templates, since it breaks the MVC paradigm. You should rather achieve this kind of stuff from your controller.

If you are using the velosurf.tool.HttpQueryTool tool to parse HTTP query parameters, you can pass it directly to an insert or an update method:

A simple example

Suppose we've got two joined tables user (id integer primary key, name varchar(50)) and message(id integer primary key autoincrement, userid integer references user(id),txt varchar(255)).

We want to list the messages per user, and to be able to add or delete messages. We'll rely on the Velosurf reverse engineering to define the $db.user and $db.message entities, as well as the $message.user and $user.messages attributes.

First, the toolbox.xml (that references all Velocity tools) defines the needed tools:

<?xml version="1.0"?> <toolbox> <!-- http query parameters --> <tool> <key>query</key> <class>velosurf.web.HttpQueryTool</class> <scope>query</scope> </tool> <!-- database --> <tool> <key>db</key> <class>velosurf.web.VelosurfTool</class> <scope>query</scope> </tool> </toolbox>

Then, the velosurf.xml configuration file, which gives connection parameters. Nothing more to put here than connection parameters, since we use reverse engineering.

<?xml version="1.0" encoding="UTF-8"?> <database user="foo" password="bar" url="jdbc:mysql://" driver="" read-only="false" reverse="full"> <!-- without reverse engineering, we would need the following definitions: <entity name="user"> <rowset name="messages" result="message">select * from message where msg_user=<user_id/></rowset> </entity> <entity name="message"> <row name="user" result="user">select * from user where id=<userid/></row> </entity> Note that when using reverse engineering, you can still list entities here if you want to customize them --> </database>

Now, the template itself:

<body> <h3>Very Simple Message Board</h3> #if ($query.action == "insert") #set( $success = $db.message.insert($query) ) #elseif ($query.action == "delete") #set( $test = $db.message.fetch($ ) #set( $success = $test.delete() ) #end #if( !$success ) Error : $db.error #end <b>Messages for user:</b> <form> <select name="userid"> #foreach($u in $db.user) <option value="$" #if($ == $query.userid)selected#end>$</option> #end </select> <input type="submit" value="Display"> </form> #if($query.userid) #set($user = $db.user.fetch($query.userid)) <ul> #foreach ($message in $user.messages) <li>$message.txt <form> <input type="hidden" name="id" value="$"> <input type="hidden" name="action" value="delete"> <input type="submit" value="Delete"> </form> </li> #end </ul> #end <b>New message:</b> <blockquote> <form> <input type="hidden" name="action" value="insert"> For user: <select name="userid"> #foreach($u in $db.user) <option value="$" #if($ == $query.userid)selected#end>$</option> #end </select><br> Text: <input type="text" name="txt"><br> <input type="submit" value="Insert"> </form> </blockquote> </body> </html>

Of course, there is no data validation, no html escaping, no authentication (each user can see the messages of others), so we'd need some more work towards a real-life message board, but we already have something functional with few lines of code.

The output would be something like this:

Very Simple Message Board

Messages for user:
  • D'oh
  • Huuummmph
New message:
For user: