一、動態(tài)SQL
1核偿、if
if元素用于判斷,一般用作是否應(yīng)該包含某一個查詢條件
<if test="boolean表達(dá)式"></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
相當(dāng)于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,"所在部門"這個要排除,設(shè)為-1-->
? ? ? <!--<if test="deptId > 0">
? ? ? ? ? AND deptId = #{deptId}
? ? ? </if>-->
? ? ? <choose> <!--相當(dāng)于switch判斷-->
? ? ? ? ? <when test="deptId > 0">AND deptId = #{deptId}</when>
? ? ? ? ? <otherwise>AND deptId IS NOT NULL</otherwise>
? ? ? </choose>
? </select>
3颓鲜、where
where元素: 判斷查詢條件是否有WHERE關(guān)鍵字,如果沒有,則在第一個查詢條件之前,插入一個WHERE,如果發(fā)現(xiàn)查詢條件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> <!--相當(dāng)于switch判斷-->
? ? ? ? ? ? ? ? <when test="deptId > 0">AND deptId = #{deptId}</when>
? ? ? ? ? ? ? ? <otherwise>AND deptId IS NOT NULL</otherwise>
? ? ? ? ? ? </choose>
? ? ? ? </where>
? ? </select>
4禾嫉、set
set元素和where元素相似,也能根據(jù)set中的sql動態(tài)的去掉最后的逗號,并在前面添加set關(guān)鍵字,如過沒有內(nèi)容,也會選擇忽略set語句灾杰。
應(yīng)用場景：
need-to-insert-img
因為password沒有設(shè)置值,所以就要采用if來動態(tài)判斷password是否為空,如果為空,則不拼接,但是此時會出現(xiàn)問題,上面拼接的語句最后會存在一個 “,” 。
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的標(biāo)簽
<trim prefix="" prefixOverrides="" suffix="" suffixOverrides="">
<!--trim包含的動態(tài)SQL-->
</trim>
前提如果trim元素包含內(nèi)容返回一個字符串,則
prefix : 在這個字符串之前插入prefix屬性值
prefixOverrides : 字符串內(nèi)容以prefixOverrides中的內(nèi)容開頭(可以包含管道符號|),那么使用prefix屬性值替換內(nèi)容的開頭
suffix : 在這個字符串之后插入suffix屬性值
suffixOverrides : 字符串的內(nèi)容以suffixOverrides中的內(nèi)容結(jié)尾(可以包含管道符號|),那么使用suffix屬性值替換內(nèi)容的結(jié)尾
need-to-insert-img
使用where等價于：
<trim prefix="WHERE" prefixOverrides="AND |OR ">
</trim>
<select id="query3" resultType="Employee">
? ? SELECT * FROM employee
? ? <!--和使用where標(biāo)簽效果一樣-->
? ? <trim prefix="WHERE" prefixOverrides="AND|OR">
? ? ? ? <if test="minSalary!=null">
? ? ? ? ? ? AND salary >= #{minSalary}
? ? ? ? </if>
? ? ? ? <if test="maxSalary!=null">
? ? ? ? ? ? AND salary <= #{maxSalary}
? ? ? ? </if>
? ? ? ? <choose> <!--相當(dāng)于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關(guān)鍵字,如WHERE id IN(10,20,30),此時可以使用${ids}直接拼接SQL ,但是會導(dǎo)致SQL注入問題,要避免SQL注入,只能使用#{}方式,此時就可以配合使用foreach元素了。foreach元素用于迭代一個集合/數(shù)組孽椰， 通常是構(gòu)建在IN運算符條件中昭娩。
foreach元素:
collection屬性:表示對哪一個集合或數(shù)組做迭代
如果參數(shù)是數(shù)組類型,此時Map的key為array;
如果參數(shù)是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
? ? * 當(dāng)參數(shù)是數(shù)組或集合時,一般要加上@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表達(dá)式創(chuàng)建一個變量,并將其綁定在上下文中
<?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:綁定一個對應(yīng)的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>
? ? <!--根據(jù)查詢條件來查詢符合條件的查詢-->
? ? <select id="queryForList" resultType="Employee">
? ? ? ? SELECT * FROM employee
? ? ? ? <include refid="Base_where"></include>
? ? </select>
? ? <!--查詢符合條件的員工數(shù)量-->
? ? <select id="queryForEmpCount" resultType="int">
? ? ? ? SELECT count(*) FROM employee
? ? ? ? <include refid="Base_where"></include>
? ? </select>
</mapper>
