如何自动删除连接表中的行，以避免ConstraintViolationException？ [英] How do I delete a row in a join table automatically, to avoid a ConstraintViolationException?

问题描述

这似乎应该是一个非常简单的问题，或者至少有一个简单的答案。但是 - 我真的不是一个数据库家伙，而且我仍然在Hibernate学习曲线上很差。也就是说，这里是设置：

考虑两个实体之间的单向多对多关系，从 Foo 到 Bar ：

^{（原谅以下任何拼写错误，下面显然是对实际代码的简化）}

FooDTO.java：

  @Entity 
 @Table（name =MyDB.dbo.Foo）
 class FooDTO实现Serializable 
 {
 private int id; 
私人字符串名称; 
私人套餐< BarDTO> bars = new HashSet< BarDTO>（）; 
 
 ... 
 
 @Fetch（FetchMode.JOIN）
 @ManyToMany（fetch = FetchType.EAGER）
 @JoinTable（name =MyDB .dbo.FooBar，
 joinColumns = {@JoinColumn（name =fooId）}，
 inverseJoinColumns = {@JoinColumn（name =barId）}）
 public Set< BarDTO> ; getBars（）
 {
返回酒吧; 
} 
 public void setBars（Set< Bar> bars）
 {
 this.bars = bars; 
} 
} 
  

BarDTO.java：

  @Entity 
 @Table（name =MyDB.dbo.Bar）
 class BarDTO实现Serializable 
 {
 private int id; 
私人字符串名称; 
 
 ... 
} 
  

在TSQL方面，连接表的设置如下所示：

  CREATE TABLE [dbo]。[FooBar]（
 [id] [int] NOT NULL IDENTITY PRIMARY KEY，
 [fooId] [int] NOT NULL，
 [barId] [int] NOT NULL，
 CONSTRAINT fk_FooBar_FooId FOREIGN KEY（fooId）REFERENCES [dbo] 。[Foo]（id），
 CONSTRAINT fk_FooBar_BarId FOREIGN KEY（barId）REFERENCES [dbo]。[Bar]（id），
）ON [PRIMARY] 
 END 
  

如果我试图彻底删除 BarDTO ，我得到一个 ConstraintViolationException 因为我没有先删除连接表中的行（duh）。

问题：

• 当我删除 Bar ？如何？


• 如果不是，我该如何选择所有 Foo s有一个特定的 Bar
code>，所以我可以从每个相关的 Foo 的一组<$ c $>中删除 Bar c> Bar s？

关于后一个问题，我认为这可以通过 NamedQuery 或使用Criteria API来完成，但我不知道如何编写这样的查询或应用哪些约束。我认为这将是一个命名查询如下所示：

  SELECT f FROM FooDTO f INNER JOIN ???哪里f.id = ??? 
  

但我不确定 barId 参数会去，或者如何加入 FooBar 表，因为我没有声明它是一个实体。 （注意，我还记得以前尝试加入命名查询的问题 - 正在加入不可能的命名查询吗？）

blockquote>

当我删除一个Bar时，我可以让Hibernate自动删除连接表中的行吗？如何？

您需要从集合中删除 BarDTO




如果不是，我该如何选择所有的code> FooDTO 有一个特别的酒吧的Foos，所以我可以从每个相关的Foo的酒吧中删除该酒吧？

以下内容应该可以工作：

  SELECT f FROM FooDTO f WHERE：bar成员f.bars 
  

或者，您可以将关联设为双向，只需调用 bar.getFoos（）。

This seems like it should be a pretty simple question, or at least have a simple answer. But - I'm really not a database guy, and I'm still pretty far down on the Hibernate learning curve. That said, here's the setup:

Consider a unidirectional many-to-many relationship between two entities, from Foo to Bar:

^{(pardon any typos below, the following is obviously a simplification of the actual code)}

FooDTO.java:

@Entity
@Table(name = "MyDB.dbo.Foo")
class FooDTO implements Serializable
{
    private int id;
    private String name;
    private Set<BarDTO> bars = new HashSet<BarDTO>();

    ...

    @Fetch(FetchMode.JOIN)
    @ManyToMany(fetch = FetchType.EAGER)
    @JoinTable(name = "MyDB.dbo.FooBar",
               joinColumns = { @JoinColumn(name = "fooId") },
               inverseJoinColumns = { @JoinColumn(name = "barId") })
    public Set<BarDTO> getBars()
    {
        return bars;
    }
    public void setBars(Set<Bar> bars)
    {
        this.bars = bars;
    }
}

BarDTO.java:

@Entity
@Table(name = "MyDB.dbo.Bar")
class BarDTO implements Serializable
{
    private int id;
    private String name;

    ...
}

On the TSQL side, the join table is set up like this:

CREATE TABLE [dbo].[FooBar](
    [id] [int] NOT NULL IDENTITY PRIMARY KEY,
    [fooId] [int] NOT NULL,
    [barId] [int] NOT NULL,
    CONSTRAINT fk_FooBar_FooId FOREIGN KEY (fooId) REFERENCES [dbo].[Foo](id),
    CONSTRAINT fk_FooBar_BarId FOREIGN KEY (barId) REFERENCES [dbo].[Bar](id),
) ON [PRIMARY]
END

If I try to outright delete a BarDTO, I get a ConstraintViolationException because I haven't first deleted the row in the join table (duh).

Questions:

• Can I get Hibernate to drop the rows in the join table automagically, when I delete a Bar? How?
• If not, how do I select all the Foos that have a particular Bar, so I can remove that Bar from each relevant Foo's set of Bars?

With regard to the latter question, I think this could be done with either a NamedQuery or using the Criteria API, but I don't know specifically how to write such a query or which constraints to apply. I think it would a named query would look something like:

SELECT f FROM FooDTO f INNER JOIN ??? WHERE f.id = ???

but I'm not sure where the barId parameter would go, or how to join on the FooBar table since I don't declare it as an entity. (Side note, I also recall previous issues with trying to join in a named query - is joining in a named query impossible?)

解决方案

Can I get Hibernate to drop the rows in the join table automagically, when I delete a Bar? How?

You need to remove the BarDTO instance from the collections in FooDTO that holds a reference on it.

If not, how do I select all the Foos that have a particular Bar, so I can remove that Bar from each relevant Foo's set of Bars?

The following should work:

SELECT f FROM FooDTO f WHERE :bar MEMBER OF f.bars 

Or you could make the association bidirectional and simply call bar.getFoos().

这篇关于如何自动删除连接表中的行，以避免ConstraintViolationException？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！