Jpa屏蔽表之间的主外键关联

技术交流
1

Jpa屏蔽表之间的主外键关联

Jpa根据实体对象生成的表，默认是带有主外键关系的。解决这个问题也简单。

通过标识注解 @ForeignKey(value = ConstraintMode.NO_CONSTRAINT) 屏蔽

这里使用5个实体演示

  • User
  • UserSeting
  • Address
  • Role

一对一，一对多，多对多都关系到了

实体类

@Entity
@Table(name = "user")
public class User implements Serializable {
	
	private static final long serialVersionUID = 8175166175439387541L;
	
	@Id
	@Column(name = "id", columnDefinition = "INT(11) UNSIGNED COMMENT '用户id'")
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private Integer id;
	
	@Column(columnDefinition = "varchar(20) COMMENT '昵称'")
	private String name;
	
	// 设置
	@OneToOne(fetch = FetchType.LAZY)
	@JoinColumn(name = "id", referencedColumnName= "user_id", foreignKey = @ForeignKey(value = ConstraintMode.NO_CONSTRAINT))
	private UserSeting userSeting;
	
	// 角色
	@ManyToMany(fetch = FetchType.LAZY)
	@JoinTable(name = "user_role", joinColumns = {
		@JoinColumn(name = "user_id", referencedColumnName = "id")
	}, inverseJoinColumns = {
		@JoinColumn(name = "role_id", referencedColumnName = "id")
	}, foreignKey = @ForeignKey(value = ConstraintMode.NO_CONSTRAINT), inverseForeignKey = @ForeignKey(value = ConstraintMode.NO_CONSTRAINT))
	private Set<Role> roles = new HashSet<>();

	// 地址
	@OneToMany(fetch = FetchType.LAZY)
	@JoinColumn(name = "user_id", referencedColumnName = "id",  foreignKey = @ForeignKey(value = ConstraintMode.NO_CONSTRAINT))
	private Set<Address> addresses = new HashSet<>();
}

@Entity
@Table(name = "user_seting")
public class UserSeting implements Serializable {
	
	/**
	 * 
	 */
	private static final long serialVersionUID = -1007318207008996614L;

	@Id
	@Column(name = "id", columnDefinition = "INT(11) unsigned COMMENT '设置id'")
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private Integer id;
	
	@Column(columnDefinition = "TINYINT(1) unsigned COMMENT '是否接受通知'")
	private Boolean notify;
	
	@OneToOne(fetch = FetchType.LAZY)
	@JoinColumn(name = "user_id", referencedColumnName = "id", unique = true, nullable = false, 
			foreignKey = @ForeignKey(value = ConstraintMode.NO_CONSTRAINT))
	private User user;
}

@Entity
@Table(name = "address", indexes = {
	@Index(columnList = "user_id", unique = false)
})
public class Address implements Serializable{

	private static final long serialVersionUID = -544913276641093655L;
	
	@Id
	@Column(name = "id", columnDefinition = "INT(11) UNSIGNED COMMENT '地址id'")
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private Integer id;
	
	@Column(columnDefinition = "VARCHAR(500) COMMENT '详细地址'")
	private String name;
	
	@ManyToOne(fetch = FetchType.LAZY)
	@JoinColumn(name = "user_id", referencedColumnName = "id", nullable = false, 
		foreignKey = @ForeignKey(value = ConstraintMode.NO_CONSTRAINT))
	private User user;
}

@Entity
@Table(name = "role")
public class Role {
	
	@Id
	@Column(name = "id", columnDefinition = "INT(11) UNSIGNED COMMENT '角色id'")
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private Integer id;
	
	@Column(columnDefinition = "varchar(20) COMMENT '角色名称'")
	private String name;
	
	@ManyToMany(fetch = FetchType.LAZY)
	@JoinTable(name = "user_role", joinColumns = {
		@JoinColumn(name = "role_id", referencedColumnName = "id")
	}, inverseJoinColumns = {
		@JoinColumn(name = "user_id", referencedColumnName = "id")
	}, foreignKey = @ForeignKey(value = ConstraintMode.NO_CONSTRAINT), inverseForeignKey = @ForeignKey(value = ConstraintMode.NO_CONSTRAINT))
	private Set<User> users = new HashSet<>();
}

生成的表结构

没有主外键关系，很干净

CREATE TABLE `user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '用户id',
  `name` varchar(20) DEFAULT NULL COMMENT '昵称',
  `version` int(11) unsigned NOT NULL COMMENT '版本号',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `user_seting` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '设置id',
  `notify` tinyint(1) unsigned DEFAULT NULL COMMENT '是否接受通知',
  `user_id` int(11) unsigned NOT NULL COMMENT '用户id',
  PRIMARY KEY (`id`),
  UNIQUE KEY `UK_rdchrax5rp1m1y4kpax285krw` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `address` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '地址id',
  `name` varchar(500) DEFAULT NULL COMMENT '详细地址',
  `user_id` int(11) unsigned NOT NULL COMMENT '用户id',
  PRIMARY KEY (`id`),
  KEY `IDX7rod8a71yep5vxasb0ms3osbg` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `role` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '角色id',
  `name` varchar(20) DEFAULT NULL COMMENT '角色名称',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `user_role` (
  `user_id` int(11) unsigned NOT NULL COMMENT '用户id',
  `role_id` int(11) unsigned NOT NULL COMMENT '角色id',
  PRIMARY KEY (`role_id`,`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

总结

注解加持就完事儿了，不是添加在 @JoinColumn 就是添加在 @JoinTable

@JoinColumn(foreignKey = @ForeignKey(value = ConstraintMode.NO_CONSTRAINT))
@JoinTable(foreignKey = @ForeignKey(value = ConstraintMode.NO_CONSTRAINT), inverseForeignKey = @ForeignKey(value = ConstraintMode.NO_CONSTRAINT))

jpa + querydsl 是真的香 :rofl:

2

QSCZVS4DDZ8N(}GJ{YI)CXW

3

学习了