List of usage examples for org.hibernate.transform AliasToEntityMapResultTransformer INSTANCE
AliasToEntityMapResultTransformer INSTANCE
To view the source code for org.hibernate.transform AliasToEntityMapResultTransformer INSTANCE.
Click Source Link
From source file:com.rdsic.pcm.common.GenericHql.java
/** * Execute a native SQL query//from w w w .j a va 2 s . c om * * @param sql The SQL select query * @param maxRow Limit the number of rows to be returned * @param params List of parameters follow by name,value * @return */ public List<Map<String, T>> querySQL(String sql, int maxRow, Object... params) { Query q = HibernateUtil.currentSession().createSQLQuery(sql); q.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE); q.setMaxResults(maxRow); HibernateUtil.currentSession().setCacheMode(CacheMode.IGNORE); if (params != null) { for (int i = 0; i < params.length; i++) { q.setParameter(params[i].toString(), params[++i]); } } return q.list(); }
From source file:com.rdsic.pcm.service.pe.ProcessFunction.java
public String execute(String jsonStr) throws PCMException { String input = prepareInput(jsonStr); String hql = String.format("call %s(:param) ", new Object[] { this.function }); Query q = HibernateUtil.currentSession().createQuery(hql); q.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE); q.setParameter(":param", input); List<Map<String, Object>> list = q.list(); return new JSONObject(list).toString(); }
From source file:com.scopix.periscope.corporatestructuremanagement.dao.CorporateStructureManagementHibernateDAOImpl.java
License:Open Source License
@Override public List<EvidenceProvider> getEvidenceProvidersByType(String type, Integer storeId) { List<EvidenceProvider> evidenceProviders = new ArrayList<EvidenceProvider>(); StringBuilder sqlEvidenceProviders = new StringBuilder(); sqlEvidenceProviders.append("select ep.* "); sqlEvidenceProviders.append(" from evidence_provider ep, evidence_provider_type ept "); sqlEvidenceProviders.append(" where ep.evidence_provider_type_id = ept.id "); sqlEvidenceProviders.append(" and ep.store_id = ").append(storeId).append(" "); sqlEvidenceProviders.append(" and ept.description = '").append(type).append("'"); Session session = this.getSession(); 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")); evidenceProviders.add(ep);/* www. j a va 2 s. c o m*/ } return evidenceProviders; }
From source file:com.scopix.periscope.corporatestructuremanagement.dao.CorporateStructureManagementHibernateDAOImpl.java
License:Open Source License
@Override public List<VadaroEvent> getLastVadaroEvents(Integer minutes, Integer storeId, Date timeEvidence) { List<VadaroEvent> ret = new ArrayList<VadaroEvent>(); String date = DateFormatUtils.format(timeEvidence, "yyyy-MM-dd HH:mm:ss"); StringBuilder sql = new StringBuilder(); // sql.append("select * from vadaro_event "); // sql.append("where store_id = ").append(storeId); // sql.append(" and server_time >= (now() - interval '").append(minutes).append(" minutes') "); // sql.append(" order by server_time desc"); sql.append("select * from vadaro_event "); sql.append("where store_id = ").append(storeId); sql.append(" and time >= (to_timestamp('").append(date).append("', 'YYYY-MM-DD HH24:MI:SS') - interval '") .append(minutes).append(" minutes') "); sql.append(" and time <= '").append(date).append("'"); sql.append(" order by time desc"); Session session = this.getSession(); try {/* www . j av a 2 s. c o m*/ Query query = session.createSQLQuery(sql.toString()); query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE); List<Map<String, Object>> list = query.list(); for (Map<String, Object> row : list) { VadaroEvent event = new VadaroEvent(); event.setId((Integer) row.get("id")); event.setAbandoned((Integer) row.get("abandoned")); event.setCameraName((String) row.get("camera_name")); event.setEntered((Integer) row.get("entered")); event.setExited((Integer) row.get("exited")); event.setLength((Integer) row.get("length")); event.setService((String) row.get("service")); event.setServiceTime((Double) row.get("service_time")); event.setTime((Date) row.get("time")); event.setWaitTime((Double) row.get("wait_time")); event.setServerTime((Date) row.get("server_time")); Store store = new Store(); store.setId((Integer) row.get("store_id")); event.setStore(store); ret.add(event); } } catch (HibernateException e) { log.error(e, e); } finally { this.releaseSession(session); } return ret; }
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());/* w ww. j a v 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
/** * * @param evidenceRequestIds/*from ww w. jav a 2 s . co 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 av a 2s.com " 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 w w . j av 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 www.jav a2 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 {/*from ww w .j a va2s .c om*/ 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); } }