Java Code Examples for javax.persistence.criteria.CriteriaQuery#select()

public List<String> listByAuthorizor( String workId, String delegatorIdentity, Integer delegateLevel ) throws Exception {
	if( workId == null || workId.isEmpty() ){
		throw new Exception( "workId is empty, system can not excute query!" );
	}
	EntityManager em = this.entityManagerContainer().get(OkrWorkAuthorizeRecord.class);
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<String> cq = cb.createQuery(String.class);
	Root<OkrWorkAuthorizeRecord> root = cq.from( OkrWorkAuthorizeRecord.class);
	Predicate p = cb.equal( root.get(OkrWorkAuthorizeRecord_.workId), workId );
	if( delegatorIdentity != null && !delegatorIdentity.isEmpty() ){
		p = cb.and( p, cb.equal( root.get( OkrWorkAuthorizeRecord_.delegatorIdentity ), delegatorIdentity));
	}
	if( delegateLevel != null && delegateLevel > 0 ){
		p = cb.and( p, cb.greaterThanOrEqualTo( root.get( OkrWorkAuthorizeRecord_.delegateLevel ), delegateLevel ));
	}
	cq.select( root.get( OkrWorkAuthorizeRecord_.id) );
	return em.createQuery(cq.where(p)).getResultList();
}
 

/**
 * 根据组织名称，统计年月，统计顶层组织所有人员工时不足人次总和
 * @param unitName
 * @param cycleYear
 * @param cycleMonth
 * @return
 * @throws Exception
 */
public Long sumLackOfTimeCountByUnitYearAndMonth(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_.lackOfTimeCount) ) );		
	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();
}
 

