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

private Predicate buildAccessControlPredicate(CriteriaBuilder criteriaBuilder, Map<String, FieldInfo> selFieldMap, Map<String, Expression> attributeMap) {
    if (selFieldMap.isEmpty())
        return criteriaBuilder.conjunction();

    Set<Integer> processedCiTypeIds = Sets.newHashSet();
    List<Predicate> predicates = selFieldMap.entrySet().stream().map(entry -> {
        FieldInfo fieldInfo = entry.getValue();
        int ciTypeId = fieldInfo.getCiTypeId();
        if (processedCiTypeIds.contains(ciTypeId))
            return null;
        processedCiTypeIds.add(ciTypeId);

        return buildAccessControlPredicate(ciTypeId, criteriaBuilder, attributeMap, true);
    }).filter(predicate -> predicate != null).collect(Collectors.toList());

    return criteriaBuilder.and(predicates.toArray(new Predicate[0]));
}
 

/**
 * 定时进行轮询保证旧数据能定期进行更新
 */
private List<String> listUpdateWork(Business business) throws Exception {
	EntityManager em = business.entityManagerContainer().get(Work.class);
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<String> cq = cb.createQuery(String.class);
	Root<Work> root = cq.from(Work.class);
	Predicate p = cb.conjunction();
	String sequence = Arguments.getCrawlUpdateWork();
	if (StringUtils.isNotEmpty(sequence)) {
		p = cb.and(cb.lessThan(root.get(Work.sequence_FIELDNAME), sequence));
	}
	cq.select(root.get(Work_.id)).where(p).orderBy(cb.desc(root.get(Work_.sequence)));
	Integer count = Config.query().getCrawlWork().getCount() / 2;
	List<String> os = em.createQuery(cq).setMaxResults(count).getResultList();
	if (os.size() == count) {
		Arguments.setCrawlUpdateWork(os.get(os.size() - 1));
	} else {
		Arguments.setCrawlUpdateWork("");
	}
	return os;
}
 

public List<String> list(EffectivePerson effectivePerson) throws Exception {
	EntityManager em = this.entityManagerContainer().get(Portal.class);
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<String> cq = cb.createQuery(String.class);
	Root<Portal> root = cq.from(Portal.class);
	Predicate p = cb.conjunction();
	if (effectivePerson.isNotManager() && (!this.business().organization().person().hasRole(effectivePerson,
			OrganizationDefinition.PortalManager))) {
		List<String> identities = this.business().organization().identity()
				.listWithPerson(effectivePerson.getDistinguishedName());
		List<String> units = this.business().organization().unit()
				.listWithPersonSupNested(effectivePerson.getDistinguishedName());
		p = cb.equal(root.get(Portal_.creatorPerson), effectivePerson.getDistinguishedName());
		p = cb.or(p, cb.isMember(effectivePerson.getDistinguishedName(), root.get(Portal_.controllerList)));
		p = cb.or(cb.and(cb.isEmpty(root.get(Portal_.availableIdentityList)),
				cb.isEmpty(root.get(Portal_.availableUnitList))));
		p = cb.or(p, root.get(Portal_.availableIdentityList).in(identities));
		p = cb.or(p, root.get(Portal_.availableUnitList).in(units));
	}
	cq.select(root.get(Portal_.id)).where(p).distinct(true);
	return em.createQuery(cq).getResultList();
}
 

/**
 * 
 * 从Process中获取可以启动的Process的application.
 */
private List<String> listFromProcess(Business business, EffectivePerson effectivePerson, List<String> roles,
		List<String> identities, List<String> units) throws Exception {
	EntityManager em = business.entityManagerContainer().get(Process.class);
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<String> cq = cb.createQuery(String.class);
	Root<Process> root = cq.from(Process.class);
	Predicate p = cb.conjunction();
	if (effectivePerson.isNotManager() && (!business.organization().person().hasRole(effectivePerson,
			OrganizationDefinition.Manager, OrganizationDefinition.ProcessPlatformManager))) {
		p = cb.and(cb.isEmpty(root.get(Process_.startableIdentityList)),
				cb.isEmpty(root.get(Process_.startableUnitList)));
		p = cb.or(p, cb.isMember(effectivePerson.getDistinguishedName(), root.get(Process_.controllerList)));
		p = cb.or(p, cb.equal(root.get(Process_.creatorPerson), effectivePerson.getDistinguishedName()));
		if (ListTools.isNotEmpty(identities)) {
			p = cb.or(p, root.get(Process_.startableIdentityList).in(identities));
		}
		if (ListTools.isNotEmpty(units)) {
			p = cb.or(p, root.get(Process_.startableUnitList).in(units));
		}
	}
	cq.select(root.get(Process_.application)).distinct(true).where(p);
	return em.createQuery(cq).getResultList();
}
 

