Home | 简体中文 | 繁体中文 | 杂文 | Github | 知乎专栏 | Facebook | Linkedin | Youtube | 打赏(Donations) | About
51.2. 声明实体
上一页 第 51 章 Spring Data with MySQL 下一页
知乎专栏

51.2. 声明实体

51.2.1. @Entity 声明实体

声明 Class 即是数据库表

			
@Entity
@Table
public class Your_table {
	...
	...
}

51.2.2. @Table 定义表名

51.2.2.1. catalog

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

51.2.2.2. schema

配置Schema

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

51.2.2.3. uniqueConstraints

唯一索引

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

定义多组唯一索引

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

51.2.3. @Id 定义主键

ID 字段，数据库中的主键。

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

@GeneratedValue 主键生成策略：

			
@GeneratedValue(strategy= GenerationType.IDENTITY) 	该注解由数据库自动生成，AUTO_INCREMENT 自增主键，在 mysql 数据库中使用最频繁，oracle 不支持。
@GeneratedValue(strategy= GenerationType.AUTO) 		主键由程序控制，默认的主键生成策略，oracle 默认是序列化的方式，mysql 默认是主键自增的方式。
@GeneratedValue(strategy= GenerationType.SEQUENCE) 	根据底层数据库的序列来生成主键，条件是数据库支持序列，Oracle支持，Mysql不支持。
@GeneratedValue(strategy= GenerationType.TABLE) 	使用一个特定的数据库表格来保存主键，较少使用。

Long = bigint

			
package cn.netkiller.domain;

import jakarta.persistence.*;
import lombok.Data;

import java.io.Serial;
import java.io.Serializable;
import java.util.Date;

@Entity
@Table
@Data
public class Picture implements Serializable {
    @Serial
    public 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 String device;
    private String model;
    private String session;
    private String prompt;
    private String thumbnail;
    private String image;
    private String story;
    private boolean share;
    private int likes;
    private int favorites;
    private int forward;
    private Date ctime;
    private Date mtime;

}
			
			
			

			
CREATE TABLE `picture` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `ctime` datetime(6) DEFAULT NULL,
  `favorites` int NOT NULL,
  `image` varchar(255) DEFAULT NULL,
  `likes` int NOT NULL,
  `mtime` datetime(6) DEFAULT NULL,
  `prompt` varchar(255) DEFAULT NULL,
  `session` varchar(255) DEFAULT NULL,
  `share` bit(1) NOT NULL,
  `story` varchar(255) DEFAULT NULL,
  `thumbnail` varchar(255) DEFAULT NULL,
  `device` varchar(255) DEFAULT NULL,
  `model` varchar(255) DEFAULT NULL,
  `forward` int NOT NULL
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

字符串做主键

			
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

51.2.4. @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表示小数点所占的位数。	
scale		int	列的精度，仅对十进制数值有效，表示小数位的总位数。默认为0。

51.2.4.1. 字段长度

字段长度定义

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

51.2.4.2. 浮点型

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

51.2.4.3. 创建于更新控制

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

51.2.4.4. TEXT 类型

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

51.2.4.5. 整形数据类型

无符号整形

			
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

51.2.5. 非数据库字段

@Transient 该注解标注的字段不会被应射到数据库当中

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

51.2.7. @NotNull 不能为空声明

			
	@NotNull
	public String username;

51.2.8. @Temporal 日期定义

			
@Entity
public class Article {
 
    @Id
    @GeneratedValue
    Integer id;
  
    @Temporal(TemporalType.DATE)
    Date publicationDate;
  
    @Temporal(TemporalType.TIME)
    Date publicationTime;
  
    @Temporal(TemporalType.TIMESTAMP)
    Date creationDateTime;
}

51.2.9. 创建日期

			
    @Column(name = "create_at")
    @CreatedDate
    private Timestamp create_date;

51.2.9.1. CreatedDate

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

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

				
	@CreatedDate
    private Date createdDateTime;

51.2.9.2. 与时间日期有关的 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  
)

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

51.2.9.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

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

51.2.10. @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;
			
			
			
    /**
     * 日期 DATE  YYYY-MM-DD
     */
    @Column(name = "create_date")
    @JsonFormat(shape= JsonFormat.Shape.STRING,pattern="yyyy-MM-dd HH:mm:ss",timezone="GMT+8")
    private Date date;

下面我们实际演示一下，例如默认返回 "ctime": "2024-01-25T08:07:39.000+00:00" 这样的格式 

			
HTTP/1.1 200 
Content-Type: application/json
Transfer-Encoding: chunked
Date: Thu, 25 Jan 2024 08:10:32 GMT
Connection: close

