MyBatis的Mapper动态标签几种常见写法

MyBatis的Mapper几种常见写法

首先了解一下例子的数据库：
school表[学校表]：

Course表[课程表]：

student表[学生表]：

sc表[学生与课程的关系表]：

需要注意：

SQL映射文件中的namespace和接口的名字一模一样
语句id和接口中的方法名字 一样

config.xml中的别名：
	<typeAliases>
        <typeAlias type="com.etoak.student.entity.Student" alias="stu"></typeAlias>
        <typeAlias type="com.etoak.student.entity.Course" alias="cou"></typeAlias>
        <typeAlias type="com.etoak.student.entity.Sc" alias="sc"></typeAlias>
        <typeAlias type="com.etoak.student.entity.School" alias="sch"></typeAlias>
    </typeAliases>

需求一：

添加学生 返回能够判断是否提交成功的参数

接口代码：
	public boolean addStudent(Student stu);
Mapper代码1：  
	 <insert id="addStudent" parameterType="stu">
        <selectKey keyProperty="id" resultType="int">
            select last_insert_id() as id
        </selectKey>
         insert into student (id,name,age,birth,schid) values(null,#{name},#{age},#{birth},#{schid});
    </insert>
<!--
	这种写法判断时需要在service中对是否添加成功进行判断时调用所传入的stu的getId()方法判断id是否不为0
-->
Mapper代码2：
	 <insert id="addStudent" parameterType="stu">
         insert into student (id,name,age,birth,schid) values(null,#{name},#{age},#{birth},#{schid});
    </insert>
    
<!--
	然而底层是默认返回一个int来表示受影响行数，在service中直接对返回值进行判断即可
-->

需求二：

查询所有学生 条件：学生姓名(可有可无) 加分页

接口代码：
	public List<Student> querySomeStuWithSchool(@Param("name") String name,@Param("start") int start,@Param("max") int max);
Mapper代码：
    <resultMap id="rMap_1" type="stu">
        <id property="id" column="sid"></id>
        <result property="name" column="sname"></result>
        <result property="age" column="age"></result>
        <result property="birth" column="birth"></result>
        <result property="schid" column="schid"></result>
        <association property="sch" javaType="com.etoak.student.entity.School">
            <id property="id" column="schid"></id>
            <result property="name" column="schname"></result>
            <result property="info" column="address"></result>
        </association>
    </resultMap>
    <select id="QuerySomeStuWithSchool" resultMap="rMap_1" >
       select s.id as sid,s.name as sname,s.age,s.birth ,sch.name as schname,sch.id as schid ,concat_ws('-',pro.name,city.name,area.name,sch.info) as address from student s left join school sch on s.schid = sch.id left join locations pro on sch.proid = pro.id left join locations city on sch.cityid = city.id left join locations area on sch.areaid = area.id
        <where>
            <if test="name!=null and name!='' ">
                and  s.name like '%${name}%'
            </if>
        </where>
        limit #{start},#{max}
    </select>

这里需要注意的是:where判断条件有几种写法：

1.
	.<where>
            <if test="name!=null and name!='' ">
                and  s_name like '%${name}%'
            </if>
            <if test="age!=null">
                and s_age = #{age}
            </if>
        </where>

and遇到where时底层会自动删除

2.
	<where>
           <choose>
               <when test="name!=null and name!=''">
                   and s_name like '%${name}%'
               </when>
               <otherwise>
                   and s_age=#{age}
               </otherwise>
           </choose>
       </where>

需求三：

统计每个学校人数 显示：学校名字 人数

接口代码：
	 public List<Map<String, Object>> queryStusCountGroupBySchool();
Mapper代码：
	<select id="queryStusCountGroupBySchool" resultType="map">
        select sch.name,count(*) as rs from school sch left join student s on sch.id = s.schid group by sch.name
    </select>

需求四：

查看某个学生的选课信息

接口代码：
	public Student queryStuWithCourseAndScore(@Param("id") int id);
Mapper代码：
	<resultMap id="rMap_3" type="stu">
        <id property="id" column="sid"></id>
        <result property="name" column="sname"></result>
        <result property="age" column="age"></result>
        <result property="birth" column="birth"></result>
        <collection property="scs" ofType="sc">
            <id property="id" column="schid"></id>
            <result property="score" column="score"></result>
            <association property="course" javaType="cou">
                <id property="id" column="cid"></id>
                <result property="name" column="cname"></result>
            </association>
        </collection>
    </resultMap>
    <select id="queryStuWithCourseAndScore" parameterType="int" resultMap="rMap_3">
        select s.id as sid,s.name as sname,s.age,s.birth
                         ,c.id as cid,c.name as cname,sc.score,sc.id schid from student s left join sc on s.id = sc.sid left join  course c on sc.cid = c.id where s.id=#{id}
    </select>

需求五：

查询某个课程学习最好的同学

接口代码：
public List<Sc> queryStudentAndScoreByCourse(@Param("name") String name);
Mapper代码：
<resultMap id="rMap_4" type="sc">
        <id property="id" column="scid"></id>
        <result property="score" column="score"></result>
        <association property="stu" javaType="stu">
            <id property="id" column="id"></id>
            <result property="name" column="name"></result>
            <result property="age" column="age"></result>
            <result property="birth" column="birth"></result>
        </association>
    </resultMap>
<!--只返回一个！-->
    <select id="queryStudentAndScoreByCourse" resultMap="rMap_4" >
        select s.id,s.name,s.age,s.birth ,sc.score,sc.id scid from student s left join sc on  s.id = sc.sid left join course c on sc.cid  = c.id
                   where s.id in( select sid from sc where score in (select max(score) from sc where cid =(select id from course where name=#{name})) and cid=(select id from course where name=#{name})) and c.name=#{name}
    </select>