MyBatis学习笔记（六）动态sql
生活随笔
收集整理的這篇文章主要介紹了
MyBatis学习笔记（六）动态sql
小編覺得挺不錯的,現在分享給大家,幫大家做個參考.
在一些情況下，如查詢參數不確定時，需要用到動態sql 例子，根據動態輸入的查詢條件查詢student． 一．if語句 １.StudentTMapper [html] view plain copy package?com.skymr.mybatis.mappers;?? ?? import?java.util.List;?? import?java.util.Map;?? ?? import?com.skymr.mybatis.model.Student;?? ?? public?interface?StudentTMapper?{?? ?? ????public?List<Student>?searchStudents(Map<String,Object>?map);?? }?? map參數是查詢條件 ２.StudentTMappler.xml [html] view plain copy <?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:Mapper映射?-->?? <!--?這個文件感覺就像StudentMapper接口的實現一樣，只是從java文件變成了xml文件?? 充當了Dao類的功能?? ?-->?? <mapper?namespace="com.skymr.mybatis.mappers.StudentTMapper">?? ?? ????<resultMap?type="Student"?id="stuMap">?? ????????<id?property="id"?column="id"/>?? ????????<result?property="name"?column="name"/>?? ????????<result?property="age"?column="age"/>?? ????????<association?property="address"?column="address_id"?select="com.skymr.mybatis.mappers.AddressMapper.getAddress">?? ????????</association>?? ????????<association?property="grade"?column="grade_id"?select="com.skymr.mybatis.mappers.GradeMapper.getGrade"></association>?? ????</resultMap>?? ????<select?id="searchStudents"?resultMap="stuMap"?parameterType="map">?? ????????select?*?from?mybatis_student?where?1=1?? ????????<if?test="name!=null">?? ????????????and?name?like?#{name}?? ????????</if>?? ????????<if?test="age!=null">?? ????????????and?age=#{age}?? ????????</if>?? ????</select>?? </mapper>??? ３.測試 [html] view plain copy package?com.skymr.mybatis.service;?? ?? import?java.util.HashMap;?? import?java.util.List;?? import?java.util.Map;?? ?? import?org.apache.ibatis.session.SqlSession;?? import?org.junit.After;?? import?org.junit.Before;?? import?org.junit.Test;?? import?org.slf4j.Logger;?? import?org.slf4j.LoggerFactory;?? ?? import?com.skymr.mybatis.mappers.StudentTMapper;?? import?com.skymr.mybatis.model.Student;?? import?com.skymr.mybatis.util.MybatisUtil;?? ?? public?class?StudentTest4?{?? ?? ????private?Logger?logger?=?LoggerFactory.getLogger(StudentTest4.class);?? ?????? ????private?SqlSession?session;?? ?????? ????@Before?? ????public?void?beforeTest(){?? ????????session?=?MybatisUtil.openSession();?? ????}?? ????@After?? ????public?void?afterTest(){?? ????????session.close();?? ????}?? ?????? ????@Test?? ????public?void?testSearch(){?? ????????logger.info("測試查詢學生");?? ????????StudentTMapper?mapper?=?session.getMapper(StudentTMapper.class);?? ????????Map<String,Object>?map?=?new?HashMap<String,?Object>();?? ????????map.put("name",?"%aaa%");?? ????????map.put("age",?111);?? ????????List<Student>?list?=?mapper.searchStudents(map);?? ????????logger.info(list.toString());?? ????}?? ?????? }?? 二．choose,when ,otherwise [html] view plain copy Sometimes?we?don’t?want?all?of?the?conditionals?to?apply,?instead?we?want?to?choose?only?one?case?? among?many?options.?Similar?to?a?switch?statement?in?Java,?MyBatis?offers?a?choose?element.?? Let’s?use?the?example?above,?but?now?let’s?search?only?on?title?if?one?is?provided,?then?only?by?author?? if?one?is?provided.?If?neither?is?provided,?let’s?only?return?featured?blogs?(perhaps?a?strategically?list?? selected?by?administrators,?instead?of?returning?a?huge?meaningless?list?of?random?blogs).?? 有些時候我們不想所有的條件都使用到，只想在所有條件中選擇一個條件．與java的switch語種相似，Mybatis提供了一個元素．讓我們做個例子． [html] view plain copy <select?id="findActiveBlogLike"?? ?????resultType="Blog">?? ??SELECT?*?FROM?BLOG?WHERE?state?=?‘ACTIVE’?? ??<choose>?? ????<when?test="title?!=?null">?? ??????AND?title?like?#{title}?? ????</when>?? ????<when?test="author?!=?null?and?author.name?!=?null">?? ??????AND?author_name?like?#{author.name}?? ????</when>?? ????<otherwise>?? ??????AND?featured?=?1?? ????</otherwise>?? ??</choose>?? </select>?? 上面的例子是引用Mybatis文檔． 自己的例子： [html] view plain copy <select?id="searchStudents2"?resultMap="stuMap"?parameterType="map">?? ????select?*?from?mybatis_student?where?1=1?? ????<choose>?? ????????<when?test="searchBy=='name'">?? ????????????and?name?like?#{name}?? ????????</when>?? ????????<when?test="searchBy=='gradeId'">?? ????????????and?grade_id=#{gradeId}?? ????????</when>?? ????????<otherwise>?? ????????????and?age=#{age}?? ????????</otherwise>?? ????</choose>?? </select>?? 三．trim, where, set元素 1.當沒有條件時，有可能會生成這樣的sql [html] view plain copy SELECT?*?FROM?BLOG?? WHERE?? 如果不在后邊加入 1=1，則應該加上where標簽 [html] view plain copy <select?id="findActiveBlogLike"?? ?????resultType="Blog">?? ??SELECT?*?FROM?BLOG?? ??<where>?? ??????<if?test="state?!=?null">?? ????????state?=?#{state}?? ??????</if>?? ??</where>?? </select>?? 改成這樣就可以避免以上情況． ２.還有這種情況 [html] view plain copy SELECT?*?FROM?BLOG?? WHERE?? AND?title?like?‘someTitle’?? 需要去年多余的and /or [html] view plain copy <select?id="searchStudents3"?resultMap="stuMap"?parameterType="map">?? ????select?*?from?mybatis_student??? ????<trim?prefix="WHERE"?prefixOverrides="AND|OR">?? ????????<if?test="name!=null">?? ????????????and?name?like?#{name}?? ????????</if>?? ????????<if?test="age!=null">?? ????????????and?age=#{age}?? ????????</if>?? ????</trim>?? </select>?? 同樣，update set語句也會出現多余的","的問題 [html] view plain copy <update?id="updateAuthorIfNecessary">?? ??update?Author?? ????<set>?? ??????<if?test="username?!=?null">username=#{username},</if>?? ??????<if?test="password?!=?null">password=#{password},</if>?? ??????<if?test="email?!=?null">email=#{email},</if>?? ??????<if?test="bio?!=?null">bio=#{bio}</if>?? ????</set>?? ??where?id=#{id}?? </update>?? 也可以使用trim修改 [html] view plain copy <update?id="updateAuthorIfNecessary">?? ??update?Author?? ????<trim?prefix="SET"?suffixOverrides=",">?? ??????<if?test="username?!=?null">username=#{username},</if>?? ??????<if?test="password?!=?null">password=#{password},</if>?? ??????<if?test="email?!=?null">email=#{email},</if>?? ??????<if?test="bio?!=?null">bio=#{bio}</if>?? ????</trim>?? ??where?id=#{id}?? </update>?? 四．foreach元素 這是一個非常有用的動態sql元素，用來遍歷集合，常應用于IN語句中，例： [html] view plain copy IN?condition.?For?example:?? <select?id="selectPostIn"?resultType="domain.blog.Post">?? ??SELECT?*?? ??FROM?POST?P?? ??WHERE?ID?in?? ??<foreach?item="item"?index="index"?collection="list"?? ??????open="("?separator=","?close=")">?? ????????#{item}?? ??</foreach>?? </select>?? foreach元素非常強大，它允許你指定一個集合，聲明item和index變量，在元素內部使用．生成有"開始/結尾/分隔符"的字符串，這個元素非常智能，它不會不小心添加額外的分隔符． 記住，你可以把一個List實例或者一個數組當作參數傳遞到MyBatis.當你這樣做了，MyBatis會自動放到一個Map里．List實例會以"list"作為Key值，數組實例會以"array"作為Key值． foreach元素的屬性主要有 item，index，collection，open，separator，close。 item表示集合中每一個元素進行迭代時的別名. index指 定一個名字，用于表示在迭代過程中，每次迭代到的位置. open表示該語句以什么開始，separator表示在每次進行迭代之間以什么符號作為分隔 符. close表示以什么結束. 上面的例子生成的sql相當于 SELECT * FROM POST P WHERE ID in (?,?,?)
轉載于:https://www.cnblogs.com/bkyliufeng/p/6291777.html
總結
以上是生活随笔為你收集整理的MyBatis学习笔记（六）动态sql的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： Angular2 - Starter -
- 下一篇： mysql odbc 配置详解