天道酬勤,学无止境

HQL: Hibernate query with ManyToMany

问题

我对 HQL 查询和休眠有疑问。

我有一个用户类和一个角色类。 一个用户可以有很多角色。 所以我有一个这样的多对多关系:

在用户类中:

@ManyToMany(fetch = FetchType.LAZY)
@oinTable(name = "PORTAIL_USERROLE", joinColumns = { @JoinColumn(name = "USERID", nullable = false, updatable = false) }, inverseJoinColumns = { @JoinColumn(name = "ROLE", nullable = false, updatable = false) })
public Set<Portailrole> getPortailroles() {
    return this.portailroles;
}

在角色类中:

@ManyToMany(fetch = FetchType.LAZY)
@JoinTable(name = "PORTAIL_USERROLE", joinColumns = { @JoinColumn(name = "ROLE", nullable = false, updatable = false) }, inverseJoinColumns = { @JoinColumn(name = "USERID", nullable = false, updatable = false) })
public Set<Portailuser> getPortailusers() {
    return this.portailusers;
}

此映射创建了第三个表 (PORTAIL_USERROLE),其中存储了关系。 一切都像这样正常工作。 当我有一个用户时,我会检索角色。

但是,我的问题是:在 HQL 查询中,如何获取所有具有特定角色的用户? 任何类都代表 PORTAIL_USERROLE 表,所以我不知道如何进行 HQL 查询。

回答1
from Portailuser u join u.portailroles r where r.name=:roleName
回答2
 @JoinTable(name = "OFFICE_USER_POSITION", joinColumns = {
        @JoinColumn(name = "user_id", referencedColumnName = "id")}, inverseJoinColumns = {
        @JoinColumn(name = "POST_ID", referencedColumnName = "id")})
@WhereJoinTable(clause = "primary_flag='" + YES + "' and del_flag='" + DEL_FLAG_NORMAL + "'")

受限制的 HTML

  • 允许的HTML标签:<a href hreflang> <em> <strong> <cite> <blockquote cite> <code> <ul type> <ol start type> <li> <dl> <dt> <dd> <h2 id> <h3 id> <h4 id> <h5 id> <h6 id>
  • 自动断行和分段。
  • 网页和电子邮件地址自动转换为链接。

