5.3. Spring Data JPA-白红宇

5.3.1. @Entity

5.3.1.1. @Id

ID 字段

@Id				@GeneratedValue(strategy = GenerationType.IDENTITY)				@Column(name = "id", unique = true, nullable = false, insertable = true, updatable = false)				private int id;

5.3.1.2. @Table

5.3.1.2.1. catalog

@Table(name="CUSTOMERS",catalog="hibernate")

5.3.1.2.2. schema

配置Schema

@Table(name="tabname", schema="public")

5.3.1.2.3. uniqueConstraints

唯一索引

@Table(name="CUSTOMERS",uniqueConstraints={@UniqueConstraint(columnNames={"name","email"})})

定义多组唯一索引

uniqueConstraints={@UniqueConstraint(columnNames={"name","email"}),@UniqueConstraint(columnNames={"name","age"})}

5.3.1.3. @Column

unique 属性表示该字段是否为唯一标识，默认为false。如果表中有一个字段需要唯一标识，则既可以使用该标记，也可以使用@Table标记中的@UniqueConstraint。				nullable 属性表示该字段是否可以为null值，默认为true。				insertable 属性表示在使用“INSERT”脚本插入数据时，是否需要插入该字段的值。				updatable 属性表示在使用“UPDATE”脚本插入数据时，是否需要更新该字段的值。insertable和updatable属性一般多用于只读的属性，例如主键和外键等。这些字段的值通常是自动生成的。				columnDefinition属性表示创建表时，该字段创建的SQL语句，一般用于通过Entity生成表定义时使用。				table 属性表示当映射多个表时，指定表的表中的字段。默认值为主表的表名。				length 属性表示字段的长度，当字段的类型为varchar时，该属性才有效，默认为255个字符。				precision 属性和scale属性表示精度，当字段类型为double时，precision表示数值的总长度，scale表示小数点所占的位数。

5.3.1.3.1. 字段长度

字段长度定义

@Column(name="name", length=80, nullable=true)

5.3.1.3.2. 浮点型

@Column(precision=18, scale=5)      private BigDecimal principal; 	        @Column(name="Price", columnDefinition="Decimal(10,2) default '100.00'")

5.3.1.3.3. 创建于更新控制

@Column(name = "ctime", nullable = false, insertable = false, updatable = false)

5.3.1.4. @NotNull 不能为空声明

@NotNull				public String username;

5.3.1.5. @DateTimeFormat 处理日期时间格式

public java.sql.Date createdate; 创建日期 YYYY-MM-DD				public java.util.Date finisheddate; 创建日期时间 YYYY-MM-DD HH:MM:SS

Json默认为 yyyy-MM-ddTHH:mm:ss 注意日期与时间中间的T，修改日期格式将T去掉

@DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")				@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")

5.3.1.6. 默认时间规则

5.3.1.6.1. CreatedDate

Spring 提供了 import org.springframework.data.annotation.CreatedDate;

但是这些只能作用于实体类。

@CreatedDate    private Date createdDateTime;

5.3.1.6.2. 与时间日期有关的 hibernate 注解

5.3.1.6.2.1. 设置默认时间

@Column(insertable = false) @org.hibernate.annotations.ColumnDefault("1.00") @org.hibernate.annotations.Generated(org.hibernate.annotations.GenerationTime.INSERT) protected Date lastModified;

5.3.1.6.2.2. 创建时间

@Temporal(TemporalType.TIMESTAMP)  @Column(updatable = false)  @org.hibernate.annotations.CreationTimestamp  protected Date createdDate;

5.3.1.6.2.3. 更新时间

@Column(name="update_time")  @org.hibernate.annotations.UpdateTimestamp  @Temporal(TemporalType.TIMESTAMP)private Date updateTime;

@Temporal(TemporalType.TIMESTAMP)  @Column(insertable = false, updatable = false)  @org.hibernate.annotations.Generated(  org.hibernate.annotations.GenerationTime.ALWAYS  )

