MyBatis(2)-动态SQL语句

1.动态SQL语句
1)PersonMapper.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="com.capgemini.mapper.PersonMapper">
    <!--根据传进的person查询，在查询前对person的name值及age值进行判断，然后根据name或者age或者name&age查询-->
    <select id="selectById" parameterType="com.capgemini.entity.Person" resultType="com.capgemini.entity.Person">
        <!--select id,name,age from person where name = ? and age = ?;-->
        select id,name,age from person
        <where>
            <if test="name!=null">
               name=#{name}
            </if>
            <if test="age!=null">
            <!-- 如果条件一不成立，条件二成立，会发现多处一个and，但是MyBatis会在将语句组合时候去除"and"-->
                and age=#{age}
            </if>
        </where>
    </select>

    <!-- 增加操作 -->

    <!-- 批量删除操作 -->
    <!-- 传入为Array -->
    <delete id="deleteByArray" parameterType="integer">
        <!-- delete from person where id in ( , , );-->
        delete from person where id in
        <!-- collection表示传入的参数集合，item表示每个元素变量的名称，open表示开始字符，close表示结束字符，separator表示分隔符-->
        <foreach collection="array" item="id" open="(" close=")" separator=",">
            #{id}
        </foreach>
    </delete>

    <!-- 传入为List -->
    <delete id="deleteByList" parameterType="integer">
        delete from person where id in
        <foreach collection="list" item="id" open="(" close=")" separator=",">
            #{id}
        </foreach>
    </delete>

    <!-- 传入为Map -->
    <delete id="deleteByMap" parameterType="Map">
      <!-- delete from person where id in( ?, ?) and name = ?-->
      <!-- 当传入的是Map集合时候，collection表示Map集合中的key值，当然其他的用#{key}也是如此 -->
      delete from person where id in
      <foreach collection="ids" item="id" open="(" close=")" separator=",">
          #{id}
      </foreach>
      and name = #{name}
    </delete>

    <!-- 更新操作 -->
    <update id="update" parameterType="com.capgemini.entity.Person">
    <!-- update person set age = ?, name = ? where id = ? ;-->
      update person
      <set>
          <if test="age!=null">
              age = #{age},
          </if>
          <if test="name!=null">
              name = #{name}
          </if>
      </set>
      where id = #{id}
    </update>
</mapper>

2)Test文件

package com.capgemini.test;

import com.capgemini.entity.Person;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;

import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


public class MyBatisTest {

    /*
    查询操作
     */
    @Test
    public void test01() throws IOException {
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("sqlMapperConfig.xml"));
        SqlSession sqlSession = sqlSessionFactory.openSession();

        Person person = new Person();
        person.setAge(25);
        person.setName("Jose");
        List<Person> persons = sqlSession.selectList("com.capgemini.mapper.PersonMapper.selectById",person);
        for (Person p: persons
             ) {
            System.out.println(p.toString());
        }

    }

    /*
    删除操作， 根据Array删除
     */
    @Test
    public void test02() throws IOException {
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("sqlMapperConfig.xml"));
        SqlSession sqlSession = sqlSessionFactory.openSession();

        //参数是数组
        sqlSession.delete("com.capgemini.mapper.PersonMapper.deleteByArray",new Integer[]{2,3,4});
        //需要手动提交
        sqlSession.commit();
    }

    /*
    批量删除，根据List删除
     */
    @Test
    public void test03() throws IOException {
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("sqlMapperConfig.xml"));
        SqlSession sqlSession = sqlSessionFactory.openSession();

        //参数是List集合
        List<Integer> idList = new ArrayList<Integer>();
        idList.add(5);
        idList.add(6);
        idList.add(7);

        sqlSession.delete("com.capgemini.mapper.PersonMapper.deleteByList",idList);
        sqlSession.commit();
    }

    /*
    批量删除，根据Map删除
     */
    @Test
    public void test04() throws IOException {
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("sqlMapperConfig.xml"));
        SqlSession sqlSession = sqlSessionFactory.openSession();

        Map<String,Object> idMap = new HashMap<String ,Object>();

        idMap.put("ids",new Integer[]{8,9});
        idMap.put("name","K");

        sqlSession.delete("com.capgemini.mapper.PersonMapper.deleteByMap",idMap);
        //需要手动提交
        sqlSession.commit();
    }

    /*
    更新操作
     */
    @Test
    public void test05() throws IOException {
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("sqlMapperConfig.xml"));
        SqlSession sqlSession = sqlSessionFactory.openSession();

        Person person = new Person();
        person.setId(9);
        person.setName("B");
        person.setAge(27);

        sqlSession.update("com.capgemini.mapper.PersonMapper.update",person);
        sqlSession.commit();
    }
}

2.分析
1）sql关键字where使用<where>标签，如果在句尾出现无用逗号则会自动忽略；
2）使用<if>标签进行判断，test为真则拼接<if>标签中的语句，在拼接时候如果句前出现and则会自动忽略；
3）使用<foreach>标签进行循环操作，collection表示传入的参数(集合，数组)，item表示循环的每个元素变量的名字，open表示开始字符，close表示结束字符，separator表示分割符；
4）sql关键字set使用<set>标签。
5）注意需要手动提交事务，使用sqlSession.commit()方法。