mybatis-plus多表联查join的实现

目录

• 1、安装依赖
• 2、定义三个基本实体类
• 3、定义三个mapper
• 4、定义返回值dto
• 5、开发业务层
• 6、假如进行分页查询的话
• 7、注意：
• 8、其他
• 9、如果针对条件进行动态查询

1、安装依赖

<dependency>
            <groupId>com.github.yulichang</groupId>
            <artifactId>myBATis-plus-join</artifactId>
            <version>1.2.4</version>
        </dependency>

2、定义三个基本实体类

2.1 User类

@Data
public class User {
 //对应数据库的主键（uuid，自增id，雪花算法,Redis，zookper）
 @TableId(type = IdType.AUTO)
 private Integer id;

 @NotNull(message = "名称xxx不能为空")//空校验
 @NotBlank //不能为空字符串
 @Size(min = 1,max = 5,message = "ssssss")//字符串长度校验
 private String name;

// @Min(value = 10,message = "最小值10")
// @Max(value = 90,message = "最大值为99")

 @NotNull
// @Max(value = 50,message = "超过最大值")
// @Min(value = 10,message = "超过最小值")
 @Range(min=10,max = 99,message = "错误")//数字范围校验
 private Integer age;
 @NotNull
 private String email;


 //逻辑删除字段
 @TableLogic //逻辑删除注解
 private Integer deleted;

 //字段添加填充内容
 //策略
// @jsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
 @TableField(fill = FieldFill.INSERT)

 @DateTimeFormat(pattern = "yyyy-MM-dd")
 @JsonFormat(pattern = "yyyy-MM-dd", timezone = "GMT+8")
 private LocalDateTime createTime;
 //策略
// @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")

 @JsonFormat(pattern = "yyyy-MM-dd", timezone = "GMT+8")
 @TableField(fill = FieldFill.INSERT_UPDATE)
 private LocalDateTime updateTime;

 @TableField(fill = FieldFill.INSERT)
 private Integer createBy;

 @TableField(fill = FieldFill.INSERT_UPDATE)
 private Integer upda编程teBy;

 @Version//乐观锁注解
 private Integer version;

}

2.2 UserScore类

@Data
public class UserScore {
    @TableId(type = IdType.AUTO)
    private long id;
    private String course;
    private String score;
    private Long userId;
}

2.3 UserCourseTeacher类

@Data
public class UserCourspythoneTeacher {
  @TableId(type = IdType.AUTO)
  private Integer id;

  private Long teacher;

  private Long userCours开发者_开发入门eId;
}

3、定义三个mapper

3.1 userMapper

@Repository
@Mapper
public interface UserMapper extends MPJBaseMapper<User> {
}

3.2 UserScoreMapper

@Repository
@Mapper
public interface UserScoreMapper extends MPJBaseMapper<UserScore> {
}

3.3 UserCourseTeacherMapper

@Mapper
@Repository
public interface UserCourseTeacherMapper extends MPJBaseMapper<UserCourseTeacher> {
}

4、定义返回值dto

@Data
public class UserTeacherDto extends User {
    private String course;
    private String score;
    private String teacher;
}

5、开发业务层

例如对应的sxKDuXzql为

select a.*,b.score,b.course,c.teacher from user a left join user_score b on a.id=b.user_id left join user_course_teacher c on b.id=c.user_course_id

则代码为

public List<UserTeacherDto> GetAllUserTeacher(){
        MPJLambdaWrapper<User> mPJLambdaWrapper=new MPJLambdaWrapper<User>()
                .selectAll(User.class)
                .select(UserScore::getScore,UserScore::getCourse)
                .select(UserCourseTeacher::getTeacher)
                .leftJoin(UserScore.class,UserScore::getUserId,User::getId)
                .leftJoin(UserCourseTeacher.class,UserCourseTeacher::getUserCourseId,UserScore::getId);
        List<UserTeacherDto> list=userMapper.selectJoinList(UserTeacherDto.class, mPJLambdaWrapper);
        return list;
    }

6、假如进行分页查询的话

@Override
  public Map<String,Object> GetUserDtoByPage(){
    Map<String,Object> result=new HashMap<>();
    MPJLambdaWrapper<User> mPJLambdaWrapper=new MPJLambdaWrapper<User>()
        .selectAll(User.class)
        .select(UserScore::getScore,UserScore::getCourse)
        .leftJoin(UserScore.class,UserScore::getUserId,User::getId);

    IPage<UserDto> UserDtoPage=userMapythonpper.selectJoinPage(
        new Page<UserDto>(3,2),
        UserDto.class,
        mPJLambdaWrapper
    );
    result.put("list",UserDtoPage.getRecords());
    result.put("count",UserDtoPage.getTotal());
    return result;
  }

7、注意：

• selectAll()：查询指定实体类的全部字段
• select()：查询指定的字段，支持可变长参数同时查询多个字段，但是在同一个select中只能查询相同表的字段，所以如果查询多张表的字段需要分开写
• selectAs()：字段别名查询，用于数据库字段与接收结果的dto中属性名称不一致时转换
• leftJoin()：左连接，其中第一个参数是参与联表的表对应的实体类，第二个参数是这张表联表的ON字段，第三个参数是参与联表的ON的另一个实体类属性

8、其他

MPJLambdaWrapper<User> mPJLambdaWrapper=new MPJLambdaWrapper<User>()
                .selectAll(User.class)

中的User可以理解为主表

leftJoin函数参数顺序依php次为：子表的类型、子表的字段（实体类属性）、主表实体类的属性，就是用于on关联的

9、如果针对条件进行动态查询

@Override
    public List<UserTeacherDto> GetAllUserTeacher(Map<String,Long> params){
        System.out.println("传递参数");
        System.out.println(params.get("id"));
        MPJLambdaWrapper<User> mPJLambdaWrapper=new MPJLambdaWrapper<User>()
                .selectAll(User.class)
                .select(UserScore::getScore,UserScore::getCourse)
                .select(UserCourseTeacher::getTeacher)
                .leftJoin(UserScore.class,UserScore::getUserId,User::getId)
                .leftJoin(UserCourseTeacher.class,UserCourseTeacher::getUserCourseId,UserScore::getId);
        if(params.get("id")!=null && params.get("id").toString()!=""){
            mPJLambdaWrapper.eq(UserScore::getId,params.get("id"));
        }
        List<UserTeacherDto> list=userMapper.selectJoinList(UserTeacherDto.class, mPJLambdaWrapper);
        return list;
    }

到此这篇关于mybatis-plus多表联查join的实现的文章就介绍到这了,更多相关mybatis-plus多表联查内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！