5.3. Spring Data JPA

5.3. Spring Data JPA
上一頁	第 5 章 Spring Data	下一頁

5.3.1. 選擇資料庫表引擎

正常創建表會使用資料庫預設引擎，有時資料庫預設引擎並不是我們需要的，通過下面配置可以指定表引擎

			
# Spring boot 1.x.x
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQLInnoDBDialect

# Spring boot 2.0.2
spring.jpa.hibernate.use-new-id-generator-mappings=true
spring.jpa.database-platform=org.hibernate.dialect.MySQL5InnoDBDialect

5.3.2. @Entity

5.3.2.1. @Table

5.3.2.1.1. catalog

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

5.3.2.1.2. schema

配置Schema

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

5.3.2.1.3. uniqueConstraints

唯一索引

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

定義多組唯一索引

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

5.3.2.2. @Id

ID 欄位，資料庫中的主鍵。

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

字元串做主鍵

			
package api.domain;

import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table()
public class TransactionsPostion {

	@Id
	private String address;
	private String startblock;
	private String endblock;

	public TransactionsPostion() {
		// TODO Auto-generated constructor stub
	}

	public String getAddress() {
		return address;
	}

	public void setAddress(String address) {
		this.address = address;
	}

	public String getStartblock() {
		return startblock;
	}

	public void setStartblock(String startblock) {
		this.startblock = startblock;
	}

	public String getEndblock() {
		return endblock;
	}

	public void setEndblock(String endblock) {
		this.endblock = endblock;
	}

}

對應資料庫表

			
CREATE TABLE "transactions_postion" (
  "address" varchar(255) NOT NULL,
  "endblock" varchar(255) DEFAULT NULL,
  "startblock" varchar(255) DEFAULT NULL,
  PRIMARY KEY ("address")
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

5.3.2.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.2.3.1. 欄位長度

欄位長度定義

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

5.3.2.3.2. 浮點型

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

5.3.2.3.3. 創建於更新控制

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

5.3.2.3.4.

				
	private String subject;
	@Column(columnDefinition = "TEXT")
	private String content;

5.3.2.4. @Lob 註解屬性將被持久化為 Blog 或 Clob 類型

Clob（Character Large Ojects）類型是長字元串類型，具體的java.sql.Clob, Character[], char[] 和 java.lang.String 將被持久化為 Clob 類型。
Blob（Binary Large Objects）類型是位元組類型，具體的java.sql.Blob, Byte[], byte[] 和 serializable type 將被持久化為 Blob 類型。
@Lob 持久化為Blob或者Clob類型,根據get方法的返回值不同,自動進行Clob和Blob的轉換。
因為這兩種類型的數據一般占用的內存空間比較大，所以通常使用延遲加載的方式，與@Basic標記同時使用，設置加載方式為FetchType.LAZY。

			
@Lob 
@Basic(fetch = FetchType.LAZY) 
@Column(name=" content", columnDefinition="CLOB", nullable=true) 
public String getContent() {
    return content;
}

5.3.2.5. @NotNull 不能為空聲明

				@NotNull
				public String username;

5.3.2.6. @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.2.7. 預設時間規則

5.3.2.7.1. CreatedDate

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

但是這些只能作用於實體類。

				
	@CreatedDate
    private Date createdDateTime;

5.3.2.7.2. 與時間日期有關的 hibernate 註解

5.3.2.7.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.2.7.2.2. 創建時間

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

5.3.2.7.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.2.7.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
@Table
public 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.2.7.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
@Table
public 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.2.7.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
@Table
public 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.2.8. 索引

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

}

5.3.2.8.2. 唯一索引

針對欄位做唯一索引

				
@Column(unique = true)

5.3.2.8.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.2.9. Enum 枚舉數據類型

5.3.2.9.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.2.9.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
@Table
public 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.2.10. SET 資料結構

			
package common.domain;

import java.util.Date;
import java.util.Map;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Convert;
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 org.springframework.format.annotation.DateTimeFormat;
import com.fasterxml.jackson.annotation.JsonFormat;

import common.type.OptionConverter;

@Entity
public class ItemPool {
	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	@Column(name = "id", unique = true, nullable = false, insertable = false, updatable = false)
	public int id;

	@ManyToOne(cascade = { CascadeType.PERSIST, CascadeType.REMOVE })
	@JoinColumn(name = "site_id", referencedColumnName = "id")
	private Site site;

	public String question;

