List of usage examples for org.hibernate Query setDate
@Deprecated @SuppressWarnings("unchecked") default Query<R> setDate(String name, Date val)
From source file:com.connexience.server.model.datasets.queries.temporal.enactors.LatestByRelativeTimeQueryEnactor.java
License:Open Source License
@Override public JSONContainer performQuery() throws ConnexienceException { Session session = null;// www . j a v a 2 s . c om try { Calendar c = Calendar.getInstance(); LatestByRelativeTimeQuery q = (LatestByRelativeTimeQuery) query; int units = -q.getNumberOfUnits(); // negative units to subtract time if (q.getTimeUnit().equals(DAY)) { c.add(Calendar.DAY_OF_YEAR, units); } else if (q.getTimeUnit().equals(HOUR)) { c.add(Calendar.HOUR_OF_DAY, units); } else if (q.getTimeUnit().equals(MINUTE)) { c.add(Calendar.MINUTE, units); } else if (q.getTimeUnit().equals(SECOND)) { c.add(Calendar.SECOND, units); } else if (q.getTimeUnit().equals(WEEK)) { c.add(Calendar.WEEK_OF_YEAR, units); } else if (q.getTimeUnit().equals(YEAR)) { c.add(Calendar.YEAR, units); } else { c.add(Calendar.DAY_OF_YEAR, units); } session = sessionProvider.getSession(); Query hqlQuery = session.createQuery( "from JsonDataRow as obj where obj.itemId=:itemid and obj.collectionTime>=:collectiontime order by obj.id asc"); hqlQuery.setLong("itemid", item.getId()); hqlQuery.setDate("collectiontime", c.getTime()); List rows = hqlQuery.list(); if (q.getKeyArray() != null && q.getKeyArray().length > 0) { return DatasetsUtils.createResultFromList(rows, q.getKeyArray()); } else { return DatasetsUtils.createResultFromList(rows, null); } } catch (Exception e) { throw new ConnexienceException("Error performing query: " + e.getMessage(), e); } finally { sessionProvider.closeSession(session); } }
From source file:com.dz.module.charge.ChargeService.java
public List<BankRecord> exportBankFile(Date time, String dept) { List<BankRecord> records = new ArrayList<>(); Session session = null;//from w ww . j a v a2s . c om try { session = HibernateSessionFactory.getSession(); String hql = "select new com.dz.module.charge.BankRecord(" + "d.idNum as idNum," + "d.name as driverName," + "c.carframeNum as carframeNum," + "c.carNum as licenseNum," + "sum(case when year(p.time)>year(:date) then 0.0 " + " when year(p.time)=year(:date) and month(p.time)>month(:date) then 0.0 " + " when year(cl.current)>year(p.time) then 0.0 " + " when year(cl.current)=year(p.time) and month(cl.current)>month(p.time) then 0.0 " + " when p.feeType like 'plan%add%' then -p.fee " + " when p.feeType like 'plan%sub%' then p.fee " + " when p.feeType like 'plan%' then -p.fee " + " when p.feeType like '%add%' then p.fee " + " else -p.fee " + "end) as derserve" + "," + "avg(case when year(cl.current)<year(:date) then c.account" + " when year(cl.current)=year(:date) and month(cl.current)<=month(:date) then c.account " + " when p.feeType='last_month_left' then p.fee" + " else 0.0 " + "end) as left " + ") " + "from ChargePlan p,Contract c,ClearTime cl,Driver d " + "where cl.department=c.branchFirm " + "and p.contractId=c.id " + "and c.state in (0,-1,1,4) " + "and c.branchFirm like :dept " + "and c.idNum=d.idNum " + "and p.isClear != true " + "and ( " + " (c.abandonedFinalTime is null) " + " or (YEAR(c.abandonedFinalTime )*12+MONTH(c.abandonedFinalTime )+(case when DAY(c.abandonedFinalTime )>26 then 1 else 0 end) >= (YEAR(:date)*12+MONTH(:date)))" + ") " + "group by c.id " + "order by c.branchFirm,c.carNum"; Query query = session.createQuery(hql); if (dept.equals("")) { query.setString("dept", "%"); } else { query.setString("dept", "%" + dept + "%"); } query.setDate("date", time); records = query.list(); } catch (HibernateException ex) { ex.printStackTrace(); } finally { HibernateSessionFactory.closeSession(); } return records; }
From source file:com.dz.module.charge.ChargeService.java
/** * ???,/* w ww .j ava 2s . com*/ * @param dept * @return true if success */ public boolean finalClearAll(String dept) { //????? if (!("".equals(dept) || "".equals(dept) || "".equals(dept))) return false; Date totalTime = clearTimeDao.getCurrent("total"); Date deptTime = clearTimeDao.getCurrent(dept); if (!DateUtil.isYearAndMonth(totalTime, deptTime)) return false; List<Contract> contractList = contractDao.contractSearchAllAvilable(deptTime, dept, null, null); //TODOI don't know if it is necessary to filter fileterContract(contractList); // for(Contract contract:contractList){ // Driver d = new Driver(); // d.setIdNum(contract.getIdNum()); // Driver driver = driverDao.selectById(d.getIdNum()); // //TODO wait to add department // if(driver != null && dept.equals(contract.getBranchFirm())){ // clear(contract.getId(),deptTime); // } // clear(contract.getId(),deptTime); // } List<CheckChargeTable> tables = this.getAllCheckChargeTable(deptTime, dept, null, 4); Session session = HibernateSessionFactory.getSession(); Transaction tx = null; try { tx = session.beginTransaction(); for (Contract contract : contractList) { clear(contract.getId(), deptTime); } String hql = "select c1,c2 from Contract c1,Contract c2 where c1.contractFrom=c2.id and (c2.abandonedFinalTime is null or (YEAR(c2.abandonedFinalTime)*12+MONTH(c2.abandonedFinalTime)+(case when DAY(c2.abandonedFinalTime)>26 then 1 else 0 end) >= (YEAR(:currentClearTime)*12+MONTH(:currentClearTime)) ))"; Query query = session.createQuery(hql); query.setDate("currentClearTime", deptTime); List<Object[]> list = query.list(); for (Object[] oarr : list) { Contract c = (Contract) oarr[0]; Contract oc = (Contract) oarr[1]; if (c.getAccount() == null) c.setAccount(BigDecimal.ZERO); if (oc.getAccount() != null) c.setAccount(c.getAccount().add(oc.getAccount())); oc.setAccount(BigDecimal.ZERO); session.saveOrUpdate(c); session.saveOrUpdate(oc); } //?? boolean res = clearTimeDao.plusAMonth(dept, session); Date time1 = clearTimeDao.getCurrent("", session); Date time2 = clearTimeDao.getCurrent("", session); Date time3 = clearTimeDao.getCurrent("", session); if (!DateUtil.isYearAndMonth(totalTime, time1) && !DateUtil.isYearAndMonth(totalTime, time2) && !DateUtil.isYearAndMonth(totalTime, time3)) { clearTimeDao.plusAMonth("total", session); } else { System.out.println(dept); System.out.println(res); System.out.println(time1); System.out.println(time2); System.out.println(time3); System.out.println(totalTime); } for (CheckChargeTable cct : tables) { session.save(cct); } tx.commit(); } catch (HibernateException ex) { ex.printStackTrace(); if (tx != null) tx.rollback(); return false; } finally { HibernateSessionFactory.closeSession(); } return true; }
From source file:com.dz.module.charge.ChargeService.java
/** * ??// w w w. j a v a 2 s . c o m * @param contractId none * @param clearTime none * @return none */ private boolean clear(int contractId, Date clearTime) throws HibernateException { Session session = HibernateSessionFactory.getSession(); Contract c = (Contract) session.get(Contract.class, contractId); BigDecimal account = c.getAccount(); //? ChargePlan lastMonthRecord = new ChargePlan(); lastMonthRecord.setFee(account); lastMonthRecord.setIsClear(true); lastMonthRecord.setContractId(contractId); lastMonthRecord.setTime(clearTime); lastMonthRecord.setFeeType("last_month_left"); session.saveOrUpdate(lastMonthRecord); Query query = session.createQuery( "from ChargePlan where contractId = :contractId and isClear=false and year(time)=year(:date) and month(time)=month(:date)"); query.setInteger("contractId", contractId); query.setDate("date", clearTime); // List<ChargePlan> plans = chargeDao.getUnclears(contractId,clearTime); List<ChargePlan> plans = query.list(); for (ChargePlan plan : plans) { // System.out.println(plan.getFeeType() + " test "+plan.getFee()); String feeType = plan.getFeeType(); if (feeType.startsWith("add") || feeType.startsWith("plan_sub")) { account = account.add(plan.getFee()); } else if (feeType.startsWith("sub") || feeType.startsWith("plan_add") || feeType.startsWith("plan_base")) { account = account.subtract(plan.getFee()); } // chargeDao.cleared(plan); plan.setIsClear(true); session.saveOrUpdate(plan); } // contractDao.updateAccount(contractId,account); c.setAccount(account); session.saveOrUpdate(c); return true; }
From source file:com.dz.module.charge.ChargeService.java
public List<CheckChargeTable> getAllCheckChargeTable(Date date, String dept, String licenseNum, int status/**0,1,2,3,4 -- ,,,,*/ ) {/*from ww w. j av a2 s . co m*/ if (date == null) date = new Date(); Calendar clear_time = Calendar.getInstance(); clear_time.setTime(getCurrentTime("".equals(dept) ? "total" : dept)); clear_time.add(Calendar.DATE, -1); Session session = HibernateSessionFactory.getSession(); try { if (date.before(clear_time.getTime())) { String ql = "from CheckChargeTable where YEAR(time)=:year and MONTH(time)=:month "; if (dept != null && !"".equals(dept)) { ql += "and dept = :dept "; } if (!StringUtils.isEmpty(licenseNum)) { ql += "and carNumber like :carNum "; } String ql2; switch (status) { case 1:// ThisMonthTotalOwe>0 ql2 = "and thisMonthTotalOwe >0.0 "; break; case 0:// ql2 = "and thisMonthTotalOwe<=0.0 "; break; case 2:// ql2 = "and bank <= 0.0 "; break; case 3:// ql2 = "and bank >0.0 "; break; default: ql2 = ""; } Query qy = session.createQuery(ql + ql2); if (dept != null && !"".equals(dept)) { qy.setString("dept", dept); } if (!StringUtils.isEmpty(licenseNum)) { qy.setString("carNum", "%" + licenseNum + "%"); } qy.setInteger("year", date.getYear() + 1900); qy.setInteger("month", date.getMonth() + 1); List<CheckChargeTable> lst = qy.list(); return lst; } String hql = // "select c.id " //+ "from Contract c " //+ "where c.state in (0,-1,1,4) " "and c.state in (0,-1,1,4) " + "and (c.abandonedFinalTime is null or (YEAR(c.abandonedFinalTime)*12+MONTH(c.abandonedFinalTime)+(case when DAY(c.abandonedFinalTime)>26 then 1 else 0 end) " + ">= (YEAR(:currentClearTime)*12+MONTH(:currentClearTime)))) "; if (dept != null && !"".equals(dept)) { hql += "and c.branchFirm = :dept "; } if (!StringUtils.isEmpty(licenseNum)) { hql += "and c.carNum like :carNum "; } String hql2; switch (status) { case 1:// ThisMonthTotalOwe>0 hql2 = "having avg(case when year(cl.current)<year(:currentClearTime) then c.account" + " when year(cl.current)=year(:currentClearTime) and month(cl.current)<=month(:currentClearTime) then c.account " + " when p.feeType='last_month_left' then p.fee" + " else 0.0 " + "end)- " + "sum(case " + "when p.feeType like '%bank%' then 0.0 " + "when p.feeType like '%cash%' then 0.0 " + "when p.feeType like '%oilAdd%' then 0.0 " + "when p.feeType like '%insurance%' then 0.0 " + "when p.feeType like '%other%' then 0.0 " + "when p.feeType like '%add%' then p.fee " + "else (-p.fee) end)>0.0 "; break; case 0:// hql2 = "having avg(case when year(cl.current)<year(:currentClearTime) then c.account" + " when year(cl.current)=year(:currentClearTime) and month(cl.current)<=month(:currentClearTime) then c.account " + " when p.feeType='last_month_left' then p.fee" + " else 0.0 " + "end)- " + "sum(case " + "when p.feeType like '%bank%' then 0.0 " + "when p.feeType like '%cash%' then 0.0 " + "when p.feeType like '%oilAdd%' then 0.0 " + "when p.feeType like '%insurance%' then 0.0 " + "when p.feeType like '%other%' then 0.0 " + "when p.feeType like '%add%' then p.fee " + "else (-p.fee) end)<=0.0 "; break; case 2:// hql2 = "having " + "sum(case " + "when p.feeType not like '%bank%' then 0.0 " + "when p.feeType like '%add%' then p.fee " + "else (-p.fee) end) <=0.0 "; break; case 3:// hql2 = "having " + "sum(case " + "when p.feeType not like '%bank%' then 0.0 " + "when p.feeType like '%add%' then p.fee " + "else (-p.fee) end) >0.0 "; break; default: hql2 = ""; } String hql_out = "select new com.dz.module.charge.CheckChargeTable(" + "p.contractId as contractId,p.time as time," + "sum(case " + "when p.feeType not like '%bank%' then 0.0 " + "when p.feeType like '%plan%' then 0.0 " + "when p.feeType like '%add%' then p.fee " + "else (-p.fee) end) as bank" + "," + "sum(case " + "when p.feeType not like '%cash%' then 0.0 " + "when p.feeType like '%plan%' then 0.0 " + "when p.feeType like '%add%' then p.fee " + "else (-p.fee) end) as cash" + "," + "sum(case " + "when p.feeType not like '%insurance%' then 0.0 " + "when p.feeType like '%plan%' then 0.0 " + "when p.feeType like '%add%' then p.fee " + "else (-p.fee) end) as insurance" + "," + "sum(case " + "when p.feeType not like '%oil%' then 0.0 " + "when p.feeType like '%plan%' then 0.0 " + "when p.feeType like '%add%' then p.fee " + "else (-p.fee) end) as oilAdd" + "," + "sum(case " + "when p.feeType not like '%other%' then 0.0 " + "when p.feeType like '%plan%' then 0.0 " + "when p.feeType like '%add%' then p.fee " + "else (-p.fee) end) as other" + "," + "sum(case " + "when p.feeType not like '%plan%' then 0.0 " + "when p.feeType like '%sub%' then -p.fee " + "else (p.fee) end) as planAll " + "," + "c.carNum as carNumber," + "c.branchFirm as dept," + "d.name as driverName, " + "avg(case when year(cl.current)<year(:currentClearTime) then c.account" + " when year(cl.current)=year(:currentClearTime) and month(cl.current)<=month(:currentClearTime) then c.account " + " when p.feeType='last_month_left' then p.fee" + " else 0.0 " + "end) as lastMonthOwe " + ") from ChargePlan " //+ "where contractId in (" + hql+ " ) " + "p ,Contract c,Driver d,ClearTime cl " + "where p.contractId=c.id and d.idNum=c.idNum and cl.department=c.branchFirm " + "and p.isClear != true " + hql + "and p.time is not null and year(p.time)=year(:currentClearTime) and month(p.time)=month(:currentClearTime) " + "group by c.id " + hql2 + "order by c.branchFirm,c.carNum"; // Query query = session.createQuery(hql_out).setResultTransformer(Transformers.aliasToBean(CheckChargeTable.class)); Query query = session.createQuery(hql_out); if (dept != null && !"".equals(dept)) { query.setString("dept", dept); } if (!StringUtils.isEmpty(licenseNum)) { query.setString("carNum", "%" + licenseNum + "%"); } query.setDate("currentClearTime", date); return query.list(); } catch (HibernateException ex) { ex.printStackTrace(); return new ArrayList<>(); } finally { HibernateSessionFactory.closeSession(); } }
From source file:com.dz.module.contract.ContractDaoImpl.java
@Override public int selectAllByStatesCount(Contract contract, Vehicle vehicle, Driver driver, Date beginDate, Date endDate, Short[] states) { Session session = null;// w w w. ja v a 2 s . c o m try { session = HibernateSessionFactory.getSession(); String hql = "select count(*) from Contract c where c.state in (:states)"; if (beginDate != null) { hql += " and c.contractBeginDate >= :beginDate"; } if (endDate != null) { hql += " and c.contractBeginDate <= :endDate"; } if (contract != null) { if (!StringUtils.isEmpty(contract.getIdNum())) { hql += " and c.idNum like :idNum"; } if (!StringUtils.isEmpty(contract.getCarNum())) { hql += " and c.carNum like :carNum"; } } Query query = session.createQuery(hql); if (beginDate != null) { query.setDate("beginDate", beginDate); } if (endDate != null) { query.setDate("endDate", endDate); } if (contract != null) { if (!StringUtils.isEmpty(contract.getIdNum())) { query.setString("idNum", "%" + contract.getIdNum() + "%"); } if (!StringUtils.isEmpty(contract.getCarNum())) { query.setString("carNum", "%" + contract.getCarNum() + "%"); } } query.setParameterList("states", states); return Integer.parseInt(query.uniqueResult().toString()); } catch (HibernateException e) { throw e; } finally { HibernateSessionFactory.closeSession(); } }
From source file:com.dz.module.contract.ContractDaoImpl.java
@SuppressWarnings("unchecked") @Override//from ww w . j a v a 2s . com public List<Contract> selectAllByStates(Page page, Contract contract, Vehicle vehicle, Driver driver, Date beginDate, Date endDate, Short[] states) { Session session = null; try { session = HibernateSessionFactory.getSession(); String hql = "from Contract c where c.state in (:states)"; if (beginDate != null) { hql += " and c.contractBeginDate >= :beginDate"; } if (endDate != null) { hql += " and c.contractBeginDate <= :endDate"; } if (contract != null) { if (!StringUtils.isEmpty(contract.getIdNum())) { hql += " and c.idNum like :idNum"; } if (!StringUtils.isEmpty(contract.getCarNum())) { hql += " and c.carNum like :carNum"; } } Query query = session.createQuery(hql); if (beginDate != null) { query.setDate("beginDate", beginDate); } if (endDate != null) { query.setDate("endDate", endDate); } if (contract != null) { if (!StringUtils.isEmpty(contract.getIdNum())) { query.setString("idNum", "%" + contract.getIdNum() + "%"); } if (!StringUtils.isEmpty(contract.getCarNum())) { query.setString("carNum", "%" + contract.getCarNum() + "%"); } } query.setParameterList("states", states); if (page != null) { query.setMaxResults(page.getEveryPage()); query.setFirstResult(page.getBeginIndex()); } return query.list(); } catch (HibernateException e) { throw e; } finally { HibernateSessionFactory.closeSession(); } }
From source file:com.dz.module.contract.ContractDaoImpl.java
@Override public int contractSearchAllAvaliableCount(Date time, String dept, String licenseNum) { Session session = null;//from ww w .jav a 2s.com Transaction tx = null; try { session = HibernateSessionFactory.getSession(); tx = (Transaction) session.beginTransaction(); Query query = null; String hql = "select count(*) from Contract where state in (0,-1,1,4) and (abandonedFinalTime is null or (YEAR(abandonedFinalTime)*12+MONTH(abandonedFinalTime)+(case when DAY(abandonedFinalTime)>26 then 1 else 0 end) >= (YEAR(:currentClearTime)*12+MONTH(:currentClearTime))) )"; if (dept != null && !"".equals(dept)) { hql += "and branchFirm = :dept "; } if (!StringUtils.isEmpty(licenseNum)) { hql += "and carNum like :carNum "; } query = session.createQuery(hql); if (dept != null && !"".equals(dept)) { query.setString("dept", dept); } if (!StringUtils.isEmpty(licenseNum)) { query.setString("carNum", licenseNum); } query.setDate("currentClearTime", time); long count = (long) query.uniqueResult(); tx.commit(); return (int) count; } catch (HibernateException e) { e.printStackTrace(); if (tx != null) { tx.rollback(); } return 0; } finally { HibernateSessionFactory.closeSession(); } }
From source file:com.dz.module.contract.ContractDaoImpl.java
@Override public List<Contract> contractSearchAllAvilable(Date time, String dept, String licenseNum, Page page) { List<Contract> l = new ArrayList<Contract>(); Session session = null;/*w w w . j av a 2 s .co m*/ Transaction tx = null; try { session = HibernateSessionFactory.getSession(); tx = (Transaction) session.beginTransaction(); Query query = null; String hql = "from Contract where state in (0,-1,1,4) and (abandonedFinalTime is null or (YEAR(abandonedFinalTime)*12+MONTH(abandonedFinalTime)+(case when DAY(abandonedFinalTime)>26 then 1 else 0 end) >= (YEAR(:currentClearTime)*12+MONTH(:currentClearTime)) ))"; if (dept != null && !"".equals(dept)) { hql += "and branchFirm = :dept "; } if (!StringUtils.isEmpty(licenseNum)) { hql += "and carNum like :carNum "; } query = session.createQuery(hql); if (dept != null && !"".equals(dept)) { query.setString("dept", dept); } if (!StringUtils.isEmpty(licenseNum)) { query.setString("carNum", licenseNum); } query.setDate("currentClearTime", time); l = query.list(); tx.commit(); return l; } catch (HibernateException e) { e.printStackTrace(); if (tx != null) { tx.rollback(); } return l; } finally { HibernateSessionFactory.closeSession(); } }
From source file:com.dz.module.driver.complain.ComplainDaoImpl.java
@SuppressWarnings("unchecked") @Override/*from w ww . j a va 2 s.c om*/ public List<Complain> selectAll(Page page, Date beginDate, Date endDate) throws HibernateException { Session session = null; try { session = HibernateSessionFactory.getSession(); String hql = "from Complain c where 1=1"; if (beginDate != null) { hql += " and c.complainTime >= :beginDate"; } if (endDate != null) { hql += " and c.complainTime <= :endDate"; } Query query = session.createQuery(hql); if (beginDate != null) { query.setDate("beginDate", beginDate); } if (endDate != null) { query.setDate("endDate", endDate); } query.setMaxResults(page.getEveryPage()); query.setFirstResult(page.getBeginIndex()); return query.list(); } catch (HibernateException e) { throw e; } finally { HibernateSessionFactory.closeSession(); } }