我正在嘗試對User具有訂單列表的實(shí)體進(jìn)行簡單的 DTO 投影。投影應(yīng)僅包含用戶名、姓氏和Order鏈接表中的 s 數(shù)。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();}數(shù)據(jù)庫包含 2 個(gè)用戶的 2 個(gè)訂單。有很多用戶沒有任何訂單（我仍然希望將 orderCount 設(shè)為 0 來接收）。存儲庫中的查詢?yōu)?2 個(gè)用戶返回 2 個(gè) DTO，每個(gè)用戶有 1 個(gè)訂單（正確），但沒有訂單的用戶會被跳過（因?yàn)樗皇亲筮B接的）。Hibernate生成的查詢?nèi)缦拢簊elect 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我怎樣才能強(qiáng)制 Hibernate 給我所有的用戶（又名左連接，但如果可能的話沒有本地查詢）？或任何其他方法來獲得我想要的解決方案？任何幫助表示贊賞。謝謝你。更新 1： 如果我嘗試強(qiáng)制 Hibernate 加入表FetchMode.JOIN，它仍然使用內(nèi)部聯(lián)接。@OneToMany(mappedBy = "user", fetch = FetchType.LAZY)@Fetch(FetchMode.JOIN)private Set<Order> orders;然后查詢?nèi)缦滤荆簊elect 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 回答
莫回?zé)o
TA貢獻(xiàn)1865條經(jīng)驗(yàn) 獲得超7個(gè)贊
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;
使用本機(jī)查詢
您可以改用本機(jī)查詢，因此您可以定義左連接：
@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 上，您不需要?jiǎng)?chuàng)建 bean，您可以定義一個(gè)接口，然后 spring 創(chuàng)建一個(gè)從接口繼承的 bean：
public interface UserDetailOrderCountDto {
public String getFirstName();
public String getLastName();
public int getOrderCount();
}
添加回答
舉報(bào)
0/150
提交
取消