Example usage for org.hibernate Query setResultTransformer

List of usage examples for org.hibernate Query setResultTransformer

  1. HOME
  2. Java
  3. org.hibernate
  4. org.hibernate.*
  5. Query
  6. setResultTransformer

Introduction

In this page you can find the example usage for org.hibernate Query setResultTransformer.

Prototype

@Deprecated
Query<R> setResultTransformer(ResultTransformer transformer);

Source Link

Document

Set a strategy for handling the query results.

Usage

From source file:com.scopix.periscope.corporatestructuremanagement.dao.CorporateStructureManagementHibernateDAOImpl.java

License:Open Source License

@Override
public List<EvidenceProvider> getEvidenceProvidersListByArea(EvidenceProvider evidenceProvider) {
    List<EvidenceProvider> evidenceProviders = new ArrayList<EvidenceProvider>();
    Set<Integer> areas = new HashSet<Integer>();
    for (Area a : evidenceProvider.getAreas()) {
        areas.add(a.getId());/*from  w  w w.j av  a2s.c o  m*/
    }
    StringBuilder sqlEvidenceProviders = new StringBuilder();
    sqlEvidenceProviders.append("select ep.id, ep.definition_data, ep.description, ep.name, ");
    sqlEvidenceProviders.append(" ep.evidence_provider_type_id, ep.store_id ");
    sqlEvidenceProviders.append(" from evidence_provider ep, rel_evidence_provider_area repa ");
    sqlEvidenceProviders.append(" where ep.store_id  = ").append(evidenceProvider.getStore().getId());
    if (!areas.isEmpty()) {
        sqlEvidenceProviders.append(" and repa.area_id in (").append(StringUtils.join(areas, ",")).append(")");
    }
    sqlEvidenceProviders.append(" and repa.evidence_provider_id = ep.id order by ep.description");

    Session session = this.getSession();
    try {
        Query query = session.createSQLQuery(sqlEvidenceProviders.toString());
        query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        List<Map<String, Object>> list = query.list();
        for (Map<String, Object> row : list) {
            EvidenceProvider ep = new EvidenceProvider();
            ep.setId((Integer) row.get("id"));
            ep.setDefinitionData((String) row.get("definition_data"));
            ep.setDescription((String) row.get("description"));
            ep.setName((String) row.get("name"));
            EvidenceProviderType evidenceProviderType = new EvidenceProviderType();
            evidenceProviderType.setId((Integer) row.get("evidence_provider_type_id"));
            ep.setEvidenceProviderType(evidenceProviderType);
            Store store = new Store();
            store.setId((Integer) row.get("store_id"));
            ep.setStore(store);
            // se deben recuperar los RelationEvidenceProviderLocation asociados
            StringBuilder sqlRelationEvidenceProviderLocation = new StringBuilder();
            sqlRelationEvidenceProviderLocation.append("select id, ");
            sqlRelationEvidenceProviderLocation
                    .append(" (case when default_evidence_provider is null then false ");
            sqlRelationEvidenceProviderLocation
                    .append(" else default_evidence_provider end) as default_evidence_provider, ");
            sqlRelationEvidenceProviderLocation.append(" location, view_order, ");
            sqlRelationEvidenceProviderLocation
                    .append(" evidence_provider_from_id, evidence_provider_to_id, area_id ");
            sqlRelationEvidenceProviderLocation.append(" from relation_evidence_provider_location ");
            sqlRelationEvidenceProviderLocation.append(" where evidence_provider_from_id  = ")
                    .append(ep.getId());
            sqlRelationEvidenceProviderLocation.append(" and area_id in(").append(StringUtils.join(areas, ","))
                    .append(")");
            Query query2 = session.createSQLQuery(sqlRelationEvidenceProviderLocation.toString());
            query2.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
            List<Map<String, Object>> list2 = query2.list();
            // ISSU con el NULL del default_evidence_provider
            for (Map<String, Object> row2 : list2) {
                RelationEvidenceProviderLocation repl = new RelationEvidenceProviderLocation();
                repl.setId((Integer) row2.get("id"));
                repl.setDefaultEvidenceProvider((Boolean) row2.get("default_evidence_provider"));
                repl.setLocation(Location.valueOf((String) row2.get("location")));
                repl.setViewOrder((Integer) row2.get("view_order"));

                Area a = new Area();
                a.setId((Integer) row2.get("area_id"));
                repl.setArea(a);
                repl.setEvidenceProviderFrom(ep);
                EvidenceProvider epTo = new EvidenceProvider();
                epTo.setId((Integer) row2.get("evidence_provider_to_id"));
                repl.setEvidenceProviderTo(epTo);
                ep.getRelationEvidenceProviderLocationsFrom().add(repl);
            }
            evidenceProviders.add(ep);
        }
    } catch (HibernateException e) {
        log.error(e, e);
    } finally {
        this.releaseSession(session);
    }
    return evidenceProviders;
}

