Spring Data 最佳实践

摘要: ORM的出现解决了程序猿学习数据库学历成本,也加快了开发的速度。程序猿无需再学习数据库定义语言DDL以及数据库客户端,也无需关注建表这些繁琐的工作,同时也降低了数据库结构变更管理中与DBA频繁沟通的成本。

本文节选自电子书《Netkiller Architect 手札》

第 12 章 Spring Data 最佳实践

目录

12.1. 分类表

12.2. 为字段增加索引

12.3. 复合索引

12.4. 一对多实例

12.5. ManyToMany 多对多

12.6. 外键级联删除

ORM的出现解决了程序猿学习数据库学历成本,也加快了开发的速度。程序猿无需再学习数据库定义语言DDL以及数据库客户端,也无需关注建表这些繁琐的工作,同时也降低了数据库结构变更管理中与DBA频繁沟通的成本。。

在过去的两年中我们采用 Spring Data JPA 定义数据库,访问数据库,积累了很多经验,最终我们发现使用 Spring Data 实体定义完全可以代替 DBA 的建模工作。

下面我们采用案例,一个一个讲解,各种数据库实体关系的定义。相关数据库建模知识请先阅读 《Netkiller Architect 手札》 以及 《Netkiller Spring 手札》

12.1. 分类表

这是一个通用分类表,常见的父子关系加上path路径

+-----------+ | category  |
 |-----------|
 |id         | <---+ |name       |     |
 |description|    1:n
 |status     |     |
 |pid        | o---+ |path       |
 |status     |
 |ctime      |
 |mtime      |
 +-----------+
package cn.netkiller.domain;import java.util.Date;import java.util.Set;import javax.persistence.CascadeType;import javax.persistence.Column;import javax.persistence.Entity;import javax.persistence.FetchType;import javax.persistence.GeneratedValue;import javax.persistence.GenerationType;import javax.persistence.Id;import javax.persistence.JoinColumn;import javax.persistence.ManyToOne;import javax.persistence.OneToMany;import org.springframework.format.annotation.DateTimeFormat;import com.fasterxml.jackson.annotation.JsonFormat;import com.fasterxml.jackson.annotation.JsonIgnore;@Entitypublic class Category {	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)	@Column(name = "id", unique = true, nullable = false, insertable = true, updatable = false)	public int id;	public String name;	public String description;	public String path;	@Column(columnDefinition = "enum('Enabled','Disabled') DEFAULT 'Enabled' COMMENT '状态'")	public String status;	@DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")	@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")	@Column(columnDefinition = "TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'")	public Date ctime;	@DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")	@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")	@Column(columnDefinition = "TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更改时间'")	public Date mtime;	@ManyToOne(cascade = { CascadeType.PERSIST, CascadeType.REMOVE })	@JoinColumn(name = "pid", referencedColumnName = "id")	private Category categorys;	@JsonIgnore
	@OneToMany(cascade = CascadeType.ALL, mappedBy = "category", fetch = FetchType.EAGER)	private Set<Category> category;	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 getDescription() {		return description;
	}	public void setDescription(String description) {		this.description = description;
	}	public String getPath() {		return path;
	}	public void setPath(String path) {		this.path = path;
	}	public String getStatus() {		return status;
	}	public void setStatus(String status) {		this.status = status;
	}	public Date getCtime() {		return ctime;
	}	public void setCtime(Date ctime) {		this.ctime = ctime;
	}	public Date getMtime() {		return mtime;
	}	public void setMtime(Date mtime) {		this.mtime = mtime;
	}	public Category getCategorys() {		return categorys;
	}	public void setCategorys(Category categorys) {		this.categorys = categorys;
	}	public Set<Category> getCategory() {		return category;
	}	public void setCategory(Set<Category> category) {		this.category = category;
	}	@Override
	public String toString() {		return "Category [id=" + id + ", name=" + name + ", description=" + description + ", path=" + path + ", status="
				+ status + ", ctime=" + ctime + ", mtime=" + mtime + ", categorys=" + categorys + ", category="
				+ category + "]";
	}

}

