javax.persistence.criteria.CriteriaBuilder Java Examples

/**
 * 迟到、缺勤、早退、工时不足、异常打卡，但未申诉通过的
 * @param year
 * @param month
 * @return
 * @throws Exception
 */
//@MethodDescribe("获取所有需要导出所有异常数据（未申诉的、申诉未通过的）")
public List<String> getDetailsWithAllAbnormalCase( String year, String month ) throws Exception {
	if( year == null || month == null ){
		return null;
	}
	EntityManager em = this.entityManagerContainer().get( AttendanceDetail.class );
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<String> cq = cb.createQuery(String.class);
	Root<AttendanceDetail> root = cq.from( AttendanceDetail.class);
	cq.select( root.get(AttendanceDetail_.id ));
	
	Predicate p = cb.lessThan( root.get(AttendanceDetail_.appealStatus), 9); //如果等于9就是申诉通过
	p = cb.and(p, cb.equal( root.get(AttendanceDetail_.cycleYear), year ));
	p = cb.and(p, cb.equal( root.get(AttendanceDetail_.cycleMonth), month ));
	
	Predicate orCase = cb.isTrue(root.get(AttendanceDetail_.isLate)); //迟到
	orCase = cb.or( orCase, cb.isTrue( root.get(AttendanceDetail_.isLeaveEarlier)) ); //或者早退
	orCase = cb.or( orCase, cb.isTrue( root.get(AttendanceDetail_.isAbnormalDuty) )); //或者异常打卡
	orCase = cb.or( orCase, cb.isTrue( root.get(AttendanceDetail_.isAbsent) )); //或者缺勤
	orCase = cb.or( orCase, cb.isTrue( root.get(AttendanceDetail_.isLackOfTime) )); //或者工时不足
	
	Predicate where = cb.and( p, orCase );
	
	return em.createQuery(cq.where(where)).setMaxResults(20000).getResultList();
}
 

@Override
public Page<String> findMessagesByActionStatusId(final Pageable pageable, final long actionStatusId) {
    final CriteriaBuilder cb = entityManager.getCriteriaBuilder();

    final CriteriaQuery<Long> countMsgQuery = cb.createQuery(Long.class);
    final Root<JpaActionStatus> countMsgQueryFrom = countMsgQuery.distinct(true).from(JpaActionStatus.class);
    final ListJoin<JpaActionStatus, String> cJoin = countMsgQueryFrom.joinList("messages", JoinType.LEFT);
    countMsgQuery.select(cb.count(cJoin))
            .where(cb.equal(countMsgQueryFrom.get(JpaActionStatus_.id), actionStatusId));
    final Long totalCount = entityManager.createQuery(countMsgQuery).getSingleResult();

    final CriteriaQuery<String> msgQuery = cb.createQuery(String.class);
    final Root<JpaActionStatus> as = msgQuery.from(JpaActionStatus.class);
    final ListJoin<JpaActionStatus, String> join = as.joinList("messages", JoinType.LEFT);
    final CriteriaQuery<String> selMsgQuery = msgQuery.select(join);
    selMsgQuery.where(cb.equal(as.get(JpaActionStatus_.id), actionStatusId));

    final List<String> result = new ArrayList<>(entityManager.createQuery(selMsgQuery)
            .setFirstResult((int) pageable.getOffset()).setMaxResults(pageable.getPageSize()).getResultList());

    return new PageImpl<>(result, pageable, totalCount);
}
 

@Override
public List<T> getAllLikeName( String name, boolean caseSensitive )
{
    CriteriaBuilder builder = getCriteriaBuilder();

    Function<Root<T>, Predicate> likePredicate;

    if ( caseSensitive )
    {
        likePredicate = root -> builder.like( root.get( "name" ), "%" + name + "%" );
    }
    else
    {
        likePredicate = root -> builder.like( builder.lower( root.get( "name" ) ), "%" + name.toLowerCase() + "%" );
    }

    JpaQueryParameters<T> param = new JpaQueryParameters<T>()
        .addPredicates( getSharingPredicates( builder ) )
        .addPredicate( likePredicate )
        .addOrder( root -> builder.asc( root.get( "name" ) ) );

    return getList( builder, param );
}
 

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

