Java Code Examples for javax.persistence.criteria.CriteriaBuilder#and()

public List<String> listUserAttendanceAppealInfoByYearAndMonth(String user, String year, String month)  throws Exception {
	if( user == null || user.isEmpty() ||year == null || month == null || year.isEmpty() || month.isEmpty()  ){
		return null;
	}
	EntityManager em = this.entityManagerContainer().get( AttendanceAppealInfo.class );
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<String> cq = cb.createQuery(String.class);
	Root<AttendanceAppealInfo> root = cq.from( AttendanceAppealInfo.class);
	cq.select( root.get(AttendanceAppealInfo_.id ));
	//一般始终为true, id is not null
	Predicate p = cb.equal( root.get(AttendanceAppealInfo_.empName), user );
	if( StringUtils.isNotEmpty( year  ) ){
		p = cb.and(p, cb.equal( root.get(AttendanceAppealInfo_.yearString), year ));
	}
	if( StringUtils.isNotEmpty( month ) ){
		p = cb.and(p, cb.equal( root.get(AttendanceAppealInfo_.monthString), month ));
	}
	
	return em.createQuery(cq.where(p)).getResultList();
}
 

@Override
public List<Item> findItemByColorOrGrade() {

    CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
    CriteriaQuery<Item> criteriaQuery = criteriaBuilder.createQuery(Item.class);
    Root<Item> itemRoot = criteriaQuery.from(Item.class);

    Predicate predicateForBlueColor = criteriaBuilder.equal(itemRoot.get("color"), "red");
    Predicate predicateForGradeA = criteriaBuilder.equal(itemRoot.get("grade"), "D");
    Predicate predicateForBlueColorAndGradeA = criteriaBuilder.and(predicateForBlueColor, predicateForGradeA);

    Predicate predicateForRedColor = criteriaBuilder.equal(itemRoot.get("color"), "blue");
    Predicate predicateForGradeB = criteriaBuilder.equal(itemRoot.get("grade"), "B");
    Predicate predicateForRedColorAndGradeB = criteriaBuilder.and(predicateForRedColor, predicateForGradeB);

    // final search filter
    Predicate finalPredicate = criteriaBuilder.or(predicateForBlueColorAndGradeA, predicateForRedColorAndGradeB);

    criteriaQuery.where(finalPredicate);

    List<Item> items = entityManager.createQuery(criteriaQuery)
        .getResultList();
    return items;
}
 

/**
 * 查询统计数据中工作责任者身份列表（去重复）
 * @param identities_ok 排除身份
 * @param identities_error 排除身份
 * @return
 * @throws Exception 
 */
public List<String> listAllDistinctResponsibilityIdentity( List<String> identities_ok, List<String> identities_error ) throws Exception {
	EntityManager em = this.entityManagerContainer().get(OkrStatisticReportStatus.class);
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<String> cq = cb.createQuery( String.class );
	Root<OkrStatisticReportStatus> root = cq.from(OkrStatisticReportStatus.class);
	
	Predicate p = cb.isNotNull( root.get( OkrStatisticReportStatus_.id ) );
	if( identities_ok != null && identities_ok.size() > 0 ){
		p = cb.and( p, cb.not(root.get( OkrStatisticReportStatus_.responsibilityIdentity ).in( identities_ok )) );
	}
	if( identities_error != null && identities_error.size() > 0 ){
		p = cb.and( p, cb.not(root.get( OkrStatisticReportStatus_.responsibilityIdentity ).in( identities_error )) );
	}
	cq.distinct(true).select(root.get( OkrStatisticReportStatus_.responsibilityIdentity ));
	return em.createQuery(cq.where(p)).getResultList();
}
 

/**
 * 根据扩展属性名以及项目ID获取一组关联信息
 * @param fieldName
 * @param project
 * @return
 * @throws Exception
 */
