javax.persistence.criteria.Root Java Examples

private Code get(EntityManagerContainer emc, String mobile, String answer) throws Exception {
	EntityManager em = emc.get(Code.class);
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<Code> cq = cb.createQuery(Code.class);
	Root<Code> root = cq.from(Code.class);
	Calendar cal = Calendar.getInstance();
	cal.add(Calendar.MINUTE, -30);
	Predicate p = cb.greaterThan(root.get(Code_.createTime), cal.getTime());
	p = cb.and(p, cb.equal(root.get(Code_.mobile), mobile));
	p = cb.and(p, cb.equal(root.get(Code_.answer), answer));
	List<Code> list = em.createQuery(cq.where(p)).getResultList();
	if (list.isEmpty()) {
		return null;
	} else {
		return list.get(0);
	}
}
 

/**
 * 查询工作干系人身份列表（去重复）
 * 
 * @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();
}
 

private Wo list(Business business, Wi wi) throws Exception {
	Wo wo = new Wo();
	List<Identity> os = business.identity().pick(wi.getIdentityList());
	List<String> ids = ListTools.extractProperty(os, JpaObject.id_FIELDNAME, String.class, true, true);
	if (ListTools.isNotEmpty(ids)) {
		EntityManager em = business.entityManagerContainer().get(UnitDuty.class);
		CriteriaBuilder cb = em.getCriteriaBuilder();
		CriteriaQuery<String> cq = cb.createQuery(String.class);
		Root<UnitDuty> root = cq.from(UnitDuty.class);
		Predicate p = root.get(UnitDuty_.identityList).in(ids);
		List<String> names = em.createQuery(cq.select(root.get(UnitDuty_.name)).where(p).distinct(true))
				.getResultList();
		if (!names.isEmpty()) {
			wo.getNameList().addAll(names);
		}
	}
	return wo;
}
 

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

/**
 * 根据重复主体以及日期查询指定的日历记录信息ID列表
 * @param repeatMasterId
 * @param startTime
 * @param endTime
 * @return
 * @throws Exception
 */
public List<String> listWithRepeatMaster( String repeatMasterId, Date startTime, Date endTime ) throws Exception {
	EntityManager em = this.entityManagerContainer().get(Calendar_Event.class);
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<String> cq = cb.createQuery(String.class);
	Root<Calendar_Event> root = cq.from(Calendar_Event.class);
	
	Predicate p = null;
	if( StringUtils.isNotEmpty( repeatMasterId )) {
		p = CriteriaBuilderTools.predicate_and( cb, p, cb.equal(root.get(Calendar_Event_.repeatMasterId), repeatMasterId));
	}
	if( startTime != null ) {
		if( endTime == null ) {
			//查询startTime之后的所有记录
			p = CriteriaBuilderTools.predicate_and( cb, p, cb.greaterThanOrEqualTo( root.get(Calendar_Event_.startTime), startTime ));
		}else {
			p = CriteriaBuilderTools.predicate_and( cb, p, cb.lessThanOrEqualTo( root.get(Calendar_Event_.startTime), endTime ));
			p = CriteriaBuilderTools.predicate_and( cb, p, cb.greaterThanOrEqualTo( root.get(Calendar_Event_.endTime), startTime ));
		}
	}
	cq.select(root.get(Calendar_Event_.id));
	return em.createQuery(cq.where(p)).getResultList();
}
 

