看一个我们的 mapper 吧, 绝大部分都不是单表语句, 觉得 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">
<!--namespace 非常重要：必须是 Mapper 类的全路径-->
<mapper namespace="ebag.mapper.ClazzMapper">
<!-- 老师或者学生的列 -->
<sql id="student_or_teacher_columns">
user.id AS id,
user.username AS username,
user.nickname AS nickname,
user.avatar AS avatar,
user.school_id AS school_id,
user.is_enabled AS is_enabled,
user.gender AS gender,
clazz.name AS clazz_name,
clazz.code AS clazz_code,
clazz.phase AS clazz_phase,
clazz.id AS clazz_id,
cts.subject AS clazz_subject
</sql>
<!-- 查找学校的班级 -->
<select id="findClazzesBySchoolId" resultType="Clazz">
SELECT id, school_id AS schoolId, code, name, phase, grade, type,
enrollment_year AS enrollmentYear, graduation_year AS graduationYear
FROM clazz
WHERE school_id = #{schoolId} AND is_history=#{history}
</select>
<!-- 查询指定 ID 的班级 -->
<select id="findClazzById" parameterType="long" resultType="Clazz">
SELECT id, school_id AS schoolId, code, name, phase, grade, type,
enrollment_year AS enrollmentYear, graduation_year AS graduationYear
FROM clazz WHERE id = #{clazzId}
</select>
<!-- 查找学校的老师 -->
<select id="findTeachersBySchoolId" resultMap="teacherResultMap">
SELECT <include refid="student_or_teacher_columns"/>
FROM (
SELECT * FROM user
WHERE school_id = #{schoolId} AND role = 'ROLE_TEACHER'
LIMIT #{offset}, #{count}
) AS user
LEFT JOIN clazz_teacher_subject AS cts ON cts.teacher_id =
user.id LEFT JOIN clazz ON
clazz.id = cts.clazz_id
</select>
<!-- 使用账号查找学校的老师 -->
<select id="findTeachersBySchoolIdAndUsernameLike" resultMap="teacherResultMap">
SELECT <include refid="student_or_teacher_columns"/>
FROM (
SELECT * FROM user
WHERE school_id = #{schoolId} AND role = 'ROLE_TEACHER' AND username LIKE CONCAT('%', #{username}, '%')
LIMIT #{offset}, #{count}
) AS user
LEFT JOIN clazz_teacher_subject AS cts ON cts.teacher_id =
user.id LEFT JOIN clazz ON
clazz.id = cts.clazz_id
</select>
<!-- 使用昵称查找学校的老师 -->
<select id="findTeachersBySchoolIdAndNicknameLike" resultMap="teacherResultMap">
SELECT <include refid="student_or_teacher_columns"/>
FROM (
SELECT * FROM user
WHERE school_id = #{schoolId} AND role = 'ROLE_TEACHER' AND nickname LIKE CONCAT('%', #{nickname}, '%')
LIMIT #{offset}, #{count}
) AS user
LEFT JOIN clazz_teacher_subject AS cts ON cts.teacher_id =
user.id LEFT JOIN clazz ON
clazz.id = cts.clazz_id
</select>
<!-- 查找学校的学生 -->
<select id="findStudentsBySchoolId" resultMap="studentResultMap">
SELECT <include refid="student_or_teacher_columns"/>
FROM (
SELECT * FROM user
WHERE school_id = #{schoolId} AND role='ROLE_STUDENT'
LIMIT #{offset}, #{count}
) AS user
LEFT JOIN clazz_student AS cs ON cs.student_id =
user.id LEFT JOIN clazz ON
clazz.id = cs.clazz_id
LEFT JOIN clazz_teacher_subject cts ON cts.clazz_id = cs.clazz_id
</select>
<!-- 使用账号查找学校的学生 -->
<!-- 注意 LIMIT 必须放到子查询内部，否则数量限制不对 -->
<select id="findStudentsBySchoolIdAndUsernameLike" resultMap="studentResultMap">
SELECT <include refid="student_or_teacher_columns"/>
FROM (
SELECT * FROM user
WHERE school_id = #{schoolId} AND role='ROLE_STUDENT' AND username LIKE CONCAT('%', #{username}, '%')
LIMIT #{offset}, #{count}
) AS user
LEFT JOIN clazz_student AS cs ON cs.student_id =
user.id LEFT JOIN clazz ON
clazz.id = cs.clazz_id
LEFT JOIN clazz_teacher_subject cts ON cts.clazz_id = cs.clazz_id
</select>
<!-- 使用名字查找学校的学生 -->
<select id="findStudentsBySchoolIdAndNicknameLike" resultMap="studentResultMap">
SELECT <include refid="student_or_teacher_columns"/>
FROM (
SELECT * FROM user
WHERE school_id = #{schoolId} AND role='ROLE_STUDENT' AND nickname LIKE CONCAT('%', #{nickname}, '%')
LIMIT #{offset}, #{count}
) AS user
LEFT JOIN clazz_student AS cs ON cs.student_id =
user.id LEFT JOIN clazz ON
clazz.id = cs.clazz_id
LEFT JOIN clazz_teacher_subject cts ON cts.clazz_id = cs.clazz_id
</select>
<!-- 查找班级下的老师 -->
<select id="findTeachersByClazzId" resultMap="teacherResultMap">
SELECT <include refid="student_or_teacher_columns"/>
FROM clazz_teacher_subject cts
LEFT JOIN clazz ON
clazz.id = cts.clazz_id
LEFT JOIN user ON
user.id = cts.teacher_id
WHERE cts.clazz_id = #{clazzId}
</select>
<!-- 查找班级下的所有学生 -->
<select id="findStudentsByClazzId" resultMap="studentResultMap">
SELECT <include refid="student_or_teacher_columns"/>
FROM clazz_student cs
LEFT JOIN user ON
user.id = cs.student_id
LEFT JOIN clazz ON
clazz.id = cs.clazz_id
LEFT JOIN clazz_teacher_subject cts ON cts.clazz_id = cs.clazz_id
WHERE cs.clazz_id=#{clazzId}
ORDER BY cs.student_username
</select>
<!-- 查找指定 ID 的老师 -->
<select id="findTeacherById" resultMap="teacherResultMap">
SELECT <include refid="student_or_teacher_columns"/>
FROM clazz_teacher_subject cts
LEFT JOIN clazz ON
clazz.id = cts.clazz_id
LEFT JOIN user ON
user.id = cts.teacher_id
WHERE cts.teacher_id = #{teacherId}
</select>
<!-- 查找指定 ID 的老师 -->
<select id="findTeacherByClazzIdAndSubject" resultMap="teacherResultMap">
SELECT <include refid="student_or_teacher_columns"/>
FROM clazz_teacher_subject cts
LEFT JOIN clazz ON
clazz.id = cts.clazz_id
LEFT JOIN user ON
user.id = cts.teacher_id
WHERE cts.clazz_id = #{clazzId} AND cts.subject = #{subject}
</select>
<!-- 查找指定 ID 的学生 -->
<select id="findStudentById" parameterType="long" resultMap="studentResultMap">
SELECT <include refid="student_or_teacher_columns"/>
FROM clazz_student cs
LEFT JOIN clazz ON
clazz.id = cs.clazz_id
LEFT JOIN clazz_teacher_subject cts ON cts.clazz_id = cs.clazz_id
LEFT JOIN user ON
user.id = cs.student_id
WHERE cs.student_id = #{studentId}
</select>
<!--查询班级学生人数-->
<select id="findStudentCount" resultType="int">
SELECT count(*) FROM clazz_student WHERE clazz_student.clazz_id = #{clazzId}
</select>
<!-- 启用或禁用老师 -->
<update id="enableTeacher">
UPDATE clazz_teacher_subject SET is_enabled=#{enabled} WHERE teacher_id=#{teacherId};
UPDATE user SET is_enabled=#{enabled} WHERE id=#{teacherId};
</update>
<!-- 启用或禁用学生 -->
<update id="enableStudent">
UPDATE clazz_student SET is_enabled=#{enabled} WHERE student_id=#{studentId};
UPDATE user SET is_enabled=#{enabled} WHERE id=#{studentId};
</update>
<!-- 删除老师 -->
<delete id="deleteTeacher">
DELETE FROM user WHERE id = #{teacherId};
DELETE FROM clazz_teacher_subject WHERE teacher_id = #{teacherId};
</delete>
<!-- 删除学生 -->
<delete id="deleteStudent">
DELETE FROM user WHERE id = #{studentId};
DELETE FROM clazz_student WHERE student_id = #{studentId};
</delete>
<!-- 插入或更新已有班级 -->
<insert id="insertOrUpdateClazz" parameterType="Clazz">
INSERT INTO clazz (id, school_id, code, name, phase, grade, type, enrollment_year, graduation_year, created_time)
VALUES (#{id}, #{schoolId}, #{code}, #{name}, #{phase}, #{grade}, #{type}, #{enrollmentYear}, #{graduationYear}, now())
ON DUPLICATE KEY
UPDATE name = #{name}, phase = #{phase}, grade = #{grade}, type = #{type},
enrollment_year = #{enrollmentYear}, graduation_year = #{graduationYear}
</insert>
<!-- 插入班级学生关系 -->
<insert id="insertClazzStudent">
INSERT INTO clazz_student (school_id, clazz_code, student_username, created_time)
SELECT #{schoolId}, #{clazzCode}, #{studentUsername}, now()
FROM dual
WHERE NOT EXISTS (
SELECT 1 FROM clazz_student
WHERE school_id = #{schoolId} AND clazz_code = #{clazzCode} AND student_username = #{studentUsername}
)
</insert>
<!-- 插入班级老师学科关系 -->
<insert id="insertClazzTeacherSubject">
INSERT INTO clazz_teacher_subject (school_id, clazz_code, teacher_username, subject, created_time)
SELECT #{schoolId}, #{clazzCode}, #{teacherUsername}, #{subject}, now()
FROM dual
WHERE NOT EXISTS (
SELECT 1 FROM clazz_teacher_subject
WHERE school_id = #{schoolId} AND clazz_code = #{clazzCode} AND teacher_username = #{teacherUsername} AND subject = #{subject}
)
</insert>
...
```
Select Language