public List<ProjectExtFieldRele> listWithFieldNameAndProject( String fieldName, String project ) throws Exception {
	if( StringUtils.isEmpty( fieldName ) ){
		throw new Exception("fieldName can not be empty!");
	}
	if( StringUtils.isEmpty( project ) ){
		throw new Exception("project can not be empty!");
	}
	EntityManager em = this.entityManagerContainer().get(ProjectExtFieldRele.class);
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<ProjectExtFieldRele> cq = cb.createQuery(ProjectExtFieldRele.class);
	Root<ProjectExtFieldRele> root = cq.from(ProjectExtFieldRele.class);
	Predicate p = cb.equal( root.get(ProjectExtFieldRele_.extFieldName), fieldName );
	p = cb.and( p,  cb.equal( root.get(ProjectExtFieldRele_.projectId), project ));
	return em.createQuery(cq.where(p)).getResultList();
}
 

/**
 * 根据顶层组织名称，统计年月，统计顶层组织所有人员早退人次总和
 * @param topUnitNames
 * @param cycleYear
 * @param cycleMonth
 * @return
 * @throws Exception
 */
public Long sumLeaveEarlyCountByTopUnitNamesYearAndMonth( List<String> topUnitNames, String cycleYear, String cycleMonth ) throws Exception{
	if( topUnitNames == null || topUnitNames.size() == 0 ){
		logger.error( new TopUnitNamesEmptyException() );
		return null;
	}	
	EntityManager em = this.entityManagerContainer().get( StatisticUnitForMonth.class);
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<Long> cq = cb.createQuery(Long.class);
	Root<StatisticUnitForMonth> root = cq.from( StatisticUnitForMonth.class);		
	//查询总数
	cq.select( cb.sum( root.get(StatisticUnitForMonth_.leaveEarlyCount) ) );		
	Predicate p = root.get(StatisticUnitForMonth_.topUnitName).in( topUnitNames );
	if( cycleYear == null || cycleYear.isEmpty() ){
		logger.error( new CycleYearEmptyException() );
	}else{
		p = cb.and( p, cb.equal( root.get(StatisticUnitForMonth_.statisticYear), cycleYear));
	}
	if( cycleMonth == null || cycleMonth.isEmpty() ){
		logger.error( new CycleMonthEmptyException() );
	}else{
		p = cb.and( p, cb.equal( root.get(StatisticUnitForMonth_.statisticMonth), cycleMonth));
	}
	return em.createQuery(cq.where(p)).getSingleResult();
}
 

private Wo list(Business business, Wi wi) throws Exception {
	Wo wo = new Wo();
	Unit unit = business.unit().pick(wi.getUnit());
	if (null != unit) {
		EntityManager em = business.entityManagerContainer().get(UnitAttribute.class);
		CriteriaBuilder cb = em.getCriteriaBuilder();
		CriteriaQuery<UnitAttribute> cq = cb.createQuery(UnitAttribute.class);
		Root<UnitAttribute> root = cq.from(UnitAttribute.class);
		Predicate p = cb.equal(root.get(UnitAttribute_.unit), unit.getId());
		p = cb.and(p, cb.equal(root.get(UnitAttribute_.name), wi.getName()));
		List<UnitAttribute> os = em.createQuery(cq.select(root).where(p)).getResultList();
		if (!os.isEmpty()) {
			wo.getAttributeList().addAll(os.get(0).getAttributeList());
		}
	}
	return wo;
}
 

/**
 * 根据条件搜索符合条件的回收站脑图信息条目数量
 * @param folderId  所属目录ID
 * @param name	  脑图的名称（模糊搜索）
 * @param person  脑图的创建者
 * @param unit       脑图创建的组织
 * @param hasShared  是否已经分享过了
 * @return
 * @throws Exception
 */
