Example usage for org.hibernate SQLQuery setString

List of usage examples for org.hibernate SQLQuery setString

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

Introduction

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

Prototype

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

Source Link

Document

Bind a positional String-valued parameter.

Usage

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

License:Open Source License

/**
 * Test for category in use/*w  w  w .  j a  va  2s. com*/
 */
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 ww.j  a  va  2  s .  c o  m*/
 */
@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  w w  .ja v a  2 s .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.NodeDocumentDAO.java

License:Open Source License

/**
 * Clear pending extraction queue/*from   w ww .ja  v a  2s .com*/
 *
 * Note: the HQL query fails due to https://hibernate.atlassian.net/browse/HHH-1657
 */
public int resetPendingExtractionFlag(String docUuid) throws DatabaseException {
    log.debug("resetPendingExtractionFlag({})", docUuid);
    // String qs = "update NodeDocument nd set nd.textExtracted=:extracted where nd.uuid=:uuid";
    String sql = "update OKM_NODE_DOCUMENT set NDC_TEXT='', NDC_TEXT_EXTRACTED='F' where NBS_UUID=:uuid";
    Session session = null;
    Transaction tx = null;
    int rowCount = 0;

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

        // Query q = session.createQuery(qs);
        // q.setBoolean("extracted", false);
        // q.setString("uuid", docUuid);
        // rowCount = q.executeUpdate();

        SQLQuery q = session.createSQLQuery(sql);
        q.setString("uuid", docUuid);
        rowCount = q.executeUpdate();

        HibernateUtil.commit(tx);
        log.debug("resetPendingExtractionFlag: {}", rowCount);
        return rowCount;
    } 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  va 2  s .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 a  va 2s  .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  .ja  v  a 2  s . co  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.ori.outlierserver.dao.ReadingsDao.java

public List<Reading> getLastNReadingsByClientId(String clientId, int N) {
    List<Reading> theReadingList = null;
    Session theSession = null;/*from   w ww .  j  a va 2 s  .  c om*/
    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;
}

From source file:com.redprairie.moca.job.dao.hibernate.TU_JobDefinitionHibernateDAO.java

License:Open Source License

/**
 * This test is here to confirm that since job_definition can be a 
 * nvarchar that we are able to do a select for it using sql query.
 *///from   ww w  . j ava2  s .c  om
@Test
public void testIsEntryAvailableHibernate() {
    Session hibernateSession = HibernateTools.getSession();

    SQLQuery query = hibernateSession
            .createSQLQuery("select job_id from job_definition" + "  where job_id = ?");

    query.setString(0, "NOT-PRESENT");

    Assert.assertTrue("There shouldn't be a NOT-PRESENT job in the db.", query.list().isEmpty());
}