问答首页 → 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

相关推荐

    react16+redux+sage+fetch+route4.0+antd+webpack 框架

    基于react16+redux+sage+fetch+route4.0+antd+webpack 框架，使用npm install,npm run start运行，框架企业级应用。

    Hibernate+中文文档

    7.4.1. 一对多（one to many) / 多对一（many to one） 7.4.2. 一对一（one to one） 7.5. 使用连接表的双向关联（Bidirectional associations with join tables） 7.5.1. 一对多（one to many） /多对一（ many ...

    Hibernate 1+N问题详解

    Hibernate的两个类设置了manyToOne之后，在查询的时候， 由于N 对1的一方默认的fetch=FetchType.EAGER,所以会 把被关联的对象一起取出来

    Hibernate Fetch 的作用

    正确理解hibernate fetch 的作用

    Hibernate_3.2.0_符合Java习惯的关系数据库持久化

    HIBERNATE - 符合Java习惯的关系数据库持久化 Hibernate参考文档 3.2 -------------------------------------------------------------------------------- 目录 前言 1. 翻译说明 2. 版权声明 1. Hibernate...

    Hibernate3+中文参考文档

    7.5.1. 一对多（one to many） /多对一（ many to one） 7.5.2. 一对一（one to one） 7.5.3. 多对多（many to many） 8. 组件（Component）映射 8.1. 依赖对象（Dependent objects） 8.2. 在集合中出现的依赖对象 ...

    hibernate3.2中文文档（chm格式）

    HIBERNATE - 符合Java习惯的关系数据库持久化 Hibernate参考文档 3.2 -------------------------------------------------------------------------------- 目录 前言 1. 翻译说明 2. 版权声明 1. Hibernate...

    HibernateAPI中文版.chm

    HIBERNATE - 符合Java习惯的关系数据库持久化 Hibernate参考文档 3.2 -------------------------------------------------------------------------------- 目录 前言 1. 翻译说明 2. 版权声明 1. Hibernate...

    精通 Hibernate：Java 对象持久化技术详解(第2版).part2

    　7.1 建立多对一的单向关联关系 　7.1.1 元素的not-null属性 　7.1.2 级联保存和更新 　7.2 映射一对多双向关联关系 　7.2.1 元素的inverse属性 　7.2.2 级联删除 　7.2.3 父子关系 　7.3 映射一对多双向自身关联...

    Hibernate中文详细学习文档

    符合Java习惯的关系数据库持久化 前言 1. 翻译说明 2. 版权声明 1. Hibernate入门 1.1. 前言 1.2. 第一部分 － 第一个Hibernate应用程序 1.2.1. 第一个class 1.2.2. 映射文件 1.2.3. Hibernate配置 1.2.4. ...

    hibernate总结

    i. 如果不加fetch关键字，则hibernate不会抓取关系属性，但会遍历关系属性所对应的表 ii. 不加fetch关键字时，select 要指定返回的对象，否则它要返回数组 iii. 条件：持久化类之间有关系属性映射 Hibernate级联...

    Hibernate 中文 html 帮助文档

    7.5.1. 一对多（one to many） /多对一（ many to one） 7.5.2. 一对一（one to one） 7.5.3. 多对多（many to many） 7.6. 更复杂的关联映射 8. 组件（Component）映射 8.1. 依赖对象（Dependent objects） 8.2. 在...

    最全Hibernate 参考文档

    1. 在Tomcat中快速上手 1.1. 开始Hibernate之旅 1.2. 第一个持久化类 1.3. 映射cat 1.4. 与Cat同乐 1.5. 结语 2. 架构(Architecture) 2.1. 概况(Overview) 2.2. 实例状态 2.3. JMX整合 2.4. 对JCA的支持 3. 配置 ...

    hibernate 体系结构与配置 参考文档(html)

    1. Hibernate入门 1.1. 前言 1.2. 第一部分 － 第一个Hibernate应用程序 1.2.1. 第一个class 1.2.2. 映射文件 1.2.3. Hibernate配置 1.2.4. 用Ant构建 1.2.5. 启动和辅助类 1.2.6. 加载并存储对象 1.3. 第...

    Fetch+Promise教程

    该文档是在学习和工作中的总结，内容全面，讲解详细。学习ES6和前端数据交互的必备知识。仅供参考，如有雷同，纯属巧合。

    精通 Hibernate：Java 对象持久化技术详解(第2版).part1.rar

    　7.1 建立多对一的单向关联关系 　7.1.1 元素的not-null属性 　7.1.2 级联保存和更新 　7.2 映射一对多双向关联关系 　7.2.1 元素的inverse属性 　7.2.2 级联删除 　7.2.3 父子关系 　7.3 映射一对多双向自身关联...

    Hibernate教程

    Hibernate参考文档 目录 前言 1. 翻译说明 2. 版权声明 1. 在Tomcat中快速上手 1.1. 开始Hibernate之旅 1.2. 第一个持久化类 1.3. 映射cat 1.4. 与Cat同乐 1.5. 结语 2. Hibernate入门 2.1. 前言 2.2. 第...

    Hibernate注释大全收藏

    Hibernate注释大全收藏 声明实体Bean @Entity public class Flight implements Serializable { Long id; @Id public Long getId() { return id; } public void setId(Long id) { this.id = id; } } @Entity ...

    精通 Hibernate：Java 对象持久化技术详解(第2版).part4

    　7.1 建立多对一的单向关联关系 　7.1.1 元素的not-null属性 　7.1.2 级联保存和更新 　7.2 映射一对多双向关联关系 　7.2.1 元素的inverse属性 　7.2.2 级联删除 　7.2.3 父子关系 　7.3 映射一对多双向自身关联...

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

相关推荐

已解决问题

未解决问题

排行榜

Global site tag (gtag.js) - Google Analytics