mybatis-plus 多表查询

@Jerry-

已于 2022-10-21 14:03:45 修改

阅读量2.3w

点赞数 14

文章标签： sql 数据库 xml java

于 2022-05-10 13:56:01 首次发布

本文链接：https://blog.csdn.net/f234344435/article/details/124686351

版权

本文探讨了如何利用Mybatis-Plus结合XML映射文件，解决在进行复杂联表查询时的不足。通过实例演示了年级和学生表的查询，并展示了如何在Mapper接口、XML文件和Service中配合使用，以获取年级和学生信息的Page对象。

摘要由CSDN通过智能技术生成

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);
    }

}