Spring Data with MySQL多维度架构 - 知乎www.zhihu.com
OneToMany 一对多
我们要实现一个一对多实体关系,ER 图如下
+----------+ +------------+
| Classes | | Student |
+----------+ +------------+
| id |
| name | | | name |
+----------+ +--o | classes_id |
+------------+
classes 表需要 OneToMany 注解,Student 表需要 ManyToOne 注解,这样就建立起了表与表之间的关系
package cn.netkiller.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 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 getStudents() {
return students;
}
public void setStudents(Set students) {
this.students = students;
}
@Override
public String toString() {
return "classes [id=" + id + ", name=" + name + ", students=" + students + "]";
}
}
package cn.netkiller.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);
ManyToMany 多对多
用户与角色就是一个多对多的关系,多对多是需要中间表做关联的。所以我方需要一个 user_has_role 表。
+----------+ +---------------+ +--------+
| users | | user_has_role | | role |
+----------+ +---------------+ +--------+
| id | | id |
| name | | role_id | o---+ | name |
| password | | | | |
+----------+ +---------------+ +--------+
创建 User 表
package cn.netkiller.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;
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 getRoles() {
return roles;
}
public void setRoles(Set roles) {
this.roles = roles;
}
@Override
public String toString() {
return "Users [id=" + id + ", name=" + name + ", password=" + password + ", roles=" + roles + "]";
}
}
创建 Role 表
package cn.netkiller.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;
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 getUsers() {
return users;
}
public void setUsers(Set 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;
外键级联删除
orphanRemoval = true 可以实现数据级联删除
package cn.netkiller.api.domain;
import java.io.Serializable;
import java.util.Set;
import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Table;
import com.fasterxml.jackson.annotation.JsonIgnore;
@Entity
@Table(name = "member")
public class Member implements Serializable {
/**
*
*/
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "id", unique = true, nullable = false, insertable = true, updatable = false)
private int id;
private String name;
private String sex;
private int age;
private String wechat;
@Column(unique = true)
private String mobile;
private String picture;
private String ipAddress;
@JsonIgnore
@OneToMany(cascade = CascadeType.ALL, orphanRemoval = true, mappedBy = "member")
private Set comment;
@JsonIgnore
@OneToMany(cascade = CascadeType.ALL, orphanRemoval = true, mappedBy = "member")
private Set statisticsHistory;
public Member() {
}
public Member(int id) {
this.id = id;
}
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 getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getWechat() {
return wechat;
}
public void setWechat(String wechat) {
this.wechat = wechat;
}
public String getMobile() {
return mobile;
}
public void setMobile(String mobile) {
this.mobile = mobile;
}
public String getPicture() {
return picture;
}
public void setPicture(String picture) {
this.picture = picture;
}
public String getIpAddress() {
return ipAddress;
}
public void setIpAddress(String ipAddress) {
this.ipAddress = ipAddress;
}
@Override
public String toString() {
return "Member [id=" + id + ", name=" + name + ", sex=" + sex + ", age=" + age + ", wechat=" + wechat + ", mobile=" + mobile + ", picture=" + picture + ", ipAddress=" + ipAddress + "]";
}
}
其他
Cascade
CascadeType.PERSIST (级联新建)
CascadeType.REMOVE (级联删除)
CascadeType.REFRESH (级联刷新)
CascadeType.MERGE (级联更新)中选择一个或多个。
CascadeType.ALL
@JsonIgnore
当尸体返回 Json 数据结构是,将不包含 @JsonIgnore 定义变量。
@JsonIgnore
@OneToMany(mappedBy = "owner")
private List pets;
@EnableJpaAuditing 开启 JPA 审计功能
@SpringBootApplication
@EnableJpaAuditing
public class Application {
public static void main(String[] args) throws Exception {
SpringApplication.run(Application .class, args);
}
}
在需要审计实体中加入 @EntityListeners(AuditingEntityListener.class)
@EntityListeners(AuditingEntityListener.class)
public class Member implements Serializable {
private static final long serialVersionUID = -6163675075289529459L;
@JsonIgnore
String entityName = this.getClass().getSimpleName();
@CreatedBy
String createdBy;
@LastModifiedBy
String modifiedBy;
/**
* 实体创建时间
*/
@Temporal(TemporalType.TIMESTAMP)
@CreatedDate
protected Date dateCreated = new Date();
/**
* 实体修改时间
*/
@Temporal(TemporalType.TIMESTAMP)
@LastModifiedDate
protected Date dateModified = new Date();
#省略getter setter
}
实体继承
B、C 类继承 A 所有属性,并且主键均为数据库(auto_increment)
@MappedSuperclass
@(strategy = InheritanceType.TABLE_PER_CLASS)
public class A{
@Id
@GeneratedValue(strategy=GenerationType.IDENTITY)
private int id;
}
@Entity
@Table(name="b")
public class B extends A{
}
@Entity
@Table(name="c")
public class C extends A{
}netkiller:Spring Data with MySQL (索引)zhuanlan.zhihu.comnetkiller:Spring Data with MySQL (实体定义)zhuanlan.zhihu.comnetkiller:Spring Data with MongoDB (四)zhuanlan.zhihu.comnetkiller:Spring Data with MongoDB (三)zhuanlan.zhihu.comnetkiller:Spring Data with MongoDB (二)zhuanlan.zhihu.comnetkiller:Spring Data with MongoDB (一)zhuanlan.zhihu.comnetkiller:Spring Data with Rediszhuanlan.zhihu.comnetkiller:Spring boot with i18nzhuanlan.zhihu.comnetkiller:Spring boot with Validatorzhuanlan.zhihu.com