我有兩個資料庫表，“A”和“B”，物體 A 的 List<B> 欄位上帶有 @OneToMany(mappedBy = "a") 和欄位 Ba 上的 @ManyToOne 我遇到了“N 1 " 在 A 上進行默認查詢時出現問題，因此我正在嘗試本機查詢，例如：
@Query(value="select * from A as a left join B as b "
"on a.ID = b.b ",
nativeQuery=true)
這在資料按預期映射回物體的意義上起作用。
我的問題是我可以看到 Hibernate 正在為每個 B 做一個單獨的選擇，而不是使用連接的結果。也就是說，我在控制臺中看到了一系列：
- 我指定的選擇
- 對于 A 的每個實體，使用 A 中的 ID 為 B 選擇另一個
換句話說，我仍然有“n 1”問題。
我認為 @OneToMany 和 @ManyToOne 注釋可能會導致 Hibernate 執行這些額外的選擇，但是當我將它們取出時，我的 IDE (IntelliJ) 說：
'Basic' attribute should not be a container
... 在 A 中的 List 屬性上。
我怎樣才能讓它通過連接將結果映射回單個選擇？我應該放棄 Hibernate 和 JPA 嗎？
我正在使用 spring-boot-start-data-jpa.2.5.4
uj5u.com熱心網友回復：
Native@Query沒有足夠的映射能力，所以似乎必須需要 Hibernate 原生查詢。
import java.util.ArrayList;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import org.hibernate.Session;
import org.hibernate.transform.BasicTransformerAdapter;
import org.springframework.stereotype.Repository;
// https://docs.spring.io/spring-data/jpa/docs/2.5.6/reference/html/#repositories.custom-implementations
@Repository
public class CustomizedARepositoryImpl implements CustomizedARepository {
@PersistenceContext
private EntityManager entityManager;
@Override
public List<A> getAll() {
// https://docs.jboss.org/hibernate/orm/5.4/userguide/html_single/Hibernate_User_Guide.html#sql-entity-associations-query
final Session sess = (Session) entityManager.getDelegate();
final List<A> res = sess
// If no duplicate column names, original sql can be used, too.
.createNativeQuery("select {a.*},{b.*} from A as a left join B as b on a.ID = b.a ")
.addEntity("a", A.class)
.addJoin("b", "a.bs")
.setResultTransformer(DistinctResultTransformer.INSTANCE)
.list();
return res;
}
// https://stackoverflow.com/q/12071014/4506703
static class DistinctResultTransformer extends BasicTransformerAdapter {
private static final long serialVersionUID = 1L;
static final DistinctResultTransformer INSTANCE = new DistinctResultTransformer();
@Override
public List transformList(final List collection) {
final List<Object> res = new ArrayList<>();
for (final Object[] obj : (List<Object[]>) collection) {
if (!res.contains(obj[0])) {
res.add(obj[0]);
}
}
return res;
}
}
}
上面的代碼執行 1 個查詢：
select a.id as id1_0_0_, a.name as name2_0_0_,b.a as a3_1_0__, b.id as id1_1_0__, b.id as id1_1_1_, b.a as a3_1_1_, b.name as name2_1_1_
from A as a left join B as b on a.ID = b.a
完整的示例代碼
您可以使用一些方法來避免 N 1 問題。
使用 JPQL fetch，而不是 native-query：
@Query("select distinct a from A a left join fetch a.bs")
List<A> getAllJpqlFetch();
上面的代碼執行 1 個查詢：
select distinct a0_.id as id1_0_0_, bs1_.id as id1_1_1_, a0_.name as name2_0_0_, bs1_.a as a3_1_1_, bs1_.name as name2_1_1_, bs1_.a as a3_1_0__, bs1_.id as id1_1_0__
from a a0_ left outer join b bs1_ on a0_.id=bs1_.a
差異
使用 JPA Criteria fetch，相當于上面的 JPQL：
@Repository
public class CustomizedARepositoryImpl implements CustomizedARepository {
@PersistenceContext
private EntityManager entityManager;
@Override
public List<A> getAllCriteria() {
// https://docs.jboss.org/hibernate/orm/5.4/userguide/html_single/Hibernate_User_Guide.html#criteria-from-fetch
final CriteriaBuilder builder = entityManager.getCriteriaBuilder();
final CriteriaQuery<A> criteria = builder.createQuery(A.class);
final Root<A> root = criteria.from(A.class);
root.fetch("bs", JoinType.LEFT);
criteria.select(root).distinct(true);
return entityManager.createQuery(criteria).getResultList();
}
上面的代碼執行 1 個查詢：
select distinct a0_.id as id1_0_0_, bs1_.id as id1_1_1_, a0_.name as name2_0_0_, bs1_.a as a3_1_1_, bs1_.name as name2_1_1_, bs1_.a as a3_1_0__, bs1_.id as id1_1_0__
from a a0_ left outer join b bs1_ on a0_.id=bs1_.a
差異
使用@Fetch(FetchMode.SUBSELECT)：
import org.hibernate.annotations.Fetch;
import org.hibernate.annotations.FetchMode;
// ...
@Entity
public class A {
@OneToMany(mappedBy = "a")
@Fetch(FetchMode.SUBSELECT)
private List<B> bs;
// ...
}
// findAll() method implementation is auto-generated by Spring Data JPA
// https://docs.spring.io/spring-data/jpa/docs/2.5.6/reference/html/#repositories.core-concepts
repository.findAll();
上面的代碼執行 2 個查詢（根物體及其關系物體）：
select a0_.id as id1_0_, a0_.name as name2_0_ from a a0_
select bs0_.a as a3_1_1_, bs0_.id as id1_1_1_, bs0_.id as id1_1_0_, bs0_.a as a3_1_0_, bs0_.name as name2_1_0_
from b bs0_ where bs0_.a in (select a0_.id from a a0_)
差異
uj5u.com熱心網友回復：
我最終使用了上面由 DEWA Kazuyuki 提供的以下解決方案。我在這里復制它是因為 DEWA 提出了幾個答案，我認為確定對我有用的特定答案很有用。謝謝，德瓦。
@Repository
public class CustomizedARepositoryImpl implements CustomizedARepository {
@PersistenceContext
private EntityManager entityManager;
@Override
public List<A> getAllCriteria() {
// https://docs.jboss.org/hibernate/orm/5.4/userguide/html_single/Hibernate_User_Guide.html#criteria-from-fetch
final CriteriaBuilder builder = entityManager.getCriteriaBuilder();
final CriteriaQuery<A> criteria = builder.createQuery(A.class);
final Root<A> root = criteria.from(A.class);
root.fetch("bs", JoinType.LEFT);
criteria.select(root).distinct(true);
return entityManager.createQuery(criteria).getResultList();
}
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/346587.html