JPA使用ElementCollection映射多行 [英] JPA Mapping Multi-Rows with ElementCollection

问题描述

我正在尝试遵循 JPA教程，并使用ElementCollection进行记录员工电话号码:

I'm trying to follow the JPA tutorial and using ElementCollection to record employee phone numbers:

PHONE (table)
OWNER_ID    TYPE      NUMBER
  1         home      792-0001
  1         work      494-1234
  2         work      892-0005

简短版

我需要的是一个像这样的课程:

Short version

What I need is a class like this:

@Entity
@Table(name="Phones")
public class PhoneId {
  @Id
  @Column(name="owner_id")
  long owner_id;

  @Embedded
  List<Phone> phones;
}

将每个人的电话号码存储在一个集合中.

that stores each person's phone numbers in a collection.

我遵循教程代码:

@Entity
@Table(name="Phones")
public class PhoneId {
  @Id
  @Column(name="owner_id")
  long owner_id;

  @ElementCollection
  @CollectionTable(
    name="Phones",
    joinColumns=@JoinColumn(name="owner_id")
  )
  List<Phone> phones = new ArrayList<Phone>();
}

@Embeddable
class Phone {
  @Column(name="type")
  String type = "";
  @Column(name="number")
  String number = "";

  public Phone () {}
  public Phone (String type, String number)
    { this.type = type; this.number = number; }
}

略有不同，我只保留一张桌子.我尝试使用以下代码将记录添加到该表中:

with a slight difference that I only keep one table. I tried to use the following code to add records to this table:

public static void main (String[] args) {
  EntityManagerFactory entityFactory =
     Persistence.createEntityManagerFactory("Tutorial");
  EntityManager entityManager = entityFactory.createEntityManager();

  // Create new entity
  entityManager.getTransaction().begin();
  Phone ph = new Phone("home", "001-010-0100");
  PhoneId phid = new PhoneId();
  phid.phones.add(ph);
  entityManager.persist(phid);
  entityManager.getTransaction().commit();

  entityManager.close();
}

但是它总是抛出异常

内部异常:org.postgresql.util.PSQLException:错误:null 列类型"中的值违反了非空约束详细信息:失败 行包含(0，null，null).错误代码:0呼叫:INSERT INTO Phones (owner_id)VALUES(?)bind => [1个参数绑定]查询: InsertObjectQuery(tutorial.Phone1@162e295)

Internal Exception: org.postgresql.util.PSQLException: ERROR: null value in column "type" violates not-null constraint Detail: Failing row contains (0, null, null). Error Code: 0 Call: INSERT INTO Phones (owner_id) VALUES (?) bind => [1 parameter bound] Query: InsertObjectQuery(tutorial.Phone1@162e295)

我做错了什么?

推荐答案

遗憾的是，我认为您只保留一张桌子的微小差异就是这里的问题.

Sadly, i think the slight difference that you only keep one table is the problem here.

查看PhoneId类的声明(我建议最好将其称为PhoneOwner或类似的名称):

Look at the declaration of the PhoneId class (which i would suggest is better called PhoneOwner or something like that):