private List<NameValueCountPair> groupByCreatorPerson(Business business, Predicate predicate) throws Exception {
	EntityManager em = business.entityManagerContainer().get(Task.class);
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<Tuple> cq = cb.createQuery(Tuple.class);
	Root<Task> root = cq.from(Task.class);
	Path<String> pathCreatorPerson = root.get(Task_.creatorPerson);
	cq.multiselect(pathCreatorPerson, cb.count(root)).where(predicate).groupBy(pathCreatorPerson);
	List<Tuple> os = em.createQuery(cq).getResultList();
	List<NameValueCountPair> list = new ArrayList<>();
	NameValueCountPair pair = null;
	for (Tuple o : os) {
		pair = new NameValueCountPair();
		pair.setName(o.get(pathCreatorPerson));
		pair.setValue(o.get(pathCreatorPerson));
		pair.setCount(o.get(1, Long.class));
		list.add(pair);
	}
	return list.stream().sorted(Comparator.comparing(NameValueCountPair::getCount).reversed())
			.collect(Collectors.toList());
}
 

/**
 * 根据身份名称，从具体工作交流信息中查询与该身份有关的所有信息列表
 * @param identity
 * @param recordId 
 * @return
 * @throws Exception 
 */
public List<OkrWorkChat> listErrorIdentitiesInWorkChat(String identity, String recordId) throws Exception {
	EntityManager em = this.entityManagerContainer().get(OkrWorkChat.class);
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<OkrWorkChat> cq = cb.createQuery( OkrWorkChat.class );
	Root<OkrWorkChat> root = cq.from( OkrWorkChat.class );
	Predicate p = cb.isNotNull(root.get( OkrWorkChat_.id ));
	
	if( recordId != null && !recordId.isEmpty() && !"all".equals( recordId ) ){
		p = cb.and( p, cb.equal( root.get( OkrWorkChat_.id ), recordId ) );
	}
	
	Predicate p_targetIdentity = cb.isNotNull(root.get( OkrWorkChat_.targetIdentity ));
	p_targetIdentity = cb.and( p_targetIdentity, cb.equal( root.get( OkrWorkChat_.targetIdentity ), identity ) );
	
	Predicate p_senderIdentity = cb.isNotNull(root.get( OkrWorkChat_.senderIdentity ));
	p_senderIdentity = cb.and( p_senderIdentity, cb.equal( root.get( OkrWorkChat_.senderIdentity ), identity ) );
	
	Predicate p_identity = cb.or( p_targetIdentity, p_senderIdentity );
	
	p = cb.and( p, p_identity );
	
	return em.createQuery(cq.where(p)).getResultList();
}
 

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

/**
 * @param vo
 * 方法名： findAllError
 * 功 能： TODO(这里用一句话描述这个方法的作用)
 * 参 数： @return
 * 返 回： List<SysComnLogs>
 * 作 者 ： Administrator
 * @throws
 */
public Page<SysComnLogs> findAllError(SysComnLogsVo vo) {
	// Example<SysComnLogs> example = Example.of(vo.getInfo());
	Sort sort = Sort.by(new Order(Direction.DESC, "createTime"));
	PageRequest request = PageRequest.of(vo.getPageNumber() - 1, vo.getPageSize(), sort);
	Page<SysComnLogs> sourceCodes = sysComnLoggersDao.findAll(new Specification<SysComnLogs>() {
		/**
		 * @Fields serialVersionUID : TODO(用一句话描述这个变量表示什么)
		 */
		private static final long serialVersionUID = 1L;
		
		@Override
		public Predicate toPredicate(Root<SysComnLogs> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
			List<Predicate> list = new ArrayList<>();
			list.add(cb.isNotNull(root.get("message").as(String.class)));
			return cb.and(list.toArray(new Predicate[list.size()]));
		}
	}, request);
	return sourceCodes;
}
 