@Override
public javax.persistence.criteria.Predicate createPredicate(From<?, ?> from, CriteriaBuilder criteriaBuilder, String attributeName) {
    Expression<?> expression = from.get(attributeName);

    if (values.isEmpty()) {
        return addNullCase ? expression.isNull() : criteriaBuilder.conjunction();
    } else if (isBasicFilter()) {
        return super.createPredicate(from, criteriaBuilder, attributeName);
    }

    javax.persistence.criteria.Predicate predicate;
    if (isBooleanComparison) {
        predicate = expression.in(booleanValues);
    } else {
        predicate = expression.as(String.class).in(values);
    }
    if (addNullCase) predicate = criteriaBuilder.or(predicate, expression.isNull());

    return predicate;
}
 

List<String> listEditableWithPortalCategory(Business business, EffectivePerson effectivePerson,
		String portalCategory) throws Exception {
	EntityManager em = business.entityManagerContainer().get(Portal.class);
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<String> cq = cb.createQuery(String.class);
	Root<Portal> root = cq.from(Portal.class);
	Predicate p = cb.conjunction();
	if (!business.isPortalManager(effectivePerson)) {
		p = cb.isMember(effectivePerson.getDistinguishedName(), root.get(Portal_.controllerList));
		p = cb.or(p, cb.equal(root.get(Portal_.creatorPerson), effectivePerson.getDistinguishedName()));
	}
	p = cb.and(p, cb.equal(root.get(Portal_.portalCategory), Objects.toString(portalCategory, "")));
	cq.select(root.get(Portal_.id)).where(p).distinct(true);
	List<String> list = em.createQuery(cq.select(root.get(Portal_.id)).where(p)).getResultList();
	return list;
}
 

/**
 * 定时进行轮询保证旧数据能定期进行更新
 */
private List<String> listUpdateWork(Business business) throws Exception {
	EntityManager em = business.entityManagerContainer().get(Work.class);
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<String> cq = cb.createQuery(String.class);
	Root<Work> root = cq.from(Work.class);
	Predicate p = cb.conjunction();
	String sequence = Arguments.getCrawlUpdateWork();
	if (StringUtils.isNotEmpty(sequence)) {
		p = cb.and(cb.lessThan(root.get(Work.sequence_FIELDNAME), sequence));
	}
	cq.select(root.get(Work_.id)).where(p).orderBy(cb.desc(root.get(Work_.sequence)));
	Integer count = Config.query().getCrawlWork().getCount() / 2;
	List<String> os = em.createQuery(cq).setMaxResults(count).getResultList();
	if (os.size() == count) {
		Arguments.setCrawlUpdateWork(os.get(os.size() - 1));
	} else {
		Arguments.setCrawlUpdateWork("");
	}
	return os;
}
 

List<String> listEditableWithPortalCategory(Business business, EffectivePerson effectivePerson,
		String portalCategory) throws Exception {
	EntityManager em = business.entityManagerContainer().get(Portal.class);
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<String> cq = cb.createQuery(String.class);
	Root<Portal> root = cq.from(Portal.class);
	Predicate p = cb.conjunction();
	if (!business.isPortalManager(effectivePerson)) {
		p = cb.isMember(effectivePerson.getDistinguishedName(), root.get(Portal_.controllerList));
		p = cb.or(p, cb.equal(root.get(Portal_.creatorPerson), effectivePerson.getDistinguishedName()));
	}
	p = cb.and(p, cb.equal(root.get(Portal_.portalCategory), Objects.toString(portalCategory, "")));
	cq.select(root.get(Portal_.id)).where(p).distinct(true);
	List<String> list = em.createQuery(cq.select(root.get(Portal_.id)).where(p)).getResultList();
	return list;
}
 

