mybatis的动态sql ---元素

MyBatis 的强大特性之一便是它的动态 SQL 能力。
如果你有使用 JDBC 或其他类似框架的经验，你就能体会到根据不同条件拼接 SQL 字符串有多么痛苦。
拼接的时候要确保不能忘了必要的空格，还要注意省掉列名列表最后的逗号。
利用动态 SQL 这一特性可以彻底摆脱这种痛苦。

通常使用动态 SQL 不可能是独立的一部分,MyBatis 当然使用一种强大的动态 SQL 语言来改进这种情形,
这种语言可以被用在任意映射的 SQL 语句中。

动态 SQL 元素和使用 JSTL 或其他相似的基于 XML 的文本处理器相似。
MyBatis 3 大大提升了它们,现在用不到原先一半的元素就能工作了。
MyBatis 采用功能强大的基于 OGNL 的表达式来消除其他元素。



    
常用的动态sql标签:
if标签、where标签、sql片段、foreach标签,choose（when，otherwise）,trim,set

*********** if标签**********
 <select id="findAllInfosCount" resultType="int">
    select count(*)
    from student
    where 1=1
    <if test="studentid != null and studentid != ''"> and STUDENTID = #{studentid}</if>
    <if test="name != null and name != ''"> and NAME like ${name}</if>
    <if test="gender != null and gender != ''"> and GENDER = #{gender}</if>
    <if test="course != null and course != ''"> and COURSE = #{course}</if>
    <if test="score_min != null and score_min != ''"> and SCORE >= #{score_min}</if>
    <if test="score_max != null and score_max != ''"> and SCORE <![CDATA[<=]]> #{score_max}</if>  
    <if test="examdate != null and examdate != ''"> and EXAMDATE = #{examdate}</if>
</select>

***********where标签***********************

where语句的作用主要是简化SQL语句中where中的条件判断的.自动处理第一个and

where元素的作用是会在写入where元素的地方输出一个where，
另外一个好处是你不需要考虑where元素里面的条件输出是什么样子 的，MyBatis会智能的帮你处理，
如果所有的条件都不满足那么MyBatis就会查出所有的记录，如果输出后是and 开头的，MyBatis会把第一个and忽略，
当然如果是or开头的，MyBatis也会把它忽略；
此外，在where元素中你不需要考虑空格的问题，MyBatis会智能的帮你加上。

    <select id="dynamicWhereTest" parameterType="Blog" resultType="Blog">  
        select * from t_blog   
     <where>  
            <if test="title != null">  
               and  title = #{title}  
            </if>  
            <if test="content != null">  
                and content = #{content}  
            </if>  
            <if test="owner != null">  
                and owner = #{owner}  
            </if>  
        </where>  
    </select> 

如果title=null， 而content != null，那么输出的整个语句会是
    select * from t_blog where content = #{content}，
    而不是select * from t_blog where and content = #{content}，
    因为MyBatis会智能的把首个and 或 or 给忽略。
    
***************set标签**************

set元素主要是用在更新操作的时候，它的主要功能和where元素其实是差不多的，
主要是在包含的语句前输出一个 set，然后如果包含的语句是以逗号结束的话将会把该逗号忽略，
如果set包含的内容为空的话则会出错。
    <update id="dynamicSetTest" parameterType="Blog">  
        update t_blog  
        <set>  
            <if test="title != null">  
                title = #{title},  
            </if>  
            <if test="content != null">  
                content = #{content},  
            </if>  
            <if test="owner != null">  
                owner = #{owner}  
            </if>  
        </set>  
        where id = #{id}  
    </update> 
    
****
****如果set中一个条件都不满足，即set中包含的内容为空的时候就会报错。
***


*******************foreach 标签*************************

foreach的主要用在构建in条件中，它可以在SQL语句中进行迭代一个集合。
foreach元素的属性主要有 item，index，collection，open，separator，close。
item表示集合中每一个元素进行迭代时的别名，
index指 定一个名字，用于表示在迭代过程中，每次迭代到的位置，
open表示该语句以什么开始，
separator表示在每次进行迭代之间以什么符号作为分隔 符，
close表示以什么结束，
在使用foreach的时候最关键的也是最容易出错的就是collection属性，
该属性是必须指定的，但是在不同情况 下，该属性的值是不一样的，

主要有一下3种情况：

如果传入的是单参数且参数类型是一个List的时候，collection属性值为list
如果传入的是单参数且参数类型是一个array数组的时候，collection的属性值为array
如果传入的参数是多个的时候，我们就需要把它们封装成一个Map了，当然单参数也可以封装成map，
实际上如果你在传入参数的时候，在MyBatis里面也是会把它封装成一个Map的，map的key就是参数名，
所以这个时候collection属性值就是传入的List或array对象在自己封装的map里面的key


综合where ,foreach
QueryVo 类中有User 和List<Integer>ids两个属性;

<select id="findUserListByQueryVo" parameterType="QueryVo" resultType="User">
	select * from user 
	<where>
		<if test="null!=user.username and ''!=user.username" >
			 username like "%"#{user.username}"%"
		</if>
		<if test="null!= ids and ids.size>0">
			<!--或者andi id in 写在这里也OK-->
			<foreach collection="ids" item="id" open="and id in (" close=")" separator=",">
				#{id}
			</foreach>
		</if>
	</where>