private Integer getArrayLastIndexWithApplicationDictWithPath(Business business, String applicationDict,
		String... paths) throws Exception {
	EntityManager em = business.entityManagerContainer().get(ApplicationDictItem.class);
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<ApplicationDictItem> cq = cb.createQuery(ApplicationDictItem.class);
	Root<ApplicationDictItem> root = cq.from(ApplicationDictItem.class);
	Predicate p = cb.equal(root.get(ApplicationDictItem_.bundle), applicationDict);
	for (int i = 0; ((i < paths.length) && (i < 8)); i++) {
		p = cb.and(p, cb.equal(root.get("path" + i), paths[i]));
	}
	for (int i = paths.length + 1; (i < 8); i++) {
		p = cb.and(p, cb.equal(root.get("path" + i), ""));
	}
	cq.select(root).where(p).orderBy(cb.desc(root.get("path" + paths.length + "Location")));
	List<ApplicationDictItem> list = em.createQuery(cq).setMaxResults(1).getResultList();
	if (list.size() == 0) {
		return null;
	} else {
		return list.get(0).get("path" + paths.length + "Location", Integer.class);
	}
}
 

public List<String> listTopUnitAttendanceDetailByYearAndMonth(List<String> topUnitNames, String year, String month)  throws Exception {
	EntityManager em = this.entityManagerContainer().get( AttendanceDetail.class );
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<String> cq = cb.createQuery(String.class);
	Root<AttendanceDetail> root = cq.from( AttendanceDetail.class);
	cq.select( root.get(AttendanceDetail_.id ));
	//一般始终为true, id is not null
	Predicate p = cb.equal( root.get(AttendanceDetail_.recordStatus), 1 );
	if( ListTools.isNotEmpty(  topUnitNames ) ){
		p = cb.and(p, root.get(AttendanceDetail_.topUnitName).in( topUnitNames ));
	}
	if( StringUtils.isNotEmpty( year ) ){
		p = cb.and(p, cb.equal( root.get(AttendanceDetail_.yearString), year ));
	}
	if( StringUtils.isNotEmpty( month ) ){
		p = cb.and(p, cb.equal( root.get(AttendanceDetail_.monthString), month ));
	}
	return em.createQuery(cq.where(p)).getResultList();
}
 

public List<String> listIdsByCenterAndPerson(String centerId, String identity, String dynamicObjectType ) throws Exception {
	if( centerId == null || centerId.isEmpty() ){
		throw new Exception( " centerId is null!" );
	}
	if( identity == null || identity.isEmpty()){
		throw new Exception( " identity is null!" );
	}
	EntityManager em = this.entityManagerContainer().get(OkrTask.class);
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<String> cq = cb.createQuery(String.class);
	Root<OkrTask> root = cq.from(OkrTask.class);
	Predicate p = cb.equal( root.get( OkrTask_.centerId ), centerId );
	p = cb.and( p, cb.equal( root.get( OkrTask_.targetIdentity ), identity) );
	if( dynamicObjectType != null && !dynamicObjectType.isEmpty() ){
		p = cb.and( p, cb.equal( root.get( OkrTask_.dynamicObjectType ), dynamicObjectType) );
	}
	cq.select(root.get(OkrTask_.id));
	return em.createQuery(cq.where(p)).getResultList();
}
 

