Introduction

mybatis-velocity is an extension that allows you to use the Apache Velocity scripting language to generate your dynamic SQL queries on the fly.

If you are not familiar with apache velocity, you can learn it from its documentation site:

Requirments

  • Java : Java 8+
  • MyBatis : 3.5+
  • Velocity : 2.1+

Install

If you are using maven, you can add this:

<dependency>
    <groupId>org.mybatis.scripting</groupId>
    <artifactId>mybatis-velocity</artifactId>
    <version>2.1.0</version>
</dependency>

If you are using gradle, you can use this snippet:

dependencies {
    compile("org.mybatis.scripting:mybatis-velocity:2.1.0")
}

Configuration

Common

By default, the mybatis-velocity.properties file create in your classpath.

  • You can specify user defined custom directives to use on the Velocity template engine using the Velocity standard property(runtime.custom_directives). For details see the "User Defined Directives".
    velocitySettings.runtime.custom_directives = com.example.directives.MyDirective
    # or
    runtime.custom_directives = com.example.directives.MyDirective
    
    The userdirective property supported for keeping backward compatibility with old versions(2.0 or under).
    userdirective = com.example.directives.MyDirective
    
  • You can specify user defined additional context attribute values that passed to template engine. For details see the "Additional Context Attributes".
    # additionalContextAttributes.{name} = {value}
    # 'additional-context-attributes' (kebab-case) can be used too
    additionalContextAttributes.likeEscape = com.example.helpers.LikeEscape
    additionalContextAttributes.masterCacheFacade = com.example.helpers.MasterCacheFacade
    
    The additional.context.attributes property supported for keeping backward compatibility with old versions(2.0 or under).
    # Value format: {name}:{value}(,...)
    additional.context.attributes = likeEscape:com.example.helpers.LikeEscape,masterCacheFacade:com.example.helpers.MasterCacheFacade
    
  • You can configure the Velocity configuration as follow: About available setting name, please refer to the reference documentation of Velocity.
    # velocitySettings.{name} = {value}
    # 'velocity-settings' (kebab-case) can be used too
    velocitySettings.resource.default_encoding = Windows-31J
    
    The {name} = {value} format(without prefixed 'velocitySettings') supported for keeping backward compatibility with old versions(2.0 or under).
    # This setting is same with 'velocitySettings.resource.default_encoding'
    resource.default_encoding = Windows-31J
    
  • Since 2.1.0, you can use an any properties file or encoding as follow:
    $ java -Dmybatis-velocity.config.file=mybatis-velocity_production.properties -Dmybatis-velocity.config.encoding=Windows-31J ...
    

Scripting Language Driver

You may need to do next steps:

  • Register the language driver alias in your mybatis configuration file:
    <configuration>
      ...
      <typeAliases>
        <typeAlias alias="velocity" type="org.mybatis.scripting.velocity.VelocityLanguageDriver"/>
      </typeAliases>
      ...
    </configuration>
  • Set the velocity as your default scripting language:
    <configuration>
      ...
      <settings>
          <setting name="defaultScriptingLanguage" value="velocity"/>
      </settings>
      ...
    </configuration>

Usage

Just write your dynamic queries and use velocity:

Example:

<select id="findPerson" lang="velocity">
  #set( $pattern = $_parameter.name + '%' )
  SELECT *
  FROM person
  WHERE name LIKE @{pattern, jdbcType=VARCHAR}
</select>
      

Note:

  • #{...} syntax is replaced by @{...} syntax to avoid collisions with VTL

Custom directives

trim

#trim( prefix prefixOverrides suffix suffixOverrides ) body #end
Arg Description Type Default Example
prefix Text to prepend if the body is not empty String Empty String "WHERE "
prefixOverrides Text to be removed from the body if it is at the beginning String list delimited by | Empty String "AND |OR "
suffix Text to appended if the body is not empty String Empty String "] "
suffixOverrides Text to be removed from the body if it is at the end String list delimited by | Empty String ","

where

#where() body #end

Removes any AND or OR from the beginning, then if the result is not empty, prepends WHERE at the beginning

mset

#mset() body #end

Removes any "," from the end, then if the result is not empty, prepends SET at the beginning

repeat

#repeat( collection var separator open close ) body #end

Same as #foreach, but with special features:

  • Support for iterative parameter bindings via @{...} syntax
  • Automatic separator and open/close string management
Arg Description Type Default Example
collection List or array to iterate List or Array Empty List $_parameter.selectedIds
var Variable to be used in the loop Reference $item
separator String to be inserted between iterations String Empty String ","
open String to be prepended at the beginning String Empty String "("
close String to be appended at the end String Empty String ")"

Example:

SELECT *
FROM City
#where()
  #repeat( $_parameter.ids $id "," " state_id IN (" ")" )
    @{id}
  #end
#end
        

in

#in( collection var field ) body #end

Generates a SQL IN clause and breaks it into groups of 1000 items seperated by an OR clause to get around database limitations on the maximum number of items that can be handled. It also supports iterative parameter bindings via @{...} syntax.

Arg Description Type Default Example
collection List or array to iterate List or Array Empty List $_parameter.selectedIds
var Variable to be used in the loop Reference $item
field Field name to be inserted before the IN clause String Empty String "state_id"

Example:

SELECT *
FROM City
#where()
  #in( $_parameter.ids $id "state_id" )
    @{id}
  #end
#end
        

User Defined Directives

mybatis-velocity allows you to write your own velocity directive:

  • Create a property file named mybatis-velocity.properties and put in on the classpath.
  • Create your own velocity directive.
  • Add the directive to the property file.
  • Use the directive in the sql template.

Example:

// User defined directive
package com.example.directives;

public class MyDirective extends Directive {
  // ...
}
# mybatis-velocity.properties
userdirective=com.example.directives.MyDirective,com.example.directives.SpecialDirective
<!-- sql template -->
SELECT *
FROM City
  #myDirective()
    ......
  #end

Additional Context Attributes

The mybatis-velocity allows you to pass any attribute(instantiate using specified FQCN) to the Velocity template engine.

  • Create a property file named mybatis-velocity.properties and put in on the classpath.
  • Create any thread safe class.
  • Add the class to the property file.
  • Use object that instantiated by mybatis-velocity in your sql template.

Example:

// User defined directive
package com.example.helpers;

public class LikeEscape {
  public String escape(String val) {
    // ...
    return escapedVal;
  }
}
# mybatis-velocity.properties
additionalContextAttributes.likeEscape = com.example.helpers.LikeEscape
<!-- sql template -->
#set( $pattern = $likeEscape.escape($_parameter.name) + "%" )
SELECT * FROM User
  WHERE name LIKE @{pattern, javaType=string}

MyBatis specific syntax

Parameter Bindings

@{ property, attr1=val1, attr2=val2, ... }

Where attrs must be any of: javaType, jdbcType, mode, numericScale, resultMap, typeHandler, jdbcTypeName