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>

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