想实现的语句如下
select * from v_taskrecord vtaskrecor0_
where (vtaskrecor0_.majorengineerid=?
or vtaskrecor0_.otherengineerid=?
or vtaskrecor0_.otherengineerid like ?
or vtaskrecor0_.otherengineerid like ?
or vtaskrecor0_.otherengineerid like ?)
and (cast(vtaskrecor0_.taskstate as signed) in (4 , 5 , 6 , 3))
and ...
实现
@Override
public PageResultVO<List<VTaskRecordPojo>> applist(HttpServletRequest request,Map<String, Object> map) throws InvocationTargetException, IllegalAccessException {
PageResultVO<List<VTaskRecordPojo>> result=new PageResultVO<List<VTaskRecordPojo>>();
if (map!=null) {
TaskSelectVO task = new TaskSelectVO();
BeanUtils.populate(task, map);
Assert.notNull(task);
SimpleDateFormat df=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Sort sort = null;
if ("1".equals(task.getTaskstate())||"2".equals(task.getTaskstate())){//
Sort.Order order=new Sort.Order(Sort.Direction.DESC,"urgentlevel");
Sort.Order order1=new Sort.Order(Sort.Direction.DESC,"xiafatime");
List list=new ArrayList();
list.add(order);
list.add(order1);
sort = new Sort(list);
}
Pageable pageable = new PageRequest(task.getPage()-1,task.getLimit(),sort);
Specification<VTaskRecordPojo> specification=new Specification<VTaskRecordPojo>() {
@Override
public Predicate toPredicate(Root<VTaskRecordPojo> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
List<Predicate> list = new ArrayList<Predicate>();//--------------//
List<Predicate> orlist = new ArrayList<Predicate>();//-------------//
if (StringUtils.isNotBlank(task.getTasking())){
if ("任务执行".equals(task.getTasking())){//
List<Integer> states=new ArrayList<Integer>();
states.add(4);states.add(5);states.add(6);states.add(3);
list.add(cb.and(root.get("taskstate").as(int.class).in(states)));//-------------//
orlist.add(cb.equal(root.get("majorengineerid"),username));//-------------//
orlist.add(cb.equal(root.get("otherengineerid"),username));//-------------//
orlist.add(cb.like(root.get("otherengineerid").as(String.class),"%," + username + ",%"));//-------------//
orlist.add(cb.like(root.get("otherengineerid").as(String.class), username + ",%"));//-------------//
orlist.add(cb.like(root.get("otherengineerid").as(String.class),"%," + username));//-------------//
}
}
Predicate predicateAnd = cb.and(list.toArray(new Predicate[list.size()]));//-------------//
predicateAnd = cb.and(predicateAnd);//-------------//
Predicate predicateOr = cb.or(orlist.toArray(new Predicate[orlist.size()]));//-------------//
predicateOr = cb.and(predicateOr);//-------------//
query.where(predicateOr,predicateAnd);//-------------//
return query.getRestriction();//-------------//
// Predicate[] p = new Predicate[list.size()];
// return cb.and(list.toArray(p));
}
};
Page<VTaskRecordPojo> taskRecordsPage=vtaskRecordRepository.findAll(specification,pageable);
String jsonString= JSONObject.toJSONString(taskRecordsPage);
JSONObject jsonObject=JSONObject.parseObject(jsonString);
JSONArray jsonArray1 = jsonObject.getJSONArray("content");
List<VTaskRecordPojo> taskRecordPojos = jsonArray1.toJavaList(VTaskRecordPojo.class);
int total= (int) vtaskRecordRepository.count(specification);
result.setTotalnum(total);
result.setData(taskRecordPojos);
result.setCode(200);
result.setMsg("查询成功");
}else {
result.setCode(-1);
result.setMsg("传参不能为空");
}
return result;
}
进阶
select * from Table
where (A and B) or (C and D) or (E or F or G) and H and I
没研究出来,有人教一下不