private List<Wo> list(Business business, Wi wi) throws Exception {
	List<Wo> wos = new ArrayList<>();
	List<Identity> os = business.identity().pick(wi.getIdentityList());
	List<String> ids = ListTools.extractProperty(os, JpaObject.id_FIELDNAME, String.class, true, true);
	EntityManager em = business.entityManagerContainer().get(Identity.class);
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<String> cq = cb.createQuery(String.class);
	Root<Identity> root = cq.from(Identity.class);
	Predicate p = root.get(Identity_.id).in(ids);
	List<String> unitIds = em.createQuery(cq.select(root.get(Identity_.unit)).where(p).distinct(true))
			.getResultList();
	unitIds = ListTools.trim(unitIds, true, true);
	for (Unit o : business.unit().pick(unitIds)) {
		wos.add(this.convert(business, o, Wo.class));
	}
	return wos;
}
 

private List<NameValueCountPair> groupByApplication(Business business, Predicate predicate) throws Exception {
	EntityManager em = business.entityManagerContainer().get(Task.class);
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<Tuple> cq = cb.createQuery(Tuple.class);
	Root<Task> root = cq.from(Task.class);
	Path<String> pathApplication = root.get(Task_.application);
	Path<String> pathApplicationName = root.get(Task_.applicationName);
	cq.multiselect(pathApplication, pathApplicationName, cb.count(root)).where(predicate).groupBy(pathApplication);
	List<Tuple> os = em.createQuery(cq).getResultList();
	List<NameValueCountPair> list = new ArrayList<>();
	NameValueCountPair pair = null;
	for (Tuple o : os) {
		pair = new NameValueCountPair();
		pair.setName(o.get(pathApplicationName));
		pair.setValue(o.get(pathApplication));
		pair.setCount(o.get(2, Long.class));
		list.add(pair);
	}
	return list.stream().sorted(Comparator.comparing(NameValueCountPair::getCount).reversed())
			.collect(Collectors.toList());
}
 

/**
 * 根据身份名称，从工作汇报处理者信息中查询与该身份有关的所有信息列表
 * @param identity
 * @param recordId 
 * @return
 * @throws Exception 
 */
public List<OkrWorkReportPersonLink> listErrorIdentitiesInReportPersonInfo(String identity, String recordId) throws Exception {
	EntityManager em = this.entityManagerContainer().get(OkrWorkReportPersonLink.class);
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<OkrWorkReportPersonLink> cq = cb.createQuery( OkrWorkReportPersonLink.class );
	Root<OkrWorkReportPersonLink> root = cq.from( OkrWorkReportPersonLink.class );
	Predicate p = cb.isNotNull(root.get( OkrWorkReportPersonLink_.id ));
	
	if( recordId != null && !recordId.isEmpty() && !"all".equals( recordId ) ){
		p = cb.and( p, cb.equal( root.get( OkrWorkReportPersonLink_.id ), recordId ) );
	}
	
	Predicate p_processorIdentity = cb.isNotNull(root.get( OkrWorkReportPersonLink_.processorIdentity ));
	p_processorIdentity = cb.and( p_processorIdentity, cb.equal( root.get( OkrWorkReportPersonLink_.processorIdentity ), identity ) );		
	p = cb.and( p, p_processorIdentity );
	return em.createQuery(cq.where(p)).getResultList();
}
 

/**
 * Find empty profiles with a HW address matching some of given HW addresses.
 *
 * @param hwAddrs the set of HW addresses
 * @return the List of MinionServer with a HW address matching some of given HW addresses
 */
public static List<MinionServer> findEmptyProfilesByHwAddrs(Set<String> hwAddrs) {
    if (hwAddrs.isEmpty()) {
        return emptyList();
    }

    CriteriaBuilder builder = getSession().getCriteriaBuilder();
    CriteriaQuery<MinionServer> query = builder.createQuery(MinionServer.class);
    Root<MinionServer> root = query.distinct(true).from(MinionServer.class);

    Join<MinionServer, NetworkInterface> nicJoin = root.join("networkInterfaces", JoinType.INNER);
    Predicate hwAddrPredicate = nicJoin.get("hwaddr").in(hwAddrs);

    query.where(hwAddrPredicate);

    return getSession().createQuery(query).stream()
            .filter(s -> s.hasEntitlement(EntitlementManager.BOOTSTRAP))
            .collect(toList());
}
 