相关推荐
  • HQL: Hibernate query with ManyToMany
    I have a question with HQL query and hibernate. I have a user class and a role class. A user can have many roles. So I have a ManyToMany relatation like this: In user class: @ManyToMany(fetch = FetchType.LAZY) @oinTable(name = "PORTAIL_USERROLE", joinColumns = { @JoinColumn(name = "USERID", nullable = false, updatable = false) }, inverseJoinColumns = { @JoinColumn(name = "ROLE", nullable = false, updatable = false) }) public Set<Portailrole> getPortailroles() { return this.portailroles; } In role class: @ManyToMany(fetch = FetchType.LAZY) @JoinTable(name = "PORTAIL_USERROLE", joinColumns = {
  • Error when use JDL in Jhipster
    I have a problem when use JDL in Jhipster. I design my entities with JDL-Studio, this is jhipster-jdl.jh file after created: entity Member { fullName String required maxlength(100), birthDay LocalDate, address String maxlength(100), phone String maxlength(12), avatar String maxlength(100), aboutMe String maxlength(5000), systemStatus SystemStatus required, star Integer required } entity RealEstate { useFor UseFor required, location String maxlength(100), address String required maxlength(100), acreage Float required, price Float required, currencyUnit CurrencyUnit required, typeRent TypeRent
  • 在 Jhipster 中使用 JDL 时出错(Error when use JDL in Jhipster)
    问题
  • JPA query in multiple tables with many-to-many relationship
    There are three tables: Hospital, Medical_Service and Language_Service, Hospital can provide medical service and language service. So there are two many-to-many relationships. Simple ERD Now I want to search hospital data with postcode = 3000 and medical service = Emergency. DaoImpl: public List<Hospital> findByPostcodeAndMedicalType(String postcode, String medical) { String str = "SELECT h FROM Hospital h INNER JOIN Medical_Service m ON h.hospital_id = m.hospital_id WHERE " + "h.Postcode = :postcode AND m.Medical_name = :medical"; Query query = em.createQuery(str); query.setParameter(
  • 使用 Hibernate Criteria 查询多对多关系(Querying ManyToMany relationship with Hibernate Criteria)
    问题 我不知道如何描述这个问题,所以我认为一个例子是问我问题的最好方法: 我有两个具有 manyToMany 关系的表: DriversLicence <-> LicenceClass LicenseClass 是诸如“汽车”、“摩托车”和“中刚性”之类的东西。 使用 Hibernate Criteria,如何找到同时具有“Car”和“Motorbike”LicenceClasses 的所有许可证? 2008 年 12 月 11 日更新我发现这可以通过使用自定义 ResultTransformer 轻松实现。 然而问题是结果转换器只在查询返回结果后应用,它实际上并没有成为 SQL 的一部分。 所以我想我现在的问题是“你能做我最初在 SQL 中描述的事情吗 - 是否有 Hibernate Criteria 模拟?” 回答1 这是我最终使用 HQL 实现的方法: public List<DriversLicence> findDriversLicencesWith(List<LicenceClass> licenceClasses) { String hqlString = "select dl from DriversLicenceImpl dl where 1=1 "; for (int i = 0; i < licenceClasses.size(); i++) {
  • 多对多关系多表中的JPA查询(JPA query in multiple tables with many-to-many relationship)
    问题 共有三个表: Hospital 、 Medical_Service和Language_Service , Hospital 可以提供医疗服务和语言服务。 所以有两个多对多的关系。 简单的ERD 现在我想用postcode = 3000和medical service = Emergency搜索医院数据。 DaoImpl: public List<Hospital> findByPostcodeAndMedicalType(String postcode, String medical) { String str = "SELECT h FROM Hospital h INNER JOIN Medical_Service m ON h.hospital_id = m.hospital_id WHERE " + "h.Postcode = :postcode AND m.Medical_name = :medical"; Query query = em.createQuery(str); query.setParameter("postcode", postcode); query.setParameter("medical", medical); return query.getResultList(); } 而且,如果我想从三个表中按邮编,医疗类型和语言进行搜索
  • Hibernate: org.hibernate.hql.ast.QuerySyntaxException: unexpected token
    问题 我正在使用 Hibernate,我有这个查询: List<Person> list = sess.createQuery("from Person").list(); 通过此语句,我从数据库中获取所有人员。 但现在,我只想要一些人。 我的数据库方案: 项目 <- Project_Person -> 人 所以我只想要作为项目成员的人员。 使用数据库上的 SQL 语句,我得到了想要的结果: select * from Person inner join Project_Person on person_id = id where project_id = 1; 所以我想,我可以用 Hibernate 写这个: List<Person> list = sess.createQuery( "from Person inner join Project_Person on person_id = id where project_id = "+projectId).list(); 但在这里我得到一个错误: SERVE: Servlet.service() for servlet myproject3 threw exception org.hibernate.hql.ast.QuerySyntaxException: unexpected token: on near line 1
  • Hibernate: org.hibernate.hql.ast.QuerySyntaxException: unexpected token
    I am using Hibernate and I have this query: List<Person> list = sess.createQuery("from Person").list(); With this statement, I get all persons from the database. But now, I only want some persons. My database scheme: Project <- Project_Person -> Person So I only want Persons which are a member of a project. With the SQL statement on the database I get the desired result: select * from Person inner join Project_Person on person_id = id where project_id = 1; So I thought, I can write this with Hibernate: List<Person> list = sess.createQuery( "from Person inner join Project_Person on person_id =
  • Hql join and group by problem
    I am trying to make a query in hql. I have these beans: Bean1 @Entity @Table(name = "bean1") public class Bean1 { ... private List<Bean2> bean2; ... @ManyToMany( cascade={CascadeType.ALL}, fetch=FetchType.LAZY) @JoinTable( name="bean1_bean2", joinColumns=@JoinColumn(name="bean1_id"), inverseJoinColumns=@JoinColumn(name="bean2_id") ) public List<Bean2> getBean2() { return bean2; } public void setTags(List<Bean2> bean2) { this.bean2 = bean2; } ... } Bean2 @Entity @Table(name = "bean2") public class Bean2 { private String bean2_id; private List<Bean1> bean1; @Id @Column(name = "bean2_id", length
  • 查询方法 public abstract java.util.List 验证失败(Validation failed for query for method public abstract java.util.List)
    问题 我有一个基本的 SpringBoot 应用程序。 使用 Spring Initializer、JPA、嵌入式 Tomcat、Thymeleaf 模板引擎,并打包为可执行 JAR 文件。 SpringBoot 的版本是 2.0.1.RELEASE。 我用这个方法创建了一个从 CrudRepository 扩展的类存储库 @Query("select us.priceAlertsTapas.tapa from User us left join us.priceAlertsTapas pat left join pat.tapa tapa where pat.priceAlert = ?1") List<Tapa> tapasByUserPriceAlert (PriceAlert pa); 但是当我初始化项目时,我收到了这个错误: Validation failed for query for method public abstract java.util.List at org.springframework.data.jpa.repository.query.SimpleJpaQuery.validateQuery(SimpleJpaQuery.java:93) at org.springframework.data.jpa.repository.query
  • 在 JPA/Hibernate 中建模多对多关系(Modeling many-to-many Relationship in JPA/Hibernate)
    问题 架构在这里:http://sqlfiddle.com/#!9/5ec63/2 或在这里; 从多对多关系查询 现在,我创建了以下类: 学生.java @Entity public class Student implements Serializable { @Id @GeneratedValue private int id; @NotNull @Size(min = 1, max = 35) private String name; 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; } } 朋友.java @Entity public class Friend implements Serializable { @EmbeddedId @NotNull @ManyToMany( targetEntity = Student.class ) private FriendPK primaryKey; public FriendPK getPrimaryKey() { return
  • JPA 与 Hibernate - 多对多关系,获取所有数据(JPA with Hibernate - Many to Many relationship, fetching all data)
    问题 我在用户和角色之间有很多关系。 例如 public class User { @Id private Integer id; @ManyToMany @JoinTable(name = "APP_USER_ROLE", joinColumns = { @JoinColumn(name = "USER_ID") }, inverseJoinColumns = { @JoinColumn(name = "ROLE_ID") }) private List<Role> roles = new ArrayList<Role>(); } @Entity public class Role { @Id private Integer id; @ManyToMany(mappedBy = "roles") private List<User> users = new ArrayList<User>(); } 我的要求是获取系统中所有用户的角色。 使用 HQL 时,我的查询是 select u, u.roles from User u 但是,这会为每个具有相应角色的用户触发查询。 如果有 100 个用户,它将触发 100 个查询。 但是在 SQL 中我可以使用以下查询来实现它 select u.id, u.name, m.roleId, r.name FROM User u left
  • JPA with Hibernate - Many to Many relationship, fetching all data
    I have many to many relation ship between User and Roles. For example public class User { @Id private Integer id; @ManyToMany @JoinTable(name = "APP_USER_ROLE", joinColumns = { @JoinColumn(name = "USER_ID") }, inverseJoinColumns = { @JoinColumn(name = "ROLE_ID") }) private List<Role> roles = new ArrayList<Role>(); } @Entity public class Role { @Id private Integer id; @ManyToMany(mappedBy = "roles") private List<User> users = new ArrayList<User>(); } My requirement is to fetch all users in the system with their roles. When using HQL using this my query is select u, u.roles from User u however
  • Hql 按问题加入和分组(Hql join and group by problem)
    问题 我正在尝试在 hql 中进行查询。 我有这些豆子: 豆1 @Entity @Table(name = "bean1") public class Bean1 { ... private List<Bean2> bean2; ... @ManyToMany( cascade={CascadeType.ALL}, fetch=FetchType.LAZY) @JoinTable( name="bean1_bean2", joinColumns=@JoinColumn(name="bean1_id"), inverseJoinColumns=@JoinColumn(name="bean2_id") ) public List<Bean2> getBean2() { return bean2; } public void setTags(List<Bean2> bean2) { this.bean2 = bean2; } ... } 豆2 @Entity @Table(name = "bean2") public class Bean2 { private String bean2_id; private List<Bean1> bean1; @Id @Column(name = "bean2_id", length = 100, unique = true, nullable =
  • 如何使用bulkUpdate批量删除(How to batch delete using bulkUpdate)
    问题 我有一个通用的用户/角色设置,带有一个 user_role 连接表。 我正在尝试使用 Spring 的 HibernateTemplate 批量删除所有锁定的用户,如下所示: getHibernateTemplate().bulkUpdate("delete from User where locked=?", true); 如果被删除的用户没有任何角色(user_role 表中没有记录),那么一切正常; 但是,如果用户确实有角色记录,则会出现以下错误: 违反完整性约束 - 找到子记录 角色在 User.java 中定义如下: @ManyToMany(fetch = FetchType.EAGER) @JoinTable(name = "user_role", joinColumns = { @JoinColumn(name = "user_id") }, inverseJoinColumns = @JoinColumn(name = "role_id")) private Set<Role> roles = new HashSet<Role>(); 那么即使用户有子记录,我如何批量删除用户? 谢谢! 回答1 根据 JPA 规范,批量删除操作不会级联到相关实体: 4.10 批量更新和删除操作批量更新和删除操作适用于单个实体类(及其子类,如果有)的实体。 在 FROM 或
  • 休眠连接错误:org.hibernate.hql.ast.HqlSqlWalker.createFromJoinElement 处的 NullPointerException(Hibernate join error: NullPointerException at org.hibernate.hql.ast.HqlSqlWalker.createFromJoinElement)
    问题 我在我的项目(小吃店)中创建了这个 hql,以搜索所有将用户选择的产品作为参数的订单: select order from Order order, OrderItem item inner join order.cod_order_item as item inner join item.cod_product as cod_product where cod_product = id 但是,当我运行 createQuery() 时,会在 org.hibernate.hql.ast.HqlSqlWalker.createFromJoinElement 处给出一个空指针。 我究竟做错了什么? 下面,这是我的代码: OrderDAO.java public class OrderDAO { private Session session; public PedidoDAO(Session session){ this.session = session; } public List<Order> getAllOrderFromProduct(Product product{ String hql = "select order from Order order, OrderItem item " + "inner join order.order_item_id as item
  • 如何使用hibernate查询连接表?(how to query the join table using hibernate?)
    问题 我在 Oracle SQL Developer 中创建了三个表,即 1.Test_Employee2 2.Test_Project2 3.Employee_Project2 。 表Employee_Project2是连接表,因为Test_Project2和Employee_Project2之间的关系是Many-To-Many 。 在休眠中,我分别为Test_Project2和Employee_Project2表创建了两个休眠类TestEmployee和TestProject , 表Employee_Project2在TestProject hibernate 类中定义如下: @ManyToMany(cascade = CascadeType.ALL) @JoinTable(name = "Employee_Project2", joinColumns = @JoinColumn(name = "proj_id"), inverseJoinColumns = @JoinColumn(name = "emp_id")) private Set<TestEmployee> employeesList; 我用一些记录填充了表Test_Project2和Employee_Project2 ,连接表Employee_Project2自动填充了一些记录。 现在我目前面临的问题是,我想使用
  • rewrite SQL query to HQL
    I've SQL query: SELECT tag.id, tag.description, tag.`name`, COUNT(question_has_tag.question_id) FROM question_has_tag INNER JOIN question ON question.id = question_has_tag.question_id INNER JOIN tag ON question_has_tag.tag_id = tag.id GROUP BY tag.id All works good. But I need to rewrite it to HQL, some one have any idea? I tried this, but it doesn't work: list = entityManager.createQuery("SELECT " + "t.id, " + "t.description, " + "t.name, " + "COUNT(q.id) " + "FROM Tag t INNER JOIN Question q ON t.id = q.id " + "GROUP BY t.id").getResultList(); Sorry it not all information, I've 3 table tag
  • 将 SQL 查询重写为 HQL(rewrite SQL query to HQL)
    问题
  • Hibernate HQL : no entity found for query
    It spent a day trying solve this problem however it's still not successful. I have Test entity : public class Test { @Id @Column(name = "id") @GeneratedValue(strategy = GenerationType.AUTO) private long id; @Column(name = "duration", nullable = false) private int duration; @Column(name = "test_name", nullable = false, unique = true) private String testName; @Column(name = "archived", nullable = false) private boolean archived; @OneToMany(cascade = CascadeType.ALL, mappedBy = "test", fetch = FetchType.EAGER) private Set<Question> questions; @ManyToMany(mappedBy = "tests") private Set<User>