private List<String> list(Business business, EffectivePerson effectivePerson, List<String> identities,
		List<String> units) throws Exception {
	EntityManager em = business.entityManagerContainer().get(Query.class);
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<String> cq = cb.createQuery(String.class);
	Root<Query> root = cq.from(Query.class);
	Predicate p = cb.conjunction();
	if (effectivePerson.isNotManager() && (!business.organization().person().hasRole(effectivePerson,
			OrganizationDefinition.Manager, OrganizationDefinition.QueryManager))) {
		p = cb.and(cb.isEmpty(root.get(Query_.availableIdentityList)),
				cb.isEmpty(root.get(Query_.availableUnitList)));
		p = cb.or(p, cb.isMember(effectivePerson.getDistinguishedName(), root.get(Query_.controllerList)));
		p = cb.or(p, cb.equal(root.get(Query_.creatorPerson), effectivePerson.getDistinguishedName()));
		if (ListTools.isNotEmpty(identities)) {
			p = cb.or(p, root.get(Query_.availableIdentityList).in(identities));
		}
		if (ListTools.isNotEmpty(units)) {
			p = cb.or(p, root.get(Query_.availableUnitList).in(units));
		}
	}
	cq.select(root.get(Query_.id)).distinct(true).where(p);
	return em.createQuery(cq).getResultList();
}
 

public List<String> listEditable(EffectivePerson effectivePerson) throws Exception {
	EntityManager em = this.business().entityManagerContainer().get(TemplatePage.class);
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<String> cq = cb.createQuery(String.class);
	Root<TemplatePage> root = cq.from(TemplatePage.class);
	Predicate p = cb.conjunction();
	if (effectivePerson.isNotManager()) {
		p = cb.isMember(effectivePerson.getDistinguishedName(), root.get(TemplatePage_.controllerList));
		p = cb.or(p, cb.equal(root.get(TemplatePage_.creatorPerson), effectivePerson.getDistinguishedName()));
	}
	cq.select(root.get(TemplatePage_.id)).where(p).distinct(true);
	List<String> list = em.createQuery(cq.select(root.get(TemplatePage_.id)).where(p)).getResultList();
	return list;
}
 

public List<String> listEditable(EffectivePerson effectivePerson) throws Exception {
	EntityManager em = this.business().entityManagerContainer().get(TemplatePage.class);
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<String> cq = cb.createQuery(String.class);
	Root<TemplatePage> root = cq.from(TemplatePage.class);
	Predicate p = cb.conjunction();
	if (effectivePerson.isNotManager()) {
		p = cb.isMember(effectivePerson.getDistinguishedName(), root.get(TemplatePage_.controllerList));
		p = cb.or(p, cb.equal(root.get(TemplatePage_.creatorPerson), effectivePerson.getDistinguishedName()));
	}
	cq.select(root.get(TemplatePage_.id)).where(p).distinct(true);
	List<String> list = em.createQuery(cq.select(root.get(TemplatePage_.id)).where(p)).getResultList();
	return list;
}
 

List<String> listEditable(Business business, EffectivePerson effectivePerson) throws Exception {
	EntityManager em = business.entityManagerContainer().get(Portal.class);
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<String> cq = cb.createQuery(String.class);
	Root<Portal> root = cq.from(Portal.class);
	Predicate p = cb.conjunction();
	if (!business.isPortalManager(effectivePerson)) {
		p = cb.isMember(effectivePerson.getDistinguishedName(), root.get(Portal_.controllerList));
		p = cb.or(p, cb.equal(root.get(Portal_.creatorPerson), effectivePerson.getDistinguishedName()));
	}
	cq.select(root.get(Portal_.id)).where(p).distinct(true);
	List<String> list = em.createQuery(cq.select(root.get(Portal_.id)).where(p)).getResultList();
	return list;
}
 

/**
 * Builds the query to delete {@link com.box.l10n.mojito.entity.TMTextUnitCurrentVariant}s that will be rolled back
 *
 * @param tmId            ID of the TM the {@link TMTextUnitCurrentVariant}s to be rolled back should belong to
 * @param extraParameters Extra parameters to filter what to rollback
 * @return The delete query
 */
