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>