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>
两张表的查询结果一定不能出现相同的字段,如果有,需要使用别名加以区分;查询语句中的字段,在映射关系中一定要有。