protected Query buildDeleteQuery(Long tmId, CurrentVariantRollbackParameters extraParameters) {

    logger.trace("Building the delete tmTextUnitCurrentVariants query");

    CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
    CriteriaDelete<TMTextUnitCurrentVariant> deleteCriteria = criteriaBuilder.createCriteriaDelete(TMTextUnitCurrentVariant.class);
    Root<TMTextUnitCurrentVariant> root = deleteCriteria.from(TMTextUnitCurrentVariant.class);

    Predicate whereClause = criteriaBuilder.conjunction();

    Predicate tmPredicate = criteriaBuilder.equal(root.get(TMTextUnitCurrentVariant_.tm), tmId);
    whereClause = criteriaBuilder.and(whereClause, tmPredicate);

    List<Long> localeIdsToRollback = extraParameters.getLocaleIds();
    if (localeIdsToRollback != null && !localeIdsToRollback.isEmpty()) {
        Predicate localesPredicate = criteriaBuilder.isTrue(root.get(TMTextUnitCurrentVariant_.locale).in(localeIdsToRollback));
        whereClause = criteriaBuilder.and(whereClause, localesPredicate);
    }

    List<Long> tmTextUnitIdsToRollback = extraParameters.getTmTextUnitIds();
    if (tmTextUnitIdsToRollback != null && !tmTextUnitIdsToRollback.isEmpty()) {
        Predicate tmTextUnitPredicate = criteriaBuilder.isTrue(root.get(TMTextUnitCurrentVariant_.tmTextUnit).in(tmTextUnitIdsToRollback));
        whereClause = criteriaBuilder.and(whereClause, tmTextUnitPredicate);
    }

    deleteCriteria.where(whereClause);

    return entityManager.createQuery(deleteCriteria);
}
 

@Override
public Predicate toPredicate(Root<T> root, CriteriaQuery<?> query, CriteriaBuilder builder) {
    if (!criterions.isEmpty()) {
        List<Predicate> predicates = new ArrayList<Predicate>();
        for (Criterion c : criterions) {
            predicates.add(c.toPredicate(root, query, builder));
        }
        // 将所有条件用 and 联合起来
        if (predicates.size() > 0) {
            return builder.and(predicates.toArray(new Predicate[predicates.size()]));
        }
    }
    return builder.conjunction();
}
 

private List<TaskCompleted> list(EffectivePerson effectivePerson, Business business, Integer adjustPage,
		Integer adjustPageSize, Wi wi) throws Exception {
	EntityManager em = business.entityManagerContainer().get(TaskCompleted.class);
	List<String> person_ids = business.organization().person().list(wi.getCredentialList());
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<TaskCompleted> cq = cb.createQuery(TaskCompleted.class);
	Root<TaskCompleted> root = cq.from(TaskCompleted.class);
	Predicate p = cb.conjunction();
	if (ListTools.isNotEmpty(wi.getApplicationList())) {
		p = cb.and(p, root.get(TaskCompleted_.application).in(wi.getApplicationList()));
	}
	if (ListTools.isNotEmpty(wi.getProcessList())) {
		p = cb.and(p, root.get(TaskCompleted_.process).in(wi.getProcessList()));
	}
	if(DateTools.isDateTimeOrDate(wi.getStartTime())){
		p = cb.and(p, cb.greaterThan(root.get(TaskCompleted_.startTime), DateTools.parse(wi.getStartTime())));
	}
	if(DateTools.isDateTimeOrDate(wi.getEndTime())){
		p = cb.and(p, cb.lessThan(root.get(TaskCompleted_.startTime), DateTools.parse(wi.getEndTime())));
	}
	if (ListTools.isNotEmpty(person_ids)) {
		p = cb.and(p, root.get(TaskCompleted_.person).in(person_ids));
	}
	if (ListTools.isNotEmpty(wi.getCreatorUnitList())) {
		p = cb.and(p, root.get(TaskCompleted_.creatorUnit).in(wi.getCreatorUnitList()));
	}
	if (ListTools.isNotEmpty(wi.getWorkList())) {
		p = cb.and(p, root.get(TaskCompleted_.work).in(wi.getWorkList()));
	}
	if (ListTools.isNotEmpty(wi.getJobList())) {
		p = cb.and(p, root.get(TaskCompleted_.job).in(wi.getJobList()));
	}
	if (ListTools.isNotEmpty(wi.getStartTimeMonthList())) {
		p = cb.and(p, root.get(TaskCompleted_.startTimeMonth).in(wi.getStartTimeMonthList()));
	}
	if (ListTools.isNotEmpty(wi.getActivityNameList())) {
		p = cb.and(p, root.get(TaskCompleted_.activityName).in(wi.getActivityNameList()));
	}
	if (StringUtils.isNoneBlank(wi.getKey())) {
		String key = StringTools.escapeSqlLikeKey(wi.getKey());
		p = cb.and(p,cb.like(root.get(TaskCompleted_.title), "%" + key + "%", StringTools.SQL_ESCAPE_CHAR));
	}
	cq.select(root).where(p).orderBy(cb.desc(root.get(TaskCompleted_.startTime)));
	return em.createQuery(cq).setFirstResult((adjustPage - 1) * adjustPageSize).setMaxResults(adjustPageSize)
			.getResultList();
}
 

