Example usage for org.hibernate Query setString

List of usage examples for org.hibernate Query setString

  1. HOME
  2. Java
  3. org.hibernate
  4. org.hibernate.*
  5. Query
  6. setString

Introduction

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

Prototype

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

Source Link

Document

Bind a named String-valued parameter.

Usage

From source file:com.duroty.application.mail.manager.MailManager.java

License:Open Source License

/**
 * DOCUMENT ME!/* w  w w . j a  va  2 s  . com*/
 *
 * @param hsession DOCUMENT ME!
 * @param repositoryName DOCUMENT ME!
 * @param folderName DOCUMENT ME!
 *
 * @return DOCUMENT ME!
 *
 * @throws MailException DOCUMENT ME!
 */
public int getCountMessages(Session hsession, String repositoryName, String folderName) throws MailException {
    try {
        folderName = parseFolder(folderName);

        Query query = null;

        if (folderName.equals(this.folderAll) || folderName.equals(this.folderHidden)) {
            query = hsession.getNamedQuery("count-all-total-messages");
            query.setString("folderSpam", this.folderSpam);
            query.setString("folderTrash", this.folderTrash);
            query.setString("folderDelete", FOLDER_DELETE);
            query.setString("folderChat", this.folderChat);
        } else {
            query = hsession.getNamedQuery("count-messages-by-folder");
            query.setString("folder", folderName);
        }

        query.setInteger("user", getUser(hsession, repositoryName).getUseIdint());

        return ((Integer) query.uniqueResult()).intValue();
    } catch (Exception e) {
        throw new MailException(e);
    } finally {
        GeneralOperations.closeHibernateSession(hsession);
    }
}

From source file:com.duroty.application.mail.manager.MailManager.java

License:Open Source License

/**
 * DOCUMENT ME!//  w w  w.  ja  v  a 2s. c o  m
 *
 * @param hsession DOCUMENT ME!
 * @param repositoryName DOCUMENT ME!
 * @param label DOCUMENT ME!
 *
 * @return DOCUMENT ME!
 *
 * @throws MailException DOCUMENT ME!
 */
public int getCountMessages(Session hsession, String repositoryName, Label label) throws MailException {
    try {
        Query query = hsession.getNamedQuery("count-messages-by-label");
        query.setInteger("label", label.getLabIdint());
        query.setInteger("user", getUser(hsession, repositoryName).getUseIdint());
        query.setString("folderSpam", this.folderSpam);
        query.setString("folderTrash", this.folderTrash);
        query.setString("folderDelete", FOLDER_DELETE);

        return ((Integer) query.uniqueResult()).intValue();
    } catch (Exception e) {
        throw new MailException(e);
    } finally {
        GeneralOperations.closeHibernateSession(hsession);
    }
}

From source file:com.duroty.application.mail.manager.MailManager.java

License:Open Source License

/**
 * DOCUMENT ME!//  w ww .  j a v a 2s  .co  m
 *
 * @param hsession DOCUMENT ME!
 * @param repositoryName DOCUMENT ME!
 *
 * @return DOCUMENT ME!
 *
 * @throws MailException DOCUMENT ME!
 */
public Counters getInfoCounters(Session hsession, String repositoryName) throws MailException {
    Counters counters = new Counters();

    try {
        Users user = getUser(hsession, repositoryName);

        Query query = hsession.getNamedQuery("count-new-messages-by-folder");
        query.setString("folder", this.folderInbox);
        query.setInteger("user", user.getUseIdint());
        counters.setInbox(((Integer) query.uniqueResult()).intValue());

        query = hsession.getNamedQuery("count-new-messages-by-folder");
        query.setString("folder", this.folderSpam);
        query.setInteger("user", user.getUseIdint());
        counters.setSpam(((Integer) query.uniqueResult()).intValue());

        query = hsession.getNamedQuery("group-count-new-messages-by-label");
        query.setInteger("user", user.getUseIdint());

        ScrollableResults scroll = query.scroll();

        while (scroll.next()) {
            Integer idint = (Integer) scroll.get(0);
            Integer count = (Integer) scroll.get(1);

            counters.addLabel(idint.intValue(), count.intValue());
        }

        counters.setQuota(getQuotaLayer(hsession, user));
    } catch (Exception ex) {
        return null;
    } finally {
        GeneralOperations.closeHibernateSession(hsession);
    }

    return counters;
}

