Example usage for org.hibernate SQLQuery list

Introduction

In this page you can find the example usage for org.hibernate SQLQuery list.

Prototype

List<R> list();

Source Link

Document

Return the query results as a List.

Usage

From source file:com.neu.web.phmis.dao.ResourceDao.java

public void createRequest(DeviceRequestBean deviceRequestBean) {

    try {/*w w  w .  ja  v  a 2 s. c om*/

        session = HibernateUtil.getSessionFactory().openSession();
        session.beginTransaction();
        SQLQuery query = session.createSQLQuery("CALL createDeviceRequest(:1, :2, :3, :4, :5, :6)");
        query.setInteger("1", deviceRequestBean.getCreatedBy().getId());
        query.setString("2", deviceRequestBean.getNumber());
        query.setInteger("3", deviceRequestBean.getTypeId());
        query.setInteger("4", deviceRequestBean.getStatusId());
        query.setInteger("5", deviceRequestBean.getWarehouse().getId());
        query.setString("6", deviceRequestBean.getSpecialInstruction());
        List<Integer> list = query.list();
        deviceRequestBean.setId(list.get(0));

        for (ProductBean p : deviceRequestBean.getRequestedProducts()) {
            query = session.createSQLQuery("INSERT INTO productrequested VALUES (:requestId, :productId)");
            query.setInteger("requestId", deviceRequestBean.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();
    }

}

---

From source file:com.neu.web.phmis.dao.ResourceDao.java

public int isDateAvailable(Date date, SurgeryRequestBean surgeryRequestBean) {

    int flag = 0;

    try {/*from w  ww .  j a  v  a2  s. c  o m*/

        session = HibernateUtil.getSessionFactory().openSession();
        SQLQuery query = session.createSQLQuery("CALL isDateAvailable (:1, :2, :3);");
        query.setInteger("1", surgeryRequestBean.getCreatedBy().getEnterpriseBean().getId());
        query.setDate("2", date);
        query.setInteger("3", surgeryRequestBean.getStaffCount());
        List<Integer> list = query.list();
        flag = list.get(0).intValue();

    } catch (HibernateException e) {
        session.getTransaction().rollback();
        surgeryRequestBean = null;
        e.printStackTrace();
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        session.close();
        return flag;
    }

}

---

From source file:com.openkm.dao.NodeBaseDAO.java

License:Open Source License

/**
 * Test for category in use//from  w  ww  .j ava  2s  .c om
 */
public boolean isCategoryInUse(String catUuid) throws DatabaseException {
    log.debug("isCategoryInUse({}, {})", catUuid);
    final String qs = "from NodeBase nb where :category in elements(nb.categories)";
    final String sql = "select NCT_NODE from OKM_NODE_CATEGORY where NCT_CATEGORY = :catUuid";
    Session session = null;
    Transaction tx = null;
    boolean check;

    try {
        session = HibernateUtil.getSessionFactory().openSession();
        tx = session.beginTransaction();

        if (Config.NATIVE_SQL_OPTIMIZATIONS) {
            SQLQuery q = session.createSQLQuery(sql);
            // q.setCacheable(true);
            q.setString("catUuid", catUuid);
            q.addScalar("NCT_NODE", StandardBasicTypes.STRING);
            check = !q.list().isEmpty();
        } else {
            Query q = session.createQuery(qs).setCacheable(true);
            q.setString("category", catUuid);
            check = !q.list().isEmpty();
        }

        HibernateUtil.commit(tx);
        log.debug("isCategoryInUse: {}", check);
        return check;
    } catch (HibernateException e) {
        HibernateUtil.rollback(tx);
        throw new DatabaseException(e.getMessage(), e);
    } finally {
        HibernateUtil.close(session);
    }
}

---

From source file:com.openkm.dao.NodeDocumentDAO.java

License:Open Source License

/**
 * Search nodes by category/*from   w w w .  j a  va  2s . c om*/
 */
@SuppressWarnings("unchecked")
public List<NodeDocument> findByCategory(String catUuid) throws PathNotFoundException, DatabaseException {
    log.debug("findByCategory({})", catUuid);
    final String qs = "from NodeDocument nd where :category in elements(nd.categories) order by nd.name";
    final String sql = "select NBS_UUID from OKM_NODE_CATEGORY, OKM_NODE_DOCUMENT "
            + "where NCT_CATEGORY = :catUuid and NCT_NODE = NBS_UUID";
    List<NodeDocument> ret = new ArrayList<NodeDocument>();
    Session session = null;
    Transaction tx = null;

    try {
        long begin = System.currentTimeMillis();
        session = HibernateUtil.getSessionFactory().openSession();
        tx = session.beginTransaction();

        // Security Check
        NodeBase catNode = (NodeBase) session.load(NodeBase.class, catUuid);
        SecurityHelper.checkRead(catNode);

        if (Config.NATIVE_SQL_OPTIMIZATIONS) {
            SQLQuery q = session.createSQLQuery(sql);
            q.setCacheable(true);
            q.setCacheRegion(CACHE_DOCUMENTS_BY_CATEGORY);
            q.setString("catUuid", catUuid);
            q.addScalar("NBS_UUID", StandardBasicTypes.STRING);

            for (String uuid : (List<String>) q.list()) {
                NodeDocument nDoc = (NodeDocument) session.load(NodeDocument.class, uuid);
                ret.add(nDoc);
            }
        } else {
            Query q = session.createQuery(qs).setCacheable(true);
            q.setString("category", catUuid);
            ret = q.list();
        }

        // Security Check
        SecurityHelper.pruneNodeList(ret);

        initialize(ret);
        HibernateUtil.commit(tx);
        SystemProfiling.log(catUuid, System.currentTimeMillis() - begin);
        log.trace("findByCategory.Time: {}", System.currentTimeMillis() - begin);
        log.debug("findByCategory: {}", ret);
        return ret;
    } catch (PathNotFoundException e) {
        HibernateUtil.rollback(tx);
        throw e;
    } catch (DatabaseException e) {
        HibernateUtil.rollback(tx);
        throw e;
    } catch (HibernateException e) {
        HibernateUtil.rollback(tx);
        throw new DatabaseException(e.getMessage(), e);
    } finally {
        HibernateUtil.close(session);
    }
}

---

From source file:com.openkm.dao.NodeDocumentDAO.java

License:Open Source License

/**
 * Search nodes by keyword// www  . j a v  a 2s  .c om
 */
@SuppressWarnings("unchecked")
public List<NodeDocument> findByKeyword(String keyword) throws DatabaseException {
    log.debug("findByKeyword({})", keyword);
    final String qs = "from NodeDocument nd where :keyword in elements(nd.keywords) order by nd.name";
    final String sql = "select NBS_UUID from OKM_NODE_KEYWORD, OKM_NODE_DOCUMENT "
            + "where NKW_KEYWORD = :keyword and NKW_NODE = NBS_UUID";
    List<NodeDocument> ret = new ArrayList<NodeDocument>();
    Session session = null;
    Transaction tx = null;

    try {
        long begin = System.currentTimeMillis();
        session = HibernateUtil.getSessionFactory().openSession();
        tx = session.beginTransaction();

        if (Config.NATIVE_SQL_OPTIMIZATIONS) {
            SQLQuery q = session.createSQLQuery(sql);
            q.setCacheable(true);
            q.setCacheRegion(CACHE_DOCUMENTS_BY_KEYWORD);
            q.setString("keyword", keyword);
            q.addScalar("NBS_UUID", StandardBasicTypes.STRING);

            for (String uuid : (List<String>) q.list()) {
                NodeDocument nDoc = (NodeDocument) session.load(NodeDocument.class, uuid);
                ret.add(nDoc);
            }
        } else {
            Query q = session.createQuery(qs).setCacheable(true);
            q.setString("keyword", keyword);
            ret = q.list();
        }

        // Security Check
        SecurityHelper.pruneNodeList(ret);

        initialize(ret);
        HibernateUtil.commit(tx);
        SystemProfiling.log(keyword, System.currentTimeMillis() - begin);
        log.trace("findByKeyword.Time: {}", System.currentTimeMillis() - begin);
        log.debug("findByKeyword: {}", ret);
        return ret;
    } catch (DatabaseException e) {
        HibernateUtil.rollback(tx);
        throw e;
    } catch (HibernateException e) {
        HibernateUtil.rollback(tx);
        throw new DatabaseException(e.getMessage(), e);
    } finally {
        HibernateUtil.close(session);
    }
}

---

From source file:com.openkm.dao.NodeFolderDAO.java

License:Open Source License

/**
 * Search nodes by category//from w w w .java  2s. co  m
 */
@SuppressWarnings("unchecked")
public List<NodeFolder> findByCategory(String catUuid) throws PathNotFoundException, DatabaseException {
    log.debug("findByCategory({})", catUuid);
    long begin = System.currentTimeMillis();
    final String qs = "from NodeFolder nf where :category in elements(nf.categories) order by nf.name";
    final String sql = "select NBS_UUID from OKM_NODE_CATEGORY, OKM_NODE_FOLDER "
            + "where NCT_CATEGORY = :catUuid and NCT_NODE = NBS_UUID";
    List<NodeFolder> ret = new ArrayList<NodeFolder>();
    Session session = null;
    Transaction tx = null;

    try {
        session = HibernateUtil.getSessionFactory().openSession();
        tx = session.beginTransaction();

        // Security Check
        NodeBase catNode = (NodeBase) session.load(NodeBase.class, catUuid);
        SecurityHelper.checkRead(catNode);

        if (Config.NATIVE_SQL_OPTIMIZATIONS) {
            SQLQuery q = session.createSQLQuery(sql);
            q.setCacheable(true);
            q.setCacheRegion(CACHE_FOLDERS_BY_CATEGORY);
            q.setString("catUuid", catUuid);
            q.addScalar("NBS_UUID", StandardBasicTypes.STRING);

            for (String uuid : (List<String>) q.list()) {
                NodeFolder nFld = (NodeFolder) session.load(NodeFolder.class, uuid);
                ret.add(nFld);
            }
        } else {
            Query q = session.createQuery(qs).setCacheable(true);
            q.setString("category", catUuid);
            ret = q.list();
        }

        // Security Check
        SecurityHelper.pruneNodeList(ret);

        initialize(ret);
        HibernateUtil.commit(tx);
        SystemProfiling.log(catUuid, System.currentTimeMillis() - begin);
        log.trace("findByCategory.Time: {}", System.currentTimeMillis() - begin);
        log.debug("findByCategory: {}", ret);
        return ret;
    } catch (PathNotFoundException e) {
        HibernateUtil.rollback(tx);
        throw e;
    } catch (DatabaseException e) {
        HibernateUtil.rollback(tx);
        throw e;
    } catch (HibernateException e) {
        HibernateUtil.rollback(tx);
        throw new DatabaseException(e.getMessage(), e);
    } finally {
        HibernateUtil.close(session);
    }
}

---

From source file:com.openkm.dao.NodeFolderDAO.java

License:Open Source License

/**
 * Search nodes by keyword/*from  w  w w .  j av a  2 s  . c o m*/
 */
@SuppressWarnings("unchecked")
public List<NodeFolder> findByKeyword(String keyword) throws DatabaseException {
    log.debug("findByKeyword({})", keyword);
    final String qs = "from NodeFolder nf where :keyword in elements(nf.keywords) order by nf.name";
    final String sql = "select NBS_UUID from OKM_NODE_KEYWORD, OKM_NODE_FOLDER "
            + "where NKW_KEYWORD = :keyword and NKW_NODE = NBS_UUID";
    List<NodeFolder> ret = new ArrayList<NodeFolder>();
    Session session = null;
    Transaction tx = null;

    try {
        session = HibernateUtil.getSessionFactory().openSession();
        tx = session.beginTransaction();

        if (Config.NATIVE_SQL_OPTIMIZATIONS) {
            SQLQuery q = session.createSQLQuery(sql);
            q.setCacheable(true);
            q.setCacheRegion(CACHE_FOLDERS_BY_KEYWORD);
            q.setString("keyword", keyword);
            q.addScalar("NBS_UUID", StandardBasicTypes.STRING);

            for (String uuid : (List<String>) q.list()) {
                NodeFolder nFld = (NodeFolder) session.load(NodeFolder.class, uuid);
                ret.add(nFld);
            }
        } else {
            Query q = session.createQuery(qs).setCacheable(true);
            q.setString("keyword", keyword);
            ret = q.list();
        }

        // Security Check
        SecurityHelper.pruneNodeList(ret);

        initialize(ret);
        HibernateUtil.commit(tx);
        log.debug("findByKeyword: {}", ret);
        return ret;
    } catch (DatabaseException e) {
        HibernateUtil.rollback(tx);
        throw e;
    } catch (HibernateException e) {
        HibernateUtil.rollback(tx);
        throw new DatabaseException(e.getMessage(), e);
    } finally {
        HibernateUtil.close(session);
    }
}

---

From source file:com.openkm.dao.NodeMailDAO.java

License:Open Source License

/**
 * Search nodes by category/*from   w  ww.j a  v a2s .  com*/
 */
@SuppressWarnings("unchecked")
public List<NodeMail> findByCategory(String catUuid) throws PathNotFoundException, DatabaseException {
    log.debug("findByCategory({})", catUuid);
    long begin = System.currentTimeMillis();
    final String qs = "from NodeMail nm where :category in elements(nm.categories) order by nm.name";
    final String sql = "select NBS_UUID from OKM_NODE_CATEGORY, OKM_NODE_MAIL "
            + "where NCT_CATEGORY = :catUuid and NCT_NODE = NBS_UUID";
    List<NodeMail> ret = new ArrayList<NodeMail>();
    Session session = null;
    Transaction tx = null;

    try {
        session = HibernateUtil.getSessionFactory().openSession();
        tx = session.beginTransaction();

        // Security Check
        NodeBase catNode = (NodeBase) session.load(NodeBase.class, catUuid);
        SecurityHelper.checkRead(catNode);

        if (Config.NATIVE_SQL_OPTIMIZATIONS) {
            SQLQuery q = session.createSQLQuery(sql);
            q.setCacheable(true);
            q.setCacheRegion(CACHE_MAILS_BY_CATEGORY);
            q.setString("catUuid", catUuid);
            q.addScalar("NBS_UUID", StandardBasicTypes.STRING);

            for (String uuid : (List<String>) q.list()) {
                NodeMail nMail = (NodeMail) session.load(NodeMail.class, uuid);
                ret.add(nMail);
            }
        } else {
            Query q = session.createQuery(qs).setCacheable(true);
            q.setString("category", catUuid);
            ret = q.list();
        }

        // Security Check
        SecurityHelper.pruneNodeList(ret);

        initialize(ret);
        HibernateUtil.commit(tx);
        SystemProfiling.log(catUuid, System.currentTimeMillis() - begin);
        log.trace("findByCategory.Time: {}", System.currentTimeMillis() - begin);
        log.debug("findByCategory: {}", ret);
        return ret;
    } catch (PathNotFoundException e) {
        HibernateUtil.rollback(tx);
        throw e;
    } catch (DatabaseException e) {
        HibernateUtil.rollback(tx);
        throw e;
    } catch (HibernateException e) {
        HibernateUtil.rollback(tx);
        throw new DatabaseException(e.getMessage(), e);
    } finally {
        HibernateUtil.close(session);
    }
}

---

From source file:com.openkm.dao.NodeMailDAO.java

License:Open Source License

/**
 * Search nodes by keyword//from  w w  w.j  a v  a  2 s  . c  om
 */
@SuppressWarnings("unchecked")
public List<NodeMail> findByKeyword(String keyword) throws DatabaseException {
    log.debug("findByKeyword({})", keyword);
    final String qs = "from NodeMail nm where :keyword in elements(nm.keywords) order by nm.name";
    final String sql = "select NBS_UUID from OKM_NODE_KEYWORD, OKM_NODE_MAIL "
            + "where NKW_KEYWORD = :keyword and NKW_NODE = NBS_UUID";
    List<NodeMail> ret = new ArrayList<NodeMail>();
    Session session = null;
    Transaction tx = null;

    try {
        session = HibernateUtil.getSessionFactory().openSession();
        tx = session.beginTransaction();

        if (Config.NATIVE_SQL_OPTIMIZATIONS) {
            SQLQuery q = session.createSQLQuery(sql);
            q.setCacheable(true);
            q.setCacheRegion(CACHE_MAILS_BY_KEYWORD);
            q.setString("keyword", keyword);
            q.addScalar("NBS_UUID", StandardBasicTypes.STRING);

            for (String uuid : (List<String>) q.list()) {
                NodeMail nMail = (NodeMail) session.load(NodeMail.class, uuid);
                ret.add(nMail);
            }
        } else {
            Query q = session.createQuery(qs).setCacheable(true);
            q.setString("keyword", keyword);
            ret = q.list();
        }

        // Security Check
        SecurityHelper.pruneNodeList(ret);

        initialize(ret);
        HibernateUtil.commit(tx);
        log.debug("findByKeyword: {}", ret);
        return ret;
    } catch (DatabaseException e) {
        HibernateUtil.rollback(tx);
        throw e;
    } catch (HibernateException e) {
        HibernateUtil.rollback(tx);
        throw new DatabaseException(e.getMessage(), e);
    } finally {
        HibernateUtil.close(session);
    }
}

---

From source file:com.ori.outlierserver.dao.ReadingsDao.java

public List<Reading> getLastNReadingsByClientId(String clientId, int N) {
    List<Reading> theReadingList = null;
    Session theSession = null;/*w  ww. ja v a  2 s .com*/
    if (N <= 0) {
        return Collections.<Reading>emptyList();
    }
    try {
        theSession = sessionFactory.openSession();
        theSession.beginTransaction();
        String sql = "SELECT * FROM readings WHERE client_id = :id limit :lim";
        SQLQuery query = theSession.createSQLQuery(sql);
        query.addEntity(Reading.class);
        query.setString("id", clientId);
        query.setInteger("lim", N);
        theReadingList = query.list();
        theSession.getTransaction().commit();
    } catch (Exception e) {
        if (theSession != null) {
            theSession.getTransaction().rollback();
        }
    } finally {
        if (theSession != null) {
            theSession.close();
        }
    }
    return theReadingList;
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• 49
• 50
• »