MyBatis之基于XML的动态SQL

先说下我的梦想，大学的时候一直想着是能开店卖胡辣汤，到目前依然还是我的梦想，上周一家出版社联系我问我有没有时间可以合作出书，这也是我的梦想之一，想了想还是放弃了，至少觉得目前不行，毕竟工作还不到五年，出书我可不想误人子弟，还有就是将来能办个培训班，这个我觉得还不错，所以也是我坚持写博客的原因之一。装逼结束，开始正题。

计划着能在年前把MyBatis学个差不多，所以上周是逼自己一把，连着3天一直在看Mybatis.前面把MyBatis中的表映射、列映射和增删改查大致了解了一下，今天主要学习动态sql。动态sql说白了就是sql拼接，看怎么拼接方便好用。

一、if

比如在一些查询功能，当输入值时模糊查询，不输入值时查询全部。这样的话就需要拼接where,一般遇到这样的我先会在sql后面加where 1=1.这样不为where和and在揪心。

    <select id="finduserbylikename"  parameterType="string"  resultMap="courseResult">
       select * from course where 1=1
       <if test="_parameter!=null and  _parameter!=''">
               and name like #{_parameter}
       </if>
    </select>
    <select id="finduserbylikename2"  parameterType="map"  resultMap="courseResult">
     <bind name="pattern" value="'%' + _parameter.name + '%'" />
       select * from course where 1=1
       <if test="_parameter.name!=null and  _parameter.name!=''">
               and name like #{pattern}
       </if>
    </select>

        String resource = "Config.xml";
        //使用MyBatis提供的Resources类加载mybatis的配置文件（它也加载关联的映射文件）
        Reader reader = Resources.getResourceAsReader(resource); 
        //构建sqlSession的工厂
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
        //创建能执行映射文件中sql的sqlSession
        SqlSession session = sessionFactory.openSession(true);
        String statement="Cuiyw.MyBatis.DBMapping.UserMapper.finduserbylikename";
        List<Course> courses=session.selectList(statement, "%J%");
        System.out.println(courses.size());
        for(int i=0;i<courses.size();i++)
        {
            System.out.println(courses.get(i).toString());
        }
        statement="Cuiyw.MyBatis.DBMapping.UserMapper.finduserbylikename2";
        Map<String,Object> map=new HashMap<String,Object>();  
        map.put("name", "J");  
        courses=session.selectList(statement, map);
        System.out.println(courses.size());
        for(int i=0;i<courses.size();i++)
        {
            System.out.println(courses.get(i).toString());
        }
        session.close();

上面用了两种传参数的方式，一个时string，一个是map,用map可以传多个参数。还有就是在第二个使用了bind，这样可以在sql中增加%%,参数中可以不带。这里记下自己踩的一个坑。

    <select id="finduserbylikename"  parameterType="string"  resultMap="courseResult">
       select * from course where 1=1
       <if test="name!=null and  name!=''">
               and name like #{name}
       </if>
    </select>

我用上面的运行报出了下面的错误，我开始以为是like后面的#{name}错误，后来查了百度才知道是if里面的。

There is no getter for property named 'name' in 'class java.lang.String'

    <select id="finduserbylikename"  parameterType="string"  resultMap="courseResult">
       select * from course where 1=1
       <if test="_parameter!=null and  _parameter!=''">
               and name like #{name}
       </if>
    </select>

二、choose (when, otherwise)

做项目也经常会遇到左边一个下拉框选择搜索类型，按什么类型搜索，右边一个文本框，输入搜索关键字，点查询搜索。如果遇到这种情况，用choose是再合适不过了。

    <select id="findcard"  parameterType="map"  resultMap="cardResult">
     <bind name="pattern" value="'%' + _parameter.value + '%'" />
       select * from card where 1=1
          <choose>
                <when test="type=='city'">
                  AND city like #{pattern}
                </when>
                <when test="type=='address'">
                  AND address like #{pattern}
                </when>
                <otherwise>
                  AND city like #{pattern} or address like #{pattern}
                </otherwise>
          </choose>
    </select>

        String resource = "Config.xml";
        //使用MyBatis提供的Resources类加载mybatis的配置文件（它也加载关联的映射文件）
        Reader reader = Resources.getResourceAsReader(resource); 
        //构建sqlSession的工厂
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
        //创建能执行映射文件中sql的sqlSession
        SqlSession session = sessionFactory.openSession(true);
        String statement="Cuiyw.MyBatis.DBMapping.UserMapper.findcard";
        Map<String,Object>map=new HashMap<String,Object>();  
        map.put("type", "other"); 
        map.put("value", "深");  
        List<Card> cards=session.selectList(statement, map);
        System.out.println(cards.size());
        for(int i=0;i<cards.size();i++)
        {
            System.out.println(cards.get(i).toString());
        }
        statement="Cuiyw.MyBatis.DBMapping.UserMapper.findcard";
        map=new HashMap<String,Object>();  
        map.put("type", "city"); 
        map.put("value", "深");  
        cards=session.selectList(statement, map);
        System.out.println(cards.size());
        for(int i=0;i<cards.size();i++)
        {
            System.out.println(cards.get(i).toString());
        }
        session.close();