5.3.1.6.3. 数据库级别的默认创建日期时间定义

package cn.netkiller.api.domain.elasticsearch;import java.util.Date;import javax.persistence.Column;import javax.persistence.Entity;import javax.persistence.Id;import javax.persistence.Table;@Entity@Tablepublic class ElasticsearchTrash {	@Id	private int id;	@Column(columnDefinition = "TIMESTAMP DEFAULT CURRENT_TIMESTAMP")	private Date ctime;	public int getId() {		return id;	}	public void setId(int id) {		this.id = id;	}	public Date getCtime() {		return ctime;	}	public void setCtime(Date ctime) {		this.ctime = ctime;	}}

对应数据库DDL

CREATE TABLE `elasticsearch_trash` (  `id` int(11) NOT NULL,  `ctime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

5.3.1.6.4. 数据库级别的默认创建日期与更新时间定义

需求是这样的：

1. 创建时间与更新时间只能由数据库产生，不允许在实体类中产生，因为每个节点的时间/时区不一定一直。另外防止人为插入自定义时间时间。

2. 插入记录的时候创建默认时间，创建时间不能为空，时间一旦插入不允许日后在实体类中修改。

3. 记录创建后更新日志字段为默认为 null 表示该记录没有被修改过。一旦数据被修改，修改日期字段将记录下最后的修改时间。

4. 甚至你可以通过触发器实现一个history 表，用来记录数据的历史修改，详细请参考作者另一部电子书《Netkiller Architect 手札》数据库设计相关章节。

package cn.netkiller.api.domain.elasticsearch;import java.util.Date;import javax.persistence.Column;import javax.persistence.Entity;import javax.persistence.Id;import javax.persistence.Table;import javax.validation.constraints.Null;@Entity@Tablepublic class ElasticsearchTrash {	@Id	private int id;	// 创建时间	@Column(insertable = false, updatable = false, columnDefinition = "TIMESTAMP DEFAULT CURRENT_TIMESTAMP")	private Date ctime;		// 修改时间	@Column(nullable = true, insertable = false, updatable = false, columnDefinition = "TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP")	private Date mtime;	public int getId() {		return id;	}	public void setId(int id) {		this.id = id;	}	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;	}}

对应数据库DDL

CREATE TABLE `elasticsearch_trash` (  `id` int(11) NOT NULL,  `ctime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,  `mtime` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8

5.3.1.6.5. 最后修改时间

需求：记录最后一次修改时间

package cn.netkiller.api.domain.elasticsearch;import java.util.Date;import javax.persistence.Column;import javax.persistence.Entity;import javax.persistence.Id;import javax.persistence.Table;@Entity@Tablepublic class ElasticsearchTrash {	@Id	private int id;	@Column(columnDefinition = "TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP")	private Date lastModified;}

产生DDL语句如下

CREATE TABLE `elasticsearch_trash` (  `id` int(11) NOT NULL,  `last_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

5.3.1.7. 索引

5.3.1.7.1. 普通索引

@Table(indexes = { @Index(name = "name", columnList = "name DESC"), @Index(name = "path", columnList = "path") })

package common.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;	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
        
          getCategory() {		return category;	}	public void setCategory(Set
         
           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 + "]";	}}

5.3.1.7.2. 唯一索引

针对字段做唯一索引

@Column(unique = true)

5.3.1.7.3. 复合索引

创建由多个字段组成的复合索引

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;

5.3.1.8. Enum 枚举数据类型

5.3.1.8.1. 实体中处理 enum 类型

@Enumerated(value = EnumType.ORDINAL) //ORDINAL序数

在实体中处理枚举类型适用于所有数据库，Spring data 将枚举视为 String 类型。

package cn.netkiller.api.domain;					import java.io.Serializable;					import javax.persistence.Column;					import javax.persistence.Entity;					import javax.persistence.GeneratedValue;					import javax.persistence.GenerationType;					import javax.persistence.Id;					import javax.persistence.Table;					@Entity					@Table(name = "statistics_history")					public class StatisticsHistory 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 long id;					private long memberId;					private long statisticsId;					public enum StatisticsType {					LIKE, COMMENT, BROWSE;					}					private StatisticsType type;					public Long getId() {					return id;					}					public void setId(Long id) {					this.id = id;					}					public long getMemberId() {					return memberId;					}					public void setMemberId(long memberId) {					this.memberId = memberId;					}					public long getStatisticsId() {					return statisticsId;					}					public void setStatisticsId(long statisticsId) {					this.statisticsId = statisticsId;					}					public StatisticsType getType() {					return type;					}					public void setType(StatisticsType type) {					this.type = type;					}					}

默认 enum 类型创建数据库等效 int(11)

CREATE TABLE `statistics_history` (  `id` bigint(20) NOT NULL AUTO_INCREMENT,  `member_id` bigint(20) NOT NULL,  `statistics_id` bigint(20) NOT NULL,  `type` int(11) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;SELECT * FROM test.statistics;

@Enumerated(EnumType.STRING) 注解可以使其成功字符串类型。

public enum StatisticsType {					LIKE, COMMENT, BROWSE;					}					@Enumerated(EnumType.STRING)					private StatisticsType type;

SQL

CREATE TABLE `statistics_history` (  `id` bigint(20) NOT NULL AUTO_INCREMENT,  `member_id` bigint(20) NOT NULL,  `statistics_id` bigint(20) NOT NULL,  `type` varchar(255) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

5.3.1.8.2. 数据库枚举类型

在枚举中处理类型虽然可以适用于所有数据库，但有时我们希望适用数据库的枚举类型（例如MySQL），数据库中得枚举类型要比字符串效率更高

package cn.netkiller.api.domain.elasticsearch;import java.util.Date;import javax.persistence.Column;import javax.persistence.Entity;import javax.persistence.Id;import javax.persistence.Table;@Entity@Tablepublic class NetkillerTrash {	@Id	private int id;	@Column(columnDefinition = "enum('Y','N') DEFAULT 'N'")	private boolean status;	public int getId() {		return id;	}	public void setId(int id) {		this.id = id;	}	public boolean isStatus() {		return status;	}	public void setStatus(boolean status) {		this.status = status;	}}

实际对应的数据库DLL

CREATE TABLE `netkiller_trash` (  `id` int(11) NOT NULL,  `status` enum('Y','N') DEFAULT 'N',  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8

5.3.1.9. 整形数据类型

无符号整形

package com.example.api.domain.elasticsearch;import java.util.Date;import javax.persistence.Column;import javax.persistence.Entity;import javax.persistence.Id;import javax.persistence.Table;@Entity@Tablepublic class Member {	@Id	private int id;			@Column(columnDefinition = "INT(10) UNSIGNED NOT NULL")	private int age;		@Column(insertable = false, updatable = false, columnDefinition = "TIMESTAMP DEFAULT CURRENT_TIMESTAMP")	private Date ctime;	@Column(nullable = true, insertable = false, updatable = false, columnDefinition = "TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP")	private Date mtime;	@Column(columnDefinition = "enum('Y','N') DEFAULT 'N'")	private boolean status;}

CREATE TABLE `member` (  `id` int(11) NOT NULL,  `age` int(10) unsigned NOT NULL,  `ctime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,  `mtime` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,  `status` enum('Y','N') DEFAULT 'N',  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8

5.3.1.10. @JoinColumn

@JoinColumn与@Column注释类似，它的定义如下代码所示。

@Target({METHOD, FIELD}) @Retention(RUNTIME) public @interface JoinColumn { String name() default ""; String referencedColumnName() default ""; boolean unique() default false; boolean nullable() default true; boolean insertable() default true; boolean updatable() default true; String columnDefinition() default ""; String table() default ""; }

5.3.1.11. @OneToOne

一对一表结构，如下面ER图所示，users表是用户表里面有登陆信息，profile 保存的时死人信息，这样的目的是我们尽量减少users表的字段，在频繁操作该表的时候性能比较好，另外一个目的是为了横向水平扩展。

+----------+             +------------+    | users    |             | profile    |    +----------+             +------------+    | id       | <---1:1---o | id         |    | name     |             | sex        |        | password |             | email      |        +----------+             +------------+

package cn.netkiller.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 cn.netkiller.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")

5.3.1.12. OneToMany 一对多

我们要实现一个一对多实体关系，ER 图如下

+----------+          +------------+    | Classes  |          | Student    |    +----------+          +------------+    | id       | <---+    | 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);

5.3.1.13. 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;	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;

5.3.1.14. 外键级联删除

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 + "]";	}}

