jpa mysql索引_Spring Data with MySQL (索引)

Spring Data with MySQL多维度架构 - 知乎​www.zhihu.com

索引

普通索引

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

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 getCategory() {

return category;

}

public void setCategory(Set 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 + "]";

}

}

唯一索引

针对字段做唯一索引

@Column(unique = true)

复合索引

创建由多个字段组成的复合索引

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 findOneByPrimaryKey(UserTokenPrimaryKey primaryKey);

@Query("select ut from UserToken ut where ut.primaryKey.address=:address")

List getByAddress(@Param("address") String address);

@Query("select ut from UserToken ut where ut.primaryKey.address=:address and ut.primaryKey.contractAddress=:contractAddress")

List findByPrimaryKey(@Param("address") String address, @Param("contractAddress") String contractAddress);

}

@JoinColumn

@JoinColumn与@Column注释类似，它的定义如下代码所示。

@Target({METHOD, FIELD}) @Retention(RUNTIME)

public @interface JoinColumn {

String name() default "";

String referencedColumnName() default "";

boolean unique() default false;

boolean nullable() default true;

boolean insertable() default true;

boolean updatable() default true;

String columnDefinition() default "";

String table() default "";

}

@OneToOne

一对一表结构，如下面ER图所示，users表是用户表里面有登陆信息，profile 保存的时死人信息，这样的目的是我们尽量减少users表的字段，在频繁操作该表的时候性能比较好，另外一个目的是为了横向水平扩展。

+----------+ +------------+

| users | | profile |

+----------+ +------------+

| 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")netkiller：Spring Data with MySQL (实体定义)​zhuanlan.zhihu.comnetkiller：Spring Data with MongoDB (四)​zhuanlan.zhihu.comnetkiller：Spring Data with MongoDB (三)​zhuanlan.zhihu.comnetkiller：Spring Data with MongoDB (二)​zhuanlan.zhihu.comnetkiller：Spring Data with MongoDB (一)​zhuanlan.zhihu.comnetkiller：Spring Data with Redis​zhuanlan.zhihu.comnetkiller：Spring boot with i18n​zhuanlan.zhihu.comnetkiller：Spring boot with Validator​zhuanlan.zhihu.comnetkiller：Spring Boot with Web Security​zhuanlan.zhihu.comnetkiller：Spring boot with Spring security​zhuanlan.zhihu.comnetkiller：Spring Security with HTTP Auth​zhuanlan.zhihu.com