使用 JPA 连接两个表

Question

我正在尝试使用@NamedQuery连接两个表，Address和Division。 同一地址有多个部门。 目前我有
部门.java：

@Entity
@Table(name="division")
@NamedQuery(name="Division.mailingAddress", query="SELECT d, a from Division d LEFT JOIN d.addresses a")
public class Division implements Serializable{
private static final long serialVersionUID = 1L;
@Id
@Basic(optional = false)
@Column(name="division_id")
private Short divisionId;
@Basic(optional = false)
@Column(name="division_name")
private String divisionName;
@Basic(optional = false)
@Column(name="address_id")
private Short addressId;
@OneToMany(mappedBy="divisionMailingAddress")
private List<Address> addresses;
//getters and setters
}

地址.java：

@Entity
@Table(name="address")
public class Address implements Serializable{
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GeneratedType.IDENTITY)
@Basic(optional = false)
@Column(name = "address_id")
private Short addressID;
@Basic(optional = false)
@Column(name = "mailing_address")
private String mailingAddress;
@ManyToOne
private Division divisionMailingAddress;
}

运行它时我得到的错误是：Unknown column 't0.DIVISIONMAILINGADDRESS_division_id' in 'on clause'

我试图得到的是这行在 MySQL 中工作：
SELECT * FROM (division d join address a on d.address_id = a.address_id);目前，JPA 正在将命名查询解析为：
SELECT t1."all columns", t0."all columns" from division t1 LEFT OUTER JOIN address t0 ON (t0.DIVISIONMAILINGADDRESS_division_id = t1.division_id)我正在做的事情有什么问题？ JPA 是否希望加入来自地址与部门？ 如何让电话从address_id与division_id中拨打？

更新：

在大力协助下，这两个类已经改变，以完成我想要的结果。

分部.java：

@Entity
@Table(name="division")
@NamedQuery(name="Division.mailingAddress", query="SELECT d from Division d LEFT JOIN d.addressesM2O a")
public class Division implements Serializable{
private static final long serialVersionUID = 1L;
@Id
@Basic(optional = false)
@Column(name="division_id")
private Short divisionId;
@Basic(optional = false)
@Column(name="division_name")
private String divisionName;
@Basic(optional = false)
@Column(name="address_id")
private Short addressId;
@ManyToOne(targetEntity = Address.java)
@JoinColumn(name = "address_id", updatable=false, insertable=false)
private Address addressesM2O;
//getters and setters
}

地址.java：

@Entity
@Table(name="address")
public class Address implements Serializable{
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GeneratedType.IDENTITY)
@Basic(optional = false)
@Column(name = "address_id")
private Short addressID;
@Basic(optional = false)
@Column(name = "mailing_address")
private String mailingAddress;
@OneToMany(mappedBy="addressesM2O")
private List<Division> divisionO2M;
}

自d年以来更改了@NamedQuery，a存在使结果列表返回Object[]列表。这是Division和Address的对象. 这就是为什么保留d并从@NamedQuery中删除a的原因。

Answer 1

我认为问题是您在地址类中提及@ManyToOne时没有提到@JoinColumn，因此在进行连接时 JPA 本身正在创建外键DIVISIONMAILINGADDRESS_division_id。

尝试在地址类中提及ManyToOne关系，如下所示，如果出现任何问题，请告诉我：

@ManyToOne(targetEntity = Division.class)
@JoinColumn(name= "division_id")//basically the name of the column here should be same as the one mentioned in your database

Answer 2

对我有用的是将@OneToMany和@ManyToOne更改为address表中的@PrimaryKeyJoinColumn@OneToOne。代码更新如下： 分部.java改为：

@OneToOne(mappedBy="divisionMailingAddress")
private Address address;

地址.java更改为：

@OneToOne
@PrimaryKeyJoinColumn
private Division divisionMailingAddress;