子查询在 CriteriaQuery 中的使用

huangapple go评论73阅读模式
英文:

SubQuery in a CriteriaQuery

问题

以下是翻译好的代码部分:

我有一个抽象类 **Entity**以及这两个继承自 Entity 的实体 **Role****User**

    @Entity
    @Inheritance(strategy = InheritanceType.TABLE_PER_CLASS)
    public abstract class Entity {
    
        @Id
        @GeneratedValue(strategy = GenerationType.AUTO)
        protected long id = 0;
    }

    @Entity
    public class Role extends Entity {
        @Column(name = "NAME", unique = true, nullable = false)
        private String name;
    }

    @Entity
    public class User extends Entity {
        @Column(name = "ABBREVIATION", unique = true, nullable = false)
        private String abbreviation;

        // 用户可以拥有多个角色
        @ManyToMany
        @JoinTable(
            name = "USER_ROLE",
            joinColumns = @JoinColumn(name = "USER_ID"),
            inverseJoinColumns = @JoinColumn(name = "ROLE_ID"))
        private Set<Role> roles = new HashSet<>();
    }

现在当我点击一个角色时我希望显示用户的缩写用于此的原生查询如下

    query = em.createNativeQuery("SELECT ABBREVIATION FROM dbtest.user WHERE ID IN"
                                + " (SELECT USER_ID FROM dbtest.user_role WHERE ROLE_ID = " + role.getId() + ")");

我现在已经创建了一个标准查询但它总是返回一个空列表

    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Role> cq = cb.createQuery(Role.class);
    Root<User> root = cq.from(User.class);

    Subquery<Long> subquery = cq.subquery(Long.class);
    Root<Role> subfrom = subquery.from(Role.class);

    subquery.select(subfrom.get(Role_.id));
    subquery.where(cb.equal(subfrom.get(Role_.id), role.getId()));

    cq.multiselect(root.get(User_.abbreviation));
    cq.where(cb.in(root.get(User_.id)).value(subquery));

    query = em.createQuery(cq);
    return query.getResultList();

这里有什么问题有人可以帮忙吗
英文:

I have the abstract class Entity and these two Entities Role and User which extends from Entity:

@Entity@Inheritance(strategy = InheritanceType.TABLE_PER_CLASS)
public abstract class Entity {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
protected long id = 0; }
@Entity
public class Role extends Entity{
@Column(name = &quot;NAME&quot;, unique = true, nullable = false)
private String name;
}
@Entity
public class User extends Entity{
@Column(name = &quot;ABBREVIATION&quot;, unique = true, nullable = false)
private String abbreviation;
//The user can have several roles
@ManyToMany
@JoinTable(
name = &quot;USER_ROLE&quot;,
joinColumns = @JoinColumn(name = &quot;USER_ID&quot;),
inverseJoinColumns = @JoinColumn(name = &quot;ROLE_ID&quot;))
private Set&lt;Role&gt; roles = new HashSet&lt;&gt;();
}

Now when I click on a role, I want the abbreviations of the users to be displayed.
The native query for it is:

query = em.createNativeQuery(&quot;SELECT ABBREVIATION FROM dbtest.user WHERE ID IN&quot;
+ &quot; (SELECT USER_ID FROM dbtest.user_role WHERE ROLE_ID = &quot; + role.getId() + &quot;)&quot;);

i have now created a criteria query of it, which always gives me an empty list.

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery&lt;Role&gt; cq = cb.createQuery(Role.class);
Root&lt;User&gt; root = cq.from(User.class);
Subquery subquery = cq.subquery(Role.class);
Root subfrom = subquery.from(Role.class);
subquery.select(subfrom.get(Role_.id));
subquery.where(cb.equal(subfrom.get(Role_.id), role.getId()));
cq.multiselect(root.get(User_.abbreviation));
cq.where(cb.equal(root.get(User_.id), subquery));
query = em.createQuery(cq);
return query.getResultList();

What is here wrong? Can anyone help ?

答案1

得分: 3

