List of usage examples for org.hibernate SQLQuery setDate
@Deprecated @SuppressWarnings("unchecked") default Query<R> setDate(int position, Date val)
From source file:br.gov.jfrj.siga.wf.relatorio.RelTempoDoc.java
License:Open Source License
/** * Retorna os docs no perodo indicado, ordenadas pelo tempo de * demora, podendo estar ou no finalizados. * /*ww w. j av a 2 s .c om*/ * Exemplo da query: * * SELECT PI.START_,PI.END_,VI.STRINGVALUE_,PI.ID_ FROM * SIGAWF.JBPM_PROCESSINSTANCE PI, (SELECT DISTINCT PROCESSINSTANCE_, * STRINGVALUE_ FROM SIGAWF.JBPM_VARIABLEINSTANCE WHERE NAME_ LIKE 'doc_%' * AND STRINGVALUE_ LIKE '%-_' AND STRINGVALUE_ IS NOT NULL) VI, (SELECT * * FROM SIGAWF.JBPM_PROCESSDEFINITION WHERE NAME_ = 'Contratao: fase de * anlise') PD WHERE PI.PROCESSDEFINITION_=PD.ID_ AND PI.END_ IS NOT NULL * AND PI.ID_ = VI.PROCESSINSTANCE_ AND (PI.START_ >= To_Date('01/03/2011') * and PI.START_ <= To_Date('31/03/2011')) AND (PI.END_ >= * To_Date('01/03/2011') and PI.END_ <= To_Date('31/05/2011')) ; * * * @param dataInicial * @param dataFinal * @param dataFinalAte * @param dataFinalDe * @param incluirAbertos * @return */ private Set<Doc> consultarDocs(String nomeProcedimento, Date dataInicialDe, Date dataInicialAte, Date dataFinalDe, Date dataFinalAte, Boolean incluirAbertos) { SQLQuery query = null; if (incluirAbertos) { query = (SQLQuery) WfDao.getInstance().getSessao() .createSQLQuery(getSQLConsultarDocumentosFinalizadosEAbertosNoPeriodo()); } else { query = (SQLQuery) WfDao.getInstance().getSessao() .createSQLQuery(getSQLConsultarDocumentosFinalizadosNoPeriodo()); } query.addScalar("START_", new CalendarType()); query.addScalar("END_", new CalendarType()); query.addScalar("STRINGVALUE_", new StringType()); query.addScalar("ID_", new LongType()); dataInicialAte = inclusiveData(dataInicialAte); dataFinalAte = inclusiveData(dataFinalAte); query.setString("nomeProcedimento", nomeProcedimento); query.setDate("dataInicialDe", dataInicialDe); query.setDate("dataInicialAte", dataInicialAte); query.setDate("dataFinalDe", dataFinalDe); query.setDate("dataFinalAte", dataFinalAte); List<Object[]> resultado = query.list(); Set<Doc> docs = new TreeSet<Doc>(new DocComparator()); for (Object[] o : resultado) { Doc s = new Doc(); Calendar inicio = (Calendar) o[0]; Calendar fim = (Calendar) o[1]; s.setNumeroDoc(o[2].toString()); s.setInicio(inicio); s.setFim(fim); s.setProcessInstanceID((Long) (o[3])); docs.add(s); } return docs; }
From source file:br.gov.jfrj.siga.wf.relatorio.RelTempoDocDetalhado.java
License:Open Source License
private Set<Tarefa> consultarTarefas(String nomeProcedimento, Date dataInicialDe, Date dataInicialAte, Date dataFinalDe, Date dataFinalAte, Boolean incluirAbertos) { // ArrayList<Tarefa> tarefas = new ArrayList<Tarefa>(); // Tarefa t1 = new Tarefa(); // Tarefa t2 = new Tarefa(); //// ww w. j av a 2 s.co m // t1.setNome("t1"); // t1.setDataFim("01/01/2000"); // t1.setDataInicio("01/01/2000"); // t1.setDuracao("5 seg"); // // t2.setNome("t2"); // t2.setDataFim("01/01/1999"); // t2.setDataInicio("01/01/1888"); // t2.setDuracao("5 seg"); // // tarefas.add(t1); // tarefas.add(t2); // return tarefas; // String sql = // "SELECT PI.START_,PI.END_,VI.STRINGVALUE_,PI.ID_ FROM JBPM_PROCESSINSTANCE PI, (SELECT DISTINCT PROCESSINSTANCE_, STRINGVALUE_ FROM JBPM_VARIABLEINSTANCE WHERE NAME_ LIKE 'doc_%' AND STRINGVALUE_ LIKE '%-_' AND STRINGVALUE_ IS NOT NULL) VI, (SELECT ID_ FROM JBPM_PROCESSDEFINITION WHERE NAME_ = :nomeProcedimento) PD WHERE PI.PROCESSDEFINITION_=PD.ID_ AND PI.END_ IS NOT NULL AND PI.ID_ = VI.PROCESSINSTANCE_ AND (PI.START_ >= :dataInicialDe and PI.START_ <= :dataInicialAte) AND (PI.END_ >= :dataFinalDe and PI.END_ <= :dataFinalAte)"; SQLQuery query = null; if (incluirAbertos) { query = (SQLQuery) WfDao.getInstance().getSessao() .createSQLQuery(getSQLConsultarDocumentosFinalizadosEAbertosNoPeriodo()); } else { query = (SQLQuery) WfDao.getInstance().getSessao() .createSQLQuery(getSQLConsultarDocumentosFinalizadosNoPeriodo()); } query.addScalar("stringvalue_", new StringType()); query.addScalar("pd_name", new StringType()); query.addScalar("p_start", new CalendarType()); query.addScalar("p_end", new CalendarType()); query.addScalar("t_name", new StringType()); query.addScalar("t_create", new CalendarType()); query.addScalar("t_end", new CalendarType()); query.setString("nomeProcedimento", nomeProcedimento); query.setDate("dataInicialDe", dataInicialDe); query.setDate("dataInicialAte", dataInicialAte); query.setDate("dataFinalDe", dataFinalDe); query.setDate("dataFinalAte", dataFinalAte); List<Object[]> resultado = query.list(); Set<Tarefa> tarefas = new TreeSet<Tarefa>(new TarefaComparator()); for (Object[] o : resultado) { Tarefa t = new Tarefa(); t.setDataInicioProcedimento((Calendar) o[2]); t.setDataFimProcedimento((Calendar) o[3]); t.setNumeroDocumento((String) o[0]); t.setNome((String) o[4]); t.setDataInicio((Calendar) o[5]); t.setDataFim((Calendar) o[6]); tarefas.add(t); } // Set<Doc> secs = new TreeSet<Doc>(new DocComparator()); // for (Object[] o : resultado) { // Doc s = new Doc(); // Calendar inicio = (Calendar) o[0]; // Calendar fim = (Calendar) o[1]; // s.setNumeroDoc(o[2].toString()); // s.setInicio(inicio); // s.setFim(fim); // s.setProcessInstanceID((Long) (o[3])); // secs.add(s); // } // return tarefas; }
From source file:ch.systemsx.cisd.openbis.generic.server.dataaccess.db.AbstractDAO.java
License:Apache License
/** * Executes given <var>sql</var>. * <p>//from ww w. ja v a2 s . c om * Should be an <code>INSERT</code> or <code>UPDATE</code> statement. * </p> */ protected final void executeUpdate(final String sql, final Serializable... parameters) { getHibernateTemplate().execute(new HibernateCallback() { // // HibernateCallback // public final Object doInHibernate(final Session session) throws HibernateException, SQLException { final SQLQuery sqlQuery = session.createSQLQuery(sql); for (int i = 0; i < parameters.length; i++) { Serializable parameter = parameters[i]; if (parameter instanceof Long) { sqlQuery.setLong(i, (Long) parameter); } else if (parameter instanceof Integer) { sqlQuery.setInteger(i, (Integer) parameter); } else if (parameter instanceof Character) { sqlQuery.setCharacter(i, (Character) parameter); } else if (parameter instanceof Date) { sqlQuery.setDate(i, (Date) parameter); } else { sqlQuery.setSerializable(i, parameter); } } sqlQuery.executeUpdate(); return null; } }); }
From source file:com.aw.core.db.support.WhereBuilder2.java
License:Open Source License
public void setParams(SQLQuery sqlQuery) { for (int i = 0; i < params.size(); i++) { Object param = params.get(i); if (param instanceof Long) sqlQuery.setLong(i, (Long) param); else if (param instanceof Integer) sqlQuery.setInteger(i, (Integer) param); else if (param instanceof Date) sqlQuery.setDate(i, (Date) param); else if (param instanceof String) sqlQuery.setString(i, (String) param); else if (param instanceof BigDecimal) sqlQuery.setBigDecimal(i, (BigDecimal) param); else if (param == null) sqlQuery.setParameter(i, null); else/* w ww. j a va 2 s.c o m*/ throw new IllegalArgumentException("Implementar codigo param:" + param.getClass()); } //To change body of created methods use File | Settings | File Templates. }
From source file:com.bookselling.dao.SellerInvoiceDaoImpl.java
@Override public StatisticData<GenericChartUnit<Object>> profitAndQuatityStatistic(User user, Date fromDate, Date toDate) {// w w w. j ava2 s. co m StatisticData<GenericChartUnit<Object>> statisticData = new StatisticData<>(new HashSet<>()); statisticData.setLabel("Doanh thu v doanh s"); SQLQuery sqlQuery = getSession() .createSQLQuery("select DATE(createdDate) date, sum(totalPrice) total, SUM(quatity) totalQuatity " + "from sellerinvoice sli, sellerinvoicedetail slid " + "where sli.id = slid.sellerInvoiceId and " + " createdDate >= :fromDate and createdDate <= :toDate and " + " seller_id = :id and " + " status = 'SHIPPED' " + "group by date " + "order by date asc"); List<Object[]> queryResult = sqlQuery.setDate("fromDate", fromDate).setDate("toDate", toDate) .setInteger("id", user.getId()).list(); for (Object[] row : queryResult) { GenericChartUnit<Object> unit = new GenericChartUnit<>(((Date) row[0]).getTime(), new ArrayList<Object>());//thm i tng hin th unit.getFigures().add(row[1]);//thm s liu unit.getFigures().add(row[2]); statisticData.getData().add(unit); } return statisticData; }
From source file:com.bookselling.dao.SellingPostDaoImpl.java
@Override public StatisticData<GenericChartUnit<Object>> countPost(Date fromDate, Date toDate) { StatisticData<GenericChartUnit<Object>> statisticData = new StatisticData<>(new HashSet<>()); statisticData.setLabel("Lt ng bi"); SQLQuery sqlQuery = getSession().createSQLQuery("select DATE(createdDate) date, count(*) " + "from post " + "where createdDate between :fromDate and :toDate " + "group by date " + "order by date "); sqlQuery.setDate("fromDate", fromDate).setDate("toDate", toDate); List<Object[]> rows = sqlQuery.list(); for (Object[] row : rows) { GenericChartUnit<Object> unit = new GenericChartUnit<>(((Date) row[0]).getTime(), new ArrayList<Object>()); unit.getFigures().add(row[1]);/* ww w . j av a 2 s . c o m*/ statisticData.getData().add(unit); } return statisticData; }
From source file:com.bookselling.dao.SystemInvoiceDaoImpl.java
@Override public StatisticData<GenericChartUnit<Object>> countTotalProfit(Date fromDate, Date toDate) { StatisticData<GenericChartUnit<Object>> statisticData = new StatisticData<>(new HashSet<>()); statisticData.setLabel("Doanh thu ng bi"); SQLQuery sqlQuery = getSession().createSQLQuery("select DATE(createdDate) date, SUM(fee) " + "from systeminvoice " + "where createdDate >= DATE(:fromDate) and createdDate <= DATE(:toDate) " + "group by date " + "order by date asc "); sqlQuery.setDate("fromDate", fromDate).setDate("toDate", toDate); List<Object[]> rows = sqlQuery.list(); for (Object[] row : rows) { GenericChartUnit<Object> unit = new GenericChartUnit<>(((Date) row[0]).getTime(), new ArrayList<Object>()); unit.getFigures().add(row[1]);/* w ww. j a v a 2s. c o m*/ statisticData.getData().add(unit); } return statisticData; }
From source file:com.bookselling.dao.UserDaoImpl.java
@Override public StatisticData<GenericChartUnit<Object>> registrationStat(Date fromDate, Date toDate) { StatisticData<GenericChartUnit<Object>> statisticData = new StatisticData<>(new HashSet<>()); statisticData.setLabel("Lt ng k"); SQLQuery sqlQuery = getSession().createSQLQuery("select DATE(createdDate) date, count(*) " + "from account acc " + "where createdDate >= :fromDate and createdDate <= :toDate " + "group by date " + "order by date asc "); sqlQuery.setDate("fromDate", fromDate).setDate("toDate", toDate); List<Object[]> rows = sqlQuery.list(); for (Object[] row : rows) { GenericChartUnit<Object> unit = new GenericChartUnit<>(((Date) row[0]).getTime(), new ArrayList<Object>()); unit.getFigures().add(row[1]);//from w w w .jav a 2s . c o m statisticData.getData().add(unit); } return statisticData; }
From source file:com.exilant.eGov.src.domain.BankReconciliationSummary.java
License:Open Source License
public String getUnReconciledDrCr(Integer bankAccId, Date fromDate, Date toDate) throws Exception { String instrumentsForTotal = "case when iv.voucherHeaderId is null then 0 else ih.instrumentAmount end)"; String instrumentsForBrsEntryTotal = "(case when br.voucherHeaderId is null then ih.instrumentAmount else 0 end)"; String totalQuery = "SELECT (sum(case when ih.ispaycheque='1' then ih.instrumentAmount else 0 end)) AS \"brs_creditTotal\", " + " (sum( case when ih.ispaycheque= '0' then ih.instrumentAmount else 0 end) ) AS \"brs_debitTotal\" " + " FROM egf_instrumentheader ih WHERE ih.bankAccountId =:bankAccountId " + " AND IH.INSTRUMENTDATE >= :fromDate" + " AND IH.INSTRUMENTDATE <= :toDate" + " AND ( (ih.ispaycheque='0' and ih.id_status=(select id from egw_status where moduletype='Instrument' and description='Deposited'))or (ih.ispaycheque='1' and ih.id_status=(select id from egw_status where moduletype='Instrument' and description='New'))) " + " and ih.instrumentnumber is not null"; //see u might need to exclude brs entries here String otherTotalQuery = " SELECT (sum(case when ih.ispaycheque='1' then ih.instrumentAmount else 0 end )) AS \"brs_creditTotalOthers\", " + " (sum(case when ih.ispaycheque='0' then ih.instrumentAmount else 0 end ) ) AS \"brs_debitTotalOthers\" " + " FROM egf_instrumentheader ih WHERE ih.bankAccountId =:bankAccountId" + " AND IH.transactiondate >= :fromDate" + " AND IH.transactiondate <= :toDate " + " AND ( (ih.ispaycheque='0' and ih.id_status=(select id from egw_status where moduletype='Instrument' and description='Deposited'))or (ih.ispaycheque='1' and ih.id_status=(select id from egw_status where moduletype='Instrument' and description='New'))) " + " AND ih.transactionnumber is not null"; String brsEntryQuery = "select (sum(case when be.type='Receipt' then (case when be.voucherheaderid is null then be.txnamount else 0 end) else 0 end))AS \"brs_creditTotalBrsEntry\"," + "(sum(case when be.type='Payment' then (case when be.voucherheaderid is null then be.txnamount else 0 end) else 0 end))AS \"brs_debitTotalBrsEntry\"" + "FROM bankentries be WHERE be.bankAccountId = :bankAccountId and be.voucherheaderid is null AND be.txndate >=:fromDate AND be.txndate <= :toDate"; if (LOGGER.isInfoEnabled()) LOGGER.info(" query for total : " + totalQuery); if (LOGGER.isInfoEnabled()) LOGGER.info(" query for other than cheque/DD: " + otherTotalQuery); if (LOGGER.isInfoEnabled()) LOGGER.info(" query for bankEntries: " + brsEntryQuery); String unReconciledDrCr = ""; String creditTotal = null;//from w w w .java 2s .com String creditOthertotal = null; String debitTotal = null; String debitOtherTotal = null; String creditTotalBrsEntry = null; String debitTotalBrsEntry = null; try { SQLQuery totalSQLQuery = persistenceService.getSession().createSQLQuery(totalQuery); totalSQLQuery.setInteger("bankAccountId", bankAccId); totalSQLQuery.setDate("fromDate", fromDate); totalSQLQuery.setDate("toDate", toDate); List list = totalSQLQuery.list(); if (list.size() > 0) { if (LOGGER.isDebugEnabled()) LOGGER.debug(list.get(0)); Object[] my = (Object[]) list.get(0); creditTotal = my[0] != null ? my[0].toString() : null; debitTotal = my[1] != null ? my[1].toString() : null; } totalSQLQuery = persistenceService.getSession().createSQLQuery(otherTotalQuery); totalSQLQuery.setInteger("bankAccountId", bankAccId); totalSQLQuery.setDate("fromDate", fromDate); totalSQLQuery.setDate("toDate", toDate); list = totalSQLQuery.list(); if (list.size() > 0) { if (LOGGER.isDebugEnabled()) LOGGER.debug(list.get(0)); Object[] my = (Object[]) list.get(0); creditOthertotal = my[0] != null ? my[0].toString() : null; debitOtherTotal = my[1] != null ? my[1].toString() : null; } totalSQLQuery = persistenceService.getSession().createSQLQuery(brsEntryQuery); totalSQLQuery.setInteger("bankAccountId", bankAccId); totalSQLQuery.setDate("fromDate", fromDate); totalSQLQuery.setDate("toDate", toDate); list = totalSQLQuery.list(); if (list.size() > 0) { if (LOGGER.isDebugEnabled()) LOGGER.debug(list.get(0)); Object[] my = (Object[]) list.get(0); creditTotalBrsEntry = my[0] != null ? my[0].toString() : null; debitTotalBrsEntry = my[1] != null ? my[1].toString() : null; } unReconciledDrCr = (creditTotal != null ? creditTotal : "0") + "/" + (creditOthertotal != null ? creditOthertotal : "0") + "/" + (debitTotal != null ? debitTotal : "0") + "/" + (debitOtherTotal != null ? debitOtherTotal : "0") + "" + "/" + (creditTotalBrsEntry != null ? creditTotalBrsEntry : "0") + "/" + (debitTotalBrsEntry != null ? debitTotalBrsEntry : "0") + ""; } catch (Exception e) { LOGGER.error("Exp in getUnReconciledDrCr" + e.getMessage()); throw e; } return unReconciledDrCr; }
From source file:com.neu.web.phmis.dao.DoctorDao.java
public void createRequest(SurgeryRequestBean surgeryRequestBean) { try {//from w ww .j a v a 2s .c om surgeryBean = surgeryRequestBean.getSurgeryBean(); List<ProductBean> requestedProducts = surgeryRequestBean.getRequestedProducts(); session = HibernateUtil.getSessionFactory().openSession(); session.beginTransaction(); SQLQuery query = session.createSQLQuery( "CALL createSurgeryRequest(:userId, :surgeryType, :patientName, :requestNo, :requestTypeId, :requestStatusId, :dateFrom, :dateTo, :instru, :staff)"); query.setInteger("userId", surgeryBean.getDoctor().getId()); query.setString("surgeryType", surgeryBean.getType()); query.setString("patientName", surgeryBean.getPatientName()); query.setString("requestNo", surgeryRequestBean.getNumber()); query.setInteger("requestTypeId", surgeryRequestBean.getTypeId()); query.setInteger("requestStatusId", surgeryRequestBean.getStatusId()); query.setDate("dateFrom", surgeryRequestBean.getDateFrom()); query.setDate("dateTo", surgeryRequestBean.getDateTo()); query.setString("instru", surgeryRequestBean.getSpecialInstructions()); query.setInteger("staff", surgeryRequestBean.getStaffCount()); List<Integer> list = query.list(); surgeryRequestBean.setId(list.get(0)); for (ProductBean p : requestedProducts) { query = session.createSQLQuery("INSERT INTO productrequested VALUES (:requestId, :productId)"); query.setInteger("requestId", surgeryRequestBean.getId()); query.setInteger("productId", p.getId()); query.executeUpdate(); } session.getTransaction().commit(); } catch (HibernateException e) { session.getTransaction().rollback(); e.printStackTrace(); } catch (Exception e) { e.printStackTrace(); } finally { session.close(); } }