動態拼接SQL語句

標籤：update   測試   res   com   字元   查詢   logs   變數   add   

1.參考官方文檔
? if：字元判斷
? choose (when, otherwise):分支選擇
? trim (where, set)：字串截取；其中where標籤封裝查詢條件，set標籤封裝修改條件
? foreach

2.if案例

1)在EmployeeMapper介面中添加一個方法：
//攜帶了哪個欄位，查詢條件就帶上哪個欄位的值
public List<Employee> getEmployeeByConditionIf(Employee employee);

2).如果要寫下列的SQL語句，只要是不為空白，就作為查詢條件，如下所示，這樣寫實際上是有問題的，所以我們要寫成動態SQL語句：
<select id="getEmployeeByConditionIf" resultType="com.neuedu.entity.Employee">
select *from tbl_employee where id = #{id} and user_name = #{userName} and email = #{email} and gender = #{gender}
</select>

3)用if標籤改寫為動態SQL,如下所示：

<select id="getEmployeeByConditionIf" resultType="com.neuedu.entity.Employee">select *from tbl_employee where  <!--test:判斷運算式(OGNL)OGNL參照PPT或者官方文檔。c:if test從參數中取值進行判斷遇見特殊符號，應該去寫逸出字元：參考W3CSchool>>HTML>>ISO8859  -->  <if test="id != null">id = #{id}  </if>  <if test="userName != null && userName !=‘‘">and user_name = #{userName}   </if>  <if test="email != null and email.trim() != """>and email = #{email}  </if>  <!-- ognl會進行字串和數位轉換判斷；"0"==0,"1"==1 -->  <if test="gender == 0 or gender == 1">and gender = #{gender}   </if></select>

　　 4).測試代碼：

@Testpublic void testGetEmployee(){EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);Employee employee = new Employee();employee.setId(1);employee.setUserName("張三丰");employee.setEmail("[email protected]");employee.setGender(1); List<Employee> list = mapper.getEmployeeByConditionIf(employee);System.out.println(list);}

　　

但是仔細來說，上面的sql語句是有問題的，當我們不給動態sql語句傳遞id值的時候，sql語句的拼裝就會有問題！
解決辦法：
       1>.給where後面加上1=1，以後的條件都可以使用and xxx了
       2>.mybatis可以使用where標籤來將所有的查詢條件包括在內。mybatis就會將where標籤中拼裝的sql，
         多出來的and或者or去掉!//需要注意：where標籤只會去掉第一個多出來的and或者or
      3.>也就是說使用where標籤有時候還是不能解決問題的，那怎麼辦呢？我們這裡可以使用trim標籤！

2.trim標籤：可以自訂字串的截取規則

<select id="getEmployeeByConditionIf" resultType="com.neuedu.entity.Employee">select *from tbl_employee<!-- 後面多出的and或者or where標籤不能夠解決   prefix="":首碼：trim標籤體重是整個字串拼串後的結果。 prefix給拼串後的整個字串加一個首碼   prefixOverrides="":首碼覆蓋：去掉整個字串前面多餘的字元   suffix="":尾碼 suffix給拼串後的整個字串加一個尾碼   suffixOverrides="": 尾碼覆蓋：去掉整個字串後面多餘的字元 --> <trim prefix="where" suffixOverrides="and">  <if test="id != null">id = #{id} and  </if>  <if test="userName != null && userName !=‘‘">user_name = #{userName} and    </if>  <if test="email != null and email.trim() != """>email = #{email} and   </if>  <!-- ognl會進行字串和數位轉換判斷；"0"==0,"1"==1 -->  <if test="gender==0 or gender==1">   gender = #{gender}  </if></trim></select>

　　3.choose標籤：分支選擇，類似於Java中的帶了break的switch...case

     

choose (when, otherwise):如果帶了id，就用id查，如果帶了userName就用userName查，只會進入其中一個！

案例示範：
     1>.在EmployeeMapper介面中添加一個方法：
            public List<Employee> getEmployeeByConditionChoose(Employee employee);
        2>.sql對應檔