5.3.1.15. 其他

5.3.1.15.1. Cascade

CascadeType.PERSIST （级联新建）					CascadeType.REMOVE （级联删除）					CascadeType.REFRESH （级联刷新）					CascadeType.MERGE （级联更新）中选择一个或多个。					CascadeType.ALL

5.3.1.15.2. @JsonIgnore

当尸体返回 Json 数据结构是，将不包含 @JsonIgnore 定义变量。

@JsonIgnore      @OneToMany(mappedBy = "owner")      private List
       
         pets;

5.3.2. 实体集成

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{}

5.3.3. Repository

Spring Data JPA 为此提供了一些表达条件查询的关键字：

Keyword	Sample	JPQL snippetAnd	findByLastnameAndFirstname	… where x.lastname = ?1 and x.firstname = ?2Or	findByLastnameOrFirstname	… where x.lastname = ?1 or x.firstname = ?2Is,Equals	findByFirstnameIs,findByFirstnameEquals	… where x.firstname = ?1Between	findByStartDateBetween	… where x.startDate between ?1 and ?2LessThan	findByAgeLessThan	… where x.age < ?1LessThanEqual	findByAgeLessThanEqual	… where x.age ⇐ ?1GreaterThan	findByAgeGreaterThan	… where x.age > ?1GreaterThanEqual	findByAgeGreaterThanEqual	… where x.age >= ?1After	findByStartDateAfter	… where x.startDate > ?1Before	findByStartDateBefore	… where x.startDate < ?1IsNull	findByAgeIsNull	… where x.age is nullIsNotNull,NotNull	findByAge(Is)NotNull	… where x.age not nullLike	findByFirstnameLike	… where x.firstname like ?1NotLike	findByFirstnameNotLike	… where x.firstname not like ?1StartingWith	findByFirstnameStartingWith	… where x.firstname like ?1 (parameter bound with appended %)EndingWith	findByFirstnameEndingWith	… where x.firstname like ?1 (parameter bound with prepended %)Containing	findByFirstnameContaining	… where x.firstname like ?1 (parameter bound wrapped in %)OrderBy	findByAgeOrderByLastnameDesc	… where x.age = ?1 order by x.lastname descNot	findByLastnameNot	… where x.lastname <> ?1In	findByAgeIn(Collection ages)	… where x.age in ?1NotIn	findByAgeNotIn(Collection age)	… where x.age not in ?1TRUE	findByActiveTrue()	… where x.active = trueFALSE	findByActiveFalse()	… where x.active = falseIgnoreCase	findByFirstnameIgnoreCase	… where UPPER(x.firstame) = UPPER(?1)		常用如下：And --- 等价于 SQL 中的 and 关键字，比如 findByUsernameAndPassword(String user, Striang pwd)Or --- 等价于 SQL 中的 or 关键字，比如 findByUsernameOrAddress(String user, String addr)Between --- 等价于 SQL 中的 between 关键字，比如 findBySalaryBetween(int max, int min)LessThan --- 等价于 SQL 中的 "<"，比如 findBySalaryLessThan(int max)GreaterThan --- 等价于 SQL 中的">"，比如 findBySalaryGreaterThan(int min)IsNull --- 等价于 SQL 中的 "is null"，比如 findByUsernameIsNull()IsNotNull --- 等价于 SQL 中的 "is not null"，比如 findByUsernameIsNotNull()NotNull --- 与 IsNotNull 等价Like --- 等价于 SQL 中的 "like"，比如 findByUsernameLike(String user)NotLike --- 等价于 SQL 中的 "not like"，比如 findByUsernameNotLike(String user)OrderBy ---等价于 SQL 中的 "order by"，比如 findByUsernameOrderBySalaryAsc(String user)Not --- 等价于 SQL 中的 "！ ="，比如 findByUsernameNot(String user)In --- 等价于 SQL 中的 "in"，比如 findByUsernameIn(Collection
     
       userList) ，方法的参数可以是 Collection 类型，也可以是数组或者不定长参数NotIn --- 等价于 SQL 中的 "not in"，比如 findByUsernameNotIn(Collection
      
        userList) ，方法的参数可以是 Collection 类型，也可以是数组或者不定长

