Here's a quick diagram of my database.
http://img17.imageshack.us/img17/2474/mpd.png
In this scheme, I created JPA entities (for all tables with a red square).
I would like to create a JPQL query to get all aircraft that have references, references defined by a reference type given by parameter.
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)
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.
Here my entities without 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.
PS2: JPA implementation is made by Hibernate.
Any idea how to make the multiple join ? Thank you !
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.manuals
since otherwise I got LazyInitializationException
. Added LEFT
to prevent the absence of manuals to influence on the output.