问答首页 → Java企业应用
0 0

hibernate3.3，多对多关系，left join fetch查询后，依然会N+1问题25

   有三张表，映射关系如下

package com.op.crm.persist.model;

import java.util.HashSet;
import java.util.Set;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.JoinTable;
import javax.persistence.ManyToMany;
import javax.persistence.Table;

import org.hibernate.annotations.Fetch;
import org.hibernate.annotations.FetchMode;

/**
 * Action entity. @author MyEclipse Persistence Tools
 */
@Entity
@Table(name = "NFK_ACTION", schema = "NFKORA")
public class Action implements java.io.Serializable {

	// Fields

	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;
	private String actionId;
	private String actionName;
	private String action;
	private String insertUser;
	private String insertTime;
	private String updateUser;
	private String updateTime;
	private Set<Role> roles = new HashSet<Role>(0);

	// Constructors

	/** default constructor */
	public Action() {
	}

	/** minimal constructor */
	public Action(String actionId, String actionName, String action,
			String insertUser, String insertTime) {
		this.actionId = actionId;
		this.actionName = actionName;
		this.action = action;
		this.insertUser = insertUser;
		this.insertTime = insertTime;
	}

	/** full constructor */
	public Action(String actionId, String actionName, String action,
			String insertUser, String insertTime, String updateUser,
			String updateTime,Set<Role> roles) {
		this.actionId = actionId;
		this.actionName = actionName;
		this.action = action;
		this.insertUser = insertUser;
		this.insertTime = insertTime;
		this.updateUser = updateUser;
		this.updateTime = updateTime;
		this.roles = roles;
	}

	// Property accessors
	@Id
	@Column(name = "ACTION_ID", unique = true, nullable = false, length = 20)
	public String getActionId() {
		return this.actionId;
	}

	public void setActionId(String actionId) {
		this.actionId = actionId;
	}

	@Column(name = "ACTION_NAME", nullable = false, length = 50)
	public String getActionName() {
		return this.actionName;
	}

	public void setActionName(String actionName) {
		this.actionName = actionName;
	}

	@Column(name = "ACTION", nullable = false, length = 300)
	public String getAction() {
		return this.action;
	}

	public void setAction(String action) {
		this.action = action;
	}

	@Column(name = "INSERT_USER", nullable = false, length = 20)
	public String getInsertUser() {
		return this.insertUser;
	}

	public void setInsertUser(String insertUser) {
		this.insertUser = insertUser;
	}

	@Column(name = "INSERT_TIME", nullable = false, length = 19)
	public String getInsertTime() {
		return this.insertTime;
	}

	public void setInsertTime(String insertTime) {
		this.insertTime = insertTime;
	}

	@Column(name = "UPDATE_USER", length = 20)
	public String getUpdateUser() {
		return this.updateUser;
	}

	public void setUpdateUser(String updateUser) {
		this.updateUser = updateUser;
	}

	@Column(name = "UPDATE_TIME", length = 19)
	public String getUpdateTime() {
		return this.updateTime;
	}

	public void setUpdateTime(String updateTime) {
		this.updateTime = updateTime;
	}

	@ManyToMany(mappedBy="actions")
	public Set<Role> getRoles() {
		return roles;
	}

	public void setRoles(Set<Role> roles) {
		this.roles = roles;
	}

}

 

package com.op.crm.persist.model;

import java.util.HashSet;
import java.util.Set;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.JoinTable;
import javax.persistence.ManyToMany;
import javax.persistence.Table;

import org.hibernate.annotations.Fetch;
import org.hibernate.annotations.FetchMode;

/**
 * Role entity. @author MyEclipse Persistence Tools
 */
@Entity
@Table(name = "NFK_ROLE", schema = "NFKORA")
public class Role implements java.io.Serializable {

	// Fields

	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;
	private String roleId;
	private String roleName;
	private String insertUser;
	private String insertTime;
	private String updateUser;
	private String updateTime;
	private Set<Action> actions = new HashSet<Action>(0);
	private Set<User> users = new HashSet<User>(0);