public Long count( String folderId, String name, String person, String unit, Boolean hasShared ) throws Exception {
	EntityManager em = this.entityManagerContainer().get(MindRecycleInfo.class);
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<Long> cq = cb.createQuery(Long.class);
	Root<MindRecycleInfo> root = cq.from(MindRecycleInfo.class);
	Predicate p = cb.isNotNull( root.get(MindRecycleInfo_.id ) );
	if( folderId != null && !folderId.isEmpty() ){
		p = cb.and( p, cb.equal( root.get( MindRecycleInfo_.id), folderId));
	}
	if( person != null && !person.isEmpty() ){
		p = cb.and( p, cb.equal( root.get( MindRecycleInfo_.creator), person));
	}
	if( unit != null && !unit.isEmpty() ){
		p = cb.and( p, cb.equal( root.get( MindRecycleInfo_.creatorUnit), unit));
	}
	if( hasShared != null ){
		p = cb.and( p, cb.equal( root.get( MindRecycleInfo_.shared), hasShared));
	}
	if( name != null && !name.isEmpty() ){
		p = cb.and( p, cb.like( root.get( MindRecycleInfo_.name ), "%" + name + "%" ));
	}
	cq.select( cb.count( root ) );
	return em.createQuery(cq.where(p)).getSingleResult();
}
 

/**
 * 根据中心工作ID，工作ID和员工姓名来查询已经存在的工作干系人信息
 * 
 * @param centerId
 * @param workId
 * @param employeeName
 * @return
 * @throws Exception
 */
public List<String> listDistinctWorkIdsByWorkAndIdentity(String centerId, String workId, String employeeIdentity,
		String identity, List<String> statuses) throws Exception {
	if (employeeIdentity == null || employeeIdentity.isEmpty()) {
		logger.warn("employeeIdentity is null!");
		return null;
	}
	EntityManager em = this.entityManagerContainer().get(OkrWorkPerson.class);
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<String> cq = cb.createQuery(String.class);
	Root<OkrWorkPerson> root = cq.from(OkrWorkPerson.class);
	cq.distinct(true).select(root.get(OkrWorkPerson_.workId));
	Predicate p = cb.equal(root.get(OkrWorkPerson_.employeeIdentity), employeeIdentity);
	p = cb.and(p, cb.isNotNull(root.get(OkrWorkPerson_.workId)));
	if (centerId != null && !centerId.isEmpty()) {
		p = cb.and(p, cb.equal(root.get(OkrWorkPerson_.centerId), centerId));
	}
	if (workId != null && !workId.isEmpty()) {
		p = cb.and(p, cb.equal(root.get(OkrWorkPerson_.workId), workId));
	}
	if (identity != null && !identity.isEmpty()) {
		p = cb.and(p, cb.equal(root.get(OkrWorkPerson_.processIdentity), identity));
	}
	if (statuses != null && statuses.size() > 0) {
		p = cb.and(p, root.get(OkrWorkPerson_.status).in(statuses));
	}
	return em.createQuery(cq.where(p)).getResultList();
}
 

private Boolean exist(Business business, String fileName, String folderId) throws Exception {
	EntityManager em = business.entityManagerContainer().get(Attachment.class);
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<Long> cq = cb.createQuery(Long.class);
	Root<Attachment> root = cq.from(Attachment.class);
	Predicate p = cb.equal(root.get(Attachment_.name), fileName);
	if (StringUtils.isNotEmpty(folderId)) {
		p = cb.and(p, cb.equal(root.get(Attachment_.folder), folderId));
	} else {
		p = cb.and(p, cb.or(cb.isNull(root.get(Attachment_.folder)), cb.equal(root.get(Attachment_.folder), "")));
	}
	return em.createQuery(cq.select(cb.count(root)).where(p)).getSingleResult() > 0;
}
 

public List<String> listWaitConfirm() throws Exception {
	EntityManager em = this.entityManagerContainer().get(Meeting.class);
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<String> cq = cb.createQuery(String.class);
	Root<Meeting> root = cq.from(Meeting.class);
	Predicate p = cb.greaterThanOrEqualTo(root.get(Meeting_.completedTime), new Date());
	p = cb.and(p, cb.equal(root.get(Meeting_.confirmStatus), ConfirmStatus.wait));
	cq.select(root.get(Meeting_.id)).where(p);
	return em.createQuery(cq).getResultList();
}
 

@Override
public Predicate toPredicate(Root<User> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder cb) {
    Predicate resultPredicate = null;
    List<Predicate> predicates = buildPredicates(root, cb);
    if (!predicates.isEmpty()) {
        resultPredicate = cb.and(predicates.toArray(new Predicate[predicates.size()]));
    }

    return resultPredicate;
}
 

