如何使用 SELECT MAX() 将这个特定的 JPQL 查询重写为 CriteriaQuery?

发布于 2024-11-15 15:19:22 字数 1407 浏览 5 评论 0原文

我对 JPA 很陌生,在理解一个特定查询时遇到了问题。我已经重写了 CriteriaQuery,但结果和转换为 SQL 的查询不正确。

背景情况:我有一个商店交易(移动)表,商店中的当前金额定义为所有更改的总和。现在我想选择并显示最后的移动,因为它们还包含有关商店中结果金额的信息。

所以,这是 JPQL 中的查询:

SELECT m FROM move m WHERE m.id = (
  SELECT MAX(o.id) FROM move o WHERE (o.item = m.item AND m.cell.store = :s)

我尝试将其重写为以下 CriteriaQuery:

CriteriaBuilder builder = model.getCriteriaBuilder();
CriteriaQuery<Move> query = builder.createQuery(Move.class);
Root<Move> root = query.from(Move.class);

Subquery<Long> subquery = query.subquery(Long.class);
Root<Move> subroot = subquery.from(Move.class);
subquery.select(builder.max(subroot.get("id").as(Long.class)));

subquery.where(builder.and(
    builder.equal(
        subroot.get("item").get("id"),
        root.get("item").get("id")),
    builder.equal(
        subroot.get("cell").get("store").as(Store.class),
        store)));

Expression<Boolean> where = builder.equal(
    root.get("id"),
    subquery);

query.where(where);

return model.getList(query);

生成的不正确的 SQL 查询如下:

SELECT t0.id, (...) FROM move t0 WHERE (t0.id = (
  SELECT MAX(t1.id) FROM item t3, item t2, move t1 
  WHERE (((t2.id = t3.id) AND 
        (t1.cell_store = ?)) AND 
        ((t2.id = t1.item_ref) AND 
        (t3.id = t0.item_ref))))
)

我不明白为什么子查询中有双交叉联接。谢谢您的帮助!

I am quite new to JPA and I have encountered a problem with understanding one particular query. I have rewritten in to the CriteriaQuery but the result and the query translated to SQL is not correct.

Background situation: I have a table of store transaction (moves) and the current amount in the store is defined as a sum of all changes. Now I want to select and display the last moves as they also contain an information about the resulting amount on store.

So, this is the query in JPQL:

SELECT m FROM move m WHERE m.id = (
  SELECT MAX(o.id) FROM move o WHERE (o.item = m.item AND m.cell.store = :s)

I tried to rewrite it to the following CriteriaQuery:

CriteriaBuilder builder = model.getCriteriaBuilder();
CriteriaQuery<Move> query = builder.createQuery(Move.class);
Root<Move> root = query.from(Move.class);

Subquery<Long> subquery = query.subquery(Long.class);
Root<Move> subroot = subquery.from(Move.class);
subquery.select(builder.max(subroot.get("id").as(Long.class)));

subquery.where(builder.and(
    builder.equal(
        subroot.get("item").get("id"),
        root.get("item").get("id")),
    builder.equal(
        subroot.get("cell").get("store").as(Store.class),
        store)));

Expression<Boolean> where = builder.equal(
    root.get("id"),
    subquery);

query.where(where);

return model.getList(query);

The incorrect SQL query produced is following:

SELECT t0.id, (...) FROM move t0 WHERE (t0.id = (
  SELECT MAX(t1.id) FROM item t3, item t2, move t1 
  WHERE (((t2.id = t3.id) AND 
        (t1.cell_store = ?)) AND 
        ((t2.id = t1.item_ref) AND 
        (t3.id = t0.item_ref))))
)

I do not understand why there is a double cross join in the subquery. Thank you for helping!

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。
列表为空,暂无数据
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文