From source file:com.scopix.periscope.corporatestructuremanagement.dao.CorporateStructureManagementHibernateDAOImpl.java

License:Open Source License

@Override
public List<EvidenceRequestDTO> getActiveEvidenceRequestDTOs(Integer storeId) throws ScopixException {
    List<EvidenceRequestDTO> evidenceRequestDTOs = new ArrayList<EvidenceRequestDTO>();
    StringBuilder sbHql = new StringBuilder();
    sbHql.append("select ");
    sbHql.append("   er.id as businessServicesRequestId, ");
    sbHql.append("   er.evidenceProvider.id as deviceId, ");
    sbHql.append("   er.evidenceTime as requestedTime, ");
    sbHql.append("   er.day as dayOfWeek, ");
    sbHql.append("   '{v}' as requestType, ");
    sbHql.append("   er.duration as duration,");
    sbHql.append("   er.priorization as priorization,");
    sbHql.append("   st.live as live");
    sbHql.append(" from ");
    sbHql.append("   EvidenceRequest er,");
    sbHql.append("   ExtractionPlanCustomizing epc, ");
    sbHql.append("   ExtractionPlanRange epr, ");
    sbHql.append("   ExtractionPlanRangeDetail eprd , ");
    sbHql.append("   SituationTemplate st ");
    sbHql.append("where ");
    sbHql.append("   epc.store.id = ").append(storeId).append(" and ");
    sbHql.append("   epc.active = true and ");
    sbHql.append("   epr.extractionPlanCustomizing.id = epc.id and ");
    sbHql.append("   eprd.extractionPlanRange.id = epr.id and ");
    sbHql.append("   er.extractionPlanRangeDetail.id = eprd.id and ");
    sbHql.append("   epc.situationTemplate.id = st.id and  ");
    sbHql.append("   st.active = true and ");
    sbHql.append("   er.type = :type1 ");

    for (EvidenceType type : EvidenceType.values()) {
        String classTextConvert = getClassConvert(type);
        String s = StringUtils.replace(sbHql.toString(), "{v}", classTextConvert);
        Query q = this.getSession().createQuery(s);
        q.setParameter("type1", type);
        evidenceRequestDTOs// w  w  w .j  a v a 2  s .co m
                .addAll(q.setResultTransformer(Transformers.aliasToBean(EvidenceRequestDTO.class)).list());
    }

    // evidenceRequestDTOs = this.getSession().createQuery(sbHql.toString()).
    // setResultTransformer(Transformers.aliasToBean(EvidenceRequestDTO.class)).list();
    return evidenceRequestDTOs;
}

From source file:com.scopix.periscope.corporatestructuremanagement.dao.CorporateStructureManagementHibernateDAOImpl.java

License:Open Source License

/**
 *
 * @param evidenceRequestIds//from  w ww  . ja v a 2  s.c  o m
 * @return
 */
