什么？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】，支持一下小名😄，給小名的文章點贊👍、評論?、收藏🤞謝謝大家啦~???