Cayenne User Documentation
Scripting SQLTemplate

SQLTemplate's internal SQL string is a dynamic script that is processed at runtime to generate PreparedStatement SQL code. Dynamic nature of SQLTemplate makes possible a few important things - it allows to bind parameters on the fly; it provides a way to pass extra information to Cayenne that is not included in the SQL text; it supports including/excluding chunks of SQL depending on runtime parameters.

Scripting of SQL strings is done using Apache Velocity. Velocity was chosen primarily for its concise template language (no XML tags within SQL!) that doesn't conflict with the SQL syntax. When creating dynamic SQL template, all standard Velocity directives are available, including `#set`, `#foreach`, `#if`. However due to the nature of the SQL and the need to integrate it with Cayenne runtime, only a few Cayenne custom directives are normally used. These directives (`#bind`..., `#result`, `#chain`, `#chunk`) are described below.

| Directive Syntax Note Velocity directives start with pound sign (#) and have their parameters separated by space, not comma. E.g. `#bind('SOMESTRING' 'VARCHAR')`.

### Named Parameters

`SQLTemplate.setParameters(java.util.Map)` allows setting a number of named parameters that are used to build parts of the query. During template processing by Velocity all keys in the parameters map are available as variables. For example if the map contains a key "name", its value can be referenced as "$name" in the template. Value of the parameter will be inserted in the SQL unmodified:

##### Named Parameters Example

    // build SQLTemplate
    String sql = "delete from $tableName";
    SQLTemplate delete = new SQLTemplate(Artist.class, sql);
    ...
    // this will create a query "delete from ARTIST"
    delete.setParameters(Collections.singletonMap("tableName", "ARTIST"));
    ...
    // this will create a query "delete from PAINTING"
    delete.setParameters(Collections.singletonMap("tableName", "PAINTING"));

### Describing the Results - #result Directive

`#result` directive is used in selecting SQLTemplates to quickly map an arbitrary ResultSet to a DataObject (or a data row with known keys), and also to control Java types of result values. #result directive has a variable number of arguments:

- `#result(columnName)` - e.g. `#result('ARTIST_NAME')`
- `#result(columnName javaType)` - e.g. `#result('DATE_OF_BIRTH' 'java.util.Date')`
- `#result(columnName javaType columnAlias)` - e.g. `#result('DATE_OF_BIRTH' 'java.util.Date' 'DOB')` - in this case returned data row will use "DOB" instead of "DATE_OF_BIRTH" for the result value.

| Generally "javaType" argument is a fully-qualified Java class name for a given result column. However for simplicity most common Java types used in JDBC can be specified without a package. These include all numeric types, primitives, String, SQL dates, BigDecimal and BigInteger. So "#result('A' 'String')", "#result('B' 'java.lang.String')" and "#result('C' 'int')" are all valid.

While "select * from" queries may work just fine, in many cases it is a good idea to explicitly describe results.

##### #result Examples

    // build selecting SQLTemplate
    String sql = "SELECT"
       + " #result('ARTIST_ID' 'int'),"
       + " #result('ARTIST_NAME' 'String'),"
       + " #result('DATE_OF_BIRTH' 'java.util.Date')"
       + " FROM ARTIST";
    SQLTemplate select = new SQLTemplate(Artist.class, sql);
    ...
    DataContext context...;
    List artists = context.performQuery(select);

Note that it is possible to mix columns described via #result() with regular columns. Columns without an explicit #result() directive will be mapped automatically using JDBC metadata. E.g.:

    // build selecting SQLTemplate
    String sql = "SELECT"
       + " #result('ARTIST_ID' 'long'),"
       + " ARTIST_NAME, DATE_OF_BIRTH FROM ARTIST";

### Binding Parameters - #bind Directive

SQLTemplate uses `#bind` directive to indicate value binding. It has the same meaning as PreparedStatement question mark ("?"), however it also tells Cayenne about the nature of the bound value, so it should be used for all bindings. `#bind()` directive can have a variable number of arguments. The following are the valid invocation formats:

- `#bind(value)` - e.g. `#bind($xyz)` or `#bind('somestring')`
- `#bind(value jdbcTypeName)` - e.g. `#bind($xyz 'VARCHAR')`. Second argument is the name of JDBC type for this binding. Valid JDBC types are defined in java.sql.Types class. This form is the the most common and useful. It is generally preferred to the single argument form, as it explicitly tells what type of JDBC value this binding is.
- `#bind(value jdbcTypeName scale)` - e.g. `#bind($xyz 'DECIMAL' 2)`

##### #bind directive example

    // build SQLTemplate
    String sql = "update ARTIST set ARTIST_NAME = #bind($name) where ARTIST_ID = #bind($id)";
    SQLTemplate update = new SQLTemplate(Artist.class, sql);
    ...
    // set parameters and run it...
    Map parameters = new HashMap();
    parameters.put("name", "Publo Picasso");
    parameters.put("id", new Integer(1001));
    update.setParameters(parameters);

    DataContext context...;
    context.performNonSelectingQuery(update);

SQLTemplate also supports binding Collections for building `IN ( .. )` queries. In any of the `#bind` invocation formats above, you may specify a Collection of values in place of `value`, and Cayenne will automatically expand it.

##### Collection example

    // build SQLTemplate
    String sql = "SELECT ARTIST_ID, ARTIST_NAME FROM ARTIST WHERE ARTIST_NAME IN (#bind($names))
    SQLTemplate select = new SQLTemplate(Artist.class, sql);
    ...
    // set parameters and run it...
    Map parameters = new HashMap();
    parameters.put("names", Arrays.asList(new String[] { "Monet", "Publo Picasso"}));
    select.setParameters(parameters);

    DataContext context...;
    List artists = context.performQuery(select);

### Null Values in Bindings - #bindEqual and #bindNotEqual Directives

Sometimes when a parameter is NULL, SQL code has to be changed. For example, instead of "WHERE COLUMN = ?", PreparedStatement should be rewritten as "WHERE COLUMN IS NULL", and instead of "WHERE COLUMN <> ?" - as "WHERE COLUMN IS NOT NULL". `#bindEqual` and `#bindNotEqual` directives are used to dynamically generate correct SQL string in this case. Their semantics is the same as #bind directive above, except that they do not require "=", "!=" or "<>" in front of them:

- `#bindEqual(value)`, `#bindNotEqual(value)`
- `#bindEqual(value jdbcTypeName)`, `#bindNotEqual(value jdbcTypeName)`
- `#bindEqual(value jdbcTypeName scale)`, `#bindNotEqual(value jdbcTypeName scale)`

##### Null Value Examples

    // build SQLTemplate
    // note that "=" is ommitted for the second binding, since it is a part of the directive
    String sql = "update ARTIST set ARTIST_NAME = #bind($name) where ARTIST_ID #bindEqual($id);
    SQLTemplate update = new SQLTemplate(Artist.class, sql);
    ...
    // set parameters and run it...
    Map parameters = new HashMap();
    parameters.put("name", "Publo Picasso");
    parameters.put("id", new Integer(1001));
    update.setParameters(parameters);

    DataContext context...;

    // after binding processing PrepapredStatement SQL will look like
    // "update ARTIST set ARTIST_NAME = ? where ARTIST_ID = ?"
    context.performNonSelectingQuery(update);

    // build SQLTemplate
    // note that "!=" is omitted for the second binding, since it is a part of the directive
    String sql = "update ARTIST set ARTIST_NAME = #bind($name) where ARTIST_ID #bindNotEqual($id)";
    SQLTemplate update = new SQLTemplate(Artist.class, sql);
    ...
    // set parameters and run it...
    Map parameters = new HashMap();
    parameters.put("name", "Publo Picasso");
    parameters.put("id", null);
    update.setParameters(parameters);

    DataContext context...;

    // after binding processing PrepapredStatement SQL will look like
    // "update ARTIST set ARTIST_NAME = ? where ARTIST_ID IS NOT NULL"
    context.performNonSelectingQuery(update);

### Binding Persistent Object Values - #bindObjectEqual and #bindObjectNotEqual directives

It can be tricky to use a Persistent object (or an ObjectId) in a binding, especially for tables with compound primary keys. There are two directives to help with that - `#bindObjectEqual` and `#bindObjectNotEqual`. Long explicit form of these directives is the following:

- `#bindObjectEqual(object columns idColumns)`
- `#bindObjectNotEqual(object columns idColumns)`

An `"object"` argument can be one of Persistent, ObjectId or Map. `"columns"` and `"idColumns"` can be of type Object[], Collection or Object. What these directives do is build the SQL to match `"columns"` (i.e. the columns from the SQL query) against `"idColumns"` (i.e. the names of the PK columns for a given entity) for a given object. E.g.:

    String sql = "SELECT * FROM PAINTING t0"
                    + " WHERE #bindObjectEqual($a 't0.ARTIST_ID' 'ARTIST_ID' ) ORDER BY PAINTING_ID";
    SQLTemplate select = new SQLTemplate(Artist.class, sql);

    Artist a = ....
    select.setParameters(Collections.singletonMap("a", a));

In case of compound PK, arrays can be used for the last two parameters:

    String sql = "SELECT * FROM TABLE_X t0"
                    + " WHERE #bindObjectEqual($a ['t0.FK1', 't0.FK2'] ['PK1', 'PK2'] )";
    SQLTemplate select = new SQLTemplate(TableX.class, sql);

In the case when SQL columns have the same names as PK columns, and there's no naming conflict that would force to use fully qualified column names, a short form of these directives can be used, where column names are inferred from the ObjectId:

- `#bindObjectEqual(object)`
- `#bindObjectNotEqual(object)`

E.g.:

    String sql = "SELECT * FROM PAINTING WHERE #bindObjectEqual($a) ORDER BY PAINTING_ID";
    SQLTemplate select = new SQLTemplate(Artist.class, sql);

### Building Dynamic SQL - #chain and #chunk Directives

Often it is desirable to exclude parts of the WHERE clause if some parameters are null or not set. This task is not trivial considering the semantics of a SQL statement. Consider this fairly simple example:

    String sql = "SELECT DISTINCT"
       + " #result('ARTIST_ID' 'int'),"
       + " #result('ARTIST_NAME' 'String'),"
       + " #result('DATE_OF_BIRTH' 'java.util.Date')"
       + " FROM ARTIST t0"
       + " WHERE ARTIST_NAME LIKE #bind($name)"
       + " OR ARTIST_ID > #bind($id)";
    SQLTemplate select = new SQLTemplate(Artist.class, sql);

It would be nice to exclude ARTIST_NAME matching if "name" parameter is null, exclude ARTIST_ID matching if "id" is null, and exclude the whole WHERE clause if both are null. `#chain` and `#chunk` directives are used for this purpose. Each logical piece is wrapped in a conditional "chunk", and a number of chunks are grouped in a chain. If chain contains no chunks it doesn't render anything enclosed in it.

    String sql = "SELECT DISTINCT"
       + " #result('ARTIST_ID' 'int'),"
       + " #result('ARTIST_NAME' 'String'),"
       + " #result('DATE_OF_BIRTH' 'java.util.Date')"
       + " FROM ARTIST t0"
       + " #chain('OR' 'WHERE')                              // start chain prefixed by WHERE,
                                                             // and joined by OR
       + " #chunk($name) ARTIST_NAME LIKE #bind($name) #end" // ARTIST_NAME "chunk"
       + " #chunk($id) ARTIST_ID > #bind($id) #end"          // ARTIST_ID "chunk"
       + " #end";                                            // end of chain
    SQLTemplate select = new SQLTemplate(Artist.class, sql);

.
Copyright ©2001-2010 Apache Software Foundation