List of usage examples for org.hibernate Query setTimestamp
@Deprecated @SuppressWarnings("unchecked") default Query<R> setTimestamp(String name, Date value)
From source file:es.emergya.bbdd.dao.RecursoHome.java
License:Open Source License
@SuppressWarnings("unchecked") @Transactional(readOnly = true, rollbackFor = Throwable.class, propagation = Propagation.REQUIRES_NEW) public Recurso[] getNearest(double x, double y, Integer num, Usuario u) { List<Recurso> res = new LinkedList<Recurso>(); Session currentSession = getSession(); currentSession.clear();/*from ww w .j av a 2 s .c o m*/ Coordinate coordinate = new Coordinate(x, y); Point p = (new GeometryFactory()).createPoint(coordinate); p.setSRID(LogicConstants.SRID); try { final DistanceOrder order = DistanceOrder.des("historico1_.geom", p); // final Criteria criteria = currentSession.createCriteria( // Recurso.class).setResultTransformer( // Criteria.DISTINCT_ROOT_ENTITY).createAlias("historicoGps", // "historico").createCriteria("flotas").createCriteria( // "roles").createCriteria("usuarios").add( // Restrictions.eq("id", u.getId())).addOrder(order) // .setMaxResults(num); Query criteria = currentSession.createSQLQuery("select this_.* from recursos this_ " + "inner join flotas flota2_ on this_.flota_x_flota=flota2_.x_flota " + "inner join ROLES_X_FLOTAS roles15_ on flota2_.x_flota=roles15_.X_FLOTA " + "inner join roles rol3_ on roles15_.X_ROL=rol3_.x_rol " + "inner join usuarios usuario4_ on rol3_.x_rol=usuario4_.fk_roles " + "inner join historico_gps historico1_ on this_.fk_historico_gps=historico1_.x_historico " + ((u != null && u.getId() != null) ? "where usuario4_.x_usuarios=:ID " + " and historico1_.marca_temporal > :TIMEOUT " : "") + "order by " + order.toString()).addEntity(Recurso.class) .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).setMaxResults(num); if (u != null && u.getId() != null) { criteria = criteria.setLong("ID", u.getId()); } Calendar timeout = Calendar.getInstance(); timeout.add(Calendar.MINUTE, -LogicConstants.getInt("AVL_TIMEOUT", 30)); criteria.setTimestamp("TIMEOUT", timeout.getTime()); log.debug(criteria); res = criteria.list(); for (Recurso uniqueResult : res) { if (uniqueResult != null) { if (uniqueResult.getPatrullas() != null) { uniqueResult.getPatrullas().getId(); } if (uniqueResult.getFlotas() != null) { uniqueResult.getFlotas().getId(); } } } } catch (Throwable e) { log.error("Error al calcular los ms cercanos", e); } log.info("Encontrados " + res.size() + " recursos"); return res.toArray(new Recurso[0]); }
From source file:es.logongas.iothome.dao.impl.MeasureDAOImplHibernate.java
private List<Measure> getMeasures(Device device, String sql, Date startDateTime, Date endDateTime, Integer startMinuteFilter, Integer endMinuteFilter, Integer startHourFilter, Integer endHourFilter, Integer startWeekDayFilter, Integer endWeekDayFilter, Integer startMonthDayFilter, Integer endMonthDayFilter) { Session session = sessionFactory.getCurrentSession(); Query query = session.createSQLQuery(sql); int numParameter = 0; query.setInteger(numParameter++, device.getIdDevice()); if (startDateTime != null) { query.setTimestamp(numParameter++, startDateTime); }//from w w w . j ava2 s.com if (endDateTime != null) { query.setTimestamp(numParameter++, endDateTime); } List<Object[]> listDatos = query.list(); List<Measure> measures = new ArrayList<Measure>(); for (Object[] datos : listDatos) { Double rawStream0 = (Double) datos[1]; Double rawStream1 = (Double) datos[2]; Double rawStream2 = (Double) datos[3]; Double rawStream3 = (Double) datos[4]; float stream0; float stream1; float stream2; float stream3; if (rawStream0 == null) { stream0 = 0; } else { stream0 = rawStream0.floatValue(); } if (rawStream1 == null) { stream1 = 0; } else { stream1 = rawStream1.floatValue(); } if (rawStream2 == null) { stream2 = 0; } else { stream2 = rawStream2.floatValue(); } if (rawStream3 == null) { stream3 = 0; } else { stream3 = rawStream3.floatValue(); } Measure measure = new Measure(0, (Date) datos[0], null, stream0, stream1, stream2, stream3); measures.add(measure); } return measures; }
From source file:fr.hoteia.qalingo.core.dao.impl.EmailDaoImpl.java
License:Apache License
public int deleteSendedEmail(Timestamp before) { // Session session = (Session) em.getDelegate(); // String sql = "DELETE FROM Email WHERE dateCreate <= :before"; // Query query = session.createQuery(sql); // query.setTimestamp("before", before); // int row = query.executeUpdate(); Session session = (Session) em.getDelegate(); String sql = "FROM Email WHERE dateCreate <= :before AND status = '" + Email.EMAIl_STATUS_SENDED + "'"; Query query = session.createQuery(sql); query.setTimestamp("before", before); List<Email> emails = (List<Email>) query.list(); if (emails != null) { for (Iterator<Email> iterator = emails.iterator(); iterator.hasNext();) { Email email = (Email) iterator.next(); deleteEmail(email);//from ww w.j av a2s. c o m } return emails.size(); } return 0; }
From source file:gcom.atendimentopublico.RepositorioAtendimentoPublicoHBM.java
License:Open Source License
/** * [UC1120] Gerar Relatrio de religao de clientes inadimplentes. * * @author Hugo Leonardo/*www . j ava2s.c om*/ * @date 25/01/2011 * * @throws ErroRepositorioException */ public Collection pesquisarRelatorioReligacaoClientesInadiplentesOS( FiltrarRelatorioReligacaoClientesInadiplentesHelper relatorioHelper) throws ErroRepositorioException { Collection retorno = null; Session session = HibernateUtil.getSession(); String consulta = ""; Query query = null; Map parameters = new HashMap(); try { if (relatorioHelper.getEscolhaRelatorio() == 1) { consulta = " select distinct (os.id), os.imovel.id, os.dataEncerramento " + " from OrdemServico os " + " inner join os.imovel imo " + " inner join os.ordemServicoUnidades orseunid " + " inner join os.servicoTipo servtipo " + " inner join imo.localidade loca "; } else if (relatorioHelper.getEscolhaRelatorio() == 2) { consulta = " select os.imovel.id, count(os.imovel.id) " + " from OrdemServico os " + " inner join os.imovel imo " + " inner join os.servicoTipo servtipo " + " inner join imo.localidade loca "; } else if (relatorioHelper.getEscolhaRelatorio() == 3 || relatorioHelper.getEscolhaRelatorio() == 4) { /* consulta = " select os.imovel.id, " + " orseunid.usuario.id, " + " count(os.imovel.id), " + " count (orseunid.usuario.id) " + " from OrdemServico os " + " inner join os.imovel imo " + " inner join os.ordemServicoUnidades orseunid " + " inner join os.servicoTipo servtipo " + " inner join imo.localidade loca "; */ consulta = " select orseunid.usuario.id " + " from OrdemServico os " + " inner join os.imovel imo " + " inner join os.ordemServicoUnidades orseunid " + " inner join os.servicoTipo servtipo " + " inner join imo.localidade loca "; } else if (relatorioHelper.getEscolhaRelatorio() == 5) { consulta = " select os.imovel.id, " + " orseunid.usuario.id, " + " clieimo.cliente.id, " + " count(os.imovel.id), " + " count (orseunid.usuario.id), " + " count (clieimo.cliente.id) " + " from OrdemServico os " + " inner join os.imovel imo " + " inner join imo.clienteImoveis clieimo " + " inner join os.ordemServicoUnidades orseunid " + " inner join os.servicoTipo servtipo " + " inner join imo.localidade loca "; } // Gerncia Regional if (relatorioHelper.getGerenciaRegional() != null) { consulta += " inner join loca.gerenciaRegional gereg "; } // Unidade Negcio if (relatorioHelper.getUnidadeNegocio() != null) { consulta += " inner join loca.unidadeNegocio unineg "; } // Setor Comercial if (relatorioHelper.getSetorComercial() != null) { consulta += " inner join imo.setorComercial setcom "; } // Cliente if (relatorioHelper.getCliente() != null && relatorioHelper.getEscolhaRelatorio() != 5) { consulta += " inner join imo.clienteImoveis clieimo "; } // Usurio if (relatorioHelper.getUsuario() != null) { consulta += " inner join orseunid.usuario usua "; } consulta += " where 1=1 "; if (relatorioHelper.getEscolhaRelatorio() == 1) { if (relatorioHelper.getDataInicioEncerramento() != null && relatorioHelper.getDataFimEncerramento() != null) { consulta += " and os.dataEncerramento between :dataInicialEncerramento and :dataFinalEncerramento "; parameters.put("dataInicialEncerramento", relatorioHelper.getDataInicioEncerramento()); parameters.put("dataFinalEncerramento", relatorioHelper.getDataFimEncerramento()); } } else { consulta += " and os.dataEncerramento between :dataInicialEncerramento and :dataFinalEncerramento "; parameters.put("dataInicialEncerramento", relatorioHelper.getDataInicioRecorrencia()); parameters.put("dataFinalEncerramento", relatorioHelper.getDataFimRecorrencia()); } consulta += " and os.situacao = 2 " + " and servtipo.constanteFuncionalidadeTipoServico = 243 "; if (relatorioHelper.getEscolhaRelatorio() == 1) { consulta += " and orseunid.atendimentoRelacaoTipo in (1, 3) "; } else if (relatorioHelper.getEscolhaRelatorio() == 2) { //consulta += " and orseunid.atendimentoRelacaoTipo in (1, 3) "; } else if (relatorioHelper.getEscolhaRelatorio() == 3) { consulta += " and orseunid.atendimentoRelacaoTipo = 1 "; } else if (relatorioHelper.getEscolhaRelatorio() == 4) { consulta += " and orseunid.atendimentoRelacaoTipo = 3 "; } else if (relatorioHelper.getEscolhaRelatorio() == 5) { consulta += " and orseunid.atendimentoRelacaoTipo = 3 " + " and clieimo.clienteRelacaoTipo.id = 2 " + " and clieimo.dataFimRelacao is null "; } // Gerncia Regional if (relatorioHelper.getGerenciaRegional() != null) { consulta += " and gereg.id = :gerencia "; parameters.put("gerencia", relatorioHelper.getGerenciaRegional()); } // Unidade Negcio if (relatorioHelper.getUnidadeNegocio() != null) { consulta += " and unineg.id = :unidade "; parameters.put("unidade", relatorioHelper.getUnidadeNegocio()); } // Localidade if (relatorioHelper.getLocalidade() != null) { consulta += " and loca.id = :localidade "; parameters.put("localidade", relatorioHelper.getLocalidade()); } // Setor Comercial if (relatorioHelper.getSetorComercial() != null) { consulta += " and setcom.id = :setor "; parameters.put("setor", relatorioHelper.getSetorComercial()); } // Cliente if (relatorioHelper.getCliente() != null) { consulta += " and clieimo.clie.id = :cliente "; parameters.put("cliente", relatorioHelper.getCliente()); } // Usurio if (relatorioHelper.getUsuario() != null) { consulta += " and usua.id = :usuario "; parameters.put("usuario", relatorioHelper.getUsuario()); } if (relatorioHelper.getEscolhaRelatorio() == 1) { consulta += " order by os.imovel, os.id "; } else if (relatorioHelper.getEscolhaRelatorio() == 2) { consulta += " group by os.imovel.id " + " having count(os.imovel.id) > 1 " + " order by os.imovel.id "; } else if (relatorioHelper.getEscolhaRelatorio() == 3 || relatorioHelper.getEscolhaRelatorio() == 4) { /* consulta += " group by os.imovel.id, orseunid.usuario.id " + " having count(os.imovel.id) > 1 and count(orseunid.usuario.id) > 1 " + " order by os.imovel.id "; */ consulta += " group by orseunid.usuario.id " + " having count(orseunid.usuario.id) > 1 " + " order by orseunid.usuario.id "; } else if (relatorioHelper.getEscolhaRelatorio() == 5) { consulta += " group by os.imovel.id, orseunid.usuario.id, clieimo.cliente.id " + " having count(os.imovel.id) > 1 and count(orseunid.usuario.id) > 1 " + " and count(clieimo.cliente.id) > 1 " + " order by os.imovel.id "; } query = session.createQuery(consulta); //ITERA OS PARAMETROS E COLOCA // OS MESMOS NA QUERY Set set = parameters.keySet(); Iterator iterMap = set.iterator(); while (iterMap.hasNext()) { String key = (String) iterMap.next(); if (parameters.get(key) instanceof Set) { Set setList = (HashSet) parameters.get(key); query.setParameterList(key, setList); } else if (parameters.get(key) instanceof Collection) { Collection collection = (ArrayList) parameters.get(key); query.setParameterList(key, collection); } else if (parameters.get(key) instanceof Date) { Date data = (Date) parameters.get(key); query.setTimestamp(key, data); } else { query.setParameter(key, parameters.get(key)); } } retorno = query.list(); } catch (HibernateException e) { throw new ErroRepositorioException(e, "Erro no Hibernate"); } finally { HibernateUtil.closeSession(session); } return retorno; }
From source file:gcom.atendimentopublico.RepositorioAtendimentoPublicoHBM.java
License:Open Source License
/** * [UC1120] Gerar Relatrio de religao de clientes inadimplentes * * @author Hugo Leonardo//from www.jav a 2 s .c om * @date 01/02/2011 * * @throws ErroRepositorioException */ public Collection pesquisarRelatorioReligacaoClientesInadiplentesRecorrentes(Integer imovel, FiltrarRelatorioReligacaoClientesInadiplentesHelper relatorioHelper) throws ErroRepositorioException { Collection retorno = null; Session session = HibernateUtil.getSession(); String consulta = ""; Query query = null; Map parameters = new HashMap(); try { consulta = " select distinct (os.id) " + " from OrdemServico os " + " inner join os.imovel imo " + " inner join os.ordemServicoUnidades orseunid " + " inner join os.servicoTipo servtipo " + " inner join imo.localidade loca "; // Gerncia Regional if (relatorioHelper.getGerenciaRegional() != null) { consulta += " inner join loca.gerenciaRegional gereg "; } // Unidade Negcio if (relatorioHelper.getUnidadeNegocio() != null) { consulta += " inner join loca.unidadeNegocio unineg "; } // Setor Comercial if (relatorioHelper.getSetorComercial() != null) { consulta += " inner join imo.setorComercial setcom "; } // Cliente if (relatorioHelper.getCliente() != null) { consulta += " inner join imo.clienteImoveis clieimo "; } // Usurio if (relatorioHelper.getUsuario() != null) { consulta += " inner join orseunid.usuario usua "; } consulta += " where imo.id = :imovel "; parameters.put("imovel", imovel); if (relatorioHelper.getEscolhaRelatorio() == 1) { if (relatorioHelper.getDataInicioEncerramento() != null && relatorioHelper.getDataFimEncerramento() != null) { consulta += " and os.dataEncerramento between :dataInicialEncerramento and :dataFinalEncerramento "; parameters.put("dataInicialEncerramento", relatorioHelper.getDataInicioEncerramento()); parameters.put("dataFinalEncerramento", relatorioHelper.getDataFimEncerramento()); } } else { if (relatorioHelper.getDataInicioRecorrencia() != null && relatorioHelper.getDataFimRecorrencia() != null) { consulta += " and os.dataEncerramento between :dataInicialRecorrencia and :dataFinalRecorrencia "; parameters.put("dataInicialRecorrencia", relatorioHelper.getDataInicioRecorrencia()); parameters.put("dataFinalRecorrencia", relatorioHelper.getDataFimRecorrencia()); } } consulta += " and os.situacao = 2 " + " and servtipo.constanteFuncionalidadeTipoServico = 243 " + " and orseunid.atendimentoRelacaoTipo.id in (1, 3) "; // Gerncia Regional if (relatorioHelper.getGerenciaRegional() != null) { consulta += " and gereg.id = :gerencia "; parameters.put("gerencia", relatorioHelper.getGerenciaRegional()); } // Unidade Negcio if (relatorioHelper.getUnidadeNegocio() != null) { consulta += " and unineg.id = :unidade "; parameters.put("unidade", relatorioHelper.getUnidadeNegocio()); } // Localidade if (relatorioHelper.getLocalidade() != null) { consulta += " and loca.id = :localidade "; parameters.put("localidade", relatorioHelper.getLocalidade()); } // Setor Comercial if (relatorioHelper.getSetorComercial() != null) { consulta += " and setcom.id = :setor "; parameters.put("setor", relatorioHelper.getSetorComercial()); } // Cliente if (relatorioHelper.getCliente() != null) { consulta += " and clieimo.cliente.id = :cliente "; parameters.put("cliente", relatorioHelper.getCliente()); } // Usurio if (relatorioHelper.getUsuario() != null) { consulta += " and usua.id = :usuario "; parameters.put("usuario", relatorioHelper.getUsuario()); } consulta += " order by os.id "; query = session.createQuery(consulta); //ITERA OS PARAMETROS E COLOCA // OS MESMOS NA QUERY Set set = parameters.keySet(); Iterator iterMap = set.iterator(); while (iterMap.hasNext()) { String key = (String) iterMap.next(); if (parameters.get(key) instanceof Set) { Set setList = (HashSet) parameters.get(key); query.setParameterList(key, setList); } else if (parameters.get(key) instanceof Collection) { Collection collection = (ArrayList) parameters.get(key); query.setParameterList(key, collection); } else if (parameters.get(key) instanceof Date) { Date data = (Date) parameters.get(key); query.setTimestamp(key, data); } else { query.setParameter(key, parameters.get(key)); } } retorno = query.list(); } catch (HibernateException e) { throw new ErroRepositorioException(e, "Erro no Hibernate"); } finally { HibernateUtil.closeSession(session); } return retorno; }
From source file:gcom.atendimentopublico.RepositorioAtendimentoPublicoHBM.java
License:Open Source License
/** * [UC1120] Gerar Relatrio de religao de clientes inadimplentes. * * @author Hugo Leonardo//from w ww.j av a 2s.c om * @date 09/02/2011 * * @throws ErroRepositorioException */ public Collection pesquisarRelatorioReligacaoClientesInadiplentesDatasOS( FiltrarRelatorioReligacaoClientesInadiplentesHelper relatorioHelper, Integer imovel) throws ErroRepositorioException { Collection<Object[]> retorno = null; Session session = HibernateUtil.getSession(); String consulta = ""; Query query = null; Map parameters = new HashMap(); try { consulta = " select distinct(os.dataEncerramento), os.id " + " from OrdemServico os " + " inner join os.imovel imo " + " inner join os.ordemServicoUnidades orseunid " + " inner join os.servicoTipo servtipo " + " inner join imo.localidade loca "; // Gerncia Regional if (relatorioHelper.getGerenciaRegional() != null) { consulta += " inner join loca.gerenciaRegional gereg "; } // Unidade Negcio if (relatorioHelper.getUnidadeNegocio() != null) { consulta += " inner join loca.unidadeNegocio unineg "; } // Setor Comercial if (relatorioHelper.getSetorComercial() != null) { consulta += " inner join imo.setorComercial setcom "; } // Cliente if (relatorioHelper.getCliente() != null) { consulta += " inner join imo.clienteImoveis clieimo "; } // Usurio if (relatorioHelper.getUsuario() != null) { consulta += " inner join orseunid.usuario usua "; } consulta += " where imo.id = :imovel " + " and os.dataEncerramento between :dataInicialEncerramento and :dataFinalEncerramento "; parameters.put("imovel", imovel); parameters.put("dataInicialEncerramento", relatorioHelper.getDataInicioRecorrencia()); parameters.put("dataFinalEncerramento", relatorioHelper.getDataFimRecorrencia()); consulta += " and os.situacao = 2 " + " and servtipo.constanteFuncionalidadeTipoServico = 243 " + " and orseunid.atendimentoRelacaoTipo in (1,3) "; // Gerncia Regional if (relatorioHelper.getGerenciaRegional() != null) { consulta += " and gereg.id = :gerencia "; parameters.put("gerencia", relatorioHelper.getGerenciaRegional()); } // Unidade Negcio if (relatorioHelper.getUnidadeNegocio() != null) { consulta += " and unineg.id = :unidade "; parameters.put("unidade", relatorioHelper.getUnidadeNegocio()); } // Localidade if (relatorioHelper.getLocalidade() != null) { consulta += " and loca.id = :localidade "; parameters.put("localidade", relatorioHelper.getLocalidade()); } // Setor Comercial if (relatorioHelper.getSetorComercial() != null) { consulta += " and setcom.id = :setor "; parameters.put("setor", relatorioHelper.getSetorComercial()); } // Cliente if (relatorioHelper.getCliente() != null) { consulta += " and clieimo.cliente.id = :cliente "; parameters.put("cliente", relatorioHelper.getCliente()); } // Usurio if (relatorioHelper.getUsuario() != null) { consulta += " and usua.id = :usuario "; parameters.put("usuario", relatorioHelper.getUsuario()); } consulta += " order by os.dataEncerramento "; query = session.createQuery(consulta); //ITERA OS PARAMETROS E COLOCA // OS MESMOS NA QUERY Set set = parameters.keySet(); Iterator iterMap = set.iterator(); while (iterMap.hasNext()) { String key = (String) iterMap.next(); if (parameters.get(key) instanceof Set) { Set setList = (HashSet) parameters.get(key); query.setParameterList(key, setList); } else if (parameters.get(key) instanceof Collection) { Collection collection = (ArrayList) parameters.get(key); query.setParameterList(key, collection); } else if (parameters.get(key) instanceof Date) { Date data = (Date) parameters.get(key); query.setTimestamp(key, data); } else { query.setParameter(key, parameters.get(key)); } } retorno = query.list(); } catch (HibernateException e) { throw new ErroRepositorioException(e, "Erro no Hibernate"); } finally { HibernateUtil.closeSession(session); } return retorno; }
From source file:gcom.atendimentopublico.RepositorioAtendimentoPublicoHBM.java
License:Open Source License
/** * [UC1120] Gerar Relatrio de religao de clientes inadimplentes * * @author Hugo Leonardo//from www . ja va2 s. c o m * @date 16/02/2011 * * @throws ErroRepositorioException */ public Collection pesquisarRecorrenciaPorUsuarioQueAbriuOuEncerrouOS(Integer usuario, FiltrarRelatorioReligacaoClientesInadiplentesHelper relatorioHelper) throws ErroRepositorioException { Collection retorno = null; Session session = HibernateUtil.getSession(); String consulta = ""; Query query = null; Map parameters = new HashMap(); try { consulta = " select os.id, imo.id, os.dataEncerramento " + " from OrdemServico os " + " inner join os.imovel imo " + " inner join os.ordemServicoUnidades orseunid " + " inner join os.servicoTipo servtipo " + " inner join imo.localidade loca "; // Gerncia Regional if (relatorioHelper.getGerenciaRegional() != null) { consulta += " inner join loca.gerenciaRegional gereg "; } // Unidade Negcio if (relatorioHelper.getUnidadeNegocio() != null) { consulta += " inner join loca.unidadeNegocio unineg "; } // Setor Comercial if (relatorioHelper.getSetorComercial() != null) { consulta += " inner join imo.setorComercial setcom "; } // Cliente if (relatorioHelper.getCliente() != null) { consulta += " inner join imo.clienteImoveis clieimo "; } // Usurio if (relatorioHelper.getUsuario() != null) { //consulta +=" inner join orseunid.usuario usua "; } consulta += " where orseunid.usuario.id = :usuario "; parameters.put("usuario", usuario); if (relatorioHelper.getEscolhaRelatorio() == 1) { if (relatorioHelper.getDataInicioEncerramento() != null && relatorioHelper.getDataFimEncerramento() != null) { consulta += " and os.dataEncerramento between :dataInicialEncerramento and :dataFinalEncerramento "; parameters.put("dataInicialEncerramento", relatorioHelper.getDataInicioEncerramento()); parameters.put("dataFinalEncerramento", relatorioHelper.getDataFimEncerramento()); } } else { if (relatorioHelper.getDataInicioRecorrencia() != null && relatorioHelper.getDataFimRecorrencia() != null) { consulta += " and os.dataEncerramento between :dataInicialRecorrencia and :dataFinalRecorrencia "; parameters.put("dataInicialRecorrencia", relatorioHelper.getDataInicioRecorrencia()); parameters.put("dataFinalRecorrencia", relatorioHelper.getDataFimRecorrencia()); } } consulta += " and os.situacao = 2 " + " and servtipo.constanteFuncionalidadeTipoServico = 243 "; if (relatorioHelper.getEscolhaRelatorio() == 3) { consulta += " and orseunid.atendimentoRelacaoTipo.id = 1 "; } else if (relatorioHelper.getEscolhaRelatorio() == 4) { consulta += " and orseunid.atendimentoRelacaoTipo.id = 3 "; } // Gerncia Regional if (relatorioHelper.getGerenciaRegional() != null) { consulta += " and gereg.id = :gerencia "; parameters.put("gerencia", relatorioHelper.getGerenciaRegional()); } // Unidade Negcio if (relatorioHelper.getUnidadeNegocio() != null) { consulta += " and unineg.id = :unidade "; parameters.put("unidade", relatorioHelper.getUnidadeNegocio()); } // Localidade if (relatorioHelper.getLocalidade() != null) { consulta += " and loca.id = :localidade "; parameters.put("localidade", relatorioHelper.getLocalidade()); } // Setor Comercial if (relatorioHelper.getSetorComercial() != null) { consulta += " and setcom.id = :setor "; parameters.put("setor", relatorioHelper.getSetorComercial()); } // Cliente if (relatorioHelper.getCliente() != null) { consulta += " and clieimo.cliente.id = :cliente "; parameters.put("cliente", relatorioHelper.getCliente()); } // Usurio if (relatorioHelper.getUsuario() != null) { consulta += " and orseunid.usuario.id = :usuario "; parameters.put("usuario", relatorioHelper.getUsuario()); } consulta += " order by os.id "; query = session.createQuery(consulta); //ITERA OS PARAMETROS E COLOCA // OS MESMOS NA QUERY Set set = parameters.keySet(); Iterator iterMap = set.iterator(); while (iterMap.hasNext()) { String key = (String) iterMap.next(); if (parameters.get(key) instanceof Set) { Set setList = (HashSet) parameters.get(key); query.setParameterList(key, setList); } else if (parameters.get(key) instanceof Collection) { Collection collection = (ArrayList) parameters.get(key); query.setParameterList(key, collection); } else if (parameters.get(key) instanceof Date) { Date data = (Date) parameters.get(key); query.setTimestamp(key, data); } else { query.setParameter(key, parameters.get(key)); } } retorno = query.list(); } catch (HibernateException e) { throw new ErroRepositorioException(e, "Erro no Hibernate"); } finally { HibernateUtil.closeSession(session); } return retorno; }
From source file:gov.nih.nci.cadsrapi.dao.orm.CartORMDAOImpl.java
License:BSD License
public List<Cart> cartSearch2(Cart exampleCart) throws DAOException, Exception { List<Cart> results = new ArrayList<Cart>(); Session session = getSession();/*w ww . j a va 2 s . c om*/ //Transaction t = session.beginTransaction(); StringBuilder query = new StringBuilder(); query.append("from gov.nih.nci.cadsr.objectcart.domain.Cart where"); if (exampleCart.getId() != null) query.append(" id = :cartId"); else { int andCntr = 0; if (exampleCart.getUserId() != null && exampleCart.getUserId().length() > 0) { query.append(" userId = :userId"); andCntr++; } if (exampleCart.getName() != null && exampleCart.getName().length() > 0) { if (andCntr > 0) query.append(" and"); query.append(" name = :name"); andCntr++; } /* if (exampleCart.getType() != null && exampleCart.getType().length() > 0){ if (andCntr >0) query.append(" and"); query.append(" type = :type"); }*/ } query.append(" and (expirationDate > :expirationDate or expirationDate is null)"); Query q = session.createQuery(query.toString()); String[] params = q.getNamedParameters(); for (String param : params) { if ("cartId".equals(param)) q.setInteger(param, exampleCart.getId()); else { if ("userId".equals(param)) q.setString(param, exampleCart.getUserId()); if ("name".equals(param)) q.setString(param, exampleCart.getName()); /*if ("type".equals(param)) q.setString(param, exampleCart.getType());*/ } } q.setTimestamp("expirationDate", new Timestamp(System.currentTimeMillis())); try { results = (List<Cart>) q.list(); } catch (JDBCException ex) { ex.printStackTrace(); log.error("JDBC Exception in ORMDAOImpl ", ex); throw new DAOException("JDBC Exception in ORMDAOImpl ", ex); } catch (org.hibernate.HibernateException hbmEx) { hbmEx.printStackTrace(); log.error(hbmEx.getMessage()); throw new DAOException("DAO:Hibernate problem ", hbmEx); } catch (Exception e) { e.printStackTrace(); log.error("Exception ", e); throw new DAOException("Exception in ORMDAOImpl ", e); } finally { try { //t.commit(); session.close(); } catch (Exception eSession) { log.error("Could not close the session - " + eSession.getMessage()); throw new DAOException("Could not close the session " + eSession); } } return results; }
From source file:gov.nih.nci.cadsrapi.dao.orm.CleanerDAO.java
License:BSD License
public void clean() { if (sessionFactory == null) { setFactory();// w w w . j ava2 s. c o m this.setSessionFactory(sessionFactory); } Session session = getSession(); Transaction tx = session.beginTransaction(); //Check for unexpired carts that have been active within the expiration interval and reset expiration time. int expirationInterval = 4 * 24 * 60; //Four days, in minutes int sleepTime = 60; //One hour, in minutes //Defaults int publicEmptyExpirationDays = 4 * 24 * 60; String emptyExpirationSQL = " (SYSDATE + INTERVAL '" + publicEmptyExpirationDays + "' MINUTE)"; int publicFullExpirationDays = 30 * 24 * 60; //30 days, in minutes String fullExpirationSQL = " (SYSDATE + INTERVAL '" + publicFullExpirationDays + "' MINUTE)"; try { int temp = Integer.valueOf(PropertiesLoader.getProperty("cart.time.expiration.minutes")); expirationInterval = temp; } catch (Exception e) { log.error(e); e.printStackTrace(); } try { int temp = Integer.valueOf(PropertiesLoader.getProperty("cart.cleaner.sleep.minutes")); sleepTime = temp; } catch (Exception e) { log.error(e); e.printStackTrace(); } try { int temp = Integer.valueOf(PropertiesLoader.getProperty("cart.public.empty.expiration.minutes")); publicEmptyExpirationDays = temp; } catch (Exception e) { log.error(e); e.printStackTrace(); } try { int temp = Integer.valueOf(PropertiesLoader.getProperty("cart.public.full.expiration.minutes")); publicFullExpirationDays = temp; } catch (Exception e) { log.error(e); e.printStackTrace(); } //Timestamps are in milliseconds Timestamp now = new Timestamp(System.currentTimeMillis()); Timestamp nowMinusTwiceSleep = new Timestamp(now.getTime() - sleepTime * 60 * 1000 * 2); //Converting minutes to milliseconds Timestamp nowPlusExpirationInterval = new Timestamp(now.getTime() + expirationInterval * 60 * 1000); //Converting minutes to milliseconds Query updateActiveCarts = session.createQuery( "update gov.nih.nci.cadsr.objectcart.domain.Cart set expirationDate = :nowPlusExpirationInterval" + " where (lastWriteDate > :nowMinusTwiceSleep or lastReadDate > :nowMinusTwiceSleep) and expirationDate > :now and expirationDate < :nowPlusExpirationInterval"); updateActiveCarts.setTimestamp("nowPlusExpirationInterval", nowPlusExpirationInterval); updateActiveCarts.setTimestamp("nowMinusTwiceSleep", nowMinusTwiceSleep); updateActiveCarts.setTimestamp("now", now); if (publicEmptyExpirationDays > 0 && publicEmptyExpirationDays < 365 * 24 * 60) //Check expiration is within a year emptyExpirationSQL = " (SYSDATE + INTERVAL '" + publicEmptyExpirationDays + "' MINUTE)"; else if (publicEmptyExpirationDays == 0) emptyExpirationSQL = "SYSDATE"; if (publicFullExpirationDays > 0 && publicFullExpirationDays < 365) //Check expiration is within a year fullExpirationSQL = " (SYSDATE + INTERVAL '" + publicFullExpirationDays + "' MINUTE)"; else if (publicFullExpirationDays == 0) fullExpirationSQL = "SYSDATE"; //Set expiration date to emptyExpirationSQL if the user starts with 'PublicUser' and the current expiration date is null String initializeSessionCartSql = "UPDATE cart c" + " set expiration_Date = " + emptyExpirationSQL + " where" + " (c.user_Id like 'PublicUser%') and " + " (c.expiration_Date is null)"; Query initPublicCarts = session.createSQLQuery(initializeSessionCartSql); //Set expiration date to fullExpiration if the user starts with 'PublicUser', the cart has been active (read or written to) in the last day and the cart has items //String nonEmptyCartSql = "UPDATE cart c left join cart_object co on c.id = co.cart_id " + //" set expiration_Date = "+fullExpirationSQL+" where" + //" (c.user_Id like 'PublicUser%') and " + //" (c.last_write_date > DATE_SUB(SYSDATE, INTERVAL "+ (sleepTime * 2)+" MINUTE) OR c.last_read_date > DATE_SUB(SYSDATE, INTERVAL "+ (sleepTime * 2) +" MINUTE)) and" + //" (co.id is not null)"; String nonEmptyCartSql = "UPDATE cart c " + " set expiration_Date = " + fullExpirationSQL + " where" + " (c.user_Id like 'PublicUser%') and " + " (c.last_write_date > (SYSDATE - INTERVAL '" + (sleepTime * 2) + "' MINUTE) OR c.last_read_date > (SYSDATE - INTERVAL '" + (sleepTime * 2) + "' MINUTE)) and" + " EXISTS (select id from cart_object co where co.id is not null and co.cart_id = c.id)"; Query expNonEmptyPublicCarts = session.createSQLQuery(nonEmptyCartSql); //Now delete expired carts (carts where expiration date is in the past) //REQUIRES ON-DELETE Cascade support in underlying database on the //CartObject cart_id FK constraint Query deleteCartQuery = session.createQuery( "delete from gov.nih.nci.cadsr.objectcart.domain.Cart " + "where expirationDate <=:now"); deleteCartQuery.setTimestamp("now", now); try { int resetResults = -1; try { resetResults = updateActiveCarts.executeUpdate(); } catch (Exception e) { e.printStackTrace(); } if (resetResults > 0) log.debug("Reset expiration date for " + resetResults + "active carts"); log.debug("Reset expiration date for " + resetResults + "active carts"); /* GF 28500 */ int expResults = -1; try { expResults = initPublicCarts.executeUpdate(); } catch (Exception e) { e.printStackTrace(); } if (expResults > 0) log.debug("Expiration date set for " + expResults + " PublicUser carts"); int expNEPCResults = expNonEmptyPublicCarts.executeUpdate(); if (expNEPCResults > 0) log.debug("Expiration date set for " + expNEPCResults + " PublicUser carts"); /* GF 28500 */ int results = -1; try { results = deleteCartQuery.executeUpdate(); } catch (Exception e) { e.printStackTrace(); } if (results > 0) { log.debug("Deleted " + results + " carts at " + now.toString()); System.out.println("Deleted " + results + " carts at " + now.toString()); } else { System.out.println("****** Nothing is deleted! ********"); } } catch (JDBCException ex) { log.error("JDBC Exception in ORMDAOImpl ", ex); ex.printStackTrace(); } catch (org.hibernate.HibernateException hbmEx) { log.error(hbmEx.getMessage()); hbmEx.printStackTrace(); } catch (Exception e) { log.error("Exception ", e); e.printStackTrace(); } finally { try { tx.commit(); session.close(); } catch (Exception eSession) { log.error("Could not close the session - " + eSession.getMessage()); eSession.printStackTrace(); } } }
From source file:gov.nih.nci.objectCart.dao.orm.CartORMDAOImpl.java
License:BSD License
public List<Cart> cartSearch(Cart exampleCart) throws DAOException, Exception { List<Cart> results = new ArrayList<Cart>(); Session session = getSession();//w w w .j a va 2 s . c o m Transaction t = session.beginTransaction(); StringBuilder query = new StringBuilder(); query.append("from Cart where"); if (exampleCart.getId() != null) query.append(" id = :cartId"); else { int andCntr = 0; if (exampleCart.getUserId() != null && exampleCart.getUserId().length() > 0) { query.append(" userId = :userId"); andCntr++; } if (exampleCart.getName() != null && exampleCart.getName().length() > 0) { if (andCntr > 0) query.append(" and"); query.append(" name = :name"); andCntr++; } /* if (exampleCart.getType() != null && exampleCart.getType().length() > 0){ if (andCntr >0) query.append(" and"); query.append(" type = :type"); }*/ } query.append(" and (expirationDate > :expirationDate or expirationDate is null)"); Query q = session.createQuery(query.toString()); String[] params = q.getNamedParameters(); for (String param : params) { if ("cartId".equals(param)) q.setInteger(param, exampleCart.getId()); else { if ("userId".equals(param)) q.setString(param, exampleCart.getUserId()); if ("name".equals(param)) q.setString(param, exampleCart.getName()); /*if ("type".equals(param)) q.setString(param, exampleCart.getType());*/ } } q.setTimestamp("expirationDate", new Timestamp(System.currentTimeMillis())); try { results = (List<Cart>) q.list(); } catch (JDBCException ex) { log.error("JDBC Exception in ORMDAOImpl ", ex); throw new DAOException("JDBC Exception in ORMDAOImpl ", ex); } catch (org.hibernate.HibernateException hbmEx) { log.error(hbmEx.getMessage()); throw new DAOException("DAO:Hibernate problem ", hbmEx); } catch (Exception e) { log.error("Exception ", e); throw new DAOException("Exception in ORMDAOImpl ", e); } finally { try { t.commit(); session.close(); } catch (Exception eSession) { log.error("Could not close the session - " + eSession.getMessage()); throw new DAOException("Could not close the session " + eSession); } } return results; }