期望结果

CREATE TABLE `category` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `ctime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '',  `description` varchar(255) DEFAULT NULL,  `mtime` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '',  `name` varchar(255) DEFAULT NULL,  `path` varchar(255) DEFAULT NULL,  `status` enum('Enabled','Disabled') DEFAULT 'Enabled' COMMENT '',  `pid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

12.2. 为字段增加索引

我们希望为 name 和 path 字段增加普通索引

package netkiller.domain;import java.util.Date;import java.util.Set;import javax.persistence.CascadeType;import javax.persistence.Column;import javax.persistence.Entity;import javax.persistence.FetchType;import javax.persistence.GeneratedValue;import javax.persistence.GenerationType;import javax.persistence.Id;import javax.persistence.Index;import javax.persistence.JoinColumn;import javax.persistence.ManyToOne;import javax.persistence.OneToMany;import javax.persistence.Table;import org.springframework.format.annotation.DateTimeFormat;import com.fasterxml.jackson.annotation.JsonFormat;import com.fasterxml.jackson.annotation.JsonIgnore;@Entity@Table(indexes = { @Index(name = "name", columnList = "name DESC"), @Index(name = "path", columnList = "path") })public class Category {	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)	@Column(name = "id", unique = true, nullable = false, insertable = true, updatable = false)	public int id;	public String name;	public String description;	public String path;	@Column(columnDefinition = "enum('Enabled','Disabled') DEFAULT 'Enabled' COMMENT '状态'")	public String status;	@DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")	@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")	@Column(columnDefinition = "TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'")	public Date ctime;	@DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")	@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")	@Column(columnDefinition = "TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更改时间'")	public Date mtime;	@ManyToOne(cascade = { CascadeType.PERSIST, CascadeType.REMOVE })	@JoinColumn(name = "pid", referencedColumnName = "id")	private Category categorys;	@JsonIgnore
	@OneToMany(cascade = CascadeType.ALL, mappedBy = "category", fetch = FetchType.EAGER)	private Set<Category> category;	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 getDescription() {		return description;
	}	public void setDescription(String description) {		this.description = description;
	}	public String getPath() {		return path;
	}	public void setPath(String path) {		this.path = path;
	}	public String getStatus() {		return status;
	}	public void setStatus(String status) {		this.status = status;
	}	public Date getCtime() {		return ctime;
	}	public void setCtime(Date ctime) {		this.ctime = ctime;
	}	public Date getMtime() {		return mtime;
	}	public void setMtime(Date mtime) {		this.mtime = mtime;
	}	public Category getCategorys() {		return categorys;
	}	public void setCategorys(Category categorys) {		this.categorys = categorys;
	}	public Set<Category> getCategory() {		return category;
	}	public void setCategory(Set<Category> category) {		this.category = category;
	}	@Override
	public String toString() {		return "Category [id=" + id + ", name=" + name + ", description=" + description + ", path=" + path + ", status="
				+ status + ", ctime=" + ctime + ", mtime=" + mtime + ", categorys=" + categorys + ", category="
				+ category + "]";
	}

}

期望结果

CREATE TABLE `category` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `ctime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '????',  `description` varchar(255) DEFAULT NULL,  `mtime` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '????',  `name` varchar(255) DEFAULT NULL,  `path` varchar(255) DEFAULT NULL,  `status` enum('Enabled','Disabled') DEFAULT 'Enabled' COMMENT '??',  `pid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),  KEY `name` (`name`),  KEY `path` (`path`),  KEY `FKeiel7nqjxu4kmefso9tm9qcsu` (`pid`),  CONSTRAINT `FKeiel7nqjxu4kmefso9tm9qcsu` FOREIGN KEY (`pid`) REFERENCES `category` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

12.3. 复合索引

创建由多个字段组成的复合索引,如: "member_id", "articleId"