/**
 * 查询工作干系人身份列表（去重复）
 * 
 * @param identities_ok
 *            排除身份
 * @param identities_error
 *            排除身份
 * @return
 * @throws Exception
 */
public List<String> listAllDistinctEmployeeIdentity(List<String> identities_ok, List<String> identities_error)
		throws Exception {
	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);

	Predicate p = cb.isNotNull(root.get(OkrWorkPerson_.id));
	if (identities_ok != null && identities_ok.size() > 0) {
		p = cb.and(p, cb.not(root.get(OkrWorkPerson_.employeeIdentity).in(identities_ok)));
	}
	if (identities_error != null && identities_error.size() > 0) {
		p = cb.and(p, cb.not(root.get(OkrWorkPerson_.employeeIdentity).in(identities_error)));
	}
	cq.distinct(true).select(root.get(OkrWorkPerson_.employeeIdentity));
	return em.createQuery(cq.where(p)).getResultList();
}
 

public List<String> listRoleIdsByObjectUniqueId( String uniqueId, String objectType ) throws Exception {
	if( uniqueId == null || uniqueId.isEmpty() ){
		throw new Exception("uniqueId is null!");
	}
	if( objectType == null || objectType.isEmpty() ){
		throw new Exception("objectType is null!");
	}
	EntityManager em = this.entityManagerContainer().get( BBSUserRole.class );
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<String> cq = cb.createQuery( String.class );
	Root<BBSUserRole> root = cq.from( BBSUserRole.class );
	Predicate p = cb.equal( root.get( BBSUserRole_.uniqueId ), uniqueId );
	p = cb.and( p, cb.equal( root.get( BBSUserRole_.objectType ), objectType ));
	cq.select( root.get( BBSUserRole_.roleId ) );
	return em.createQuery( cq.where(p) ).getResultList();
}
 

/**
 * Builds a query restriction predicate for the storage.
 *
 * @param builder the criteria builder
 * @param storageEntityFrom the storage entity that appears in the from clause
 * @param storageEntities the optional list of storage entities where business object data storage units should be looked for
 * @param storagePlatformEntity the optional storage platform entity, e.g. S3 for Hive DDL. It is ignored when the list of storage entities is not empty
 * @param excludedStoragePlatformEntity the optional storage platform entity to be excluded from search. It is ignored when the list of storage entities is
 * not empty or the storage platform entity is specified
 *
 * @return the query restriction predicate
 */
protected Predicate getQueryRestrictionOnStorage(CriteriaBuilder builder, From<?, StorageEntity> storageEntityFrom, List<StorageEntity> storageEntities,
    StoragePlatformEntity storagePlatformEntity, StoragePlatformEntity excludedStoragePlatformEntity)
{
    List<Predicate> predicates = new ArrayList<>();

    // If specified, add restriction on storage names.
    if (!CollectionUtils.isEmpty(storageEntities))
    {
        List<String> storageNames = storageEntities.stream().map(StorageEntity::getName).collect(Collectors.toList());
        predicates.add(storageEntityFrom.get(StorageEntity_.name).in(storageNames));
    }
    // Otherwise, add restriction on storage platform, if specified.
    else if (storagePlatformEntity != null)
    {
        predicates.add(builder.equal(storageEntityFrom.get(StorageEntity_.storagePlatformCode), storagePlatformEntity.getName()));
    }
    // Otherwise, add restriction per excluded storage platform, if specified.
    else if (excludedStoragePlatformEntity != null)
    {
        predicates.add(builder.notEqual(storageEntityFrom.get(StorageEntity_.storagePlatformCode), excludedStoragePlatformEntity.getName()));
    }

    return builder.and(predicates.toArray(new Predicate[predicates.size()]));
}
 

