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

/**
 * 根据顶层组织，打卡日期，统计工时不足次数
 * @param topUnitNames
 * @param recordDate
 * @return
 * @throws Exception
 */
public Long countLackOfTimeByTopUnitAndDate( List<String> topUnitNames, String recordDate ) throws Exception{
	if( topUnitNames == null || topUnitNames.size() == 0 ){
		logger.error( new TopUnitNamesEmptyException() );
		return null;
	}		
	EntityManager em = this.entityManagerContainer().get( AttendanceDetail.class);
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<Long> cq = cb.createQuery(Long.class);
	Root<AttendanceDetail> root = cq.from( AttendanceDetail.class);
	Predicate p = root.get( AttendanceDetail_.topUnitName ).in( topUnitNames );
	p = cb.and( p, cb.equal( root.get( AttendanceDetail_.recordStatus ), 1));
	p = cb.and( p, cb.isTrue( root.get( AttendanceDetail_.isLackOfTime) ));
	if( recordDate == null || recordDate.isEmpty() ){
		logger.error( new RecordDateEmptyException() );
	}else{
		p = cb.and( p, cb.equal( root.get( AttendanceDetail_.recordDateString ), recordDate));
	}
	//查询总数
	cq.select( cb.count( root ) );
	return em.createQuery(cq.where(p)).getSingleResult();
}
 

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

    // The criteria root is the search index type.
    Root<SearchIndexTypeEntity> searchIndexTypeEntityRoot = criteria.from(SearchIndexTypeEntity.class);

    // Create the standard restrictions (i.e. the standard where clauses).
    Predicate predicate = builder.equal(builder.upper(searchIndexTypeEntityRoot.get(SearchIndexTypeEntity_.code)), code.toUpperCase());

    // Add all clauses to the query.
    criteria.select(searchIndexTypeEntityRoot).where(predicate);

    // Execute the query and return the result.
    return executeSingleResultQuery(criteria, String.format("Found more than one search index type with code \"%s\".", code));
}
 

private Script getScriptWithApplicationWithUniqueName(String applicationId, String uniqueName) throws Exception {
	Script script = null;
	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_.name), uniqueName);
	p = cb.or(p, cb.equal(root.get(Script_.alias), uniqueName));
	p = cb.or(p, cb.equal(root.get(Script_.id), uniqueName));
	p = cb.and(p, cb.equal(root.get(Script_.application), applicationId));
	List<Script> list = em.createQuery(cq.where(p)).setMaxResults(1).getResultList();
	if (!list.isEmpty()) {
		script = list.get(0);
	}
	return script;
}
 

private Long countExpiredWorkWorkCompleted(Business business, DateRange dateRange, String applicationId,
		String processId, List<String> units, String person) throws Exception {
	EntityManager em = business.entityManagerContainer().get(WorkCompleted.class);
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<Long> cq = cb.createQuery(Long.class);
	Root<WorkCompleted> root = cq.from(WorkCompleted.class);
	Predicate p = cb.between(root.get(WorkCompleted_.expireTime), dateRange.getStart(), dateRange.getEnd());
	p = cb.and(p, cb.equal(root.get(WorkCompleted_.expired), true));
	if (!StringUtils.equals(applicationId, StandardJaxrsAction.EMPTY_SYMBOL)) {
		p = cb.and(p, cb.equal(root.get(WorkCompleted_.application), applicationId));
	}
	if (!StringUtils.equals(processId, StandardJaxrsAction.EMPTY_SYMBOL)) {
		p = cb.and(p, cb.equal(root.get(WorkCompleted_.process), processId));
	}
	if (ListTools.isNotEmpty(units)) {
		p = cb.and(p, root.get(WorkCompleted_.creatorUnit).in(units));
	}
	if (!StringUtils.equals(person, StandardJaxrsAction.EMPTY_SYMBOL)) {
		p = cb.and(p, cb.equal(root.get(WorkCompleted_.creatorPerson), person));
	}
	cq.select(cb.count(root)).where(p);
	return em.createQuery(cq).getSingleResult();
}
 

/**
 * 查询具体工作阅知领导身份列表（去重复）
 * @param identities_ok 排除身份
 * @param identities_error 排除身份
 * @return
 * @throws Exception 
 */