package cn.netkiller.api.model;import java.io.Serializable;import java.util.Date;import javax.persistence.CascadeType;import javax.persistence.Column;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;import javax.persistence.Temporal;import javax.persistence.TemporalType;import javax.persistence.UniqueConstraint;import com.fasterxml.jackson.annotation.JsonFormat;@Entity@Table(name = "comment", uniqueConstraints = { @UniqueConstraint(columnNames = { "member_id", "articleId" }) })public class Comment implements Serializable {	/**
	 * 
	 */
	private static final long serialVersionUID = -1484408775034277681L;	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)	@Column(name = "id", unique = true, nullable = false, insertable = true, updatable = false)	private int id;	@ManyToOne(cascade = { CascadeType.ALL })	@JoinColumn(name = "member_id")	private Member member;	private int articleId;	private String message;	@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")	@Temporal(TemporalType.TIMESTAMP)	@Column(updatable = false)	@org.hibernate.annotations.CreationTimestamp	protected Date createDate;	public int getId() {		return id;
	}	public void setId(int id) {		this.id = id;
	}	public Member getMember() {		return member;
	}	public void setMember(Member member) {		this.member = member;
	}	public int getArticleId() {		return articleId;
	}	public void setArticleId(int articleId) {		this.articleId = articleId;
	}	public String getMessage() {		return message;
	}	public void setMessage(String message) {		this.message = message;
	}	public Date getCreateDate() {		return createDate;
	}	public void setCreateDate(Date createDate) {		this.createDate = createDate;
	}
}

期望结果

