在Spring数据计数方法中使用具有规范的组 [英] using group by with specification in Spring data count method
本文介绍了在Spring数据计数方法中使用具有规范的组的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我想实现一个spring数据方法，它检查以下查询的结果
从installation_requested_t请求的select count（1）
其中requested.installation_id在
中（从installation_requested_t中选择installation_id，其中request_id =？）
group by requested.installation_id
具有count（1）> 1;
对于该项目，必须使用spring数据jpa的规范。
所以有我的库方法调用：
installationRequestedRepository.count（
InstallationSpecs
。 existsInstallationRequestLinkedToAnotherEPR（movingEprId）
）;
和我的规范方法：
public static Specification< InstallationRequested> existsInstallationRequestLinkedToAnotherEPR（final Long EPRId）{
return new Specification< InstallationRequested>（）{
@Override
public Predicate toPredicate（Root< InstallationRequested> root，CriteriaQuery<> query ，CriteriaBuilder cb）{
子查询< Long> InstallationIdsLinkedToEPRsubquery = query.subquery（Long.class）;
InstallationIdsLinkedToEPRsubquery.select（root.get（InstallationRequested_.id））
.where（cb.equal（root.get（InstallationRequested_.environmentPermitRequestId），EPRId））;
子查询< InstallationRequested> installationRequestForTheseInstallationIdsWhoAreLinkedToAnotherEPR =
query.subquery（InstallationRequested.class）;
installationRequestForTheseInstallationIdsWhoAreLinkedToAnotherEPR
.select（installationRequestForTheseInstallationIdsWhoAreLinkedToAnotherEPR $ b $ .from（InstallationRequested.class））
.where（root.get（InstallationRequested_.id）.in（InstallationIdsLinkedToEPRsubquery））
.groupBy（root.get（InstallationRequested_.id））
.having（cb.greaterThan（cb.count（cb.literal（1）），cb.literal（1l）））;
return cb.exists（installationRequestForTheseInstallationIdsWhoAreLinkedToAnotherEPR）;
}
};
$ / code $ / pre
我的问题是一切都不能很好地执行： org.springframework.dao.InvalidDataAccessApiUsageException：
org.hibernate.hql.internal.ast.QuerySyntaxException：
意外标记：其中第1行第333列
[select count（generatedAlias0）from be.question.domain.model.request.installation.InstallationRequested as generatedAlias0
where exists
（select generatedAlias1 from be.question.domain.model.request.installation.InstallationRequested as generatedAlias1
其中generatedAlias0.id在
中（选择generatedAlias0.id from where generatedAlias0.environmentPermitRequestId = 1072487L）
group by generatedAlias0 .id计数（1）> 1L）
];
您是否有想法使我的规范有效并以另一种方式完成问题任务？
解决方案我找到了解决我的问题的方法。
我修正了规范：
public static Specification< InstallationRequested> existsInstallationRequestLinkedToAnotherEPR（final Long EPRId）{
return new Specification< InstallationRequested>（）{
@Override
public Predicate toPredicate（Root< InstallationRequested> root，CriteriaQuery<> query ，CriteriaBuilder cb）{
//子查询选择安装ID
子查询< Long> InstallationIdsLinkedToEPRsubquery = query.subquery（Long.class）;
Root< InstallationRequested> selectIdSubQueryRoot =
InstallationIdsLinkedToEPRsubquery.from（InstallationRequested.class）;
InstallationIdsLinkedToEPRsubquery
.select（selectIdSubQueryRoot.get（InstallationRequested_.installation）.get（Installation_.id））
.where（cb.equal（selectIdSubQueryRoot.get（InstallationRequested_.environmentPermitRequestId），
EPRId））;
//子查询通过安装ID选择安装请求组的计数
子查询< Long> installationRequestForTheseInstallationIdsWhoAreLinkedToAnotherEPR =
query.subquery（Long.class）;
Root< InstallationRequested> groupBySubQueryRoot =
installationRequestForTheseInstallationIdsWhoAreLinkedToAnotherEPR
.from（InstallationRequested.class）;
Path< Long> installationIdInGroupBySubQuery =
groupBySubQueryRoot.get（InstallationRequested_.installation）.get（Installation_.id）;
installationRequestForTheseInstallationIdsWhoAreLinkedToAnotherEPR.select（cb.count（cb.literal（1）））
.where（installationIdInGroupBySubQuery.in（InstallationIdsLinkedToEPRsubquery））
.groupBy（installationIdInGroupBySubQuery）
.having（cb.greaterThan（cb.count（cb.literal（1）），cb.literal（1l）））;
//返回组的结果的现有条件
return cb.exists（installationRequestForTheseInstallationIdsWhoAreLinkedToAnotherEPR）;
}
};
code
$ b 顺便说一句，如果有人认为我更好很高兴看到它;）
I want to implement a spring data method which check result of the following query
select count(1) from installation_requested_t requested
where requested.installation_id in
(select installation_id from installation_requested_t where request_id=?)
group by requested.installation_id
having count(1)>1;
For the project, it is mandatory to use specification with the spring data jpa.
So there is my repository method call :
installationRequestedRepository.count(
InstallationSpecs
.existsInstallationRequestLinkedToAnotherEPR(movingEprId)
);
and my spec method:
public static Specification<InstallationRequested> existsInstallationRequestLinkedToAnotherEPR(final Long EPRId) {
return new Specification<InstallationRequested>() {
@Override
public Predicate toPredicate(Root<InstallationRequested> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
Subquery<Long> InstallationIdsLinkedToEPRsubquery = query.subquery(Long.class);
InstallationIdsLinkedToEPRsubquery.select(root.get(InstallationRequested_.id))
.where(cb.equal(root.get(InstallationRequested_.environmentPermitRequestId), EPRId));
Subquery<InstallationRequested> installationRequestForTheseInstallationIdsWhoAreLinkedToAnotherEPR =
query.subquery(InstallationRequested.class);
installationRequestForTheseInstallationIdsWhoAreLinkedToAnotherEPR
.select(installationRequestForTheseInstallationIdsWhoAreLinkedToAnotherEPR
.from(InstallationRequested.class))
.where(root.get(InstallationRequested_.id).in(InstallationIdsLinkedToEPRsubquery))
.groupBy(root.get(InstallationRequested_.id))
.having(cb.greaterThan(cb.count(cb.literal(1)), cb.literal(1l)));
return cb.exists(installationRequestForTheseInstallationIdsWhoAreLinkedToAnotherEPR);
}
};
}
And my problem is that everything doesn't execute well:
org.springframework.dao.InvalidDataAccessApiUsageException:
org.hibernate.hql.internal.ast.QuerySyntaxException:
unexpected token: where near line 1, column 333
[select count(generatedAlias0) from be.question.domain.model.request.installation.InstallationRequested as generatedAlias0
where exists
(select generatedAlias1 from be.question.domain.model.request.installation.InstallationRequested as generatedAlias1
where generatedAlias0.id in
(select generatedAlias0.id from where generatedAlias0.environmentPermitRequestId=1072487L)
group by generatedAlias0.id having count(1)>1L)
];
Do you have an idea to make my spec work and another way to make the asked task ?
解决方案 I found a solution to my problem.
I fix the specification:
public static Specification<InstallationRequested> existsInstallationRequestLinkedToAnotherEPR(final Long EPRId) {
return new Specification<InstallationRequested>() {
@Override
public Predicate toPredicate(Root<InstallationRequested> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
// subquery to select installation ids
Subquery<Long> InstallationIdsLinkedToEPRsubquery = query.subquery(Long.class);
Root<InstallationRequested> selectIdSubQueryRoot =
InstallationIdsLinkedToEPRsubquery.from(InstallationRequested.class);
InstallationIdsLinkedToEPRsubquery
.select(selectIdSubQueryRoot.get(InstallationRequested_.installation).get(Installation_.id))
.where(cb.equal(selectIdSubQueryRoot.get(InstallationRequested_.environmentPermitRequestId),
EPRId));
// subquery to select count of installation request group by installation id
Subquery<Long> installationRequestForTheseInstallationIdsWhoAreLinkedToAnotherEPR =
query.subquery(Long.class);
Root<InstallationRequested> groupBySubQueryRoot =
installationRequestForTheseInstallationIdsWhoAreLinkedToAnotherEPR
.from(InstallationRequested.class);
Path<Long> installationIdInGroupBySubQuery =
groupBySubQueryRoot.get(InstallationRequested_.installation).get(Installation_.id);
installationRequestForTheseInstallationIdsWhoAreLinkedToAnotherEPR.select(cb.count(cb.literal(1)))
.where(installationIdInGroupBySubQuery.in(InstallationIdsLinkedToEPRsubquery))
.groupBy(installationIdInGroupBySubQuery)
.having(cb.greaterThan(cb.count(cb.literal(1)), cb.literal(1l)));
// returning existing condition on result of the group by
return cb.exists(installationRequestForTheseInstallationIdsWhoAreLinkedToAnotherEPR);
}
};
}
By the way, if someone as a better idea i would be happy to read it ;)
这篇关于在Spring数据计数方法中使用具有规范的组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
