MyBatis Velocity Script Use tutorial detailed (recommended) _java

Maybe a lot of children's shoes are still not very clear. In MyBatis you can use a variety of scripting languages to define dynamic SQL in a mapper file; Currently, MyBatis supports scripting languages with XML (default), Velocity and freemarker three. Use a different scripting language to complete the mapper file, the first is to use their familiar scripting language, the second is to be able to define more rich custom instructions to simplify the development of mapper, about mybatis support the principle of scripting, custom script instructions to write after the article analysis, This article first describes how velocity scripts are used in MyBatis.

The Mybatis-velocity project allows you to easily use velocity as a scripting language to complete the writing of various dynamic SQL in mapper files.

Note that velocity development is heavily used in scripts, and if you are unfamiliar, you can first look at velocity scripts;

Installation

adding in Maven

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

Note that we are using a mybatis-velocity1.2 version, 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.

The velocity script can then be used in the Mapper file:

<select id= "Findperson" lang= "velocity" > 
#set ($pattern = $_parameter.name + '% ') 
select * FROM person wher E name like @{pattern, Jdbctype=varchar}
</select>

Attention:

If velocity is used, the parameter is referenced using @{}, because velocity's instruction starts with a #, such as #set #if等;

With velocity scripts, the corresponding Javatype and Jdbctype can also be configured in the parameters; @{property, Attr1=val1, Attr2=val2, ...} Configurable items are Javatype, Jdbctype, mode, NumericScale, Resultmap, Typehandler, Jdbctypename;
Using variables in the context in the velocity instruction requires using $_parameter as a prefix reference, such as

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

Mybatis-velocity-built Instructions

In addition to the velocity instructions, the Mybatis-velocity project defines some built-in velocity instructions for MyBatis:

Trim

#trim (prefix prefixoverrides suffix suffixoverrides) body #end

The meaning of the parameter is the same as that of the trim parameter of XML;

An example:

#trim ("WHERE" "and| or ")
#if ($_parameter.name)
#set ($_name = '% ' +$_parameter.name+ '% ') and
name like @{_name}
#end
#end

where

#where () Body #end
#where () is the same as <where> in XML, you can replace the and/or before the condition, and replace with where; note must be \where () with parentheses;

Mset

The #mset () body #end #mset前面加一个m in order to
distinguish it from the #set instruction of velocity itself, #mset等同于XML中的 <set> element, you can precede the condition with the SET statement, and remove the last semicolon from the set block ；

An 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) The body #end The
same #repeat指令和XML中的 <foreach> elements to easily traverse the collection/array type element. and use each of these elements:

An example:

SELECT *from City
#where () 
#repeat ($_parameter.ids $id "," "state_id in (") ") 
@{id} 
#end
#e nd

In

#in (collection var field) Body #end

#in指令是一个新的指令, you can quickly generate the corresponding field in () statement specifically for the in condition in the SQL, and in the argument list, collection represents the content in the in that you want to traverse; var represents the temporary reference name of each object in the traversal The field represents the names of the fields that were generated before the in statement;

An example:

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

Custom directives

Mybatis-velocity allows you to easily customize your own instructions to simplify development, and the steps for customizing the instructions are:

Add a mybatis-velocity.properties configuration file to the Classpath;

Create your own velocity instruction parsing class;

Add the velocity instruction resolution class that you created to the configuration file;

The use of instructions in mapper.xml files;

An example:

User defined directivepackage com.myproject.directives;
Custom instruction classes need to inherit the directive class; public
class Mydirective extends directive {}
//mybatis-velocity.properties
// If you have more than one custom instruction class, use a semicolon to separate it;
userdirective=com.myproject.directives.mydirective
//Mapper XML file
SELECT * From the city 
#myDirective () ... #end

Comprehensive use

If you use Velocity-mybatis, a typical crud mapper can look like this:

<mapper namespace= "Mybatis.com._520it.mybatis.usermapper" > <resultmap type= "User" id= "user_mapping" > <id column= "id" property= "id"/> <result column= "name" property= "name"/> <result "age" column= " Age "/> <result column= borndate" property= "borndate"/> </resultMap> <insert id= "Add" keycolumn= "id" keyproperty= "id" usegeneratedkeys= "true" > INSERT into USER (name,age,borndate) VALUES (@{name,javatype=string, Jdbctype=varchar},@{age},@{borndate}) </insert> <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> <delete id= "Delete" parametertype= "long" > Delete from USER wher E id = @{id} </delete> <sql id= "User_column" > Id,name,age,borndate </sql> <select id= "Get" resultmap = "User_mapping" > SELECT <include refid= "User_column"/> from user WHERE id = @{id} </select> <select id= "list" resultmap= "user_mapping" > select <include refid= "User_column"/ > from USER </select> <select id= "Listbyname" resultmap= "user_mapping" parametertype= "string" > select & Lt;include refid= "User_column"/> from user WHERE name = @{name} </select> <select id= ' queryby ' resultmap= ' use R_mapping "> SELECT id,name,age,borndate from USER #where () #if ($_parameter.name) #set ($_name = '% ' +$_parameter.name+ '% ') and name like @{_name} #end the #end #if ($_parameter.orderby) Order by @{orderby} @{ordertype} #end #if ($_parameter.pages ize>-1) LIMIT @{start},@{pagesize} #end </select> </mapper>

The above is a small set of mybatis velocity script to introduce the use of the tutorial detailed (recommended), I hope to help you, if you have any questions please give me a message, small series will promptly reply to everyone. Here also thank you very much for the cloud Habitat Community website support!