where set trim sql if foreach 实现xml方式的动态sql

1.student-mapping.xml

<?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">
<mapper namespace="dyna">
    <!-- create table STUDENT ( 
                ID NUMBER not null, 
                NAME VARCHAR2(20), 
                SEX NUMBER(2), 
                HOBBY VARCHAR2(200), 
                AGE NUMBER ) 
    -->

    <!-- sql用于定义共享的sql 其他select中通过include引用该sql -->
    <sql id="queryColumn">
       name,sex,age
   </sql>
   <select id="queryStudnetABCDEFG">
      select
      <!-- include 应用 -->
      <include refid="queryColumn"></include>
      from book
   </select>

    <!-- trim 

         suffixOverrides 
         prefixOverrides 要覆盖的字符串
         suffix 
         prefix 替换的字符串
    -->
    <insert id="addStudent" parameterType="map" >
        insert into student(
        <!-- trim 前缀替换用法 -->
        <trim prefixOverrides="," prefix="">
            ,id
            <if test="name!=null and name!=null" >
                ,name
            </if>
            <if test="sex!=null and sex!=null" >
                ,sex
            </if>
            <if test="hobby!=null and hobby!=null" >
                ,hobby
            </if>
            <if test="age!=null and age!=null" >
                ,age
            </if>
        </trim>
        )
        values(
        #{id},
        <!-- trim 后缀替换用法 -->
        <trim suffixOverrides="," suffix="">
            <if test="name!=null and name!=''" >
                #{name},
            </if>
            <if test="sex!=null and sex!=''" >
                #{sex},
            </if>
            <if test="hobby!=null and hobby!=''" >
                #{hobby},
            </if>
            <if test="age!=null and age!=''" >
                #{age},
            </if>

        </trim>
        )
        <!-- selectKey主键生成 -->
        <selectKey keyProperty="id"
                   order="BEFORE"
                   resultType="string"
        >
            select max(id)+1 from student
        </selectKey>
    </insert>

    <!-- where -->
    <select id="queryStudent" parameterType="map" resultType="map">
        select * from student
        <!-- where 默认代替第一的 and 为 where-->
        <where>
            <if test="id!=null and id!=''" >
                and id=#{id}
            </if>
            <if test="name!=null and name!=''" >
                and name=#{name}
            </if>
            <if test="sex!=null and sex!=''" >
                and sex=#{sex}
            </if>
            <if test="hobby!=null and hobby!=''" >
                and hobby=#{hobby}
            </if>
            <if test="age!=null and age!=''" >
                and age=#{age}
            </if>
        </where>
    </select>

    <!-- set -->
    <update id="updateStudent" parameterType="map"  >
        update student
        <!-- set 默认会去掉最后一个   逗号（,）  -->
        <set>
            <if test="name!=null and name!=''" >
                name=#{name},
            </if>
            <if test="sex!=null and sex!=''" >
                sex=#{sex},
            </if>
            <if test="hobby!=null and hobby!=''" >
                hobby=#{hobby},
            </if>
            <if test="age!=null and age!=''" >
                age=#{age},
            </if>
        </set>
        where id=#{id}
    </update>

    <!-- foreach -->
    <select id="queryStudentByHobby" parameterType="map" resultType="map">
        <!-- open  循环开始前的字符串 
             close 循环结束的字符串
             collection 循环的参数(该参数是一个集合或数组)
             index 循环的索引
             item  循环所使用的变量
             separator 除了最后一次循环外 所要拼接的字符串（分隔符）
        -->
        <foreach  open="select * from student where hobby in(" 
                  close=")" 
                  collection="hobbys" 
                  index="i" 
                  item="hobby" 
                  separator=","
        >
            #{hobby}
        </foreach>
    </select>



</mapper>

private static SqlSession sqlSession;
    @Before
    public void getSqlSession() throws IOException{
        if (sqlSession==null) {
            final String mybatisConf = "mybatis-conf.xml";
            InputStream in = Resources.getResourceAsStream(mybatisConf);
            SqlSessionFactory ssf= new SqlSessionFactoryBuilder() .build(in);
            sqlSession=ssf.openSession();
        }
    }
    @After
    public void closeSqlSession(){
        sqlSession.commit();
        sqlSession.close();
    }
@Test
    public void testDynaSqlWhere(){
        String statement="dyna.queryStudent";
        Map map= new HashMap();
        map.put("id", "1");
        map.put("name", "oracle");
        List result=sqlSession.selectList(statement, map);
        System.out.println("--->  " +result);
    }
    @Test
    public void testDynaSqlSet(){
        String statement="dyna.updateStudent";
        Map map= new HashMap();
        map.put("id", "1");
        map.put("name", "11122");
        map.put("age", "30");
        sqlSession.update(statement, map);

    }
    @Test
    public void testDynaSqlForEach(){
        String statement="dyna.queryStudentByHobby";
        Map map= new HashMap();
        String[] hobbys={"playgame","readbook","listenCD"};
        map.put("hobbys",hobbys );
        List result=sqlSession.selectList(statement, map);
        System.out.println("--->  " +result);

    }
    @Test
    public void testDynaSqlTrim(){
        String statement="dyna.addStudent";
        Map map= new HashMap();
        map.put("hobby","playgame");
        sqlSession.insert(statement, map);
    }