Java Code Examples for org.hibernate.criterion.ProjectionList#add()

@SuppressWarnings("rawtypes")
private Map getJobDateRange(Session session, boolean ignoreTestAccounts) {
	//Get min and max values of dates
	Criteria rangeCriteria = session.createCriteria(JobLogEntry.class);
	testAccountFilter.addCriteriaForTestAccounts(session, ignoreTestAccounts, rangeCriteria);
	ProjectionList projections = Projections.projectionList();
	projections.add(Projections.min(JobLogContainer.START_TIME), "min");
	projections.add(Projections.max(JobLogContainer.START_TIME), "max");
	rangeCriteria.setProjection(projections);
	rangeCriteria.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);

	@SuppressWarnings("unchecked")
	List<Map> rangeList = rangeCriteria.list();

	return rangeList.get(0);
}
 

/**
 * Get all the Counters information from DB
 */
@SuppressWarnings("unchecked")
private List<Counters> getAllCounters() {
    Session session = sessionFactory.openSession();
    Criteria counterCriteria = session.createCriteria(CounterEntity.class, "counter");
    counterCriteria.addOrder(Order.asc("counter.position"));
    ProjectionList projectionList = Projections.projectionList();
    projectionList.add(Projections.property("counter.counterId"));
    projectionList.add(Projections.property("counter.counterName"));
    projectionList.add(Projections.property("counter.position"));
    projectionList.add(Projections.property("counter.delInd"));

    counterCriteria.setProjection(projectionList);
    List<Counters> counterList = new ArrayList<Counters>();
    for (Object[] counterObj : (List<Object[]>) counterCriteria.list()) {
        Counters counter = new Counters();
        counter.setCounterId(Integer.parseInt(String.valueOf(counterObj[0])));
        counter.setCounterName(String.valueOf(counterObj[1]));
        counter.setPosition(Integer.parseInt(String.valueOf(counterObj[2])));
        counter.setDelInd(Integer.parseInt(String.valueOf(counterObj[3])));
        counterList.add(counter);
    }
    session.close();
    return counterList;
}
 

private static DetachedCriteria createEcrfFieldStatusEntryDetachedCriteriaMaxId(org.hibernate.Criteria ecrfFieldStatusEntryCriteria, org.hibernate.Criteria ecrfFieldCriteria,
		org.hibernate.Criteria probandListEntryCriteria,
		ECRFFieldStatusQueue queue, Long probandListEntryId, Long ecrfFieldId) {
	DetachedCriteria subQuery = createEcrfFieldStatusEntryDetachedCriteria(ecrfFieldStatusEntryCriteria, ecrfFieldCriteria, probandListEntryCriteria, probandListEntryId,
			ecrfFieldId);
	if (queue != null) {
		subQuery.add(Restrictions.eq("queue", queue));
		subQuery.setProjection(Projections.max("id"));
	} else {
		ProjectionList proj = Projections.projectionList();
		proj.add(Projections.sqlGroupProjection(
				"max({alias}.id) as maxId",
				"{alias}.queue",
				new String[] { "maxId" },
				new org.hibernate.type.Type[] { Hibernate.LONG }));
		subQuery.setProjection(proj);
	}
	return subQuery;
}
 

public static List listDistinctRoot(Criteria criteria, Object dao, String... fields) throws Exception {
	if (dao != null && criteria != null) {
		criteria.setProjection(null);
		criteria.setResultTransformer(CriteriaSpecification.ROOT_ENTITY);
		Method loadMethod = CoreUtil.getDaoLoadMethod(dao);
		ProjectionList projectionList = Projections.projectionList().add(Projections.id());
		boolean cast = false;
		if (fields != null && fields.length > 0) {
			for (int i = 0; i < fields.length; i++) {
				projectionList.add(Projections.property(fields[i]));
			}
			cast = true;
		}
		List items = criteria.setProjection(Projections.distinct(projectionList)).list();
		Iterator it = items.iterator();
		ArrayList result = new ArrayList(items.size());
		while (it.hasNext()) {
			result.add(loadMethod.invoke(dao, cast ? ((Object[]) it.next())[0] : it.next()));
		}
		return result;
	}
	return null;
}
 

