mybatis的XML文件格式

仅供自己参考，用于复制粘贴

基础mybatis的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.itheima.mapper.ClassMapper">
    <resultMap id="class" type="com.itheima.pojo.ClassInfo">
        <result column="id" property="cid"/>
        <result column="cname" property="cname"/>
    </resultMap>

    <resultMap id="stu" type="com.itheima.pojo.Student">
        <result column="sid" property="id"/>
        <result column="sname" property="name"/>
        <result column="age" property="birthday"/>
        <result column="sex" property="sex"/>
        <result column="cid" property="cid"/>
    </resultMap>

    <insert id="clsAdd">
        insert into cls(cname) value (#{cname});
    </insert>

    <select id="findById" resultMap="class">
        select *
        from cls
        where id = #{id};
    </select>

    <select id="findAllCls" resultMap="class">
        select *
        from cls;
    </select>

    <select id="findStus" resultMap="stu">
        SELECT student.sid, student.sname, student.age, student.sex
        FROM student
        WHERE student.cid = #{cid};
    </select>
</mapper>

动态sql，懒加载的使用

<?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.itheima.mapper.StudentMapper">
    <resultMap id="stu" type="com.itheima.pojo.Student">
        <result column="sid" property="id"/>
        <result column="sname" property="name"/>
        <result column="age" property="birthday"/>
        <result column="sex" property="sex"/>
        <result column="cid" property="cid"/>

        <association property="classInfo"
                     javaType="com.itheima.pojo.ClassInfo"
                     fetchType="lazy"
                     column="cid"
                     select="com.itheima.mapper.ClassMapper.findById"/>

        <collection property="course"
                    javaType="java.util.List"
                    fetchType="lazy"
                    column="sid"
                    select="com.itheima.mapper.CourseMapper.findCourseWithScore"/>
        <!--                     select="SELECT sc.courseid from student, cls, sc where student.cid = cls.id and student.sid = sc.studentid"/>-->

        <!--        <collection property="course"-->
        <!--                    javaType="java.util.List"-->
        <!--                    fetchType="lazy"-->
        <!--                    column="courseid"-->
        <!--                    select="com.itheima.mapper.CourseMapper"/>-->
    </resultMap>

    <update id="updateStu">
        update student
        <set>
            <if test="name != null and name != ''">
                sname = #{name},
            </if>
            <if test="birthday != null">
                age = #{birthday},
            </if>
            <if test="sex != null and sex != ''">
                sex = #{sex},
            </if>
            <if test="cid != 0">
                cid = #{cid},
            </if>
        </set>
        where sid = #{id};
    </update>


    <delete id="deleteById">
        delete
        from student
        where sid = #{id};
    </delete>

    <delete id="deleteByIds">
        delete from student where sid in
        <foreach collection="ids" item="id" separator="," open="(" close=")">
            #{id}
        </foreach>
    </delete>

    <select id="findAllStuWithClass" resultMap="stu">
        select *
        from student,
             cls
        where student.cid = cls.id;
    </select>

    <select id="findStuById" resultMap="stu">
        select *
        from student,
             cls
        where sid = #{id}
          and student.cid = cls.id;
    </select>

    <select id="findStuByIds" resultMap="stu">
        select *
        from student,
        cls
        where student.cid = cls.id and
        sid in
        <foreach collection="ids" item="id" separator="," open="(" close=")">
            #{id}
        </foreach>
    </select>

    <select id="findAllStuWithClassCourse" resultMap="stu">
        select *
        from student;
    </select>

    <select id="selectInfoByStr" resultMap="stu">
        select * from student
        <where>
            <if test="str != null">or sid like #{str}</if>
            <if test="str != null and str != ''">or sname like #{str}</if>
            <if test="str != null and str != ''">or sex like #{str}</if>
<!--            <if test="str != null">or age like #{str}</if>-->
        </where>
    </select>

    <select id="selectByPageAndCondition" resultMap="stu">
        select *
        from student
        <where>
            <if test="student!= null and student.name != null and student.name != ''">
                sname like #{student.name}
            </if>
            <if test="student!= null and student.sex != null and student.sex != ''">
                and sex like #{student.sex}
            </if>
        </where>
        limit #{begin}, #{size};
    </select>
    <select id="findStusByCoId" resultMap="stu">
        SELECT *
        FROM (SELECT * FROM student, sc WHERE student.sid = sc.studentid) t1, course
        WHERE t1.courseid = course.id AND t1.courseid = #{id}
    </select>

    <insert id="addStuInfo" useGeneratedKeys="true" keyProperty="id">
        insert into student(sname, age, sex, cid)
        values (#{name}, #{birthday}, #{sex}, #{cid})
    </insert>
</mapper>