{
  "status": true,
  "code": "SUCCESS",
  "data": [
    {
      "id": 3918,
      "session": "27310934-4159-4bc9-8142-67a3780faf35",
      "progress": "1.音频上传",
      "description": "test.amr",
      "ctime": "2024-01-25T08:07:16.000+00:00"
    },
    {
      "id": 3919,
      "session": "27310934-4159-4bc9-8142-67a3780faf35",
      "progress": "2.音频转换",
      "description": "AMR 转 PCM",
      "ctime": "2024-01-25T08:07:16.000+00:00"
    },
    {
      "id": 3920,
      "session": "27310934-4159-4bc9-8142-67a3780faf35",
      "progress": "3.语音识别",
      "description": "小明是个调皮的小男孩，他觉得地板上有很多污垢，于是他拿起了牙刷用它来刷地板，虽然效果不佳，但是小明却觉得是一种有趣的尝试。",
      "ctime": "2024-01-25T08:07:36.000+00:00"
    },
    {
      "id": 3921,
      "session": "27310934-4159-4bc9-8142-67a3780faf35",
      "progress": "4.内容合规",
      "description": "合规：小明是个调皮的小男孩，他觉得地板上有很多污垢，于是他拿起了牙刷用它来刷地板，虽然效果不佳，但是小明却觉得是一种有趣的尝试。",
      "ctime": "2024-01-25T08:07:36.000+00:00"
    },
    {
      "id": 3922,
      "session": "27310934-4159-4bc9-8142-67a3780faf35",
      "progress": "5.故事创作",
      "description": "小明，一个满腔调皮的小男孩，发现家里仿佛被污秽覆盖。他拿起牙刷，一面一面地在地板上划过，结果却不尽人意。然而，这个看似毫无预期的尝试，他自己却获得了无比的乐趣。\n\n问题：小明的行为可能使他的生活环境变得更糟糕，你会如何帮助他改变这种状况？",
      "ctime": "2024-01-25T08:07:36.000+00:00"
    },
    {
      "id": 3923,
      "session": "27310934-4159-4bc9-8142-67a3780faf35",
      "progress": "6.语音合成",
      "description": "http://oss.test.netkiller.cn/2024/01/25/27310934-4159-4bc9-8142-67a3780faf35.mp3",
      "ctime": "2024-01-25T08:07:39.000+00:00"
    }
  ],
  "reason": "操作成功"
}

加入 @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss") 注解

			
package cn.netkiller.domain;

import com.fasterxml.jackson.annotation.JsonFormat;
import jakarta.persistence.*;
import lombok.Data;
import org.hibernate.annotations.Comment;
import org.hibernate.annotations.DynamicUpdate;

import java.io.Serial;
import java.io.Serializable;
import java.util.Date;

@Entity
@Table
@DynamicUpdate
@Data
public class SessionStatus implements Serializable {
    @Serial
    public static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", unique = true, nullable = false, insertable = false, updatable = false, columnDefinition = "int unsigned")
    @Comment("主键")
    private Long id;

    @Comment("会话主键")
    private String session;

    @Comment("进度")
    private String progress;

    @Lob
    @Basic(fetch = FetchType.LAZY)
    @Column(nullable = true, columnDefinition = "text")
    @Comment("描述")
    private String description;

    @Column(insertable = false, updatable = false, columnDefinition = "TIMESTAMP DEFAULT CURRENT_TIMESTAMP")
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    @Comment("创建时间")
    private Date ctime;
}

日期被格式化为 "ctime": "2024-01-25 08:07:16"

			
HTTP/1.1 200 
Content-Type: application/json
Transfer-Encoding: chunked
Date: Thu, 25 Jan 2024 08:12:10 GMT
Connection: close

{
  "status": true,
  "code": "SUCCESS",
  "data": [
    {
      "id": 3918,
      "session": "27310934-4159-4bc9-8142-67a3780faf35",
      "progress": "1.音频上传",
      "description": "test.amr",
      "ctime": "2024-01-25 08:07:16"
    },
    {
      "id": 3919,
      "session": "27310934-4159-4bc9-8142-67a3780faf35",
      "progress": "2.音频转换",
      "description": "AMR 转 PCM",
      "ctime": "2024-01-25 08:07:16"
    },
    {
      "id": 3920,
      "session": "27310934-4159-4bc9-8142-67a3780faf35",
      "progress": "3.语音识别",
      "description": "小明是个调皮的小男孩，他觉得地板上有很多污垢，于是他拿起了牙刷用它来刷地板，虽然效果不佳，但是小明却觉得是一种有趣的尝试。",
      "ctime": "2024-01-25 08:07:36"
    },
    {
      "id": 3921,
      "session": "27310934-4159-4bc9-8142-67a3780faf35",
      "progress": "4.内容合规",
      "description": "合规：小明是个调皮的小男孩，他觉得地板上有很多污垢，于是他拿起了牙刷用它来刷地板，虽然效果不佳，但是小明却觉得是一种有趣的尝试。",
      "ctime": "2024-01-25 08:07:36"
    },
    {
      "id": 3922,
      "session": "27310934-4159-4bc9-8142-67a3780faf35",
      "progress": "5.故事创作",
      "description": "小明，一个满腔调皮的小男孩，发现家里仿佛被污秽覆盖。他拿起牙刷，一面一面地在地板上划过，结果却不尽人意。然而，这个看似毫无预期的尝试，他自己却获得了无比的乐趣。\n\n问题：小明的行为可能使他的生活环境变得更糟糕，你会如何帮助他改变这种状况？",
      "ctime": "2024-01-25 08:07:36"
    },
    {
      "id": 3923,
      "session": "27310934-4159-4bc9-8142-67a3780faf35",
      "progress": "6.语音合成",
      "description": "http://oss.test.netkiller.cn/2024/01/25/27310934-4159-4bc9-8142-67a3780faf35.mp3",
      "ctime": "2024-01-25 08:07:39"
    }
  ],
  "reason": "操作成功"
}

