• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
  • Campbell Ritchie
  • Paul Clapham
  • Bear Bibeault
  • Liutauras Vilda
  • Devaka Cooray
  • Knute Snortum
  • Junilu Lacar
  • Henry Wong
Saloon Keepers:
  • Ron McLeod
  • Stephan van Hulst
  • Tim Moores
  • Carey Brown
  • Tim Holloway
  • salvin francis
  • Frits Walraven
  • Piet Souris

Help with HQL statement

Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi all-

I have a database that is being used as a sort of version control system. That is, instead of ever updating any rows, I add a new row with the same information. Each row also contains a version column that is a date timestamp, so the only difference is the new row will have a more recent timestamp. Each product has an ID that does not change. Each ID will have multiple "versions" and I would like to return the most recent "version" of each ID.

What I'm having trouble with is writing an efficient hibernate query to return the latest version of these rows. For the sake of example, these are rows in a table called Product, the timestamped column is version. There are multiple versions of multiple products in the table. So there may be multiple versions (rows) of ProductA, multiple versions of ProductB, etc. And I would like to grab the latest version of each.

Can I do this in just a single hibernate query?

I think something like this should theoretically work, but I don't know how to implement it in HQL

That may or may not be on the right track, but can anybody help me achieve this in hibernate? Thanks!
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
To do this, you'll have to use sub-queries. If you connect your Products via the "name" column, then your query should look like that:

I would also consider redesigning a data structure to use one-to-many association to keep your "versions" on the "many" end. This way you can query them more efficiently - in fact, all you need is to order them on the "timestamp" column in descending order and fetch the first one.

Best regards,
Nikita Protsenko
I'm all tasted up for a BLT! This tiny ad wants a monte cristo!
Java file APIs (DOC, XLS, PDF, and many more)
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!