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".ThevelocitySettings.runtime.custom_directives = com.example.directives.MyDirective # or runtime.custom_directives = com.example.directives.MyDirective
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".
The
# additionalContextAttributes.{name} = {value} # 'additional-context-attributes' (kebab-case) can be used too additionalContextAttributes.likeEscape = com.example.helpers.LikeEscape additionalContextAttributes.masterCacheFacade = com.example.helpers.MasterCacheFacade
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.
The
# velocitySettings.{name} = {value} # 'velocity-settings' (kebab-case) can be used too velocitySettings.resource.default_encoding = Windows-31J
{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