知乎专栏 |
@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 + "]"; } }
定义相同的 name 可以实现组合索引
@Table(indexes = { @Index(name = "key_device_hobby", columnList = "device_id"), @Index(name = "key_device_hobby", columnList = "hobby") })
参考案例
package cn.netkiller.domain; import jakarta.persistence.*; import lombok.Data; import org.hibernate.annotations.Comment; import org.hibernate.annotations.DynamicInsert; import org.hibernate.annotations.DynamicUpdate; import java.io.Serializable; @Entity @Table(indexes = { @Index(name = "key_device_hobby", columnList = "device_id"), @Index(name = "key_device_hobby", columnList = "hobby") }) @DynamicUpdate @DynamicInsert @Data @Comment("用户画像") public class UserPersona implements Serializable { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) @Column(name = "id", unique = true, nullable = false, insertable = false, updatable = false, columnDefinition = "bigint unsigned") @Comment("主键") private Long id; @OneToOne @Comment("设备") @JoinColumn(name = "device_id", unique = true, nullable = false, insertable = true, updatable = false) private Device device; @Comment("兴趣爱好") private String hobby; @Comment("权重") private Integer weight; }
输出结果 KEY `key_device_hobby` (`device_id`,`hobby`)
CREATE TABLE `user_persona` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键', `hobby` varchar(255) DEFAULT NULL COMMENT '兴趣爱好', `weight` int DEFAULT NULL COMMENT '权重', `device_id` int unsigned NOT NULL COMMENT '设备', PRIMARY KEY (`id`), UNIQUE KEY `UK_5ib64k66pbo08dmhkr0i0uu7k` (`device_id`), KEY `key_device_hobby` (`device_id`,`hobby`), CONSTRAINT `FK8ar0rxcxej5u3q0l38xdpwke1` FOREIGN KEY (`device_id`) REFERENCES `device` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户画像'
在表中定义唯一索引
@Table(uniqueConstraints = @UniqueConstraint(columnNames= {"name"}))
创建由多个字段组成的复合索引
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.domain; import jakarta.persistence.*; import lombok.Data; import org.hibernate.annotations.Comment; import org.hibernate.annotations.DynamicInsert; import org.hibernate.annotations.DynamicUpdate; import java.io.Serializable; @Entity @Table(uniqueConstraints = {@UniqueConstraint(name = "UK_device_hobby", columnNames = {"device_id", "hobby"})}) @DynamicUpdate @DynamicInsert @Data @Comment("用户画像") public class UserPersona implements Serializable { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) @Column(name = "id", unique = true, nullable = false, insertable = false, updatable = false, columnDefinition = "int unsigned") @Comment("主键") private Integer id; @ManyToOne @Comment("设备") @JoinColumn(name = "device_id", unique = false, nullable = false, insertable = true, updatable = false) private Device device; @Comment("兴趣爱好") private String hobby; @Comment("权重") private Integer weight; }
CREATE TABLE `user_persona` ( `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '主键', `hobby` varchar(255) DEFAULT NULL COMMENT '兴趣爱好', `weight` int DEFAULT NULL COMMENT '权重', `device_id` int unsigned NOT NULL COMMENT '设备', PRIMARY KEY (`id`), UNIQUE KEY `UK_device_hobby` (`device_id`,`hobby`), CONSTRAINT `FK8ar0rxcxej5u3q0l38xdpwke1` FOREIGN KEY (`device_id`) REFERENCES `device` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户画像'