mybatis动态sql的一些常规操作demo

MyBatis 的强大特性之一便是它的动态 SQL。如果你有使用 JDBC 或其它类似框架的经验，你就能体会到根据不同条件拼接 SQL
语句的痛苦。例如拼接时要确保不能忘记添加必要的空格，还要注意去掉列表最后一个列名的逗号。利用动态 SQL 这一特性可以彻底摆脱这种痛苦。
废话不说直接上代码

1.表结构

2. 映射类

public class User {
    private int id;
    private String name;
    private byte age;
}

3. Mapper接口

public interface UserMapper {
    User select(int id);

    int insert(User user);

    // 选择性更新
    int update(User user);

    int delete(int id);

    // 按条件模糊查询
    List<User> selectByParams(User user);

    // 查出的结果放在map中
    List<Map> selectAsMap();

    // 数组批量查询
    List<User> selectInArray(int[] arr);

    // list批量查询
    List<User> selectInList(List list);

    // set批量查询
    List<User> selectInSet(Set set);

    // 批量插入
    int insertBatch(List<User> users);

}

4.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="dao.UserMapper">
    <sql id="selectAll">
        select * from `user`
    </sql>
    <!-- parameterType可以不写 -->
    <select id="select" resultType="model.User">
        select * from `user` where id = #{id}
    </select>

    <insert id="insert" parameterType="model.User" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO `user`(name,age) VALUES (#{name},#{age})
    </insert>

    <update id="update" parameterType="model.User">
        UPDATE `user`
        <set>
            <if test="name!=null">
                name=#{name},
            </if>
            <if test="age!=0">
                age=#{age},
            </if>
        </set>
        WHERE id=#{id}
    </update>

    <delete id="delete" parameterType="int" >
        DELETE FROM `user` WHERE id = #{id}
    </delete>

    <select id="selectByParams" resultType="model.User">
        select * from `user`
        <where>
            <choose>
                <when test="id!=0">
                    id=#{id}
                </when>
                <otherwise>
                    <if test="name!=null">
                        /* 生成一个变量 */
                        <bind name="pattern" value="'%' + name + '%'" />
                        AND name like #{pattern}
                    </if>
                    <if test="age!=0">
                        AND age=#{age}
                    </if>
                </otherwise>
            </choose>
        </where>
    </select>
    <!-- resultType 可以填map、hashmap、java.util.Map-->
    <select id="selectAsMap"  resultType="java.util.Map">
        select * from `user`
    </select>

    <select id="selectInArray"  resultType="model.User">
        select * from `user`
        <where>
            <if test="array!=null and array.length>0">
                id IN
                <foreach collection="array" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>

        </where>
    </select>
    <select id="selectInList"  resultType="model.User">
        select * from `user`
        <where>
            /*collection、list*/
            <if test="collection!=null and collection.size()>0">
                id IN
                <foreach collection="collection" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>

        </where>
    </select>
    <select id="selectInSet"  resultType="model.User">
        select * from `user`
        <where>
            /*collection、list*/
            <if test="collection!=null and collection.size()>0">
                id IN
                <foreach collection="collectio" item="item" open="(" separator="," close=")">
                    #{item}
                </foreach>
            </if>

        </where>
    </select>


    <insert id="insertBatch" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO `user`(name,age) VALUES
        <foreach item="item" collection="list" separator=",">
            (#{item.name},#{item.age})
        </foreach>
    </insert>
</mapper>

5. 测试类

public class Test {
    public static void main(String[] args) throws Exception {

//        User user = session.selectOne("dao.UserMapper.select",1);
        testSelect();
//        testInsert();
//        testUpdate();
//        testDelete();
//        testSelectByParams();
//        testSelectAsMap();
//        testSelectInArray();
//        testSelectInList();
//        testSelectInSet();
//        testInsertBatch();
    }

    public static SqlSession getSession() {
        String resource = "mybatis-config.xml";
        InputStream inputStream = null;
        try {
            inputStream = Resources.getResourceAsStream(resource);
        } catch (IOException e) {
            e.printStackTrace();
        }
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession session = sqlSessionFactory.openSession(true);
        return session;
    }

    public static void testSelect()  {
        SqlSession session = getSession();
        UserMapper mapper = session.getMapper(UserMapper.class);
        User user = mapper.select(1);
        System.out.println("查询的结果："+user);
        session.close();


    }

    public static void testInsert()  {
        SqlSession session = getSession();
        UserMapper mapper = session.getMapper(UserMapper.class);
        User user = new User();
        user.setName("斯坦李");
        user.setAge((byte)97);

        int total = mapper.insert(user);
        System.out.println("插入总数："+total);
        System.out.println(user.getName()+"的id："+user.getId());
        session.close();
    }
    public static void testUpdate()  {
        SqlSession session = getSession();
        UserMapper mapper = session.getMapper(UserMapper.class);
        User user = new User();
        user.setId((byte)3);
        user.setName(null);
        user.setAge((byte)58);

        int total = mapper.update(user);
        System.out.println("修改总数："+total);
        session.close();
    }

    public static void testDelete() {
        SqlSession session = getSession();
        UserMapper mapper = session.getMapper(UserMapper.class);

        int total = mapper.delete(1);
        System.out.println("删除总数："+total);
        session.close();
    }
    public static void testSelectByParams() {
        SqlSession session = getSession();
        UserMapper mapper = session.getMapper(UserMapper.class);
        User user = new User();
        user.setId(1);
//        user.setName("四");
        user.setAge((byte)43);

        List<User> users = mapper.selectByParams(user);
        System.out.println("users："+users);
        session.close();
    }

    public static void testSelectAsMap() {
        SqlSession session = getSession();
        UserMapper mapper = session.getMapper(UserMapper.class);
        List<Map> map = mapper.selectAsMap();
        System.out.println("map："+map);
        session.close();
    }

    public static void testSelectInArray() {
        SqlSession session = getSession();
        UserMapper mapper = session.getMapper(UserMapper.class);
        int[] arr = {1,2,3,4};
        List<User> users = mapper.selectInArray(arr);
        System.out.println("users："+users);
        session.close();
    }

    public static void testSelectInList() {
        SqlSession session = getSession();
        UserMapper mapper = session.getMapper(UserMapper.class);
        List list = new ArrayList();
        list.add(1);
        list.add(2);
        list.add(3);
        list.add(4);
        List<User> users = mapper.selectInList(list);
        System.out.println("users："+users);
        session.close();
    }

    public static void testSelectInSet() {
        SqlSession session = getSession();
        UserMapper mapper = session.getMapper(UserMapper.class);
        Set set = new HashSet();
        set.add(1);
        set.add(2);
        set.add(3);
        set.add(4);
        List<User> users = mapper.selectInSet(set);
        System.out.println("users："+users);
        session.close();
    }

    public static void testInsertBatch() {
        SqlSession session = getSession();
        UserMapper mapper = session.getMapper(UserMapper.class);
        List list = new ArrayList();
        User user1 = new User("李5",(byte)25);
        User user2 = new User("李6",(byte)25);
        User user3 = new User("李7",(byte)25);
        list.add(user1);
        list.add(user2);
        list.add(user3);
        int total = mapper.insertBatch(list);
        System.out.println("插入总数："+total);
        System.out.println("users："+list);
        session.close();
    }
}