sql - Spring JPA + Hibernate 获取无关数据

Question

我需要一些关于spring jpa和hibernate的帮助。我对这两种技术都是新手，在没有运气的情况下尝试了很少的解决方案。所以想问问专家。

我们有 3 个数据库表：

表 1：成员

+----------------+
| id   |   Name  |
+------+---------+
|  M1  | Member1 |
+------+---------+

表 2：账户

+----------------------+
|  id |  Member_id(FK) |
+-----|----------------+
|  A1 |     M1         |
|-----|----------------|
|  A2 |     M1         |
|-----|----------------|
|  A3 |     M1         |
+-----+----------------+

表 3：限制

+-------+---------+----------------+
|   ID  |  LIMIT  | ACCOUNT_ID(FK) |
+-------+---------+----------------+
|   L1  |  1000   | A2             |
+-------+---------+----------------+

成员.java

@Cacheable
@org.hibernate.annotations.Cache(usage = CacheConcurrencyStrategy.READ_ONLY)
@Data
@Entity
@Table(name = "MEMBERSHIP")
public class Member implements Serializable {

    @Id
    @Column(name = "ID")
    private Long id;

    @Column(name = "NAME")
    private String name;
}

账户.java

@Cacheable
@org.hibernate.annotations.Cache(usage = CacheConcurrencyStrategy.READ_ONLY)
@Data
@Entity
@Table(name = "ACCOUNT")
public class Account implements Serializable {

    @Id
    @Column(name = "ID")
    private Long id;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "MEMBER_ID")
    private Member member;

    @org.hibernate.annotations.Cache(usage =         CacheConcurrencyStrategy.READ_ONLY)
    @OneToMany(
            mappedBy = "account",
            fetch = FetchType.LAZY
    )
    private List<Limit> limits = new ArrayList<>();
}

限制.java

@Cacheable
@org.hibernate.annotations.Cache(usage = CacheConcurrencyStrategy.READ_ONLY)
@Data
@Entity
@Table(name = "LIMIT")
public class Limit implements Serializable {

    @Id
    @Column(name = "ID")
    private Long id;

    @ManyToOne(fetch = FetchType.LAZY)
    //    @JoinFormula("select ACCOUNT_ID from LIMIT where ACCOUNT_ID in " +
    //            "(select id from ACCOUNT where MEMBER_ID = :MEMBER_ID)")

    @JoinColumn(name = "ACCOUNT")
    //    @JoinColumnsOrFormulas({
    //            @JoinColumnOrFormula(formula=@JoinFormula(value="(select     ACCOUNT_ID from LIMIT where ACCOUNT_ID in (select id from ACCOUNT where MEMBER_ID     = :MEMBER_ID))", referencedColumnName="CLEARING_MEMBER_ID"))
    //            @JoinColumnOrFormula(column = @JoinColumn("MEMBER_ID",     referencedColumnName="MEMBER_ID"))
    //    })

    private Account account;

}

现在，通过上述设置和代码，当我获得帐户列表时，我获得了为每个帐户配置的限制。但要求是获取该帐户所属成员的任何帐户的限制列表。

因此，当前查询如下所示：

Select * from limit where account_id = ?

我希望 JPA 执行的预期查询是：

Select * from limit l, account a, member m
where l.account_id = a.id
and a.member_id = m.id
and m.id = ?

这里的值 ? 将是 account.member.id

我知道数据库结构是错误的，它不符合业务需求，但我现在无法更改它，它是遗留问题。

我尝试了许多解决方案，例如尝试查看是否可以使用@JoinFormula，但似乎没有任何效果。

任何帮助，将不胜感激。

标签： sqlhibernatespring-data-jpa

Answer 1

这些变化可能会对您有所帮助，

在成员实体中，

@Cacheable
@org.hibernate.annotations.Cache(usage = CacheConcurrencyStrategy.READ_ONLY)
@Data
@Entity
@Table(name = "MEMBERSHIP")
public class Member implements Serializable {

@Id
@Column(name = "ID")
private Long id;

@Column(name = "NAME")
private String name;

@OneToMany(
        mappedBy = "member",
        fetch = FetchType.LAZY,
        cascade = CascadeType.ALL,
        orphanRemoval = true
)
private List<Account> accounts;
}

在帐户实体中，

@Cacheable
@org.hibernate.annotations.Cache(usage = CacheConcurrencyStrategy.READ_ONLY)
@Data
@Entity
@Table(name = "ACCOUNT")
public class Account implements Serializable {

@Id
@Column(name = "ID")
private Long id;

@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "MEMBER_ID",
        referencedColumnName="id")
private Member member;

@org.hibernate.annotations.Cache(usage =         CacheConcurrencyStrategy.READ_ONLY)
@OneToMany(
        mappedBy = "account",
        fetch = FetchType.LAZY,
        cascade = CascadeType.ALL,
        orphanRemoval = true
)
private List<Limit> limits;
}

在限制实体中，

@Cacheable
@org.hibernate.annotations.Cache(usage = CacheConcurrencyStrategy.READ_ONLY)
@Data
@Entity
@Table(name = "LIMIT")
public class Limit implements Serializable {

@Id
@Column(name = "ID")
private Long id;

@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "ACCOUNT_ID",
       referencedColumnName="id")
private Account account;
}

除非您在 Entity 中添加 @PrePersist 和 @PreRemove 方法，否则这将不起作用。

示例：在成员实体中，

@PrePersist
public void prePersist() {
    if (org.apache.commons.collections4.CollectionUtils.isNotEmpty(accounts)) {
        accounts.forEach(account -> {
            account.setMember(this);
            account.prePersist();
        });
    }
}