实体类层:
import lombok.Data;
import java.util.List;
/**
* 教师对象 teacher
*/
@Data
public class Teacher {
/** 教师id */
private String id;
/** 教师名称 */
private String teacherName;
/** 性别 */
private String sex;
/** 年龄 */
private int age;
/** 科目列表 */
private List<Subject> subjectList;
}
import lombok.Data;
/**
* 科目对象 subject
*/
@Data
public class Subject {
/** 科目id */
private String id;
/** 科目名称 */
private String subjectName;
/** 科目教室 */
private String classroom;
/** 课程时间(分钟) */
private String courseTime;
}
import lombok.Data;
/**
* 教师关系科目 teacher_relation_subject
*/
@Data
public class TeacherRelationSubject {
/** id */
private String id;
/** 教师ID */
private String teacherId;
/** 科目id */
private String subjectId;
}
mapper.xml层
collection:表示关联查询的结果集;
property: 表示关联查询的结果集映射到实体类的那个属性中;
ofType: 指定关联查询的结果集映射的对象类型;
select : 表示关联的查询语句;
column: 表示关联的字段名称。
<?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.rckj.check.mapper.TeacherMapper">
<resultMap type="Teacher" id="TeacherResult">
<result property="id" column="id" />
<result property="teacherName" column="teacher_name" />
<result property="sex" column="sex" />
<result property="age" column="age" />
<collection property="subjectList" ofType="com.rckj.check.domain.Subject"
select="findSubjectById" column="id"/>
</resultMap>
<select id="findSubjectById" resultMap="SubjectResult">
SELECT * FROM subject s
LEFT JOIN teacher_relation_subject trs on s.id = trs.subject_id
WHERE trs.teacher_id = #{id}
</select>
<resultMap type="com.rckj.check.domain.Subject" id="SubjectResult">
<result property="id" column="id" />
<result property="subjectName" column="subject_name" />
<result property="classroom" column="classroom" />
<result property="courseTime" column="course_time" />
</resultMap>
<sql id="selectTeacherVo">
select id, teacher_name, sex, age from teacher
</sql>
<select id="selectTeacherList" parameterType="Teacher" resultMap="TeacherResult">
<include refid="selectTeacherVo"/>
<where>
<if test="teacherName != null and teacherName != ''">teacher_name like concat('%', #{teacherName}, '%')</if>
</where>
</select>
<select id="selectTeacherById" parameterType="String" resultMap="TeacherResult">
<include refid="selectTeacherVo"/>
where id = #{id}
</select>
<insert id="insertTeacher" parameterType="Teacher">
insert into teacher
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="id != null">id,</if>
<if test="teacherName != null">teacher_name,</if>
<if test="sex != null">sex,</if>
<if test="age != null">age,</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="id != null">#{id},</if>
<if test="teacherName != null">#{teacherName},</if>
<if test="sex != null">#{sex},</if>
<if test="age != null">#{age},</if>
</trim>
</insert>
<update id="updateTeacher" parameterType="TPortfolioProject">
update teacher
<trim prefix="SET" suffixOverrides=",">
<if test="teacherName != null">teacher_name = #{teacherName},</if>
<if test="sex != null">sex = #{sex},</if>
<if test="age != null">age = #{age},</if>
</trim>
where id = #{id}
</update>
<delete id="deleteTeacherById" parameterType="String">
delete from teacher where id = #{id}
</delete>
<delete id="deleteTeacherIds" parameterType="String">
delete from teacher where id in
<foreach item="id" collection="array" open="(" separator="," close=")">
#{id}
</foreach>
</delete>
</mapper>