	// Constructors

	/** default constructor */
	public Role() {
	}

	/** minimal constructor */
	public Role(String roleId, String roleName, String insertUser,
			String insertTime) {
		this.roleId = roleId;
		this.roleName = roleName;
		this.insertUser = insertUser;
		this.insertTime = insertTime;
	}

	/** full constructor */
	public Role(String roleId, String roleName, String insertUser,
			String insertTime, String updateUser, String updateTime,
			Set<Action> actions, Set<User> users) {
		this.roleId = roleId;
		this.roleName = roleName;
		this.insertUser = insertUser;
		this.insertTime = insertTime;
		this.updateUser = updateUser;
		this.updateTime = updateTime;
		this.actions = actions;
		this.users = users;
	}

	// Property accessors
	@Id
	@Column(name = "ROLE_ID", unique = true, nullable = false, length = 20)
	public String getRoleId() {
		return this.roleId;
	}

	public void setRoleId(String roleId) {
		this.roleId = roleId;
	}

	@Column(name = "ROLE_NAME", nullable = false, length = 50)
	public String getRoleName() {
		return this.roleName;
	}

	public void setRoleName(String roleName) {
		this.roleName = roleName;
	}

	@Column(name = "INSERT_USER", nullable = false, length = 20)
	public String getInsertUser() {
		return this.insertUser;
	}

	public void setInsertUser(String insertUser) {
		this.insertUser = insertUser;
	}

	@Column(name = "INSERT_TIME", nullable = false, length = 19)
	public String getInsertTime() {
		return this.insertTime;
	}

	public void setInsertTime(String insertTime) {
		this.insertTime = insertTime;
	}

	@Column(name = "UPDATE_USER", length = 20)
	public String getUpdateUser() {
		return this.updateUser;
	}

	public void setUpdateUser(String updateUser) {
		this.updateUser = updateUser;
	}

	@Column(name = "UPDATE_TIME", length = 19)
	public String getUpdateTime() {
		return this.updateTime;
	}

	public void setUpdateTime(String updateTime) {
		this.updateTime = updateTime;
	}

	@ManyToMany
	@JoinTable(name="NFK_ROLE_ACTION",
				joinColumns=@JoinColumn(name="ROLE_ID"),
				inverseJoinColumns=@JoinColumn(name="ACTION_ID"))
	public Set<Action> getActions() {
		return actions;
	}

	public void setActions(Set<Action> actions) {
		this.actions = actions;
	}

	@ManyToMany(mappedBy="roles")
	public Set<User> getUsers() {
		return users;
	}

	public void setUsers(Set<User> users) {
		this.users = users;
	}

}

 

package com.op.crm.persist.model;

import java.util.HashSet;
import java.util.Set;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.JoinTable;
import javax.persistence.ManyToMany;
import javax.persistence.Table;
import javax.persistence.Transient;

import org.hibernate.annotations.Fetch;
import org.hibernate.annotations.FetchMode;

/**
 * User entity. @author MyEclipse Persistence Tools
 */
@Entity
@Table(name = "NFK_USER", schema = "NFKORA")
public class User implements java.io.Serializable {

	// Fields

	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;
	private String userId;
	private String password;
	private String name;
	private String sex;
	private String mobile;
	private String qq;
	private String email;
	private String address;
	private String insertUser;
	private String insertTime;
	private String updateUser;
	private String updateTime;
	private Set<Role> roles = new HashSet<Role>(0);
	private Set<Group> groups = new HashSet<Group>(0);
	private String rePassword;

	// Constructors

	/** default constructor */
	public User() {
	}

	/** minimal constructor */
	public User(String userId, String password, String name, String sex,
			String mobile, String insertUser, String insertTime) {
		this.userId = userId;
		this.password = password;
		this.name = name;
		this.sex = sex;
		this.mobile = mobile;
		this.insertUser = insertUser;
		this.insertTime = insertTime;
	}

