关闭

[Criteria]not in子查询,查询不在中间表的数据

标签: Criterianot inwhere子查询中间表无实体类
306人阅读 评论(1) 收藏 举报
分类:

为了查询不在某个角色的所有用户,操碎了心!当然,主要是因为对hibernate不熟练。

其实sql语句就是很简单的一句,如下:

select t.id, t.account from t_sm_user t where t.id not in (
       select r.user_id from t_sm_user_role r where r.role_id = ?
)

但是,总有那么些有的没有的约束不让你用sql语句简单粗暴的实现啊,比如某组长的强迫症啊(无限怨念中...)

所以本菜鸟只好苦逼的上网到处找资料,毕竟菜鸟求谅解。

经过无数次的刷新页面和无数次的控制台报错,本屌不负众望,成功用Criteria实现了not in 子查询。

下面放代码:


User类:

package cn.ffcs.system.domain;

// default package

import static javax.persistence.GenerationType.SEQUENCE;

import java.util.Collection;
import java.util.Date;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.JoinTable;
import javax.persistence.ManyToMany;
import javax.persistence.ManyToOne;
import javax.persistence.SequenceGenerator;
import javax.persistence.Table;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;
import javax.persistence.Transient;

import org.codehaus.jackson.annotate.JsonIgnore;
import org.codehaus.jackson.annotate.JsonIgnoreProperties;
import org.springframework.security.core.GrantedAuthority;
import org.springframework.security.core.authority.GrantedAuthorityImpl;
import org.springframework.security.core.userdetails.UserDetails;

import cn.ffcs.system.common.ReflectIgnore;



/**
 * User entity. @author MyEclipse Persistence Tools
 */
@SuppressWarnings("serial")
@Entity
@Table(name="t_sm_user")
@JsonIgnoreProperties(value={"hibernateLazyInitializer"})
public class User  implements java.io.Serializable, UserDetails {


    // Fields    

     private Integer id;
     private String account;              //账号
     private Set<Role> roles = new HashSet<Role>(0);
    // Constructors

    /** default constructor */

   
    @SequenceGenerator(name="seq_t_sm_user_id", sequenceName="seq_t_sm_user_id",allocationSize=10)
 	@GeneratedValue (generator="seq_t_sm_user_id")
    @Id
    public Integer getId() {
        return this.id;
    }
    
    public void setId(Integer id) {
        this.id = id;
    }
    
    @Column(name="ACCOUNT", length=20)

    public String getAccount() {
        return this.account;
    }
    
    public void setAccount(String account) {
        this.account = account;
    }
    @ManyToMany(cascade = CascadeType.PERSIST, fetch = FetchType.LAZY)
	@JoinTable(name = "t_sm_user_role",
		joinColumns = @JoinColumn(name = "USER_ID", referencedColumnName = "id"),
		inverseJoinColumns = @JoinColumn(name = "ROLE_ID", referencedColumnName = "id")
	)
    @JsonIgnore
	public Set<Role> getRoles() {
		return roles;
	}

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

}


Role类:

package cn.ffcs.system.domain;

import static javax.persistence.GenerationType.SEQUENCE;
import java.io.IOException;
import java.util.Date;
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.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinTable;
import javax.persistence.ManyToMany;
import javax.persistence.SequenceGenerator;
import javax.persistence.Table;
import javax.persistence.JoinColumn;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;
import javax.persistence.Transient;

import org.codehaus.jackson.JsonGenerationException;
import org.codehaus.jackson.annotate.JsonAutoDetect;
import org.codehaus.jackson.annotate.JsonIgnore;
import org.codehaus.jackson.annotate.JsonIgnoreProperties;
import org.codehaus.jackson.map.JsonMappingException;
import org.codehaus.jackson.map.ObjectMapper;
import org.hibernate.annotations.GenericGenerator;

import cn.ffcs.system.common.ReflectIgnore;


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


    // Fields    

     /**
	 * 
	 */
	private static final long serialVersionUID = 5586297524648936632L;
	private Integer id;
     private String name;
     <span style="color:#ff0000;">private Set<User> users = new HashSet<User>(0);</span>
     
     
    // Constructors
    //.......
<span style="white-space:pre">	</span>
    @SequenceGenerator(name="seq_t_sm_role_id", sequenceName="seq_t_sm_role_id",allocationSize=10)
  	@GeneratedValue (generator="seq_t_sm_role_id")
    @Id
    public Integer getId() {
        return this.id;
    }
    
    public void setId(Integer id) {
        this.id = id;
    }
    
    @Column(name="NAME", length=20)

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

<span style="color:#ff0000;">	@ManyToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY, mappedBy = "roles")
	@JsonIgnore
	public Set<User> getUsers() {
		return users;
	}


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



查询关键部分代码如下:

DetachedCriteria query = DetachedCriteria.forClass(Role.class);
			<span style="color:#ff0000;">query.createAlias("users", "users")</span>
			.add(Restrictions.eq("id", roleId))
			<span style="color:#ff0000;">.setProjection(Property.forName("users.id"));</span>
			c.add(Property.forName("id").notIn(query));

因为中间表无实体类,所以要注意红色高亮部分;


hibernate打印语句:

select this_.id as id7_0_, this_.ACCOUNT as ACCOUNT7_0_
  from t_sm_user this_
 where this_.id not in
       (select users1_.id as y0_
          from t_sm_role this_
         inner join t_sm_user_role users3_ on this_.id = users3_.ROLE_ID
         inner join t_sm_user users1_ on users3_.USER_ID = users1_.id
         where this_.id = ?)




0
0

猜你在找
【直播】机器学习&数据挖掘7周实训--韦玮
【套餐】系统集成项目管理工程师顺利通关--徐朋
【直播】3小时掌握Docker最佳实战-徐西宁
【套餐】机器学习系列套餐(算法+实战)--唐宇迪
【直播】计算机视觉原理及实战--屈教授
【套餐】微信订阅号+服务号Java版 v2.0--翟东平
【直播】机器学习之矩阵--黄博士
【套餐】微信订阅号+服务号Java版 v2.0--翟东平
【直播】机器学习之凸优化--马博士
【套餐】Javascript 设计模式实战--曾亮
查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:12084次
    • 积分:206
    • 等级:
    • 排名:千里之外
    • 原创:9篇
    • 转载:2篇
    • 译文:0篇
    • 评论:1条
    文章分类
    最新评论