mybatis 关联查询如何
关联查询： 有job表 和 recruitment表 ，job表有个recruitment_id 字段 与 recruitment 表关联
job表结构
recruitment 表结构：
Job.java
@Data @Table(name = "t_job") public class Job{ /** * */ @ApiModelProperty(value = "") @Id @GeneratedValue(strategy = GenerationType.IDENTITY, generator = "JDBC") private Integer id; /** * 用户id */ @ApiModelProperty(value = "用户id") private String userId; /** * 发布岗位的id */ @ApiModelProperty(value = "发布岗位的id") private Integer recruitmentId; /** * 简历状态（0投递1被查看2沟通过3面试4不合适), */ @ApiModelProperty(value = "简历状态（0投递1被查看2沟通过3面试4不合适),") private Integer currentStatus; /** * 是否收藏0未收藏1收藏 */ @ApiModelProperty(value = "是否收藏0未收藏1收藏") private Boolean isLike; /** * 创建时间 */ @ApiModelProperty(value = "创建时间") @DateTimeFormat(pattern="yyyy-MM-dd HH:mm:ss") @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+08:00") private java.util.Date createTime; /** * 更新时间 */ @ApiModelProperty(value = "更新时间") @DateTimeFormat(pattern="yyyy-MM-dd HH:mm:ss") @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+08:00") private java.util.Date updateTime; /** * 面试时间 */ @ApiModelProperty(value = "面试时间") @DateTimeFormat(pattern="yyyy-MM-dd HH:mm:ss") @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+08:00") private java.util.Date faceTime; /** * 收藏时间 */ @ApiModelProperty(value = "收藏时间") @DateTimeFormat(pattern="yyyy-MM-dd HH:mm:ss") @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+08:00") private java.util.Date likeTime; /** * 0启用1禁用 */ @ApiModelProperty(value = "0启用1禁用") private Boolean status; /** * 是否删除 */ @ApiModelProperty(value = "是否删除") private Boolean deleteFlag; }
Recruitment.java
@Data @Table(name = "t_recruitment") public class Recruitment{ /** * */ @ApiModelProperty(value = "") @Id @GeneratedValue(strategy = GenerationType.IDENTITY, generator = "JDBC") private Integer id; /** * 公司id */ @ApiModelProperty(value = "公司id") private Integer companyId; /** * 公司id */ @ApiModelProperty(value = "公司名称") private String companyName; /** * 学历要求 */ @ApiModelProperty(value = "学历要求") private String educational; /** * 招聘标题 */ @ApiModelProperty(value = "招聘标题") private String title; /** * 工作详情 */ @ApiModelProperty(value = "工作详情") private String jobDesc; /** * 岗位需求 */ @ApiModelProperty(value = "岗位需求") private String jobRequirement; /** * 招聘人数 */ @ApiModelProperty(value = "招聘人数") private Integer needNumber; /** * 发布人 */ @ApiModelProperty(value = "发布人") private String publishPeople; /** * 发布时间 */ @ApiModelProperty(value = "发布时间") @DateTimeFormat(pattern="yyyy-MM-dd HH:mm:ss") @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+08:00") private java.util.Date publishTime; /** * 工作地 */ @ApiModelProperty(value = "工作地") private String workAddress; /** * 岗位诱惑 */ @ApiModelProperty(value = "岗位诱惑") private String welfare; /** * 省份id */ @ApiModelProperty(value = "省份id") private Integer provinceCode; /** * 城市id */ @ApiModelProperty(value = "城市id") private Integer cityCode; /** * 微信二维码 */ @ApiModelProperty(value = "微信二维码") private String wechatCode; /** * 经度 */ @ApiModelProperty(value = "经度") private String longitude; /** * 纬度 */ @ApiModelProperty(value = "纬度") private String latitude; /** * 0正常1关闭 */ @ApiModelProperty(value = "0正常1关闭") private Boolean status; /** * 创建时间 */ @ApiModelProperty(value = "创建时间") @DateTimeFormat(pattern="yyyy-MM-dd HH:mm:ss") @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+08:00") private java.util.Date createTime; /** * 更新时间 */ @ApiModelProperty(value = "更新时间") @DateTimeFormat(pattern="yyyy-MM-dd HH:mm:ss") @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+08:00") private java.util.Date updateTime; /** * 删除状态 */ @ApiModelProperty(value = "删除状态") private Boolean deleteFlag; /** * 最低薪资 */ @ApiModelProperty(value = "最低薪资") private Integer salaryMin; /** * 最高薪资 */ @ApiModelProperty(value = "最低薪资") private Integer salaryMax; /** * 行业id */ @ApiModelProperty(value = "行业id") private Integer industryId; /** * 行业名称 */ @ApiModelProperty(value = "行业名称") private String industryName; }
关联查询返回的出来的对象
@Data public class JobVo { private Job job; private Recruitment recruitment; }
JobMapper.java
public interface JobMapper extends BaseMapper<Job> { List<JobVo> findJobWithRecruitment(@Param("job") Job job); }
JobMapper.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"> <mapper namespace="cn.rc.mapper.JobMapper"> <resultMap type="cn.rc.api.message.vo.job.JobVo" id="JobWithRecruitmentMap"> <association property="job" column="recruitment_id" javaType="cn.rc.api.entity.Job"> <id property="id" column="id" /> <result property="userId" column="user_id" /> <result property="recruitmentId" column="recruitment_id" /> <result property="currentStatus" column="company_id" /> <result property="isLike" column="is_like" /> <result property="faceTime" column="face_time" /> <result property="createTime" column="create_time" /> <result property="updateTime" column="update_time" /> <result property="status" column="status" /> <result property="deleteFlag" column="delete_flag" /> </association> <association property="recruitment" column="recruitment_id" javaType="cn.rc.api.entity.Recruitment"> <id property="id" column="id" /> <result property="companyId" column="company_id" /> <result property="companyName" column="company_name" /> <result property="educational" column="educational" /> <result property="title" column="title" /> <result property="jobDesc" column="job_desc" /> <result property="jobRequirement" column="job_requirement" /> <result property="needNumber" column="need_number" /> <result property="publishPeople" column="publish_people" /> <result property="publishTime" column="publish_time" /> <result property="workAddress" column="work_address" /> <result property="welfare" column="welfare" /> <result property="provinceCode" column="province_code" /> <result property="cityCode" column="city_code" /> <result property="wechatCode" column="wechat_Code" /> <result property="wechatCode" column="wechat_Code" /> <result property="latitude" column="latitude" /> <result property="longitude" column="longitude" /> <result property="createTime" column="create_time" /> <result property="updateTime" column="update_time" /> <result property="status" column="status" /> <result property="deleteFlag" column="delete_flag" /> <result property="salaryMin" column="salary_min" /> <result property="salaryMax" column="salary_max" /> <result property="industryId" column="industry_id" /> <result property="industryName" column="industry_name" /> </association> </resultMap> <select id="findJobWithRecruitment" resultMap="JobWithRecruitmentMap"> SELECT job.*,recruitment.* FROM t_job job, t_recruitment recruitment WHERE job.recruitment_id = recruitment.id <if test="job.currentStatus != null "> AND job.current_status = #{job.currentStatus} </if> <if test="job.id != null"> AND job.id = #{job.id} </if> </select> </mapper>
具体的查询：
public Pagenation<Job, JobVo> findJobs(@RequestBody Pagenation<Job, JobVo> pagenation) { Page<Job> pageInfo = PageHelper.startPage(pagenation.getPageNum(), pagenation.getPageSize()); Job job = pagenation.getSelectData(); List<JobVo> jobList = jobMapper.findJobWithRecruitment(job); if (jobList == null || jobList.size() == 0) { pagenation.setTotal(0); return pagenation; } pagenation.setData(jobList); pagenation.setTotal(pageInfo.getTotal()); return pagenation; }