private static Criteria applyStratificationTagValuesCriterions(org.hibernate.Criteria listEntryCriteria, Set<Long> selectionSetValueIds) {
	org.hibernate.Criteria tagValuesCriteria = listEntryCriteria.createCriteria("tagValues", CriteriaSpecification.INNER_JOIN);
	tagValuesCriteria.createCriteria("tag", CriteriaSpecification.INNER_JOIN).add(Restrictions.eq("stratification", true));
	org.hibernate.Criteria selectionValuesCriteria = tagValuesCriteria.createCriteria("value", CriteriaSpecification.INNER_JOIN).createCriteria("selectionValues",
			CriteriaSpecification.INNER_JOIN);
	selectionValuesCriteria.add(Restrictions.in("id", selectionSetValueIds));
	ProjectionList proj = Projections.projectionList();
	proj.add(Projections.id());
	proj.add(Projections.sqlGroupProjection(
			"count(*) as selectionValuesCount",
			"{alias}.id having count(*) = " + selectionSetValueIds.size(),
			new String[] { "selectionValuesCount" },
			new org.hibernate.type.Type[] { Hibernate.LONG }));
	listEntryCriteria.setProjection(proj);
	return listEntryCriteria;
}
 

/**
 * Get All the APPS with the launch date null
 */
public static Set<Integer> getAppsLauchDateNull(int envId) {

	Session session = HibernateConfig.getSessionFactory().getCurrentSession();
	Transaction txn = session.beginTransaction();
	Criteria healthCheckCriteria = session.createCriteria(HealthCheckEntity.class, "health");
	healthCheckCriteria.createCriteria("health.component", "component");
	healthCheckCriteria.add(Restrictions.isNull("health.createdDate"));
	healthCheckCriteria.add(Restrictions.eq("health.environment.environmentId", envId));
	
	ProjectionList projectionList = Projections.projectionList();
       projectionList.add(Projections.property("component.componentId"));
       healthCheckCriteria.setProjection(projectionList);
       
       Set<Integer> compSet = new HashSet<Integer>();
	@SuppressWarnings("unchecked")
	List<Integer> resultList = (List<Integer>) healthCheckCriteria.list();
	compSet.addAll(resultList); 
	txn.commit();
	return compSet;         
}
 

private HashMap<Long, ArrayList<VisitScheduleItem>> listExpandDateModeByProband(org.hibernate.Criteria visitScheduleItemCriteria, Long probandId, Timestamp from, Timestamp to)
		throws Exception {
	ProjectionList proj = Projections.projectionList();
	proj.add(Projections.id());
	Iterator<Projection> sqlColumnsIt = applyExpandDateModeCriterions(visitScheduleItemCriteria, probandId, from, to, null).values().iterator();
	while (sqlColumnsIt.hasNext()) {
		proj.add(sqlColumnsIt.next());
	}
	visitScheduleItemCriteria.setProjection(proj);
	HashMap<Long, ArrayList<VisitScheduleItem>> result = new HashMap<Long, ArrayList<VisitScheduleItem>>();
	Iterator it = visitScheduleItemCriteria.list().iterator();
	while (it.hasNext()) {
		Object[] row = (Object[]) it.next();
		probandId = (Long) row[4];
		ArrayList<VisitScheduleItem> visitScheduleItems;
		if (result.containsKey(probandId)) {
			visitScheduleItems = result.get(probandId);
		} else {
			visitScheduleItems = new ArrayList<VisitScheduleItem>();
			result.put(probandId, visitScheduleItems);
		}
		visitScheduleItems.add(getVisitScheduleItemFromRow(row));
	}
	CoreUtil.getUserContext().voMapRegisterIgnores(VisitScheduleItem.class);
	return result;
}
 

