Example usage for org.hibernate SQLQuery addScalar

List of usage examples for org.hibernate SQLQuery addScalar

Introduction

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

Prototype

SQLQuery<T> addScalar(String columnAlias, Type type);

Source Link

Document

Declare a scalar query result.

Usage

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

License:Open Source License

/**
 * Search nodes by category//from ww  w.  j a v a 2  s . com
 */
@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/*from  w  ww.j ava  2s . c  o m*/
 */
@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  .  ja v a 2 s.c o 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 ww  .  j  ava 2 s.  co 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 w  w .  j  a va2 s .  c  o m
 */
@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  www . j a  v a 2  s.c o  m
 */
@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.sip.pe.dao.ClienteDAO.java

public List<Object> ObtenerHistorialCliente(int idCliente) {

    List<Object> listaHistorial = null;

    SessionFactory sesFact = HibernateUtil.getSessionFactory();
    Session ses = sesFact.openSession();

    try {//  w  w w  . java 2 s  .co m

        SQLQuery q = ses.createSQLQuery("exec sp_ObtenerHistorialCliente :IdCliente");

        q.addScalar("ID_TRANSACCION", INTEGER);
        q.addScalar("FE_TRANSACCION");
        q.addScalar("TIPO_CUENTA");
        q.addScalar("SERVICIO");
        q.addScalar("ENTIDAD");
        q.addScalar("BANCO");
        q.addScalar("NU_CUENTA");
        q.addScalar("IMPORTE_PAGO", FLOAT);

        q.setInteger("IdCliente", idCliente);
        listaHistorial = q.list();

    } catch (HibernateException e) {
        System.out.println("Error en la aplicacion: " + e.toString());
        System.out.println("causa: " + e.getClass());
        e.printStackTrace();
    } finally {
        ses.close();
    }
    return listaHistorial;
}

From source file:com.sysware.customize.hd.investment.baseData.vendor.VendorDaoImpl.java

private void setFindByMaterialIds(SQLQuery query) {
    query = query.addScalar("vendorID", Hibernate.STRING);
    query = query.addScalar("vendorName", Hibernate.STRING);
    query = query.addScalar("vendorCode", Hibernate.STRING);
    query = query.addScalar("accountID", Hibernate.STRING);
    query = query.addScalar("address", Hibernate.STRING);
    query = query.addScalar("bank", Hibernate.STRING);
    query = query.addScalar("businessScope", Hibernate.STRING);
    query = query.addScalar("initialEvaluationDate", Hibernate.DATE);
    query = query.addScalar("vendorLevel", Hibernate.STRING);
    query = query.addScalar("phone", Hibernate.STRING);
    query = query.addScalar("reviewDate", Hibernate.DATE);
    query = query.addScalar("taxID", Hibernate.STRING);
    query = query.addScalar("reposal", Hibernate.STRING);
    query = query.addScalar("property", Hibernate.STRING);
    query = query.addScalar("simpleName", Hibernate.STRING);
    query = query.addScalar("type", Hibernate.STRING);
    query = query.addScalar("sector", Hibernate.STRING);
}

From source file:com.sysware.customize.hd.investment.baseData.vendor.VendorDaoImpl.java

private void SetAVendor(SQLQuery query) {
    query = query.addScalar("vendorCode", Hibernate.STRING);
    query = query.addScalar("vendorName", Hibernate.STRING);
}

From source file:com.thoughtworks.go.server.persistence.MaterialRepository.java

License:Apache License

private List<Long> fromInclusiveModificationsForPipelineRange(Session session, String pipelineName,
        Integer fromCounter, Integer toCounter) {
    String pipelineIdsSql = queryExtensions.queryFromInclusiveModificationsForPipelineRange(pipelineName,
            fromCounter, toCounter);//from  w  ww .j  a  v a2s . co  m
    SQLQuery pipelineIdsQuery = session.createSQLQuery(pipelineIdsSql);
    final List ids = pipelineIdsQuery.list();
    if (ids.isEmpty()) {
        return new ArrayList<>();
    }

    String minMaxQuery = " SELECT mods1.materialId as materialId, min(mods1.id) as min, max(mods1.id) as max"
            + " FROM modifications mods1 "
            + "     INNER JOIN pipelineMaterialRevisions pmr ON (mods1.id >= pmr.actualFromRevisionId AND mods1.id <= pmr.toRevisionId) AND mods1.materialId = pmr.materialId "
            + " WHERE pmr.pipelineId IN (:ids) " + " GROUP BY mods1.materialId";

    SQLQuery query = session
            .createSQLQuery("SELECT mods.id " + " FROM modifications mods" + "     INNER JOIN (" + minMaxQuery
                    + ") as edges on edges.materialId = mods.materialId and mods.id >= min and mods.id <= max"
                    + " ORDER BY mods.materialId ASC, mods.id DESC");
    query.addScalar("id", new LongType());
    query.setParameterList("ids", ids);

    return query.list();
}