@Override
public List<EvidenceRequest> getEvidenceRequestsList(List<Integer> evidenceRequestIds) throws ScopixException {
    List<EvidenceRequest> evidenceRequests = null;
    try {
        evidenceRequests = new ArrayList<EvidenceRequest>();
        StringBuilder sql = new StringBuilder();
        sql.append("select er.id, er.day, er.duration, er.evidence_provider_id,  ");
        sql.append("er.evidence_time, er.metric_id, er.type,  ");
        sql.append("m.description AS metric_description, ep.description as ep_description ");
        sql.append("from evidence_request er, metric m, evidence_provider ep ");
        sql.append("where  ");
        sql.append("m.id = er.metric_id ");
        sql.append("and ep.id = er.evidence_provider_id and er.id in (");

        StringBuilder ids = new StringBuilder();
        for (Integer id : evidenceRequestIds) {
            ids.append(id);
            ids.append(", ");
        }
        sql.append(ids.substring(0, ids.length() - 2));
        sql.append(")");

        Session session = this.getSession();
        try {
            Query query = session.createSQLQuery(sql.toString());
            query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
            List<Map<String, Object>> list = query.list();
            for (Map m : list) {
                EvidenceRequest er = null;
                if (m.get("duration") != null) {
                    er = new RequestedVideo();
                    ((RequestedVideo) er).setDuration((Integer) m.get("duration"));
                } else {
                    er = new RequestedImage();
                }
                er.setId((Integer) m.get("id"));
                er.setDay((Integer) m.get("day"));
                EvidenceProvider ep = new EvidenceProvider();
                ep.setId((Integer) m.get("evidence_provider_id"));
                ep.setDescription((String) m.get("ep_description"));
                er.setEvidenceProvider(ep);
                er.setEvidenceTime((Date) m.get("evidence_time"));
                EvidenceType et = EvidenceType.valueOf((String) m.get("type"));
                er.setType(et);
                Metric mt = new Metric();
                mt.setId((Integer) m.get("metric_id"));
                mt.setDescription((String) m.get("metric_description"));
                er.setMetric(mt);
                evidenceRequests.add(er);
            }

        } finally {
            this.releaseSession(session);
        }
    } catch (DataAccessResourceFailureException e) {
        throw new ScopixException(e);
    } catch (IllegalStateException e) {
        throw new ScopixException(e);
    } catch (HibernateException e) {
        throw new ScopixException(e);
    }
    return evidenceRequests;
}

From source file:com.scopix.periscope.corporatestructuremanagement.dao.CorporateStructureManagementHibernateDAOImpl.java

License:Open Source License

@Override
public List<SituationTemplate> getSituationTemplateListByArea(Set<Integer> areaIds) {

    List<SituationTemplate> situationTemplates = new ArrayList<SituationTemplate>();
    if (areaIds != null && !areaIds.isEmpty()) {
        StringBuilder sql = new StringBuilder();
        sql.append("select distinct ");
        sql.append(//from   w ww . j  a  v a 2  s .  co m
                " st.id, st.active, st.evidence_spring_bean_evaluator_name, st.name, st.area_type_id , st.product_id ");
        sql.append(" from ");
        sql.append(" situation_template  st, ");
        sql.append(" situation s, ");
        sql.append(" metric m ");
        sql.append(" where m.area_id in(").append(StringUtils.join(areaIds, ",")).append(")");
        sql.append(" and s.id = m.situation_id ");
        sql.append(" and st.id = s.situation_template_id ");
        sql.append(" and active = true ");
        sql.append(" order by st.name");
        Session session = this.getSession();
        try {
            Query query = session.createSQLQuery(sql.toString());
            query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
            List<Map<String, Object>> list = query.list();
            for (Map<String, Object> row : list) {
                SituationTemplate st = new SituationTemplate();
                st.setId((Integer) row.get("id"));
                st.setActive((Boolean) row.get("active"));
                st.setEvidenceSpringBeanEvaluatorName((String) row.get("evidence_spring_bean_evaluator_name"));
                st.setName((String) row.get("name"));

                AreaType at = new AreaType();
                at.setId((Integer) row.get("area_type_id"));
                st.setAreaType(at);

                Product p = new Product();
                p.setId((Integer) row.get("product_id"));
                st.setProduct(p);
                situationTemplates.add(st);
            }
        } catch (DataAccessResourceFailureException e) {
            log.error(e, e);
        } catch (IllegalStateException e) {
            log.error(e, e);
        } catch (HibernateException e) {
            log.error(e, e);
        } finally {
            this.releaseSession(session);
        }
    }
    return situationTemplates;
}

