JPA的Predicate多条件设置
详细代码
@Transactional
public boolean incrNum(String matterId,String termId){
Matter matter = matterDao.findOne(matterId);
if(matter == null) return false;
DeviceInfo deviceInfo = deviceInfoDao.findByTermId(termId);
if(deviceInfo == null)return false;
MatterBrowseReport old = matterBrowseReportDao.findOne(new Specification<MatterBrowseReport>() {
Predicate matterIdcon = null;
Predicate termIdCon = null;
Predicate visitDateCon = null;
@Override
public Predicate toPredicate(Root<MatterBrowseReport> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
if (StringUtils.isNotBlank(matterId)) {
matterIdcon = cb.equal(root.get("matterId"), matterId);
}
if (StringUtils.isNotBlank(termId)) {
termIdCon = cb.equal(root.get("termId"), termId);
}
visitDateCon = cb.equal(root.get("visitDate"), DateUtil.format(new Date(), "yyyy-MM-dd"));
return cb.and(matterIdcon,termIdCon,visitDateCon);
}
});
if(old == null) {
old = new MatterBrowseReport();
old.setId(IDKeyUtil.getId());
old.setMatterId(matterId);
old.setVisitNum(1l);
old.setTermId(termId);
old.setMatterName(matter.getMatterName());
old.setVisitDate(DateUtil.format(new Date(), "yyyy-MM-dd"));
old.setDeptId(deviceInfo.getDeptId());
old.setDeptName(deviceInfo.getDeptName());
matterBrowseReportDao.save(old);
}else {
old.setVisitNum(old.getVisitNum() + 1);
matterBrowseReportDao.saveAndFlush(old);
}
return true;
}
多表关联查询
DetachedCriteria detachedCriteria = DetachedCriteria.forClass(SalesRecordView.class, "V");//主表
if(org.springframework.util.StringUtils.hasText(queryForm.getSyssn())) {
detachedCriteria.add(Restrictions.eq("V.id", queryForm.getSyssn()));
}
if(org.springframework.util.StringUtils.hasText(queryForm.getCertNum())) {
detachedCriteria.add(Restrictions.eq("V.srcSN", queryForm.getCertNum()));
detachedCriteria.addOrder(org.hibernate.criterion.Order.desc("bookTime"));
}
if(org.springframework.util.StringUtils.hasText(queryForm.getMobilnum())) {
detachedCriteria.add(Restrictions.eq("V.cellTel", queryForm.getMobilnum()));
}
if(org.springframework.util.StringUtils.hasText(queryForm.getFetchCode())) {
detachedCriteria.add(Restrictions.like("V.fetchCode", queryForm.getFetchCode(),MatchMode.START));
}
if (org.springframework.util.StringUtils.hasText(queryForm.getOtherUserName()) || org.springframework.util.StringUtils.hasText(queryForm.getOtherUserPhone())
|| org.springframework.util.StringUtils.hasText(queryForm.getOtherUserIdCode())
|| org.springframework.util.StringUtils.hasText(queryForm.getBarcode())) {
DetachedCriteria subCriteria = DetachedCriteria.forClass(SalesDetailsView.class, "D");//子表
ProjectionList pList = Projections.projectionList();
pList.add(Projections.property("D.owner.id").as("id"));//实体属性
subCriteria.setProjection(pList);//在子表中添加关联列
//添加子表查询条件
if (org.springframework.util.StringUtils.hasText(queryForm.getOtherUserName()))
subCriteria.add(Restrictions.like("D.userName", queryForm.getOtherUserName(),MatchMode.ANYWHERE));
if (org.springframework.util.StringUtils.hasText(queryForm.getOtherUserPhone()))
subCriteria.add(Restrictions.like("D.userPhone", queryForm.getOtherUserPhone(),MatchMode.ANYWHERE));
if (org.springframework.util.StringUtils.hasText(queryForm.getOtherUserIdCode()))
subCriteria.add(Restrictions.like("D.idCode", queryForm.getOtherUserIdCode(),MatchMode.ANYWHERE));
if (org.springframework.util.StringUtils.hasText(queryForm.getBarcode())) {
subCriteria.add(Restrictions.like("D.barCode", queryForm.getBarcode(),MatchMode.ANYWHERE));
}
subCriteria.addOrder(org.hibernate.criterion.Order.desc("D.buyTime"));
subCriteria.setResultTransformer(DetachedCriteria.DISTINCT_ROOT_ENTITY);
detachedCriteria.add(Property.forName("id").in(subCriteria));//关联子表条件查询结果的主表结果
}
List<SalesRecordView> views = this.getSalesRecordViewDAO().findByCriteria(detachedCriteria, 0, 200);//行数
分页模糊查询orderby
public Page<User> findForPage(User user,int page, int size) {
PageRequest pageable = new PageRequest(page, size);
return userDao.findAll(new Specification<User>() {
Predicate pd = null;
@Override
public Predicate toPredicate(Root<User> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
if (StringUtils.hasText(user.getName())) {
pd = cb.like(root.get("name").as(String.class), "%"+user.getName()+"%");
cb.and(pd);
}
query.where(pd);
query.orderBy(cb.desc(root.get("updateTime").as(Date.class)));
return query.getRestriction();
}
},pageable);
}
分组统计countgroup
String strStartDate = DateUtil.dateToString(startDate, "yyyy-MM-dd");
Date endDate = DateUtil.dateAdd(startDate, 1);
DetachedCriteria dc2 = DetachedCriteria.forClass(SalesDetails.class);
dc2.add(Restrictions.eq("validDate", startDate));
ProjectionList ProjectionList = Projections.projectionList();
ProjectionList.add(Projections.property("tkCode"));
ProjectionList.add(Projections.count("validDate"),"count");
ProjectionList.add(Projections.groupProperty("tkCode"));
dc2.setProjection(ProjectionList);
List<Object[]> dateCounts = this.getTicketDAO().getHibernateTemplate().findByCriteria(dc2);