Using Hibernate 5.1.0 and MySQL to store a tree structure in the database.
The structure looks like following:
Code:
class Node {
@Id
private int id;
@ManyToOne
private Node parent;
@OneToMany(fetch = EAGER)
private Set<Node> children = new HashSet<>();
@OneToMany(fetch = EAGER)
@Filter(name = "lastRecordsFilter", condition = ** Descripted below **)
private Set<Value> values = new HashSet<>();
}
As you can see the child nodes and value are fetched eagerly. This is necessary, as I am doing calculations on the parent node, which depend on the parent nodes values and child values.
But not of all values, every recorded. Just the last recorded for every type, as a value has a timestamp and a type.
For further understandment a look on the Value entity:
Code:
class Value {
@Id
@ManyToOne
@JoinColum(name = "node_id", referrencedColumnName = "id")
private Node node;
@Id
private Date timestamp;
@Id
private String type;
private double value;
}
A simple filter condition to receive the wanted values would be:
Quote:
condition = "(node_id, type, timestamp) IN (SELECT v.type, MAX(v.timestamp) FROM Value v WHERE v.node_id = :node_id GROUP BY v.type DESC)"
Obviously when fetching the values for the children it would substitute :node_id with the parent's id, which I define when enabling and setting filter parameters, and therefore returns empty value sets. The query created by Hibernate looks something like:
Quote:
select * from Value va0_
where (va0_.node_id, va0_.type, va0_.timestamp) IN (SELECT v.type, MAX(v.timestamp) FROM Value v WHERE v.node_id = ?1 GROUP BY v.type DESC)
and va0_.node_id = ?1
?1 is the filter parameter and therefore stays the same, parent node_id for all children.
?2 is correctly replaced by the correct node_id for which the values are retrieved.
To solve this problem I used a hack and changed the filter to.
Quote:
condition = "(node_id, type, timestamp) IN (SELECT v.type, MAX(v.timestamp) FROM Value v WHERE v.node_id = ? GROUP BY v.type DESC)#"
Meaning Hibernate substitutes the first parameter, like it is a query parameter and not a filter parameter.
And to avoid an exception, as the number of parameters in the query would be greater than the number of given parameters, the end part is commented out by adding # to the end.
This can't be a good solution, that's why I am asking here how I could solve this a proper way.
Login
FAQ
Search