public List<String> listAllDistinctReportLeaderIdentity(List<String> identities_ok, List<String> identities_error) throws Exception {
	EntityManager em = this.entityManagerContainer().get( OkrWorkBaseInfo.class );
	CriteriaBuilder cb = em.getCriteriaBuilder();
	/*
	CriteriaQuery<List> cq = cb.createQuery( List.class );		
	Root<OkrWorkBaseInfo> root = cq.from(OkrWorkBaseInfo.class);
	cq.select(root.get( OkrWorkBaseInfo_.readLeaderIdentityList ));
	List<List> allList = em.createQuery(cq).getResultList();
	*/
	
	CriteriaQuery<OkrWorkBaseInfo> cq = cb.createQuery( OkrWorkBaseInfo.class );
	Root<OkrWorkBaseInfo> root = cq.from(OkrWorkBaseInfo.class);		
	List<OkrWorkBaseInfo> os = em.createQuery(cq.select(root)).getResultList();
	List<List> allList = new ArrayList<>();
	for (OkrWorkBaseInfo o : os) {
		allList.add(o.getReadLeaderIdentityList());
	}
	
	if(ListTools.isNotEmpty( allList )) {
		HashSet hashSet = new  HashSet();
		for( List<String> identities : allList ) {
			if(ListTools.isNotEmpty( identities )) {
				for( String identity : identities ) {
					if( ListTools.isNotEmpty(identities_ok) && identities_ok.contains( identity ) ){
						continue;
					}
					if( ListTools.isNotEmpty(identities_error) && identities_error.contains( identity ) ){
						continue;
					}
					hashSet.add( identity );
				}
			}
		}
		List<String> result = new ArrayList<>();
		result.addAll(hashSet);
		return result;
	}
	return null;
}
 

private boolean exist(Business business, String name, String id, String applicationId) throws Exception {
	EntityManager em = business.entityManagerContainer().get(File.class);
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<Long> cq = cb.createQuery(Long.class);
	Root<File> root = cq.from(File.class);
	Predicate p = cb.or(cb.equal(root.get(File_.name), name), cb.equal(root.get(File_.alias), name),
			cb.equal(root.get(File_.id), name));
	p = cb.and(p, cb.equal(root.get(File_.application), applicationId), cb.notEqual(root.get(File_.id), id));
	cq.select(cb.count(root)).where(p);
	return em.createQuery(cq).getSingleResult() > 0;
}
 

private String getApplicationNameFromTaskCompleted(Business business, DateRange dateRange, String applicationId)
		throws Exception {
	EntityManagerContainer emc = business.entityManagerContainer();
	EntityManager em = emc.get(TaskCompleted.class);
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<String> cq = cb.createQuery(String.class);
	Root<TaskCompleted> root = cq.from(TaskCompleted.class);
	Predicate p = cb.between(root.get(TaskCompleted_.startTime), dateRange.getStart(), dateRange.getEnd());
	p = cb.and(p, cb.equal(root.get(TaskCompleted_.application), applicationId));
	cq.select(root.get(TaskCompleted_.applicationName)).where(p);
	List<String> list = em.createQuery(cq).setMaxResults(1).getResultList();
	return list.isEmpty() ? null : list.get(0);
}
 

@Test
@Transactional
public void testGetOrCreateRoot() {
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<Category> c = cb.createQuery(Category.class);
	Root<Category> root = JpaUtils.findOrCreateRoot(c, Category.class);

	notNull(root);

	c.where(cb.equal(JpaUtils.getPath(root, "name"), "Java"));
	List<Category> list = em.createQuery(c).getResultList();
	Category cat = list.get(0);
	JpaUtils.initialize(em, cat, 2);
	cat.getBooks().contains(new Book());
}
 

/**
 * 查询所有用户都可以发布的分类ID列表(检测allPeoplePublish)
 * 
 * @param inAppInfoIds      - 过滤栏目ID列表
 * @param inCategoryIds     - 过滤分类ID列表
 * @param excludCategoryIds - 排队分类ID列表
 * @return
 * @throws Exception
 */
private List<String> listAllPeoplePublishCategoryIds(EntityManagerContainer emc, List<String> inAppInfoIds,
		List<String> inCategoryIds, List<String> excludCategoryIds, String documentType, Integer maxCount)
		throws Exception {
	List<String> categoryInfoIds = null;
	List<String> categoryInfoIds_out = new ArrayList<>();

	EntityManager em = emc.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_FIELDNAME));

	Predicate p = cb.isTrue(root.get(CategoryInfo.allPeoplePublish_FIELDNAME));
	if (ListTools.isNotEmpty(inAppInfoIds)) {
		p = cb.and(p, root.get(CategoryInfo.appId_FIELDNAME).in(inAppInfoIds));
	}
	if (ListTools.isNotEmpty(inCategoryIds)) {
		p = cb.and(p, root.get(CategoryInfo.id_FIELDNAME).in(inCategoryIds));
	}
	if (ListTools.isNotEmpty(excludCategoryIds)) {
		p = cb.and(p, cb.not(root.get(CategoryInfo.id_FIELDNAME).in(excludCategoryIds)));
	}
	if (StringUtils.isNotEmpty(documentType) && !"全部".equals(documentType)
			&& !"all".equalsIgnoreCase(documentType)) {
		p = cb.and(p, cb.equal(root.get(CategoryInfo.documentType_FIELDNAME), documentType));
	}
	categoryInfoIds = em.createQuery(cq.where(p)).setMaxResults(maxCount).getResultList();
	if (categoryInfoIds == null) {
		categoryInfoIds = new ArrayList<>();
	}
	categoryInfoIds_out.addAll(categoryInfoIds);
	categoryInfoIds_out = excludListContent(categoryInfoIds_out, excludCategoryIds);
	return categoryInfoIds_out;
}
 

