Spring data 数据库建表(一对一,一对多,多对多)
2017-07-25 景峯 Netkiller
本文节选择电子书《Netkiller Java 手札》
DBA 如果你在不学习编程,你将失业。如今我们对DBA的依赖越来越少,多数框架都支持实体关系映射,通过面向对象编程即可定义数据库结构。数据库设计也是在这个阶段完成的,不再需要DBA协助。如果DBA不学习怎样编写实体Class 最终将被淘汰。所以DBA除了会使用 ER图工具,还需要会写实体类。
8.3.1.9. @OneToOne
一对一表结构,如下面ER图所示,users表是用户表里面有登陆信息,profile 保存的时死人信息,这样的目的是我们尽量减少users表的字段,在频繁操作该表的时候性能比较好,另外一个目的是为了横向水平扩展。
+----------+ +------------+ | users | | profile | +----------+ +------------+ | id | <---1:1---o | id | | name | | sex | | password | | email | +----------+ +------------+
package com.example.api.domain.test; import java.io.Serializable; import javax.persistence.Entity; import javax.persistence.GeneratedValue; import javax.persistence.GenerationType; import javax.persistence.Id; import javax.persistence.Table; @Entity @Table(name = "users") public class Users implements Serializable { @Id @GeneratedValue(strategy = GenerationType.AUTO) private int id; private String name; private String password; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getPassword() { return password; } public void setPassword(String password) { this.password = password; } @Override public String toString() { return "Users [id=" + id + ", name=" + name + ", password=" + password + "]"; } }
package com.example.api.domain.test; import java.io.Serializable; import javax.persistence.Entity; import javax.persistence.Id; import javax.persistence.JoinColumn; import javax.persistence.OneToOne; import javax.persistence.Table; @Entity @Table(name = "profile") public class Profile implements Serializable { /** * */ private static final long serialVersionUID = -2500499458196257167L; @Id @OneToOne @JoinColumn(name = "id") private Users users; private int age; private String sex; private String email; public Users getUsers() { return users; } public void setUsers(Users users) { this.users = users; } public int getAge() { return age; } public void setAge(int age) { this.age = age; } public String getSex() { return sex; } public void setSex(String sex) { this.sex = sex; } public String getEmail() { return email; } public void setEmail(String email) { this.email = email; } @Override public String toString() { return "Profile [users=" + users + ", age=" + age + ", sex=" + sex + ", email=" + email + "]"; } }
CREATE TABLE `users` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `name` VARCHAR(255) NULL DEFAULT NULL, `password` VARCHAR(255) NULL DEFAULT NULL, PRIMARY KEY (`id`) ) COLLATE='utf8_general_ci' ENGINE=InnoDB; CREATE TABLE `profile` ( `age` INT(11) NOT NULL, `email` VARCHAR(255) NULL DEFAULT NULL, `sex` VARCHAR(255) NULL DEFAULT NULL, `id` INT(11) NOT NULL, PRIMARY KEY (`id`), CONSTRAINT `FK6x079ilawxjrfsljwyyi5ujjq` FOREIGN KEY (`id`) REFERENCES `users` (`id`) ) COLLATE='utf8_general_ci' ENGINE=InnoDB;
如果第二张表关联的并非主表的PK(主键)需要使用 referencedColumnName 指定。
@JoinColumn(name = "member_id",referencedColumnName="member_id")
8.3.1.10. OneToMany 一对多
我们要实现一个一对多实体关系,ER 图如下
+----------+ +------------+ | Classes | | Student | +----------+ +------------+ | id | <---+ | id | | name | | | name | +----------+ +--o | classes_id | +------------+
classes 表需要 OneToMany 注解,Student 表需要 ManyToOne 注解,这样就建立起了表与表之间的关系
package com.example.api.domain.test; import java.io.Serializable; import java.util.Set; import javax.persistence.CascadeType; import javax.persistence.Entity; import javax.persistence.GeneratedValue; import javax.persistence.GenerationType; import javax.persistence.Id; import javax.persistence.OneToMany; import javax.persistence.Table; @Entity @Table(name="classes") public class Classes implements Serializable{ /** * */ private static final long serialVersionUID = -5422905745519948312L; @Id @GeneratedValue(strategy=GenerationType.AUTO) private int id; private String name; @OneToMany(cascade=CascadeType.ALL,mappedBy="classes") private Set<Student> students; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public Set<Student> getStudents() { return students; } public void setStudents(Set<Student> students) { this.students = students; } @Override public String toString() { return "classes [id=" + id + ", name=" + name + ", students=" + students + "]"; } }
package com.example.api.domain.test; import java.io.Serializable; import javax.persistence.CascadeType; import javax.persistence.Entity; import javax.persistence.GeneratedValue; import javax.persistence.GenerationType; import javax.persistence.Id; import javax.persistence.JoinColumn; import javax.persistence.ManyToOne; import javax.persistence.Table; @Entity @Table(name = "student") public class Student implements Serializable{ /** * */ private static final long serialVersionUID = 6737037465677800326L; @Id @GeneratedValue(strategy = GenerationType.AUTO) private int id; private String name; // 若有多个cascade,可以是:{CascadeType.PERSIST,CascadeType.MERGE} @ManyToOne(cascade = { CascadeType.ALL }) @JoinColumn(name = "classes_id") private Classes classes; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public Classes getClasses() { return classes; } public void setClasses(Classes classes) { this.classes = classes; } @Override public String toString() { return "Student [id=" + id + ", name=" + name + ", classes=" + classes + "]"; } }
最终 SQL 表如下
CREATE TABLE `classes` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `name` VARCHAR(255) NULL DEFAULT NULL, PRIMARY KEY (`id`) ) COLLATE='utf8_general_ci' ENGINE=InnoDB; CREATE TABLE `student` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `name` VARCHAR(255) NULL DEFAULT NULL, `class_id` INT(11) NULL DEFAULT NULL, PRIMARY KEY (`id`), INDEX `FKnsl7w2nw6o6eq53hqlxfcijpm` (`class_id`), CONSTRAINT `FKnsl7w2nw6o6eq53hqlxfcijpm` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`) ) COLLATE='utf8_general_ci' ENGINE=InnoDB;
Classes classes=new Classes(); classes.setName("One"); Student st1=new Student(); st1.setSname("jason"); st1.setClasses(classes); studentRepostitory.save(st1); Student st2=new Student(); st2.setSname("neo"); st2.setClasses(classes); studentRepostitory.save(st2);
8.3.1.11. ManyToMany 多对多
用户与角色就是一个多对多的关系,多对多是需要中间表做关联的。所以我方需要一个 user_has_role 表。
+----------+ +---------------+ +--------+ | users | | user_has_role | | role | +----------+ +---------------+ +--------+ | id | <------o | user_id | /---> | id | | name | | role_id | o---+ | name | | password | | | | | +----------+ +---------------+ +--------+
创建 User 表
package com.example.api.domain.test; import java.io.Serializable; import java.util.Set; import javax.persistence.Entity; import javax.persistence.FetchType; import javax.persistence.GeneratedValue; import javax.persistence.GenerationType; import javax.persistence.Id; import javax.persistence.JoinTable; import javax.persistence.ManyToMany; import javax.persistence.Table; import javax.persistence.JoinColumn; @Entity @Table(name = "users") public class Users implements Serializable { /** * */ private static final long serialVersionUID = -2480194112597046349L; @Id @GeneratedValue(strategy = GenerationType.AUTO) private int id; private String name; private String password; @ManyToMany(fetch = FetchType.EAGER) @JoinTable(name = "user_has_role", joinColumns = { @JoinColumn(name = "user_id", referencedColumnName = "id") }, inverseJoinColumns = { @JoinColumn(name = "role_id", referencedColumnName = "id") }) private Set<Roles> roles; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getPassword() { return password; } public void setPassword(String password) { this.password = password; } public Set<Roles> getRoles() { return roles; } public void setRoles(Set<Roles> roles) { this.roles = roles; } @Override public String toString() { return "Users [id=" + id + ", name=" + name + ", password=" + password + ", roles=" + roles + "]"; } }
创建 Role 表
package com.example.api.domain.test; import java.io.Serializable; import java.util.Set; import javax.persistence.Entity; import javax.persistence.GeneratedValue; import javax.persistence.GenerationType; import javax.persistence.Id; import javax.persistence.ManyToMany; import javax.persistence.Table; @Entity @Table(name = "roles") public class Roles implements Serializable { private static final long serialVersionUID = 6737037465677800326L; @Id @GeneratedValue(strategy = GenerationType.AUTO) private int id; private String name; @ManyToMany(mappedBy = "roles") private Set<Users> users; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public Set<Users> getUsers() { return users; } public void setUsers(Set<Users> users) { this.users = users; } @Override public String toString() { return "Roles [id=" + id + ", name=" + name + ", users=" + users + "]"; } }
最终产生数据库表如下
CREATE TABLE `users` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `name` VARCHAR(255) NULL DEFAULT NULL, `password` VARCHAR(255) NULL DEFAULT NULL, PRIMARY KEY (`id`) ) COLLATE='utf8_general_ci' ENGINE=InnoDB; CREATE TABLE `roles` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `name` VARCHAR(255) NULL DEFAULT NULL, PRIMARY KEY (`id`) ) COLLATE='utf8_general_ci' ENGINE=InnoDB; CREATE TABLE `user_has_role` ( `user_id` INT(11) NOT NULL, `role_id` INT(11) NOT NULL, PRIMARY KEY (`user_id`, `role_id`), INDEX `FKsvvq61v3koh04fycopbjx72hj` (`role_id`), CONSTRAINT `FK2dl1ftxlkldulcp934i3125qo` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`), CONSTRAINT `FKsvvq61v3koh04fycopbjx72hj` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ) COLLATE='utf8_general_ci' ENGINE=InnoDB;
Donations (打赏)
We accept PayPal through:
https://www.paypal.me/netkiller
Wechat (微信) / Alipay (支付宝) 打赏:
http://www.netkiller.cn/home/donations.html
作者相关文章:
Apache Sqoop 将mysql导入到Hadoop HDFS
CentOS 7.3 + Server JRE 1.8 + Hadoop-2.8.0
Spring boot with Schedule (启用/禁用)
《Netkiller Virtualization 手札》Docker 卷管理
Struts2 S2-046, S2-045 Firewall(漏洞防火墙)
转载请注明出处与作者声明,扫描二维码关注作者公众好,不定期更新文章