1+N问题/典型的面试题
a)Lazy
b)BatchSize
c)join fetch
什么是1+N:如果我在一个对象里面关联另外一个对象,同时fetch=FetchType.EAGER,
最典型的是@ManyToOne。本来我用一条Sql语句就可以解决的,结果发了1条外加N条sql语句。
1+N问题实例剖析
Category.java:
Topic.java:
建表之后先填入记录:
//测试1加N问题
Hibernate:
select
topic0_.id as id2_,
topic0_.category_id as category4_2_,
topic0_.createDate as createDate2_,
topic0_.title as title2_
from
Topic topic0_
Hibernate:
select
category0_.id as id0_0_,
category0_.name as name0_0_
from
Category category0_
where
category0_.id=?
Hibernate:
select
category0_.id as id0_0_,
category0_.name as name0_0_
from
Category category0_
where
category0_.id=?
Hibernate:
select
category0_.id as id0_0_,
category0_.name as name0_0_
from
Category category0_
where
category0_.id=?
Hibernate:
select
category0_.id as id0_0_,
category0_.name as name0_0_
from
Category category0_
where
category0_.id=?
Hibernate:
select
category0_.id as id0_0_,
category0_.name as name0_0_
from
Category category0_
where
category0_.id=?
Hibernate:
select
category0_.id as id0_0_,
category0_.name as name0_0_
from
Category category0_
where
category0_.id=?
Hibernate:
select
category0_.id as id0_0_,
category0_.name as name0_0_
from
Category category0_
where
category0_.id=?
Hibernate:
select
category0_.id as id0_0_,
category0_.name as name0_0_
from
Category category0_
where
category0_.id=?
Hibernate:
select
category0_.id as id0_0_,
category0_.name as name0_0_
from
Category category0_
where
category0_.id=?
Hibernate:
select
category0_.id as id0_0_,
category0_.name as name0_0_
from
Category category0_
where
category0_.id=?
1-t0
2-t1
3-t2
4-t3
5-t4
6-t5
7-t6
8-t7
9-t8
10-t9
我只是想取出topic而已,为什么多了那么多category?因为在实体类上,topic类的category属性上面的@ManyToOne的fetch默认是EAGER,所以取出topic的时候,会顺带着把topic关联的category出取出来。取category的过程就是,每取一个topic的时候,根据topic中的category_id取category表中按照id号取出相应的category对象。
这就是1+N问题,就是本来你该发一条sql语句,结果发了N条sql语句。
解决方案1:
如果上面的@MangToOne改为@ManyToOne(fetch=FetchType.LAZY)
结果就变成了:
Hibernate:
select
topic0_.id as id2_,
topic0_.category_id as category4_2_,
topic0_.createDate as createDate2_,
topic0_.title as title2_
from
Topic topic0_
1-t0
2-t1
3-t2
4-t3
5-t4
6-t5
7-t6
8-t7
9-t8
10-t9
只有你取category的时候他才会发出sql语句(按需而发)
解决方案2:
使用QBC面向对象的查询语句:
List<Topic> topics=(List<Topic>)session.createCriteria(Topic.class).list();
这是因为Criteria默认的是用了"表连接"的方式来取数据,两张表做连接来取,而不是另外单独发sql语句来取。
测试结果:
Hibernate:
select
this_.id as id2_0_,
this_.category_id as category4_2_0_,
this_.createDate as createDate2_0_,
this_.title as title2_0_
from
Topic this_
1-t0
2-t1
3-t2
4-t3
5-t4
6-t5
7-t6
8-t7
9-t8
10-t9
解决方案3:(这里@ManyToOne的fetch默认仍是EAGER)
@BatchSize(size=5)
首先在Category的类上面加@BatchSize(size=5):
测试:
Hibernate:
select
topic0_.id as id2_,
topic0_.category_id as category4_2_,
topic0_.createDate as createDate2_,
topic0_.title as title2_
from
Topic topic0_
Hibernate:
select
category0_.id as id0_0_,
category0_.name as name0_0_
from
Category category0_
where
category0_.id in (
?, ?, ?, ?, ?
)
Hibernate:
select
category0_.id as id0_0_,
category0_.name as name0_0_
from
Category category0_
where
category0_.id in (
?, ?, ?, ?, ?
)
1-t0
2-t1
3-t2
4-t3
5-t4
6-t5
7-t6
8-t7
9-t8
10-t9
发现关于Category的查询语句变成了两条,每条查询五个记录。(取出了10条)
这样就没有必要一条一条的去取了。要设成10只要发一个就可以了。
BatchSize只是提高了效率,少发多条sql语句,其实并没有完全解决1+N问题。
解决方案4:join fetch
Hibernate:
select
topic0_.id as id2_0_,
category1_.id as id0_1_,
topic0_.category_id as category4_2_0_,
topic0_.createDate as createDate2_0_,
topic0_.title as title2_0_,
category1_.name as name0_1_
from
Topic topic0_
left outer join
Category category1_
on topic0_.category_id=category1_.id
1-t0
2-t1
3-t2
4-t3
5-t4
6-t5
7-t6
8-t7
9-t8
10-t9
a)Lazy
b)BatchSize
c)join fetch
什么是1+N:如果我在一个对象里面关联另外一个对象,同时fetch=FetchType.EAGER,
最典型的是@ManyToOne。本来我用一条Sql语句就可以解决的,结果发了1条外加N条sql语句。
1+N问题实例剖析
Category.java:
package com.bjsxt.hibernate; import javax.persistence.Entity; import javax.persistence.GeneratedValue; import javax.persistence.Id; @Entity public class Category { private int id; private String name; @Id @GeneratedValue public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } }
Topic.java:
package com.bjsxt.hibernate; import java.util.ArrayList; import java.util.Date; import java.util.List; import javax.persistence.Entity; import javax.persistence.FetchType; import javax.persistence.GeneratedValue; import javax.persistence.Id; import javax.persistence.ManyToOne; import javax.persistence.NamedQueries; import javax.persistence.NamedQuery; import javax.persistence.OneToMany; import javax.persistence.Temporal; import javax.persistence.TemporalType; @Entity public class Topic { private int id; private String title; private Category category; private Date createDate; private List<Msg> msgs = new ArrayList<Msg>(); @OneToMany(mappedBy="topic") public List<Msg> getMsgs() { return msgs; } public void setMsgs(List<Msg> msgs) { this.msgs = msgs; } @Temporal(TemporalType.TIME) public Date getCreateDate() { return createDate; } public void setCreateDate(Date createDate) { this.createDate = createDate; } @ManyToOne public Category getCategory() { return category; } public void setCategory(Category category) { this.category = category; } @Id @GeneratedValue public int getId() { return id; } public void setId(int id) { this.id = id; } public String getTitle() { return title; } public void setTitle(String title) { this.title = title; } }
建表之后先填入记录:
@Test public void testSave() { Session session = sf.openSession(); session.beginTransaction(); //每一个版块ci下面有一个话题ti for(int i=0; i<10; i++) { Category c = new Category(); c.setName("c" + i); Topic t=new Topic(); t.setCategory(c); t.setTitle("t"+i); t.setCreateDate(new Date()); session.save(c); session.save(t); } session.getTransaction().commit(); session.close(); }
//测试1加N问题
@Test public void testOneAddNProblem1(){ Session session = sf.openSession(); session.beginTransaction(); List<Topic> topics=(List<Topic>)session.createQuery("from Topic").list(); for(Topic t:topics){ System.out.println(t.getId()+"-"+t.getTitle()); } session.getTransaction().commit(); session.close(); }先取出Topic的所有信息,结果:
Hibernate:
select
topic0_.id as id2_,
topic0_.category_id as category4_2_,
topic0_.createDate as createDate2_,
topic0_.title as title2_
from
Topic topic0_
Hibernate:
select
category0_.id as id0_0_,
category0_.name as name0_0_
from
Category category0_
where
category0_.id=?
Hibernate:
select
category0_.id as id0_0_,
category0_.name as name0_0_
from
Category category0_
where
category0_.id=?
Hibernate:
select
category0_.id as id0_0_,
category0_.name as name0_0_
from
Category category0_
where
category0_.id=?
Hibernate:
select
category0_.id as id0_0_,
category0_.name as name0_0_
from
Category category0_
where
category0_.id=?
Hibernate:
select
category0_.id as id0_0_,
category0_.name as name0_0_
from
Category category0_
where
category0_.id=?
Hibernate:
select
category0_.id as id0_0_,
category0_.name as name0_0_
from
Category category0_
where
category0_.id=?
Hibernate:
select
category0_.id as id0_0_,
category0_.name as name0_0_
from
Category category0_
where
category0_.id=?
Hibernate:
select
category0_.id as id0_0_,
category0_.name as name0_0_
from
Category category0_
where
category0_.id=?
Hibernate:
select
category0_.id as id0_0_,
category0_.name as name0_0_
from
Category category0_
where
category0_.id=?
Hibernate:
select
category0_.id as id0_0_,
category0_.name as name0_0_
from
Category category0_
where
category0_.id=?
1-t0
2-t1
3-t2
4-t3
5-t4
6-t5
7-t6
8-t7
9-t8
10-t9
我只是想取出topic而已,为什么多了那么多category?因为在实体类上,topic类的category属性上面的@ManyToOne的fetch默认是EAGER,所以取出topic的时候,会顺带着把topic关联的category出取出来。取category的过程就是,每取一个topic的时候,根据topic中的category_id取category表中按照id号取出相应的category对象。
这就是1+N问题,就是本来你该发一条sql语句,结果发了N条sql语句。
解决方案1:
如果上面的@MangToOne改为@ManyToOne(fetch=FetchType.LAZY)
结果就变成了:
Hibernate:
select
topic0_.id as id2_,
topic0_.category_id as category4_2_,
topic0_.createDate as createDate2_,
topic0_.title as title2_
from
Topic topic0_
1-t0
2-t1
3-t2
4-t3
5-t4
6-t5
7-t6
8-t7
9-t8
10-t9
只有你取category的时候他才会发出sql语句(按需而发)
解决方案2:
使用QBC面向对象的查询语句:
List<Topic> topics=(List<Topic>)session.createCriteria(Topic.class).list();
这是因为Criteria默认的是用了"表连接"的方式来取数据,两张表做连接来取,而不是另外单独发sql语句来取。
测试结果:
Hibernate:
select
this_.id as id2_0_,
this_.category_id as category4_2_0_,
this_.createDate as createDate2_0_,
this_.title as title2_0_
from
Topic this_
1-t0
2-t1
3-t2
4-t3
5-t4
6-t5
7-t6
8-t7
9-t8
10-t9
解决方案3:(这里@ManyToOne的fetch默认仍是EAGER)
@BatchSize(size=5)
首先在Category的类上面加@BatchSize(size=5):
package com.bjsxt.hibernate; import javax.persistence.Entity; import javax.persistence.GeneratedValue; import javax.persistence.Id; import org.hibernate.annotations.BatchSize; @Entity @BatchSize(size=5)//当你去加载Category的时候一次性加载5条 public class Category { private int id; private String name; @Id @GeneratedValue public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } }
测试:
@Test public void testOneAddNProblem1(){ Session session = sf.openSession(); session.beginTransaction(); List<Topic> topics=(List<Topic>)session.createQuery("from Topic").list(); for(Topic t:topics){ System.out.println(t.getId()+"-"+t.getTitle()); } session.getTransaction().commit(); session.close(); }测试结果:
Hibernate:
select
topic0_.id as id2_,
topic0_.category_id as category4_2_,
topic0_.createDate as createDate2_,
topic0_.title as title2_
from
Topic topic0_
Hibernate:
select
category0_.id as id0_0_,
category0_.name as name0_0_
from
Category category0_
where
category0_.id in (
?, ?, ?, ?, ?
)
Hibernate:
select
category0_.id as id0_0_,
category0_.name as name0_0_
from
Category category0_
where
category0_.id in (
?, ?, ?, ?, ?
)
1-t0
2-t1
3-t2
4-t3
5-t4
6-t5
7-t6
8-t7
9-t8
10-t9
发现关于Category的查询语句变成了两条,每条查询五个记录。(取出了10条)
这样就没有必要一条一条的去取了。要设成10只要发一个就可以了。
BatchSize只是提高了效率,少发多条sql语句,其实并没有完全解决1+N问题。
解决方案4:join fetch
@Test public void testOneAddNProblem2(){ Session session = sf.openSession(); session.beginTransaction(); List<Topic> topics=(List<Topic>)session.createQuery("from Topic t left join fetch t.category c").list(); for(Topic t:topics){ System.out.println(t.getId()+"-"+t.getTitle()); } session.getTransaction().commit(); session.close(); }测试结果:
Hibernate:
select
topic0_.id as id2_0_,
category1_.id as id0_1_,
topic0_.category_id as category4_2_0_,
topic0_.createDate as createDate2_0_,
topic0_.title as title2_0_,
category1_.name as name0_1_
from
Topic topic0_
left outer join
Category category1_
on topic0_.category_id=category1_.id
1-t0
2-t1
3-t2
4-t3
5-t4
6-t5
7-t6
8-t7
9-t8
10-t9
只发出了一条sql语句,原因是用了"Topic t left join fetch",做了一个外连接,做了一个外连接的话就没有必要每拿出一条来单独再去取了。
转载请注明出处:http://blog.csdn.net/acmman/article/details/43937551