public <T extends JpaObject> ActionResult<List<T>> standardListPrev(Class<T> cls, String id, Integer count,
		String sequenceField, String order, Predicate predicate) throws Exception {
	try (EntityManagerContainer emc = EntityManagerContainerFactory.instance().create()) {
		// 先获取上一页最后一条的sequence值，如果有值的话，以此sequence值作为依据取后续的count条数据
		Object sequence = null;
		if (!StringUtils.equalsIgnoreCase(id, EMPTY_SYMBOL)) {
			T t = emc.fetch(id, cls, ListTools.toList(sequenceField));
			if (null != t) {
				sequence = PropertyUtils.getProperty(t, sequenceField);
			}
		}
		EntityManager em = emc.get(cls);
		CriteriaBuilder cb = em.getCriteriaBuilder();
		CriteriaQuery<T> cq = cb.createQuery(cls);
		Root<T> root = cq.from(cls);

		if (null == predicate) {
			predicate = cb.conjunction();
		}

		if (StringUtils.equalsIgnoreCase(order, DESC)) {
			cq.where(null == sequence ? predicate
					: cb.and(predicate, cb.greaterThan(root.get(sequenceField), (Comparable) sequence)));
			cq.orderBy(cb.asc(root.get(sequenceField)));
		} else {
			cq.where(null == sequence ? predicate
					: cb.and(predicate, cb.lessThan(root.get(sequenceField), (Comparable) sequence)));
			cq.orderBy(cb.desc(root.get(sequenceField)));
		}

		List<T> os = em.createQuery(cq.select(root)).setMaxResults(Math.max(Math.min(count, list_max), list_min))
				.getResultList();

		List<T> wos = new ArrayList<>();

		for (int i = os.size() - 1; i >= 0; i--) {
			wos.add(os.get(i));
		}

		ActionResult<List<T>> result = new ActionResult<>();
		result.setData(wos);
		// 设置查询结果的总条目数
		result.setCount(this.count(emc, cls, predicate));
		return result;
	}
}
 

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 GreaterEqual:
            processGreaterEqualOperator(cb, predicates, filter, filterExpr);
            break;
        case Less:
            processLessOperator(cb, predicates, filter, filterExpr);
            break;
        case LessEqual:
            processLessEqualOperator(cb, predicates, filter, filterExpr);
            break;
        case NotEqual:
            processNotEqualsOperator(cb, predicates, filter, filterExpr);
            break;
        case NotNull:
            predicates.add(cb.isNotNull(filterExpr));
            break;
        case Null:
            predicates.add(cb.isNull(filterExpr));
            break;
        case LIKE:
            predicates.add(cb.like(cb.upper(filterExpr), "%" + (String) filter.getValue().toString().toUpperCase() + "%"));
            break;
        case NotEmpty:
            filter.setValue("");
            processNotEqualsOperator(cb, predicates, filter, filterExpr);
            predicates.add(cb.isNotNull(filterExpr));
            break;
        case Empty:
            filter.setValue("");
            processNotEqualsOperator(cb, predicates, filter, filterExpr);
            break;
        default:
            throw new InvalidArgumentException(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 Long count(EffectivePerson effectivePerson, Business business, Wi wi) throws Exception {
	EntityManager em = business.entityManagerContainer().get(Work.class);
	List<String> person_ids = business.organization().person().list(wi.getCredentialList());
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<Long> cq = cb.createQuery(Long.class);
	Root<Work> root = cq.from(Work.class);
	Predicate p = cb.conjunction();
	if (ListTools.isNotEmpty(wi.getApplicationList())) {
		p = cb.and(p, root.get(Work_.application).in(wi.getApplicationList()));
	}
	if (ListTools.isNotEmpty(wi.getProcessList())) {
		p = cb.and(p, root.get(Work_.process).in(wi.getProcessList()));
	}
	if(DateTools.isDateTimeOrDate(wi.getStartTime())){
		p = cb.and(p, cb.greaterThan(root.get(Work_.startTime), DateTools.parse(wi.getStartTime())));
	}
	if(DateTools.isDateTimeOrDate(wi.getEndTime())){
		p = cb.and(p, cb.lessThan(root.get(Work_.startTime), DateTools.parse(wi.getEndTime())));
	}
	if (ListTools.isNotEmpty(person_ids)) {
		p = cb.and(p, root.get(Work_.creatorPerson).in(person_ids));
	}
	if (ListTools.isNotEmpty(wi.getCreatorUnitList())) {
		p = cb.and(p, root.get(Work_.creatorUnit).in(wi.getCreatorUnitList()));
	}
	if (ListTools.isNotEmpty(wi.getWorkList())) {
		p = cb.and(p, root.get(Work_.id).in(wi.getWorkList()));
	}
	if (ListTools.isNotEmpty(wi.getJobList())) {
		p = cb.and(p, root.get(Work_.job).in(wi.getJobList()));
	}
	if (ListTools.isNotEmpty(wi.getStartTimeMonthList())) {
		p = cb.and(p, root.get(Work_.startTimeMonth).in(wi.getStartTimeMonthList()));
	}
	if (ListTools.isNotEmpty(wi.getActivityNameList())) {
		p = cb.and(p, root.get(Work_.activityName).in(wi.getActivityNameList()));
	}
	if (ListTools.isNotEmpty(wi.getWorkStatusList())) {
		p = cb.and(p, root.get(Work_.workStatus).in(wi.getWorkStatusList()));
	}
	if (StringUtils.isNoneBlank(wi.getKey())) {
		String key = StringTools.escapeSqlLikeKey(wi.getKey());
		p = cb.and(p,cb.like(root.get(Work_.title), "%" + key + "%", StringTools.SQL_ESCAPE_CHAR));
	}
	return em.createQuery(cq.select(cb.count(root)).where(p)).getSingleResult();
}
 

private List<WorkCompleted> list(EffectivePerson effectivePerson, Business business, Integer adjustPage,
		Integer adjustPageSize, Wi wi) throws Exception {
	EntityManager em = business.entityManagerContainer().get(WorkCompleted.class);
	List<String> person_ids = business.organization().person().list(wi.getCredentialList());
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<WorkCompleted> cq = cb.createQuery(WorkCompleted.class);
	Root<WorkCompleted> root = cq.from(WorkCompleted.class);
	Predicate p = cb.conjunction();
	if (ListTools.isNotEmpty(wi.getApplicationList())) {
		p = cb.and(p, root.get(WorkCompleted_.application).in(wi.getApplicationList()));
	}
	if (ListTools.isNotEmpty(wi.getProcessList())) {
		p = cb.and(p, root.get(WorkCompleted_.process).in(wi.getProcessList()));
	}
	if(DateTools.isDateTimeOrDate(wi.getStartTime())){
		p = cb.and(p, cb.greaterThan(root.get(WorkCompleted_.startTime), DateTools.parse(wi.getStartTime())));
	}
	if(DateTools.isDateTimeOrDate(wi.getEndTime())){
		p = cb.and(p, cb.lessThan(root.get(WorkCompleted_.startTime), DateTools.parse(wi.getEndTime())));
	}
	if (ListTools.isNotEmpty(person_ids)) {
		p = cb.and(p, root.get(WorkCompleted_.creatorPerson).in(person_ids));
	}
	if (ListTools.isNotEmpty(wi.getCreatorUnitList())) {
		p = cb.and(p, root.get(WorkCompleted_.creatorUnit).in(wi.getCreatorUnitList()));
	}
	if (ListTools.isNotEmpty(wi.getWorkList())) {
		p = cb.and(p, root.get(WorkCompleted_.id).in(wi.getWorkList()));
	}
	if (ListTools.isNotEmpty(wi.getJobList())) {
		p = cb.and(p, root.get(WorkCompleted_.job).in(wi.getJobList()));
	}
	if (ListTools.isNotEmpty(wi.getStartTimeMonthList())) {
		p = cb.and(p, root.get(WorkCompleted_.startTimeMonth).in(wi.getStartTimeMonthList()));
	}
	if (StringUtils.isNoneBlank(wi.getKey())) {
		String key = StringTools.escapeSqlLikeKey(wi.getKey());
		p = cb.and(p,cb.like(root.get(WorkCompleted_.title), "%" + key + "%", StringTools.SQL_ESCAPE_CHAR));
	}
	cq.select(root).where(p).orderBy(cb.desc(root.get(WorkCompleted_.startTime)));
	return em.createQuery(cq).setFirstResult((adjustPage - 1) * adjustPageSize).setMaxResults(adjustPageSize)
			.getResultList();
}
 

private Long count(EffectivePerson effectivePerson, Business business, Wi wi) throws Exception {
	EntityManager em = business.entityManagerContainer().get(Read.class);
	List<String> person_ids = business.organization().person().list(wi.getCredentialList());
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<Long> cq = cb.createQuery(Long.class);
	Root<Read> root = cq.from(Read.class);
	Predicate p = cb.conjunction();
	if (ListTools.isNotEmpty(wi.getApplicationList())) {
		p = cb.and(p, root.get(Read_.application).in(wi.getApplicationList()));
	}
	if (ListTools.isNotEmpty(wi.getProcessList())) {
		p = cb.and(p, root.get(Read_.process).in(wi.getProcessList()));
	}
	if(DateTools.isDateTimeOrDate(wi.getStartTime())){
		p = cb.and(p, cb.greaterThan(root.get(Read_.startTime), DateTools.parse(wi.getStartTime())));
	}
	if(DateTools.isDateTimeOrDate(wi.getEndTime())){
		p = cb.and(p, cb.lessThan(root.get(Read_.startTime), DateTools.parse(wi.getEndTime())));
	}
	if (ListTools.isNotEmpty(person_ids)) {
		p = cb.and(p, root.get(Read_.person).in(person_ids));
	}
	if (ListTools.isNotEmpty(wi.getCreatorUnitList())) {
		p = cb.and(p, root.get(Read_.creatorUnit).in(wi.getCreatorUnitList()));
	}
	if (ListTools.isNotEmpty(wi.getWorkList())) {
		p = cb.and(p, root.get(Read_.work).in(wi.getWorkList()));
	}
	if (ListTools.isNotEmpty(wi.getJobList())) {
		p = cb.and(p, root.get(Read_.job).in(wi.getJobList()));
	}
	if (ListTools.isNotEmpty(wi.getStartTimeMonthList())) {
		p = cb.and(p, root.get(Read_.startTimeMonth).in(wi.getStartTimeMonthList()));
	}
	if (ListTools.isNotEmpty(wi.getActivityNameList())) {
		p = cb.and(p, root.get(Read_.activityName).in(wi.getActivityNameList()));
	}
	if (StringUtils.isNotEmpty(wi.getKey())) {
		String key = StringUtils.trim(StringUtils.replace(wi.getKey(), "\u3000", " "));
		if (StringUtils.isNotEmpty(key)) {
			key = StringUtils.replaceEach(key, new String[] { "?", "%" }, new String[] { "", "" });
			p = cb.and(p,
					cb.or(cb.like(root.get(Read_.title), "%" + key + "%"),
							cb.like(root.get(Read_.opinion), "%" + key + "%"),
							cb.like(root.get(Read_.serial), "%" + key + "%"),
							cb.like(root.get(Read_.creatorPerson), "%" + key + "%"),
							cb.like(root.get(Read_.creatorUnit), "%" + key + "%")));
		}
	}
	return em.createQuery(cq.select(cb.count(root)).where(p)).getSingleResult();
}