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

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

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

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

#trim( prefix prefixOverrides suffix suffixOverrides ) body #end

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

#where() body #end
#where()同XML中的<where>相同，可以替换条件前的AND/OR，替换为WHERE；注意一定是\where()有括号的；

#mset() body #end
#mset前面加一个m，为的是和velocity本身的#set指令区别，#mset等同于XML中的<set>元素，可以在条件前加上set语句，并且去掉set块最后的分号；

<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( collection var separator open close ) body #end
#repeat指令和XML中的<foreach>元素相同，能够方便的遍历集合/数组类型元素，并使用其中的每一个元素：

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

#in( collection var field ) body #end

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

// User defined directivepackage com.myproject.directives;
//自定义的指令类需要继承Directive类；
public class MyDirective extends Directive { }
//mybatis-velocity.properties
//如果有多个自定义指令类，使用分号隔开；
userdirective=com.myproject.directives.MyDirective；
// mapper xml file
SELECT *FROM City 
#myDirective() ...... #end

<mapper namespace="mybatis.com._520it.mybatis.UserMapper">
<resultMap type="User" id="user_mapping">
<id column="id" property="id"/>
<result column="name" property="name"/>
<result column="age" property="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 WHERE 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 <include refid="user_column"/> 
FROM USER WHERE name = @{name}
</select>
<select id="queryBy" resultMap="user_mapping">
SELECT id,name,age,borndate
FROM USER
#where()
#if($_parameter.name)
#set($_name = '%'+$_parameter.name+'%')
AND name LIKE @{_name}
#end
#end
#if($_parameter.orderBy)
ORDER BY @{orderBy} @{orderType}
#end
#if($_parameter.pageSize>-1)
LIMIT @{start},@{pageSize}
#end
</select>
</mapper>