| 知乎專欄 ｜ 多維度架構 | | | 微信號 netkiller-ebook | | | QQ群：128659835 請註明“讀者” |
正常創建表會使用資料庫預設引擎，有時資料庫預設引擎並不是我們需要的，通過下面配置可以指定表引擎
# 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
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
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表示小數點所占的位數。
@Column(precision=18, scale=5)
private BigDecimal principal;
@Column(name="Price", columnDefinition="Decimal(10,2) default '100.00'")
private String subject; @Column(columnDefinition = "TEXT") private String content;
無符號整形
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
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;
}
@Entity
public class Article {
@Id
@GeneratedValue
Integer id;
@Temporal(TemporalType.DATE)
Date publicationDate;
@Temporal(TemporalType.TIME)
Date publicationTime;
@Temporal(TemporalType.TIMESTAMP)
Date creationDateTime;
}
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") private Date createDate;
Spring 提供了 import org.springframework.data.annotation.CreatedDate;
但是這些只能作用於實體類。
@CreatedDate
private Date createdDateTime;
@Column(insertable = false)
@org.hibernate.annotations.ColumnDefault("1.00")
@org.hibernate.annotations.Generated(
org.hibernate.annotations.GenerationTime.INSERT
)
protected Date lastModified;
@Temporal(TemporalType.TIMESTAMP) @Column(updatable = false) @org.hibernate.annotations.CreationTimestamp protected Date createdDate;
@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 )
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;
需求是這樣的：
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
需求：記錄最後一次修改時間
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;
@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;
在枚舉中處理類型雖然可以適用於所有資料庫，但有時我們希望適用資料庫的枚舉類型（例如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
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)
完美實現
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)
@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 + "]";
}
}
創建由多個欄位組成的復合索引
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;
定義實體
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);
}
@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 "";
}
一對一表結構，如下面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")
我們要實現一個一對多實體關係，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);
用戶與角色就是一個多對多的關係，多對多是需要中間表做關聯的。所以我方需要一個 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;
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 + "]";
}
}
CascadeType.PERSIST （級聯新建） CascadeType.REMOVE （級聯刪除） CascadeType.REFRESH （級聯刷新） CascadeType.MERGE （級聯更新）中選擇一個或多個。 CascadeType.ALL
當屍體返回 Json 資料結構是，將不包含 @JsonIgnore 定義變數。
@JsonIgnore
@OneToMany(mappedBy = "owner")
private List<Pet> pets;
@SpringBootApplication
@EnableJpaAuditing
public class Application {
public static void main(String[] args) throws Exception {
SpringApplication.run(Application .class, args);
}
}
在需要審計實體中加入 @EntityListeners(AuditingEntityListener.class)
@EntityListeners(AuditingEntityListener.class)
public class Member implements Serializable {
private static final long serialVersionUID = -6163675075289529459L;
@JsonIgnore
String entityName = this.getClass().getSimpleName();
@CreatedBy
String createdBy;
@LastModifiedBy
String modifiedBy;
/**
* 實體創建時間
*/
@Temporal(TemporalType.TIMESTAMP)
@CreatedDate
protected Date dateCreated = new Date();
/**
* 實體修改時間
*/
@Temporal(TemporalType.TIMESTAMP)
@LastModifiedDate
protected Date dateModified = new Date();
#省略getter setter
}
B、C 類繼承 A 所有屬性，並且主鍵均為資料庫（auto_increment）
@MappedSuperclass
@(strategy = InheritanceType.TABLE_PER_CLASS)
public class A{
@Id
@GeneratedValue(strategy=GenerationType.IDENTITY)
private int id;
}
@Entity
@Table(name="b")
public class B extends A{
}
@Entity
@Table(name="c")
public class C extends A{
}
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 類型，也可以是數組或者不定長
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(); //刪除所有實體,不用或慎用!
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.
@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();
}
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);
}
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);
public interface UserRepository extends CrudRepository<User, Long> {
Long countByFirstName(String firstName);
@Transactional
Long deleteByFirstName(String firstName);
@Transactional
List<User> removeByFirstName(String firstName);
}
public List<StudentEntity> findAllByOrderByIdAsc(); public List<StudentEntity> findAllByOrderByIdDesc(); List<RecentRead> findByMemberIdOrderByIdDesc(int memberId, Pageable pageable);
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);
}
翻頁返回數據可以選擇 Iterable/List 或者 Page。
Iterable/List 只返回數據，不含頁碼等數據
Page 返回數據和頁碼等數據
PageRequest(int page, int size, Sort sort) Deprecated. use PageRequest.of(int, int, Sort) instead.
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));
介面實現 PagingAndSortingRepository
package api.repository.h5;
import org.springframework.data.repository.PagingAndSortingRepository;
import api.domain.User;
public interface GatherRepository extends PagingAndSortingRepository<User, Integer> {
}
控製器添加 Pageable pageable 參數
@RequestMapping("/browse")
public ModelAndView browse(Pageable pageable) {
Page<User> users = userRepository.findAll(pageable);
System.out.println(users.toString());
ModelAndView mv = new ModelAndView();
mv.addObject("users", users.getContent());
mv.addObject("number", users.getNumber());
mv.addObject("size", users.getSize());
mv.addObject("totalPages", users.getTotalPages());
mv.setViewName("table");
return mv;
}
排序 /browse?sort=id,desc 每頁返回數量 /browse?size=10 返回第二頁5條數據 /browse?size=5&page=1 返回第二頁5條數據，ID倒序排序 /browse?size=5&page=1&sort=id,desc
List<UserModel> findByName(String name, Sort sort);
Sort sort = new Sort(Direction.DESC, "id");
repostitory.findByName("Neo", sort);
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);
}
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);
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);
}
通過實體返回數據有時結果集非常龐大，可能會影響性能，這時我們只需要返回指定欄位即可。
@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();
臨時寫一個新的模型
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();
下面介紹一下@Transactional註解的參數以及使用：
事物傳播行為介紹:
@Transactional(propagation=Propagation.REQUIRED) ：如果有事務, 那麼加入事務, 沒有的話新建一個(預設情況下)
@Transactional(propagation=Propagation.NOT_SUPPORTED) ：容器不為這個方法開啟事務
@Transactional(propagation=Propagation.REQUIRES_NEW) ：不管是否存在事務,都創建一個新的事務,原來的掛起,新的執行完畢,繼續執行老的事務
@Transactional(propagation=Propagation.MANDATORY) ：必須在一個已有的事務中執行,否則拋出異常
@Transactional(propagation=Propagation.NEVER) ：必須在一個沒有的事務中執行,否則拋出異常(與Propagation.MANDATORY相反)
@Transactional(propagation=Propagation.SUPPORTS) ：如果其他bean調用這個方法,在其他bean中聲明事務,那就用事務.如果其他bean沒有聲明事務,那就不用事務.
事物超時設置:
@Transactional(timeout=30) //預設是30秒
事務隔離級別:
@Transactional(isolation = Isolation.READ_UNCOMMITTED)：讀取未提交數據(會出現臟讀, 不可重複讀) 基本不使用
@Transactional(isolation = Isolation.READ_COMMITTED)：讀取已提交數據(會出現不可重複讀和幻讀)
@Transactional(isolation = Isolation.REPEATABLE_READ)：可重複讀(會出現幻讀)
@Transactional(isolation = Isolation.SERIALIZABLE)：串列化 MYSQL: 預設為REPEATABLE_READ級別 SQLSERVER: 預設為READ_COMMITTED
@Transactional註解中常用參數說明
注意的幾點:
@Transactional 只能被應用到public方法上, 對於其它非public的方法,如果標記了@Transactional也不會報錯,但方法沒有事務功能.
用 spring 事務管理器,由spring來負責資料庫的打開,提交,回滾.預設遇到運行期例外(throw new RuntimeException("註釋");)會回滾，即遇到不受檢查（unchecked）的例外時回滾；而遇到需要捕獲的例外(throw new Exception("註釋");)不會回滾,即遇到受檢查的例外（就是非運行時拋出的異常，編譯器會檢查到的異常叫受檢查例外或說受檢查異常）時，需我們指定方式來讓事務回滾要想所有異常都回滾,要加上 @Transactional( rollbackFor={Exception.class,其它異常}) .如果讓unchecked例外不回滾： @Transactional(notRollbackFor=RunTimeException.class)
@Transactional 註解應該只被應用到 public 可見度的方法上。 如果你在 protected、private 或者 package-visible 的方法上使用 @Transactional 註解，它也不會報錯， 但是這個被註解的方法將不會展示已配置的事務設置。
@Transactional 註解可以被應用於介面定義和介面方法、類定義和類的 public 方法上。然而，請注意僅僅 @Transactional 註解的出現不足於開啟事務行為，它僅僅 是一種元數據，能夠被可以識別 @Transactional 註解和上述的配置適當的具有事務行為的beans所使用。上面的例子中，其實正是 元素的出現 開啟 了事務行為。
Spring團隊的建議是你在具體的類（或類的方法）上使用 @Transactional 註解，而不要使用在類所要實現的任何介面上。你當然可以在介面上使用 @Transactional 註解，但是這將只能當你設置了基于介面的代理時它才生效。因為註解是不能繼承的，這就意味着如果你正在使用基于類的代理時，那麼事務的設置將不能被基于類的代理所識別，而且對象也將不會被事務代理所包裝（將被確認為嚴重的）。因此，請接受Spring團隊的建議並且在具體的類上使用 @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);
}