From source file:com.scopix.periscope.corporatestructuremanagement.dao.CorporateStructureManagementHibernateDAOImpl.java

License:Open Source License

@Override
public List<SensorAndEvidenceExtractionServicesServerDTO> getSensorAndEvidenceExtractionServicesServerList(
        List<String> sensors) throws ScopixException {
    log.info("start");
    List<SensorAndEvidenceExtractionServicesServerDTO> listResp = new LinkedList<SensorAndEvidenceExtractionServicesServerDTO>();

    StringBuilder sql = new StringBuilder();
    sql.append(//from  w ww . j a  v  a  2 s.c  o  m
            "select s.name as sensor_name, eess.id, eess.name, eess.ssh_address, eess.ssh_local_tunnel_port,");
    sql.append(" eess.ssh_password, eess.ssh_port, eess.ssh_remote_tunnel_port, eess.ssh_user, eess.url,");
    sql.append(" p.name as store_name");
    sql.append(" from sensor s,place p, evidence_extraction_services_server eess");
    sql.append(" where");
    sql.append(" s.store_id = p.id");
    sql.append(" and p.evidence_extraction_services_server_id = eess.id");
    sql.append(" and s.name in (");
    sql.append(StringUtils.join(sensors, ","));
    sql.append(" )");
    Session session = this.getSession();
    try {
        SensorAndEvidenceExtractionServicesServerDTO dto = null;
        EvidenceExtractionServicesServerDTO eessDTO = null;
        Query query = session.createSQLQuery(sql.toString());
        query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        List<Map<String, Object>> list = query.list();
        for (Map<String, Object> row : list) {
            dto = new SensorAndEvidenceExtractionServicesServerDTO();
            eessDTO = new EvidenceExtractionServicesServerDTO();

            dto.setSensorName((String) row.get("sensor_name"));
            dto.setStoreName((String) row.get("store_name"));

            eessDTO.setIdAtBusinessServices((Integer) row.get("id"));
            eessDTO.setName((String) row.get("name"));
            eessDTO.setName((String) row.get("ssh_address"));
            eessDTO.setName((String) row.get("ssh_local_tunnel_port"));
            eessDTO.setName((String) row.get("ssh_password"));
            eessDTO.setName((String) row.get("ssh_port"));
            eessDTO.setName((String) row.get("ssh_remote_tunnel_port"));
            eessDTO.setName((String) row.get("ssh_user"));
            eessDTO.setName((String) row.get("url"));

            dto.setEvidenceExtractionServicesServerDTO(eessDTO);

            listResp.add(dto);
        }
    } catch (DataAccessResourceFailureException e) {
        log.error(e, e);
    } catch (IllegalStateException e) {
        log.error(e, e);
    } catch (HibernateException e) {
        log.error(e, e);
    } finally {
        this.releaseSession(session);
    }

    log.info("end");
    return listResp;
}

From source file:com.scopix.periscope.evaluationmanagement.dao.EvidenceRegionTransferDAOImpl.java

License:Open Source License