private ArrayList<VisitScheduleItem> listExpandDateMode(org.hibernate.Criteria visitScheduleItemCriteria, Long probandId, Timestamp from, Timestamp to,
		org.hibernate.criterion.Criterion or, SubCriteriaMap criteriaMap, PSFVO psf, boolean distinct) throws Exception {
	// projection to avoid multiplebagexception and get calculated dates	
	ProjectionList proj = Projections.projectionList();
	proj.add(Projections.id());
	Iterator<Projection> sqlColumnsIt = applyExpandDateModeCriterions(visitScheduleItemCriteria, probandId, from, to, or).values().iterator();
	while (sqlColumnsIt.hasNext()) {
		proj.add(sqlColumnsIt.next());
	}
	// populate result collection
	CriteriaUtil.applyPSFVO(criteriaMap, psf); //apply filter, populate rowcount
	visitScheduleItemCriteria.setProjection(proj); //set projection for final .list()
	HashSet<Long> dupeCheck = new HashSet<Long>();
	ArrayList<VisitScheduleItem> result = new ArrayList<VisitScheduleItem>();
	Iterator it = visitScheduleItemCriteria.list().iterator();
	while (it.hasNext()) {
		Object[] row = (Object[]) it.next();
		if (!distinct || dupeCheck.add((Long) row[0])) {
			result.add(getVisitScheduleItemFromRow(row));
		}
	}
	// support sorting by start/stop
	AssociationPath sortFieldAssociationPath = new AssociationPath(psf != null ? psf.getSortField() : null);
	if (sortFieldAssociationPath.isValid()) {
		String sortProperty = sortFieldAssociationPath.getPropertyName();
		if ("start".equals(sortProperty)) {
			Collections.sort(result, psf.getSortOrder() ? VISIT_SCHEDULE_ITEM_COMPARATOR_START_ASC : VISIT_SCHEDULE_ITEM_COMPARATOR_START_DESC);
		} else if ("stop".equals(sortProperty)) {
			Collections.sort(result, psf.getSortOrder() ? VISIT_SCHEDULE_ITEM_COMPARATOR_STOP_ASC : VISIT_SCHEDULE_ITEM_COMPARATOR_STOP_DESC);
		}
	}
	// prevent vo caching to substitute unintentionally because of nonunique id's
	CoreUtil.getUserContext().voMapRegisterIgnores(VisitScheduleItem.class);
	return result;
}
 

private void setProjections(Criteria criteria, Projection[] projections) {
	ProjectionList projList = Projections.projectionList();
	if(projections != null && projections.length > 0) {
		for(Projection projection : projections) {
			projList.add(projection);
		}
		criteria.setProjection(projList);
	}
	
	
}
 

/**
 * A distinct projection that takes a list
 *
 * @param propertyNames The list of distince property names
 * @param alias The alias to use
 */
@SuppressWarnings("rawtypes")
public org.grails.datastore.mapping.query.api.ProjectionList distinct(Collection propertyNames, String alias) {
    ProjectionList list = Projections.projectionList();
    for (Object o : propertyNames) {
        list.add(Projections.property(calculatePropertyName(o.toString())));
    }
    final Projection proj = Projections.distinct(list);
    addProjectionToList(proj, alias);
    return this;
}
 

private static Timestamp minStartExpandDateMode(org.hibernate.Criteria visitScheduleItemCriteria, Long probandId, Timestamp from, Timestamp to,
		org.hibernate.criterion.Criterion or) throws Exception {
	ProjectionList proj = Projections.projectionList();
	LinkedHashMap<String, Projection> sqlColumns = applyExpandDateModeCriterions(visitScheduleItemCriteria, probandId, from, to, or);
	proj.add(Projections.sqlProjection(
			"least(min({alias}.start), min(" + ((SQLProjection) sqlColumns.get("tagStart")).getSql() + ")) as minStart",
			new String[] { "minStart" },
			new org.hibernate.type.Type[] { Hibernate.TIMESTAMP }));
	visitScheduleItemCriteria.setProjection(proj);
	return (Timestamp) visitScheduleItemCriteria.uniqueResult();
}
 