private List<NameValueCountPair> listActivityNamePair(Business business, EffectivePerson effectivePerson)
		throws Exception {
	EntityManager em = business.entityManagerContainer().get(TaskCompleted.class);
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<String> cq = cb.createQuery(String.class);
	Root<TaskCompleted> root = cq.from(TaskCompleted.class);
	Predicate p = cb.equal(root.get(TaskCompleted_.person), effectivePerson.getDistinguishedName());
	p = cb.and(p,
			cb.or(cb.equal(root.get(TaskCompleted_.latest), true), cb.isNull(root.get(TaskCompleted_.latest))));
	cq.select(root.get(TaskCompleted_.activityName)).where(p).distinct(true);
	List<String> os = em.createQuery(cq).getResultList();
	List<NameValueCountPair> wos = new ArrayList<>();
	for (String str : os) {
		if (StringUtils.isNotEmpty(str)) {
			NameValueCountPair o = new NameValueCountPair();
			o.setValue(str);
			o.setName(str);
			wos.add(o);
		}
	}
	SortTools.asc(wos, "name");
	return wos;
}
 

public Long getCompletedWorkCountByCenterId(String identity, List<String> status, String processIdentity)
		throws Exception {
	if (identity == null || identity.isEmpty()) {
		throw new Exception("identity is null.");
	}
	if (status == null || status.isEmpty()) {
		throw new Exception("status is null.");
	}
	EntityManager em = this.entityManagerContainer().get(OkrWorkPerson.class);
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<Long> cq = cb.createQuery(Long.class);
	Root<OkrWorkPerson> root = cq.from(OkrWorkPerson.class);
	Predicate p = root.get(OkrWorkPerson_.status).in(status);
	p = cb.and(p, cb.equal(root.get(OkrWorkPerson_.employeeIdentity), identity));
	p = cb.and(p, cb.equal(root.get(OkrWorkPerson_.workProcessStatus), "已完成"));
	if (processIdentity != null && !processIdentity.isEmpty()) {
		p = cb.and(p, cb.equal(root.get(OkrWorkPerson_.processIdentity), processIdentity));
	}
	// 查询总数
	cq.select(cb.count(root));
	// logger.info( ">>>>getCompletedWorkCountByCenterId-SQL:" +
	// em.createQuery(cq.where(p)).toString() );
	return em.createQuery(cq.where(p)).getSingleResult();
}
 

public AppDictItem getWithAppDictWithPath(String appDict, String path0, String path1, String path2, String path3, String path4, String path5,
		String path6, String path7) 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);
	p = cb.and(p, cb.equal(root.get("path0"), path0));
	p = cb.and(p, cb.equal(root.get("path1"), path1));
	p = cb.and(p, cb.equal(root.get("path2"), path2));
	p = cb.and(p, cb.equal(root.get("path3"), path3));
	p = cb.and(p, cb.equal(root.get("path4"), path4));
	p = cb.and(p, cb.equal(root.get("path5"), path5));
	p = cb.and(p, cb.equal(root.get("path6"), path6));
	p = cb.and(p, cb.equal(root.get("path7"), path7));
	cq.select(root).where(p);
	List<AppDictItem> list = em.createQuery(cq).getResultList();
	if (list.size() == 0) {
		return null;
	}
	if (list.size() == 1) {
		return list.get(0);
	}
	throw new Exception("error mulit appDictItem{id:" + appDict + ", path0:" + path0 + ", path1:" + path1 + ", path2:" + path2 + ", path3:" + path3
			+ ", path4:" + path4 + ", path5:" + path5 + ", path6:" + path6 + ", path7:" + path7 + "}");
}
 

@Override
public List<TagEntity> getTagsByIds(List<Long> ids)
{
    // Create the criteria builder and a tuple style criteria query.
    CriteriaBuilder builder = entityManager.getCriteriaBuilder();
    CriteriaQuery<TagEntity> criteria = builder.createQuery(TagEntity.class);

    // The criteria root is the tag entity.
    Root<TagEntity> tagEntityRoot = criteria.from(TagEntity.class);

    // Create the standard restrictions (i.e. the standard where clauses).
    Expression<Long> expression = tagEntityRoot.get(TagEntity_.id);
    Predicate queryRestriction = expression.in(ids);

    criteria.select(tagEntityRoot).where(queryRestriction);

    return entityManager.createQuery(criteria).getResultList();
}
 