</select>

下面分别来看看上述三种情况的示例代码：
1.单参数List的类型：
    
    <!--resultType处List首字母大写,colloection处的首字母小写-->
    <select id="dynamicForeachTest" resultType="Blog" parameterType="List">  
        select * from t_blog where id in  
        <foreach collection="list" index="index" item="item" open="(" separator="," close=")">  
            #{item}  
        </foreach>  
    </select>  

    上述collection的值为list，对应的Mapper是这样的

    public List<Blog> dynamicForeachTest(List<Integer> ids);  

    测试代码：


    @Test  
    public void dynamicForeachTest() {  
        SqlSession session = Util.getSqlSessionFactory().openSession();  
        BlogMapper blogMapper = session.getMapper(BlogMapper.class);  
        List<Integer> ids = new ArrayList<Integer>();  
        ids.add(1);  
        ids.add(3);  
        ids.add(6);  
        List<Blog> blogs = blogMapper.dynamicForeachTest(ids);  
        for (Blog blog : blogs)  
            System.out.println(blog);  
        session.close();  
    }  



2.单参数array数组的类型：

        <select id="dynamicForeach2Test" resultType="Blog" parameterType="Integer[]">  
            select * from t_blog where id in  
            <foreach collection="array" index="index" item="item" open="(" separator="," close=")">  
                #{item}  
            </foreach>  
        </select>  
    
        上述collection为array，对应的Mapper代码：
    
        public List<Blog> dynamicForeach2Test(int[] ids);  
    
        对应的测试代码：
    
    
        @Test  
        public void dynamicForeach2Test() {  
            SqlSession session = Util.getSqlSessionFactory().openSession();  
            BlogMapper blogMapper = session.getMapper(BlogMapper.class);  
            int[] ids = new int[] {1,3,6,9};  
            List<Blog> blogs = blogMapper.dynamicForeach2Test(ids);  
            for (Blog blog : blogs)  
                System.out.println(blog);  
            session.close();  
        }  



3.自己把参数封装成Map的类型

        <select id="dynamicForeach3Test" parameterType="Map" resultType="Blog" >  
            select * from t_blog where title like "%"#{title}"%" and id in  
            <foreach collection="ids" index="index" item="item" open="(" separator="," close=")">  
                #{item}  
            </foreach>  
        </select>  
    
        上述collection的值为ids，是传入的参数Map的key，对应的Mapper代码：
    
        public List<Blog> dynamicForeach3Test(Map<String, Object> params); 
    
        对应测试代码：
    
    
        @Test  
        public void dynamicForeach3Test() {  
            SqlSession session = Util.getSqlSessionFactory().openSession();  
            BlogMapper blogMapper = session.getMapper(BlogMapper.class);  
            final List<Integer> ids = new ArrayList<Integer>();  
            ids.add(1);  
            ids.add(2);  
            ids.add(3);  
            ids.add(6);  
            ids.add(7);  
            ids.add(9);  
            Map<String, Object> params = new HashMap<String, Object>();  
            params.put("ids", ids);  
            params.put("title", "中国");  
            List<Blog> blogs = blogMapper.dynamicForeach3Test(params);  
            for (Blog blog : blogs)  
                System.out.println(blog);  
            session.close();  
        }  
        
********************choose标签*************************** 

有些时候，我们不想用到所有的条件语句，而只想从中择其一二。
针对这种情况，MyBatis 提供了 choose 元素，它有点像 Java 中的 switch 语句。

提供了“title”就按“title”查找，提供了“author”就按“author”查找，
若两者都没有提供，就返回所有符合条件的BLOG
（实际情况可能是由管理员策略地选出BLOG列表，而不是返回大量无意义的随机结果）。

<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>


******************bind标签***************

bind 元素可以从 OGNL 表达式中创建一个变量并将其绑定到上下文。比如：

<select id="selectBlogsLike" resultType="Blog">
  <bind name="pattern" value="'%' + _parameter.getTitle() + '%'" />
  SELECT * FROM BLOG WHERE title LIKE #{pattern}
</select>



*****sql片段******

Sql片段可将重复的sql提取出来,使用时用include引用即可，最终达到sql重用的目的

Sql片段需要先定义后使用

<!-- 传递pojo综合查询用户信息 -->

<select id="findUserList" parameterType="user" resultType="user">
	select * from user 
	<where>
	<if test="id!=null and id!=''">
	and id=#{id}
	</if>
	<if test="username!=null and username!=''">
	and username like '%${username}%'
	</if>
	</where>
</select>

    将where条件抽取出来：
    
    <sql id="query_user_where">
    	<if test="id!=null and id!=''">
    		and id=#{id}
    	</if>
    	<if test="username!=null and username!=''">
    		and username like '%${username}%'
    	</if>
    </sql>
    
    使用include引用：
    
    <select id="findUserList" parameterType="user" resultType="user">
    		select * from user 
    		<where>
    		<include refid="query_user_where"/>
    		</where>
    	</select>
    
    注意：如果引用其它mapper.xml的sql片段，则在引用时需要加上namespace，如下：
    <include refid="namespace.sql片段”/>