Spring Data Jpa 一对多不使用物理外键

简介

使用Jpa的时候,两个表存在一对多的关联关系,又不想使用外键。

@EqualsAndHashCode(callSuper = true)
@Entity
@Data
@NoArgsConstructor
public class Father extends AbstractPersistable<Integer> {
    @Column
    private String name;

    @OneToMany
    private List<Son> sons = new ArrayList<>();

    public Father(String name) {
        this.name = name;
    }
}

@EqualsAndHashCode(callSuper = true)
@Data
@Entity
public class Son extends AbstractPersistable<Integer> {
    @Column
    private String name;

    @ManyToOne
    private Father father;
}

结果:发现自动生成了一个中间表,在son表中设置了一个外键,并不是想要的结果。

Hibernate: create table father (id integer not null, name varchar(255), primary key (id))
Hibernate: create table father_sons (father_id integer not null, sons_id integer not null)
Hibernate: create table son (id integer not null, name varchar(255), father_id integer, primary key (id))
Hibernate: alter table father_sons add constraint UK_f5nfp8iipn7nks1exv9dvyh34 unique (sons_id)
Hibernate: alter table father_sons add constraint FKt7xmjie9e1i3b836i6qx0wkg0 foreign key (sons_id) references son
Hibernate: alter table father_sons add constraint FKitnq4v5xm9sv3qhxit1upg4p7 foreign key (father_id) references father
Hibernate: alter table son add constraint FKlj2lfhydak8vsb5sbcu6hq0r9 foreign key (father_id) references father

修改如下:

 	@OneToMany(mappedBy = "father")
    private List<Son> sons = new ArrayList<>();

    @ManyToOne
    @JoinColumn
    private Father father;

修改之后还是让f_id字段生成了一个外键约束,我的目的是f_id这个字段不是外键约束的,而是要靠应用程序来保证。

Hibernate: create table father (id integer not null, name varchar(255), primary key (id))
Hibernate: create table son (id integer not null, name varchar(255), father_id integer, primary key (id))
Hibernate: alter table son add constraint FKlj2lfhydak8vsb5sbcu6hq0r9 foreign key (father_id) references father

修改为@JoinColumn(foreignKey = @ForeignKey(name = “none”,value = ConstraintMode.NO_CONSTRAINT))
结果还是和上面一样。

修改,最终代码如下:

@EqualsAndHashCode(callSuper = true)
@Entity
@Data
@NoArgsConstructor
public class Father extends AbstractPersistable<Integer> {
    @Column
    private String name;

    @OneToMany(mappedBy = "father")
    @org.hibernate.annotations.ForeignKey(name = "none")
    private List<Son> sons = new ArrayList<>();

    public Father(String name) {
        this.name = name;
    }
}

@EqualsAndHashCode(callSuper = true)
@Data
@Entity
public class Son extends AbstractPersistable<Integer> {
    @Column
    private String name;

    @ManyToOne
    @JoinColumn(foreignKey = @ForeignKey(name = "none",value = ConstraintMode.NO_CONSTRAINT))
    private Father father;
}
Hibernate: create table father (id integer not null, name varchar(255), primary key (id))
Hibernate: create table son (id integer not null, name varchar(255), father_id integer, primary key (id))

在这里插入图片描述
但是,father_id是有必要加上索引的。

@EqualsAndHashCode(callSuper = true)
@Data
@Entity
@Table(
        indexes = {
                @Index(columnList = "father_id")
        }
)
public class Son extends AbstractPersistable<Integer> {
    @Column
    private String name;

    @ManyToOne
    @JoinColumn(columnDefinition = "father_id",foreignKey = @ForeignKey(name = "none",value = ConstraintMode.NO_CONSTRAINT))
    private Father father;
}


成功解决。

Hibernate: create table father (id integer not null, name varchar(255), primary key (id))
Hibernate: create table son (id integer not null, name varchar(255), father_id integer, primary key (id))
Hibernate: create index IDXopuaw4gyeg5x9mt0kf78s90dj on son (father_id)

在这里插入图片描述
这里已经解决了。达到了关联不使用物理外键,逻辑外键加上索引的效果。

但是@org.hibernate.annotations.ForeignKey是一个Deprecated的注解,
经过尝试,我找到了完美的解决方案。

@EqualsAndHashCode(callSuper = true)
@Entity
@Data
public class Teacher extends AbstractPersistable<Integer> {
    String name;

    @Transient
    List<Student> students;
}




@EqualsAndHashCode(callSuper = true)
@Entity
@Table(
        indexes = {
                @Index(columnList = "teacher_id")
        }
)
@Data
public class Student extends AbstractPersistable<Integer> {
    String name;

	@Transient
    Teacher teacher;

    @Column(name = "teacher_id")
    Integer teacherId;
}

在这里插入图片描述
student新建一列teacher_id作为逻辑外键,加上索引,然后teacher和学生的关联完全交给应用程序负责。所有的表在数据库看来没有任何关联。

有人提出条件关联查询怎么办,这里研究一下。

  1. 考虑一对一关联条件查询,比如如下情景:
    一个Person关联一个Apple。Person和Apple两张表都有一个flag属性,这里假设要求查出所有Person,要求自己的flag为1并且对应的Apple的flag为1,这里演示 与的情况,与或非做法相同。

@Entity
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Person {
  @Id
  private Integer id;

  private Integer flag;

  public Person(Integer id,Integer flag) {
    this.id = id;
    this.flag = flag;
  }

  @Transient
  private Apple apple;
}

@Entity
@Data
@NoArgsConstructor
@AllArgsConstructor
@Table(
        indexes = {
                @Index(columnList = "person_id")
        }
)
public class Apple {

  @Id
  private Integer id;

  private Integer flag;

  @Column(name = "person_id")
  private Integer personId;
}

查询接口:


public interface PersonRepository extends JpaRepository<Person,Integer> {
  @Query("select new Person (p.id,p.flag,a)  from Person p join Apple a on a.personId = p.id " +
          "where  p.flag = ?1 and a.flag = ?2")
  List<Person> p2(int personFlag,int appleFlag);
}

测试

System.out.println(personRepository.p2(1,1))

sql日志

Hibernate: select person0_.id as col_0_0_, person0_.flag as col_1_0_, apple1_.id as col_2_0_ from person person0_ inner join apple apple1_ on (apple1_.person_id=person0_.id) where person0_.flag=? and apple1_.flag=?
Hibernate: select apple0_.id as id1_0_0_, apple0_.flag as flag2_0_0_, apple0_.person_id as person_i3_0_0_ from apple apple0_ where apple0_.id=?
Hibernate: select apple0_.id as id1_0_0_, apple0_.flag as flag2_0_0_, apple0_.person_id as person_i3_0_0_ from apple apple0_ where apple0_.id=?

查询结果:

[Person(id=1, flag=1, apple=Apple(id=1, flag=1, personId=1)), Person(id=2, flag=1, apple=Apple(id=2, flag=1, personId=2))]

我这里只演示了join一张表的写法,join多张表,多种条件的写法也是相同的。
一对一多表查询,条件存在与或非的问题解决。

  • 2
    点赞
  • 19
    收藏
    觉得还不错? 一键收藏
  • 18
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 18
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值