mybatis-plus-多表查询

1.单靠mybatis-plus提供的crud方式是不能实现联表的复杂查询的 ;这也是plus相对与mybatis唯一的短板;

解决办法 ;还是需要引入xml写SQL语句的方式结合plus实现;

具体实现过程;

现在有两张表;一张年级表  一张学生表;

CREATE TABLE ;grade; (
  ;gradeid; int NOT NULL AUTO_INCREMENT COMMENT ;年级ID;,
  ;gradename; varchar(50) NOT NULL COMMENT ;年级名称;,
  PRIMARY KEY (;gradeid;)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb3;

CREATE TABLE ;student; (
  ;studentid; int NOT NULL COMMENT ;学号;,
  ;studentname; varchar(20) NOT NULL DEFAULT ;匿名; COMMENT ;姓名;,
  ;sex; tinyint(1) DEFAULT ;1; COMMENT ;性别;,
  ;gradeid; int DEFAULT NULL COMMENT ;年级;,
  ;phoneNum; varchar(50) NOT NULL COMMENT ;手机;,
  ;address; varchar(255) DEFAULT NULL COMMENT ;地址;,
  ;borndate; datetime DEFAULT NULL COMMENT ;生日;,
  ;email; varchar(50) DEFAULT NULL COMMENT ;邮箱;,
  ;idCard; varchar(18) DEFAULT NULL COMMENT ;身份证号;,
  PRIMARY KEY (;studentid;),
  KEY ;FK_gradeid; (;gradeid;),
  CONSTRAINT ;FK_gradeid; FOREIGN KEY (;gradeid;) REFERENCES ;grade; (;gradeid;)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

grade实体类

;Data
;EqualsAndHashCode(callSuper = false)
;Accessors(chain = true)
;ApiModel(value=;Grade对象;, description=;年级数据表;)
public class Grade implements Serializable {
 
    private static final long serialVersionUID=1L;
 
    ;ApiModelProperty(value = ;年级ID;)
    ;TableId(value = ;gradeid;, type = IdType.AUTO)
    private Integer gradeid;
 
    ;ApiModelProperty(value = ;年级名称;)
    private String gradename;
 
 
}

gradeVo类

;Data
public class GradeVO {
 
    private String gradename;//年级名称
 
    private String studentname;//学生姓名
 
    private String address;//住址
}

student实体类;

;Data
;EqualsAndHashCode(callSuper = false)
;Accessors(chain = true)
;ApiModel(value=;Student对象;, description=;学生数据表;)
public class Student implements Serializable {
 
    private static final long serialVersionUID=1L;
 
    ;ApiModelProperty(value = ;学号;)
    ;TableId(value = ;studentid;, type = IdType.ASSIGN_UUID)
    private Integer studentid;
 
    ;ApiModelProperty(value = ;姓名;)
    private String studentname;
 
    ;ApiModelProperty(value = ;性别;)
    private Boolean sex;
 
    ;ApiModelProperty(value = ;年级;)
    private Integer gradeid;
 
    ;ApiModelProperty(value = ;手机;)
    ;TableField(;phoneNum;)
    private String phoneNum;
 
    ;ApiModelProperty(value = ;地址;)
    private String address;
 
    ;ApiModelProperty(value = ;生日;)
    private Date borndate;
 
    ;ApiModelProperty(value = ;邮箱;)
    private String email;
 
    ;ApiModelProperty(value = ;身份证号;)
    ;TableField(;idCard;)
    private String idCard;
 
 
}

mappper层;

;Mapper
;Repository
public interface GradeMapper extends BaseMapper<Grade> {
    //与数据库交互
    IPage<GradeVO> findPage(IPage<GradeVO> page, ;Param(Constants.WRAPPER) QueryWrapper<GradeVO> wrapper);
}

xml里面的SQL语句;

<?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.xxx.xxx.mapper.GradeMapper;>
 
    <select id=;findPage; resultType=;com.xxx.xxx.entity.grade.GradeVO;>
 
       SELECT
       *
       FROM
       grade inner join student
       on
       grade.gradeid=student.gradeid
       ${ew.customSqlSegment}
 
      <!--<where>-->
          <!--${ew.SqlSegment} -->
      <!--</where>-->
    </select>
</mapper>

GradeService层;

public interface GradeService extends IService<Grade> {
 
    /**
     *联表分页查询xml
     * ;param page
     * ;param queryWrapper
     * ;return
     */
    IPage<GradeVO> findPage(Page<GradeVO> page, QueryWrapper<GradeVO> queryWrapper);
 
 
}

GradeServiceIpml接口实现类;

;Service
public class GradeServiceImpl extends ServiceImpl<GradeMapper, Grade> implements GradeService {
    ;Autowired
    private GradeMapper gradeMapper;
 
 
   ;Override
    public IPage<GradeVO> findPage(Page<GradeVO> page, QueryWrapper<GradeVO> queryWrapper) {
        return baseMapper.findPage(page, queryWrapper);
    }
}

GradeController层;

;RestController
;RequestMapping(;/grade;)
public class GradeController {
    ;Autowired
    private GradeService gradeService;
 
  /**
     * 复杂查询
     */
    ;GetMapping(;pageXml/{corund}/{limit};)
    public R pageXml(;PathVariable(;corund;)Integer corund,;PathVariable(;limit;)Integer limit){
       Page<GradeVO> page = new Page<>(corund,limit);
        IPage<GradeVO> GradePage = gradeService.findPage(page, new QueryWrapper<>());
        return R.ok().data(;GradePage;,GradePage);
    }
 
}

最后测试一下;

测试api接口;http://localhost:8080/grade/pageXml/1/5

来源;mybatis-plus 多表查询_;Jerry-的博客-CSDN博客_mybatis plus多表查询