由于内部联接而不是左联接，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 查询您可以按照下一种方法指示左连接：&nbsp; @Query("SELECT new com.your.package.dto.UserDetailOrderCountDto(u.firstName, u.lastName, COUNT(o)) "&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; + "FROM User u LEFT JOIN u.orders o group by u.firstName, u.lastName")&nbsp; List<UserDetailOrderCountDto> findUsersAndOrderCount();只需确保将类属性更改orderCount为 long：public class UserDetailOrderCountDto {&nbsp; private String firstName;&nbsp; private String lastName;&nbsp; private long orderCount;&nbsp; public UserDetail() {&nbsp; }&nbsp; public UserDetail(String firstName, String lastName, long orderCount) {&nbsp; &nbsp; this.firstName = firstName;&nbsp; &nbsp; this.lastName = lastName;&nbsp; &nbsp; this.orderCount = orderCount;&nbsp; }&nbsp; // 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&nbsp; group by u.first_name, u.last_name;"&nbsp; &nbsp; &nbsp; &nbsp;, nativeQuery = true)&nbsp; List<UserDetailOrderCountDto> findUsersAndOrderCount();您只需要确保生成的列名称与 bean 的属性名称匹配。此外，在最新版本的 spring 上，您不需要创建 bean，您可以定义一个接口，然后 spring 创建一个从接口继承的 bean：public interface UserDetailOrderCountDto {&nbsp; public String getFirstName();&nbsp; public String getLastName();&nbsp; public int getOrderCount();}