CREATE TABLE `comment` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `article_id` int(11) NOT NULL,  `create_date` datetime DEFAULT NULL,  `message` varchar(255) DEFAULT NULL,  `member_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),  UNIQUE KEY `UK5qxfiu92nwlvgli7bl3evl11m` (`member_id`,`article_id`),  CONSTRAINT `FKmrrrpi513ssu63i2783jyiv9m` FOREIGN KEY (`member_id`) REFERENCES `member` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

12.4. 一对多实例

如下图,我们将实现 categroy 和 article 的一对多关系

+-----------+      | category  |
      |-----------|
  +-->|id         | <---+  |   |title      |     |
  |   |description|    1:n
  |   |status     |     |
  |   |parent_id  | o---+  |   +-----------+
  |
 1:n
  |
  |   +-----------------+            +-----------------+  |   | article         |            | feedback        |
  |   |-----------------|            |-----------------|
  |   |id               |<--1:n--+   |id               |
  |   |title            |        |   |title            |
  |   |content          |        |   |content          |
  |   |datetime         |        |   |datetime         |
  |   |status           |        |   |status           |
  +--o|category_id      |        +--o|article_id       |
  +--o|member_id        |        +-->|member_id        |
  |   +-----------------+        |   +-----------------+
  |   | 2007,2008,2009  |        |   | 2007,2008,2009  |
  |   +-----------------+        |   +-----------------+
  |                              |
 1:n  +----------+     +---1:n---+
  |   | member   |     |
  |   |----------|     |
  +-->|id        | <---+
      |user      |
      |passwd    |
      |nickname  |
      |status    |
      +----------+

首先定义分类实体类

package cn.netkiller.domain;import java.util.Date;import java.util.Set;import javax.persistence.CascadeType;import javax.persistence.Column;import javax.persistence.Entity;import javax.persistence.FetchType;import javax.persistence.GeneratedValue;import javax.persistence.GenerationType;import javax.persistence.Id;import javax.persistence.Index;import javax.persistence.JoinColumn;import javax.persistence.ManyToOne;import javax.persistence.OneToMany;import javax.persistence.Table;import org.springframework.format.annotation.DateTimeFormat;import com.fasterxml.jackson.annotation.JsonFormat;import com.fasterxml.jackson.annotation.JsonIgnore;@Entity@Table(indexes = { @Index(name = "name", columnList = "name DESC"), @Index(name = "path", columnList = "path") })public class Category {	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)	@Column(name = "id", unique = true, nullable = false, insertable = true, updatable = false)	public int id;	public String name;	public String description;	public String path;	@Column(columnDefinition = "enum('Enabled','Disabled') DEFAULT 'Enabled' COMMENT '状态'")	public String status;	@DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")	@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")	@Column(columnDefinition = "TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'")	public Date ctime;	@DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")	@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")	@Column(columnDefinition = "TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更改时间'")	public Date mtime;	@ManyToOne(cascade = { CascadeType.PERSIST, CascadeType.REMOVE })	@JoinColumn(name = "pid", referencedColumnName = "id")	private Category categorys;	@JsonIgnore
	@OneToMany(cascade = CascadeType.ALL, mappedBy = "category", fetch = FetchType.EAGER)	private Set<Category> category;	@JsonIgnore
	@OneToMany(cascade = CascadeType.ALL, mappedBy = "category", orphanRemoval = true)	private Set<Article> article;	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 getDescription() {		return description;
	}	public void setDescription(String description) {		this.description = description;
	}	public String getPath() {		return path;
	}	public void setPath(String path) {		this.path = path;
	}	public String getStatus() {		return status;
	}	public void setStatus(String status) {		this.status = status;
	}	public Date getCtime() {		return ctime;
	}	public void setCtime(Date ctime) {		this.ctime = ctime;
	}	public Date getMtime() {		return mtime;
	}	public void setMtime(Date mtime) {		this.mtime = mtime;
	}	public Category getCategorys() {		return categorys;
	}	public void setCategorys(Category categorys) {		this.categorys = categorys;
	}	public Set<Category> getCategory() {		return category;
	}	public void setCategory(Set<Category> category) {		this.category = category;
	}	@Override
	public String toString() {		return "Category [id=" + id + ", name=" + name + ", description=" + description + ", path=" + path + ", status="
				+ status + ", ctime=" + ctime + ", mtime=" + mtime + ", categorys=" + categorys + ", category="
				+ category + "]";
	}

}

定义文章实体类

package cn.netkiller.domain;import java.io.Serializable;import java.util.Date;import java.util.Set;import javax.persistence.CascadeType;import javax.persistence.Column;import javax.persistence.Entity;import javax.persistence.FetchType;import javax.persistence.GeneratedValue;import javax.persistence.GenerationType;import javax.persistence.Id;import javax.persistence.JoinColumn;import javax.persistence.ManyToOne;import javax.persistence.OneToMany;import javax.persistence.Table;import org.springframework.format.annotation.DateTimeFormat;import com.fasterxml.jackson.annotation.JsonFormat;import com.fasterxml.jackson.annotation.JsonIgnore;@Entity@Table(name = "article")public class Article implements Serializable {	private static final long serialVersionUID = 7603772682950271321L;	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)	@Column(name = "id", unique = true, nullable = false, insertable = true, updatable = false)	public int id;	public String title;	@Column(name = "short")	public String shortTitle;	public String description;	public String author;	public int star;	public String tag;	public boolean share;	public boolean status;	public String content;	@ManyToOne(cascade = { CascadeType.PERSIST, CascadeType.REMOVE })	@JoinColumn(name = "category_id", referencedColumnName = "id")	private Category category;	@ManyToOne(cascade = { CascadeType.PERSIST, CascadeType.REMOVE })	@JoinColumn(name = "site_id", referencedColumnName = "id")	private Site site;	@ManyToOne(cascade = { CascadeType.ALL })	@JoinColumn(name = "member_id", referencedColumnName = "id")	private Member member;	@DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")	@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")	@Column(columnDefinition = "TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'")	public Date ctime;	@DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")	@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")	@Column(columnDefinition = "TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更改时间'")	public Date mtime;	@JsonIgnore
	@OneToMany(cascade = CascadeType.ALL, mappedBy = "article", fetch = FetchType.EAGER)	private Set<Comment> comment;	@JsonIgnore
	@OneToMany(cascade = CascadeType.ALL, mappedBy = "article", fetch = FetchType.EAGER)	private Set<Favorites> favorites;	@JsonIgnore
	@OneToMany(cascade = CascadeType.ALL, mappedBy = "article", orphanRemoval = true)	private Set<Statistics> statistics;	public int getId() {		return id;
	}	public void setId(int id) {		this.id = id;
	}	public String getTitle() {		return title;
	}	public void setTitle(String title) {		this.title = title;
	}	public String getDescription() {		return description;
	}	public void setDescription(String description) {		this.description = description;
	}	public Date getCtime() {		return ctime;
	}	public void setCtime(Date ctime) {		this.ctime = ctime;
	}	public String getShortTitle() {		return shortTitle;
	}	public void setShortTitle(String shortTitle) {		this.shortTitle = shortTitle;
	}	public String getAuthor() {		return author;
	}	public void setAuthor(String author) {		this.author = author;
	}	public int getStar() {		return star;
	}	public void setStar(int star) {		this.star = star;
	}	public String getTag() {		return tag;
	}	public void setTag(String tag) {		this.tag = tag;
	}	public boolean isShare() {		return share;
	}	public void setShare(boolean share) {		this.share = share;
	}	public boolean isStatus() {		return status;
	}	public void setStatus(boolean status) {		this.status = status;
	}	public String getContent() {		return content;
	}	public void setContent(String content) {		this.content = content;
	}	public Category getCategory() {		return category;
	}	public void setCategory(Category category) {		this.category = category;
	}	public Member getMember() {		return member;
	}	public void setMember(Member member) {		this.member = member;
	}	public Set<Comment> getComment() {		return comment;
	}	public void setComment(Set<Comment> comment) {		this.comment = comment;
	}	public Set<Favorites> getFavorites() {		return favorites;
	}	public void setFavorites(Set<Favorites> favorites) {		this.favorites = favorites;
	}	public Set<Statistics> getStatistics() {		return statistics;
	}	public void setStatistics(Set<Statistics> statistics) {		this.statistics = statistics;
	}	public Site getSite() {		return site;
	}	public void setSite(Site site) {		this.site = site;
	}	public Date getMtime() {		return mtime;
	}	public void setMtime(Date mtime) {		this.mtime = mtime;
	}	@Override
	public String toString() {		return "Article [id=" + id + ", title=" + title + ", shortTitle=" + shortTitle + ", description=" + description
				+ ", author=" + author + ", star=" + star + ", tag=" + tag + ", share=" + share + ", status=" + status
				+ ", content=" + content + ", category=" + category + ", site=" + site + ", member=" + member
				+ ", ctime=" + ctime + ", mtime=" + mtime + ", comment=" + comment + ", favorites=" + favorites
				+ ", statistics=" + statistics + "]";
	}

}

希望结果

CREATE TABLE `category` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `ctime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '????',  `description` varchar(255) DEFAULT NULL,  `mtime` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '????',  `name` varchar(255) DEFAULT NULL,  `path` varchar(255) DEFAULT NULL,  `status` enum('Enabled','Disabled') DEFAULT 'Enabled' COMMENT '??',  `pid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),  KEY `name` (`name`),  KEY `path` (`path`),  KEY `FKeiel7nqjxu4kmefso9tm9qcsu` (`pid`),  CONSTRAINT `FKeiel7nqjxu4kmefso9tm9qcsu` FOREIGN KEY (`pid`) REFERENCES `category` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;CREATE TABLE `article` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `author` varchar(255) DEFAULT NULL,  `content` varchar(255) DEFAULT NULL,  `ctime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '????',  `description` varchar(255) DEFAULT NULL,  `mtime` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '????',  `share` bit(1) NOT NULL,  `short` varchar(255) DEFAULT NULL,  `star` int(11) NOT NULL,  `status` bit(1) NOT NULL,  `tag` varchar(255) DEFAULT NULL,  `title` varchar(255) DEFAULT NULL,  `category_id` int(11) DEFAULT NULL,  `member_id` int(11) DEFAULT NULL,  `site_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),  KEY `FKy5kkohbk00g0w88fi05k2hcw` (`category_id`),  KEY `FK6l9vkfd5ixw8o8kph5rj1k7gu` (`member_id`),  KEY `FKrxbc33rok9m4n6pnbbwb3piwf` (`site_id`),  CONSTRAINT `FK6l9vkfd5ixw8o8kph5rj1k7gu` FOREIGN KEY (`member_id`) REFERENCES `member` (`id`),  CONSTRAINT `FKrxbc33rok9m4n6pnbbwb3piwf` FOREIGN KEY (`site_id`) REFERENCES `site` (`id`),  CONSTRAINT `FKy5kkohbk00g0w88fi05k2hcw` FOREIGN KEY (`category_id`) REFERENCES `category` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

现在我们已经将 categroy 与 article 两张表一对多关系建立起来。

12.5. ManyToMany 多对多

用户与角色就是一个多对多的关系,多对多是需要中间表做关联的。所以需要一个 user_has_role 表。

+----------+          +---------------+            +--------+    | users    |          | user_has_role |            | role   |
    +----------+          +---------------+            +--------+    | id       | <------o | user_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> 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 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> 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;

原文发布于微信公众号 - Netkiller(netkiller-ebook)

原文发表时间:2017-12-21

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏一个会写诗的程序员的博客

《Spring Boot极简教程》 第6章 Springboot数据库集成

在SpringBoot集成MyBatis时,我们将去掉和Mybatis配置相关的xml文件配置方式,用注解和属性配置来代替这些繁杂的配置。

531
来自专栏乐沙弥的世界

oracle imp导入时出现skipping table

    最近有同事在使用传统的imp工具导入数据时,总是提示收到skipping table的提示,也就是表被跳过,而不是被重建。即使是将目标数据库上的表对象删...

711
来自专栏杨建荣的学习笔记

通过闪回事务查看数据dml的情况 (r2笔记69天)

昨天有一个网友问我,怎么能够查询一个表中最后一条插入的记录,我大概回复了,可以通过闪回事务来实现,但是得看什么时候插入的数据,也需要一定的运气。 如果通过闪回事...

1953
来自专栏杨建荣的学习笔记

通过java来格式化sql语句(r4笔记第61天)

经常在抓取一些sql语句的时候,得到的sql文本有格式的问题,如果尝试得到执行计划,每次都会费一番周折。 比如下面的sql语句,基本包含了常见的格式问题。第3行...

2624
来自专栏后台及大数据开发

Linux巩固记录(5) hadoop 2.7.4下自己编译代码并运行MapReduce程序

程序代码为 ~\hadoop-2.7.4\share\hadoop\mapreduce\sources\hadoop-mapreduce-examples-2....

704
来自专栏菩提树下的杨过

Hadoop: MapReduce2的几个基本示例

1) WordCount  这个就不多说了,满大街都是,网上有几篇对WordCount的详细分析 http://www.sxt.cn/u/235/blog/58...

2286
来自专栏乐沙弥的世界

Oracle 索引监控与外键索引

      Oracle 监控索引特性为我们提供了一个大致判断索引是否被使用的情形。之所以这么说,是因为在Oracle 10g 中收集统计信息时会导致索引被监控...

642
来自专栏数据和云

突破常识:SQL增加DISTINCT后查询效率反而提高

杨廷琨,网名 yangtingkun 云和恩墨技术总监,Oracle ACE Director,ACOUG 核心专家 只要增加了DISTINCT关键字,Orac...

2816
来自专栏杨建荣的学习笔记

关于desc的一个奇怪问题及分析(r2第18天)

在平时的工作中,desc这个命令可谓短小精悍,可以很方便的查看表结构和not null的情况。 今天在生产环境中碰到一个有些奇怪的desc问题。 首先是数据迁移...

2605
来自专栏Netkiller

Mybatis 入门

Netkiller Java 手札 关于《Netkiller Java 手札》 作者2002年开始在项目中使用Java,各种原因没有留下Java文档,2015因...

2083

扫描关注云+社区