public Script flagWithPortalObject(String flag, String portalId) throws Exception {
	String cacheKey = ApplicationCache.concreteCacheKey("flagObject", flag);
	Element element = scriptCache.get(cacheKey);
	if ((null != element) && (null != element.getObjectValue())) {
		return (Script) element.getObjectValue();
	} else {
		EntityManager em = this.entityManagerContainer().get(Script.class);
		CriteriaBuilder cb = em.getCriteriaBuilder();
		CriteriaQuery<Script> cq = cb.createQuery(Script.class);
		Root<Script> root = cq.from(Script.class);
		Predicate p = cb.equal(root.get(Script_.portal), portalId);
		p = cb.and(p, cb.or(cb.equal(root.get(Script_.name), flag), cb.equal(root.get(Script_.alias), flag)));
		List<Script> list = em.createQuery(cq.select(root).where(p)).setMaxResults(1).getResultList();
		if (list.isEmpty()) {
			return null;
		} else {
			Script o = list.get(0);
			em.detach(o);
			scriptCache.put(new Element(cacheKey, o));
			return o;
		}
	}
}
 

/**
 * 查询指定用户，组织，群组可以管理的分类列表
 * @param personName
 * @param unitNames
 * @param groupNames
 * @param inAppInfoIds
 * @return
 * @throws Exception
 */
public List<String> listManageableCategoryIds( String personName, List<String> unitNames, List<String> groupNames, List<String> inAppInfoIds,
		String documentType, Integer maxCount ) 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);
	
	Predicate p = cb.isMember( personName, root.get( CategoryInfo_.manageablePersonList ));
	if( ListTools.isNotEmpty( inAppInfoIds )) {
		p = cb.and( p, root.get( CategoryInfo_.appId ).in( inAppInfoIds ) );
	}
	if( ListTools.isNotEmpty( unitNames )) {
		p = cb.or( p,  root.get( CategoryInfo_.manageableUnitList).in(unitNames));
	}
	if( ListTools.isNotEmpty( groupNames )) {
		p = cb.or( p,  root.get( CategoryInfo_.manageableGroupList).in(groupNames));
	}
	cq.select(root.get( CategoryInfo_.id ));
	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 ) ).setMaxResults(maxCount).getResultList();
}
 

@Override
public List<NetworkVO> list(String name, String namePattern, String sortField, boolean sortOrderAsc, Integer take, Integer skip, Optional<HivePrincipal> principal) {
    CriteriaBuilder cb = criteriaBuilder();
    CriteriaQuery<Network> criteria = cb.createQuery(Network.class);
    Root<Network> from = criteria.from(Network.class);

    Predicate[] nameAndPrincipalPredicates = CriteriaHelper.networkListPredicates(cb, from, ofNullable(name), ofNullable(namePattern), principal);
    criteria.where(nameAndPrincipalPredicates);

    CriteriaHelper.order(cb, criteria, from, ofNullable(sortField), sortOrderAsc);

    TypedQuery<Network> query = createQuery(criteria);
    cacheQuery(query, of(CacheConfig.refresh()));
    ofNullable(take).ifPresent(query::setMaxResults);
    ofNullable(skip).ifPresent(query::setFirstResult);
    List<Network> result = query.getResultList();
    Stream<NetworkVO> objectStream = result.stream().map(Network::convertNetwork);
    return objectStream.collect(Collectors.toList());
}
 

/**
 * 获取属性名字路径
 * 
 * @param propertyName 属性名
 * @param root Query roots always reference entities
 * 
 * @return {@link Path}
 */
public static Path<?> getPath(String propertyName,Root<?> root) {
	
	Path<?> path = null;
	
	if (StringUtils.contains(propertyName, ".")) {
		String[] propertys = StringUtils.splitByWholeSeparator(propertyName, ".");
		path = root.get(propertys[0]);
		for (int i = 1; i < propertys.length; i++) {
			path = path.get(propertys[i]);
		}
	} else {
		path = root.get(propertyName);
	}
	
	return path;
}
 

