Hi all.
I'm using Hibernate Entity Manager 3.4.0.GA with Spring 2.5.6 and MySql 5.1.
I have a use case where an entity called Artifact has a reflexive many-to-many relation with itself, and the join table is quite large (1 million lines). As a result, the HQL query performed by one of the methods in my DAO takes a long time.
Any advice on how to optimize this and still use HQL ? Or do I have no choice but to switch to a native SQL query that would perform a join between the table ARTIFACT and the join table ARTIFACT_DEPENDENCIES ?
Here is the problematic query performed in the DAO :
Code:
@SuppressWarnings("unchecked")
public List<Artifact> findDependentArtifacts(Artifact artifact) {
Query query = em.createQuery("select a from Artifact a where :artifact in elements(a.dependencies)");
query.setParameter("artifact", artifact);
List<Artifact> list = query.getResultList();
return list;
}
And the code for the Artifact entity :
Code:
package com.acme.dependencytool.persistence.model;
import java.util.ArrayList;
import java.util.List;
import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.JoinTable;
import javax.persistence.ManyToMany;
import javax.persistence.Table;
import javax.persistence.UniqueConstraint;
@Entity
@Table(name = "ARTIFACT", uniqueConstraints={@UniqueConstraint(columnNames={"GROUP_ID", "ARTIFACT_ID", "VERSION"})})
public class Artifact {
@Id
@GeneratedValue
@Column(name = "ID")
private Long id = null;
@Column(name = "GROUP_ID", length = 255, nullable = false)
private String groupId;
@Column(name = "ARTIFACT_ID", length = 255, nullable = false)
private String artifactId;
@Column(name = "VERSION", length = 255, nullable = false)
private String version;
@ManyToMany(cascade=CascadeType.ALL, fetch=FetchType.EAGER)
@JoinTable(
name="ARTIFACT_DEPENDENCIES",
joinColumns = @JoinColumn(name="ARTIFACT_ID", referencedColumnName="ID"),
inverseJoinColumns = @JoinColumn(name="DEPENDENCY_ID", referencedColumnName="ID")
)
private List<Artifact> dependencies = new ArrayList<Artifact>();
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getGroupId() {
return groupId;
}
public void setGroupId(String groupId) {
this.groupId = groupId;
}
public String getArtifactId() {
return artifactId;
}
public void setArtifactId(String artifactId) {
this.artifactId = artifactId;
}
public String getVersion() {
return version;
}
public void setVersion(String version) {
this.version = version;
}
public List<Artifact> getDependencies() {
return dependencies;
}
public void setDependencies(List<Artifact> dependencies) {
this.dependencies = dependencies;
}
}
Thanks in advance.
Fabien