VTL reference
Java reference
Velosurf FAQ

Velosurf Overview

Content of this page:

What is Velosurf?

Velosurf is a java database mapping layer, for the Velocity template engine. It is meant for ease-of-use, genericity and efficiency.

Velosurf main features are:

  • easy VTL grammar
  • SQL queries are gathered in one place and appear as standard VTL properties
  • dynamic mapping: no need to recompile on any database change
  • automatic connection recovery
  • statements and connections pooling
  • automatic reverse engeenering of database schema
  • natural type mapping
  • concurrent accesses
  • transactions
  • soft caching
  • default mapping Java objects, that you can override when needed
  • integration with HTTP parameters: ID obfuscation and 'autofetching'
  • configurable case-handling policy

Velosurf can be used as a standard Velocity tool (check the Velocity Tools subproject, a great architectural component allowing re-usable tools to be easily plugged in Web applications) as well as a generic java database abstraction layer.

Why Velosurf?

The main goal of Velosurf is to spare template writers the pain of rewriting specific database mapping layers in Java for each project involving Velocity and database entities. It is also meant to have a clean separation between SQL, Java and VTL.

Persistence layers are hard to design and maintain, and have been made obsolete by database caching technology. So why not have a thin and generic mapping engine that will fetch values directly from the database when you need them? With Velosurf, object properties can represent, not just column values, but also complex queries, something difficult to do with a persistence framework.

Last but not least: developers often try to protect users from many concepts that may appear too complex or too weird to them. But even if a data model is complex, its complexity has nothing or little to do with technical constraints, and everything to do with logic and modeling. The spirit of Velosurf is that those constraints should be shared and exposed to all people involved in a project - like designers - who should be as competent as developers are (who said more?) to deal with business logic.

In a nutshell, Velosurf allows developers to expose a data model where properties are table fields or SQL queries and designers to easily traverse this data model by means of the 'dot' operator.

Velosurf principles

Velosurf provides an 'out-of-the-box' automatized mapping to database tables and fields, along with the ability to easily define custom entities, queries and actions in SQL. Velosurf also allows the overloading of the mapping Java objects.

Terminology: 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 long as customized queries). Entities methods that trigger database update/delete/insert queries are actions. Each realisation of an entity is an instance.

Velosurf uses an XML configuration file that:

  • contains all necessary parameters to connect to the database.
  • contains the definition of all needed custom SQL entities, attributes and actions.
  • defines the access rights to entities.

All queries are done via a pool of prepared statements automatically handled by Velosurf. Concurrent accesses are taken into account.

Entities, Instances, Attributes and Actions

  • Entities are the objects that map database tables. By default, Velosurf defines an entity for each table. Entities are used to access Instances, either by the mean of #foreach loops or via one of the fetch methods.

  • Instances map rows of a table (or of the result set of a customized query): they are a realization of their corresponding entity. Instances are used to access specific fields, attributes or actions.

  • Attributes belong either to the database itself ('root' attributes) or to specific entities, in which case they can be accessed from the corresponding instances. Attributes have a result type which is one of 'scalar', 'row', or 'rowset'. One can specify the resulting entity of row or rowset attributes, to access nested properties on them. By default, Velosurf defines a scalar attribute for each table column.

  • Actions are like attributes, except their SQL query is a modification query (insert/update/delete), or even a succession of such modification queries. They return the number of affected rows. If present, the root action named 'startup' is executed at startup.

What does the syntax look like?

Task: fetch a single value of the database and display it:

Assuming that you've got the following table in your database:

msg_id int autoincrement primary key
msg_text varchar

and that it contains the value (1,'Hello, World!')

then you can write the following VTL expression:


Or, to be more explicit:

    #set( $hello = $db.message.fetch(1) )

and this would of course produce:

    Hello World!

Task: fetch multiple rows of a single column and display them in sequential order:

Still with the same table:

    #foreach($message in $db.messages)

Task: insert a value in the database:

Assuming that $values is an empty map:

    #set( $values.msg_text = 'How do you do?' )
    #set( $success = $db.message.insert($values) )

    #if ($success)
        New row inserted: id = $db.message.lastInsertID
        Error: $db.error

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. However, please note that you should not modify database values from within templates if you want your Webapp to follow the MVC paradigm.

A simple example

The code in sample.vm allows the user to view the content of the 'my_strings' table (key-value pairs), and to add or remove values. Then, it gives an example of a 1-n join between tables 'user' and 'message'.

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

  • 'db' will be the key to access the database
  • 'query' will be the key to access http parameters

Then, the velosurf.xml configuration file, which gives connection parameters and defines entities, along with an example of a 1-n bidirectionnal join between tables user and message:

Now, the template itself:

which would give something like:

A more complex example is given in the ./samples directory, in the form of a simple bookstore Webapp.

Detailed features

You can start using Velosurf right out-the-box just relying on the reverse-engeenering it provides.

Next, by explicitely declaring entities in velosurf.xml, you can extend its functionnalities:

  • Add attributes to entities in the form of custom SQL queries. They will appear as standard properties from within templates.
  • Decide to map it with your own Java class if you need more complexity here and there. You just have to inherit velosurf.context.Instance and the attributes you've already defined in velosurf.xml will still be valid.

Inside a Veltools Webapp, Velosurf provides the following additionnal features:

  • IDs obfuscation: transparent obfuscation of primary key ids, so that they can appear as HTTP parameters. The idea of this feature is to let Velosurf obfuscate and deobfuscate IDs at a 'low-level' in a transparent way. For this to work, you must make sure you obfuscate all foreign keys relative to the primaty key you are obfuscating (by mean of the obfuscate XML attribute in target entities).
    You can use the $db.obfuscate(value) and $db.deobfuscate(value) methods if you need to manually control obfuscation.

  • Autofetching: This option populates the Velocity context by automatically fetching instances that have their primary key specified as an HTTP parameter. For instance, the clause autofetch='foo_id' in the foo entity can replace

    #if($query.foo_id) #set($foo=$db.foo.fetch($query.foo_id)) #end

    in all the templates that want a foo_id HTTP parameter.
    More complex autofetch clauses include:
    • autofetch='my_foo=foo_id' wich corresponds to #if($query.foo_id) #set($my_foo=$db.foo.fetch($query.foo_id)) #end
    • autofetch='query.my_foo=foo_id' wich corresponds to #if($query.foo_id) #set($query.my_foo=$db.foo.fetch($query.foo_id)) #end

The syntax for those two last features is detailed in the Configuration section. Check also the bookstore sample.

Caching: If an entity in your application is very often fetched, you can decide to cache it (by adding caching="soft" to its declaration). For instance, it could be a good choice if you've got a framed Webapp (which of course one should try to avoid, frames are bad ;o) and that each frame request will have to fetch the current user based on an id stored as a cookie.

Working with several schemas: two options here.

  • the 'light' solution: you can specify tables with schema qualifiers in the config file. That's handy if you've got only few external entities to handle.
  • the 'clean' solution: use one VelosurfTool instance per schema, each with a distinct config file (see the VelosurfTool javadoc for how to do this). You can refer to entities across config files, using schema qualifiers, as long as the instances use the same database login.

Connection pool: Velosurf starts with min-connections initial connections (default is only one connection). New connections are created on the fly (up to max-connections) only when needed (Velosurf tries to reuse existing connections first) and then kept in the pool, which means that Velosurf will adapt itself to the load of the [web-]application.