n + 1查询无法正常工作 [英] n+1 query not working
本文介绍了n + 1查询无法正常工作的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
在下面的代码中，我期望发生 n + 1 查询问题，但这不会发生。
User.java ：
import java.util。*;
public class User {
private long userId;
private String firstName;
私人设置电话;
public User（）{
System.out.println（0-arg constructor：User）;
}
public String getFirstName（）{
return firstName;
}
public void setFirstName（String firstName）{
this.firstName = firstName;
}
public long getUserId（）{
return userId;
}
public void setUserId（long userId）{
this.userId = userId;
}
public设置getPhones（）{
返回手机;
}
public void setPhones（Set phones）{
this.phones = phones;
}
}
PhoneNumber.java ：
公共类PhoneNumber {
私人字符串numberType;
私人长途电话;
私人长ID;
用户家长;
$ b $ public PhoneNumber（）{
System.out.println（0-arg constructor：PhoneNumber）;
$ b $ //写getXxx（），setXxx（）方法（4套）
public void setId（long id）{
this。 id = id;
}
public long getId（）{
return id;
}
public String getNumberType（）{
return numberType;
}
public void setNumberType（String numberType）{
this.numberType = numberType;
}
public long getPhone（）{
return phone;
}
public void setPhone（长话机）{
this.phone = phone;
}
public void setParent（User parent）{
this.parent = parent;
}
public User getParent（）{
return parent;
}
}
User.hbm。 xml ：
< hibernate-mapping>
< class name =User
table =USER_TABLE>
< id name =userId
column =USER_ID/>
< property name =firstName
column =FIRST_NAME/>
< set name =phones
table =PHONE_NUMBERScascade =all
lazy =true>
< key column =UNID/>
<一对多
class =PhoneNumber/>
< / set>
< / class>
< / hibernate-mapping>
phoneNumber.hbm ：
< hibernate-mapping>
< class name =PhoneNumbertable =PHONE_NUMBERS>
< id name =phonecolumn =PHONE/>
< property name =numberTypecolumn =NUMBER_TYPE/>
< property name =idcolumn =UNIDinsert =falseupdate =false/>
<多对一名称=父级class =用户列=UNID2级联=全部/>
< / class>
< / hibernate-mapping>
hibernate.cfg ：
< session-factory>
name =hibernate.connection.driver_class> oracle.jdbc.driver.OracleDriver< /
property>
name =hibernate.connection.url> jdbc：oracle：thin：@localhost：1521：xe< / prope
rty> ;
name =hibernate.connection.username>系统< / property>
name =hibernate.connection.password> oracle123< / property>
name =hibernate.dialect> org.hibernate.dialect.OracleDialect< / property>
< property name =hibernate.hbm2ddl.auto>更新< / property>
< property name =show_sql> true< / property>
< mapping resource =user.hbm.xml/>
< mapping resource =phoneNumber.hbm.xml/>
< / session-factory>
HQLClient.java ：
import org.hibernate。*;
import org.hibernate.cfg。*;
import java.util。*;
public class HQLJoinsClient {
public static void main（String [] args）{
try {
Configuration conf = new Configuration（）。configure （）;
SessionFactory factory = conf.buildSessionFactory（）;
Session ses = factory.openSession（）;
String hql =from User;
Query q = ses.createQuery（hql）;
List l = q.list（）;
System.out.println（+++++++++++++++++++ l.size（）
+++++++++ +++++）;
for（int i = 0; i< l.size（）; ++ i）{
User u1 =（User）l.get（i）;
System.out
.println（\\\
\\\
\\\
Parent -------------------------- --------------------------------------->中）;
System.out.print（user id：+ u1.getUserId（））;
System.out.println（FirstName+ u1.getFirstName（））;
Set s = u1.getPhones（）;
if（s！= null）{
Iterator it = s.iterator（）; （it.hasNext（））{
PhoneNumber p1 =（PhoneNumber）it.next（）;
System.out.println（\\\
child ---->）;
System.out.print（Number Type =+ p1.getNumberType（））;
System.out.print（Phone Number =+ p1.getPhone（））;
System.out.println（User id =+ p1.getId（））;
$ b} //内部，而
} //如果
}
ses.close（）;
} catch（Exception e）{
e.printStackTrace（）;
输出是：
INFO：架构更新完成休眠：将USER0_.USER_ID选择为USER1_0_，user0_.FIRST_NAME作为FIRST2_0_ USER_TABLE user0_
0-arg构造函数：用户0-参数构造函数：用户0-参数构造函数：User ++++++++++++++++++ 3 +++++++++++++++++++++++++家长----------------------- ------------------------------------------>用户ID：102FirstName ravi休眠：选择phones0_.UNID为UNID1_，phones0_.PHONE为PHONE1_，phones0_.PHONE为PHONE1_0_，phones0_.NUMBER_TYPE为NUMBER2_1_0_，phones0_.UNID为UNID1_0_，phones0_.UNID2为UNID4_1_0_，来自PHONE_NUMBERS phones0_，其中phones0_.UNID =？ 0-arg构造函数：PhoneNumber 0-arg构造函数：PhoneNumber 0-arg构造函数：PhoneNumber child ----> Number Type = resPhone Number = 81818181User id = 102 child ----> Number Type = officePhone Number = 71717171User id = 102 child ---->> Number Type = homePhone Number = 91919191User id = 102 Parent --------------------------------------- -------------------------->用户ID：103FirstName jayendra休眠：选择phones0_.UNID为UNID1_，phones0_.PHONE为PHONE1_，phones0_.PHONE为PHONE1_0_，phones0_.NUMBER_TYPE为NUMBER2_1_0_，phones0_.UNID为UNID1_0_，phones0_.UNID2为UNID4_1_0_，来自PHONE_NUMBERS phones0_，其中phones0_.UNID =？ 0-arg构造函数：PhoneNumber 0-arg构造函数：PhoneNumber child ----> Number Type = resPhone Number = 3748329382User id = 103 child ----> Number Type = homePhone Number = 538432342User id = 103 Parent --------------------------------------- -------------------------->用户ID：104名称麦克休眠：选择phones0_.UNID作为UNID1_，phones0_.PHONE作为PHONE1_，phones0_.PHONE作为PHONE1_0_，phones0_.NUMBER_TYPE作为NUMBER2_1_0_，phones0_.UNID作为UNID1_0_，phones0_.UNID2作为UNID4_1_0_来自PHONE_NUMBERS phones0_，其中phones0_.UNID =？ 0-arg构造函数：PhoneNumber 0-arg构造函数：PhoneNumber child ---->号码类型=家庭电话号码= 238349384用户id = 104孩子----> Number Type = mobilePhone Number = 9455682832User id = 104
我预计每个电话号码记录都会以用户ID将会有单独的select语句，但是对于3个phonenumber-1 userid，有一个select语句[而不是（3 + 1）]。为什么会这样？
谢谢！
解决方案你的映射中很少有问题。
首先，一对多和多对一在关系DB中由一列表示。
// class name =User
< / p> set name =phonestable =PHONE_NUMBERS
cascade =alllazy =true>
< key column =UNID/> //此列必须相同
<一对多等级=PhoneNumber/>
< / set>
// class name =PhoneNumber
...
<多对一名称=父class =User
column = UNID2// as this column
cascade =all/>
这两列值必须定位在同一列
其次，您遇到1 + N问题。有一个 SELECT用户，但3个用于他们的电话。这是标准的1 + N问题。
解决方案是使用：
20.1.5。使用批量抓取
小引用：
$ b
使用批量抓取，如果访问一个代理，Hibernate可以加载多个未初始化的代理。批量抓取是对懒惰选择抓取策略的优化。有两种方法可以配置批次抓取：在类级别和集合级别。
类/实体的批量抓取更容易理解。考虑下面的例子：在运行时，你有一个会话中加载了25个Cat实例，每个Cat都有一个对它的所有者的引用Person。 Person类映射了一个代理，lazy =true。如果你现在遍历所有的猫，并调用getOwner（），默认情况下，Hibernate将执行25条SELECT语句来检索代理所有者。您可以通过在Person的映射中指定批处理大小来调整此行为：
<< ; class name =Personbatch-size =10> ...< / class>
因此，我们应该在集合上使用此设置：
< set name =phonestable =PHONE_NUMBERSbatch-size =25
cascade =alllazy =true>
我也建议在每个类映射中使用它：
< class name =PhoneNumbertable =PHONE_NUMBERSbatch-size =25>
In the below code I expect the n+1 query problem to occur, but it's not happening.
User.java:
import java.util.*;
public class User {
private long userId;
private String firstName;
private Set phones;
public User() {
System.out.println("0-arg constructor :User");
}
public String getFirstName() {
return firstName;
}
public void setFirstName(String firstName) {
this.firstName = firstName;
}
public long getUserId() {
return userId;
}
public void setUserId(long userId) {
this.userId = userId;
}
public Set getPhones() {
return phones;
}
public void setPhones(Set phones) {
this.phones = phones;
}
}
PhoneNumber.java:
public class PhoneNumber {
private String numberType;
private long phone;
private long id;
User parent;
public PhoneNumber() {
System.out.println("0-arg constructor :PhoneNumber");
}
// write getXxx(),setXxx() methods (4 sets)
public void setId(long id) {
this.id = id;
}
public long getId() {
return id;
}
public String getNumberType() {
return numberType;
}
public void setNumberType(String numberType) {
this.numberType = numberType;
}
public long getPhone() {
return phone;
}
public void setPhone(long phone) {
this.phone = phone;
}
public void setParent(User parent) {
this.parent = parent;
}
public User getParent() {
return parent;
}
}
User.hbm.xml:
<hibernate-mapping>
<class name="User"
table="USER_TABLE" >
<id name="userId"
column="USER_ID"/>
<property name="firstName"
column="FIRST_NAME"/>
<set name="phones"
table="PHONE_NUMBERS" cascade="all"
lazy="true">
<key column="UNID"/>
<one-to-many
class="PhoneNumber"/>
</set>
</class>
</hibernate-mapping>
phoneNumber.hbm:
<hibernate-mapping>
<class name="PhoneNumber" table="PHONE_NUMBERS" >
<id name="phone" column="PHONE"/>
<property name="numberType" column="NUMBER_TYPE"/>
<property name="id" column="UNID" insert="false" update="false"/>
<many-to-one name="parent" class="User" column="UNID2" cascade="all"/>
</class>
</hibernate-mapping>
hibernate.cfg:
<session-factory>
<property
name="hibernate.connection.driver_class">oracle.jdbc.driver.OracleDriver</
property>
<property
name="hibernate.connection.url">jdbc:oracle:thin:@localhost:1521:xe</prope
rty>
<property
name="hibernate.connection.username">system</property>
<property
name="hibernate.connection.password">oracle123</property>
<property
name="hibernate.dialect">org.hibernate.dialect.OracleDialect</property>
<property name="hibernate.hbm2ddl.auto">update</property>
<property name="show_sql">true</property>
<mapping resource="user.hbm.xml"/>
<mapping resource="phoneNumber.hbm.xml"/>
</session-factory>
HQLClient.java:
import org.hibernate.*;
import org.hibernate.cfg.*;
import java.util.*;
public class HQLJoinsClient {
public static void main(String[] args) {
try {
Configuration conf = new Configuration().configure();
SessionFactory factory = conf.buildSessionFactory();
Session ses = factory.openSession();
String hql = "from User ";
Query q = ses.createQuery(hql);
List l = q.list();
System.out.println("++++++++++++++++++" + l.size()
+ "+++++++++++++");
for (int i = 0; i < l.size(); ++i) {
User u1 = (User) l.get(i);
System.out
.println("\n\n\nParent----------------------------------------------------------------->");
System.out.print("user id: " + u1.getUserId());
System.out.println("FirstName " + u1.getFirstName());
Set s = u1.getPhones();
if (s != null) {
Iterator it = s.iterator();
while (it.hasNext()) {
PhoneNumber p1 = (PhoneNumber) it.next();
System.out.println("\nchild---->");
System.out.print("Number Type=" + p1.getNumberType());
System.out.print("Phone Number=" + p1.getPhone());
System.out.println("User id=" + p1.getId());
}// inner while
}// if
}
ses.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
The output is as:
INFO: schema update complete Hibernate: select user0_.USER_ID as USER1_0_, user0_.FIRST_NAME as FIRST2_0_ from USER_TABLE user0_
0-arg constructor :User 0-arg constructor :User 0-arg constructor :User ++++++++++++++++++3+++++++++++++++++++++++++ Parent-----------------------------------------------------------------> user id: 102FirstName ravi Hibernate: select phones0_.UNID as UNID1_, phones0_.PHONE as PHONE1_, phones0_.PHONE as PHONE1_0_, phones0_.NUMBER_TYPE as NUMBER2_1_0_, phones0_.UNID as UNID1_0_, phones0_.UNID2 as UNID4_1_0_ from PHONE_NUMBERS phones0_ where phones0_.UNID=? 0-arg constructor :PhoneNumber 0-arg constructor :PhoneNumber 0-arg constructor :PhoneNumber child----> Number Type=resPhone Number=81818181User id=102 child----> Number Type=officePhone Number=71717171User id=102 child----> Number Type=homePhone Number=91919191User id=102 Parent-----------------------------------------------------------------> user id: 103FirstName jayendra Hibernate: select phones0_.UNID as UNID1_, phones0_.PHONE as PHONE1_, phones0_.PHONE as PHONE1_0_, phones0_.NUMBER_TYPE as NUMBER2_1_0_, phones0_.UNID as UNID1_0_, phones0_.UNID2 as UNID4_1_0_ from PHONE_NUMBERS phones0_ where phones0_.UNID=? 0-arg constructor :PhoneNumber 0-arg constructor :PhoneNumber child----> Number Type=resPhone Number=3748329382User id=103 child----> Number Type=homePhone Number=538432342User id=103 Parent-----------------------------------------------------------------> user id: 104FirstName mike Hibernate: select phones0_.UNID as UNID1_, phones0_.PHONE as PHONE1_, phones0_.PHONE as PHONE1_0_, phones0_.NUMBER_TYPE as NUMBER2_1_0_, phones0_.UNID as UNID1_0_, phones0_.UNID2 as UNID4_1_0_ from PHONE_NUMBERS phones0_ where phones0_.UNID=? 0-arg constructor :PhoneNumber 0-arg constructor :PhoneNumber child----> Number Type=homePhone Number=238349384User id=104 child----> Number Type=mobilePhone Number=9455682832User id=104
I expected for each phone number record to a user id there will be separate select statement but for 3 Phonenumber- 1 userid there is one select statement [instead of (3+1)]. why is coming like this ?
Thanks!
解决方案 There are few issues in your mapping.
First of all, one-to-many and many-to-one is in relational DB expressed by one column. The same column on both ends, so this is wrong:
// class name="User"
<set name="phones" table="PHONE_NUMBERS"
cascade="all" lazy="true">
<key column="UNID"/> // this column must be same
<one-to-many class="PhoneNumber" />
</set>
// class name="PhoneNumber"
...
<many-to-one name="parent" class="User"
column="UNID2" // as this column
cascade="all"/>
Both column values must be targeting the same column
Secondly, you are experiencing 1 + N issue. There is one SELECT for user, but 3 selects for their Phones. This is standard 1 + N problem.
The solution is to use:
20.1.5. Using batch fetching
small cite:
Using batch fetching, Hibernate can load several uninitialized proxies if one proxy is accessed. Batch fetching is an optimization of the lazy select fetching strategy. There are two ways you can configure batch fetching: on the class level and the collection level.
Batch fetching for classes/entities is easier to understand. Consider the following example: at runtime you have 25 Cat instances loaded in a Session, and each Cat has a reference to its owner, a Person. The Person class is mapped with a proxy, lazy="true". If you now iterate through all cats and call getOwner() on each, Hibernate will, by default, execute 25 SELECT statements to retrieve the proxied owners. You can tune this behavior by specifying a batch-size in the mapping of Person:
<class name="Person" batch-size="10">...</class>
So, we should use this setting on collection:
<set name="phones" table="PHONE_NUMBERS" batch-size="25"
cascade="all" lazy="true">
And I would suggest to use it also on every class mapping:
<class name="PhoneNumber" table="PHONE_NUMBERS" batch-size="25">
这篇关于n + 1查询无法正常工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
