一、动态SQL
1、if
if元素用于判断,一般用作是否应该包含某一个查询条件
<if test="boolean表达式"></if>
<!--查询工资大于等于1000的员工-->
<select id="query1" resultType="Employee">
SELECT * FROM employee
<if test="minSalary!=null">
WHERE salary >= #{minSalary}
</if>
</select>
<!--查询工资在1000-2000之间的员工-->
<select id="query2" resultType="Employee">
SELECT * FROM employee WHERE 1 = 1
<if test="minSalary!=null">
AND salary >= #{minSalary}
</if>
<if test="maxSalary!=null">
AND salary <= #{maxSalary};
</if>
</select>
2、choose、when、otherwise
相当于switch
<!--查询指定部门的员工信息-->
<select id="query3" resultType="Employee">
SELECT * FROM employee WHERE 1 = 1
<if test="minSalary!=null">
AND salary >= #{minSalary}
</if>
<if test="maxSalary!=null">
AND salary <= #{maxSalary}
</if>
<!--假如下拉列表获取的部门id,"所在部门"这个要排除,设为-1-->
<!--<if test="deptId > 0">
AND deptId = #{deptId}
</if>-->
<choose> <!--相当于switch判断-->
<when test="deptId > 0">AND deptId = #{deptId}</when>
<otherwise>AND deptId IS NOT NULL</otherwise>
</choose>
</select>
3、where
where元素: 判断查询条件是否有WHERE关键字,如果没有,则在第一个查询条件之前,插入一个WHERE,如果发现查询条件AND 或者 OR开头,也会把第一个查询条件前的AND/OR 替换成WHERE。
这种方式避免了 WHERE 1 = 1的形式
<select id="query3" resultType="Employee">
SELECT * FROM employee
<where>
<if test="minSalary!=null">
AND salary >= #{minSalary}
</if>
<if test="maxSalary!=null">
AND salary <= #{maxSalary}
</if>
<choose> <!--相当于switch判断-->
<when test="deptId > 0">AND deptId = #{deptId}</when>
<otherwise>AND deptId IS NOT NULL</otherwise>
</choose>
</where>
</select>
4、set
set元素和where元素相似,也能根据set中的sql动态的去掉最后的逗号,并在前面添加set关键字,如过没有内容,也会选择忽略set语句。
应用场景：
need-to-insert-img
因为password没有设置值,所以就要采用if来动态判断password是否为空,如果为空,则不拼接,但是此时会出现问题,上面拼接的语句最后会存在一个 “,” 。
need-to-insert-img
若不使用set：update employee name = #{name}, where id = #{id}
<update id="update">
UPDATE employee
<set>
<if test="name!=null">
name = #{name},
</if>
<if test="sn!=null">
sn = #{sn},
</if>
<if test="salary!=null">
salary = #{salary},
</if>
</set>
WHERE id = #{id};
</update>
5、trim
trim是更强大的格式化SQL的标签
<trim prefix="" prefixOverrides="" suffix="" suffixOverrides="">
<!--trim包含的动态SQL-->
</trim>
前提如果trim元素包含内容返回一个字符串,则
prefix : 在这个字符串之前插入prefix属性值
prefixOverrides : 字符串内容以prefixOverrides中的内容开头(可以包含管道符号|),那么使用prefix属性值替换内容的开头
suffix : 在这个字符串之后插入suffix属性值
suffixOverrides : 字符串的内容以suffixOverrides中的内容结尾(可以包含管道符号|),那么使用suffix属性值替换内容的结尾
need-to-insert-img
使用where等价于：
<trim prefix="WHERE" prefixOverrides="AND |OR ">
</trim>
<select id="query3" resultType="Employee">
SELECT * FROM employee
<!--和使用where标签效果一样-->
<trim prefix="WHERE" prefixOverrides="AND|OR">
<if test="minSalary!=null">
AND salary >= #{minSalary}
</if>
<if test="maxSalary!=null">
AND salary <= #{maxSalary}
</if>
<choose> <!--相当于switch判断-->
<when test="deptId > 0">AND deptId = #{deptId}</when>
<otherwise>AND deptId IS NOT NULL</otherwise>
</choose>
</trim>
</select>
注意：此时AND和OR后面有一个空格
使用set等价于：
<trim prefix="WHERE" suffix="" suffixOverrides=",">
</trim>
<trim prefix="SET" suffix="" suffixOverrides=",">
<if test="name!=null">
name = #{name},
</if>
<if test="sn!=null">
sn = #{sn},
</if>
<if test="salary!=null">
salary = #{salary},
</if>
</trim>
6、foreach
SQL中有时候使用IN关键字,如WHERE id IN(10,20,30),此时可以使用${ids}直接拼接SQL ,但是会导致SQL注入问题,要避免SQL注入,只能使用#{}方式,此时就可以配合使用foreach元素了。foreach元素用于迭代一个集合/数组， 通常是构建在IN运算符条件中。
foreach元素:
collection属性:表示对哪一个集合或数组做迭代
如果参数是数组类型,此时Map的key为array;
如果参数是List类型,此时Map的key为list;
open属性:在迭代集合之前,拼接什么符号
close属性:在迭代集合之后,拼接什么符号
separactor属性:在迭代元素时,每一个元素之间使用什么符号分割开来
item属性:被迭代的每一个元素的变量
index属性:迭代的索引
/**
* 使用foreach元素批量删除
* @param ids
* param注解原理还是Map,Map的key
*/
void batchDelete(@Param("ids") Long[] ids);
/**
* 批量插入用户信息
* @param list
* @return
* 当参数是数组或集合时,一般要加上@Param注解,写死
*/
int batchInsert(@Param("emps") List<Employee> emps);
<!--使用foreach元素_完成批量删除-->
<delete id="batchDelete">
DELETE FROM employee WHERE id IN
<foreach collection="ids" open="(" close=")" separator="," item="id">
#{id}
</foreach>
</delete>
<!--使用foreach元素_完成批量插入-->
<insert id="batchInsert">
INSERT INTO employee(id, name, sn, salary, deptId) VALUES
<foreach collection="emps" separator="," item="e">
(#{e.id}, #{e.name}, #{e.sn}, #{e.salary}, #{e.deptId})
</foreach>
</insert>
/**
* 批量删除指定id的员工信息
*/
@Test
public void test5(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
mapper.batchDelete(new Long[]{10L,20L,30L});
sqlSession.commit();
sqlSession.close();
}
/**
* 批量插入员工信息
*/
@Test
public void test6(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
List<Employee> list = new ArrayList<Employee>();
list.add(new Employee(null, "周", "10001", new BigDecimal("5555.00"), 50L));
list.add(new Employee(null, "吴", "10002", new BigDecimal("6666.00"), 60L));
list.add(new Employee(null, "郑", "10003", new BigDecimal("7777.00"), 70L));
int count = mapper.batchInsert(list);
if (count > 0){
System.out.println("成功插入了:"+count+"条用户信息!");
}
sqlSession.commit();
sqlSession.close();
}
7、sql、include、bind
使用sql可以把相同的sql片段起一个名字,并使用include在sql任意位置使用
bind: 使用OGNL表达式创建一个变量,并将其绑定在上下文中
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--命名空间,类似包的概念: namespace:绑定一个对应的Dao/Mapper接口-->
<mapper namespace="com.sunny.dao.EmployeeMapper">
<!--多个查询共同使用的sql-->
<sql id="Base_where">
<where>
<if test="keyword!=null">
<bind name="keywordLike" value="'%' + keyword +'%'"/>
AND (name LIKE #{keywordLike} OR sn LIKE #{keywordLike})
<!--AND (name LIKE concat('%', #{keyword}, '%') OR sn LIKE concat('%', #{keyword}, '%'))-->
</if>
<if test="minSalary!=null">
AND salary >= #{minSalary}
</if>
<if test="maxSalary!=null">
AND salary <= #{maxSalary}
</if>
<if test="deptId!=null">
AND deptId = #{deptId}
</if>
</where>
</sql>
<!--根据查询条件来查询符合条件的查询-->
<select id="queryForList" resultType="Employee">
SELECT * FROM employee
<include refid="Base_where"></include>
</select>
<!--查询符合条件的员工数量-->
<select id="queryForEmpCount" resultType="int">
SELECT count(*) FROM employee
<include refid="Base_where"></include>
</select>
</mapper>
