SpringBoot+MybatisPlus实现关联表查询
1.说明
最近写代码用到了mybatisPlus涉及到关联表查询。需求是这样的：
我有一个专业表major其中有个字段是所属院系dept_id，我需要通过这个dept_id关联院系表department的ID去获取院系的名称。
例如：经济管理学院。
实现效果：
2.实现步骤
2.1 现有实体类
- 院系实体
@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
@TableName("tb_department")
@ApiModel(value="Department对象", description="院系")
public class Department implements Serializable {
private static final long serialVersionUID = 1L;
@ApiModelProperty(value = "院系ID")
@TableId(value = "id", type = IdType.AUTO)
private Long id;
@ApiModelProperty(value = "院系代码")
private String deptNo;
@ApiModelProperty(value = "院系名称")
private String deptName;
@ApiModelProperty(value = "院系位置")
private String deptLocation;
@ApiModelProperty(value = "备注")
private String deptRemark;
@ApiModelProperty(value = "创建时间")
@TableField(fill = FieldFill.INSERT)
private Date createTime;
@ApiModelProperty(value = "更新时间")
@TableField(fill = FieldFill.INSERT_UPDATE)
private Date updateTime;
}
- 专业实体
@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
@TableName("tb_major")
@ApiModel(value="Major对象", description="专业")
public class Major implements Serializable {
private static final long serialVersionUID = 1L;
@ApiModelProperty(value = "专业ID")
@TableId(value = "id", type = IdType.AUTO)
private Long id;
@ApiModelProperty(value = "专业代码")
private String majorNo;
@ApiModelProperty(value = "专业名称")
private String majorName;
@ApiModelProperty(value = "所属学院")
private Long deptId;
@ApiModelProperty(value = "备注")
private String majorRemark;
@ApiModelProperty(value = "创建时间")
@TableField(fill = FieldFill.INSERT)
private Date createTime;
@ApiModelProperty(value = "更新时间")
@TableField(fill = FieldFill.INSERT_UPDATE)
private Date updateTime;
}
2.2 创建vo对象
- MajorVO 前端列表展示用
@Data
@ApiModel(value = "MajorVO", description = "展示用专业信息")
public class MajorVO implements Serializable {
private static final long serialVersionUID = 1L;
@ApiModelProperty(value = "专业ID")
private Long id;
@ApiModelProperty(value = "专业代码")
private String majorNo;
@ApiModelProperty(value = "专业名称")
private String majorName;
@ApiModelProperty(value = "所属院系ID")
@TableField("dept_id")
private Long deptId;
@ApiModelProperty(value = "所属院系")
private String deptName;
@ApiModelProperty(value = "备注")
private String majorRemark;
@ApiModelProperty(value = "创建时间")
private Date createTime;
}
2.3 修改MajorMapper
public interface MajorMapper extends BaseMapper<Major> {
/**
*
* @param page 前端传入
* @return
*/
@Select("SELECT m.*,d.`dept_name` FROM tb_major m,tb_department d WHERE m.dept_id=d.id")
List<MajorVO> selectMajorList(Page page);
}
2.4 修改MajorService
public interface MajorService extends IService<Major> {
void pageQuery(Page<MajorVO> pageParam);
}
@Service
public class MajorServiceImpl extends ServiceImpl<MajorMapper, Major> implements MajorService {
@Override
public void pageQuery(Page<MajorVO> page) {
List<MajorVO> records = baseMapper.selectMajorList(page);
page.setRecords(records);
page.setTotal(records.size());
}
}
2.5 修改controller
@ApiOperation(value = "专业分页列表")
@GetMapping("{page}/{limit}")
public Result pageQuery(
@ApiParam(name = "page", value = "当前页码", required = true)
@PathVariable Long page,
@ApiParam(name = "limit", value = "每页记录数", required = true)
@PathVariable Long limit){
Page<MajorVO> pageParam = new Page<>(page, limit);
majorService.pageQuery(pageParam);
return Result.ok().data("total", pageParam.getTotal()).data("rows", pageParam.getRecords());
}
3.swagger测试
