MyBatis 【Mapper】文件常用写法

<?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.UsersMapper">
    <!-- 查询所有用户信息，对应接口中的findAll()方法 -->
    <select id="findAll" resultType="Users">
        select * from users
    </select>
    
    <!-- 修改用户信息，对应接口中的updateUsers方法,测试set元素 -->
    <update id="updateUsers" parameterType="Users">
        update users
        <set>
            <if test="name!=null">
                name=#{name}
            </if>
            <if test="password!=null">
                ,password=#{password}
            </if>
            <if test="age!=0">
                ,age=#{age}
            </if>
        </set>
        where id=#{id}
    </update>
    
    <!-- 删除用户 对应接口中的deleteUsers方法 -->
    <delete id="deleteUsers" parameterType="Users">
        delete from users where id=#{id}
    </delete>
    
    <!-- 添加用户，对应接口中的insertUsers方法 -->
    <insert id="insertUsers" parameterType="Users">
        insert into users values(#{id},#{name},#{password},#{age},#{sex})
    </insert>
    
    <!-- 按多重条件查找用户，对应接口中的findByAll方法,测试trim元素 -->
    <select id="findByAll" parameterType="Users" resultType="Users">
        select * from users
        <trim prefix="where" prefixOverrides="and|or">
            <if test="name!=null">
                name=#{name}
            </if>
            <if test="password!=null">
                and password=#{password}
            </if>
            <if test="age!=0">
                and age=#{age}
            </if>
        </trim>
    </select>
    <select id="findById" parameterType="Users" resultType="Users">
        select * from users where id=#{id}
    </select>
    
    <!-- 根据多重条件查找用户，对应接口中的testList方法，测试foreach元素中的单参数list类型 -->
    <select id="test_List_ids" parameterType="Users" resultType="Users">
        select * from users where id in
        <foreach collection="list" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
    </select>
    
    <!-- 根据多重条件查找用户，对应接口中的testList2方法，测试foreach元素中的单参数array类型 -->
    <select id="test_Array_ids" parameterType="Users" resultType="Users">
        select * from users where id in
        <foreach collection="array" index="index" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
    </select>
    
    
    <!--    final List<Integer> ids = new ArrayList<Integer>();
            ids.add(1);
            ids.add(2);
            ids.add(3);
            ids.add(4);
            Map<String, Object> params = new HashMap<String, Object>();
            params.put("ids", ids);
            params.put("name", "as");
            params.put("name1", "萨"); 
    -->
    <select id="test_Map" resultType="Users">
        select * from users where name like "%"#{name1}"%" or name like
        #{name} and id in
        <foreach collection="ids" index="index" item="item" open="("
            separator="," close=")">
            #{item}
        </foreach>
    </select>
    
    <!-- choose 选首个条件匹配查询 -->
    <select id="test_Choose" parameterType="Users" resultType="Users">
        select * from users where 1=1
        <choose>
            <when test="id!=0">
                and id=#{id}
            </when>
            <when test="name!=null">
                and name=#{name}
            </when>
            <when test="password!=null">
                and password=#{password}
            </when>
            <when test="age!=0">
                and age=#{age}
            </when>
            <otherwise>
                and 2=2
            </otherwise>
        </choose>
    </select>
</mapper>

 

转载于:https://www.cnblogs.com/luffya/p/3637824.html