private static Timestamp maxStopExpandDateMode(org.hibernate.Criteria visitScheduleItemCriteria, Long probandId, Timestamp from, Timestamp to,
		org.hibernate.criterion.Criterion or) throws Exception {
	ProjectionList proj = Projections.projectionList();
	LinkedHashMap<String, Projection> sqlColumns = applyExpandDateModeCriterions(visitScheduleItemCriteria, probandId, from, to, or);
	proj.add(Projections.sqlProjection(
			"greatest(max({alias}.stop), max(" + ((SQLProjection) sqlColumns.get("tagStop")).getSql() + "), max(" + ((SQLProjection) sqlColumns.get("durationStop")).getSql()
					+ ")) as maxStop",
			new String[] { "maxStop" },
			new org.hibernate.type.Type[] { Hibernate.TIMESTAMP }));
	visitScheduleItemCriteria.setProjection(proj);
	return (Timestamp) visitScheduleItemCriteria.uniqueResult();
}
 

private ArrayList<Object[]> listExpandDateModeProband(org.hibernate.Criteria visitScheduleItemCriteria, Long probandId, Timestamp from, Timestamp to,
		SubCriteriaMap criteriaMap, PSFVO psf)
		throws Exception {
	ProjectionList proj = Projections.projectionList();
	proj.add(Projections.id());
	Iterator<Projection> sqlColumnsIt = applyExpandDateModeCriterions(visitScheduleItemCriteria, probandId, from, to, null).values().iterator();
	while (sqlColumnsIt.hasNext()) {
		proj.add(sqlColumnsIt.next());
	}
	CriteriaUtil.applyPSFVO(criteriaMap, psf);
	visitScheduleItemCriteria.setProjection(proj);
	ArrayList<Object[]> result = new ArrayList<Object[]>();
	Iterator it = visitScheduleItemCriteria.list().iterator();
	while (it.hasNext()) {
		Object[] row = (Object[]) it.next();
		probandId = (Long) row[4];
		result.add(new Object[] {
				getVisitScheduleItemFromRow(row),
				probandId != null ? this.getProbandDao().load(probandId) : null
		});
	}
	AssociationPath sortFieldAssociationPath = new AssociationPath(psf != null ? psf.getSortField() : null);
	if (sortFieldAssociationPath.isValid()) {
		String sortProperty = sortFieldAssociationPath.getPropertyName();
		if ("start".equals(sortProperty)) {
			Collections.sort(result, psf.getSortOrder() ? VISIT_SCHEDULE_ITEM_PROBAND_COMPARATOR_START_ASC : VISIT_SCHEDULE_ITEM_PROBAND_COMPARATOR_START_DESC);
		} else if ("stop".equals(sortProperty)) {
			Collections.sort(result, psf.getSortOrder() ? VISIT_SCHEDULE_ITEM_PROBAND_COMPARATOR_STOP_ASC : VISIT_SCHEDULE_ITEM_PROBAND_COMPARATOR_STOP_DESC);
		}
	}
	CoreUtil.getUserContext().voMapRegisterIgnores(VisitScheduleItem.class);
	return result;
}
 

public static ProjectionList getContainerStatusProjectionList() {
	ProjectionList projectionList = Projections.projectionList();
	projectionList.add(Projections.property("component.parentComponent.componentId"));
	projectionList.add(Projections.property("contSts.statusDate"));
	projectionList.add(Projections.sum("contSts.totalContainers"));
    projectionList.add(Projections.groupProperty("component.parentComponent.componentId"));
	projectionList.add(Projections.groupProperty("contSts.statusDate"));
	return projectionList;
}
 