<!-- public List<Employee> getEmployeeByConditionChoose(Employee employee); --><select id="getEmployeeByConditionChoose" resultType="com.neuedu.entity.Employee">select *from tbl_employee<where><!-- 如果帶了id，就用id查，如果帶了userName就用userName查，只會進入其中一個！ --><choose><when test="id != null">id = #{id}</when><when test="userName != null">user_name like #{userName}</when><when test="email != null">   email = #{email}</when><otherwise>1=1</otherwise></choose></where></select>

　　

4.trim 中的set標籤(where, set)：字串截取；其中where標籤封裝查詢條件，set標籤封裝修改條件
set元素會動態前置set關鍵字，同時也會消除無關的逗號。

        1).在EmployeeMapper中添加一個更新的方法，如下所示：
              public void updateEmp(Employee employee);
         2)在sql對應檔中，填寫相應的sql語句，如下所示【set標籤可以將欄位後面的逗號去掉】：

<update id="updateEmp">update tbl_employee <set><if test="userName != null">user_name = #{userName},</if><if test="email != null">email = #{email},</if><if test="gender != null">gender = #{gender},</if></set>where id = #{id}</update>

　　測試類別代碼為：

@Testpublic void testGetEmployee(){EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);Employee employee = new Employee();employee.setId(1);employee.setUserName("哈哈");employee.setEmail("[email protected]");employee.setGender(1);mapper.updateEmp(employee);}//當然上面的set標籤我們也可以使用trim標籤來代替,如下所示：<update id="updateEmp">update tbl_employee <trim prefix="set" suffixOverrides=","><if test="userName != null">user_name = #{userName},</if><if test="email != null">email = #{email},</if><if test="gender != null">gender = #{gender},</if></trim>where id = #{id}</update>

　　5.foreach:遍曆元素

動態SQL的另一個常用的操作是需要對一個集合進行遍曆，通常在構建in條件陳述式的時候！
foreach元素允許指定一個集合，聲明集合項目和索引變數，並可以指定開閉匹配的字串以及在迭代之間放置分隔字元。

案例示範：
         1>.在EmployeeMapper介面中加入一個方法，如下所示：
            public List<Employee> getEmpsByConditionForeach(@Param("ids") List<Integer> ids);
         2>.在MyBatis的sql對應檔中寫相應的代碼：

<!-- public List<Employee> getEmpsByConditionForeach(List<Integer> ids); --><select id="getEmpsByConditionForeach" resultType="com.neuedu.entity.Employee">select * from  tbl_employee where id in<!-- collection:指定要遍曆的集合item:將當前遍曆出的元素賦值給指定的變數separator:每個元素之間的分隔字元open:遍曆出所有記過拼接一個開始的字元close:遍曆出所有結果拼接一個結束的字元 --><foreach collection="ids" open="(" close=")" separator="," item="id">#{id}</foreach></select>

　　   3.>測試類別代碼為：

@Testpublic void testGetEmployee(){EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);List<Integer> asList = Arrays.asList(1,2,3,4);List<Employee> emps = mapper.getEmpsByConditionForeach(asList);for (Employee employee : emps) {System.out.println(employee);}}

　　

foreach標籤還可以用於批量儲存資料，如下所示：

1.在EmployeeMapper介面類中添加批量插入的方法：
public void addEmps(@Param("emps") List<Employee> emps);

2.在EmployeeMapper.xml的sql對應檔中添加響應的語句：

<!-- public void addEmps(@Param("emps") List<Employee> emps); -->
<!-- MySQL下批量儲存：可以foreach遍曆，mysql支援values(),(),()文法 -->
<insert id="addEmps">
INSERT INTO tbl_employee(user_name,gender,email,d_id) VALUES
<foreach collection="emps" item="emp" separator=",">
(#{emp.userName},#{emp.gender},#{emp.email},#{emp.depart.id})
</foreach>
</insert>
3.測試代碼：
@Test
public void testGetEmployee(){
EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
List<Employee> emps = new ArrayList<Employee>();
emps.add(new Employee(0, 1, "allen", "[email protected]", new Department(1)));
emps.add(new Employee(0, 0, "tom", "[email protected]", new Department(2)));
emps.add(new Employee(0, 1, "mux", "[email protected]", new Department(1)));
mapper.addEmps(emps);
}

動態拼接SQL語句