mybatis多表查询

1，业务需求

有两个实体类，分别是Requirement和Cooperate，分别表示需求和对接。一个需求可以有多个对接。
需求和对接，都需要记录用户。简而言之，需要知道需求是谁发布的，对接的人是谁。所以Requirement和
Cooperate分别有一个SysUser成员变量。

2，实体类

@Table(name = "sys_user")
public class SysUser extends BaseEntity{
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    /**
     * 登陆账号
     */
    private String username;

    /**
     * 密码
     */
    private String password;

    /**
     * 用户类型
     */
    private Integer usertype;

    /**
     * 是否可用
     */
    private Boolean enabled;

    /**
     * 真实姓名
     */
    private String nickname;

    /**
     * 邮箱
     */
    private String email;

    /**
     * 电话
     */
    private String tel;

    String qq;


    private Integer gender;
    private String company;
    private Integer type;


    private String developKey;


    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public SysUser setUsername(String username) {
        this.username = username;
        return this;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public Integer getUsertype() {
        return usertype;
    }

    public void setUsertype(Integer usertype) {
        this.usertype = usertype;
    }

    public Boolean getEnabled() {
        return enabled;
    }

    public void setEnabled(Boolean enabled) {
        this.enabled = enabled;
    }

    public String getNickname() {
        return nickname;
    }

    public void setNickname(String nickname) {
        this.nickname = nickname;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getTel() {
        return tel;
    }

    public void setTel(String tel) {
        this.tel = tel;
    }

    public String getQq() {
        return qq;
    }

    public void setQq(String qq) {
        this.qq = qq;
    }

    public Integer getGender() {
        return gender;
    }

    public void setGender(Integer gender) {
        this.gender = gender;
    }
    public String getCompany() {
        return company;
    }

    public void setCompany(String company) {
        this.company = company;
    }

    public Integer getType() {
        return type;
    }

    public void setType(Integer type) {
        this.type = type;
    }



    public String getDevelopKey() {
        return developKey;
    }

    public void setDevelopKey(String developKey) {
        this.developKey = developKey;
    }
}

@Entity
@Table(name = "cooperate")
public class Cooperate extends BaseEntity {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    private Integer userId;
    private Integer requirementId;
    private String illustration;//对接的留言和说明
    private String userName;
    @Transient
    private Requirement requirement;
    @Transient
    private SysUser user;
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public Integer getUserId() {
        return userId;
    }
    public void setUserId(Integer userId) {
        this.userId = userId;
    }
    public Integer getRequirementId() {
        return requirementId;
    }
    public void setRequirementId(Integer requirementId) {
        this.requirementId = requirementId;
    }
    public String getIllustration() {
        return illustration;
    }
    public void setIllustration(String illustration) {
        this.illustration = illustration;
    }

    public String getUserName() {
        return userName;
    }
    public void setUserName(String userName) {
        this.userName = userName;
    }
    public SysUser getUser() {
        return user;
    }
    public void setUser(SysUser user) {
        this.user = user;
    }

}

@Entity
@Table(name = "requirement")
public class Requirement extends BaseEntity {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    private Integer userId;
    private Integer hardwareTypeId;//需求的硬件类型
    private String hardwareTypeName;//需要的硬件类型名称
    private String hardwareConnect;//需求所需要的硬件设备，多个硬件设备名称拼接成一个字符串，以“*”进行分割。
    private Integer reqFileId;//需求文档
    private Integer schedule;  //需求对接进度
    private String title;
    private String content;
    @Transient
    private List<Cooperate> cooperates;//所有已经对接的意向合作
    @Transient
    private SysUser sysUser;
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public Integer getUserId() {
        return userId;
    }
    public void setUserId(Integer userId) {
        this.userId = userId;
    }
    public Integer getHardwareTypeId() {
        return hardwareTypeId;
    }
    public void setHardwareTypeId(Integer hardwareTypeId) {
        this.hardwareTypeId = hardwareTypeId;
    }
    public Integer getReqFileId() {
        return reqFileId;
    }
    public void setReqFileId(Integer reqFileId) {
        this.reqFileId = reqFileId;
    }
    public String getTitle() {
        return title;
    }
    public void setTitle(String title) {
        this.title = title;
    }
    public String getContent() {
        return content;
    }
    public void setContent(String content) {
        this.content = content;
    }
    @Override
    public String toString() {
        return "Requirement [id=" + id + ", userId=" + userId
                + ", hardwareTypeId=" + hardwareTypeId + ", reqFileId="
                + reqFileId + ", title=" + title + ", content=" + content + "]";
    }
    public String getHardwareTypeName() {
        return hardwareTypeName;
    }
    public void setHardwareTypeName(String hardwareTypeName) {
        this.hardwareTypeName = hardwareTypeName;
    }
    public String getHardwareConnect() {
        return hardwareConnect;
    }
    public void setHardwareConnect(String hardwareConnect) {
        this.hardwareConnect = hardwareConnect;
    }
    public Integer getSchedule() {
        return schedule;
    }
    public void setSchedule(Integer schedule) {
        this.schedule = schedule;
    }
    public List<Cooperate> getCooperates() {
        return cooperates;
    }
    public void setCooperates(List<Cooperate> cooperates) {
        this.cooperates = cooperates;
    }
    public SysUser getSysUser() {
        return sysUser;
    }
    public void setSysUser(SysUser sysUser) {
        this.sysUser = sysUser;
    }
}

public class BaseEntity {
    //创建时间
    private Date created;
    //更新时间
    private Date updated;

    public Date getCreated() {
        return created;
    }

    public void setCreated(Date created) {
        this.created = created;
    }

    public Date getUpdated() {
        return updated;
    }

    public void setUpdated(Date updated) {
        this.updated = updated;
    }
}

3，mapper

public interface RequirementMapper extends MapperSupport<Requirement>{
    public List<Requirement> getAllRequirements();

}

<?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.official.manager.mapper.RequirementMapper" >
  <resultMap id="requirement" type="com.official.manager.model.Requirement" >
    <!--
      WARNING - @mbg.generated
    -->
    <id column="id" property="id" jdbcType="INTEGER" />
    <result column="user_id" property="userId" jdbcType="INTEGER" />
    <result column="hardware_type_id" property="hardwareTypeId" jdbcType="INTEGER" />
    <result column="req_file_id" property="reqFileId" jdbcType="INTEGER" />
    <result column="schedule" property="schedule" jdbcType="INTEGER"/>
    <result column="title" property="title" jdbcType="VARCHAR" />
    <result column="hardware_type_name" property="hardwareTypeName" jdbcType="VARCHAR" />
    <result column="hardware_connect" property="hardwareConnect" jdbcType="VARCHAR" />
    <result column="content" property="content" jdbcType="VARCHAR" />
    <result column="created" property="created" jdbcType="TIMESTAMP" />
    <result column="updated" property="updated" jdbcType="TIMESTAMP" />
  </resultMap>



  <resultMap id="allRequirement" type="com.official.manager.model.Requirement" >
    <!--
      WARNING - @mbg.generated
    -->
    <id column="id" property="id" jdbcType="INTEGER" />
    <id column="u_id" property="userId" jdbcType="INTEGER" />
    <result column="schedule" property="schedule" jdbcType="INTEGER"/>
    <result column="title" property="title" jdbcType="VARCHAR" />
    <result column="content" property="content" jdbcType="VARCHAR" />
    <result column="hardware_type_name" property="hardwareTypeName" jdbcType="VARCHAR" />
    <result column="hardware_connect" property="hardwareConnect" jdbcType="VARCHAR" />
    <result column="time" property="created" jdbcType="TIMESTAMP" />

      <association property="sysUser" javaType="com.official.manager.model.SysUser">
        <id column="user_id" property="id" jdbcType="INTEGER" />
            <result column="user_name2" jdbcType="VARCHAR" property="username" />
            <result column="user_company" jdbcType="VARCHAR" property="company" />
            <result column="user_email" jdbcType="VARCHAR" property="email" />
            <result column="user_tel" jdbcType="VARCHAR" property="tel" />
      </association>


  <collection property="cooperates" ofType="com.official.manager.model.Cooperate">

    <id column="c_id" property="id" jdbcType="INTEGER" />
    <result column="requirement_id" property="requirementId" jdbcType="INTEGER" />
    <result column="illustration" property="illustration" jdbcType="VARCHAR" />
    <result column="user_name" property="userName" jdbcType="VARCHAR" />
    <result column="created" property="created" jdbcType="TIMESTAMP" />

        <association property="user" javaType="com.official.manager.model.SysUser">
        <id column="s_id" property="id" jdbcType="INTEGER" />
            <result column="username" jdbcType="VARCHAR" property="username" />
            <result column="company" jdbcType="VARCHAR" property="company" />
            <result column="email" jdbcType="VARCHAR" property="email" />
            <result column="tel" jdbcType="VARCHAR" property="tel" />
        </association>
  </collection>

  </resultMap>

  <select id="getAllRequirements" resultMap="allRequirement">
  select 
  r.id,r.user_id u_id,  r.content,r.created time,r.hardware_connect,r.hardware_type_name,r.title,r.`schedule`,
  c.illustration,c.user_name,c.created,c.id c_id,
  s.username,s.company,s.email,s.tel,s.id s_id,
  user.username user_name2,user.company user_company,user.email user_email,user.tel user_tel
  from requirement r 
  left join cooperate c 
  on r.id=c.requirement_id
  left join sys_user s
  on c.user_id=s.id
  left join sys_user user
  on r.user_id=user.id
  </select>


</mapper>

两张表的查询结果一定不能出现相同的字段，如果有，需要使用别名加以区分；查询语句中的字段，在映射关系中一定要有。