mybatis-plus多表联查join的实现
作者:spring-java 时间:2023-11-24 06:49:56
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;
}
来源:https://blog.csdn.net/weixin_41463944/article/details/128637068
标签:mybatis-plus,多表联查
0
投稿
猜你喜欢
C# this关键字的四种用法
2022-10-06 07:09:22
Java微信公众平台开发(12) 微信用户信息的获取
2023-05-26 07:28:56
详解MyBatis直接执行SQL查询及数据批量插入
2021-12-02 17:52:08
IDEA导出jar打包成exe应用程序的小结
2023-06-22 04:02:46
SpringBoot中Dozer的使用小结
2023-11-25 01:24:38
java中ArrayList和LinkedList的区别详解
2022-09-05 11:13:48
java 发送http和https请求的实例
2023-11-29 12:46:52
Android 反射注解与动态代理综合使用详解
2023-01-13 12:30:56
java的Arrays工具类实战
2023-08-21 15:46:36
java生成随机数的方法
2023-12-12 12:49:51
C#中FileStream的对比及使用方法
2023-10-17 21:09:41
Java如何在临界区中避免竞态条件
2022-11-02 09:39:29
java 学习笔记(入门篇)_多选择结构switch语句
2023-02-04 11:35:59
Android 使用Vitamio打造自己的万能播放器(10)—— 本地播放 (缩略图、视频信息、视频扫描服务)
2023-07-11 08:48:22
Java StringBuilder和StringBuffer源码分析
2023-04-03 00:10:58
详解Eclipse 字体、字号的设置、最佳字体推荐
2023-11-26 12:25:32
java搜索无向图中两点之间所有路径的算法
2023-11-10 09:28:26
Android实现毛玻璃效果的对话框
2021-07-19 03:28:42
Java数组与堆栈相关知识总结
2023-11-12 06:12:18
c#线程同步使用详解示例
2021-06-15 19:22:58