由于内部联接而不是左联接，Spring DTO 投影查询不返回所有结果

Question

我正在尝试对User具有订单列表的实体进行简单的 DTO 投影。投影应仅包含用户名、姓氏和Order链接表中的 s 数。User班级：@Entity@Table(name = "user")public class User {    @Column(name = "firstName")    private String firstName;    @Column(name = "lastName")    private String lastName;    @OneToMany(mappedBy = "user", fetch = FetchType.LAZY)    private Set<Order> orders;    // many other fields here}Order班级：@Entity@Table(name = "order")public class Order {    @ManyToOne    @JoinColumn(name = "user_id")    private User user;    // many other fields here}然后我有 DTO 对象：public class UserDetailOrderCountDto {    private String firstName;    private String lastName;    private int orderCount;    public UserDetailOrderCountDto(String firstName, String lastName, int orderCount) {        this.firstName = firstName;        this.lastName = lastName;        this.orderCount = orderCount;    }    // getters, setters, ...}最后是带有查询的存储库：public interface UserRepository extends JpaRepository<User, Long> {    @Query("select new a.b.c.UserDetailOrderCountDto(u.firstName, u.lastName, size(u.orders)) from User u group by u.firstName, u.lastName")    List<UserDetailOrderCountDto> findUsersAndOrderCount();}数据库包含 2 个用户的 2 个订单。有很多用户没有任何订单（我仍然希望将 orderCount 设为 0 来接收）。存储库中的查询为 2 个用户返回 2 个 DTO，每个用户有 1 个订单（正确），但没有订单的用户会被跳过（因为它不是左连接的）。Hibernate生成的查询如下：select user0_.firstName as col_0_0_, user0_.lastName as col_1_0_, count(orders1_.user_id) as col_2_0_ from user user0_, orders orders1_ where user0_.id=orders1_.user_id group by user0_.firstName , user0_.lastName我怎样才能强制 Hibernate 给我所有的用户（又名左连接，但如果可能的话没有本地查询）？或任何其他方法来获得我想要的解决方案？任何帮助表示赞赏。谢谢你。更新 1： 如果我尝试强制 Hibernate 加入表FetchMode.JOIN，它仍然使用内部联接。@OneToMany(mappedBy = "user", fetch = FetchType.LAZY)@Fetch(FetchMode.JOIN)private Set<Order> orders;然后查询如下所示：select user0_.firstName as col_0_0_, user0_.lastName as col_1_0_, count(orders1_.user_id) as col_2_0_ from user user0_ cross join orders orders1_ where user0_.id=orders1_.user_id group by user0_.firstName , user0_.lastName

Answer 1

JPA 查询

您可以按照下一种方法指示左连接：

  @Query("SELECT new com.your.package.dto.UserDetailOrderCountDto(u.firstName, u.lastName, COUNT(o)) "

          + "FROM User u LEFT JOIN u.orders o group by u.firstName, u.lastName")

  List<UserDetailOrderCountDto> findUsersAndOrderCount();

只需确保将类属性更改orderCount为 long：

public class UserDetailOrderCountDto {

  private String firstName;

  private String lastName;

  private long orderCount;

  public UserDetail() {

  }

  public UserDetail(String firstName, String lastName, long orderCount) {

    this.firstName = firstName;

    this.lastName = lastName;

    this.orderCount = orderCount;

  }

  // Getters and setters

}

请注意，这适用于您User班级的以下配置：

@OneToMany(mappedBy = "user", fetch = FetchType.LAZY)

private Set<Order> orders;

使用本机查询

您可以改用本机查询，因此您可以定义左连接：

@Query(value = "select u.first_name as firstName, u.last_name as lastName, count(o.id) as orderCount from user u left join orders o on u.id = o.user_id  group by u.first_name, u.last_name;"

       , nativeQuery = true)

  List<UserDetailOrderCountDto> findUsersAndOrderCount();

您只需要确保生成的列名称与 bean 的属性名称匹配。

此外，在最新版本的 spring 上，您不需要创建 bean，您可以定义一个接口，然后 spring 创建一个从接口继承的 bean：

public interface UserDetailOrderCountDto {

  public String getFirstName();

  public String getLastName();

  public int getOrderCount();

}