	/** full constructor */
	public User(String userId, String password, String name, String sex,
			String mobile, String qq, String email, String address,
			String insertUser, String insertTime, String updateUser,
			String updateTime, Set<Role> roles,
			Set<Group> groups) {
		this.userId = userId;
		this.password = password;
		this.name = name;
		this.sex = sex;
		this.mobile = mobile;
		this.qq = qq;
		this.email = email;
		this.address = address;
		this.insertUser = insertUser;
		this.insertTime = insertTime;
		this.updateUser = updateUser;
		this.updateTime = updateTime;
		this.roles = roles;
		this.groups = groups;
	}

	// Property accessors
	@Id
	@Column(name = "USER_ID", unique = true, nullable = false, length = 20)
	public String getUserId() {
		return this.userId;
	}

	public void setUserId(String userId) {
		this.userId = userId;
	}

	@Column(name = "PASSWORD", nullable = false, length = 300)
	public String getPassword() {
		return this.password;
	}

	public void setPassword(String password) {
		this.password = password;
	}

	@Column(name = "NAME", nullable = false, length = 30)
	public String getName() {
		return this.name;
	}

	public void setName(String name) {
		this.name = name;
	}

	@Column(name = "SEX", nullable = false, length = 1)
	public String getSex() {
		return this.sex;
	}

	public void setSex(String sex) {
		this.sex = sex;
	}

	@Column(name = "MOBILE", nullable = false, length = 20)
	public String getMobile() {
		return this.mobile;
	}

	public void setMobile(String mobile) {
		this.mobile = mobile;
	}

	@Column(name = "QQ", length = 20)
	public String getQq() {
		return this.qq;
	}

	public void setQq(String qq) {
		this.qq = qq;
	}

	@Column(name = "EMAIL", length = 100)
	public String getEmail() {
		return this.email;
	}

	public void setEmail(String email) {
		this.email = email;
	}

	@Column(name = "ADDRESS", length = 300)
	public String getAddress() {
		return this.address;
	}

	public void setAddress(String address) {
		this.address = address;
	}

	@Column(name = "INSERT_USER", nullable = false, length = 20)
	public String getInsertUser() {
		return this.insertUser;
	}

	public void setInsertUser(String insertUser) {
		this.insertUser = insertUser;
	}

	@Column(name = "INSERT_TIME", nullable = false, length = 19)
	public String getInsertTime() {
		return this.insertTime;
	}

	public void setInsertTime(String insertTime) {
		this.insertTime = insertTime;
	}

	@Column(name = "UPDATE_USER", length = 20)
	public String getUpdateUser() {
		return this.updateUser;
	}

	public void setUpdateUser(String updateUser) {
		this.updateUser = updateUser;
	}

	@Column(name = "UPDATE_TIME", length = 19)
	public String getUpdateTime() {
		return this.updateTime;
	}

	public void setUpdateTime(String updateTime) {
		this.updateTime = updateTime;
	}

	@ManyToMany
	@JoinTable(name="NFK_USER_ROLE",
				joinColumns=@JoinColumn(name="USER_ID"),
				inverseJoinColumns=@JoinColumn(name="ROLE_ID"))
	public Set<Role> getRoles() {
		return roles;
	}

	public void setRoles(Set<Role> roles) {
		this.roles = roles;
	}

	@Transient
	public Set<Group> getGroups() {
		return groups;
	}

	public void setGroups(Set<Group> groups) {
		this.groups = groups;
	}

	@Transient
	public String getRePassword() {
		return rePassword;
	}

	public void setRePassword(String rePassword) {
		this.rePassword = rePassword;
	}

	

}

 然后我想查出所有的Role，并且，每个Role下都有对应的Users和Actions对象

于是我这样查询

public List<Role> selectRoles() throws Exception {
		List<Role> results = (List<Role>)this.getHibernateTemplate().find("select distinct r from Role r left outer join fetch r.actions left outer join fetch r.users");
		return results;
	}

 我把hibernate的show_sql打开了，观察他生产的sql语句

