java JPQL：多重连接。如何做我的命名查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11950279/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
JPQL : multiple join. How to do my namedquery?
提问by MychaL
Here's a quick diagram of my database.
这是我的数据库的快速图表。
http://img17.imageshack.us/img17/2474/mpd.png
http://img17.imageshack.us/img17/2474/mpd.png
In this scheme, I created JPA entities (for all tables with a red square).
在这个方案中，我创建了 JPA 实体（对于所有带有红色方块的表）。
I would like to create a JPQL query to get all aircraft that have references, references defined by a reference type given by parameter.
我想创建一个 JPQL 查询来获取所有具有引用的飞机，引用由参数给定的引用类型定义。
I tried :
SELECT DISTINCT a FROM Aircraft a JOIN FETCH a.references r WHERE EXISTS (SELECT ref FROM Reference ref WHERE ref = r AND ref.referenceType.id = :id)
我试过 ：
SELECT DISTINCT a FROM Aircraft a JOIN FETCH a.references r WHERE EXISTS (SELECT ref FROM Reference ref WHERE ref = r AND ref.referenceType.id = :id)
But i have an error cause Eclipse doesnt like the alias in JOIN FETCH a.references *r*and the request doesnt work in JUnit test.
但是我有一个错误，因为 Eclipse 不喜欢中的别名，JOIN FETCH a.references *r*并且该请求在 JUnit 测试中不起作用。
Here my entities without getter/setter :
这里我的实体没有 getter/setter ：
Aircraft
飞机
@Entity
@Table(name = "T_R_AIRCRAFT_AIR", uniqueConstraints = @UniqueConstraint(columnNames = "AIR_NAME"))
public class Aircraft implements java.io.Serializable {
@Id
@Column(name = "AIR_ID", unique = true, nullable = false)
@TableGenerator(name="aircraftSeqStore",
table="T_S_APP_SEQ_STORE_AST",
pkColumnName="AST_SEQ_NAME",
valueColumnName = "AST_SEQ_VALUE",
pkColumnValue = "T_R_AIRCRAFT_AIR.AIR_ID",
allocationSize=1)
@GeneratedValue(strategy=GenerationType.TABLE,
generator="aircraftSeqStore")
private Integer id;
@Column(name = "AIR_NAME", unique = true, nullable = false, length = 50)
private String name;
@ManyToMany(fetch = FetchType.LAZY)
@JoinTable(name = "T_J_REF_AIR_RFA",
joinColumns = { @JoinColumn(name = "RFA_AIR_ID", nullable = false, updatable = false) },
inverseJoinColumns = { @JoinColumn(name = "RFA_REF_ID", nullable = false, updatable = false) })
private Set<Reference> references = new HashSet<Reference>(0);
@ManyToMany(fetch = FetchType.LAZY)
@JoinTable(name = "T_J_MAN_AIR_MNA",
joinColumns = { @JoinColumn(name = "MNA_AIR_ID", nullable = false, updatable = false) },
inverseJoinColumns = { @JoinColumn(name = "MNA_MAN_ID", nullable = false, updatable = false) })
private Set<Manual> manuals = new HashSet<Manual>(0);
@OneToMany(fetch = FetchType.LAZY,
mappedBy = "aircraft",
cascade = { CascadeType.REMOVE })
private Set<UserConfig> userConfigs = new HashSet<UserConfig>(0);
}
** Reference **
** 参考 **
@Entity
@Table(name = "T_E_REFERENCE_REF",
uniqueConstraints = @UniqueConstraint(columnNames = "REF_IDENTIFIER"))
public class Reference implements java.io.Serializable {
@Id
@Column(name = "REF_ID", unique = true, nullable = false)
@TableGenerator(name="referenceSeqStore",
table="T_S_APP_SEQ_STORE_AST",
pkColumnName="AST_SEQ_NAME",
valueColumnName = "AST_SEQ_VALUE",
pkColumnValue = "T_E_REFERENCE_REF.REF_ID",
allocationSize=1)
@GeneratedValue(strategy=GenerationType.TABLE, generator="referenceSeqStore")
private Integer id;
@Column(name = "REF_IDENTIFIER", unique = true, nullable = false, length = 50)
private String identifier;
@Column(name = "REF_LINK")
private String link;
@Column(name = "REF_OBSERVATIONS", length = 4000)
private String observations;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "REF_RFT_ID", nullable = false)
private ReferenceType referenceType;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "REF_MAN_ID")
private Manual manual;
@OneToMany(fetch = FetchType.LAZY,
mappedBy = "reference",
cascade = { CascadeType.REMOVE })
private Set<Translation> translations = new HashSet<Translation>(0);
@ManyToMany(fetch = FetchType.LAZY)
@JoinTable(name = "T_J_REF_AIR_RFA",
joinColumns = { @JoinColumn(name = "RFA_REF_ID", nullable = false, updatable = false) },
inverseJoinColumns = { @JoinColumn(name = "RFA_AIR_ID", nullable = false, updatable = false) })
private Set<Aircraft> aircrafts = new HashSet<Aircraft>(0);
}
** ReferenceType **
** 参考类型 **
@Entity
@Table(name = "T_R_REFERENCE_TYPE_RFT",
uniqueConstraints = @UniqueConstraint(columnNames = "RFT_TYPE"))
public class ReferenceType implements java.io.Serializable {
@Id
@Column(name = "RFT_ID", unique = true, nullable = false)
@TableGenerator(name="referenceTypeSeqStore",
table="T_S_APP_SEQ_STORE_AST",
pkColumnName="AST_SEQ_NAME",
valueColumnName = "AST_SEQ_VALUE",
pkColumnValue = "T_R_REFERENCE_TYPE_RFT.RFT_ID",
allocationSize=1)
@GeneratedValue(strategy=GenerationType.TABLE, generator="referenceTypeSeqStore")
private Integer id;
@Column(name = "RFT_TYPE", unique = true, nullable = false, length = 50)
private String type;
@OneToMany(fetch = FetchType.LAZY,
mappedBy = "referenceType",
cascade = { CascadeType.REMOVE })
private Set<Reference> references = new HashSet<Reference>(0);
@OneToMany(fetch = FetchType.LAZY,
mappedBy = "referenceType",
cascade = { CascadeType.REMOVE })
private Set<UserConfig> userConfigs = new HashSet<UserConfig>(0);
}
PS: I forgot to say that a table had been added between Aircraft and Reference. This is the Manual table. But i dont think there is an impact.
PS：忘了说在Aircraft和Reference之间加了一张表。这是手册表。但我不认为有影响。
PS2: JPA implementation is made by Hibernate.
PS2：JPA 实现是由 Hibernate 实现的。
Any idea how to make the multiple join ? Thank you !
知道如何进行多重连接吗？谢谢 ！
回答by Oleksandr Bondarenko
You can execute your query much easier as follows:
您可以更轻松地执行查询，如下所示：
SELECT DISTINCT a FROM Aircraft a JOIN FETCH a.references r
LEFT JOIN FETCH a.manuals
WHERE r.referenceType.id = :id
I've added join fetch a.manualssince otherwise I got LazyInitializationException. Added LEFTto prevent the absence of manuals to influence on the output.
我已经添加了join fetch a.manuals，否则我得到LazyInitializationException. 添加LEFT以防止缺少手册影响输出。
