Mybatis-Plus用纯注解搞定一对多查询
业务中很常见的用户-角色就属于典型的一对多关系. 假设我们需要将用户信息（包括了用户对应的角色信息）查询出来
数据表结构
user表和role表的关系为多对多，即一个user可以分配多个role，一个role可以属于多个user。
1.user表
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
`nickname` varchar(20) NOT NULL COMMENT '昵称',
`birthday` date NOT NULL COMMENT '生日',
`username` varchar(32) NOT NULL COMMENT '用户名',
`password` varchar(30) NOT NULL COMMENT '密码',
`status` varchar(2) NOT NULL DEFAULT '0' COMMENT '状态 ',
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB;
INSERT INTO `user` VALUES (1, 'lh', '2022-06-29', '李大', '123456', '0', '2022-06-29 09:02:07', '2022-06-29 09:02:07');
INSERT INTO `user` VALUES (2, 'ww', '2022-06-29', '陈大', '123456', '0', '2022-06-29 13:06:24', '2022-06-29 13:06:24');
INSERT INTO `user` VALUES (3, 'aa', '2022-06-29', '刘达', '123456', '0', '2022-06-29 13:06:48', '2022-06-29 13:06:48');
2.role表
CREATE TABLE `role` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
`name` varchar(20) NOT NULL COMMENT '角色名称',
`remark` varchar(50) DEFAULT NULL COMMENT '角色描述',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB;
INSERT INTO `role` VALUES (1, '系统管理员', '系统管理员');
INSERT INTO `role` VALUES (2, '数据管理员', '数据管理员');
3.user_role中间表
CREATE TABLE `user_role` (
`user_id` int(11) NOT NULL COMMENT '用户id',
`role_id` int(11) DEFAULT NULL COMMENT '角色id'
) ENGINE=InnoDB;
INSERT INTO `user_role` VALUES (1, 1);
INSERT INTO `user_role` VALUES (1, 2);
INSERT INTO `user_role` VALUES (2, 1);
INSERT INTO `user_role` VALUES (2, 2);
INSERT INTO `user_role` VALUES (3, 1);
实体类:
1.User类:
package wjw.test.mybatisplus.vo;
import java.io.Serializable;
import java.time.LocalDate;
import java.time.LocalDateTime;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
public class User implements Serializable {
private static final long serialVersionUID = 1L;
@TableId(
type = IdType.AUTO)
/**
* 主键
*/
private Integer id;
/**
* 昵称
*/
private String nickname;
/**
* 生日
*/
private LocalDate birthday;
/**
* 用户名
*/
private String username;
/**
* 密码
*/
private String password;
/**
* 状态
*/
private String status;
/**
* 创建时间
*/
private LocalDateTime createTime;
/**
* 更新时间
*/
private LocalDateTime updateTime;
public User() {
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getNickname() {
return nickname;
}
public void setNickname(String nickname) {
this.nickname = nickname;
}
public LocalDate getBirthday() {
return birthday;
}
public void setBirthday(LocalDate birthday) {
this.birthday = birthday;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getStatus() {
return status;
}
public void setStatus(String status) {
this.status = status;
}
public LocalDateTime getCreateTime() {
return createTime;
}
public void setCreateTime(LocalDateTime createTime) {
this.createTime = createTime;
}
public LocalDateTime getUpdateTime() {
return updateTime;
}
public void setUpdateTime(LocalDateTime updateTime) {
this.updateTime = updateTime;
}
@Override
public String toString() {
StringBuilder builder = new StringBuilder();
builder.append("User [id=");
builder.append(id);
builder.append(", nickname=");
builder.append(nickname);
builder.append(", birthday=");
builder.append(birthday);
builder.append(", username=");
builder.append(username);
builder.append(", password=");
builder.append(password);
builder.append(", status=");
builder.append(status);
builder.append(", createTime=");
builder.append(createTime);
builder.append(", updateTime=");
builder.append(updateTime);
builder.append("]");
return builder.toString();
}
}
2.Role类:
package wjw.test.mybatisplus.vo;
import java.io.Serializable;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
public class Role implements Serializable {
private static final long serialVersionUID = 1L;
@TableId(
type = IdType.AUTO)
/**
* 主键id
*/
private Integer id;
/**
* 角色名称
*/
private String name;
/**
* 角色描述
*/
private String remark;
public Role() {
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getRemark() {
return remark;
}
public void setRemark(String remark) {
this.remark = remark;
}
@Override
public String toString() {
StringBuilder builder = new StringBuilder();
builder.append("Role [id=");
builder.append(id);
builder.append(", name=");
builder.append(name);
builder.append(", remark=");
builder.append(remark);
builder.append("]");
return builder.toString();
}
}
3.包含一对多关系的UserVo类:
package wjw.test.mybatisplus.vo;
import java.time.LocalDate;
import java.util.List;
public class UserVo {
/**
* 用户编号
*/
private Integer id;
/**
* 昵称
*/
private String nick_Name;
/**
* 用户名
*/
private String username;
/**
* 生日
*/
private LocalDate birthday;
private List<Role> roles;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getNick_Name() {
return nick_Name;
}
public void setNick_Name(String nick_Name) {
this.nick_Name = nick_Name;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public LocalDate getBirthday() {
return birthday;
}
public void setBirthday(LocalDate birthday) {
this.birthday = birthday;
}
public List<Role> getRoles() {
return roles;
}
public void setRoles(List<Role> roles) {
this.roles = roles;
}
public UserVo() {
super();
}
@Override
public String toString() {
StringBuilder builder = new StringBuilder();
builder.append("UserVo [id=");
builder.append(id);
builder.append(", nick_Name=");
builder.append(nick_Name);
builder.append(", username=");
builder.append(username);
builder.append(", birthday=");
builder.append(birthday);
builder.append(", roles=");
builder.append(roles);
builder.append("]");
return builder.toString();
}
}
Mapper类:
1.RoleMapper:
package wjw.test.mybatisplus.mapper;
import java.util.List;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import wjw.test.mybatisplus.vo.Role;
@Mapper
public interface RoleMapper extends BaseMapper<Role> {
/**
* 根据指定的userId去user和user_role关联表查出该user所属role列表
* @param userId
* @return
*/
@Select("select * from role left join user_role on role.id=user_role.role_id where user_role.user_id=#{userId}")
List<Role> getListByUserId(Integer userId);
}
2.UserMapper:
package wjw.test.mybatisplus.mapper;
import java.util.List;
import org.apache.ibatis.annotations.Many;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import wjw.test.mybatisplus.vo.User;
import wjw.test.mybatisplus.vo.UserVo;
@Mapper
public interface UserMapper extends BaseMapper<User> {
//OneToMany一对多查询
@Select("select * from user where ${ew.sqlSegment}")
@Results({@Result(column = "id",property = "id"), // <1>
@Result(column = "nickname",property = "nick_Name"),
@Result(column = "id" /* 这里的id是要传递给下面查询里需要的参数 */, //<2>
property = "roles" /* 这里是UserVo类里的roles字段 */,many=@Many(
select = "wjw.test.mybatisplus.mapper.RoleMapper.getListByUserId"
))
})
List<UserVo> getList(@Param("ew") QueryWrapper wrapper);
}
<1>:
@Result(column=“id”,property=“Id”),这个可以不写，也不会报错，但是会导致我们查询结果列表里每个UVO 实体的Id等属性没有值。<2>: 这个@Result中
column代表子查询关联的主表中的列名，即子查询和主查询之间的关联关系。
property，代表子查询的查询结果关联的实体属性，就是UserVo中的roles字段。
select="wjw.test.mybatisplus.mapper.RoleMapper.getListByUserId"代表使用的子查询方法，就是RoleMapper类里的getListByUserId()方法。 意思就是查询主表，并将主表的id这个列和子查询关联起来，将id的值作为查询条件传入子查询中，子查询返回的结果存入到属性roles中.
测试
package wjw.test.mybatisplus;
import java.util.List;
import org.junit.jupiter.api.AfterEach;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.boot.test.context.SpringBootTest.WebEnvironment;
import org.springframework.test.context.ActiveProfiles;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import wjw.test.mybatisplus.mapper.RoleMapper;
import wjw.test.mybatisplus.mapper.UserMapper;
import wjw.test.mybatisplus.vo.User;
import wjw.test.mybatisplus.vo.UserVo;
@SpringBootTest(webEnvironment = WebEnvironment.NONE)
public class MybatisPlusTests1 {
@Autowired
private UserMapper userMapper;
@Autowired
private RoleMapper roleMapper;
public MybatisPlusTests1() {
//
}
@BeforeEach
public void setup() {
//
}
@AfterEach
public void stop() {
}
@Test
public void getUserInfo(){
QueryWrapper<User> wrapper=new QueryWrapper();
wrapper.in("id",1,2);
List<UserVo> list = userMapper.getList(wrapper);
System.out.println(list);
}
}
输出结果:
wjw.test.mybatisplus.MybatisPlusTests1 - [logStarted,61] - Started MybatisPlusTests1 in 2.855 seconds (JVM running for 3.885)
wjw.test.mybatisplus.mapper.UserMapper.getList - [debug,137] - ==> Preparing: select * from user where (id IN (?,?))
wjw.test.mybatisplus.mapper.UserMapper.getList - [debug,137] - ==> Parameters: 1(Integer), 2(Integer)
wjw.test.mybatisplus.mapper.UserMapper.getList - [trace,143] - <== Columns: id, nickname, birthday, username, password, status, create_time, update_time
wjw.test.mybatisplus.mapper.UserMapper.getList - [trace,143] - <== Row: 1, lh, 2022-06-29, 李大, 123456, 0, 2022-06-29 09:02:07.0, 2022-06-29 09:02:07.0
w.t.mybatisplus.mapper.RoleMapper.getListByUserId - [debug,137] - ====> Preparing: select * from role left join user_role on role.id=user_role.role_id where user_role.user_id=?
w.t.mybatisplus.mapper.RoleMapper.getListByUserId - [debug,137] - ====> Parameters: 1(Integer)
w.t.mybatisplus.mapper.RoleMapper.getListByUserId - [trace,143] - <==== Columns: id, name, remark, user_id, role_id
w.t.mybatisplus.mapper.RoleMapper.getListByUserId - [trace,143] - <==== Row: 1, 系统管理员, 系统管理员, 1, 1
w.t.mybatisplus.mapper.RoleMapper.getListByUserId - [trace,143] - <==== Row: 2, 数据管理员, 数据管理员, 1, 2
w.t.mybatisplus.mapper.RoleMapper.getListByUserId - [debug,137] - <==== Total: 2
wjw.test.mybatisplus.mapper.UserMapper.getList - [trace,143] - <== Row: 2, ww, 2022-06-29, 陈大, 123456, 0, 2022-06-29 13:06:24.0, 2022-06-29 13:06:24.0
w.t.mybatisplus.mapper.RoleMapper.getListByUserId - [debug,137] - ====> Preparing: select * from role left join user_role on role.id=user_role.role_id where user_role.user_id=?
w.t.mybatisplus.mapper.RoleMapper.getListByUserId - [debug,137] - ====> Parameters: 2(Integer)
w.t.mybatisplus.mapper.RoleMapper.getListByUserId - [trace,143] - <==== Columns: id, name, remark, user_id, role_id
w.t.mybatisplus.mapper.RoleMapper.getListByUserId - [trace,143] - <==== Row: 1, 系统管理员, 系统管理员, 2, 1
w.t.mybatisplus.mapper.RoleMapper.getListByUserId - [trace,143] - <==== Row: 2, 数据管理员, 数据管理员, 2, 2
w.t.mybatisplus.mapper.RoleMapper.getListByUserId - [debug,137] - <==== Total: 2
wjw.test.mybatisplus.mapper.UserMapper.getList - [debug,137] - <== Total: 2
[UserVo [id=1, nick_Name=lh, username=李大, birthday=2022-06-29, roles=[Role [id=1, name=系统管理员, remark=系统管理员], Role [id=2, name=数据管理员, remark=数据管理员]]], UserVo [id=2, nick_Name=ww, username=陈大, birthday=2022-06-29, roles=[Role [id=1, name=系统管理员, remark=系统管理员], Role [id=2, name=数据管理员, remark=数据管理员]]]]
将user的id=1,id=2传入，得到结果如下：
[UserVo [id=1, nick_Name=lh, username=李大, birthday=2022-06-29, roles=[Role [id=1, name=系统管理员, remark=系统管理员], Role [id=2, name=数据管理员, remark=数据管理员]]], UserVo [id=2, nick_Name=ww, username=陈大, birthday=2022-06-29, roles=[Role [id=1, name=系统管理员, remark=系统管理员], Role [id=2, name=数据管理员, remark=数据管理员]]]]
可以看出表中id为1和2的user信息和其对应的角色信息都已经查询出来了。