mybatis常用注解

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="slq.mapper.StudentMapper">
    <!--    sql片段-->
    <sql id="if_id">
        <if test="id!=null">id=#{id}</if>
    </sql>
    <select id="getStudent" resultType="slq.pojo.Student">
        select * from student
        <where>
            <include refid="if_id"></include>
        </where>
    </select>
    <!--    多对一，方法1-->
    <select id="getStudent2" resultMap="Student2">
        select * from student where id=#{id}
    </select>
    <select id="getTeacher2" resultType="slq.pojo.Teacher">
        select * from teacher where id=#{id}
    </select>
    <resultMap id="Student2" type="slq.pojo.Student">
        <result property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="age" column="age"/>
        <association property="teacher" javaType="slq.pojo.Teacher" select="getTeacher2" column="tid">
            <result property="id" column="id"/>
            <result property="name" column="name"/>
        </association>
    </resultMap>
    <!--    多对一，方法2-->
    <select id="getStudent3" resultMap="Student3">
        select t.name tname,s.name sname from teacher t inner join student s 
        	where s.id=#{id} and t.id=s.tid
    </select>
    <resultMap id="Student3" type="slq.pojo.Student">
        <result property="name" column="sname"/>
        <association property="teacher" javaType="slq.pojo.Teacher">
            <result property="name" column="tname"/>
        </association>
    </resultMap>
    <!--    多个条件满足时添加所有的满足的条件-->
    <select id="getss2" resultType="slq.pojo.Student">
        select * from student
        <where>
            <if test="id!=null">and id=#{id}</if>
            <if test="name!=null">or name=#{name}</if>
        </where>
    </select>
    <!--    多个条件满足时只添加第一个满足的条件-->
    <select id="getss" resultType="slq.pojo.Student">
        select * from student
        <where>
            <choose>
                <when test="id!=null">id=#{id}</when>
                <when test="name!=null">name=#{name}</when>
                <otherwise>tid=2</otherwise>
            </choose>
        </where>
    </select>
    <update id="update" parameterType="slq.pojo.Student">
        update student
        <set>
            name=#{name},
            <if test="age>20">age=#{age},</if>
        </set>
        where id=#{id}
    </update>
    <!--    根据一个不同字段动态查询-->
    <select id="getStudent4" resultType="slq.pojo.Student">
        select * from student where ${col}=#{val}
    </select>
    <!--    <insert id="insert">-->
    <!--    返回主键-->
    <insert id="insert" useGeneratedKeys="true" keyProperty="id">
        insert student(name,age) values(#{name},#{age})
    </insert>
    <!--    delete from student WHERE age >222-->
    <delete id="delete">
        delete from student
        <where>
            <if test="name!=null">and name like concat("%",#{name},"%")</if>
            <if test="age!=null">and age ${age}</if>
        </where>
    </delete>
    <!--    foreach遍历array和list-->
    <!--   ids(item):   (id=1 or id=2 or id=3)-->
    <!--   ids(index):  and (id=0 or id=1 or id=2)-->
    <!--   names:       and name in (n2,n3,n4)-->
    <select id="getStudent5" resultType="slq.pojo.Student">
        select * from student
        <where>
            <foreach collection="ids" separator=" or id=" item="item" open="(id=" close=")">
                #{item}
            </foreach>
            <foreach collection="ids2" separator=" or id=" index="index" open="and (id=" close=")">
                #{index}
            </foreach>
            <foreach collection="names" separator="," item="item" open="and name in (" close=")">
                #{item}
            </foreach>
        </where>
    </select>
    <!--    嵌套foreach，遍历map，根据多个不同字段动态查询-->
    <!--    name in (n2,n3,n4) and (id=1 or id=2 or id=3 )-->
    <select id="getStudent6" resultType="slq.pojo.Student">
        select * from student
        <where>
            <foreach collection="mappp.entrySet()" index="key" item="value" separator=" and ">
                <if test="key=='id'">
                    <foreach collection="value" separator=" or id=" item="item" open="(id=" close=")">
                        #{item}
                    </foreach>
                </if>
                <if test="key=='name'">
                    <foreach collection="value" separator="," item="item" open="name in (" close=")">
                        #{item}
                    </foreach>
                </if>
            </foreach>
        </where>
    </select>
</mapper>