ActionResult<List<Wo>> execute(EffectivePerson effectivePerson, Integer page, Integer size, JsonElement jsonElement)
		throws Exception {
	try (EntityManagerContainer emc = EntityManagerContainerFactory.instance().create()) {
		Wi wi = this.convertToWrapIn(jsonElement, Wi.class);
		ActionResult<List<Wo>> result = new ActionResult<>();
		EntityManager em = emc.get(EmpowerLog.class);
		CriteriaBuilder cb = em.getCriteriaBuilder();
		CriteriaQuery<Tuple> cq = cb.createQuery(Tuple.class);
		Root<EmpowerLog> root = cq.from(EmpowerLog.class);
		Predicate p = cb.equal(root.get(EmpowerLog_.toPerson), effectivePerson.getDistinguishedName());
		if (StringUtils.isNotEmpty(wi.getKey())) {
			String key = "%" + StringTools.escapeSqlLikeKey(wi.getKey()) + "%";
			p = cb.and(p, cb.like(root.get(EmpowerLog_.title), key, StringTools.SQL_ESCAPE_CHAR));
		}
		List<Wo> wos = emc.fetchDescPaging(EmpowerLog.class, Wo.copier, p, page, size,
				EmpowerLog.createTime_FIELDNAME);
		result.setData(wos);
		result.setCount(emc.count(EmpowerLog.class, p));
		return result;
	}
}
 

private void applyFilters(CriteriaBuilder cb, CriteriaQuery<?> cq, Root<M> root, FilterOptions<M> options) {
    Predicate filterId = cb.gt(root.get(Model_.id), options.getLastId());
    Predicate filterEq = cb.and(options.getColumnsEq().entrySet()
            .stream()
            .map(e -> cb.equal(root.get(e.getKey()), e.getValue()))
            .toArray(Predicate[]::new));
    Predicate filterIn = cb.and(options.getColumnsIn().entrySet()
            .stream()
            .map(e -> root.get(e.getKey()).in(e.getValue()))
            .toArray(Predicate[]::new));
    Predicate filterLike = options.getColumnsLike().isEmpty() ? cb.and() : cb.or(options.getColumnsLike().entrySet()
            .stream()
            .map(e -> cb.like(root.get(e.getKey()), contains(e.getValue())))
            .toArray(Predicate[]::new));
    Predicate filterCustom = cb.and(options.getCustomPredicates()
            .stream()
            .map(f -> f.apply(cb, cq, root))
            .toArray(Predicate[]::new));

    cq.where(filterId, filterEq, filterIn, filterLike, filterCustom);
}
 

public List<String> listIdsByPerson( String name, String leaderName ) throws Exception {
	if( name == null || name.isEmpty() ){
		throw new Exception ( "the parameter: 'name' is null!" );
	}
	if( leaderName == null || leaderName.isEmpty() ){
		throw new Exception ( "the parameter: 'leaderName' is null!" );
	}
	EntityManager em = this.entityManagerContainer().get( OkrConfigSecretary.class );
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<String> cq = cb.createQuery(String.class);
	Root<OkrConfigSecretary> root = cq.from( OkrConfigSecretary.class);
	cq.select(root.get( OkrConfigSecretary_.id ));
	Predicate p = cb.equal( root.get( OkrConfigSecretary_.secretaryName ) , name );
	p = cb.and( p, cb.equal( root.get( OkrConfigSecretary_.leaderName ), leaderName ));
	return em.createQuery(cq.where(p)).getResultList();
}
 