public Long countWithPersonWithEditor(String owner, String person) throws Exception {
	EntityManager em = this.entityManagerContainer().get(Attachment.class);
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<Long> cq = cb.createQuery(Long.class);
	Root<Attachment> root = cq.from(Attachment.class);
	Predicate p = cb.isMember(person, root.get(Attachment_.editorList));
	p = cb.and(p, cb.equal(root.get(Attachment_.person), owner));
	cq.select(cb.count(root)).where(p);
	return em.createQuery(cq).getSingleResult();
}
 

protected Long countWithApplication(Business business, EffectivePerson effectivePerson, String id)
		throws Exception {
	EntityManager em = business.entityManagerContainer().get(TaskCompleted.class);
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<Long> cq = cb.createQuery(Long.class);
	Root<TaskCompleted> root = cq.from(TaskCompleted.class);
	Predicate p = cb.equal(root.get(TaskCompleted_.person), effectivePerson.getDistinguishedName());
	p = cb.and(p, cb.equal(root.get(TaskCompleted_.application), id));
	cq.select(cb.count(root)).where(p);
	return em.createQuery(cq).getSingleResult();
}
 

private List<String> listNumberFormField(Business business, List<String> formIds) throws Exception {
	EntityManager em = business.entityManagerContainer().get(FormField.class);
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<String> cq = cb.createQuery(String.class);
	Root<FormField> root = cq.from(FormField.class);
	Predicate p = root.get(FormField_.form).in(formIds);
	p = cb.and(p, cb.equal(root.get(FormField_.dataType), "number"));
	cq.select(root.get(FormField_.name)).where(p).distinct(true);
	return em.createQuery(cq).getResultList();
}
 

public Long countVoteRecordForSubject( String subjectId, String voteOptionId ) throws Exception {
	EntityManager em = this.entityManagerContainer().get( BBSVoteRecord.class );
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<Long> cq = cb.createQuery(Long.class);
	Root<BBSVoteRecord> root = cq.from( BBSVoteRecord.class);
	Predicate p = cb.equal( root.get( BBSVoteRecord_.subjectId ), subjectId );
	if( StringUtils.isNotEmpty( voteOptionId ) ){
		p = cb.and( p, cb.equal( root.get( BBSVoteRecord_.optionId ), voteOptionId ));
	}
	cq.select( cb.count( root ) );
	return em.createQuery(cq.where(p)).getSingleResult();
}
 

public List<Attachment> listAttachmentWithProject(String project) throws Exception {
	if( StringUtils.isEmpty( project ) ){
		return new ArrayList<Attachment>();
	}
	EntityManager em = this.entityManagerContainer().get(Attachment.class);
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<Attachment> cq = cb.createQuery(Attachment.class);
	Root<Attachment> root = cq.from(Attachment.class);
	Predicate p = cb.equal( root.get(Attachment_.projectId), project );
	p = cb.and( p, cb.equal( root.get(Attachment_.bundleObjType ), "PROJECT"));
	return em.createQuery(cq.where(p)).getResultList();
}
 

public List<String> listWithDate(Date start, Date end) throws Exception {
	EntityManager em = this.entityManagerContainer().get(Meeting.class);
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<String> cq = cb.createQuery(String.class);
	Root<Meeting> root = cq.from(Meeting.class);
	Predicate p = cb.greaterThanOrEqualTo(root.get(Meeting_.startTime), start);
	p = cb.and(p, cb.lessThanOrEqualTo(root.get(Meeting_.startTime), end));
	cq.select(root.get(Meeting_.id)).where(p);
	return em.createQuery(cq).getResultList();
}
 

private Predicate workCompletedPredicate(CriteriaBuilder cb, Root<WorkCompleted> root) throws Exception {
	List<Predicate> ps = new TreeList<>();
	ps.add(this.workCompletedPredicate_application(cb, root));
	ps.add(this.workCompletedPredicate_creator(cb, root));
	ps.add(this.workCompletedPredicate_date(cb, root));
	ps = ListTools.trim(ps, true, false);
	if (ps.isEmpty()) {
		throw new Exception("where is empty.");
	}
	return cb.and(ps.toArray(new Predicate[] {}));
}
 