From source file:com.duroty.application.mail.manager.PreferencesManager.java

License:Open Source License

/**
 * DOCUMENT ME!//from ww  w.j  a  v a2  s.c  o  m
 *
 * @param hsession DOCUMENT ME!
 * @param repositoryName DOCUMENT ME!
 * @param idint DOCUMENT ME!
 *
 * @throws Exception DOCUMENT ME!
 */
public void deleteFilter(Session hsession, String repositoryName, Integer idint) throws Exception {
    try {
        Query query = hsession.getNamedQuery("delete-filter-by-idint");
        query.setInteger("idint", idint);
        query.setString("username", repositoryName);

        query.executeUpdate();

        hsession.flush();
    } finally {
        GeneralOperations.closeHibernateSession(hsession);
    }
}

From source file:com.duroty.task.IMAPServiceTask.java

License:Open Source License

/**
 * DOCUMENT ME!//from   w w w  .  j av a2  s.c  om
 */
private void flush() {
    setInit(true);

    SessionFactory hfactory = null;
    Session hsession = null;
    javax.mail.Session msession = null;
    Store store = null;
    Folder rootFolder = null;
    Folder folder = null;

    try {
        hfactory = (SessionFactory) ctx.lookup(hibernateSessionFactory);
        hsession = hfactory.openSession();
        msession = (javax.mail.Session) ctx.lookup(durotyMailFactory);
        store = msession.getStore("imap");

        String imapHost = msession.getProperty("mail.imap.host");

        Query query = hsession.getNamedQuery("users-mail");
        query.setBoolean("active", true);
        query.setString("role", "mail");

        ScrollableResults scroll = query.scroll();

        while (scroll.next()) {
            Users user = (Users) scroll.get(0);

            String repositoryName = user.getUseUsername();

            store.connect(imapHost, repositoryName, user.getUsePassword());

            rootFolder = store.getDefaultFolder();

            if ((rootFolder.getType() & Folder.HOLDS_FOLDERS) != 0) {
                folder = store.getFolder(this.imapInbox);

                Message[] messages = getMimeMessages(folder, new Flags(Flags.Flag.SEEN), false);
                MailUtilities.setFolderOpenAndReady(folder, Folder.READ_WRITE);

                if ((messages != null) && (messages.length > 0)) {
                    for (int i = 0; i < messages.length; i++) {
                        if (pool.size() >= poolSize) {
                            DLog.log(DLog.WARN, this.getClass(), "PoolSize " + pool.size());

                            break;
                        }

                        MimeMessage mime = null;

                        if (messages[i] instanceof MimeMessage) {
                            mime = new MimeMessage((MimeMessage) messages[i]);

                            String messageName = null;

                            if (folder instanceof IMAPFolder) {
                                messageName = String.valueOf(((IMAPFolder) folder).getUID(messages[i]));
                            } else {
                                messageName = String.valueOf(messages[i].getMessageNumber());
                            }

                            messageName += this.imapInbox;

                            boolean existMessage = existMessageName(hfactory.openSession(), user, messageName);

                            if (existMessage) {
                                messages[i].setFlag(Flags.Flag.SEEN, true);
                            } else {
                                String key = messageName + "--" + repositoryName;

                                if (!poolContains(key)) {
                                    addPool(key);

                                    if (!isSpam(user, mime)) {
                                        Mailet mailet = new Mailet(this, messageName, repositoryName, mime);

                                        Thread thread = new Thread(mailet, key);
                                        thread.start();

                                        //cal controlar si el borrem
                                        messages[i].setFlag(Flags.Flag.SEEN, true);
                                    } else {
                                        messages[i].setFlag(Flags.Flag.SEEN, true);
                                        messages[i].setFlag(Flags.Flag.DELETED, true);
                                    }
                                }
                            }

                            Thread.sleep(100);
                        }
                    }
                }

                MailUtilities.setFolderClose(folder, true);
                folder = null;
                messages = null;

                folder = store.getFolder(this.imapSent);
                messages = getMimeMessages(folder, new Flags(Flags.Flag.SEEN), false);
                MailUtilities.setFolderOpenAndReady(folder, Folder.READ_WRITE);

                if (!folder.exists()) {
                    folder.create(Folder.HOLDS_MESSAGES);
                }

                if (!folder.isSubscribed()) {
                    folder.setSubscribed(true);
                }

                if ((messages != null) && (messages.length > 0)) {
                    for (int i = 0; i < messages.length; i++) {
                        if (pool.size() >= poolSize) {
                            DLog.log(DLog.WARN, this.getClass(), "PoolSize " + pool.size());

                            break;
                        }

                        MimeMessage mime = null;

                        if (messages[i] instanceof MimeMessage) {
                            mime = new MimeMessage((MimeMessage) messages[i]);

                            mime.addHeader("X-DBox", "SENT");

                            String messageName = null;

                            if (folder instanceof IMAPFolder) {
                                messageName = String.valueOf(((IMAPFolder) folder).getUID(messages[i]));
                            } else {
                                messageName = String.valueOf(messages[i].getMessageNumber());
                            }

                            messageName += this.imapSent;

                            boolean existMessage = existMessageName(hfactory.openSession(), user, messageName);

                            if (existMessage) {
                                messages[i].setFlag(Flags.Flag.SEEN, true);
                            } else {
                                String key = messageName + "--" + repositoryName;

                                if (!poolContains(key)) {
                                    addPool(key);

                                    if (!isSpam(user, mime)) {
                                        Mailet mailet = new Mailet(this, messageName, repositoryName, mime);

                                        Thread thread = new Thread(mailet, key);
                                        thread.start();

                                        //cal controlar si el borrem
                                        messages[i].setFlag(Flags.Flag.SEEN, true);
                                    } else {
                                        messages[i].setFlag(Flags.Flag.SEEN, true);
                                        messages[i].setFlag(Flags.Flag.DELETED, true);
                                    }
                                }
                            }

                            Thread.sleep(100);
                        }
                    }
                }

                MailUtilities.setFolderClose(folder, true);
                folder = null;
                messages = null;

                folder = store.getFolder(this.imapDraft);
                messages = getMimeMessages(folder, new Flags(Flags.Flag.SEEN), false);
                MailUtilities.setFolderOpenAndReady(folder, Folder.READ_WRITE);

                if (!folder.exists()) {
                    folder.create(Folder.HOLDS_MESSAGES);
                }

                if (!folder.isSubscribed()) {
                    folder.setSubscribed(true);
                }

                if ((messages != null) && (messages.length > 0)) {
                    for (int i = 0; i < messages.length; i++) {
                        if (pool.size() >= poolSize) {
                            DLog.log(DLog.WARN, this.getClass(), "PoolSize " + pool.size());

                            break;
                        }

                        MimeMessage mime = null;

                        if (messages[i] instanceof MimeMessage) {
                            mime = new MimeMessage((MimeMessage) messages[i]);

                            mime.addHeader("X-DBox", "DRAFT");

                            String messageName = null;

                            if (folder instanceof IMAPFolder) {
                                messageName = String.valueOf(((IMAPFolder) folder).getUID(messages[i]));
                            } else {
                                messageName = String.valueOf(messages[i].getMessageNumber());
                            }

                            messageName += this.imapDraft;

                            boolean existMessage = existMessageName(hfactory.openSession(), user, messageName);

                            if (existMessage) {
                                messages[i].setFlag(Flags.Flag.SEEN, true);
                            } else {
                                String key = messageName + "--" + repositoryName;

                                if (!poolContains(key)) {
                                    addPool(key);

                                    if (!isSpam(user, mime)) {
                                        Mailet mailet = new Mailet(this, messageName, repositoryName, mime);

                                        Thread thread = new Thread(mailet, key);
                                        thread.start();

                                        //cal controlar si el borrem
                                        messages[i].setFlag(Flags.Flag.SEEN, true);
                                    } else {
                                        messages[i].setFlag(Flags.Flag.SEEN, true);
                                        messages[i].setFlag(Flags.Flag.DELETED, true);
                                    }
                                }
                            }

                            Thread.sleep(100);
                        }
                    }
                }

                MailUtilities.setFolderClose(folder, true);
                folder = null;
            }

            MailUtilities.setFolderClose(rootFolder, true);
            MailUtilities.setStoreClose(store);
        }
    } catch (Exception e) {
        System.gc();
        pool.clear();

        StringWriter writer = new StringWriter();
        e.printStackTrace(new PrintWriter(writer));
        DLog.log(DLog.ERROR, this.getClass(), writer.toString());
    } catch (OutOfMemoryError e) {
        System.gc();
        pool.clear();

        StringWriter writer = new StringWriter();
        e.printStackTrace(new PrintWriter(writer));
        DLog.log(DLog.ERROR, this.getClass(), writer.toString());
    } catch (Throwable e) {
        System.gc();
        pool.clear();

        StringWriter writer = new StringWriter();
        e.printStackTrace(new PrintWriter(writer));
        DLog.log(DLog.ERROR, this.getClass(), writer.toString());
    } finally {
        System.gc();

        GeneralOperations.closeMailFolder(folder, false);
        GeneralOperations.closeMailFolder(rootFolder, false);
        GeneralOperations.closeMailStore(store);
        GeneralOperations.closeHibernateSession(hsession);

        setInit(false);
    }
}