private <P> void addPropertyPredicate(List<Predicate> predicates, Root<T> root, CriteriaBuilder cb, ExampleProperty<P> exampleProperty) {
    switch (exampleProperty.getMatcher()) {
        case DEFAULT:
        case EXACT:
            predicates.add(cb.equal(root.get(exampleProperty.getName()), exampleProperty.getValue()));
            break;
        case CONTAINING:
            predicates.add(cb.like(root.get(exampleProperty.getName()), "%" + exampleProperty.getValue() + "%"));
            break;
        case STARTING:
            predicates.add(cb.like(root.get(exampleProperty.getName()), exampleProperty.getValue() + "%"));
            break;
        case ENDING:
            predicates.add(cb.like(root.get(exampleProperty.getName()), "%" + exampleProperty.getValue()));
            break;
        default:
            throw new IllegalArgumentException(
                    "Unsupported StringMatcher " + exampleProperty.getMatcher().name());
    }
}
 

private Wo list(Business business, Wi wi) throws Exception {
	List<Unit> os = business.unit().pick(wi.getUnitList());
	List<String> unitIds = new ArrayList<>();
	for (Unit o : os) {
		unitIds.add(o.getId());
	}
	EntityManager em = business.entityManagerContainer().get(Identity.class);
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<String> cq = cb.createQuery(String.class);
	Root<Identity> root = cq.from(Identity.class);
	Predicate p = root.get(Identity_.unit).in(unitIds);
	List<String> identityIds = em.createQuery(cq.select(root.get(Identity_.id)).where(p).distinct(true))
			.getResultList();
	List<String> values = business.identity().listIdentityDistinguishedNameSorted(identityIds);
	Wo wo = new Wo();
	wo.getIdentityList().addAll(values);
	return wo;
}
 

public Integer getArrayLastIndexWithAppDictWithPath(String appDict, String... paths) throws Exception {
	EntityManager em = this.entityManagerContainer().get(AppDictItem.class);
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<AppDictItem> cq = cb.createQuery(AppDictItem.class);
	Root<AppDictItem> root = cq.from(AppDictItem.class);
	Predicate p = cb.equal(root.get(AppDictItem_.bundle), appDict);
	for (int i = 0; ((i < paths.length) && (i < 8)); i++) {
		p = cb.and(p, cb.equal(root.get("path" + i), paths[i]));
	}
	for (int i = paths.length + 1; (i < 8); i++) {
		p = cb.and(p, cb.equal(root.get("path" + i), ""));
	}
	cq.select(root).where(p).orderBy(cb.desc(root.get("path" + paths.length + "Location")));
	List<AppDictItem> list = em.createQuery(cq).setMaxResults(1).getResultList();
	if (list.size() == 0) {
		return null;
	} else {
		return list.get(0).get("path" + paths.length + "Location", Integer.class);
	}
}
 

public List<String> listWithPersonWithEditor(String owner, String person) throws Exception {
	EntityManager em = this.entityManagerContainer().get(Attachment.class);
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<String> cq = cb.createQuery(String.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(root.get(Attachment_.id)).where(p);
	return em.createQuery(cq).getResultList();
}
 

public List<String> expendUnitToPersonId(List<String> unitList) throws Exception {
	if (ListTools.isEmpty(unitList)) {
		return new ArrayList<String>();
	}
	List<String> identityIds = this.expendUnitToIdentityId(unitList);
	if (ListTools.isEmpty(identityIds)) {
		return new ArrayList<String>();
	}
	EntityManager em = this.entityManagerContainer().get(Identity.class);
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<String> cq = cb.createQuery(String.class);
	Root<Identity> root = cq.from(Identity.class);
	Predicate p = cb.isMember(root.get(Identity_.id), cb.literal(identityIds));
	List<String> personIds = em.createQuery(cq.select(root.get(Identity_.person)).where(p)).getResultList();
	personIds = ListTools.trim(personIds, true, true);
	return personIds;
}
 

public List<String> listWithInvitedRejected(String person) 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.isMember(person, root.get(Meeting_.invitePersonList));
	p = cb.and(p, cb.isMember(person, root.get(Meeting_.rejectPersonList)));
	p = cb.and(p, cb.lessThan(root.get(Meeting_.completedTime), new Date()));
	cq.select(root.get(Meeting_.id)).where(p);
	return em.createQuery(cq).getResultList();
}
 