public Long getProcessingWorkCountByCenterId(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( ">>>>getProcessingWorkCountByCenterId-SQL:" +
	// em.createQuery(cq.where(p)).toString() );
	return em.createQuery(cq.where(p)).getSingleResult();
}
 

/**
 * 根据组织列表，统计年月，计算组织内所有员工工时不足次数总和
 * @param unitName
 * @param sYear
 * @param sMonth
 * @return
 * @throws Exception
 */
public Long sumLackOfTimeCountByUnitYearAndMonth(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( StatisticPersonForMonth.class);
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<Long> cq = cb.createQuery(Long.class);
	Root<StatisticPersonForMonth> root = cq.from( StatisticPersonForMonth.class);		
	//查询总数
	cq.select( cb.sum( root.get(StatisticPersonForMonth_.lackOfTimeCount) ) );		
	Predicate p = root.get(StatisticPersonForMonth_.unitName).in( unitName );
	if( sYear == null || sYear.isEmpty() ){
		logger.error( new StatisticYearEmptyException() );
	}else{
		p = cb.and( p, cb.equal( root.get(StatisticPersonForMonth_.statisticYear), sYear));
	}
	if( sMonth == null || sMonth.isEmpty() ){
		logger.error( new StatisticMonthEmptyException() );
	}else{
		p = cb.and( p, cb.equal( root.get(StatisticPersonForMonth_.statisticMonth), sMonth));
	}
	return em.createQuery(cq.where(p)).getSingleResult();
}
 

/**
 * 根据条件查询符合条件的目录信息条目数量
 * @param parentId
 * @param folderId
 * @param name
 * @return
 * @throws Exception
 */
public Long count( String parentId, String folderId, String name, String creator, String creatorUnit ) throws Exception {
	EntityManager em = this.entityManagerContainer().get(MindFolderInfo.class);
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<Long> cq = cb.createQuery(Long.class);
	Root<MindFolderInfo> root = cq.from(MindFolderInfo.class);
	Predicate p = cb.isNotNull( root.get(MindFolderInfo_.id ) );
	if( folderId != null && !folderId.isEmpty() ){
		p = cb.and( p, cb.equal( root.get( MindFolderInfo_.id), folderId));
	}
	if( creator != null && !creator.isEmpty() ){
		p = cb.and( p, cb.equal( root.get( MindFolderInfo_.creator), creator));
	}
	if( creatorUnit != null && !creatorUnit.isEmpty() ){
		p = cb.and( p, cb.equal( root.get( MindFolderInfo_.creatorUnit), creatorUnit));
	}
	if( parentId != null && !parentId.isEmpty() ){
		p = cb.and( p, cb.equal( root.get( MindFolderInfo_.parentId ), parentId));
	}
	if( name != null && !name.isEmpty() ){
		p = cb.and( p, cb.like( root.get( MindFolderInfo_.name ), "%" + name + "%" ));
	}
	cq.select( cb.count( root ) );
	return em.createQuery(cq.where(p)).getSingleResult();
}
 

/**
 * 根据中心工作ID，列示所有的数据信息
 * @param centerId 中心工作
 * @return
 * @throws Exception
 */
//@MethodDescribe( "根据中心工作ID，列示所有的信息" )
public List<String> listByCenterWorkId(String centerId) throws Exception {
	if( centerId == null || centerId.isEmpty() ){
		throw new Exception( " centerId is null!" );
	}
	EntityManager em = this.entityManagerContainer().get(OkrWorkReportPersonLink.class);
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<String> cq = cb.createQuery(String.class);
	Root<OkrWorkReportPersonLink> root = cq.from(OkrWorkReportPersonLink.class);
	Predicate p = cb.equal( root.get( OkrWorkReportPersonLink_.centerId ), centerId );
	cq.select(root.get( OkrWorkReportPersonLink_.id));
	return em.createQuery(cq.where(p)).getResultList();
}
 

public List<String> listByUnitNames( List<String> unitNameList ) throws Exception{		
	EntityManager em = this.entityManagerContainer().get(AttendanceScheduleSetting.class);
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<String> cq = cb.createQuery(String.class);
	Root<AttendanceScheduleSetting> root = cq.from(AttendanceScheduleSetting.class);
	Predicate p = root.get(AttendanceScheduleSetting_.unitName).in(unitNameList);
	cq.select(root.get(AttendanceScheduleSetting_.id));
	return em.createQuery(cq.where(p)).getResultList();
}
 

public Long countAttendanceDetailMobileForPage( String empNo, String empName, String signDescription,
		String startDate, String endDate) throws Exception {
	EntityManager em = this.entityManagerContainer().get( AttendanceDetailMobile.class );
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<Long> cq = cb.createQuery(Long.class);
	Root<AttendanceDetailMobile> root = cq.from(AttendanceDetailMobile.class);
	Predicate p = cb.isNotNull( root.get( AttendanceDetailMobile_.id ) );
	if( StringUtils.isNotEmpty( empNo ) ){
		p = cb.and( p, cb.equal( root.get( AttendanceDetailMobile_.empNo ), empNo ) );
	}
	if( StringUtils.isNotEmpty( empName ) ){
		p = cb.and( p, cb.equal( root.get( AttendanceDetailMobile_.empName ), empName ) );
	}
	if( StringUtils.isNotEmpty( signDescription ) ){
		p = cb.and( p, cb.equal( root.get( AttendanceDetailMobile_.signDescription ), signDescription ) );
	}
	if( StringUtils.isNotEmpty( startDate ) ){
		if( StringUtils.isNotEmpty( endDate ) && !endDate.equals( startDate ) ){//查询日期区间
			p = cb.between( root.get( AttendanceDetailMobile_.recordDateString ), startDate, endDate );
		}else{
			//查询startDate当天
			p = cb.and( p, cb.equal( root.get( AttendanceDetailMobile_.recordDateString ), startDate ) );
		}
	}
	cq.select( cb.count( root ) );		
	return em.createQuery(cq.where(p)).getSingleResult();
}
 

public List<String> listAll() throws Exception {
	EntityManager em = this.entityManagerContainer().get(OkrWorkAuthorizeRecord.class);
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<String> cq = cb.createQuery(String.class);
	Root<OkrWorkAuthorizeRecord> root = cq.from( OkrWorkAuthorizeRecord.class);
	cq.select(root.get(OkrWorkAuthorizeRecord_.id));
	return em.createQuery(cq).getResultList();
}
 

public List<User> get() {
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<User> cq = cb.createQuery(User.class);
    Root<User> pet = cq.from(User.class);
    cq.select(pet);
    TypedQuery<User> q = em.createQuery(cq);
    return q.getResultList();
}
 

public List<String> listIdsByCenterAndIdentityActivity( String dynamicObjectType, String dynamicObjectId, String identity, String processType, String activityName) throws Exception {
	if( dynamicObjectType == null || dynamicObjectType.isEmpty() ){
		throw new Exception( " dynamicObjectType is null!" );
	}
	if( dynamicObjectId == null || dynamicObjectId.isEmpty()){
		throw new Exception( " dynamicObjectId is null!" );
	}
	if( identity == null || identity.isEmpty()){
		throw new Exception( " identity is null!" );
	}
	if( processType == null || processType.isEmpty()){
		throw new Exception( " processType is null!" );
	}
	if( activityName == null || activityName.isEmpty()){
		throw new Exception( " activityName 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_.dynamicObjectType ), dynamicObjectType );
	p = cb.and( p, cb.equal( root.get( OkrTask_.dynamicObjectId ), dynamicObjectId) );
	p = cb.and( p, cb.equal( root.get( OkrTask_.targetIdentity ), identity) );
	p = cb.and( p, cb.equal( root.get( OkrTask_.processType ), processType) );
	p = cb.and( p, cb.equal( root.get( OkrTask_.activityName ), activityName) );
	cq.select(root.get(OkrTask_.id));
	return em.createQuery(cq.where(p)).getResultList();
}
 

public List<String> listTagIdsWithTask(String taskId, String person) throws Exception {
	EntityManager em = this.entityManagerContainer().get( TaskTagRele.class );
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<String> cq = cb.createQuery(String.class);
	Root<TaskTagRele> root = cq.from(TaskTagRele.class);
	Predicate p = cb.equal( root.get( TaskTagRele_.taskId ), taskId );
	p = CriteriaBuilderTools.predicate_and( cb, p, cb.equal( root.get( TaskTagRele_.owner ), person ) );
	cq.select( root.get(TaskTagRele_.tagId) );
	return em.createQuery(cq.where(p)).getResultList();
}
 

protected static <T extends Model> long count(final Class<T> clazz, Filter filter) {
	CriteriaBuilder builder = getEntityManager().getCriteriaBuilder();
	CriteriaQuery<Long> cQuery = builder.createQuery(Long.class);
	Root<T> root = cQuery.from(clazz);
	cQuery.select(builder.count(root));
	filter.constructQuery(builder, cQuery, root);
	TypedQuery<Long> query = createQuery(cQuery, filter);
	return query.getSingleResult();
}
 

public Long getNotReportConfirmTaskCount(List<String> taskTypeList, String userIdentity, String workTypeName) throws Exception {
	EntityManager em = this.entityManagerContainer().get( OkrTask.class );
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<Long> cq = cb.createQuery(Long.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.notEqual( root.get( OkrTask_.activityName ), "汇报确认" ) );
	if( workTypeName != null && !workTypeName.isEmpty() ){
		p = cb.and( p, cb.equal( root.get( OkrTask_.workType ), workTypeName ) );
	}
	cq.select( cb.count( root ) );
	return em.createQuery(cq.where(p)).getSingleResult();
}
 

public List<String> listByEmployeeNameAndAppealDate( String employeeName, String appealDateString ) throws Exception {
	
	if( employeeName == null || appealDateString == null ){
		return null;
	}		
	EntityManager em = this.entityManagerContainer().get(AttendanceAppealInfo.class);
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<String> cq = cb.createQuery(String.class);
	Root<AttendanceAppealInfo> root = cq.from( AttendanceAppealInfo.class);
	cq.select(root.get(AttendanceAppealInfo_.id));
	Predicate p = cb.equal( root.get(AttendanceAppealInfo_.empName),  employeeName );
	p = cb.and( p, cb.equal( root.get(AttendanceAppealInfo_.appealDateString ),  appealDateString ) );
	return em.createQuery(cq.where( p )).getResultList();
}
 

@Override
public Map<String, Long> selectCountsByProblemSetJids(Set<String> problemSetJids) {
    if (problemSetJids.isEmpty()) {
        return Collections.emptyMap();
    }

    CriteriaBuilder cb = currentSession().getCriteriaBuilder();
    CriteriaQuery<Tuple> cq = cb.createTupleQuery();
    Root<ProblemSetProblemModel> root = cq.from(getEntityClass());

    cq.select(cb.tuple(
            root.get(ProblemSetProblemModel_.problemSetJid),
            cb.count(root)));

    cq.where(
            cb.equal(root.get(ProblemSetProblemModel_.status), "VISIBLE"),
            cb.equal(root.get(ProblemSetProblemModel_.type), ProblemType.PROGRAMMING.name()),
            root.get(ProblemSetProblemModel_.problemSetJid).in(problemSetJids));

    cq.groupBy(
            root.get(ProblemSetProblemModel_.problemSetJid));

    return currentSession().createQuery(cq).getResultList()
            .stream()
            .collect(Collectors.toMap(tuple -> tuple.get(0, String.class), tuple -> tuple.get(1, Long.class)));

}
 

public List<String> listAllProcessingCenterWorkIds( List<String> processStatus, List<String> status ) 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);
	cq.select(root.get( OkrCenterWorkInfo_.id ));
	Predicate p = root.get(OkrCenterWorkInfo_.processStatus).in( processStatus );
	if( status != null && status.size() > 0 ){
		p = cb.and( p, root.get( OkrCenterWorkInfo_.status ).in( status ) );
	}
	return em.createQuery(cq.where(p)).getResultList();
}
 

/**
 * 查询用户有权限发布文档的所有栏目ID列表（ 不检测allPeopleView, with List copy ）
 * 
 * @param personName
 * @param unitNames
 * @param groupNames
 * @param inAppInfoIds
 * @param excludAppInfoIds
 * @return
 * @throws Exception
 */
private List<String> listPublishableAppIdsInPermission(EntityManagerContainer emc, String personName,
		List<String> unitNames, List<String> groupNames, List<String> inAppInfoIds, List<String> excludAppInfoIds,
		String documentType, Integer maxCount) throws Exception {
	List<String> appInfoIds = null;
	List<String> appInfoIds_out = new ArrayList<>();
	EntityManager em = emc.get(AppInfo.class);
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<String> cq = cb.createQuery(String.class);
	Root<AppInfo> root = cq.from(AppInfo.class);

	Predicate p = null;
	// 限定范围
	if (ListTools.isNotEmpty(inAppInfoIds)) {
		p = CriteriaBuilderTools.predicate_and(cb, p, cb.and(p, root.get(AppInfo.id_FIELDNAME).in(inAppInfoIds)));
	}
	// 排除指定的ID列表
	if (ListTools.isNotEmpty(excludAppInfoIds)) {
		p = CriteriaBuilderTools.predicate_and(cb, p, cb.not(root.get(AppInfo.id_FIELDNAME).in(excludAppInfoIds)));
	}
	if (StringUtils.isNotEmpty(documentType) && !"全部".equals(documentType)
			&& !"all".equalsIgnoreCase(documentType)) {
		p = CriteriaBuilderTools.predicate_and(cb, p,
				cb.equal(root.get(AppInfo.documentType_FIELDNAME), documentType));
	}

	Predicate p_permission = null;
	if (StringUtils.isNotEmpty(personName)) {
		// 可以管理的栏目，肯定可以发布信息
		p_permission = cb.isMember(personName, root.get(AppInfo.manageablePersonList_FIELDNAME));
		p_permission = cb.or(p_permission,
				cb.isMember(personName, root.get(AppInfo.publishablePersonList_FIELDNAME)));
	}
	if (ListTools.isNotEmpty(unitNames)) {
		p_permission = cb.or(p_permission, root.get(AppInfo.publishableUnitList_FIELDNAME).in(unitNames));
	}
	if (ListTools.isNotEmpty(groupNames)) {
		p_permission = cb.or(p_permission, root.get(AppInfo.publishableGroupList_FIELDNAME).in(groupNames));
	}

	p = CriteriaBuilderTools.predicate_and(cb, p, p_permission);
	cq.select(root.get(AppInfo.id_FIELDNAME));
	appInfoIds = em.createQuery(cq.where(p)).setMaxResults(maxCount).getResultList();
	if (appInfoIds == null) {
		appInfoIds = new ArrayList<>();
	}
	appInfoIds_out.addAll(appInfoIds);
	appInfoIds_out = excludListContent(appInfoIds_out, excludAppInfoIds);
	return appInfoIds_out;
}
 

@Override
public List<Resource> findByResourceServer(Map<String, String[]> attributes, String resourceServerId, int firstResult, int maxResult) {
    CriteriaBuilder builder = entityManager.getCriteriaBuilder();
    CriteriaQuery<ResourceEntity> querybuilder = builder.createQuery(ResourceEntity.class);
    Root<ResourceEntity> root = querybuilder.from(ResourceEntity.class);
    querybuilder.select(root.get("id"));
    List<Predicate> predicates = new ArrayList();

    if (resourceServerId != null) {
        predicates.add(builder.equal(root.get("resourceServer").get("id"), resourceServerId));
    }

    attributes.forEach((name, value) -> {
        if ("id".equals(name)) {
            predicates.add(root.get(name).in(value));
        } else if ("scope".equals(name)) {
            predicates.add(root.join("scopes").get("id").in(value));
        } else if ("ownerManagedAccess".equals(name) && value.length > 0) {
            predicates.add(builder.equal(root.get(name), Boolean.valueOf(value[0])));
        } else if ("uri".equals(name) && value.length > 0 && value[0] != null) {
            predicates.add(builder.lower(root.join("uris")).in(value[0].toLowerCase()));
        } else if ("uri_not_null".equals(name)) {
            // predicates.add(builder.isNotEmpty(root.get("uris"))); looks like there is a bug in hibernate and this line doesn't work: https://hibernate.atlassian.net/browse/HHH-6686
            // Workaround
            Expression<Integer> urisSize = builder.size(root.get("uris"));
            predicates.add(builder.notEqual(urisSize, 0));
        } else if ("owner".equals(name)) {
            predicates.add(root.get(name).in(value));
        } else if (!Resource.EXACT_NAME.equals(name)) {
            if ("name".equals(name) && attributes.containsKey(Resource.EXACT_NAME) && Boolean.valueOf(attributes.get(Resource.EXACT_NAME)[0]) 
                    && value.length > 0 && value[0] != null) {
                predicates.add(builder.equal(builder.lower(root.get(name)), value[0].toLowerCase()));
            } else if (value.length > 0 &&  value[0] != null) {
                predicates.add(builder.like(builder.lower(root.get(name)), "%" + value[0].toLowerCase() + "%"));
            }
        }
    });

    querybuilder.where(predicates.toArray(new Predicate[predicates.size()])).orderBy(builder.asc(root.get("name")));

    Query query = entityManager.createQuery(querybuilder);

    if (firstResult != -1) {
        query.setFirstResult(firstResult);
    }
    if (maxResult != -1) {
        query.setMaxResults(maxResult);
    }

    List<String> result = query.getResultList();
    List<Resource> list = new LinkedList<>();
    ResourceStore resourceStore = provider.getStoreFactory().getResourceStore();

    for (String id : result) {
        Resource resource = resourceStore.findById(id, resourceServerId);

        if (resource != null) {
            list.add(resource);
        }
    }

    return list;
}
 

@Override
public long transactionCount() {

    CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();

    CriteriaQuery<Long> countQuery = criteriaBuilder.createQuery(Long.class);
    countQuery.select(criteriaBuilder.count(countQuery.from(EncryptedTransaction.class)));

    return entityManager.createQuery(countQuery).getSingleResult();
}