Mybatis动态sql及批量删除、插入

映射文件：

<!-- =================动态sql -->
<!--
    where 用于动态条件组合查询，可以去掉where后的第一个and
-->
<select id="slelectPersonByCondition" parameterType="qc" resultType="zhou.model.Person">
    select * from person_test t
    <where>
        <if test="name != null">
            t.name like '%${name}%'
        </if>

        <if test="gender != null">
            and t.gender = #{gender}
        </if>

        <if test="address != null">
            and t.address like '%${address}%'
        </if>

        <if test="birthday != null">
            and t.birthday &lt; #{birthday}
        </if>
    </where>
</select>

<!--
    动态修改
    使用set标签处理，能处理掉最后一个逗号，
    如果前端set的值都为空可加t.id = #{id} 变通处理
-->
<update id="dynamicUpdate" parameterType="person">
    update person_test t
    <set>
        t.id = #{id},
        <if test="name != null">
            t.name = #{name},
        </if>
        <if test="gender != null">
            t.gender = #{gender},
        </if>
        <if test="address != null">
            t.address = #{address},
        </if>

        <if test="birthday != null">
            t.birthday = #{birthday}
        </if>
    </set>
    where t.id = #{id}
</update>

<!--
    (1,2,3)
    map.put("ids", list/array/set)

    foreach：遍历集合来组装sql
    collection: map集合的key
    open: 以某种字符开始
    close:以某种字符结束
    item: 集合中的元素
    separator:以某种字符分割
    index: 当前所遍历到的索引号
-->
<select id="selectPersonByIn" parameterType="map" resultType="zhou.model.Person">
    select * from person_test t where t.id in
    <foreach collection="ids" open="(" close=")" item="id" separator="," index="index">
        #{id}
    </foreach>
</select>

<delete id="deleteBatch" parameterType="map">
    delete from person_test where id in
    <foreach collection="ids" open="(" close=")" item="id" separator="," index="index">
        #{id}
    </foreach>
</delete>

<!--
    map.put("personList", List<Person> list)
    mybatis有它的一种机制可插入多条： insert into person(id, name) values (1, "张三"), (2, "张三"), (3, "张三")
-->
<insert id="insertBatch" parameterType="person">
    <selectKey keyProperty="id" order="AFTER" resultType="int">
        select last_insert_id()
    </selectKey>
    insert into person_test (<include refid="colums"/>)
    values
    <foreach collection="personList" separator="," item="person">
        (#{person.name},#{person.gender},#{person.address},#{person.birthday})
    </foreach>
</insert>

Test类：

package zhou;

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.Before;
import org.junit.Test;
import zhou.model.Person;
import zhou.model1.QueryCondition;

import java.io.InputStream;
import java.util.*;

/**
 * Unit test for simple App.
 */
public class AppTest02 {
    SqlSessionFactory sessionFactory;

    @Before
    public void setUp() throws Exception {
        InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
        sessionFactory = new SqlSessionFactoryBuilder().build(in);
    }


    @Test
    public void test() {
        SqlSession session = sessionFactory.openSession();
        try {
            QueryCondition condition = new QueryCondition();
            condition.setName("张三");
            condition.setAddress("上");
//            condition.setBirthday(new Date());
//            condition.setGender(0);
            List<Person> list = session.selectList("mappings.PersonTestMapper.slelectPersonByCondition", condition);
            for (Person p : list) {
                System.out.println(p);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            session.close();
        }

    }

    @Test
    public void dymicUpdate() {
        SqlSession session = sessionFactory.openSession();
        Person p = new Person();
//        p.setName("张三");
//        p.setAddress("上");
        p.setId(1);
//            p.setBirthday(new Date());
//            p.setGender(0);
        try {
            int list = session.update("mappings.PersonTestMapper.dynamicUpdate", p);
            session.commit();
        } catch (Exception e) {
            e.printStackTrace();
            session.rollback();
        } finally {
            session.close();
        }

    }

    @Test
    public void dymicSelete() {
        SqlSession session = sessionFactory.openSession();
        try {
            Integer[] ids = {1, 2, 3};
            HashMap<String, Object> map = new HashMap<>();
            map.put("ids", ids);
            List<Person> list = session.selectList("mappings.PersonTestMapper.selectPersonByIn", map);
            for (Person p : list) {
                System.out.println(p);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            session.close();
        }

    }

    @Test
    public void dymicDelete() {
        SqlSession session = sessionFactory.openSession();
        try {
            Integer[] ids = {1, 2, 3};
            HashMap<String, Object> map = new HashMap<>();
            map.put("ids", ids);
            int list = session.delete("mappings.PersonTestMapper.deleteBatch", map);
            session.commit();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            session.close();
        }

    }

    @Test
    public void insertBatch() {
        SqlSession session = sessionFactory.openSession();
        List<Person> list = new ArrayList<>();
        Map<String, Object> map = new HashMap<>();
        try {

            for (int i = 0; i < 1000000; i++) {
                Person person = new Person();
                person.setName("张三" + i);
                person.setGender(0);
                person.setAddress("上海" + i);
                person.setBirthday(new Date());
                list.add(person);
                if (i % 100 == 0) {
                    map.put("personList", list);
                    session.insert("mappings.PersonTestMapper.insertBatch", map);
                    list.clear();
                }
            }
            map.put("personList", list);
            session.insert("mappings.PersonTestMapper.insertBatch", map);
            // 数据库的变更都要提交事务
            session.commit();
        } catch (Exception e) {
            e.printStackTrace();
            session.rollback();
        } finally {
            session.close();
        }
    }


}