Hibernate: select distinct role0_.ROLE_ID as ROLE1_0_0_, action2_.ACTION_ID as ACTION1_1_1_, user4_.USER_ID as USER1_2_2_, role0_.INSERT_TIME as INSERT2_0_0_, role0_.INSERT_USER as INSERT3_0_0_, role0_.ROLE_NAME as ROLE4_0_0_, role0_.UPDATE_TIME as UPDATE5_0_0_, role0_.UPDATE_USER as UPDATE6_0_0_, action2_.ACTION as ACTION1_1_, action2_.ACTION_NAME as ACTION3_1_1_, action2_.INSERT_TIME as INSERT4_1_1_, action2_.INSERT_USER as INSERT5_1_1_, action2_.UPDATE_TIME as UPDATE6_1_1_, action2_.UPDATE_USER as UPDATE7_1_1_, actions1_.ROLE_ID as ROLE1_0__, actions1_.ACTION_ID as ACTION2_0__, user4_.ADDRESS as ADDRESS2_2_, user4_.EMAIL as EMAIL2_2_, user4_.INSERT_TIME as INSERT4_2_2_, user4_.INSERT_USER as INSERT5_2_2_, user4_.MOBILE as MOBILE2_2_, user4_.NAME as NAME2_2_, user4_.PASSWORD as PASSWORD2_2_, user4_.QQ as QQ2_2_, user4_.SEX as SEX2_2_, user4_.UPDATE_TIME as UPDATE11_2_2_, user4_.UPDATE_USER as UPDATE12_2_2_, users3_.ROLE_ID as ROLE2_1__, users3_.USER_ID as USER1_1__ from NFKORA.NFK_ROLE role0_ left outer join NFK_ROLE_ACTION actions1_ on role0_.ROLE_ID=actions1_.ROLE_ID left outer join NFKORA.NFK_ACTION action2_ on actions1_.ACTION_ID=action2_.ACTION_ID left outer join NFK_USER_ROLE users3_ on role0_.ROLE_ID=users3_.ROLE_ID left outer join NFKORA.NFK_USER user4_ on users3_.USER_ID=user4_.USER_ID

 说明left join fetch起作用了

但是，当我访问Role对象中的actions时，有产生了四条sql语句

Hibernate: select roles0_.ACTION_ID as ACTION2_1_, roles0_.ROLE_ID as ROLE1_1_, role1_.ROLE_ID as ROLE1_0_0_, role1_.INSERT_TIME as INSERT2_0_0_, role1_.INSERT_USER as INSERT3_0_0_, role1_.ROLE_NAME as ROLE4_0_0_, role1_.UPDATE_TIME as UPDATE5_0_0_, role1_.UPDATE_USER as UPDATE6_0_0_ from NFK_ROLE_ACTION roles0_ left outer join NFKORA.NFK_ROLE role1_ on roles0_.ROLE_ID=role1_.ROLE_ID where roles0_.ACTION_ID=?
Hibernate: select roles0_.ACTION_ID as ACTION2_1_, roles0_.ROLE_ID as ROLE1_1_, role1_.ROLE_ID as ROLE1_0_0_, role1_.INSERT_TIME as INSERT2_0_0_, role1_.INSERT_USER as INSERT3_0_0_, role1_.ROLE_NAME as ROLE4_0_0_, role1_.UPDATE_TIME as UPDATE5_0_0_, role1_.UPDATE_USER as UPDATE6_0_0_ from NFK_ROLE_ACTION roles0_ left outer join NFKORA.NFK_ROLE role1_ on roles0_.ROLE_ID=role1_.ROLE_ID where roles0_.ACTION_ID=?
Hibernate: select roles0_.USER_ID as USER1_1_, roles0_.ROLE_ID as ROLE2_1_, role1_.ROLE_ID as ROLE1_0_0_, role1_.INSERT_TIME as INSERT2_0_0_, role1_.INSERT_USER as INSERT3_0_0_, role1_.ROLE_NAME as ROLE4_0_0_, role1_.UPDATE_TIME as UPDATE5_0_0_, role1_.UPDATE_USER as UPDATE6_0_0_ from NFK_USER_ROLE roles0_ left outer join NFKORA.NFK_ROLE role1_ on roles0_.ROLE_ID=role1_.ROLE_ID where roles0_.USER_ID=?
Hibernate: select roles0_.USER_ID as USER1_1_, roles0_.ROLE_ID as ROLE2_1_, role1_.ROLE_ID as ROLE1_0_0_, role1_.INSERT_TIME as INSERT2_0_0_, role1_.INSERT_USER as INSERT3_0_0_, role1_.ROLE_NAME as ROLE4_0_0_, role1_.UPDATE_TIME as UPDATE5_0_0_, role1_.UPDATE_USER as UPDATE6_0_0_ from NFK_USER_ROLE roles0_ left outer join NFKORA.NFK_ROLE role1_ on roles0_.ROLE_ID=role1_.ROLE_ID where roles0_.USER_ID=?

 那说明还是有N+1问题，有什么办法解决呀，求解