public static void applyFilter(CriteriaBuilder cb, CriteriaQuery query, List<Filter> filters,
                               Map<String, Expression> selectionMap, Map<String, Class<?>> fieldTypeMap, FilterRelationship filterRs, List<Predicate> predicates, Predicate accessControlPredicate) {
    if (predicates == null) {
        predicates = new LinkedList<>();
    }
    for (Filter filter : filters) {
        Expression filterExpr = validateFilterName(selectionMap, filter);
        switch (FilterOperator.fromCode(filter.getOperator())) {
            case IN:
                processInOperator(cb, predicates, filter, filterExpr);
                break;
            case CONTAINS:
                processContainsOperator(cb, predicates, filter, filterExpr);
                break;
            case EQUAL:
                processEqualsOperator(cb, predicates, filter, filterExpr);
                break;
            case GREATER:
                processGreaterOperator(cb, predicates, filter, filterExpr);
                break;
            case LESS:
                processLessOperator(cb, predicates, filter, filterExpr);
                break;
            case NOT_EQUAL:
                processNotEqualsOperator(cb, predicates, filter, filterExpr);
                break;
            case NOT_NULL:
                predicates.add(cb.isNotNull(filterExpr));
                break;
            case NULL:
                predicates.add(cb.isNull(filterExpr));
                break;
            default:
                throw new WecubeCoreException(String.format("Filter operator [%s] is unsupportted.", filter.getOperator()));
        }
    }

    Predicate mergedFilterPredicate;
    if (predicates.size() > 0) {
        if (FilterRelationship.OR.equals(filterRs)) {
            mergedFilterPredicate = cb.or(predicates.toArray(new Predicate[0]));
        } else {
            mergedFilterPredicate = cb.and(predicates.toArray(new Predicate[0]));
        }
    } else {
        mergedFilterPredicate = cb.conjunction();
    }

    if (accessControlPredicate != null) {
        query.where(cb.and(mergedFilterPredicate, accessControlPredicate));
    } else {
        query.where(mergedFilterPredicate);
    }
}
 

private <T> Query createFinderQuery(final EntityManager entityManager, final String methodName, final Class<T> entityType, final Object[] args) {
    final List<String> conditions = parseMethodName(methodName);

    final EntityType<T> et = entityManager.getMetamodel().entity(entityType);
    final CriteriaBuilder cb = entityManager.getCriteriaBuilder();

    CriteriaQuery<Object> query = cb.createQuery();
    final Root<T> from = query.from(entityType);
    query = query.select(from);

    int i = 0;
    Predicate where = null;
    for (final String condition : conditions) {
        final SingularAttribute<? super T, ?> attribute = et.getSingularAttribute(condition);
        final Path<?> path = from.get(attribute);
        final Class<?> javaType = attribute.getType().getJavaType();

        final Predicate currentClause;
        if (javaType.equals(String.class)) {
            currentClause = cb.like((Expression<String>) path, (String) args[i++]);
        } else if (Number.class.isAssignableFrom(javaType) || javaType.isPrimitive()) {
            currentClause = cb.equal(path, args[i++]);
        } else {
            LOGGER.warning("field " + condition + " not found, ignoring");
            continue;
        }

        if (where == null) {
            where = currentClause;
        } else {
            where = cb.and(where, currentClause);
        }
    }

    if (where != null) {
        query = query.where(where);
    }

    // pagination
    final TypedQuery<?> emQuery = entityManager.createQuery(query);
    if (args != null && args.length == conditions.size() + 2
        && isInt(args[args.length - 2].getClass()) && isInt(args[args.length - 1].getClass())) {
        final int first = (Integer) args[args.length - 2];
        final int max = (Integer) args[args.length - 1];

        emQuery.setFirstResult(first);
        emQuery.setMaxResults(max);
    }

    return emQuery;
}