@Autowired	private ArticleRepository articleRepository;	@RequestMapping("/mysql")	@ResponseBody	public String mysql() {		articleRepository.save(new Article("Neo", "Chen"));		for (Article article : articleRepository.findAll()) {			System.out.println(article);		}		Article tmp = articleRepository.findByTitle("Neo");		return tmp.getTitle();	}	@RequestMapping("/search")	@ResponseBody	public String search() {				for (Article article : articleRepository.findBySearch(1)) { System.out.println(article); }		 		List
     
       tmp = articleRepository.findBySearch(1L);		tmp.forEach((temp) -> {			System.out.println(temp.toString());		});		return tmp.get(0).getTitle();	}

5.3.3.1. 传递枚举参数

package cn.netkiller.api.repository;import org.springframework.data.repository.CrudRepository;import cn.netkiller.api.domain.StatisticsHistory;public interface StatisticsHistoryRepostitory extends CrudRepository
      
        {	public StatisticsHistory findByMemberIdAndStatisticsIdAndType(long member_id, long statistics_id,			StatisticsHistory.StatisticsType type);}

@Autowired	private StatisticsHistoryRepostitory statisticsHistoryRepostitory;							statisticsHistoryRepostitory.findByMemberIdAndStatisticsIdAndType(uid, id, type);