From source file:com.duroty.task.POP3ServiceTask.java

License:Open Source License

/**
 * DOCUMENT ME!//from  w w w.j  av a 2  s  . c  o  m
 */
private void flush() {
    setInit(true);

    SessionFactory hfactory = null;
    Session hsession = null;
    javax.mail.Session msession = null;

    try {
        hfactory = (SessionFactory) ctx.lookup(hibernateSessionFactory);
        hsession = hfactory.openSession();
        msession = (javax.mail.Session) ctx.lookup(durotyMailFactory);

        String pop3Host = msession.getProperty("mail.pop3.host");

        int port = 0;

        try {
            port = Integer.parseInt(msession.getProperty("mail.pop3.port"));
        } catch (Exception ex) {
            port = 0;
        }

        Query query = hsession.getNamedQuery("users-mail");
        query.setBoolean("active", true);
        query.setString("role", "mail");

        ScrollableResults scroll = query.scroll();

        while (scroll.next()) {
            POP3Client client = new POP3Client();

            try {
                if (port > 0) {
                    client.connect(pop3Host, port);
                } else {
                    client.connect(pop3Host);
                }

                client.setState(POP3Client.AUTHORIZATION_STATE);

                //client.setDefaultTimeout()
                Users user = (Users) scroll.get(0);

                String repositoryName = user.getUseUsername();

                if (client.login(repositoryName, user.getUsePassword())) {
                    POP3MessageInfo[] info = client.listUniqueIdentifiers();

                    if ((info != null) && (info.length > 0)) {
                        for (int i = 0; i < info.length; i++) {
                            if (pool.size() >= poolSize) {
                                break;
                            }

                            Reader reader = client.retrieveMessage(info[i].number);

                            boolean existMessage = existMessageName(hfactory.openSession(), user,
                                    info[i].identifier);

                            String key = info[i].identifier + "--" + repositoryName;

                            if (existMessage) {
                                client.deleteMessage(info[i].number);
                            } else {
                                if (!poolContains(key)) {
                                    addPool(key);

                                    MimeMessage mime = buildMimeMessage(info[i].identifier, reader, user);

                                    if (!isSpam(user, mime)) {
                                        client.deleteMessage(info[i].number);

                                        Mailet mailet = new Mailet(this, info[i].identifier, repositoryName,
                                                mime);

                                        Thread thread = new Thread(mailet, key);
                                        thread.start();
                                    } else {
                                        client.deleteMessage(info[i].number);
                                    }
                                }
                            }

                            Thread.sleep(100);
                        }
                    }
                } else {
                }
            } catch (Exception e) {
            } finally {
                System.gc();

                try {
                    client.logout();
                    client.disconnect();
                } catch (Exception e) {
                }
            }
        }
    } catch (Exception e) {
        System.gc();
        pool.clear();
        DLog.log(DLog.ERROR, this.getClass(), e.getMessage());
    } catch (OutOfMemoryError e) {
        System.gc();
        pool.clear();
        DLog.log(DLog.ERROR, this.getClass(), e.getMessage());
    } catch (Throwable e) {
        System.gc();
        pool.clear();
        DLog.log(DLog.ERROR, this.getClass(), e.getMessage());
    } finally {
        System.gc();

        GeneralOperations.closeHibernateSession(hsession);

        setInit(false);
    }
}