	@Column(columnDefinition = "json DEFAULT NULL")
	@Convert(converter = OptionConverter.class)
	public Map<String, String> options;

	@Column(columnDefinition = "SET('A','B','C','D','E','F','G') DEFAULT NULL COMMENT '答案'")
	public String answer;

	@ManyToOne(cascade = { CascadeType.PERSIST, CascadeType.REMOVE })
	@JoinColumn(name = "category_id", referencedColumnName = "id")
	private Category category;

	@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;
}

定義 SET 如下，在JAVA中 SET被映射為逗號分隔的字元串（String），所以操作起來並無不同。使用字元串"A,B,C"存儲即可，取出也同樣是字元串。

			
@Column(columnDefinition = "SET('A','B','C','D','E','F','G') DEFAULT NULL COMMENT '答案'")

接入後查看

			
mysql> select answer from item_pool;
+--------+
| answer |
+--------+
| A,B,C  |
+--------+
1 row in set (0.00 sec)

完美實現

5.3.2.11. 整形數據類型

無符號整形

			
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
@Table
public 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.2.12. JSON 數據類型

MySQL 5.7 中增加了 json 數據類型，下面是一個例子：

			
CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `your` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

我們需要在 Java 實體中定義 json 資料庫結構，我搜索遍了整個互聯網（Google,Bing,Baidu......），沒有找到解決方案，功夫不負有心人，反覆嘗試後終於成功。記住我是第一個這樣用的：）。

			
package common.domain;

import java.util.Date;
import java.util.Map;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Convert;
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 org.springframework.format.annotation.DateTimeFormat;
import com.fasterxml.jackson.annotation.JsonFormat;

import common.type.OptionConverter;

@Entity
public class ItemPool {
	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	@Column(name = "id", unique = true, nullable = false, insertable = false, updatable = false)
	public int id;

	@ManyToOne(cascade = { CascadeType.PERSIST, CascadeType.REMOVE })
	@JoinColumn(name = "site_id", referencedColumnName = "id")
	private Site site;

	public String name;

	@Column(columnDefinition = "json DEFAULT NULL")
	@Convert(converter = OptionConverter.class)
	public Map<String, String> options;

	@ManyToOne(cascade = { CascadeType.PERSIST, CascadeType.REMOVE })
	@JoinColumn(name = "category_id", referencedColumnName = "id")
	private Category category;

	@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;
}

類型轉換 Class

			
package common.type;

import java.util.Map;
import javax.persistence.AttributeConverter;

import com.google.gson.Gson;
import com.google.gson.reflect.TypeToken;

public class OptionConverter implements AttributeConverter<Map<String, String>, String> {

	Gson json = new Gson();

	@Override
	public String convertToDatabaseColumn(Map<String, String> items) {
		return json.toJson(items, new TypeToken<Map<String, String>>() {
		}.getType());
	}

	@Override
	public Map<String, String> convertToEntityAttribute(String str) {
		return json.fromJson(str, new TypeToken<Map<String, String>>() {
		}.getType());
	}
}

通過 @Column(columnDefinition = "json DEFAULT NULL") 定義資料庫為 JSON 數據類型

數據存儲與取出通過 @Convert(converter = OptionConverter.class) 做轉換

這裡我需要使用 Map 資料結構 public Map<String, String> options;，你可以根據你的實際需要定義數據類型 Class

啟動 Spring 項目後創建 Schema 如下：

			
CREATE TABLE `item_pool` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ctime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '????',
  `mtime` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '????',
  `name` varchar(255) DEFAULT NULL,
  `category_id` int(11) DEFAULT NULL,
  `site_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FKgwuxedi20fxclobkk2po053hj` (`category_id`),
  KEY `FKiujumwssofow95st51ukklpgv` (`site_id`),
  CONSTRAINT `FKgwuxedi20fxclobkk2po053hj` FOREIGN KEY (`category_id`) REFERENCES `category` (`id`),
  CONSTRAINT `FKiujumwssofow95st51ukklpgv` FOREIGN KEY (`site_id`) REFERENCES `site` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

我們做個簡單的測試, 創建倉庫。

			
package common.repository;

import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;

import common.domain.ItemPool;

@Repository
public interface ItemPoolRepository extends CrudRepository<ItemPool, Integer> {

}

			
package cn.netkiller.api.restful;

import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;

import common.domain.ItemPool;
import common.repository.ItemPoolRepository;

@RestController
public class TestRestController {

