JPA Criteria API – GROUP BY 中的空值检查

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

JPA Criteria API - null check on GROUP BY

问题

我需要返回结果数组,其中分组列上的数据可能包含空值,而当前跳过了这些空值,但我也希望将它们分组。

我的实体:

public class UserEntity {
// ...
    @Basic
    @Column(name = "username")
    private String username;
}

public class ZgloszenieEntity {
// ... 

    @ManyToOne
    @JoinColumn(name = "assigned_user_id" )
    @OrderBy("username")
    private UserEntity assignedUser;

    @ManyToOne(targetEntity = InternalStatusEntity.class)
    @JoinColumn(name = "internal_status")
    @NotAudited
    private InternalStatusEntity internalStatus;
}

受影响的代码:

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<EfficiencyStatusReportDTO> cq = cb.createQuery(EfficiencyStatusReportDTO.class);
Root<ZgloszenieEntity> root = cq.from(ZgloszenieEntity.class);
Join<UserEntity, ZgloszenieEntity> join = root.join("assignedUser");

cq.multiselect(join.get("username")
        , root.get("internalStatus").get("description")
        , getNumberOfDocOfType(cb, root, "X-1")
        , getNumberOfDocOfType(cb, root, "X-2")
        , getNumberOfDocOfType(cb, root, "X-3")
        , getNumberOfDocOfType(cb, root, "X-4")
        , cb.count(root)
);
cq.groupBy(join.get("username"), root.get("internalStatus").get("description"));

对于internalStatus不为null的实体,它运行得很好,但是可能有没有关联的InternalStatus的情况,我希望在这种情况下也进行分组。

当前示例结果:

// ... 
{
    "username": "admin@gmail.com",
    "internalStatus": "Do stuff",
    "numberOfDoc1": 0,
    "numberOfDoc2": 2,
    "numberOfDoc3": 1,
    "numberOfDoc4": 0,
    "sumOfDoc": 3
},

我也希望有类似以下结果:

{
    "username": "admin@gmail.com",
    "internalStatus": null,
    "numberOfDoc1": 4,
    "numberOfDoc2": 1,
    "numberOfDoc3": 5,
    "numberOfDoc4": 0,
    "sumOfDoc": 10
},

JPA生成的查询:

select userentity1_.username                                          as col_0_0_,
       internalst2_.description                                       as col_1_0_,
       count(case when zgloszenie0_.form_type='X-1' then 1 else null end) as col_2_0_,
       count(case when zgloszenie0_.form_type='X-2' then 1 else null end) as col_3_0_,
       count(case when zgloszenie0_.form_type='X-3' then 1 else null end) as col_4_0_,
       count(case when zgloszenie0_.form_type='X-4' then 1 else null end) as col_5_0_,
       count(zgloszenie0_.uid)                                        as col_6_0_
from zgloszenie zgloszenie0_
         inner join user userentity1_ on zgloszenie0_.assigned_user_id = userentity1_.id
         cross join internal_status internalst2_
where zgloszenie0_.internal_status = internalst2_.id
  and 1 = 1
group by userentity1_.username, internalst2_.description
order by userentity1_.username desc

有没有办法更改自动生成的交叉连接(cross join)以考虑null?

英文:

I need to return array of results, where data on the grouped column could contain null values, and those are skipped currently, while I would like to also have them grouped.

My entities:

public class UserEntity {
// ...
    @Basic
    @Column(name = &quot;username&quot;)
    private String username;
}

public class ZgloszenieEntity {
// ... 

    @ManyToOne
    @JoinColumn(name = &quot;assigned_user_id&quot; )
    @OrderBy(&quot;username&quot;)
    private UserEntity assignedUser;

    @ManyToOne(targetEntity = InternalStatusEntity.class)
    @JoinColumn(name = &quot;internal_status&quot;)
    @NotAudited
    private InternalStatusEntity internalStatus;
}

Code affected:

  CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery&lt;EfficiencyStatusReportDTO&gt; cq = cb.createQuery(EfficiencyStatusReportDTO.class);
        Root&lt;ZgloszenieEntity&gt; root = cq.from(ZgloszenieEntity.class);
        Join&lt;UserEntity, ZgloszenieEntity&gt; join = root.join(&quot;assignedUser&quot;);

        cq.multiselect(join.get(&quot;username&quot;)
                , root.get(&quot;internalStatus&quot;).get(&quot;description&quot;)
                , getNumberOfDocOfType(cb, root, &quot;X-1&quot;)
                , getNumberOfDOcOfType(cb, root, &quot;X-2&quot;)
                , getNumberOfDocOfType(cb, root, &quot;X-3&quot;)
                , getNumberOfDocOfType(cb, root, &quot;X-4&quot;)
                , cb.count(root)
        );
        cq.groupBy(join.get(&quot;username&quot;), root.get(&quot;internalStatus&quot;).get(&quot;description&quot;));

