我正在嘗試對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
1 回答
莫回無
TA貢獻1865條經驗 獲得超7個贊
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();
}
添加回答
舉報
0/150
提交
取消