	private static final Logger logger = LoggerFactory.getLogger(TestRestController.class);
	@Autowired
	private ItemPoolRepository itemPoolRepository;

	@GetMapping("/test/json/data/type")
	public void jsonType() {

		ItemPool itemPool = new ItemPool();
		itemPool.name = "Which is Operstion System?";
		Map<String, String> opt = new LinkedHashMap<String, String>();
		opt.put("A", "Linux");
		opt.put("B", "Java");
		itemPool.options = opt;
		itemPoolRepository.save(itemPool);

		itemPool = null;
		itemPool = itemPoolRepository.findOne(1);
		System.out.println(itemPool.toString());
	}

}

只能用完美來形容

			
mysql> select options from item_pool;
+-----------------------------+
| options                     |
+-----------------------------+
| {"A": "Linux", "B": "Java"} |
+-----------------------------+
1 row in set (0.00 sec)

5.3.2.13. @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.2.14. @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.2.15. 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<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 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.2.16. 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;

5.3.2.17. 外鍵級聯刪除

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> comment;
	@JsonIgnore
	@OneToMany(cascade = CascadeType.ALL, orphanRemoval = true, mappedBy = "member")
	private Set<StatisticsHistory> 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.2.18. 其他

5.3.2.18.1. Cascade

					CascadeType.PERSIST （級聯新建）
					CascadeType.REMOVE （級聯刪除）
					CascadeType.REFRESH （級聯刷新）
					CascadeType.MERGE （級聯更新）中選擇一個或多個。
					CascadeType.ALL

5.3.2.18.2. @JsonIgnore

當屍體返回 Json 資料結構是，將不包含 @JsonIgnore 定義變數。

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

5.3.3. 創建復合主鍵

定義實體

		
package cn.netkiller.wallet.domain;

import java.io.Serializable;

import javax.persistence.Column;
import javax.persistence.Embeddable;
import javax.persistence.EmbeddedId;
import javax.persistence.Entity;

@Entity
public class UserToken {
	@EmbeddedId
	@Column(unique = true, nullable = false, insertable = true, updatable = false)
	private UserTokenPrimaryKey primaryKey;

	private String name;
	private String symbol;
	private int decimals;

	public UserToken() {
		// TODO Auto-generated constructor stub
	}

	public UserTokenPrimaryKey getPrimaryKey() {
		return primaryKey;
	}

