mysql - Spring Boot:如何使用谓词创建涉及“加入”和“分组依据”的动态查询
问题描述
我的帖子请求正文会像
{
"queryCondition":[
{
"filter":"status",
"filterlist":["Closed","New","Resolved"...]
},
{
"filter":"assigned_team",
"filterlist":["A","B","C"...]
},
{
"filter":"assigned_to",
"filterlist":["ram","govind","ajith"...]
},
{
"filter":"duration",
"filterlist":["2020-02-01","2020-05-01"....]
}
....
....
],
"durationField":"created_date"
}
我动态收到了构建此查询所需的列(过滤器)和值(过滤器列表)。
SELECT * FROM tickets
WHERE ticket_id IN (SELECT ticket_id FROM Tickets WHERE created_date >= '2020-02-01') AND created_date '2020-05-01'
AND status IN ('Closed','Resolved','New')
AND assigned_team IN ('A' , 'B', 'C')
AND assigned_to IN ('ram','govind','ajith');
我使用 Predicate 动态构建了这个查询,它工作正常。
@Override
public List<Tickets> conditionedQuery(QueryCondition queryCondition) {
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Tickets> query = cb.createQuery(Tickets.class);
Root<Tickets> ticket = query.from(Tickets.class);
List<Predicate> predicatessub = new ArrayList<>();
for(FilterConditions fc:queryCondition.getQueryCondition()) {
if(fc.getFilter().equals("duration")) {
Predicate ps = cb.greaterThanOrEqualTo(ticket.get(queryCondition.getDurationField()), fc.getFilterlist()[0]);
Predicate pe = cb.lessThan(ticket.get(queryCondition.getDurationField()), fc.getFilterlist()[1]);
predicatessub.add(cb.and(ps,pe));
}else
{
List<Predicate> predicates = new ArrayList<>();
for(int i=0; i<fc.getFilterlist().length; i++) {
Predicate p = cb.equal(ticket.get(fc.getFilter()),fc.getFilterlist()[i]);
predicates.add(p);
}
predicatessub.add(cb.or(predicates.toArray(new Predicate[predicates.size()])));
}
}
query.select(ticket)
.where(cb.and(predicatessub.toArray(new Predicate[predicatessub.size()])));
return entityManager.createQuery(query)
.getResultList();
}
QueryCondition.class
public class QueryCondition {
private List<FilterConditions> filterCondition;
private String durationField;
}
FilterConditions.class
public class FilterConditions {
private String filter;
private String[] filterlist;
}
现在我想构建一个涉及联接和分组依据的更复杂的查询。下面是我想使用谓词构建的示例查询。
SELECT
YEAR(pt.created_date),
MONTH(pt.created_date),
pt.assigned_team,
COUNT(tk.ticket_id)
FROM
(SELECT
*
FROM
tickets
WHERE
ticket_id IN (SELECT
ticket_id
FROM
Tickets
WHERE
resolved_date >= '2020-02-01')
AND resolved_date < '2020-05-01'
and assigned_team IN ('A' , 'B', 'C')) pt
LEFT JOIN
(SELECT
*
FROM
tickets
WHERE
status IN ('Closed','Resolved','New')
AND assigned_to IN ('ram','govind','ajith')) tk ON tk.ticket_id = pt.ticket_id
GROUP BY YEAR(pt.created_date) , MONTH(pt.created_date), pt.assigned_team order by
pt.assigned_team,YEAR(pt.created_date),MONTH(pt.created_date) asc;
请告知如何使用 Predicate 来实现这一点,或者有没有比 Predicate 更简单的方法。
解决方案
推荐阅读
- python - 绘制的图像显示为灰色
- sql - 列出 msi 文件中的表?
- javascript - 单击文本框文本弹出菜单应打开文本框
- android - 滑动期间无法更新 ViewPager 中的菜单项状态
- python - 制作游戏,我无法导入背景图片或精灵
- node.js - 浏览器外的AngularJS单元测试
- python - 你如何修复这个for循环来验证用户名?
- java - 如何编写为所有参数添加 esapi 过滤器的 slf4j 记录器
- javascript - HTML Canvas - 无法获取未定义或空引用的属性“getElementById”
- intellij-idea - Alt+Enter 在 IntelliJ 社区版中停止显示创建方法