@Entity
@Table(name="Phones")
public class PhoneId {

当您声明一个类是映射到某个表的实体时，您将在其中声明一组，其中两个在这里特别重要.首先，该实体的每个实例在表中都有一行，反之亦然.其次，表中实体的每个标量字段都有一列，反之亦然.这两者都是对象关系映射思想的核心.

When you declare that a class is an entity mapped to a certain table, you are making a set of assertions, of which two are particularly important here. Firstly, that there is one row in the table for each instance of the entity, and vice versa. Secondly, that there is one column in the table for each scalar field of the entity, and vice versa. Both of these are at the heart of the idea of object-relational mapping.

但是，在您的模式中，这些断言都不成立.在您提供的数据中:

However, in your schema, neither of these assertions hold. In the data you gave:

OWNER_ID    TYPE      NUMBER
  1         home      792-0001
  1         work      494-1234
  2         work      892-0005

与owner_id 1对应的实体有两行，违反了第一个断言.有TYPE和NUMBER列未映射到实体中的字段，这违反了第二个断言.

There are two rows corresponding to the entity with owner_id 1, violating the first assertion. There are columns TYPE and NUMBER which are not mapped to fields in the entity, violating the second assertion.

(很明显，您对Phone类或phones字段的声明没有任何问题-仅是PhoneId实体)

(To be clear, there is nothing wrong with your declaration of the Phone class or the phones field - just the PhoneId entity)

结果，当您的JPA提供程序尝试将PhoneId的实例插入数据库时​​，就会遇到麻烦.因为PhoneId中的TYPE和NUMBER列没有映射，所以当它为插入生成SQL时，它不包含它们的值.这就是为什么看到错误的原因-提供程序写INSERT INTO Phones (owner_id) VALUES (?)，PostgreSQL将其视为INSERT INTO Phones (owner_id, type, number) VALUES (?, null, null)，被拒绝.

As a result, when your JPA provider tries to insert an instance of PhoneId into the database, it runs into trouble. Because there are no mappings for the TYPE and NUMBER columns in PhoneId, when it generates the SQL for the insert, it does not include values for them. This is why you get the error you see - the provider writes INSERT INTO Phones (owner_id) VALUES (?), which PostgreSQL treats as INSERT INTO Phones (owner_id, type, number) VALUES (?, null, null), which is rejected.

即使您确实设法在该表中插入了一行，也可能会从该表中检索对象时遇到麻烦.假设您要求使用owner_id 1的PhoneId实例.提供程序将编写总计为select * from Phones where owner_id = 1的SQL，并且希望它找到恰好一行，可以将其映射到对象.但是它将找到两行！

Even if you did manage to insert a row into this table, you would then run into trouble on retrieving an object from it. Say you asked for the instance of PhoneId with owner_id 1. The provider would write SQL amounting to select * from Phones where owner_id = 1, and it would expect that to find exactly one row, which it can map to an object. But it will find two rows!

恐怕，解决方案是使用两个表，一个表用于PhoneId，另一个表用于Phone. PhoneId的表将非常简单，但是对于正确运行JPA机制而言，这是必需的.

The solution, i'm afraid, is to use two tables, one for PhoneId, and one for Phone. The table for PhoneId will be trivially simple, but it is necessary for the correct operation of the JPA machinery.

假设您将PhoneId重命名为PhoneOwner，则表应如下所示:

Assuming you rename PhoneId to PhoneOwner, the tables need to look like:

create table PhoneOwner (
    owner_id integer primary key
)

create table Phone (
    owner_id integer not null references PhoneOwner,
    type varchar(255) not null,
    number varchar(255) not null,
    primary key (owner_id, number)
)

(我已经将(owner_id, number)用作Phone的主键，假设一个所有者可能拥有多个给定类型的数字，但永远不会在两种类型下记录一个数字.您可能更喜欢(owner_id, type)如果可以更好地反映您的领域.)

(I've made (owner_id, number) the primary key for Phone, on the assumption that one owner might have more than one number of a given type, but will never have one number recorded under two types. You might prefer (owner_id, type) if that better reflects your domain.)

实体如下:

@Entity
@Table(name="PhoneOwner")
public class PhoneOwner {
    @Id
    @Column(name="owner_id")
    long id;

    @ElementCollection
    @CollectionTable(name = "Phone", joinColumns = @JoinColumn(name = "owner_id"))
    List<Phone> phones = new ArrayList<Phone>();
}

@Embeddable
class Phone {
    @Column(name="type", nullable = false)
    String type;
    @Column(name="number", nullable = false)
    String number;
}

现在，如果您真的不想为PhoneOwner引入表，则可以使用视图摆脱该表.像这样:

Now, if you really don't want to introduce a table for the PhoneOwner, then you might be able to get out of it using a view. Like this:

create view PhoneOwner as select distinct owner_id from Phone;

据JPA提供者说，这是一个表，它将支持读取数据所需的查询.

As far as the JPA provider can tell, this is a table, and it will support the queries it needs to do to read data.

但是，它不支持插入.如果您需要为当前不在数据库中的所有者添加电话，则需要绕后并直接在Phone中插入一行.不太好.

However, it won't support inserts. If you ever needed to add a phone for an owner who is not currently in the database, you would need to go round the back and insert a row directly into Phone. Not very nice.

这篇关于JPA使用ElementCollection映射多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！