mybatis-plus多表联查join的实现

时间:2023-02-03 spring-java 人气:0

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 updateBy;

  @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 UserCourseTeacher {
    @TableId(type = IdType.AUTO)
    private Integer id;

    private Long teacher;

    private Long userCourseId;
}

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、开发业务层

例如对应的sql为

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=userMapper.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函数参数顺序依次为：子表的类型、子表的字段（实体类属性）、主表实体类的属性，就是用于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;
    }

加载全部内容