三、trim (where, set)

prefix:在trim标签内sql语句加上前缀。
suffix:在trim标签内sql语句加上后缀。
suffixOverrides:指定去除多余的后缀内容，如：suffixOverrides=","，去除trim标签内sql语句多余的后缀","。
prefixOverrides:指定去除多余的前缀内容

有这样一种情况，大家应该也很常见，比如对对象的修改操作，可能有时需要根据根据不同的条件进行修改不同的列，如果比如根据id修改name,根据id修改age,这样不能每个都写一个sql，这样也太不灵活了，于是trim出现了。

    <update id="updatecard" parameterType="map">
     update card 
     <trim prefix="set" suffixOverrides=",">
             <if test="_parameter.cardNo!=null and _parameter.cardNo!=''">
                    cardNo = #{cardNo},
           </if>
           <if test=" _parameter.city!=null and _parameter.city!=''">
                    city = #{city},
           </if>
     </trim>
     <trim prefix="WHERE" prefixOverrides="and|or">
            <if test=" _parameter.id!=null and _parameter.id!=''">
                   and id = #{id}
           </if>
           <if test=" _parameter.address!=null and _parameter.address!=''">
                   and address = #{address}
           </if>
     </trim>
    </update>

有了trim可以很方便的写出where、set子句，而不用担心set子句的逗号，where子句的and和or。这里有一个地方要注意，也是我踩过的坑，我在写prefixOverrides时中间的O写成了小写，然后就报错了,还有set子句中每个后面增加一个逗号。

        String statement="Cuiyw.MyBatis.DBMapping.UserMapper.updatecard";
        Map<String,Object> map=new HashMap<String,Object>();  
        map.put("cardNo", "100002");  
        map.put("city", "广州"); 
        map.put("id", "1");
        int result=session.update(statement, map);
        System.out.println(result);

四、foreach

 还有一种情况，这篇都是情况，举栗子，我们有时候需要使用in查询，通常传的是一个collection集合，那改怎么样拼接呢？当然是foreach.

item表示集合中每一个元素进行迭代时的别名，
index指 定一个名字，用于表示在迭代过程中，每次迭代到的位置，
open表示该语句以什么开始，
separator表示在每次进行迭代之间以什么符号作为分隔 符，
close表示以什么结束

    <select id="findcardbycardnos" parameterType="map" resultMap="cardResult">
        select * from card where cardNo in 
        <foreach collection="nos" index="index" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
    </select>

        SqlSession session = sessionFactory.openSession(true);
        String statement="Cuiyw.MyBatis.DBMapping.UserMapper.findcardbycardnos";
        List nos = new ArrayList();
        nos.add("100001");
        nos.add("100002");
        nos.add("100003");
        Map<String,Object> map=new HashMap<String,Object>();  
        map.put("nos", nos);  
        List<Card> cards=session.selectList(statement,map);
        for(int i=0;i<cards.size();i++)
        {
            System.out.println(cards.get(i).toString());
        }

这里使用的map进行的传参，如果是单个参数的话可以用Array数组或List类型，使用List时，需要parameterType和collection都改为list。使用数组时，parameterType="arraylist"，collection="array"。

list

        String statement="Cuiyw.MyBatis.DBMapping.UserMapper.findcardbycardnos";
        List nos = new ArrayList();
        nos.add("100001");
        nos.add("100002");
        nos.add("100003");
        Map<String,Object> map=new HashMap<String,Object>();  
        map.put("nos", nos);  
        List<Card> cards=session.selectList(statement,nos);
        for(int i=0;i<cards.size();i++)
        {
            System.out.println(cards.get(i).toString());
        }

    <select id="findcardbycardnos" parameterType="list" resultMap="cardResult">
        select * from card where cardNo in 
        <foreach collection="list" index="index" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
    </select>

array

        String statement="Cuiyw.MyBatis.DBMapping.UserMapper.findcardbycardnos";
        List<Card> cards=session.selectList(statement,new String[] {"100001","100002","100003"});
        for(int i=0;i<cards.size();i++)
        {
            System.out.println(cards.get(i).toString());
        }

    <select id="findcardbycardnos" parameterType="arraylist" resultMap="cardResult">
        select * from card where cardNo in 
        <foreach collection="array" index="index" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
    </select>