mybatis-plus 多表查询

灰太狼 2023-09-26 10:54 25阅读 0赞

#### 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语句][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\}  
  
  
  
  
</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][http_localhost_8080_grade_pageXml_1_5]

![05393beeebf44f2b9d4c3e641946e909.png][]

来源：[mybatis-plus 多表查询\_@Jerry-的博客-CSDN博客\_mybatis plus多表查询][mybatis-plus _Jerry-_-CSDN_mybatis plus]

[mybatis]: https://so.csdn.net/so/search?q=mybatis&spm=1001.2101.3001.7020
[xml]: https://so.csdn.net/so/search?q=xml&spm=1001.2101.3001.7020
[SQL]: https://so.csdn.net/so/search?q=SQL%E8%AF%AD%E5%8F%A5&spm=1001.2101.3001.7020
[http_localhost_8080_grade_pageXml_1_5]: http://localhost:8989/grade/pageXml/1/5
[05393beeebf44f2b9d4c3e641946e909.png]: https://img-blog.csdnimg.cn/05393beeebf44f2b9d4c3e641946e909.png
[mybatis-plus _Jerry-_-CSDN_mybatis plus]: https://blog.csdn.net/f234344435/article/details/124686351