The tutorial on using Mybatis velocity scripts details the of recommendations

Many children's shoes may not be clear that mybatis can use various scripting languages to define dynamic SQL in Mapper files; The scripting languages currently supported by mybatis are XML (by default); Velocity and Freemarker. Using a different script language to complete Mapper file writing, first familiarize yourself is to use a scripting language, second is the ability to define more abundant custom instruction to simplify Mapper development, principle analysis about MyBatis support script, behind a custom script command to write the analysis, this paper first introduces mybatis velocity script in the way of use.

The mybatis-velocity project allows you to easily use velocity as a scripting language to write various dynamic sql files in Mapper.

Note that velocity development is heavily used in scripts. If you are not familiar with velocity script, you can check it out first.

The installation

Add in maven

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

Note that we are using mybatis-velocity1.2, which requires mybatis3.3 support;

In the mybatis configuration file, set the velocity script engine to the default mapper file engine:

<typeAliases>
... <typeAlias type="org.mybatis.scripting.velocity.Driver" alias="velocity"/>
</typeAliases>
<settings>
...
<setting name="defaultScriptingLanguage" value="velocity"/>
</settings>

Configuration is complete.

Now you can use the velocity script in the mapper file:

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

Note:

If velocity is used, the parameter is referenced with @{}, because velocity's instructions begin with #, such as #set #if, etc.

The velocity script can also be used to configure the corresponding javaType and jdbcType in the parameters. The configuration format is: @{property, attr1=val1, attr2=val2,... }; Configurable items are javaType, jdbcType, mode, numericScale, resultMap, typeHandler, jdbcTypeName;
To use the variable in context in velocity's directive, you need to use $_parameter as the prefix reference, for example

#if($_parameter.name)
#set($_name = '%'+$_parameter.name+'%')
AND name LIKE @{_name}
#end

mybatis-velocity built-in instruction

In addition to the velocity directive, the mybatis-velocity project defines a number of built-in velocity directives for mybatis:

trim

#trim( prefix prefixOverrides suffix suffixOverrides ) body #end

The parameter meaning is the same as XML's trim parameter meaning;

One example:

#trim(" WHERE " " AND| OR")
#if($_parameter.name)
#set($_name = '%'+$_parameter.name+'%')
AND name LIKE @{_name}
#end
#end

where

#where() body #end
#where() with XML In the <where> Same thing, you can replace the one before the condition AND/OR And replaced with the WHERE ; Pay attention to 1 It is \where() Bracketed; 

mset

#mset() body #end
#mset The front and 1 a m In order to velocity In and of itself #set Instruction distinction, #mset Is equivalent to XML In the <set> The element, you can put it before the condition set Statement, and get rid of it set The semicolon at the end of the block; 

One example:

<update id="update">
UPDATE USER 
#mset()
#if($_parameter.name) name=@{name}, #end
#if($_parameter.age) age=@{age}, #end
#if($_parameter.bornDate) borndate=@{bornDate} #end
#end
WHERE id = @{id}
</update>

repeat

#repeat( collection var separator open close ) body #end
#repeat Instructions and XML In the <foreach> The elements are the same, so you can easily traverse the collection / Array type elements and use each of them 1 An element: 

One example:

<typeAliases>
... <typeAlias type="org.mybatis.scripting.velocity.Driver" alias="velocity"/>
</typeAliases>
<settings>
...
<setting name="defaultScriptingLanguage" value="velocity"/>
</settings>

0

in

<typeAliases>
... <typeAlias type="org.mybatis.scripting.velocity.Driver" alias="velocity"/>
</typeAliases>
<settings>
...
<setting name="defaultScriptingLanguage" value="velocity"/>
</settings>

1

#in instruction is a new instruction that can quickly generate field IN() statements specifically for IN conditions in SQL. In the parameter list, collection represents the content of IN to be traversed; var represents the temporary reference name of each object in the traversal; field represents the name of the field generated before the IN statement;

One example:

<typeAliases>
... <typeAlias type="org.mybatis.scripting.velocity.Driver" alias="velocity"/>
</typeAliases>
<settings>
...
<setting name="defaultScriptingLanguage" value="velocity"/>
</settings>

2

Custom instruction

mybatis-velocity allows you to easily customize your own instructions to simplify development by following these steps:

Add one mybatis-velocity.properties profile to classpath;

Create your own Velocity directive resolution class;

Add the Velocity directive resolution class you created to the configuration file.

Use instructions in the mapper.xml file;

One example:

// User defined directivepackage com.myproject.directives;
// Custom instruction classes need inheritance Directive Class; 
public class MyDirective extends Directive { }
//mybatis-velocity.properties
// If you have more than one custom instruction class, separate it with a semicolon. 
userdirective=com.myproject.directives.MyDirective ; 
// mapper xml file
SELECT *FROM City 
#myDirective() ...... #end

Comprehensive use of

If you use velocity-mybatis, a typical CRUD mapper can look like this:

<typeAliases>
... <typeAlias type="org.mybatis.scripting.velocity.Driver" alias="velocity"/>
</typeAliases>
<settings>
...
<setting name="defaultScriptingLanguage" value="velocity"/>
</settings>

4