Hibernatefetchn+1 
2013年6月05日 14:04

1个答案 按时间排序 按投票排序

0 0

你这个性能不会好的，建议直接启用 ManyToMany映射 而是单独查，并且考虑加缓存

看了下你这个是权限相关的 可以参考我的
https://github.com/zhangkaitao/es/tree/master/web/src/main/java/com/sishuok/es/sys

2013年6月07日 07:18

相关推荐

    hibernate的n+1问题.docx

    然而，使用不当可能会导致性能瓶颈，其中最典型的就是“N+1次SELECT查询问题”。本文将深入探讨这个问题，以及如何通过优化检索策略来解决它。 N+1次SELECT查询问题源于Hibernate的默认行为。当从数据库中加载一个...

    Hibernate Fetch 的作用

    例如，在上面的代码片段中，通过`left outer join fetch parent.childs`语句，我们告诉Hibernate在加载`Parent`实体时，也一并加载所有相关的`Child`实体。这样，即使`Parent`实体的`childs`属性被标记为`lazy="true...

    hibernate关联查询

    同时，为了避免N+1查询问题，可以使用`fetch`或`join fetch`来预加载关联的数据。 ### 6. 性能考虑 在实际应用中，我们需要根据业务场景合理选择关联查询的方式，避免大数据量下的性能问题。例如，对于不常访问的...

    Struts2+Hibernate实现一对多联表查询

    总的来说，这个项目展示了如何结合Struts2和Hibernate框架处理Web应用中的数据操作，特别是涉及一对多关联关系的查询。开发者需要理解MVC架构，掌握Hibernate的实体映射和关联关系，以及Struts2的Action、Service、...

    Hibernate-基础联表模板

    `@ManyToMany`注解用于定义这种关系，联表查询时，可能需要使用`JOIN FETCH`来一次性获取所有关联数据，以避免N+1问题。 8. **Fetch策略** Hibernate提供了EAGER和LAZY两种加载策略。EAGER策略会立即加载关联数据...

    hibernate 中 fetch=FetchType.LAZY 懒加载失败处理方法

    Hibernate 中 fetch=FetchType.LAZY 懒加载失败处理方法是 Hibernate 框架中的一种常见问题。当我们在 Hibernate 中使用懒加载特性时，可能会遇到 LazyInitializationException 异常，这是因为 Hibernate 的 ...

    hibernate性能优化.doc

    在上面的代码中，我们使用了 HQL、Criteria 和 Hibernate Annotation 中的 lazy、batchSize 和 join fetch 等方法来优化查询，避免了 1+n 问题。 Hibernate 性能优化需要我们注意批量处理和 1+n 问题，并使用相应的...

    hsql查询语法

    HSQL还支持关联和连接，用于查询多个类之间的关系。例如： from Cat as cat inner join cat.mate as mate left outer join cat.kittens as kitten 这个语句将Cat类的实例与其mate和kittens之间的关系进行关联和...

    Hibernate问题解决

    在这个查询中，`JOIN FETCH`用于同时获取用户信息和角色信息，从而避免了多次数据库访问的问题。 - **第二种解决办法：** 如果不需要使用`LEFT JOIN`或`RIGHT JOIN`，可以直接使用嵌套的`SELECT`查询。例如： ``...

    HQL Hibernate查询语言

    HQL Hibernate 查询语言 HQL（Hibernate Query Language）是 Hibernate 中的一种强大的查询语言，它看起来很像 SQL，但是不要被语法结构上的相似所迷惑，HQL 是非常有意识的被设计为完全面向对象的查询，它可以理解...

    Hibernate_HQL大全

    通过使用FETCH JOIN，可以一次性加载关联对象或集合，避免了多次查询数据库的问题，提高了数据加载效率。 6. **SELECT子句**：HQL的SELECT子句用于指定查询结果中包含的对象或属性。例如，“SELECT mate FROM Cat”...

    Hibernate的学习笔记

    - 一对多关联：可以使用迫切左外连接查询，避免N+1问题。例如，通过`join fetch`进行集合的预加载。 4. **HQL查询**： - 内联接：用于筛选满足特定条件的关联对象，如`From Item i join i.bids b where ...`。 -...

    Hibernate查询详解

    这意味着，对于每条记录，iterator()可能触发额外的SQL查询，总计N+1次（N为结果集大小）。虽然增加了数据库交互次数，但在处理大数据集时能有效控制内存使用，避免内存溢出风险。 ### HQL与SQL的对比 #### 面向...

    Hibernate进行数据查询

    Hibernate允许在查询中处理一对多、多对一、多对多等关联关系。例如，如果User类有一个Address类的属性，可以通过以下方式查询用户及其地址： ```java List&lt;User&gt; users = session.createQuery("from User u left...

    hibernate的使用心得

    在Hibernate 3.2.3之后的版本，对于单个实体或组件属性的关联，仍然可以用英文点号隐式连接，但集合属性（如1-N、N-N关联）必须使用`join`显示连接。对于集合属性的延迟加载问题，可以通过在映射文件中设置`lazy=...

    hibernate延迟加载技术详细解

    通常，在多对多或者一对多的关系中，延迟加载能够避免 N+1 查询问题。本文将详细探讨 Hibernate 的各种延迟加载策略及其应用场景。 #### 二、Fetching 策略 Fetching 策略定义了 Hibernate 在执行查询时如何获取...

    Hibernate HQL.doc

    Hibernate HQL，全称为Hibernate Query Language，是Hibernate框架中用于对象关系映射（ORM）的查询语言。HQL的设计目标是提供一种面向对象的查询方式，使得开发者能够以类和对象的角度来操作数据库，而不是直接使用...

    Hibernate查询语言

    【Hibernate查询语言】是ORM框架Hibernate中用于操作数据库的重要组件，它提供了面向对象的查询机制，使得开发者可以更方便地处理数据库数据。本篇主要讨论批量插入和批量更新，以及HQL（Hibernate Query Language）...

    hibernate的hql语句

    4. **关联的处理**：HQL支持处理一对一、一对多、多对一和多对多的关联。例如，`from Cat as cat left join fetch cat.kittens as kittens`会加载每只猫及其所有幼崽，即使幼崽数据未被立即使用，也能通过父对象访问...

    Hibernate查询语言（HQL）

    - **支持高级特性**：HQL 支持复杂的查询操作，如联接、分组、排序等，并且可以很好地处理一对多和多对多的关系。 - **语法简洁**：HQL 的语法相对简洁，易于理解和编写。 #### 三、HQL 基础查询 - **基本的 ...

Magicbox
Ask-but
我的问答 FAQ | 勋章

相关推荐

已解决问题

未解决问题

排行榜

Global site tag (gtag.js) - Google Analytics