private List<Identity> listIdentity(Business business, Person person) throws Exception {
	EntityManager em = business.entityManagerContainer().get(Identity.class);
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<Identity> cq = cb.createQuery(Identity.class);
	Root<Identity> root = cq.from(Identity.class);
	Predicate p = cb.equal(root.get(Identity_.person), person.getId());
	List<Identity> os = em.createQuery(cq.select(root).where(p)).getResultList();
	return os;
}
 

public List<String> getByWorkFlowDocId(String docId) throws Exception {
	EntityManager em = this.entityManagerContainer().get(AttendanceSelfHoliday.class);
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<String> cq = cb.createQuery(String.class);
	Root<AttendanceSelfHoliday> root = cq.from( AttendanceSelfHoliday.class);
	Predicate p = cb.equal(root.get(AttendanceSelfHoliday_.docId), docId);
	cq.select(root.get(AttendanceSelfHoliday_.id));
	return em.createQuery(cq.where(p)).getResultList();
}
 

private List<String> listPersonWithGroup(Business business, List<String> groupIds) throws Exception {
	EntityManager em = business.entityManagerContainer().get(Group.class);
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<Group> cq = cb.createQuery(Group.class);
	Root<Group> root = cq.from(Group.class);
	Predicate p = root.get(Group_.id).in(groupIds);
	List<Group> os = em.createQuery(cq.select(root).where(p).distinct(true)).getResultList();
	List<String> personIds = new ArrayList<>();
	for (Group o : os) {
		personIds.addAll(o.getPersonList());
	}
	personIds = ListTools.trim(personIds, true, true);
	return personIds;
}
 

public Long countWithPersonWithWorkCompleted(String person, WorkCompleted workCompleted) throws Exception {
	EntityManager em = this.entityManagerContainer().get(Read.class);
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<Long> cq = cb.createQuery(Long.class);
	Root<Read> root = cq.from(Read.class);
	Predicate p = cb.equal(root.get(Read_.workCompleted), workCompleted.getId());
	p = cb.and(p, cb.equal(root.get(Read_.person), person));
	cq.select(cb.count(root)).where(p);
	return em.createQuery(cq).getSingleResult();
}
 

/** 查找使用表单的invoke */
public List<String> listWithForm(String formId) throws Exception {
	EntityManager em = this.entityManagerContainer().get(Invoke.class);
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<String> cq = cb.createQuery(String.class);
	Root<Invoke> root = cq.from(Invoke.class);
	Predicate p = cb.equal(root.get(Invoke_.form), formId);
	cq.select(root.get(Invoke_.id)).where(p);
	return em.createQuery(cq).getResultList();
}
 

public List<OkrConfigWorkType> listAll() throws Exception {
	EntityManager em = this.entityManagerContainer().get(OkrConfigWorkType.class);
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<OkrConfigWorkType> cq = cb.createQuery(OkrConfigWorkType.class);
	@SuppressWarnings("unused")
	Root<OkrConfigWorkType> root = cq.from( OkrConfigWorkType.class);
	return em.createQuery(cq).getResultList();
}
 

/**
 * Lookup multiple image profiles by an id list and organization
 * @param ids image profile id list
 * @param org the organization
 * @return Returns a list of image profiles with the given ids if it exists
 * inside the organization
 */
public static List<ImageProfile> lookupByIdsAndOrg(List<Long> ids, Org org) {
    CriteriaBuilder builder = getSession().getCriteriaBuilder();
    CriteriaQuery<ImageProfile> criteria = builder.createQuery(ImageProfile.class);
    Root<ImageProfile> root = criteria.from(ImageProfile.class);
    criteria.where(builder.and(
            root.get("profileId").in(ids),
            builder.equal(root.get("org"), org)));
    return getSession().createQuery(criteria).getResultList();
}
 

