动态SQL是MyBatis的一个强大的特性
动态SQL基于OGNL的表达式，可以使用动态SQL完成多条件查询等逻辑实现
MyBatis通过标签的配合使用，可实现如下功能
语句的动态拼接
前后缀格式处理
复杂参数处理
常用标签
if：利用if实现简单的条件选择
where：简化SQL语句中where的条件判断
choose（when、otherwise）：相当于java中的switch语句，通常与when和otherwise搭配
foreach：迭代一个集合，通常用于in条件
set：解决动态更新语句
trim：可以灵活地去除多余的关键字
if标签
查询出角色id值为2且用户姓名包含"赵"的用户信息
测试方法传入角色id参数为null的时候，为什么检索结果为空？
原因
如何解决？
if标签；动态SQL技术中最常用的标签之一，类似于Java中的if语句
语法
示例
Dao接口//根据用户名称和角色查询用户列表，用户名实现模糊查询 参数是基础数据类型 //实现多条件查询 条件用户名和角色是可选的 List<User> getUserListByNameAndRole1(@Param("xm") String name, @Param("js") Integer role);
Dao接口映射文件
<!--根据用户名称和角色查询用户列表，用户名实现模糊查询 参数是基础数据类型 条件可选-->
<select id="getUserListByNameAndRole1" resultType="user">
SELECT * FROM smbms_user WHERE 1=1 /*写1=1是为了防止 后面没有条件 那么where关键字就成多余的了*/
<if test="xm!=null and xm!=''">
AND userName LIKE CONCAT ('%',#{xm},'%')
</if>
<if test="js!=null">
AND userRole=#{js}
</if>
</select>
测试类
@Test
public void getUserListByNameAndRole1Test(){
SqlSession sqlSession=null;
try {
//创建SqlSession实例
sqlSession= MybatisUtil.createSqlSession();
//创建UserMapper接口实例，调用其方法执行相关的SQL语句
List<User> list = sqlSession.getMapper(UserMapper.class).getUserListByNameAndRole1("赵",null);
for (User user:list){
log.info("编码：" + user.getUserCode()+"\t用户名："+user.getUserName()+"\t角色："+user.getUserRole());
}
} catch (Exception e) {
e.printStackTrace();
}finally {
MybatisUtil.closeSqlSession(sqlSession);
}
}
where标签(if+where)
根据上面的示例，如果1=1不写；当所有的条件为null或者是有条件时，控制台汇报SQL异常错误
原因是
如何解决？
where标签：它自动识别其标签内是否有返回值，若有，就插入一个where。
若该标签返回的内容是以and或or开头，会自动删除
语法
示例
Dao接口映射文件
<!--根据用户名称和角色查询用户列表，用户名实现模糊查询 参数是基础数据类型 条件可选-->
<select id="getUserListByNameAndRole1" resultType="user">
SELECT * FROM smbms_user
<where>
<if test="xm!=null and xm!=''">
AND userName LIKE CONCAT ('%',#{xm},'%')
</if>
<if test="js!=null">
AND userRole=#{js}
</if>
</where>
</select>
choose(when、otherwise)标签
是一个组合标签，通常与when、otherwise标签配合使用
类似于Java中switch语句，当when有条件满足的时候，就跳出choose
示例
Dao接口
//查询用户列表choose(when、otherwise) 条件可选
List<User> getUserList_choose(@Param("userName")String userName,
@Param("userRole")Integer roleId,
@Param("userCode")String userCode,
@Param("creationDate") Date creationDete);
Dao接口映射文件
<!--查询用户列表choose(when、otherwise) 条件可选-->
<select id="getUserList_choose" resultType="user">
SELECT * FROM smbms_user WHERE 1=1/*SQL语句中加入where 1=1的原因是我们不需要再去处理多余的“and”*/
<choose>
<when test="userName!=null and userName!=''">
and userName like CONCAT ('%',#{userName},'%')
</when>
<when test="userCode != null and userCode != ''">
and userCode like CONCAT ('%',#{userCode},'%')
</when>
<when test="userRole != null">
and userRole=#{userRole}
</when>
<otherwise>
<!-- and YEAR(creationDate) = YEAR(NOW()) -->
and YEAR(creationDate) = YEAR(#{creationDate})
</otherwise>
</choose>
</select>
测试类
//查询用户列表choose(when、otherwise) 条件可选
@Test
public void getUserList_chooseTest(){
SqlSession sqlSession=null;
try {
String userName = "";
Integer roleId = null;
String userCode = "";
Date creationDate = new SimpleDateFormat("yyyy-MM-dd").parse("2016-08-08");
//创建SqlSession实例
sqlSession= MybatisUtil.createSqlSession();
//创建UserMapper接口实例，调用其方法执行相关的SQL语句
List<User> list = sqlSession.getMapper(UserMapper.class).getUserList_choose(userName, roleId, userCode, creationDate);
for (User user:list){
log.info("id：" + user.getId()+"\tuserCode："+user.getUserCode()+
"\tuserName："+user.getUserName()+"\tuserRole："+user.getUserRole()+
"\tcreationDate："+new SimpleDateFormat("yyyy-MM-dd").format(user.getCreationDate()));
}
} catch (Exception e) {
e.printStackTrace();
}finally {
MybatisUtil.closeSqlSession(sqlSession);
}
}
使用foreach完成复杂查询
属性
item：表示集合中每一个元素进行迭代时的别名
index：指定一个名称，用于表示在迭代过程中，每次迭代到的位置
collection：必须指定
list：入参为单参数且参数类型是一个List
array：入参为单参数且参数类型是一个数组
map-key：传入的参数为多参数，就需要把他们封装为一个Map进行处理
open：表示语句以什么开始（既然是in条件语句，所以必然以“（”开始）
separator [ˈsepəreɪtər]：表示在每次进行迭代之间以什么符号作为分隔符（既然是in条件语句，所以必然是以“，”作为分隔符）
close：表示该语句以什么结束（既然是in条件语句，所以必然以“）”结束）
无论入参是何种数据类型，MyBatis都会将其放入一个Map
迭代一个集合，通常用于in语句来处理多个值的参数
foreach——入参为数组
Dao接口
//根据用户角色列表，获取该角色列表下用户列表信息foreach-array
List<User> getUserByRoleId_foreach_array(Integer[] jsid);
Dao接口映射文件
<!--根据用户角色列表，获取该角色列表下用户列表信息foreach-array-->
<select id="getUserByRoleId_foreach_array" resultType="user">
SELECT * FROM smbms_user WHERE userRole IN
<foreach collection="array" item="jsid" open="(" separator="," close=")">
#{jsid}
</foreach>
</select>
Dao接口映射文件
<!--根据用户角色列表，获取该角色列表下用户列表信息foreach-list-->
<select id="getUserByRoleId_foreach_list" resultType="user">
SELECT * FROM smbms_user WHERE userRole IN
<foreach collection="list" item="jsList" open="(" separator="," close=")">
#{jsList}
</foreach>
</select>
foreach——入参为集合Map-key
Dao接口
//根据用户角色列表，获取该角色列表下用户列表信息foreach-map(单参数封装成Map)
public List<User> getUserByRoleId_foreach_map1(Map<String,Object> roleMap);
//根据用户角色和性别(多参数)，获取该角色列表下指定性别的用户列表信息foreach-map
public List<User> getUserByRoleId_foreach_map2(Map<String,Object> conditionMap);
Dao接口映射文件
<!-- 根据用户角色和性别(多参数)，获取该角色列表下指定性别的用户列表信息foreach-map -->
<select id="getUserByRoleId_foreach_map" resultMap="userRole">
SELECT * FROM smbms_user WHERE gender = #{gender} AND userRole IN
<foreach collection="roleIdsMapKey" item="roleMap" open="(" separator="," close=")">
#{roleMap}
</foreach>
</select>
<!-- 根据用户角色列表，获取该角色列表下用户列表信息foreach-map(单参数封装成Map) -->
<select id="getUserByRoleId_foreach_map1" resultMap="userRole">
SELECT * FROM smbms_user WHERE userRole IN
<foreach collection="rKey" item="roleMap" open="(" separator="," close=")">
#{roleMap}
</foreach>
</select>
测试类
//根据用户角色列表，获取该角色列表下用户列表信息foreach-map(单参数封装成Map)
@Test
public void getUserByRoleId_foreach_map1Test(){
SqlSession sqlSession=null;
List<Integer> roleList =new ArrayList<Integer>();
roleList.add(2);
roleList.add(3);
Map<String,Object> map=new HashMap<String, Object>();
map.put("jsid", roleList);
try {
//创建SqlSession实例
sqlSession= MybatisUtil.createSqlSession();
//创建UserMapper接口实例，调用其方法执行相关的SQL语句
List<User> list = sqlSession.getMapper(UserMapper.class).getUserByRoleId_foreach_map1(map);
for (User user:list){
log.info("id：" + user.getId()+"\tuserCode："+user.getUserCode()+
"\tuserName："+user.getUserName()+"\tuserRole："+user.getUserRole()+"\tgender："+user.getGender());
}
} catch (Exception e) {
e.printStackTrace();
}finally {
MybatisUtil.closeSqlSession(sqlSession);
}
}
//根据用户角色和性别(多参数)，获取该角色列表下指定性别的用户列表信息foreach-map
@Test
public void getUserByRoleId_foreach_map2Test(){
SqlSession sqlSession=null;
List<Integer> roleList =new ArrayList<Integer>();
roleList.add(2);
roleList.add(3);
Map<String,Object> map=new HashMap<String, Object>();
map.put("xb", 1);
map.put("jsid", roleList);
try {
//创建SqlSession实例
sqlSession= MybatisUtil.createSqlSession();
//创建UserMapper接口实例，调用其方法执行相关的SQL语句
List<User> list = sqlSession.getMapper(UserMapper.class).getUserByRoleId_foreach_map2(map);
for (User user:list){
log.info("id：" + user.getId()+"\tuserCode："+user.getUserCode()+
"\tuserName："+user.getUserName()+"\tuserRole："+user.getUserRole()+"\tgender："+user.getGender());
}
} catch (Exception e) {
e.printStackTrace();
}finally {
MybatisUtil.closeSqlSession(sqlSession);
}
}
set标签(if+set)
更新用户id为16的用户的真实姓名，其他用户属性保持不变
发现
更新用户表数据时，除重新赋值的属性外，其他属性的值都更新为null
原因
分析SQL语句
如何解决？set标签、if标签
set元素主要用于更新，它的功能和where元素差不多，主要是在包含的语句前输出一个set，若包含的语句以逗号结束，会自动的把该逗号忽略掉
Dao接口
//修改用户信息
int update(User user);
Dao接口映射文件
<!--修改用户信息-->
<update id="update" parameterType="user">
UPDATE smbms_user
<set>
<if test="userCode!=null">userCode=#{userCode},</if>
<if test="userName!=null">userName=#{userName},</if>
<if test="userPassword!=null">userPassword=#{userPassword},</if>
<if test="gender!=null">gender=#{gender},</if>
<if test="birthday!=null">birthday=#{birthday},</if>
<if test="phone!=null">phone=#{phone},</if>
<if test="address!=null">address=#{address},</if>
<if test="userRole!=null">userRole=#{userRole},</if>
<if test="modifyBy!=null">modifyBy=#{modifyBy},</if>
<if test="modifyDate!=null">modifyDate=#{modifyDate}</if>
</set>
WHERE id=#{id}
</update>
trim标签(if+trim)
trim元素也会自动识别其标签内是否有返回值，若有返回值，会在自己包含的内容前加上某些前缀，也可在其后加上某些后缀，与之对应的属性是prefix和suffix；也可把包含内容的首部某些内容覆盖（即忽略），或者把尾部的某些内容覆盖，与之对应的属性是prefixOverrides和suffixOverrides；
prefix [ˈpriːfɪks]：前缀，作用是通过自动识别是否有返回值，在trim包含的内容上加上前缀
suffix [ˈsʌfɪks]：后缀，作用是在trim包含的内容上加上后缀
prefixOverrides：对于trim包含内容的首部进行指定内容的忽略
suffixOverrides [ˌoʊvərˈraɪdz]：对于trim包含内容的尾部进行指定内容的忽略
Dao接口
//实现多条件查询 条件用户名和角色是可选的
List<User> getUserListByNameAndRole1(@Param("xm") String name, @Param("js") Integer role);
//修改用户信息
int update(User user);
Dao接口
//实现多条件查询 条件用户名和角色是可选的
List<User> getUserListByNameAndRole1(@Param("xm") String name, @Param("js") Integer role);
//修改用户信息
int update(User user);
Dao接口映射文件
<!--实现多条件查询 条件用户名和角色是可选的-->
<select id="getUserListByNameAndRole1" resultType="user">
SELECT * FROM smbms_user
<trim prefix="where" prefixOverrides="and|or">
<if test="xm!=null and xm!=''">
AND userName LIKE CONCAT ('%',#{xm},'%')
</if>
<if test="js!=null">
AND userRole=#{js}
</if>
</trim>
</select>
<!--修改用户信息-->
<update id="update" parameterType="user">
UPDATE smbms_user
<trim prefix="set" suffix="WHERE id=#{id}" suffixOverrides=",">
<if test="userCode!=null">userCode=#{userCode},</if>
<if test="userName!=null">userName=#{userName},</if>
<if test="userPassword!=null">userPassword=#{userPassword},</if>
<if test="gender!=null">gender=#{gender},</if>
<if test="birthday!=null">birthday=#{birthday},</if>
<if test="phone!=null">phone=#{phone},</if>
<if test="address!=null">address=#{address},</if>
<if test="userRole!=null">userRole=#{userRole},</if>
<if test="modifyBy!=null">modifyBy=#{modifyBy},</if>
<if test="modifyDate!=null">modifyDate=#{modifyDate}</if>
</trim>
</update>
MyBatis分页功能实现
mysql实现分页：limit(起始位置，页面容量)
起始位置下标=（页码-1）*页面容量
Dao接口
/**
* 查询用户列表 （分页显示）
* 根据用户名称和角色id查询
* @param userName 用户名称
* @param roleId 用户id
* @param currentPageNo 当前页码的起始索引
* @param pageSize 页面容量
* @return
*/
List<User> getUserListPage(@Param("userName")String userName,
@Param("userRole")Integer roleId,
@Param("from")Integer currentPageNo,
@Param("pageSize")Integer pageSize);
Dao接口映射文件
<!-- 查询用户列表 （分页显示）-->
<select id="getUserListPage" resultType="user">
SELECT * FROM smbms_user
<trim prefix="where" prefixOverrides="and|or">
1=1
<if test="userRole != null">
AND userRole = #{userRole}
</if>
<if test="userName != null and userName != ''">
AND userName like CONCAT ('%',#{userName},'%')
</if>
ORDER BY creationDate DESC limit #{from},#{pageSize}
</trim>
</select>
Mybatis中#和$的区别
mybatis中"#"和"$"的区别 - 三生有幸呵呵哒 - 博客园
MyBatis中#{}和${}区别_坦GA的博客-CSDN博客
#{}的解析发生在dbms（关系型数据库）中，${}的解析发生在java中。
所以#{}在java中参数使用？占位，在dbms中处理参数
${}直接在Java程序中进行简单的字符串替换，容易导致sql注入。
所以尽量使用#{}
MyBatis/Ibatis中#和$的区别
1. #将传入的数据都当成一个字符串，会对自动传入的数据加一个双引号。如：order by #user_id#，如果传入的值是111,那么解析成sql时的值为order by "111", 如果传入的值是id，则解析成的sql为order by "id".
2. $将传入的数据直接显示生成在sql中。如：order by $user_id$，如果传入的值是111,那么解析成sql时的值为user_idorder by , 如果传入的值是id，则解析成的sql为order by user_id.
3. #方式能够很大程度防止sql注入。
4.$方式无法防止Sql注入。
5.$方式一般用于传入数据库对象，例如传入表名.
6.一般能用#的就别用$.