5.3.3.2. 翻页操作

package cn.netkiller.api.repository;import java.util.List;import org.springframework.data.domain.Pageable;import org.springframework.data.repository.CrudRepository;import cn.netkiller.api.domain.RecentRead;public interface RecentReadRepostitory extends CrudRepository
      
        {	List
       
         findByMemberId(long id, Pageable pageable);}

Top 10 实例

@RequestMapping("/recent/read/list/{id}")	public List
      
        recentList(@PathVariable long id) {		int page = 0;		int limit = 10;		List
       
         recentRead = recentReadRepostitory.findByMemberId(id, new PageRequest(page, limit));		return recentRead;	}

5.3.3.3. 排序操作操作

List
      
        findByName(String name, Sort sort);

Sort sort = new Sort(Direction.DESC, "id"); repostitory.findByName("Neo", sort);

5.3.3.4. OrderBy

public List
      
        findAllByOrderByIdAsc();public List
       
         findAllByOrderByIdDesc();List
        
          findByMemberIdOrderByIdDesc(int memberId, Pageable pageable);

5.3.3.5. Query

5.3.3.5.1. 参数传递

package api.repository.oracle;import org.springframework.data.domain.Page;import org.springframework.data.domain.Pageable;import org.springframework.data.jpa.repository.Query;import org.springframework.data.repository.CrudRepository;import org.springframework.data.repository.query.Param;import org.springframework.stereotype.Repository;import api.domain.oracle.Member;@Repositorypublic interface MemberRepository extends CrudRepository
       
         {	public Page
        
          findAll(Pageable pageable);	// public Member findByBillno(String billno);	public Member findById(String id);	@Query("SELECT m FROM Member m WHERE m.status = 'Y' AND m.id = :id")	public Member findFinishById(@Param("id") String id);}

import org.springframework.data.jpa.repository.JpaRepository;import org.springframework.data.jpa.repository.Query;import org.springframework.data.repository.query.Param;public interface PersonRepository extends JpaRepository
       
         {    @Query("SELECT p FROM Person p WHERE LOWER(p.lastName) = LOWER(:lastName)")    public List
        
          find(@Param("lastName") String lastName);}

5.3.3.6. @Transactional

5.3.3.6.1. 删除更新需要 @Transactional 注解

package cn.netkiller.api.repository;import javax.transaction.Transactional;import org.springframework.data.domain.Page;import org.springframework.data.domain.Pageable;import org.springframework.data.jpa.repository.Modifying;import org.springframework.data.jpa.repository.Query;import org.springframework.data.repository.CrudRepository;import org.springframework.data.repository.query.Param;import org.springframework.stereotype.Repository;import cn.netkiller.api.domain.RecentRead;@Repositorypublic interface RecentReadRepostitory extends CrudRepository
       
         {	Page
        
          findByMemberIdOrderByIdDesc(int memberId, Pageable pageable);	int countByMemberId(int memberId);		@Transactional	@Modifying	@Query("DELETE FROM RecentRead r WHERE r.memberId = ?1 AND r.articleId = ?2")	void deleteByMemberIdAndArticleId(int memberId, int articleId);		@Transactional	@Modifying	@Query("delete from RecentRead where member_id = :member_id")	public void deleteByMemberId(@Param("member_id") int memberId);	int countByMemberIdAndArticleId(int memberId, int articleId);}

5.3.3.6.2. 回滚操作

// 指定Exception回滚	@Transactional(rollbackFor=Exception.class)    public void methodName() {       // 不会回滚       throw new Exception("...");    }	//指定Exception回滚，但其他异常不回滚	@Transactional(noRollbackFor=Exception.class)    public ItimDaoImpl getItemDaoImpl() {        // 会回滚        throw new RuntimeException("注释");    }

5.3.4. CrudRepository

CrudRepository 接口提供了最基本的对实体类的添删改查操作

T save(T entity);								//保存单个实体 Iterable
     
       save(Iterable
       entities);//保存集合        T findOne(ID id);								//根据id查找实体         boolean exists(ID id);							//根据id判断实体是否存在         Iterable
      
        findAll();							//查询所有实体,不用或慎用!         long count();									//查询实体数量         void delete(ID id);								//根据Id删除实体         void delete(T entity);							//删除一个实体 void delete(Iterable
        entities);		//删除一个实体的集合         void deleteAll();								//删除所有实体,不用或慎用!

public interface UserRepository extends CrudRepository
     
       {    Long countByFirstName(String firstName);    @Transactional    Long deleteByFirstName(String firstName);    @Transactional    List
      
        removeByFirstName(String firstName);}

5.3.4.1. GreaterThan

package schedule.repository;import java.util.Date;import org.springframework.data.repository.CrudRepository;import common.domain.CmsTrash;public interface CmsTrashRepository extends CrudRepository
      
        {	Iterable
       
         findBySiteIdAndTypeOrderByCtimeASC(int siteId, String string);	Iterable
        
          findBySiteIdAndTypeAndCtimeGreaterThanOrderByCtimeASC(int siteId, String string, Date date);}

5.3.5. JpaRepository

原文出处：Netkiller 系列手札

本文作者：陈景峯

转载请与作者联系，同时请务必标明文章原始出处和作者信息及本声明。