public List<String> getDetailsUnitsByCycleYearAndMonth(String cycleYear, String cycleMonth) throws Exception {
	if( cycleYear == null || cycleMonth == 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.distinct(true).select( root.get(AttendanceDetail_.unitName ));
	Predicate p = cb.equal( root.get(AttendanceDetail_.recordStatus), 1 );
	p = cb.and(p, cb.equal( root.get(AttendanceDetail_.cycleYear), cycleYear ));
	p = cb.and(p, cb.equal( root.get(AttendanceDetail_.cycleMonth), cycleMonth ));
	return em.createQuery(cq.where(p)).setMaxResults(20000).getResultList();
}
 

public List<Split> listWithProcessObject(String processId) throws Exception {
	EntityManager em = this.entityManagerContainer().get(Split.class);
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<Split> cq = cb.createQuery(Split.class);
	Root<Split> root = cq.from(Split.class);
	Predicate p = cb.equal(root.get(Split_.process), processId);
	cq.select(root).where(p);
	return em.createQuery(cq).getResultList();
}
 

private Long count(EffectivePerson effectivePerson, Business business, 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<Long> cq = cb.createQuery(Long.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));
	}
	return em.createQuery(cq.select(cb.count(root)).where(p)).getSingleResult();
}
 

/**
 * 根据权限查询用户可以发布文档的分类ID列表(根据权限, 不检测allPeopleView和allPeoplePublish)
 * 可管理的分类也属于可发布的分类
 * @param personName
 * @param unitNames
 * @param groupNames
 * @param inAppInfoIds  - 需要限定的栏目ID列表
 * @param inCategoryIds  - 栏目ID的最大范围
 * @param excludCategoryIds - 需要排除的栏目ID
 * @return
 * @throws Exception 
 */
public List<String> listPublishableCategoryInfoIdsWithPermission(String personName, List<String> unitNames,
		List<String> groupNames, List<String> inAppInfoIds, List<String> inCategoryIds,
		List<String> excludCategoryIds, 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 = null;
	Predicate p_filter = null;
	//限定栏目范围
	if( ListTools.isNotEmpty( inAppInfoIds )) {
		p_filter = root.get( CategoryInfo_.appId ).in( inAppInfoIds );
	}
	//限定范围
	if( ListTools.isNotEmpty( inCategoryIds )) {
		if( p_filter == null ) {
			p_filter = root.get( CategoryInfo_.id ).in( inCategoryIds );
		}else {
			p_filter = cb.and( p_filter, root.get( CategoryInfo_.id ).in( inCategoryIds ));
		}
	}
	//排除指定的ID列表
	if( ListTools.isNotEmpty( excludCategoryIds )) {
		if( p_filter == null ) {
			p_filter = cb.not( root.get( CategoryInfo_.id ).in( excludCategoryIds ));
		}else {
			p_filter = cb.and( p_filter, cb.not( root.get( CategoryInfo_.id ).in( excludCategoryIds )));
		}
	}		
	Predicate p_permission = null;
	if( StringUtils.isNotEmpty( personName )) {
		//可以管理的栏目，肯定可以发布信息
		p_permission = cb.isMember( personName, root.get( CategoryInfo_.manageablePersonList ));	
		p_permission = cb.or( p_permission, cb.isMember( personName, root.get( CategoryInfo_.publishablePersonList )));			
	}
	if( ListTools.isNotEmpty( unitNames )) {
		if( p_permission == null  ) {
			p_permission = root.get( CategoryInfo_.publishableUnitList).in(unitNames);
		}else {
			p_permission = cb.or( p_permission,  root.get( CategoryInfo_.publishableUnitList).in(unitNames));
		}
	}
	if( ListTools.isNotEmpty( groupNames )) {
		if( p_permission == null  ) {
			p_permission = root.get( CategoryInfo_.publishableGroupList).in(groupNames);
		}else {
			p_permission = cb.or( p_permission,  root.get( CategoryInfo_.publishableGroupList).in(groupNames));
		}
	}
	
	//使用新的条件将两个条件组合起来
	if( p_filter != null ) {
		p = p_filter;
	}
	if( p != null ) {
		if( p_permission != null ) {
			p = cb.and(p, p_permission);
		}
	}else {
		if( p_permission != null ) {
			p = p_permission;
		}
	}
	if( StringUtils.isNotEmpty( documentType) && !"全部".equals(documentType)&& !"all".equalsIgnoreCase(documentType)) {
		p = cb.and( p, cb.equal( root.get( CategoryInfo_.documentType), documentType));
	}
	cq.select(root.get( CategoryInfo_.id ));
	return em.createQuery( cq.where( p ) ).setMaxResults(maxCount).getResultList();
}