From source file:com.dz.module.charge.ChargeAction.java

public void rollbackImport() throws IOException {
    ServletActionContext.getResponse().setContentType("text/plain");
    ServletActionContext.getResponse().setCharacterEncoding("utf-8");
    PrintWriter out = ServletActionContext.getResponse().getWriter();

    JSONArray jarray = JSONArray.fromObject(jsonStr);

    Session session = HibernateSessionFactory.getSession();
    Transaction tx = null;//  w  ww. j  a  v a 2s  .c o  m
    String msg = "???";
    int fid = 0;
    try {
        tx = session.beginTransaction();
        for (int i = 0; i < jarray.size(); i++) {
            int id = Integer.parseInt(jarray.get(i).toString());
            BankRecordTmp bt = (BankRecordTmp) session.get(BankRecordTmp.class, id);
            String licenseNum = bt.getLicenseNum();
            fid = bt.getFid();
            Query q_v = session.createQuery("select carframeNum from Vehicle where licenseNum=:carnum");
            q_v.setString("carnum", licenseNum);
            q_v.setMaxResults(1);
            String carframeNum = q_v.uniqueResult().toString();
            Query q_dept = session.createQuery("select branchFirm from Contract where carframeNum=:id ");
            q_dept.setString("id", carframeNum);
            q_dept.setMaxResults(1);
            String dept = q_dept.uniqueResult().toString();
            Query query = session.createQuery("from ClearTime where department = :dept");
            query.setString("dept", dept);
            Object obj = query.uniqueResult();
            ClearTime ct = (ClearTime) obj;
            Date current = ct.getCurrent();
            if (isYearAndMonth(current, bt.getInTime())) {
                Query q_c = session
                        .createQuery("delete from ChargePlan where feeType='add_bank' and comment=:id");
                q_c.setString("id", "" + id);
                q_c.executeUpdate();
                session.delete(bt);
            } else {
                msg = "???";
            }
        }

        Query q_f = session.createQuery("select count(*) from BankRecordTmp where fid=:id ");
        q_f.setInteger("id", fid);
        long ct = (long) q_f.uniqueResult();
        if (ct == 0) {
            BankFile bf = (BankFile) session.get(BankFile.class, fid);
            session.delete(bf);
        }
        tx.commit();
    } catch (HibernateException ex) {
        ex.printStackTrace();
        if (tx != null) {
            tx.rollback();
        }
        msg = "?" + ex.getMessage();
    } finally {
        HibernateSessionFactory.closeSession();
    }

    out.print(msg);

    out.flush();
    out.close();
}

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;//  w  w  w  . ja  v  a2 s. c  o  m
    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

public List<CheckChargeTable> getAllCheckChargeTable(Date date, String dept, String licenseNum,
        int status/**0,1,2,3,4 -- ,,,,*/
) {// w w w  .  j a va2  s.c o  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 Contract contractSearchRentAvaliable(String carfarameNum) {
    Session session = null;/*from   w ww  . j  a  v a2 s . c  om*/
    Transaction tx = null;
    Contract contract = null;
    try {
        session = HibernateSessionFactory.getSession();
        tx = (Transaction) session.beginTransaction();
        Query query = session.createQuery("from Contract where carframeNum = :carframeNum and state in (0,-1)");
        query.setString("carframeNum", carfarameNum);
        contract = (Contract) query.uniqueResult();
        tx.commit();
    } catch (HibernateException e) {
        if (tx != null) {
            tx.rollback();
        }
        throw e;
    } finally {
        HibernateSessionFactory.closeSession();
    }
    return contract;
}