@Override
public List<EvidenceRegionTransferDTO> getEvidenceRegionTransferByCriteria(Integer storeId,
        Integer situationTemplateId, Integer transmNotTransm, Date startDate, Date endDate) {
    Session session = this.getSession();
    try {//from  ww w. j  av  a 2  s .  c  o  m
        List<EvidenceRegionTransferDTO> list = new ArrayList<EvidenceRegionTransferDTO>();

        StringBuilder sql = new StringBuilder();
        sql.append(
                "select distinct (e.id), e.evidence_path, e.evidence_date, ert.region_server_name, ert.completed, ert.transmisition_date, ert.error_message ");
        sql.append(
                "from place store, situation_template st,situation s, metric m, evidence_request er, rel_evidence_request_evidence rel, evidence_region_transfer ert, evidence e ");
        sql.append("where rel.evidence_id = e.id ");
        sql.append("and er.id = rel.evidence_request_id ");
        sql.append("and m.id = er.metric_id ");
        sql.append("and s.id = m.situation_id ");
        sql.append("and st.id = s.situation_template_id ");
        sql.append("and store.id = m.store_id ");
        sql.append("and e.id = ert.evidence_id ");
        if (storeId != null) {
            sql.append("and store.id = ").append(storeId.toString()).append(" ");
        }
        if (situationTemplateId != null) {
            sql.append("and st.id = ").append(situationTemplateId.toString()).append(" ");
        }
        if (transmNotTransm.equals(1)) {
            sql.append("and ert.completed = true").append(" ");
        } else if (transmNotTransm.equals(2)) {
            sql.append("and ert.completed = false").append(" ");
        }
        List<Map<String, Object>> resultSql;
        if (startDate != null && endDate != null) {
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
            sql.append("and  ert.transmisition_date >= '").append(sdf.format(startDate)).append("'");
            sql.append("and  ert.transmisition_date <= '").append(sdf.format(endDate)).append("'");
            sql.append("order by ert.transmisition_date desc limit 1000");
            Query query = session.createSQLQuery(sql.toString());
            query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
            resultSql = query.list();
        } else {
            sql.append("order by ert.transmisition_date desc limit 1000");
            Query query = session.createSQLQuery(sql.toString());
            query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
            resultSql = query.list();
        }

        for (Map<String, Object> row : resultSql) {
            Integer evidenceId = (Integer) row.get("id");
            String evidenceFileName = (String) row.get("evidence_path");
            Date evidenceDate = (Date) row.get("evidence_date");
            String evidenceDateString = DateFormatUtils.format(evidenceDate, "dd-MM-yyyy HH:mm:ss");
            String regionServerName = (String) row.get("region_server_name");
            Boolean completed = (Boolean) row.get("completed");
            Date transmisitionDate = (Date) row.get("transmisition_date");
            String transmisitionDateString = DateFormatUtils.format(transmisitionDate, "dd-MM-yyyy HH:mm:ss");
            String errorMessage = (String) row.get("error_message");
            if (errorMessage == null) {
                errorMessage = "";
            }
            EvidenceRegionTransferDTO evidenceRegionTransferDTO = new EvidenceRegionTransferDTO();
            evidenceRegionTransferDTO.setEvidenceId(evidenceId);
            evidenceRegionTransferDTO.setEvidenceFileName(evidenceFileName);
            evidenceRegionTransferDTO.setEvidenceDate(evidenceDateString);
            evidenceRegionTransferDTO.setRegionServerName(regionServerName);
            evidenceRegionTransferDTO.setCompleted(completed);
            evidenceRegionTransferDTO.setTransmissionDate(transmisitionDateString);
            evidenceRegionTransferDTO.setErrorMessage(errorMessage);
            list.add(evidenceRegionTransferDTO);
        }
        return list;
    } finally {
        this.releaseSession(session);
    }
}

From source file:com.scopix.periscope.evaluationmanagement.dao.EvidenceRegionTransferDAOImpl.java

License:Open Source License

@Override
public List<Map<String, Object>> getRegionTransferStats(Integer storeId, Integer situationTemplateId,
        Integer transmNotTransm, Date startDate, Date endDate) {
    Session session = this.getSession();
    try {//w  w w  . ja  v  a 2  s  .c  o m
        StringBuilder sql = new StringBuilder();
        sql.append("select  count (distinct e.id) , ert.completed ");
        sql.append(
                "from place store, situation_template st,situation s, metric m, evidence_request er, rel_evidence_request_evidence rel, evidence_region_transfer ert, evidence e ");
        sql.append("where rel.evidence_id = e.id ");
        sql.append("and er.id = rel.evidence_request_id ");
        sql.append("and m.id = er.metric_id ");
        sql.append("and s.id = m.situation_id ");
        sql.append("and st.id = s.situation_template_id ");
        sql.append("and store.id = m.store_id ");
        sql.append("and e.id = ert.evidence_id ");
        if (storeId != null) {
            sql.append("and store.id = ").append(storeId.toString()).append(" ");
        }
        if (situationTemplateId != null) {
            sql.append("and st.id = ").append(situationTemplateId.toString()).append(" ");
        }
        List<Map<String, Object>> resultSql;
        if (startDate != null && endDate != null) {
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
            sql.append("and  ert.transmisition_date >= '").append(sdf.format(startDate)).append("'");
            sql.append("and  ert.transmisition_date <= '").append(sdf.format(endDate)).append("'");

        }
        sql.append(" group by ert.completed ");
        Query query = session.createSQLQuery(sql.toString());
        query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        resultSql = query.list();
        return resultSql;
    } finally {
        this.releaseSession(session);
    }
}

