JPA查询选择多对一查询 [英] JPA Query Select many-to-one Query with a count

问题描述

因此，我试图根据下面的类(略述一下)编写一个JPA查询，该查询将产生以下结果:

So I'm trying to write a JPA query based on the classes below (dumbed down a bit) that will produce the following:

所以我有两个对象:Thing和Person.一个人可以持有对单个事物的引用.这是类的简化版本:

So I have two objects: Thing and Person. A Person can hold a reference to a single Thing. Here are simplified version of the Classes:

public class Thing {
    @Id
    public Long id;
    public String name;
    public String description;
}

public class Person {
    @Id
    public Long id;
    public String firstname;
    public String lastname;
    @ManyToOne
    public Thing thing;
}

我正在尝试编写一个JPA查询，该查询将为我提供每个Thing对象的所有详细信息以及Person对象引用Thing对象的次数.请注意，某人的Thing值可能为null.此外，Thing对象可能根本不会被任何Person对象引用，但仍应列出.

I'm trying to write a JPA query that will give me all the details of every Thing object as well as the number of times that Thing object is referenced by a Person object. Note that a Person could have the value null for Thing. Also a Thing object might not be referenced by any Person object at all but should still be listed.

因此，请参见下表:

Thing Table
| id | name | description |
|  1 | thg1 | a thing     |
|  2 | thg2 | another one |
|  3 | thg3 | one more    |

Person Table
| id | firstname | lastname | thing |
|  1 | John      | Smith    |     1 |
|  2 | Simon     | Doe      |     3 |
|  3 | Anne      | Simmons  |     1 |
|  4 | Jessie    | Smith    |     1 |
|  5 | Adam      | Doe      |     3 |
|  6 | Phil      | Murray   |  null |

我最终得到的结果是:

| id | name | description | amount |
|  1 | thg1 | a thing     |      3 |
|  2 | thg2 | another one |      2 |
|  3 | thg3 | one more    |      0 |

我将如何编写该JPA查询? (如果有所作为，我使用的是Play Framework 1.2.5)

How would I go about writing that JPA Query? (If it makes a difference I'm using the Play Framework 1.2.5)

推荐答案

应该是这样的:

select t.id, t.name, t.description, count(p) as amount
         from Person as p right join p.thing as t group by t.id

发生异常右连接"的原因是JPA查询需要查询类之间的映射，并且您只有一个从Person到Thing的映射.

The reason for the unusual "right join" is that JPA queries requires mappings between query classes and you only have one from Person to Thing.

如果您具有从Thing到Person的映射:

If you had a mapping from Thing to Person:

class Thing {
    ...
    @OneToMany
    Set<Person> persons;
}

您可以使用经典的左联接":

you could use the classic "left join":

select t.id, t.name, t.description, count(p) as amount
             from Thing as t left join t.persons as p group by t.id

这篇关于JPA查询选择多对一查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！