51.2.11. Enum 枚举数据类型

Enum 枚举数据类型 MySQL 特殊数据类型

51.2.11.1. 实体中处理 enum 类型，存储字符串

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

51.2.11.2. 实体中处理 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;

51.2.11.3. 数据库枚举类型

在枚举中处理类型虽然可以适用于所有数据库，但有时我们希望适用数据库的枚举类型（例如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

51.2.11.4. 自定义枚举value属性

				
public enum Gender {
    MALE("男士"),
    FEMALE("女士");

    private final String value;

    private Gender(String value) {
        this.value = value;
    }
    
    // value 转枚举
    public static Gender fromValue(String value) {
        for (Gender gender : values()) {
            if (gender.toValue().equals(value)) {
                return gender;
            }
        }
        return null;
    }

    // 枚举转 value
    public String toValue() {
        return value;
    }
}

创建 Gender 的自定义转换器

				
// 实现 AttributeConverter 接口
java复制代码public class GenderConverter implements AttributeConverter<Gender, String> {
    @Override
    public String convertToDatabaseColumn(Gender gender) {
        return gender.toValue();
    }

    @Override
    public Gender convertToEntityAttribute(String value) {
        return Gender.fromValue(value);
    }
}

在实体中，枚举字段加 @Convert 注解

				
@Convert(converter = GenderConverter.class)
@Column(name = "gender")
private Gender gender;

51.2.12. 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)

完美实现

51.2.13. 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)

51.2.14. 嵌入

51.2.14.1. @Embeddable / @Embedded

				
package cn.netkiller.domain.demo;

import jakarta.persistence.Embeddable;

@Embeddable
public class Address {

    private String city;
    private String district;
    private String street;
    private String community;
}
				
				
				
				
package cn.netkiller.domain.demo;

import jakarta.persistence.*;

@Entity
public class Company {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;
    private String name;

    @Embedded
    private Address address;
}

				
				
				
CREATE TABLE `company` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `city` varchar(255) DEFAULT NULL,
  `community` varchar(255) DEFAULT NULL,
  `district` varchar(255) DEFAULT NULL,
  `street` varchar(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

51.2.14.2. @AttributeOverrides 定义字段名称

				
@Data
@Embeddable
public class Address {
    private String province;
    private String city;
    private String street;
}
				
				
				
@Data
@Entity
@Table
public class Company {
    @Id
    @GeneratedValue
    private Long id;
 
    @Column
    private String name;
 
    // 公司地址
    private Address address;
 
    // 注册地址
    @AttributeOverrides({
        @AttributeOverride(name = "city", column = @Column(name= "location_city")),
        @AttributeOverride(name = "province", column=@Column(name="location_province")),
        @AttributeOverride(name = "street", column = @Column(name="location_street"))
    })
    private Address locationAddress;
 
}

51.2.14.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);
}

51.2.15. @JsonIgnore

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

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

51.2.16. @EnableJpaAuditing 开启 JPA 审计功能

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

51.2.17. 注释 @Comment

			
    @Column(columnDefinition = "int unsigned NOT NULL DEFAULT '0'")
    @Comment("点赞")
    private int likes;
    @Column(columnDefinition = "int unsigned NOT NULL DEFAULT '0'")
    @Comment("收藏")
    private int favorites;
    @Column(columnDefinition = "int unsigned NOT NULL DEFAULT '0'")
    @Comment("转发")
    private int forward;

51.2.18. @Pattern 数据匹配

			
    /**
     * 性别 CHAR(1) 0:女 1:男
     */
    @Pattern(regexp = "[01]")
    @Column(name = "gender",columnDefinition = "char(1)")
    private String gender;

    /**
     * 身份证号 CHAR(18)
     */
    @Pattern(regexp = "^([1-6][1-9]|50)\\d{4}(18|19|20)\\d{2}((0[1-9])|10|11|12)(([0-2][1-9])|10|20|30|31)\\d{3}[0-9Xx]$")
    @Column(name = "identityCard",columnDefinition = "char(18)")
    private String identityCard;

    /**
     * 所属部门 CHAR(2)  01：金融一部, 02：金融二部, 03：创新中心
     */
    @Pattern(regexp = "(01|02|03)")
    @Column(name = "department",columnDefinition = "char(2)")
    private String department;

51.2.19. 实体继承

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{

}
上一页 上一级 下一页
第 51 章 Spring Data with MySQL 起始页 51.3. 映射集合属性