N + 1个查询不工作(n+1 query not working)

2019-10-20 22:47发布

站内文章 / 移动开发
67 0 0

在下面的代码中，我想到了n+1出现问题的查询，但它没有发生。 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();
        }
    }
}

输出是为： 

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

我预期每个电话号码记录到一个用户ID会有单独的选择语句，但对于3 Phonenumber- 1的userid有一个选择语句[代替（3 + 1）]。 为什么要来这样的吗？

谢谢！

Answer 1:

有在映射几个问题。

首先， one-to-many和many-to-one是在关系数据库由一列表示。 两端的同一列，所以这是错误的： 

// 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"/>

这两个列值必须针对同一列

其次，你遇到1个+ N问题。 有一个 SELECT用户，但3选择为自己的手机。 这是标准的1 + N问题。

该解决方案是使用：

20.1.5。 使用批量抓取

小举：

使用批量抓取，如果一个访问代理Hibernate可以载入其他未代理。 批量抓取是延迟查询抓取的优化方案。 有两种方法可以配置批量抓取：在类级别和集合级别。

批量抓取，类/实体更容易理解。 请看下面的例子：在运行时你在一个Session中载入了25个Cat实例，每个Cat有它的主人，一个人参考。 Person类的映射使用了代理服务器，为lazy =“真”。 如果你必须遍历整个cats集合，呼吁各getOwner（），Hibernate会在默认情况下，执行25个SELECT语句检索代理业主。 您可以通过调整在Person的映射指定批量大小的这种行为： 

<class name="Person" batch-size="10">...</class>

所以，我们应该用在收集此设置： 

<set name="phones" table="PHONE_NUMBERS" batch-size="25"
     cascade="all" lazy="true">

我会建议也用它在每类映射： 

<class name="PhoneNumber" table="PHONE_NUMBERS" batch-size="25">


文章来源: n+1 query not working
标签： hibernate hibernate-onetomany select-n-plus-1
举报

收藏的人(0)

Ta的文章 更多文章
登录 后发表评论
0条评论
还没有人评论过~