JPA多表分页Specif多表分页Criteri多表分页

最新推荐文章于 2023-02-21 07:52:06 发布

π逻辑

最新推荐文章于 2023-02-21 07:52:06 发布

阅读量1.7k

点赞数

分类专栏： JPA

本文链接：https://blog.csdn.net/weixin_44200656/article/details/104001009

版权

JPA 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

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);