public static List<ApiStatus> getEnvApis(String startDate, String endDate, int envId, String componentIdsStrg,
		Session session, Class enitityClass) throws ParseException {
	final SimpleDateFormat sdf = new SimpleDateFormat(SIMPLE_DATE_FORMAT);
	List<Integer> comIdList = DaoUtil.convertCSVToList(componentIdsStrg);
	
	Date sDate = sdf.parse(startDate);
	Date eDate = sdf.parse(endDate);
			
	Criteria apiCriteria = session.createCriteria(enitityClass, "apiSts");
	apiCriteria.createCriteria("apiSts.component", "component");
	apiCriteria.createCriteria("apiSts.environment", "environment");
	apiCriteria.add(Restrictions.gt("apiSts.statusDate", sDate ));
	apiCriteria.add(Restrictions.le("apiSts.statusDate", eDate ));
	if(envId != 0){
		apiCriteria.add(Restrictions.eq("environment.environmentId", envId));
	}
	if(comIdList.size() > 0){
		apiCriteria.add(Restrictions.in("component.componentId", comIdList));
	}
	apiCriteria.add(Restrictions.eq("environment.envLock", 0));
	ProjectionList projectionList = Projections.projectionList();
	projectionList.add(Projections.property("component.componentId"));
	projectionList.add(Projections.property("apiSts.statusDate"));
	projectionList.add(Projections.property("component.componentName"));
       projectionList.add(Projections.sum("apiSts.totalApi"));
    projectionList.add(Projections.groupProperty("component.componentId"));
	projectionList.add(Projections.groupProperty("apiSts.statusDate"));
    apiCriteria.setProjection(projectionList);
    @SuppressWarnings("unchecked")
	List<Object[]> appList = apiCriteria.list();
    
    List<ApiStatus> apiStatusList = new ArrayList<ApiStatus>();
       for (Object[] aRow : appList) {
       	ApiStatus apisStatus = new ApiStatus();
           Integer comId = (Integer) aRow[0];
           apisStatus.setComponentId(comId);
           Date statsDate = (Date) aRow[1];
           apisStatus.setStatusDate(statsDate.toString());
           String compName = (String) aRow[2];
           apisStatus.setComponentName(compName);
           long totalApi = (long) aRow[3];
           apisStatus.setTotalApis(totalApi);
           apiStatusList.add(apisStatus); 		
       }
       session.close();
       return apiStatusList;
}
 

public static TpsLatency getCurrentTpsAndLatency(String environment, String componentIdsStrg, boolean isParent,
		String platform, SessionFactory sessionFactory, EnvironmentDao environmentDao) {
	Session session = sessionFactory.openSession();
	List<Integer> comIdList = convertCSVToList(componentIdsStrg);
	
	Criteria crtCurrentCont = session.createCriteria(TpsServiceEntity.class, "tpsLat");
	crtCurrentCont.createCriteria("tpsLat.component", "component");
	if(environment != null && environment.length() != 0 && !environment.equalsIgnoreCase("all")){
		int envId = environmentDao.getEnironmentIdFromName(environment);
		crtCurrentCont.add(Restrictions.eq("environment.environmentId", envId));
	}
	/**
	 * Adding platform criteria for current TPS & Latency.
	 */
	if(platform!=null && !platform.equalsIgnoreCase("All"))
		crtCurrentCont.add(Restrictions.eq("component.platform", platform));
	if (comIdList.size() > 0) {
		if (isParent) {
			crtCurrentCont.add(Restrictions.in("component.parentComponent.componentId", comIdList));				
		} else {
			crtCurrentCont.add(Restrictions.in("component.componentId", comIdList));				
		}
	}
	
	ProjectionList projList = Projections.projectionList();
	projList.add(Projections.sum("tpsValue"));
	projList.add(Projections.sum("latencyValue"));
	crtCurrentCont.setProjection(projList);
	List<Object[]> curTpsLatList = crtCurrentCont.list();
	TpsLatency tpsLat = new TpsLatency();
        for (Object[] aRow : curTpsLatList) {
        	if(aRow[0] == null){
        		continue;
        	}
        	double tpsVal = (double) aRow[0];
            double latencyVal = (double) aRow[1];
            tpsLat.setTpsValue(tpsVal);
            tpsLat.setLatencyValue(latencyVal);
        } 	   
	session.close();
	return tpsLat;
}
 

