最近自己做项目碰到一个需求:
不同的用户登录系统后根据用户的角色显示不同的菜单,一个用户可以拥有多个不同的角色,一个角色可以对应多个用户,这就是典型的----------多对多关系
表:
用户表
权限表
中间表
这里我的项目中已经引入了Ebean环境,不会的可以参照我的另一篇博文:
springboot整合ebean
这里中间表设计有两点需要注意
1、中间表的表名必须是:多对多映射关系的两张表的表名以“_”连接,并且是主表表名在后,如:用户表(user)和角色表(role)的中间表表名应该是role_user,而不是user_role,更不能是其他名字
2、中间表的字段名必须是:表名_字段名,如:中间表role_user的字段名不是简单的uid和rid,而应该是user_uid和role_rid,必须要以所在表的表名加下划线开头
Ebean注解式开发实体类的编写:
用户实体类:
import io.ebean.annotation.DbComment;
import javax.persistence.*;
import java.util.HashSet;
import java.util.List;
import java.util.Set;
@Entity
@Table(name = "user")
@DbComment("用户表")
public class User {
@Id
@Column(name = "uid", nullable = false)
@DbComment("唯一id")
private Integer uid;
@Column(name = "username")
@DbComment("用户名")
private String username;
@Column(name = "password")
@DbComment("密码")
private String password;
@ManyToMany(mappedBy = "users",cascade = CascadeType.ALL)
List<Role> roles;
public Integer getUid() {
return uid;
}
public void setUid(Integer uid) {
this.uid = uid;
}
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 List<Role> getRoles() {
return roles;
}
public void setRoles(List<Role> roles) {
this.roles = roles;
}
@Override
public String toString() {
return "User{" +
"uid=" + uid +
", username='" + username + '\'' +
", password='" + password + '\'' +
", roles=" + roles +
'}';
}
}
上面用户实体类中以下这两行是重点:
@ManyToMany(mappedBy = “users”,cascade = CascadeType.ALL)
List roles;
其中mappedBy = "users"和角色实体类中的用户维护相呼应。
角色实体类:
import io.ebean.annotation.DbComment;
import javax.persistence.*;
import java.util.HashSet;
import java.util.List;
import java.util.Set;
@Entity
@Table(name = "role")
@DbComment("角色表")
public class Role {
@Id
@Column(name = "rid", nullable = false)
@DbComment("唯一id")
private Integer rid;
@Column(name = "name")
@DbComment("角色名")
private String name;
@ManyToMany(cascade = CascadeType.ALL)
List<User> users;
public Integer getRid() {
return rid;
}
public void setRid(Integer rid) {
this.rid = rid;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public List<User> getUsers() {
return users;
}
public void setUsers(List<User> users) {
this.users = users;
}
@Override
public String toString() {
return "Role{" +
"rid=" + rid +
", name='" + name + '\'' +
", users=" + users +
'}';
}
}
上面角色实体类中以下这两行是重点:
@ManyToMany(cascade = CascadeType.ALL)
List users;
Controller写接口测试
import com.slm.shirotest.project.model.Role;
import com.slm.shirotest.project.model.User;
import io.ebean.Ebean;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import java.util.List;
@Controller
@RequestMapping("test")
public class TestController {
@RequestMapping("page1")
public String page1(){
List<User> list = Ebean.find(User.class).findList();
List<Role> roles = list.get(0).getRoles();
System.out.println(list.get(0).getUsername());
System.out.println(roles.get(0).getName());
return "page1";
}
}
测试结果(插入的数据比较少):
上面两点黄色的提示只要有一点做不到,就会碰到类似下面的提示,这种提示只有在断点调试模式下才会看到,正常运行报的错误是看不懂的,很浪费时间,比较坑,所以建议一定要按照黄色提示部分的两点去设计多对多关系中的中间表,很重要!!!
贴上我的数据库脚本:
-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`uid` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(255) NOT NULL DEFAULT '',
`password` varchar(255) DEFAULT '',
PRIMARY KEY (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', 'tom', 'tom');
INSERT INTO `user` VALUES ('2', 'jack', 'jack');
-- ----------------------------
-- Table structure for role
-- ----------------------------
DROP TABLE IF EXISTS `role`;
CREATE TABLE `role` (
`rid` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL DEFAULT '',
PRIMARY KEY (`rid`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of role
-- ----------------------------
INSERT INTO `role` VALUES ('1', '超级管理员');
INSERT INTO `role` VALUES ('2', '管理员');
INSERT INTO `role` VALUES ('3', '普通用户');
-- ----------------------------
-- Table structure for role_user
-- ----------------------------
DROP TABLE IF EXISTS `role_user`;
CREATE TABLE `role_user` (
`role_rid` int(11) NOT NULL,
`user_uid` int(11) NOT NULL,
KEY `idx_rid` (`role_rid`),
KEY `idx_uid` (`user_uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of role_user
-- ----------------------------
INSERT INTO `role_user` VALUES ('1', '1');
INSERT INTO `role_user` VALUES ('2', '2');
INSERT INTO `role_user` VALUES ('3', '2');