@RequestMapping("/index")
@Menu(type = "notice", subtype = "noticesys")
public ModelAndView index(ModelMap map , HttpServletRequest request ,HttpServletResponse response ,@Valid String msg) {
	final String orgi = super.getOrgi(request);
	Page<Notice> noticeList = noticeRes.findAll(new Specification<Notice>(){
		@Override
		public Predicate toPredicate(Root<Notice> root, CriteriaQuery<?> query,CriteriaBuilder cb) {
			List<Predicate> list = new ArrayList<Predicate>();  

			list.add(cb.equal(root.get("orgi").as(String.class), orgi));
			list.add(cb.equal(root.get("type").as(String.class), UKDataContext.NoticeType.SYSTEMUPGRADE.toString()));

			Predicate[] p = new Predicate[list.size()];  
			return cb.and(list.toArray(p));   
		}}, new PageRequest(super.getP(request), super.getPs(request), Sort.Direction.DESC, new String[] { "createtime" }));
	
	
	map.addAttribute("noticeList",noticeList) ;
	map.addAttribute("msg",msg) ;
	map.addAttribute("userList",userRes.findByOrgi(orgi)) ;
	map.addAttribute("type",UKDataContext.NoticeType.SYSTEMUPGRADE.toString());
	return request(super.createAppsTempletResponse("/apps/notice/index")) ;
}
 

protected <T extends JpaObject> String idlePortalAlias(Business business, String alias, String excludeId)
		throws Exception {
	if (StringUtils.isEmpty(alias)) {
		return "";
	}
	List<String> list = new ArrayList<>();
	list.add(alias);
	for (int i = 1; i < 99; i++) {
		list.add(alias + String.format("%02d", i));
	}
	list.add(StringTools.uniqueToken());
	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 = root.get(Portal_.alias).in(list);
	if (StringUtils.isNotEmpty(excludeId)) {
		p = cb.and(p, cb.notEqual(root.get(JpaObject.id_FIELDNAME), excludeId));
	}
	cq.select(root.get(Portal_.alias)).where(p);
	List<String> os = em.createQuery(cq).getResultList();
	list = ListUtils.subtract(list, os);
	return list.get(0);
}
 

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

/**
 * 根据待办类别和用户身份，查询待办列表
 * @param taskTypeList
 * @param userIdentity
 * @return
 * @throws Exception 
 */
public List<OkrTask> listReadByTaskType( List<String> taskTypeList, String userIdentity, String workTypeName ) throws Exception {
	List<OkrTask> okrTaskList = null;
	EntityManager em = this.entityManagerContainer().get( OkrTask.class );
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery< OkrTask > cq = cb.createQuery( OkrTask.class );
	Root<OkrTask> root = cq.from( OkrTask.class);
	Predicate p = root.get( OkrTask_.dynamicObjectType ).in( taskTypeList );
	p = cb.and( p, cb.equal( root.get( OkrTask_.targetIdentity ), userIdentity ) );
	p = cb.and( p, cb.equal( root.get( OkrTask_.processType ), "READ" ) );
	if( workTypeName != null && !workTypeName.isEmpty() ){
		p = cb.and( p, cb.equal( root.get( OkrTask_.workType ), workTypeName ) );
	}
	okrTaskList = em.createQuery(cq.where(p)).getResultList();
	if( okrTaskList == null ){
		return null;
	}else{
		return okrTaskList;
	}
}
 

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

@Override
public SecurityRoleEntity getSecurityRoleByName(String securityRoleName)
{
    // Create the criteria builder and the criteria.
    CriteriaBuilder builder = entityManager.getCriteriaBuilder();
    CriteriaQuery<SecurityRoleEntity> criteria = builder.createQuery(SecurityRoleEntity.class);

    // The criteria root is the security role.
    Root<SecurityRoleEntity> securityRoleEntity = criteria.from(SecurityRoleEntity.class);

    // Create the standard restrictions (i.e. the standard where clauses).
    List<Predicate> predicates = new ArrayList<>();
    predicates.add(builder.equal(builder.upper(securityRoleEntity.get(SecurityRoleEntity_.code)), securityRoleName.toUpperCase()));

    // Add the clauses for the query.
    criteria.select(securityRoleEntity).where(builder.and(predicates.toArray(new Predicate[predicates.size()])));

    return executeSingleResultQuery(criteria,
        String.format("Found more than one security role with parameters {securityRoleName=\"%s\"}.", securityRoleName));
}
 