From source file:com.scopix.periscope.evaluationmanagement.dao.EvidenceRegionTransferDAOImpl.java

License:Open Source License

@Override
public List<Map<String, Object>> getFailedPendingEvidences(Date startDate, Date endDate) {
    Session session = this.getSession();
    try {/*from  w ww.  ja va 2 s  .  com*/
        StringBuilder sql = new StringBuilder();
        sql.append(
                "SELECT DISTINCT ON (ert.id) ert.id as evidence_region_transfer_id, ert.region_server_name as region_server_name, pe.evaluation_state as pending_evaluation_state ");
        sql.append("FROM ");
        sql.append("evidence ev join evidence_region_transfer ert on ev.id = ert.evidence_id, ");
        sql.append(
                "observed_situation os left join pending_evaluation pe on  os.id = pe.observed_situation_id, ");
        sql.append("observed_metric om, ");
        sql.append("rel_observed_metric_evidence rel ");
        sql.append("WHERE rel.evidence_id = ev.id ");
        sql.append("AND rel.observed_metric_id = om.id ");
        sql.append("AND om.observed_situation_id = os.id ");
        sql.append("AND ert.completed = false ");
        List<Map<String, Object>> resultSql;
        if (startDate != null && endDate != null) {
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
            sql.append("and  ert.transmisition_date >= '").append(sdf.format(startDate)).append("'");
            sql.append("and  ert.transmisition_date <= '").append(sdf.format(endDate)).append("'");
        }
        Query query = session.createSQLQuery(sql.toString());
        query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        resultSql = query.list();
        return resultSql;
    } finally {
        this.releaseSession(session);
    }
}

From source file:com.scopix.periscope.evaluationmanagement.dao.ObservedMetricHibernateDAO.java

License:Open Source License

public List<ObservedMetric> getObservedMetricListSQL(ObservedMetric observedMetric) {
    List<ObservedMetric> observedMetrics = new ArrayList<ObservedMetric>();
    log.info("start");
    Session session = null;//from w w w .  j a  v  a  2 s .c om
    try {
        StringBuilder sql = new StringBuilder();
        sql.append("select om.id, om.evaluation_state, om.metric_id, om.observed_metric_date, ");
        sql.append("om.observed_situation_id from observed_metric om ");
        if (observedMetric != null) {
            if (observedMetric.getEvaluationState() != null) {
                sql.append(" WHERE om.evaluation_state = '");
                sql.append(observedMetric.getEvaluationState().name());
                sql.append("'");
            }
        }
        session = this.getSession();
        Query query = session.createSQLQuery(sql.toString());
        query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        List<Map<String, Object>> list = query.list();

        if (list.size() > 0) {
            for (Map m : list) {
                ObservedMetric om = new ObservedMetric();
                om.setId((Integer) m.get("id"));
                om.setEvaluationState(EvaluationState.valueOf((String) m.get("evaluation_state")));
                Metric metric = new Metric();
                metric.setId((Integer) m.get("metric_id"));
                om.setMetric(metric);
                om.setObservedMetricDate((Date) m.get("observed_metric_date"));
                ObservedSituation observedSituation = new ObservedSituation();
                observedSituation.setId((Integer) m.get("observed_situation_id"));
                om.setObservedSituation(observedSituation);

                observedMetrics.add(om);
            }
        }

    } catch (Exception ex) {
        log.error("error = " + ex.getMessage());
    } finally {
        if (session != null) {
            this.releaseSession(session);
        }
    }
    log.info("end");
    return observedMetrics;
}

From source file:com.scopix.periscope.evaluationmanagement.dao.ObservedMetricHibernateDAO.java

License:Open Source License

