[Java知识库]什么？mybatis-plus的多表查询，你还在写sql语句？！【CRUD2】多表联查的三种方式

1. 一篇朴实无华的MyBatis-Plus小白看完原地进阶的文章
2. 【CRUD】番外篇
3. 此专栏下其他文章

数据表
practice_user：

practice_teacher：

公用部分：

1. VO

@Data
public class TeacherAndUserVo extends PracticeTeacher{

    @ApiModelProperty(value = "User名字")
    private String name;

    @ApiModelProperty(value = "User性别")
    private String sex;

}

2. PracticeTeacherService

/**
 * 通过sql多表查询
 * @return
 */
List<TeacherAndUserVo> selectTeacherAndUserVoBySql();
/**
 * 通过foreach多表查询
 * @return
 */
List<TeacherAndUserVo> selectTeacherAndUserVoByForeach();
/**
 * 通过stream多表查询
 * @return
 */
List<TeacherAndUserVo> selectTeacherAndUserVoByStream();

一、SQL

1. PracticeTeacherMapper.xml

<select id="selectTeacherAndUserVoBySql" resultType="eamon.daily.practice.teacher.entity.vo.TeacherAndUserVo">
    SELECT
    `user`.`name`,
    `user`.sex,
    teacher.*
    FROM
    `practice_teacher` teacher
    LEFT JOIN practice_user `user` ON teacher.user_id = `user`.id
    where `user`.sex="女"
</select>

2. PracticeTeacherMapper.java

/**
 * 通过sql多表查询
 * @return
 */
List<TeacherAndUserVo> selectTeacherAndUserVoBySql();

3. PracticeTeacherServiceImpl.java

@Resource
PracticeTeacherMapper practiceTeacherMapper;

@Override
public List<TeacherAndUserVo> selectTeacherAndUserVoBySql() {
    return practiceTeacherMapper.selectTeacherAndUserVoBySql();
}

4. 结果：

二、链式调用 lambda 式，lambdaQuery()+foreach

（不了解lambdaQuery()的小伙伴见：【CRUD1】简单单表查询）

1. PracticeTeacherServiceImpl.java

@Autowired
IPracticeTeacherService practiceTeacherService;

@Autowired
IPracticeUserService practiceUserService;

@Override
public List<TeacherAndUserVo> selectTeacherAndUserVoByForeach() {

    //查询所有Teacher
    List<PracticeTeacher> teachers = practiceTeacherService.lambdaQuery().list();

    ArrayList<TeacherAndUserVo> teacherAndUserVos = new ArrayList<>();

    for (PracticeTeacher teacher : teachers) {
        //查询符合条件的User表中的女性
        PracticeUser woman = practiceUserService.lambdaQuery()
                .eq(PracticeUser::getSex, "女")
                .eq(PracticeUser::getId, teacher.getUserId())
                .one();
        if(woman!=null){
            //将当前teacher对象拷贝到teacherAndUserVo中
            TeacherAndUserVo copy = CopyUtil.copy(teacher, TeacherAndUserVo.class);
            //将User姓名及性别放到teacherAndUserVo中
            copy.setName(woman.getName());
            copy.setSex(woman.getSex());
            teacherAndUserVos.add(copy);
        }
    }
    return teacherAndUserVos;
}

2. 结果

三、stream

1. PracticeTeacherServiceImpl.java

@Override
    public List<TeacherAndUserVo> selectTeacherAndUserVoByStream() {
        //查询所有teacher对象
        List<PracticeTeacher> teachers = practiceTeacherService.lambdaQuery().list();
        //查询女生用户
        List<PracticeUser> womans = practiceUserService.lambdaQuery().eq(PracticeUser::getSex, "女").list();
        List<TeacherAndUserVo> teacherAndUserVos = womans.stream().map(
                o -> {
                    //过滤符合要求的对象
                    TeacherAndUserVo vo = new TeacherAndUserVo();
                    for (PracticeTeacher teacher : teachers) {
                        if (teacher.getUserId().longValue() == o.getId()) {
                            vo = CopyUtil.copy(teacher, TeacherAndUserVo.class);
                            vo.setName(o.getName());
                            vo.setSex(o.getSex());
                        }
                    }
                    return vo;
                })
                .filter(x -> x.getName() != null)//过滤空对象
                .collect(Collectors.toList());
        return teacherAndUserVos;
    }

这个例子不是很恰当，小名只是为了给大家演示一下lambda的用法。

2. 结果

如果觉得小名的文章帮助到了您，请关注小名的新专栏 MyBatis-Plus【CRUD】，支持一下小名😄，给小名的文章点赞👍、评论?、收藏🤞谢谢大家啦~???

关注博主即可阅读全文