It works fine for entities, that have internalStatus != null, but it is possible to have it without connected InternalStatus, and I would like to have it grouped by null then.

Current example result:

// ... 
        {
            &quot;username&quot;: &quot;admin@gmail.com&quot;,
            &quot;internalStatus&quot;: &quot;Do stuff&quot;,
            &quot;numberOfDoc1&quot;: 0,
            &quot;numberOfDoc2&quot;: 2,
            &quot;numberOfDoc3&quot;: 1,
            &quot;numberOfDoc4&quot;: 0,
            &quot;sumOfDoc&quot;: 3
        },

And I would like to have it also result with something like this:

        {
            &quot;username&quot;: &quot;admin@gmail.com&quot;,
            &quot;internalStatus&quot;: null,
            &quot;numberOfDoc1&quot;: 4,
            &quot;numberOfDoc2&quot;: 1,
            &quot;numberOfDoc3&quot;: 5,
            &quot;numberOfDoc4&quot;: 0,
            &quot;sumOfDoc&quot;: 10
        },

Query generated by the JPA:

select userentity1_.username                                          as col_0_0_,
       internalst2_.description                                       as col_1_0_,
       count(case when zgloszenie0_.form_type=&#39;X-1&#39; then 1 else null end) as col_2_0_,
       count(case when zgloszenie0_.form_type=&#39;X-2&#39; then 1 else null end) as col_3_0_,
       count(case when zgloszenie0_.form_type=&#39;X-3&#39; then 1 else null end) as col_4_0_,
       count(case when zgloszenie0_.form_type=&#39;X-4&#39; then 1 else null end) as col_5_0_,
       count(zgloszenie0_.uid)                                        as col_6_0_
from zgloszenie zgloszenie0_
         inner join user userentity1_ on zgloszenie0_.assigned_user_id = userentity1_.id
         cross join internal_status internalst2_
where zgloszenie0_.internal_status = internalst2_.id
  and 1 = 1
group by userentity1_.username, internalst2_.description
order by userentity1_.username desc

Is there a way to change the cross join created automatically to have a null taken into the consideration?

答案1

得分: 2

试试这个

    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<EfficiencyStatusReportDTO> cq = 
        cb.createQuery(EfficiencyStatusReportDTO.class);
    Root<ZgloszenieEntity> root = cq.from(ZgloszenieEntity.class);
    Join<ZgloszenieEntity, UserEntity> assignedUser = root.join("assignedUser", 
        JoinType.LEFT);
    Join<ZgloszenieEntity, InternalStatus> internalStatus = root.join("internalStatus", 
        JoinType.LEFT);
    
    cq.multiselect(assignedUser.get("username"), 
                   internalStatus.get("description"),
                   getNumberOfDocOfType(cb, root, "X-1"),
                   getNumberOfDOcOfType(cb, root, "X-2"),
                   getNumberOfDocOfType(cb, root, "X-3"),
                   getNumberOfDocOfType(cb, root, "X-4"),
                   cb.count(root)
    );
    cq.groupBy(assignedUser.get("username"), internalStatus.get("description"));
英文:

Try this

 CriteriaBuilder cb = em.getCriteriaBuilder();
 CriteriaQuery&lt;EfficiencyStatusReportDTO&gt; cq = 
     cb.createQuery(EfficiencyStatusReportDTO.class);
 Root&lt;ZgloszenieEntity&gt; root = cq.from(ZgloszenieEntity.class);
 Join&lt;ZgloszenieEntity, UserEntity&gt; assignedUser = root.join(&quot;assignedUser&quot;, 
     JoinType.LEFT);
 Join&lt;ZgloszenieEntity, InternalStatus&gt; internalStatus = root.join(&quot;internalStatus&quot;, 
     JoinType.LEFT);

 cq.multiselect(assignedUser.get(&quot;username&quot;), 
                internalStatus.get(&quot;description&quot;),
                getNumberOfDocOfType(cb, root, &quot;X-1&quot;),
                getNumberOfDOcOfType(cb, root, &quot;X-2&quot;),
                getNumberOfDocOfType(cb, root, &quot;X-3&quot;),
                getNumberOfDocOfType(cb, root, &quot;X-4&quot;),
                cb.count(root)
 );
 cq.groupBy(assignedUser.get(&quot;username&quot;), internalStatus.get(&quot;description&quot;));

huangapple
  • 本文由 发表于 2020年9月15日 19:03:56
  • 转载请务必保留本文链接:https://go.coder-hub.com/63900496.html
匿名

发表评论

匿名网友

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

确定