@Override
public StorageEntity getStorageByName(String storageName)
{
    // Create the criteria builder and the criteria.
    CriteriaBuilder builder = entityManager.getCriteriaBuilder();
    CriteriaQuery<StorageEntity> criteria = builder.createQuery(StorageEntity.class);

    // The criteria root is the namespace.
    Root<StorageEntity> storageEntity = criteria.from(StorageEntity.class);

    // Create the standard restrictions (i.e. the standard where clauses).
    Predicate queryRestriction = builder.equal(builder.upper(storageEntity.get(StorageEntity_.name)), storageName.toUpperCase());

    criteria.select(storageEntity).where(queryRestriction);

    return executeSingleResultQuery(criteria, String.format("Found more than one storage with \"%s\" name.", storageName));
}
 

public static Specification<Conference> pastConferences(final Date past) {
	return new Specification<Conference>() {
		@Override
		public Predicate toPredicate(Root<Conference> root,
				CriteriaQuery<?> query, CriteriaBuilder cb) {
			Expression<Timestamp> currentTimestamp = cb.currentTimestamp();
			if (past == null) {
				return cb.greaterThan(currentTimestamp,
						root.get("endedDate").as(Date.class));
			} else {
				return cb.and(cb.greaterThan(currentTimestamp,
						root.get("endedDate").as(Date.class)), cb
						.greaterThan(
								root.get("startedDate").as(Date.class),
								past));
			}
		}
	};
}
 

/**
 * @see io.apiman.manager.api.core.IStorageQuery#getUserMemberships(java.lang.String)
 */
@Override
public Set<RoleMembershipBean> getUserMemberships(String userId) throws StorageException {
    Set<RoleMembershipBean> memberships = new HashSet<>();
    beginTx();
    try {
        EntityManager entityManager = getActiveEntityManager();
        CriteriaBuilder builder = entityManager.getCriteriaBuilder();
        CriteriaQuery<RoleMembershipBean> criteriaQuery = builder.createQuery(RoleMembershipBean.class);
        Root<RoleMembershipBean> from = criteriaQuery.from(RoleMembershipBean.class);
        criteriaQuery.where(builder.equal(from.get("userId"), userId));
        TypedQuery<RoleMembershipBean> typedQuery = entityManager.createQuery(criteriaQuery);
        List<RoleMembershipBean> resultList = typedQuery.getResultList();
        memberships.addAll(resultList);
        return memberships;
    } catch (Throwable t) {
        logger.error(t.getMessage(), t);
        throw new StorageException(t);
    } finally {
        rollbackTx();
    }
}
 

private <T> TypedQuery<T> doQueryCrossRes(Class<T> domainClazz, CrossResRequest request, boolean selectCount) {
    CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    EntityGraph<T> rootEg = entityManager.createEntityGraph(domainClazz);

    CriteriaQuery query = cb.createQuery(domainClazz);
    Root<T> root = query.from(domainClazz);
    if (selectCount) {
        query.select(cb.count(root));
    }
    List<Predicate> predicates = new LinkedList<>();
    queryJoin(cb, query, root, request.getRootFilterPath(), rootEg, null, predicates);

    if (predicates.size() > 0) {
        if (FilterRelationship.Or.equals(request.getFilterRs())) {
            query.where(cb.or(predicates.toArray(new Predicate[0])));
        } else {
            query.where(cb.and(predicates.toArray(new Predicate[0])));
        }
    }
    TypedQuery<T> typedQuery = entityManager.createQuery(query);
    if (!selectCount) {
        typedQuery.setHint("javax.persistence.fetchgraph", rootEg);
    }
    return typedQuery;
}
 

