有一种避免n + 1条查询而不接触实体的方法,仅更改对findAll的查询。我们可以编写一个包装器函数,该函数将首先为人加载汽车,然后他们一次选择即可获取所有工具。
PersonRepository
@Query("SELECT distinct p FROM Person p JOIN FETCH p.autos a")
List<Person> findAll();
包装代码
List<Person> persons = personRepository.findAll();
Session session = (Session) entityManager.getDelegate();
List<Auto> autos = new ArrayList<>();
for (Person person : persons) {
if(!CollectionUtils.isEmpty(person.getAutos())) {
autos.addAll(person.getAutos());
}
}
try{
autos = session.createQuery("select distinct a from Auto a Join fetch a.tools " +
" where a in :autos",Auto.class)
.setParameter("autos",autos)
.setHint(QueryHints.PASS_DISTINCT_THROUGH,false)
.getResultList();
} catch (Exception ex) {
ex.printStackTrace();
}
第一个查询将是:
SELECT DISTINCT
person0_.id AS id1_6_0_,autos1_.id AS id1_0_1_,person0_.name AS name2_6_0_,autos1_.name AS name2_0_1_,autos1_.person_id AS person_i3_0_1_,autos1_.person_id AS person_i3_0_0__,autos1_.id AS id1_0_0__
FROM
Person person0_
INNER JOIN
Auto autos1_
ON
person0_.id=autos1_.person_id
生成的第二个查询将是:
SELECT
auto0_.id AS id1_0_0_,tools1_.id AS id1_8_1_,auto0_.name AS name2_0_0_,auto0_.person_id AS person_i3_0_0_,tools1_.auto_id AS auto_id3_8_1_,tools1_.name AS name2_8_1_,tools1_.auto_id AS auto_id3_8_0__,tools1_.id AS id1_8_0__
FROM
Auto auto0_
INNER JOIN
Tool tools1_
ON
auto0_.id=tools1_.auto_id
WHERE
auto0_.id IN (?,?,?)
除此之外,我认为我们的选择是有限的,我们将不得不更改 Tool 实体FetchMode或为默认的FetchMode.SELECT添加BatchSize以便在单独的查询中获得Tools。
@OneToMany(mappedBy = "auto",fetch = FetchType.LAZY)
@Fetch(FetchMode.SUBSELECT)
private List<Tool> tools;
查询将
SELECT
tools0_.auto_id AS auto_id3_8_1_,tools0_.id AS id1_8_1_,tools0_.id AS id1_8_0_,tools0_.auto_id AS auto_id3_8_0_,tools0_.name AS name2_8_0_
FROM
Tool tools0_
WHERE
tools0_.auto_id IN
(
SELECT
autos1_.id
FROM
Person person0_
INNER JOIN
Auto autos1_
ON
person0_.id=autos1_.person_id
)
本文链接:https://www.f2er.com/3132303.html