亲宝软件园·资讯

展开

mybatis-plus多表联查join的实现

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、注意:

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

加载全部内容

相关教程
猜你喜欢
用户评论