我假设我遇到了 N+1 选择问题。
我得到了这个实体:
@Entity
@Table(name = "Devices")
public class Device implements Serializable {
@OneToOne(mappedBy="holdingDevice", fetch=FetchType.LAZY)
@Cascade(CascadeType.ALL)
@PrimaryKeyJoinColumn
private WarrantyEntry warranty;
}
这是另一个实体:
@Entity
@Table(name = "Warranty")
public class WarrantyEntry implements Serializable{
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy=GenerationType.AUTO)
private Long id;
@OneToOne
@JoinColumn(name = "serial")
@PrimaryKeyJoinColumn
private Device holdingDevice;
现在,当我开始迭代此循环时:
Set<Device> customerDevices = user.getCustomer().getDevices();
for (Device device : customerDevices) {
...
}
我被卡住了,我在日志中看到Hibernate选择:
休眠:*/选择 warrantyen0_.id 作为...休眠:/* 加载我的类。装置*/
休眠:*/选择 warrantyen0_.id 作为 ..休眠:/* 加载我的类。装置*/。。。
一遍又一遍,我想我遇到了 n+1 选择问题。
任何建议如何解决并仅用一个选择替换所有这些选择?
完成此操作后
String query="from Customer c left join fetch c.devices d n" +
"left join fetch d.tradeInOldDevice " +
"left join fetch d.tradeInNewDevice "+
"left join fetch d.warranty";
deviceDao.getSessionFactory().openSession().createQuery(query);
for (Device device : customerDevices) {
..
}
我仍然明白:
Hibernate:
devices0_.owningCompany_customerRefId as owningC15_0_1_,
devices0_.serial as serial1_,
devices0_.serial as serial9_0_,
devices0_.blackListed as blackLis2_9_0_,
devices0_.Creation_id as Creation12_9_0_,
devices0_.deactivated as deactiva3_9_0_,
devices0_.deviceComment as deviceCo4_9_0_,
devices0_.deviceName as deviceName9_0_,
devices0_.deviceType as deviceType9_0_,
devices0_.distributor_customerRefId as distrib13_9_0_,
devices0_.endCustomer_customerRefId as endCust14_9_0_,
devices0_.owningCompany_customerRefId as owningC15_9_0_,
devices0_.paChallenge as paChalle7_9_0_,
devices0_.parent_serial as parent16_9_0_,
devices0_.pendingDeactivation as pendingD8_9_0_,
devices0_.safetyStock as safetySt9_9_0_,
devices0_.serialSalt as serialSalt9_0_,
devices0_.signedBlackBerry as signedB11_9_0_,
devices0_.tradeInOldDevice as tradeIn17_9_0_
from
Devices devices0_
where
devices0_.owningCompany_customerRefId=?
Hibernate:
device0_.serial as serial9_0_,
device0_.blackListed as blackLis2_9_0_,
device0_.Creation_id as Creation12_9_0_,
device0_.deactivated as deactiva3_9_0_,
device0_.deviceComment as deviceCo4_9_0_,
device0_.deviceName as deviceName9_0_,
device0_.deviceType as deviceType9_0_,
device0_.distributor_customerRefId as distrib13_9_0_,
device0_.endCustomer_customerRefId as endCust14_9_0_,
device0_.owningCompany_customerRefId as owningC15_9_0_,
device0_.paChallenge as paChalle7_9_0_,
device0_.parent_serial as parent16_9_0_,
device0_.pendingDeactivation as pendingD8_9_0_,
device0_.safetyStock as safetySt9_9_0_,
device0_.serialSalt as serialSalt9_0_,
device0_.signedBlackBerry as signedB11_9_0_,
device0_.tradeInOldDevice as tradeIn17_9_0_
from
Devices device0_
where
device0_.tradeInOldDevice=?
Hibernate:
warrantyen0_.id as id34_2_,
warrantyen0_.createdTime as createdT2_34_2_,
warrantyen0_.deleted as deleted34_2_,
warrantyen0_.expiryDate as expiryDate34_2_,
warrantyen0_.serial as serial34_2_,
warrantyen0_.updateTime as updateTime34_2_,
warrantyen0_.updateUser as updateUser34_2_,
device1_.serial as serial9_0_,
device1_.blackListed as blackLis2_9_0_,
device1_.Creation_id as Creation12_9_0_,
device1_.deactivated as deactiva3_9_0_,
device1_.deviceComment as deviceCo4_9_0_,
device1_.deviceName as deviceName9_0_,
device1_.deviceType as deviceType9_0_,
device1_.distributor_customerRefId as distrib13_9_0_,
device1_.endCustomer_customerRefId as endCust14_9_0_,
device1_.owningCompany_customerRefId as owningC15_9_0_,
device1_.paChallenge as paChalle7_9_0_,
device1_.parent_serial as parent16_9_0_,
device1_.pendingDeactivation as pendingD8_9_0_,
device1_.safetyStock as safetySt9_9_0_,
device1_.serialSalt as serialSalt9_0_,
device1_.signedBlackBerry as signedB11_9_0_,
device1_.tradeInOldDevice as tradeIn17_9_0_,
management2_.id as id22_1_,
management2_1_.deleted as deleted22_1_,
management2_1_.firstName as firstName22_1_,
management2_1_.lastLogin as lastLogin22_1_,
management2_1_.lastName as lastName22_1_,
management2_1_.password as password22_1_,
management2_1_.primaryEmail as primaryE7_22_1_,
management2_1_.userName as userName22_1_,
management2_.authority as authority23_1_,
management2_.isViewer as isViewer23_1_,
management2_3_.distributor as distribu1_25_1_,
management2_4_.umeKeysQuota as umeKeysQ1_27_1_,
case
when management2_2_.id is not null then 2
when management2_3_.id is not null then 3
when management2_4_.id is not null then 5
when management2_5_.id is not null then 6
when management2_6_.id is not null then 7
when management2_.id is not null then 1
end as clazz_1_,
cids3_.Users_id as Users1_22_4_,
cids3_.element as element4_,
emails4_.Users_id as Users1_22_5_,
emails4_.element as element5_,
roles5_.Users_Management_id as Users1_22_6_,
roles5_.element as element6_
from
Warranty warrantyen0_
left outer join
Devices device1_
on warrantyen0_.serial=device1_.serial
left outer join
Users_Management management2_
on warrantyen0_.updateUser=management2_.id
left outer join
Users management2_1_
on management2_.id=management2_1_.id
left outer join
Users_Management_Administrators management2_2_
on management2_.id=management2_2_.id
left outer join
Users_Management_Distributors management2_3_
on management2_.id=management2_3_.id
left outer join
Users_Management_Limited management2_4_
on management2_.id=management2_4_.id
left outer join
Users_Management_Managers management2_5_
on management2_.id=management2_5_.id
left outer join
Users_Management_Workers management2_6_
on management2_.id=management2_6_.id
left outer join
Users_CID cids3_
on management2_.id=cids3_.Users_id
left outer join
Users_Emails emails4_
on management2_.id=emails4_.Users_id
left outer join
Users_Management_roles roles5_
on management2_.id=roles5_.Users_Management_id
where
warrantyen0_.serial=?
谢谢射线。
假设您已将Customer
类中的customerDevices
关联定义为如下所示:
@OneToMany(fetch = FetchType.LAZY, mappedBy = "device")
private Set<Device> customerDevices;
此LAZY
映射关联使您面临n+1
选择的问题。让我们考虑一个简单的查询,该查询检索给定customerId
的customer
:
session().createQuery("from Customer c where c.name=:name").setParameter("name", name);
这将返回一个customer
其中customerDevices
集合是未初始化的集合包装器。现在,当您循环迭代时:
Set<Device> customerDevices = user.getCustomer().getDevices();
for (Device device : customerDevices) {
...
}
在访问devices
集合时,Hibernate必须从数据库中获取这个惰性集合,执行额外的select
语句。
此问题的建议解决方案是在运行时在代码中重写默认提取策略,这可以通过使用如下所示的查询来实现:
session().createQuery(from Customer c left join fetch c.devices d
left join fetch d.warrantyEntry)
这将返回一个Customer
以及关联的集合。因此,不是只检索初始查询中的顶级对象,而是通过准确指定将在正在进行的工作单元中访问哪些关联来获取初始查询中所有需要的数据。
编辑:
看起来有时一对一映射会导致 HQL 紧急获取失败。您可以在这些讨论中找到有关此问题的更多信息:HQL 预先获取失败和左侧连接的 N+1 选择。
那里提到的一种解决方案是将OneToOne
映射更改为 ManyToOne
和 OneToMany
,我尝试过(使用类似类型的模型类),它非常适合我。我通过一个 sql 查询选择了所有结果。
例如,在Device
类中,可以将OneToOne
映射更改为ManyToOne
:
@ManyToOne(cascade = CascadeType.ALL)
@JoinColumn(name = "warrantyFK", unique = true)
private WarrantyEntry warranty;
unique=true
属性将使此关联成为一对一关联,因为没有两个Device
可以具有相同的WarrantyEntry
。您可以在 WarrantyEntry
类中定义Device
集合:
@OneToMany(mappedBy = "warranty")
private Set<Device> holdingDevices;
主要思想是使用 OneToMany
和 ManyToOne
而不是 OneToOne
.您只需要确保最多向映射OneToMany
端的set
添加一个项目。
它可以是"n+1 选择问题",你可以确保将这些属性放在你的持久性中.xml:
<property name="hibernate.show_sql">true</property>
<property name="hibernate.format_sql">true</property>
<property name="hibernate.use_sql_comments">true</property>
我认为你也可以在你的JoinColumn中使用unique=true,nullable=false。
您可以尝试使用 JPQL/HQL 进行急切获取并解决此问题(也许使用标准也可以解决,不确定)。