	public void setPrimaryKey(UserTokenPrimaryKey primaryKey) {
		this.primaryKey = primaryKey;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getSymbol() {
		return symbol;
	}

	public void setSymbol(String symbol) {
		this.symbol = symbol;
	}

	public int getDecimals() {
		return decimals;
	}

	public void setDecimals(int decimals) {
		this.decimals = decimals;
	}

	@Override
	public String toString() {
		return "UserToken [primaryKey=" + primaryKey + ", name=" + name + ", symbol=" + symbol + ", decimals=" + decimals + "]";
	}

	@Embeddable
	public static class UserTokenPrimaryKey implements Serializable {

		private static final long serialVersionUID = 1242827922377178368L;
		private String address;
		private String contractAddress;

		public UserTokenPrimaryKey() {
		}

		public UserTokenPrimaryKey(String address, String contractAddress) {
			this.address = address;
			this.contractAddress = contractAddress;
		}

		public String getAddress() {
			return address;
		}

		public void setAddress(String address) {
			this.address = address;
		}

		public String getContractAddress() {
			return contractAddress;
		}

		public void setContractAddress(String contractAddress) {
			this.contractAddress = contractAddress;
		}

		@Override
		public String toString() {
			return "UserTokenPrimaryKey [address=" + address + ", contractAddress=" + contractAddress + "]";
		}

	}

}

實際效果

		
CREATE TABLE "user_has_token" (
  "address" varchar(255) NOT NULL,
  "contract_address" varchar(255) NOT NULL,
  "decimals" int(11) NOT NULL,
  "name" varchar(255) DEFAULT NULL,
  "symbol" varchar(255) DEFAULT NULL,
  PRIMARY KEY ("address","contract_address")
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

		
package cn.netkiller.wallet.repository;

import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import cn.netkiller.wallet.domain.UserToken;
import cn.netkiller.wallet.domain.UserToken.UserTokenPrimaryKey;;

public interface UserTokenRepository extends JpaRepository<UserToken, UserTokenPrimaryKey> {

	UserToken findOneByPrimaryKey(UserTokenPrimaryKey primaryKey);

	@Query("select ut from UserToken ut where ut.primaryKey.address=:address")
	List<UserToken> getByAddress(@Param("address") String address);

	@Query("select ut from UserToken ut where ut.primaryKey.address=:address and ut.primaryKey.contractAddress=:contractAddress")
	List<UserToken> findByPrimaryKey(@Param("address") String address, @Param("contractAddress") String contractAddress);
}

5.3.4. 實體整合

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.5. Repository

		
Repository： 僅僅是一個標識，沒有任何方法，方便Spring自動掃瞄識別
CrudRepository： 繼承Repository，實現了一組CRUD相關的方法
PagingAndSortingRepository： 繼承CrudRepository，實現了一組分頁排序相關的方法
JpaRepository： 繼承PagingAndSortingRepository，實現一組JPA規範相關的方法

Spring Data JPA 為此提供了一些表達條件查詢的關鍵字：

		
Keyword	Sample	JPQL snippet
And			findByLastnameAndFirstname	… where x.lastname = ?1 and x.firstname = ?2
Or			findByLastnameOrFirstname	… where x.lastname = ?1 or x.firstname = ?2
Is,Equals	findByFirstnameIs,findByFirstnameEquals	… where x.firstname = ?1
Between		findByStartDateBetween	… where x.startDate between ?1 and ?2
LessThan	findByAgeLessThan	… where x.age < ?1
LessThanEqual	findByAgeLessThanEqual	… where x.age ⇐ ?1
GreaterThan	findByAgeGreaterThan	… where x.age > ?1
GreaterThanEqual	findByAgeGreaterThanEqual	… where x.age >= ?1
After		findByStartDateAfter	… where x.startDate > ?1
Before		findByStartDateBefore	… where x.startDate < ?1
IsNull		findByAgeIsNull	… where x.age is null
IsNotNull,NotNull	findByAge(Is)NotNull	… where x.age not null
Like		findByFirstnameLike	… where x.firstname like ?1
NotLike		findByFirstnameNotLike	… where x.firstname not like ?1
StartingWith	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 desc
Not			findByLastnameNot	… where x.lastname <> ?1
In			findByAgeIn(Collection ages)	… where x.age in ?1
NotIn		findByAgeNotIn(Collection age)	… where x.age not in ?1
TRUE		findByActiveTrue()	… where x.active = true
FALSE		findByActiveFalse()	… where x.active = false
IgnoreCase	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<String> userList) ，方法的參數可以是 Collection 類型，也可以是數組或者不定長參數
NotIn --- 等價于 SQL 中的 "not in"，比如 findByUsernameNotIn(Collection<String> userList) ，方法的參數可以是 Collection 類型，也可以是數組或者不定長

5.3.5.1. findByXXX

		
	@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<Article> tmp = articleRepository.findBySearch(1L);

		tmp.forEach((temp) -> {
			System.out.println(temp.toString());
		});

		return tmp.get(0).getTitle();
	}

5.3.5.2. Boolean

			
package cn.netkiller.wallet.repository.fcoin;

import java.util.List;

import org.springframework.data.domain.Pageable;
import org.springframework.data.repository.CrudRepository;

import cn.netkiller.wallet.domain.fcoin.Fcoin;;

public interface FcoinRepository extends CrudRepository<Fcoin, String> {

	Fcoin findOneByAddress(String address);

	int countByAirdropFalse();

	List<Fcoin> findByAirdrop(boolean airdrop, Pageable pageable);

}

5.3.5.3. 傳遞枚舉參數

			
package cn.netkiller.api.repository;

import org.springframework.data.repository.CrudRepository;

import cn.netkiller.api.domain.StatisticsHistory;

public interface StatisticsHistoryRepostitory extends CrudRepository<StatisticsHistory, Long> {

	public StatisticsHistory findByMemberIdAndStatisticsIdAndType(long member_id, long statistics_id,
			StatisticsHistory.StatisticsType type);

}

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

5.3.5.4. 翻頁操作

			
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<RecentRead, Long> {

	List<RecentRead> findByMemberId(long id, Pageable pageable);

}

Top 10 實例

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

注意 PageRequest(int page, int size) 在新版 Spring boot 2.x 中已經廢棄請使用 PageRequest.of(page, size) 替代

			
List<Fcoin> fcoins = fcoinRepository.findByAirdrop(false, PageRequest.of(0, size));

			
PageRequest(int page, int size, Sort sort)  Deprecated. 
use PageRequest.of(int, int, Sort) instead.

