mybatis-plus多表联查join的实现
短信预约 -IT技能 免费直播动态提醒
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;
}到此这篇关于mybatis-plus多表联查join的实现的文章就介绍到这了,更多相关mybatis-plus多表联查内容请搜索编程网以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程网！
免责声明：
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
