N + 1 selects problem

假設有User，Country，Image等Entity，如下：

<<Table>>
IMAGE
ID <<PK>> USERID <<FK>> NAME TYPE DATA

<<Table>>
USER
ID <<PK>> COUNTRYID <<FK>> USERNAME

<<Table>>
COUNTRY
ID <<PK>> NAME CAPITAL

Java Class：

	package com.model;
	import java.io.Serializable;
	import javax.persistence.Entity;
	import javax.persistence.GeneratedValue;
	import javax.persistence.Id;
	@Entity
	public class Country implements Serializable{
	private static final long serialVersionUID = 8823558791465328019L;
	@Id
	@GeneratedValue(generator = "ID_GENERATOR")
	private Long id;
	private String name;
	private String capital;
	public Country(){}
	public Country(String name, String capital) {
	this.name = name;
	this.capital = capital;
	}
	public Long getId() {
	return id;
	}
	public void setId(Long id) {
	this.id = id;
	}
	public String getName() {
	return name;
	}
	public void setName(String name) {
	this.name = name;
	}
	public String getCapital() {
	return capital;
	}
	public void setCapital(String capital) {
	this.capital = capital;
	}
	}

view raw Country.java hosted with ❤ by GitHub

	package com.model;
	import java.io.Serializable;
	import javax.persistence.CascadeType;
	import javax.persistence.Entity;
	import javax.persistence.FetchType;
	import javax.persistence.GeneratedValue;
	import javax.persistence.Id;
	import javax.persistence.JoinColumn;
	import javax.persistence.ManyToOne;
	import javax.persistence.Table;
	@Entity
	@Table(name = "IMAGE")
	public class Image implements Serializable {
	private static final long serialVersionUID = -4861363108774020175L;
	@Id
	@GeneratedValue(generator = "ID_GENERATOR")
	private Long id;
	private String name;
	private String type;
	public Image(){}
	public Image(String name, String type) {
	this.name = name;
	this.type = type;
	}
	@ManyToOne(fetch = FetchType.LAZY, cascade=CascadeType.PERSIST)
	@JoinColumn
	private User user;
	public Long getId() {
	return id;
	}
	public void setId(Long id) {
	this.id = id;
	}
	public String getName() {
	return name;
	}
	public void setName(String name) {
	this.name = name;
	}
	public String getType() {
	return type;
	}
	public void setType(String type) {
	this.type = type;
	}
	public User getUser() {
	return user;
	}
	public void setUser(User user) {
	this.user = user;
	}
	}

view raw Image.java hosted with ❤ by GitHub

	package com.model;
	import java.util.Date;
	import java.util.List;
	import javax.persistence.CascadeType;
	import javax.persistence.Column;
	import javax.persistence.Entity;
	import javax.persistence.FetchType;
	import javax.persistence.GeneratedValue;
	import javax.persistence.Id;
	import javax.persistence.ManyToOne;
	import javax.persistence.OneToMany;
	import javax.persistence.Table;
	import javax.persistence.Temporal;
	import javax.persistence.TemporalType;
	@Entity
	@Table(name = "USER")
	//@org.hibernate.annotations.BatchSize(size = 10)
	public class User {
	@Id
	@GeneratedValue(generator = "ID_GENERATOR")
	private Long id;
	private String username;
	@Temporal(TemporalType.TIMESTAMP)
	@Column(updatable = false)
	@org.hibernate.annotations.CreationTimestamp
	private Date createdOn;
	@ManyToOne(fetch = FetchType.LAZY)
	private Country country;
	@OneToMany(fetch = FetchType.LAZY, cascade=CascadeType.PERSIST, mappedBy="user")
	private List<Image> images;
	public User() {
	}
	public Long getId() {
	return id;
	}
	public void setId(Long id) {
	this.id = id;
	}
	public String getUsername() {
	return username;
	}
	public void setUsername(String username) {
	this.username = username;
	}
	public Country getCountry() {
	return country;
	}
	public void setCountry(Country country) {
	this.country = country;
	}
	public List<Image> getImages() {
	return images;
	}
	public void setImages(List<Image> images) {
	this.images = images;
	}
	public Date getCreatedOn() {
	return createdOn;
	}
	public void setCreatedOn(Date createdOn) {
	this.createdOn = createdOn;
	}
	}

view raw User.java hosted with ❤ by GitHub

如果透過以下的方式存取：

	List<User> users = em.createQuery("select u from User u").getResultList();
	// select * from USER ---------- (1)
	for (User user : users) {
	System.out.println(user.getCountry().getName());
	// select * from COUNTRY where ID = ? ----------- (2)
	}

view raw NPluseOneProblem.java hosted with ❤ by GitHub

那麼會先產生一個SELECT來load USER entity，
之後要iterates每個user以取得country，
因此總共會產生一個(來自query User)，再加上N(視你的User總共有多少個)個SELECT 。
如果已經事先知道一定會取的每個user中的country，那麼這種做法是很沒有效率的。
另外在Lazily loaded collections也是會發生同樣的問題：

	List<User> users = em.createQuery("select u from User u").getResultList();
	// select * from USER
	for (User user : users) {
	System.out.println(user.getImages().size());
	// select * from IMAGE where USER_ID = ?
	}

view raw NPluseOneProblemWithCollection.java hosted with ❤ by GitHub