5.3.5.5. 排序操作操作

			
List<UserModel> findByName(String name, Sort sort);

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

5.3.5.6. OrderBy

			
public List<StudentEntity> findAllByOrderByIdAsc();
public List<StudentEntity> findAllByOrderByIdDesc();
List<RecentRead> findByMemberIdOrderByIdDesc(int memberId, Pageable pageable);

5.3.5.7. Query

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

@Repository
public interface MemberRepository extends CrudRepository<Member, Long> {
	public Page<Member> 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<Person, Long> {
    @Query("SELECT p FROM Person p WHERE LOWER(p.lastName) = LOWER(:lastName)")
    public List<Person> find(@Param("lastName") String lastName);
}

5.3.5.7.2. 原生 SQL

				
public interface UserRepository extends JpaRepository<User, Long> {

	@Query(value = "SELECT * FROM USERS WHERE EMAIL_ADDRESS = ?0", nativeQuery = true)
	User findByEmailAddress(String emailAddress);
}

insert ignore

				
	@Modifying
    @Query(value = "insert ignore into emp(create, modified, user_id, user_name, user_nickname, user_mail) values(?1, ?2, ?3, ?4, ?5, ?6)", nativeQuery = true)
    void insertIgnoreEmployee(Timestamp create, Timestamp modified, String userId, String name, String nickname, String mail);

5.3.5.7.3. @Query 與 Pageagble

https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#_native_queries

				
package api.domain;

import java.io.Serializable;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Index;
import javax.persistence.Table;

@Entity
@Table(indexes = { @Index(name = "address", columnList = "from_address,to_address"), @Index(name = "contractAddress", columnList = "contractAddress") })

public class TransactionHistory implements Serializable {
	private static final long serialVersionUID = 6710992220657056861L;
	@Id
	@Column(name = "blockNumber", unique = true, nullable = false, insertable = true, updatable = false)
	private int blockNumber;
	private String timeStamp;
	private String hash;
	@Column(name = "from_address")
	private String from;
	@Column(name = "to_address")
	private String to;
	private String value;
	private String gas;
	private String gasPrice;
	private String isError;
	private String contractAddress;
	private String gasUsed;
	private String symbol;

	public TransactionHistory() {
		// TODO Auto-generated constructor stub
	}

	public int getBlockNumber() {
		return blockNumber;
	}

	public void setBlockNumber(int blockNumber) {
		this.blockNumber = blockNumber;
	}

	public String getTimeStamp() {
		return timeStamp;
	}

	public void setTimeStamp(String timeStamp) {
		this.timeStamp = timeStamp;
	}

	public String getHash() {
		return hash;
	}

	public void setHash(String hash) {
		this.hash = hash;
	}

	public String getFrom() {
		return from;
	}

	public void setFrom(String from) {
		this.from = from;
	}

	public String getTo() {
		return to;
	}

	public void setTo(String to) {
		this.to = to;
	}

	public String getValue() {
		return value;
	}

	public void setValue(String value) {
		this.value = value;
	}

	public String getGas() {
		return gas;
	}

	public void setGas(String gas) {
		this.gas = gas;
	}

	public String getGasPrice() {
		return gasPrice;
	}

	public void setGasPrice(String gasPrice) {
		this.gasPrice = gasPrice;
	}

	public String getIsError() {
		return isError;
	}

	public void setIsError(String isError) {
		this.isError = isError;
	}

	public String getContractAddress() {
		return contractAddress;
	}

	public void setContractAddress(String contractAddress) {
		this.contractAddress = contractAddress;
	}

	public String getGasUsed() {
		return gasUsed;
	}

	public void setGasUsed(String gasUsed) {
		this.gasUsed = gasUsed;
	}

	public static long getSerialversionuid() {
		return serialVersionUID;
	}

	public String getSymbol() {
		return symbol;
	}

	public void setSymbol(String symbol) {
		this.symbol = symbol;
	}

	@Override
	public String toString() {
		return "TransactionHistory [blockNumber=" + blockNumber + ", timeStamp=" + timeStamp + ", hash=" + hash + ", from=" + from + ", to=" + to + ", value=" + value + ", gas=" + gas + ", gasPrice=" + gasPrice + ", isError=" + isError + ", contractAddress=" + contractAddress + ", gasUsed=" + gasUsed + ", symbol=" + symbol + "]";
	}

}

				
package api.repository;

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

@Repository
public interface TransactionHistoryRepository extends CrudRepository<TransactionHistory, Integer> {

