Hi,
I have a many to many relationship between a Cluster and Tag table using an another intermediate table ClusterTag. Below, I have the bidirectional mapping.
I need help with HQL for the scenario where users can specify multiple (TagName and TagValue) combinations, and I have to retrieve only those Clusters which have ALL those TagName and TagValues associated with them.
like WHERE (tagName=? AND tagValue=?) AND (tagName=? AND tagValue=?)
- Cluster table
@Entity(name = "Cluster")
@Table(name = "cluster")
public class ClusterDto implements Serializable {
private static final long serialVersionUID = -3501772243949297059L;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "id", updatable = false, nullable = false)
private Integer id;
@Column(name = "uuid", nullable = false, unique = true)
@Convert(converter = UUIDConverter.class)
private UUID uuid;
@Column(name = "mapped_id", nullable = false)
private String mappedId;
@Column(name = "create_time", nullable = false, updatable = false)
@CreationTimestamp
private Timestamp createTimestamp;
@Column(name = "last_update", nullable = false)
@UpdateTimestamp
private Timestamp updateTimestamp;
@OneToMany(
mappedBy = "cluster",
cascade = CascadeType.ALL,
orphanRemoval = true
)
private List<ClusterTagDto> tags = new ArrayList<>();
public void addTag(TagDto tag) {
ClusterTagDto clusterTag = new ClusterTagDto(this, tag);
tags.add(clusterTag);
tag.getClusters().add(clusterTag);
}
public void removeTag(TagDto tag) {
for (Iterator<ClusterTagDto> iterator = tags.iterator();
iterator.hasNext(); ) {
ClusterTagDto clusterTag = iterator.next();
if (clusterTag.getCluster().equals(this) &&
clusterTag.getTag().equals(tag)) {
iterator.remove();
clusterTag.getTag().getClusters().remove(clusterTag);
clusterTag.setCluster(null);
clusterTag.setTag(null);
}
}
}
@Override public boolean equals(Object o) {
if (this == o)
return true;
if (!(o instanceof ClusterDto))
return false;
ClusterDto that = (ClusterDto) o;
return Objects.equals(uuid, that.uuid);
}
@Override public int hashCode() {
return Objects.hash(uuid);
}
}
- Entity for the Intermediate table ClusterTag
@Entity(name = "ClusterTag")
@Table(name = "cluster_tag")
public class ClusterTagDto {
@EmbeddedId
private ClusterTagId id;
@ManyToOne(fetch = FetchType.LAZY)
@MapsId("clusterId")
private ClusterDto cluster;
@ManyToOne(fetch = FetchType.LAZY)
@MapsId("tagId")
private TagDto tag;
@Column(name="create_time", nullable = false, updatable = false)
@CreationTimestamp
private Timestamp createTimestamp;
@Column(name="last_update", nullable = false)
@UpdateTimestamp
private Timestamp updateTimestamp;
private ClusterTagDto() {}
public ClusterTagDto(ClusterDto cluster, TagDto tag) {
this.cluster = cluster;
this.tag = tag;
this.id = new ClusterTagId(cluster.getId(), tag.getId());
}
@Override public boolean equals(Object o) {
if (this == o)
return true;
if (!(o instanceof ClusterTagDto))
return false;
ClusterTagDto that = (ClusterTagDto) o;
return Objects.equals(cluster, that.cluster) &&
Objects.equals(tag, that.tag);
}
@Override public int hashCode() {
return Objects.hash(cluster, tag);
}
}
- Tag table
@Entity(name = "ClusterTag")
@Table(name = "tag")
public class TagDto implements Serializable {
private static final long serialVersionUID = -3501772243949297059L;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "id", updatable = false, nullable = false)
private Integer id;
@Column(name = "tag_name", nullable = false)
private String tagName;
@Column(name = "tag_value", nullable = false)
private String tagValue;
@OneToMany(
mappedBy = "tag",
cascade = CascadeType.ALL,
orphanRemoval = true
)
private List<ClusterTagDto> clusters = new ArrayList<>();
@Override public boolean equals(Object o) {
if (this == o)
return true;
if (!(o instanceof TagDto))
return false;
TagDto tagDto = (TagDto) o;
return Objects.equals(uuid, tagDto.uuid);
}
@Override public int hashCode() {
return Objects.hash(uuid);
}
}
regards
aravias