Hibernate Query - get the latest versions by timestamps?
I have a database that is being used as a kind of source control system. That is, instead of updating any lines, I add a new line with the same information. Each row also contains a column version
that is a date stamp, so the only difference is that the new row will have a newer time stamp.
I am having trouble writing an efficient hibernate query to return the latest version of these lines. For example, these are rows in a table with a name Product
, a column with a time stamp version
. There are multiple versions of multiple products in the table. So there can be multiple versions (rows) of ProductA, multiple versions of ProductB, etc. And I would like to get the latest version of each one.
Can I do this in just one hibernate request?
session.createQuery("select product from Product product where...?");
Or will it require some intermediate steps?
a source to share
To answer this question, every product needs some sort of identifier. Since there may be multiple versions, we need to know "what is the product", I assume it is product.id
missing, as it is probably a surrogate key. I'll choose product.name
for an example.
Here's one request to get the latest version of each product:
select p1 from Product p1 where
p1.timestamp >= all (
select p2.timestamp from Product p2 where
p2.name=p1.name
)
(My HQL is a little rusty, but I hope you get the gist.)
The trick is self-connection between similar products of different timestamps. p1
products are recently changed than p2
. To find the most recent one p1
, we will find rows that have no values p2
, i.e. There is no product later than p1
.
EDIT: I revisited the request - I saw someone using the "on" syntax in a forum post lately, but now I remember that this is not a valid HQL. Sorry I don't have a system to test. It now uses a correlated subquery that functions the same as JOIN.
Hope it helps.
a source to share
To find the latest version of a specific product:
select product where ... order by version DESC
add a composite key with this version.
To find the latest version of all products, you need two queries (or a subquery):
Insert into TMP (id, version) select (id, max(version)) from product group by id);
select P.* Product P, TMP where TMP.ID = P.ID
a source to share