英文:
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 = "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;
}
Code affected:
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"));
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:
// ...
{
"username": "admin@gmail.com",
"internalStatus": "Do stuff",
"numberOfDoc1": 0,
"numberOfDoc2": 2,
"numberOfDoc3": 1,
"numberOfDoc4": 0,
"sumOfDoc": 3
},
And I would like to have it also result with something like this:
{
"username": "admin@gmail.com",
"internalStatus": null,
"numberOfDoc1": 4,
"numberOfDoc2": 1,
"numberOfDoc3": 5,
"numberOfDoc4": 0,
"sumOfDoc": 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='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
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<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"));
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论