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:

Install

  1. Put the mybatis-velocity-<version>.jar in your classpath.

    If you are using maven, just add a dependency to:

    • GroupId: org.mybatis.scripting
    • ArtifactId: mybatis-velocity

  2. Register the language driver alias in your mybatis configuration file:

    <configuration>
      ...
      <typeAliases>
        <typeAlias alias="velocity" type="org.mybatis.scripting.velocity.Driver"/>
      </typeAliases>
      ...
    </configuration>
  3. Optional: 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 mapper xml file.

Example:

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

public class MyDirective extends Directive {
  
}

//mybatis-velocity.properties
userdirective=com.myproject.directives.MyDirective,com.myproject.directives.SpecialDirective;

// mapper xml file
SELECT *
FROM City
  #myDirective()
    ......
  #end
        

MyBatis specific syntax

Parameter Bindings

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

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