mybatis 之多表查询

这次是三张表连查，第一次写，若有需要改进的地方还请大家多多指点

一、三张表

教师基本信息表（teacher_info）

教师考勤表（leave）（注：教师一个月的出勤情况对应一条考勤） 

教师工资明细表（teacher_salary）

在给表起名字的时候，注意有些是关键字，例如‘leave’在后续的代码中可能会造成一些错误，大家注意，不要像我的这个

表与表之间的关系：

一名教师有多条考勤，所以teacher_info 和leave之间是一对多的关系   1：n   因此在teacher_info对应的实体类中，添加了leaveTeacher 一个属性

一条考勤对应一条工资明细，   leave 和 teacher_salary 是一对一的关系  1：1  因此在leave对应的实体类中，添加了teacherSalary 一个属性

通过mybatis-generator生成对应的实体类是这样的：

教师基本信息表（teacher_info）：

public class TeacherInfo {

    private Integer tnum;

    private String name;

    private String sex;

    private Date birth;

    private String tel;

    private String grade;

    private String school;

    private String college;

    private String office;

    private String profess;

    private String state;

    private Date createtime;

    private Date changetime;

//    三表联查    一名教师有多条考勤，所以用List
    private List<LeaveTeacher>  leaveTeacher;
}

教师考勤表（leave）

public class LeaveTeacher {
    private Integer leaveId;

    private Integer tnum;

    private String teacherName;

    private Date startTime;

    private Float workDay;

    private Float sickRelax;

    private Float thingRelax;

    private String approvePerson;

    private String yearMonth;

    private Float fullAttendance;

    private Date createtime;

    private Date changetime;

    //关联的字段  考勤表和教师基本信息进行关联  考勤俩表联查
    private String name;

    //考勤表和工资表进行关联   一条考勤对应一条工资 三表联查
    private TeacherSalary teacherSalary;

教师工资明细表（teacher_salary）

public class TeacherSalary {
    private Integer salTeacherId;

    private Integer leaveTeacherId;

    private Integer tnum;

    private String name;

    private String tMonth;

    private Float basicWage;

    private Float trafficWage;

    private Float fullReward;

    private Float totalWage;

    private Float kaoqinReduce;

    private Float secureReduce;

    private Float taxReduce;

    private Float totalReduce;

    private Float realWage;

    private Date createtime;

    private Date changetime;

二、mapper.xml文件

 <resultMap id="BaseResultMap" type="com.aim.graduation.dao.entity.TeacherInfo">
    <id column="tnum" jdbcType="INTEGER" property="tnum" />
    <result column="id" jdbcType="INTEGER" property="id" />
    <result column="name" jdbcType="VARCHAR" property="name" />
    <result column="sex" jdbcType="VARCHAR" property="sex" />
    <result column="birth" jdbcType="DATE" property="birth" />
    <result column="tel" jdbcType="VARCHAR" property="tel" />
    <result column="grade" jdbcType="VARCHAR" property="grade" />
    <result column="school" jdbcType="VARCHAR" property="school" />
    <result column="college" jdbcType="VARCHAR" property="college" />
    <result column="office" jdbcType="VARCHAR" property="office" />
    <result column="profess" jdbcType="VARCHAR" property="profess" />
    <result column="state" jdbcType="VARCHAR" property="state" />
    <!--<result column="basic_wage" jdbcType="REAL" property="basicWage" />-->
    <result column="createtime" jdbcType="TIMESTAMP" property="createtime" />
    <result column="changetime" jdbcType="TIMESTAMP" property="changetime" />
    <!--assocication可以指定联合的JavaBean对象   基本信息表
    property="role"指定哪个属性是联合的对象
    javaType:指定这个属性对象的类型
    -->
    <collection property="leaveTeacher" ofType="com.aim.graduation.dao.entity.LeaveTeacher">
      <id column="leave_id" jdbcType="INTEGER" property="leaveId" />
      <result column="tnum" jdbcType="INTEGER" property="tnum" />
      <!--<result column="teacher_name" jdbcType="VARCHAR" property="teacherName" />-->
      <result column="start_time" jdbcType="DATE" property="startTime" />
      <result column="work_day" jdbcType="FLOAT" property="workDay" />
      <result column="sick_relax" jdbcType="FLOAT" property="sickRelax" />
      <result column="thing_relax" jdbcType="FLOAT" property="thingRelax" />
      <result column="approve_person" jdbcType="VARCHAR" property="approvePerson" />
      <result column="year_month" jdbcType="VARCHAR" property="yearMonth" />
      <result column="full_attendance" jdbcType="FLOAT" property="fullAttendance" />
      
      <association property="teacherSalary" javaType="com.aim.graduation.dao.entity.TeacherSalary">
        <id column="sal_teacher_id" jdbcType="INTEGER" property="salTeacherId" />
        <result column="basic_wage" jdbcType="REAL" property="basicWage" />
        <result column="traffic_wage" jdbcType="REAL" property="trafficWage" />
        <result column="full_reward" jdbcType="REAL" property="fullReward" />
        <result column="secure_reduce" jdbcType="REAL" property="secureReduce" />
        <result column="tax_reduce" jdbcType="REAL" property="taxReduce" />
      </association>
    </collection>
  </resultMap>

 <association>通常用来映射一对一的关系

 <collection >通常用来映射一对多的关系

三、sql语句

我写的很简单，能查出来就行

  <!--三表联查-->
  <select id="selectInfoLeaveSalary" resultMap="BaseResultMap">
    SELECT
    *
    FROM
    teacher_info,
    `leave`,
    teacher_salary
    WHERE
    teacher_info.tnum = `leave`.tnum
    AND teacher_salary.leave_teacher_id = `leave`.leave_id
  </select>

我写的很简单，若有什么需要改进的地方希望大家多多指教！