	@Query(value = "SELECT * FROM transaction_history th WHERE (th.from_address = :address or th.to_address = :address) and contract_address is NULL",
			countQuery = "SELEÇT count(*) FROM transaction_history th WHERE (th.from_address = :address or th.to_address = :address) and contract_address is NULL",
			nativeQuery = true)
	public Page<TransactionHistory> findEthByAddress(@Param("address") String address, Pageable pageable);

}

5.3.5.7.4. 返回指定欄位

通過實體返回數據有時結果集非常龐大，可能會影響性能，這時我們只需要返回指定欄位即可。

				
@Query(value = "select u.userName, ui.name, ui.gender, ui.description from UserInfo ui, User u where u.id = ui.userId")
public List<Object> getCustomField();

5.3.5.7.5. 返回指定的模型

臨時寫一個新的模型

				
public class MyModel implements Serializable {

    private String userName;
    private String name;
    private String gender;
    private String description;

    public MyModel() {};

    public MyModel(String userName, String name, String gender, String description) {
        this.userName = userName;
        this.name = name;
        this.gender = gender;
        this.description = description;
    }
}

使用構造方法賦值

				
@Query(value = "select new cn.netkiller.model.MyModel(u.userName, ui.name, ui.gender, ui.description) from UserInfo ui, User u where u.id = ui.userId")
public List<MyModel> getAllRecord();

5.3.5.8. @Transactional

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

@Repository
public interface RecentReadRepostitory extends CrudRepository<RecentRead, Integer> {

	Page<RecentRead> 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.5.8.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.5.9. 鎖 @Lock

			
interface UserRepository extends Repository<User, Long> {

  // Plain query method
  @Lock(LockModeType.READ)
  List<User> findByLastname(String lastname);

5.3.6. CrudRepository

CrudRepository 介面提供了最基本的對實體類的添刪改查操作

		
T save(T entity);								//保存單個實體 
Iterable<T> save(Iterable<? extends T> entities);//保存集合        
T findOne(ID id);								//根據id查找實體         
boolean exists(ID id);							//根據id判斷實體是否存在         
Iterable<T> findAll();							//查詢所有實體,不用或慎用!         
long count();									//查詢實體數量         
void delete(ID id);								//根據Id刪除實體         
void delete(T entity);							//刪除一個實體 
void delete(Iterable<? extends T> entities);		//刪除一個實體的集合         
void deleteAll();								//刪除所有實體,不用或慎用!

		
public interface UserRepository extends CrudRepository<User, Long> {

    Long countByFirstName(String firstName);

    @Transactional
    Long deleteByFirstName(String firstName);

    @Transactional
    List<User> removeByFirstName(String firstName);

}

5.3.6.1. GreaterThan

			
package schedule.repository;

import java.util.Date;

import org.springframework.data.repository.CrudRepository;

import common.domain.CmsTrash;

public interface CmsTrashRepository extends CrudRepository<CmsTrash, Integer> {

	Iterable<CmsTrash> findBySiteIdAndTypeOrderByCtimeASC(int siteId, String string);

	Iterable<CmsTrash> findBySiteIdAndTypeAndCtimeGreaterThanOrderByCtimeASC(int siteId, String string, Date date);

}

5.3.7. JpaRepository

https://docs.spring.io/spring-data/jpa/docs/current/api/org/springframework/data/jpa/repository/JpaRepository.html

		
Modifier and Type	Method and Description
void	deleteAllInBatch()
Deletes all entities in a batch call.
void	deleteInBatch(Iterable<T> entities)
Deletes the given entities in a batch which means it will create a single Query.
List<T>	findAll() 
<S extends T>
List<S>	findAll(Example<S> example) 
<S extends T>
List<S>	findAll(Example<S> example, Sort sort) 
List<T>	findAll(Sort sort) 
List<T>	findAllById(Iterable<ID> ids) 
void	flush()
Flushes all pending changes to the database.
T	getOne(ID id)
Returns a reference to the entity with the given identifier.
<S extends T>
List<S>	saveAll(Iterable<S> entities) 
<S extends T>
S	saveAndFlush(S entity)
Saves an entity and flushes changes instantly.

上一頁	上一級	下一頁
5.2. Spring Data MongoDB	起始頁	5.4. EntityManager