public Object[] queryWithSelectAttributesAndTrim(String name)
{
    return criteria()
            .select(attribute(Simple_.name), trim(Simple_.name),
                    trim(CriteriaBuilder.Trimspec.LEADING, Simple_.name))
            .eq(Simple_.name, name)
            .createQuery()
            .getSingleResult();
}
 

public List<String> listMyCategoryWithAppId( List<String> myCategoryIds, String documentType, String appId ) throws Exception {
	EntityManager em = this.entityManagerContainer().get( CategoryInfo.class );
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<String> cq = cb.createQuery(String.class);
	Root<CategoryInfo> root = cq.from(CategoryInfo.class);
	cq.select(root.get(CategoryInfo_.id));
	Predicate p = cb.equal( root.get( CategoryInfo_.appId ), appId );
	if( myCategoryIds != null && !myCategoryIds.isEmpty() ){
		p = cb.and( p, root.get( CategoryInfo_.id ).in( myCategoryIds ) );
	}
	if( StringUtils.isNotEmpty( documentType) && !"全部".equals(documentType) && !"all".equalsIgnoreCase(documentType)) {
		p = cb.and( p, cb.equal( root.get( CategoryInfo_.documentType), documentType));
	}
	return em.createQuery(cq.where( p )).getResultList();
}
 

@Override
public void lock(long id) {
	CriteriaBuilder cb = entityManager.getCriteriaBuilder();
	CriteriaQuery<Long> query = cb.createQuery(Long.class);
	Root<Post> root = query.from(Post.class);
	query.select(root.get(Post_.id));
	query.where(cb.equal(root.get(Post_.id), id));
	entityManager.createQuery(query).setLockMode(LockModeType.PESSIMISTIC_WRITE).getSingleResult();
}
 

public List<Member> findAllOrderedByName()
{
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Member> criteria = cb.createQuery(Member.class);
    Root<Member> member = criteria.from(Member.class);

    /*
     * Swap criteria statements if you would like to try out type-safe criteria queries, a new
     * feature in JPA 2.0 criteria.select(member).orderBy(cb.asc(member.get(Member_.name)));
     */

    criteria.select(member).orderBy(cb.asc(member.get("name")));
    return em.createQuery(criteria).getResultList();
}
 

private Long countExpiredWork(Business business, Date start, Date current, ProcessStub processStub)
		throws Exception {
	EntityManager em = business.entityManagerContainer().get(Work.class);
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<Long> cq = cb.createQuery(Long.class);
	Root<Work> root = cq.from(Work.class);
	Predicate p = cb.greaterThan(root.get(Work_.startTime), start);
	p = cb.and(p, cb.lessThan(root.get(Work_.expireTime), current));
	p = cb.and(p, cb.equal(root.get(Work_.process), processStub.getValue()));
	cq.select(cb.count(root)).where(p);
	return em.createQuery(cq).getSingleResult();
}
 

public List<BBSForumInfo> listAllOpenForumInfo() throws Exception {
	EntityManager em = this.entityManagerContainer().get(BBSForumInfo.class);
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<BBSForumInfo> cq = cb.createQuery(BBSForumInfo.class);
	Root<BBSForumInfo> root = cq.from(BBSForumInfo.class);
	Predicate p = cb.equal( root.get(BBSForumInfo_.forumStatus ), "启用" );
	cq.orderBy( cb.asc( root.get( BBSForumInfo_.orderNumber ) ) );
	return em.createQuery( cq.where(p) ).setMaxResults( 1000 ).getResultList();
}