private List<NameValueCountPair> listCompletedTimeMonthPair(Business business, EffectivePerson effectivePerson,
		String applicationId) throws Exception {
	EntityManager em = business.entityManagerContainer().get(WorkCompleted.class);
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<String> cq = cb.createQuery(String.class);
	Root<WorkCompleted> root = cq.from(WorkCompleted.class);
	Predicate p = cb.equal(root.get(WorkCompleted_.creatorPerson), effectivePerson.getDistinguishedName());
	p = cb.and(p, cb.equal(root.get(WorkCompleted_.application), applicationId));
	cq.select(root.get(WorkCompleted_.completedTimeMonth)).where(p).distinct(true);
	List<String> list = em.createQuery(cq).getResultList();
	List<NameValueCountPair> wraps = new ArrayList<>();
	for (String str : list) {
		NameValueCountPair o = new NameValueCountPair();
		o.setValue(str);
		o.setName(str);
		wraps.add(o);
	}
	return wraps;
}
 

/**
 * 根据组织名称，统计年月，统计顶层组织所有人员迟到次数总和
 * @param unitName
 * @param cycleYear
 * @param cycleMonth
 * @return
 * @throws Exception
 */
public Long sumLateCountByUnitYearAndMonth(List<String> unitName, String sYear, String sMonth) throws Exception{
	if( unitName == null || unitName.size() == 0 ){
		logger.error( new UnitNamesEmptyException() );
		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_.lateCount) ) );		
	Predicate p = root.get(StatisticUnitForMonth_.unitName).in( unitName );
	if( sYear == null || sYear.isEmpty() ){
		logger.error( new StatisticYearEmptyException() );
	}else{
		p = cb.and( p, cb.equal( root.get(StatisticUnitForMonth_.statisticYear), sYear));
	}
	if( sMonth == null || sMonth.isEmpty() ){
		logger.error( new StatisticMonthEmptyException() );
	}else{
		p = cb.and( p, cb.equal( root.get(StatisticUnitForMonth_.statisticMonth), sMonth));
	}
	return em.createQuery(cq.where(p)).getSingleResult();
}
 

@Override
public List<AttributeValue> getAttributeValueByAttributeAndValue( Attribute attribute, String value )
{
    CriteriaBuilder builder = getCriteriaBuilder();

    CriteriaQuery<String> query = builder.createQuery( String.class );
    Root<T> root = query.from( getClazz() );

    query.select( builder.function( FUNCTION_JSONB_EXTRACT_PATH, String.class, root.get( "attributeValues" ) ,
        builder.literal( attribute.getUid() ) ) );

    query.where( builder.equal(
        builder.function( FUNCTION_JSONB_EXTRACT_PATH_TEXT, String.class, root.get( "attributeValues" ), builder.literal( attribute.getUid() ),  builder.literal( "value" ) ) , value ) );

    List<String> result = getSession().createQuery( query ).list();

    return JsonAttributeValueBinaryType.convertListJsonToListObject( result );
}
 

/**
 * 分页查询会话中的聊天消息
 * @param adjustPage
 * @param adjustPageSize
 * @param conversationId
 * @return
 * @throws Exception
 */
public List<IMMsg> listMsgWithConversationByPage(Integer adjustPage,
												 Integer adjustPageSize, String conversationId) throws Exception {
	EntityManager em = this.entityManagerContainer().get(IMMsg.class);
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<IMMsg> cq = cb.createQuery(IMMsg.class);
	Root<IMMsg> root = cq.from(IMMsg.class);
	Predicate p = cb.equal(root.get(IMMsg_.conversationId), conversationId);
	cq.select(root).where(p).orderBy(cb.desc(root.get(IMMsg_.createTime)));
	return em.createQuery(cq).setFirstResult((adjustPage - 1) * adjustPageSize).setMaxResults(adjustPageSize)
			.getResultList();
}