Hibernate查询 - 通过时间戳获取最新版本？ [英] Hibernate Query - Get latest versions by timestamp?

问题描述

我有一个数据库被用作一种版本控制系统。也就是说，我没有更新任何行，而是添加了具有相同信息的新行。每行还包含一个版本列，这是一个日期时间戳，所以唯一的区别是新行将有一个更新的时间戳。

我遇到的麻烦是编写一个高效的hibernate查询来返回这些行的最新版本。举例来说，这些行是一个名为 Product 的表中的行，时间戳列是 version 。表格中有多个产品的多个版本。因此，可能有多个版本（行）的ProductA，ProductB的多个版本等。我想抓住每个版本的最新版本。

我可以做这个吗？只需一个hibernate查询？

  session.createQuery（从产品中选择产品......）; 
  

或者这会需要一些中间步骤？

为了回答这个问题，每个产品都需要一些标识符。由于可以有多个版本，因此我们需要知道什么是产品，因为这可能是代理关键字，因此我假设 product.id 不在。为了举例，我会选择 product.name 。

这里有一个查询来检索最新版本的每个产品：

 从产品p1中选择p1，其中
 p1.timestamp> =全部（
 select p2.timestamp from Product p2其中
 p2.name = p1.name 
）
  

< （我的HQL有点生疏，但我希望你能明白）。

关键是不同时间戳的相似产品之间的自我连接。 p1 产品最近被修改过比 p2 。要找到最近的 p1 ，我们找到没有 p2 值的行 - 即没有产品更新比 p1 。

编辑：我修改了查询 - 我最近看到有人在论坛帖子中使用了on语法，但我现在记得那是无效的HQL。对不起 - 我没有可用的测试系统。它现在使用一个相关的子查询，其功能与JOIN相同。

希望这有助于您。

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.

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?

session.createQuery("select product from Product product where...?");

Or would this require some intermediate steps?

解决方案

To answer this, each product needs some kind of identifier. Since there can be multiple versions, we need to know "what is a product" I'm assuming product.id is out, as this is probably a surrogate key. I'll choose product.name for sake of example.

Here's a single query to retrieve 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 bit rusty, but I hope you get the gist.)

The trick is the self join between like products of differing timestamps. p1 products are more recently modified than p2. To find the most recent p1, we find rows where there are no p2 values - i.e. there are no product more recent than p1.

EDIT: I've revised the query - I'd saw someone used the "on" syntax in a forum post recently, but I now remember that that is not valid HQL. Sorry about that - I don't have a system available to test on. It now uses a correlated subquery that functions the same as the JOIN.

Hope this helps.

这篇关于Hibernate查询 - 通过时间戳获取最新版本？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！