@Override
protected Collection<StratificationRandomizationList> handleFindByTrialTagValues(Long trialId, Set<Long> selectionSetValueIds) throws Exception {
	org.hibernate.Criteria stratificationRandomizationListCriteria = createStratificationRandomizationListCriteria("stratificationRandomizationList0");
	if (trialId != null) {
		stratificationRandomizationListCriteria.add(Restrictions.eq("trial.id", trialId.longValue()));
	}
	// multiple joins dont produce "duplicate association" exception
	// https://forum.hibernate.org/viewtopic.php?t=931249
	// if (selectionSetValueIds != null) {
	// Iterator<Long> it = selectionSetValueIds.iterator();
	// int i = 0;
	// while (it.hasNext()) {
	// Long selectionSetValueId = it.next();
	// if (selectionSetValueId != null) {
	// stratificationRandomizationListCriteria.createCriteria("selectionSetValues", "selectionSetValues" +
	// i,CriteriaSpecification.INNER_JOIN).add(Restrictions.idEq(selectionSetValueId.longValue()));
	// i++;
	// }
	// }
	// }
	if (selectionSetValueIds != null && selectionSetValueIds.size() > 0) {
		org.hibernate.Criteria selectionSetValuesCriteria = stratificationRandomizationListCriteria.createCriteria("selectionSetValues",
				CriteriaSpecification.INNER_JOIN);
		selectionSetValuesCriteria.add(Restrictions.in("id", selectionSetValueIds));
		ProjectionList proj = Projections.projectionList();
		proj.add(Projections.id());
		proj.add(Projections.sqlGroupProjection(
				"count(*) as selectionSetValuesCount",
				"{alias}.id having count(*) = " + selectionSetValueIds.size(),
				new String[] { "selectionSetValuesCount" },
				new org.hibernate.type.Type[] { Hibernate.LONG }));
		stratificationRandomizationListCriteria.setProjection(proj);
		ArrayList<StratificationRandomizationList> result = new ArrayList<StratificationRandomizationList>();
		Iterator it = stratificationRandomizationListCriteria.list().iterator();
		while (it.hasNext()) {
			result.add(this.load((Long) ((Object[]) it.next())[0]));
		}
		return result;
	} else {
		return stratificationRandomizationListCriteria.list();
	}
}
 

public static List<TpsLatencyHistory> getTpsAndLatOfParent(String startDate, String endDate, String environment,
		String componentIdsStrg, String platform, Class entityClass, SessionFactory sessionFactory, EnvironmentDao environmentDao) throws ParseException {

	Session session = sessionFactory.openSession();
	
	final SimpleDateFormat sdf1 = new SimpleDateFormat(SIMPLE_DATE_FORMAT);
	List<Integer> comIdList = convertCSVToList(componentIdsStrg);
	
	Date sDate = sdf1.parse(startDate);
	Date eDate = sdf1.parse(endDate);
	Criteria tpaLatCriteria = session.createCriteria(entityClass, "tpsLat");
	tpaLatCriteria.createCriteria("tpsLat.component", "component");
	tpaLatCriteria.add(Restrictions.gt("tpsLat.statusDate", sDate ));
	tpaLatCriteria.add(Restrictions.le("tpsLat.statusDate", eDate ));
	tpaLatCriteria.add(Restrictions.isNotNull("component.parentComponent.componentId"));
	if(environment != null){
		int envId = environmentDao.getEnironmentIdFromName(environment);
		tpaLatCriteria.add(Restrictions.eq("tpsLat.environment.environmentId", envId));
	}

	if(platform!=null && !platform.equalsIgnoreCase("All")){
		tpaLatCriteria.add(Restrictions.eq("component.platform", platform));
	}
	if(comIdList.size() > 0){
		tpaLatCriteria.add(Restrictions.in("component.parentComponent.componentId", comIdList));
	}
	
	ProjectionList projectionList = Projections.projectionList();
	projectionList.add(Projections.property("component.parentComponent.componentId"));
	projectionList.add(Projections.property("tpsLat.statusDate"));
	projectionList.add(Projections.sum("tpsLat.tpsValue"));
	projectionList.add(Projections.sum("tpsLat.latencyValue"));
    projectionList.add(Projections.groupProperty("component.parentComponent.componentId"));
	projectionList.add(Projections.groupProperty("tpsLat.statusDate"));
	tpaLatCriteria.setProjection(projectionList);
    @SuppressWarnings("unchecked")
	List<Object[]> conList = tpaLatCriteria.list();
    List<TpsLatencyHistory> tpsLatList = new ArrayList<TpsLatencyHistory>();
       for (Object[] aRow : conList) {
       	TpsLatencyHistory tpsLatHist = new TpsLatencyHistory();
           Integer comId = (Integer) aRow[0];
           tpsLatHist.setComponentId(comId);
           Date statusDate = (Date) aRow[1];
           tpsLatHist.setStatusDate(statusDate.toString());
           double tpsVal = (double) aRow[2];
           tpsLatHist.setTpsValue(tpsVal);
           double latencyVal = (double) aRow[3];
           tpsLatHist.setLatencyValue(latencyVal);
           tpsLatList.add(tpsLatHist);   		
       } 	    
       session.close();
	return tpsLatList;
}
 

