动态SQL必知必会

1、什么是动态SQL

动态 SQL 是 MyBatis 的强大特性之一。如果你使用过 JDBC 或其它类似的框架，你应该能理解根据不同条件拼接 SQL 语句有多痛苦，例如拼接时要确保不能忘记添加必要的空格，还要注意去掉列表最后一个列名的逗号。利用动态 SQL，可以彻底摆脱这种痛苦。

• if
• choose (when, otherwise)
• trim (where, set)
• foreach

2、为什么使用动态SQL

使用动态SQL可以解决某些功能的实现，例如分页模糊查询的多条件判断、批量删除、处理sql语句的拼接问题等。

3、动态SQL的标签

元素	作用	描述
if	条件判断	单条件判断
choose(when、otherwise)	条件选择	多条件分支判断
where set	条件	处理sql语句的拼接问题
foreach	循环（批量插入、修改、删除）	循环（批量使用）

4、if 标签-单标签判断

使用<where></where> 可以自动消除第一个where条件中的and 且加上where条件。

Mapper.java

User getByCondtion(@Param("name") String name,@Param("phone") String phone);

Mapper.xml

<select id="getByCondtion" resultType="com.pojo.User">select * from user<where><if test="name != null and name!='' ">and name = #{name}</if><if test="phone != null and phone!='' ">and phone = #{phone}</if></where>
</select>

5、choose标签-多条件分支判断

Mapper.java

User getByCondtion(@Param("name") String name,@Param("phone") String phone,@Param("email") String email);

Mapper.xml

<select id="getByCondtion" resultType="com.pojo.User">select * from user<where><choose><when test="name != null and name != ''">and name = #{name}</when><when test="phone != null and phone != ''">and phone = #{phone}</when><otherwise>and email = #{email}</otherwise></choose></where>
</select>

6、set 标签-修改语句

Mapper.java

int updateUser(User user);

Mapper.xml

<update id="updateUser" parameterType="com.pojo.User">update user<set><if test="name != null and name != ''">name = #{name},</if><if test="phone != null and phone != ''">phone = #{phone}</if><if test="email != null and email != ''">email = #{email}</if></set>where id = #{id}
</update>

7、foreach标签

foreach标签适用于批量添加、删除和查询

<foreach collection="集合类型" open="开始的字符" close="结束的字符"item="集合中的成员" separator="集合成员之间的分割符">#{item的值}
</foreach>

foreach标签属性：

• collection：表示循环的对象是数组还是list集合；
  • 如果Mapper接口方法的形参是数组，collection=“array”；
  • 如果Mapper接口方法形参是list，collection=“list”；
• open：循环开始的字符，sql.append(“(”);
• close：循环结束的字符，sql.append(“)”);
• item：集合成员，自定义的变量。Integer item = idList.get(i);
• separator：集合成员之间的分隔符。sql.append(“,”);
• #{item的值}：获取集合成员的值;

7.1 批量查询

Mapper.java

List<User> getByIds(Integer[] ids)

Mapper.xml

<select id="getByIds" resultType="com.pojo.User">select * from user where id in<foreach collection="array" item="id" open="(" close=")" separator=",">#{id}</foreach>
</select>

7.2 批量删除

Mapper.java

int deleteByIds(Integer[] ids);

Mapper.xml

<delete id="deleteByIds">delete from user where id in<foreach collection="array" item="id" open="(" close=")" separator=",">#{id}</foreach>
</delete>

7.3 批量添加

Mapper.java

int addByList(List<User> users);

Mapper.xml

<insert id="addByList">insert into user(name,phone,email) values<foreach collection="list" item="users" separator=",">(#{users.name},#{users.phone},#{users.email})</foreach>
</insert>

8、模糊分页查询

根据角色名称查询角色信息列表（模糊查询），分页查询，根据CreationDate倒序排列

Mapper.java

List<Role> getRoleListByRoleName(@Param("roleName") String rolename,@Param("from") Integer from,@Param("pageSize") Integer pageSize);

Mapper.xml

<select id="getRoleListByRoleName" resultType="cn.smbms.pojo.Role">select * from smbms_role<where><if test="roleName != null and roleName !=''">and roleName like concat('%',#{roleName},'%');</if></where>order by creationDate desc limit #{from}, #{pageSize};
</select>