Spring Data with MySQL
节选自《Netkiller Spring Cloud 手札》
多维度架构 - 知乎www.zhihu.com
选择数据库表引擎
正常创建表会使用数据库默认引擎，有时数据库默认引擎并不是我们需要的，通过下面配置可以指定表引擎
# 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
声明实体
@Entity 声明实体
声明 Class 即是数据库表
@Entity
@Table
public class Your_table {
...
...
} @Table 定义表名
catalog
@Table(name="CUSTOMERS",catalog="hibernate")
schema
配置Schema
@Table(name="tabname", schema="public")
uniqueConstraints
唯一索引
@Table(name="CUSTOMERS",uniqueConstraints={@UniqueConstraint(columnNames={"name","email"})})
定义多组唯一索引
uniqueConstraints={@UniqueConstraint(columnNames={"name","email"}),@UniqueConstraint(columnNames={"name","age"})}
@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@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表示小数点所占的位数。
字段长度
字段长度定义
@Column(name="name", length=80, nullable=true)
浮点型
@Column(precision=18, scale=5)
private BigDecimal principal;
@Column(name="Price", columnDefinition="Decimal(10,2) default '100.00'") 创建于更新控制
@Column(name = "ctime", nullable = false, insertable = false, updatable = false)
TEXT 类型
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 @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;
} @NotNull 不能为空声明
@NotNull
public String username;
@Temporal 日期定义
@Entity
public class Article {
@Id
@GeneratedValue
Integer id;
@Temporal(TemporalType.DATE)
Date publicationDate;
@Temporal(TemporalType.TIME)
Date publicationTime;
@Temporal(TemporalType.TIMESTAMP)
Date creationDateTime;
} @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")
private Date createDate;默认时间规则
CreatedDate
Spring 提供了 import org.springframework.data.annotation.CreatedDate;
但是这些只能作用于实体类。
@CreatedDate
private Date createdDateTime;
与时间日期有关的 hibernate 注解
设置默认时间
@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; Enum 枚举数据类型
实体中处理 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;数据库枚举类型
在枚举中处理类型虽然可以适用于所有数据库，但有时我们希望适用数据库的枚举类型（例如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=utf8SET 数据结构
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) 完美实现
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) 
2882
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
