Example usage for org.hibernate Query setDate

List of usage examples for org.hibernate Query setDate

Introduction

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

Prototype

@Deprecated
@SuppressWarnings("unchecked")
default Query<R> setDate(String name, Date val) 

Source Link

Document

Bind the val (time is truncated) of a given Date object to a named query parameter.

Usage

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();
    }
}