I came across a very particular scenario of a nested join query in Hibernate that should be fairly straightforward, but I'm having trouble narrowing down the data set.
I have a table structure like the following:
top bottom common
------------ ------------ ------------
id id top_id
... ... bottom_id
... ... common_value
lastUpdated lastUpdated date_added
The goal is to query it so that I get an object with the following structure:
top:
{ "id": 1,
"lastUpdated": "2016-05-01",
...
"bottom" : [ {
"id": 1
"lastUpdated": "2016-01-01",
...
"values": [ {
"top_id": 1,
"bottom_id": 1,
"common_value": "abc",
"date_added": "2016-05-15"
} ]
} ]
}
To do so I have these relationships:
@Entity
@Table(name = "top")
public class Top {
@Id
@GeneratedValue(strategy = IDENTITY)
private Long id;
private String name;
//...other fields...
private Date updatedOn;
@OneToMany(fetch = FetchType.EAGER)
@JoinTable(name = "common",
joinColumns = @JoinColumn(name = "top_id"),
inverseJoinColumns = @JoinColumn(name = "bottom_id")
)
private Set<Bottom> bottomSet;
}
@Entity
@Table(name = "bottom")
public class Bottom {
@Id
@GeneratedValue(strategy = IDENTITY)
private Long id;
private String name;
//...other fields...
@OneToMany(fetch = FetchType.EAGER)
@JoinColumn(name = "bottom_id")
private Set<Common> values;
}
@Entity
@Table(name = "common")
public class Common {
@EmbeddedId
@AttributeOverrides({ @AttributeOverride(name = "top_id", column = @Column(name = "top_id") ),
@AttributeOverride(name = "common_value", column = @Column(name = "common_value", length = 32) ),
@AttributeOverride(name = "date_add", column = @Column(name = "date_added", length = 19) ),
@AttributeOverride(name = "bottom_id", column = @Column(name = "bottom_id") ) })
private CommonId id;
}
@Embeddable
public class CommonId {
private Integer top_id;
private String common_value;
private Date added_date;
private Integer bottom_id;
}
What happens is that the resulting structure contains all of the values liked to the bottom_id, which is too many results. How can I make it so that the bottom join for the common_values is bound to both the bottom_id and the top_id?
Ideas greatly appreciated!
Solved --
The relationships did not change, but instead of querying all of the top items, I created the following HQL to associate the ids:
FROM Top t
INNER JOIN FETCH t.bottomSet b
INNER JOIN FETCH b.values v
WHERE t.id = :topId AND v.id.top_id = :topId