/**
 * Get all the Environment wise Counters parameters information from DB
 */
@SuppressWarnings({ "rawtypes", "unchecked" })
private List<EnvCounters> getAllEnvCounterDetails() {
    Session session = sessionFactory.openSession();
    Criteria counterCriteria = session.createCriteria(CounterEntity.class, "counter");
    counterCriteria.addOrder(Order.asc("counter.position"));
    counterCriteria.createCriteria("counter.envCounter", "ec");
    counterCriteria.createCriteria("ec.environment", "environment");

    ProjectionList projectionList = Projections.projectionList();
    projectionList.add(Projections.property("ec.envCounterId"));
    projectionList.add(Projections.property("counter.counterName"));
    projectionList.add(Projections.property("environment.environmentName"));
    projectionList.add(Projections.property("ec.metricTypeId"));
    projectionList.add(Projections.property("ec.parameter1"));
    projectionList.add(Projections.property("ec.parameter2"));
    projectionList.add(Projections.property("ec.platform"));
    counterCriteria.setProjection(projectionList);
    List<Object[]> result1 = counterCriteria.list();

    Criteria counterCriteria1 = session.createCriteria(CounterEntity.class, "counter");
    counterCriteria.addOrder(Order.asc("counter.position"));
    Criteria envCounCriteria1 = counterCriteria1.createCriteria("counter.envCounter", "envCounter");
    envCounCriteria1.add(Restrictions.isNull("environmentId"));

    ProjectionList projectionList1 = Projections.projectionList();
    projectionList1.add(Projections.property("envCounter.envCounterId"));
    projectionList1.add(Projections.property("counter.counterName"));
    projectionList1.add(Projections.property("envCounter.environmentId"));
    projectionList1.add(Projections.property("envCounter.metricTypeId"));
    projectionList1.add(Projections.property("envCounter.parameter1"));
    projectionList1.add(Projections.property("envCounter.parameter2"));
    projectionList1.add(Projections.property("envCounter.platform"));
    counterCriteria1.setProjection(projectionList1);
    List<Object[]> result2 = counterCriteria1.list();
    session.close();

    result1.addAll(result2);
    List unionResult = result1;
    List<EnvCounters> envCounterList = new ArrayList<EnvCounters>();
    for (Object[] counterObj : (List<Object[]>) unionResult) {
        EnvCounters envCounters = new EnvCounters();
        envCounters.setEnvCounterId(Integer.valueOf(String.valueOf(counterObj[0])));
        envCounters.setCounterName(String.valueOf(counterObj[1]));
        if (counterObj[2] != null) {
            envCounters.setEnvName(String.valueOf(counterObj[2]));
        } else {
            envCounters.setEnvName("");
        }
        envCounters.setMetricTypeId(Integer.valueOf(String.valueOf(counterObj[3])));
        envCounters.setParameter1(String.valueOf(counterObj[4]));
        envCounters.setParameter2(String.valueOf(counterObj[5]));
        envCounters.setPlatform(String.valueOf(counterObj[6]));
        envCounterList.add(envCounters);
    }
    return envCounterList;
}