首先,设置正确的类型:

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<String> cq = cb.createQuery(String.class);
Root<User> root = cq.from(User.class);
Join<User, Role> joinRole = root.join(User_.roles, JoinType.Inner);

Subquery<Long> subquery = cq.subquery(Long.class);
Root<Role> subfrom = subquery.from(Role.class);

subquery.select(subfrom.get(Role_.id));
subquery.where(cb.equal(subfrom.get(Role_.id), role.getId()));

cq.multiselect(root.get(User_.abbreviation));
cq.where(cb.equal(root.get(User_.id), subquery.getSelection()));

query = em.createQuery(cq);
return query.getResultList();

另外,主查询的条件应为 in 而非 equals,请将这部分代码:

cq.where(cb.equal(root.get(User_.id), subquery.getSelection()));

替换为:

cq.where(joinRole.get(Role_.id).in(subquery.getSelection()));
英文:

First of all, set the correct types:

CriteriaBuilder cb = em.getCriteriaBuilder();
//CriteriaQuery&lt;Role&gt; cq = cb.createQuery(Role.class);
//The type of the data returned in multiselect.
CriteriaQuery&lt;String&gt; cq = cb.createQuery(String.class);
Root&lt;User&gt; root = cq.from(User.class);
//Add to compare
Join&lt;User,Role&gt; joinRole = root.join(User_.roles,JoinType.Inner);
//The type of the data returned in select.
//Subquery subquery = cq.subquery(Role.class);
Subquery&lt;Long&gt; subquery = cq.subquery(Long.class);
//Root subfrom = subquery.from(Role.class);
//We set the data type in the Root
Root&lt;Role&gt; subfrom = subquery.from(Role.class);
subquery.select(subfrom.get(Role_.id));
subquery.where(cb.equal(subfrom.get(Role_.id), role.getId()));
cq.multiselect(root.get(User_.abbreviation));
//cq.where(cb.equal(root.get(User_.id), subquery));
//To use in the where subquery, call the getSelection () method
cq.where(cb.equal(root.get(User_.id), subquery.getSelection()));
query = em.createQuery(cq);
return query.getResultList();

Also, the where of the main query is an in not an equals, change this

cq.where(cb.equal(root.get(User_.id), subquery.getSelection()));

by this

cq.where(joinRole.get(Role_.id).in(subquery.getSelection()));

答案2

得分: 1

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Role> cq = cb.createQuery(Role.class);
Root<User> root = cq.from(User.class);

Subquery<Long> subquery = cq.subquery(Long.class);
Root<Role> subqueryRoot = subquery.from(Role.class);
Predicate subqueryPredicate = 
        cb.equal(subqueryRoot.get(Role_.id), role.getId());

//!!! 我怀疑在这里您需要选择 USER_ID,但是 Role 实体中并不包含这样的字段        
subquery.select(subqueryRoot.get(Role_.id)) 
        .where(subqueryPredicate); 

Predicate queryPredicate = cb.in(root.get(User_.id)).value(subquery); 
cq.multiselect(root.get(User_.abbreviation)).where(queryPredicate);
英文:
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery&lt;Role&gt; cq = cb.createQuery(Role.class);
Root&lt;User&gt; root = cq.from(User.class);
Subquery&lt;Long&gt; subquery = cq.subquery(Long.class);
Root&lt;Role&gt; subqueryRoot = subquery.from(Role.class);
Predicate subqueryPredicate = 
cb.equal(subqueryRoot.get(Role_.id), role.getId());
//!!! I suspect here you have to select USER_ID, but Role entity does not contain such field        
subquery.select(subqueryRoot.get(Role_.id)) 
.where(subqueryPredicate); 
Predicate queryPredicate = cb.in(root.get(User_.id)).value(subquery); 
cq.multiselect(root.get(User_.abbreviation)).where(queryPredicate);

huangapple
  • 本文由 发表于 2020年9月10日 14:26:46
  • 转载请务必保留本文链接:https://go.coder-hub.com/63823911.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定