public List<EvidencesAndProofsDTO> getEvidenceAndProof(Integer observedMetricId) throws ScopixException {
    List<EvidencesAndProofsDTO> listResult = new ArrayList<EvidencesAndProofsDTO>();
    Session session = null;//from w ww . j  a v  a 2s.  c om
    try {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT distinct e.id AS evidence_id, p.id AS proof_id, ep.description, ep.id AS camera");
        sql.append(" FROM");
        sql.append(" evidence_evaluation ee,");
        sql.append(" rel_evidence_evaluation_evidence reee,");
        sql.append(" evidence e,");
        sql.append(" rel_evidence_request_evidence rel,");
        sql.append(" evidence_request er,");
        sql.append(" evidence_provider ep,");
        sql.append(" proof p");
        sql.append(" WHERE");
        sql.append(" ee.observed_metric_id = ");
        sql.append(observedMetricId);
        sql.append(" and reee.evidence_evaluation_id=ee.id");
        sql.append(" and reee.evidence_id = e.id");
        sql.append(" and p.evidence_evaluation_id = ee.id");
        sql.append(" and rel.evidence_id = e.id");
        sql.append(" and rel.evidence_request_id = er.id");
        sql.append(" and er.evidence_provider_id = ep.id");
        sql.append(" and ee.rejected = false");
        sql.append(" ORDER BY ep.id");

        session = this.getSession();
        Query query = session.createSQLQuery(sql.toString());
        query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        List<Map<String, Object>> list = query.list();

        if (list.size() > 0) {
            Integer evidenceId = null;
            EvidencesAndProofsDTO dto = null;
            List<Integer> proofs = null;
            for (Map m : list) {
                if (evidenceId == null || !evidenceId.equals((Integer) m.get("evidence_id"))) {
                    if (dto != null) {
                        dto.setProofsId(proofs);
                        listResult.add(dto);
                    }
                    evidenceId = (Integer) m.get("evidence_id");
                    dto = new EvidencesAndProofsDTO();
                    dto.setEvidenceId(evidenceId);
                    proofs = new ArrayList<Integer>();
                }
                proofs.add((Integer) m.get("proof_id"));
            }
            if (dto != null) {
                dto.setProofsId(proofs);
                listResult.add(dto);
            }
        } else {
            sql.append("SELECT distinct e.id AS evidence_id, -1 AS proof_id, ep.description, ep.id AS camera");
            sql.append(" FROM");
            sql.append(" evidence_evaluation ee,");
            sql.append(" rel_evidence_evaluation_evidence reee,");
            sql.append(" evidence e,");
            sql.append(" rel_evidence_request_evidence rel,");
            sql.append(" evidence_request er,");
            sql.append(" evidence_provider ep");
            sql.append(" WHERE");
            sql.append(" ee.observed_metric_id =");
            sql.append(observedMetricId);
            sql.append(" and reee.evidence_evaluation_id=ee.id");
            sql.append(" and reee.evidence_id = e.id");
            sql.append(" and rel.evidence_id = e.id");
            sql.append(" and rel.evidence_request_id = er.id");
            sql.append(" and er.evidence_provider_id = ep.id");
            sql.append(" and ee.rejected = false");
            sql.append(" ORDER BY ep.id");

            query = session.createSQLQuery(sql.toString());
            list = query.list();

            Integer evidenceId = null;
            EvidencesAndProofsDTO dto = null;
            List<Integer> proofs = null;
            for (Map m : list) {
                if (evidenceId == null || !evidenceId.equals((Integer) m.get("evidence_id"))) {
                    if (dto != null) {
                        dto.setProofsId(proofs);
                        listResult.add(dto);
                    }
                    evidenceId = (Integer) m.get("evidence_id");
                    dto = new EvidencesAndProofsDTO();
                    dto.setEvidenceId(evidenceId);
                    proofs = new ArrayList<Integer>();
                }
                proofs.add((Integer) m.get("proof_id"));
            }
            if (dto != null) {
                dto.setProofsId(proofs);
                listResult.add(dto);
            }

        }

    } catch (Exception ex) {
        log.error("error = " + ex.getMessage());
    } finally {
        if (session != null) {
            this.releaseSession(session);
        }
    }
    return listResult;
}