Example usage for org.hibernate SQLQuery getQueryString

List of usage examples for org.hibernate SQLQuery getQueryString

Introduction

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

Prototype

String getQueryString();

Source Link

Document

Get the query string.

Usage

From source file:org.bonitasoft.engine.persistence.SQLQueryBuilder.java

License:Open Source License

private void addConstantsAsParameters(SQLQuery sqlQuery) {
    if (sqlQuery.getQueryString().contains(":" + TRUE_VALUE_PARAMETER)) {
        if (useIntegerForBoolean(vendor)) {
            sqlQuery.setParameter(TRUE_VALUE_PARAMETER, 1);
        } else {/*ww  w . j  a  v  a 2  s. c  om*/
            sqlQuery.setParameter(TRUE_VALUE_PARAMETER, true);
        }
    }
}

From source file:org.chenillekit.hibernate.daos.AbstractHibernateDAO.java

License:Apache License

/**
 * retieve entites by SQL query./* w w  w  .j  a v  a 2 s  .  c o m*/
 *
 * @param queryString the query to find entities.
 *
 * @return list of entities
 */
@SuppressWarnings("unchecked")
public List<T> findBySQLQuery(String queryString) {
    SQLQuery sqlQuery = session.createSQLQuery(queryString);

    if (logger.isDebugEnabled())
        logger.debug(sqlQuery.getQueryString());

    return sqlQuery.list();
}

From source file:org.hyperic.hq.grouping.CritterTranslator.java

License:Open Source License

private SQLQuery translate(CritterTranslationContext ctx, CritterList cList, boolean issueCount, boolean desc) {
    StringBuilder sql = new StringBuilder();
    Map txContexts = new HashMap(cList.getCritters().size());
    if (issueCount) {
        sql.append("select count(1) from ");
    } else {//from  w  w  w. j  a  v a  2 s. co  m
        sql.append("select {res.*} from ");
    }
    if (cList.isAll()) {
        sql.append("EAM_RESOURCE res \n");
        sql.append(getSQLConstraints(ctx, cList, txContexts));
        sql.append(PermissionManagerFactory.getInstance().getSQLWhere(ctx.getSubject().getId()));
    } else {
        sql.append(getUnionStmts(ctx, cList, txContexts));
        sql.append(PermissionManagerFactory.getInstance().getSQLWhere(ctx.getSubject().getId()));
        sql.append(") res ");
    }

    if (!issueCount) {
        sql.append(" ORDER BY res.name ");
        if (desc)
            sql.append("DESC");
    }
    if (_log.isDebugEnabled()) {
        _log.debug("Created SQL: [" + sql + "]");
    }
    SQLQuery res = ctx.getSession().createSQLQuery(sql.toString());
    if (_log.isDebugEnabled()) {
        _log.debug("Translated into: [" + res.getQueryString() + "]");
    }
    if (!issueCount) {
        res.addEntity("res", Resource.class);
    }
    for (Iterator i = cList.getCritters().iterator(); i.hasNext();) {
        Critter c = (Critter) i.next();
        c.bindSqlParams((CritterTranslationContext) txContexts.get(c), res);
    }
    return res;
}

From source file:org.jcvi.ometa.hibernate.dao.SecurityDAO.java

License:Open Source License

/**
 * Common code for both project and sample, to get data by list-of-identifiers.
 * @throws Exception if anything requested is left out, iff failureResponse == throw ex.
 *///  ww  w.  j  av  a 2 s  .  co m
private List<Long> getListOfAuthorizedById(List<Long> ids, String username,
        ResponseToFailedAuthorization failureResponse, AccessLevel accessLevel, Session session,
        String securedIdsQuery, String openAndSecuredIdsQuery, String securedParamListName,
        String openParamListName, String returnVarName) throws Exception {

    ids = uniquifyIds(ids);

    String queryStr = null;
    if (accessLevel == AccessLevel.View) {
        queryStr = openAndSecuredIdsQuery.replace(PROJ_GRP_SUBST_STR, VIEW_PROJECT_GROUP_FIELD);
    } else {
        queryStr = securedIdsQuery.replace(PROJ_GRP_SUBST_STR, EDIT_PROJECT_GROUP_FIELD);
    }
    SQLQuery query = session.createSQLQuery(queryStr);
    query.addScalar(returnVarName, Hibernate.STRING);
    if (accessLevel == AccessLevel.View) {
        query.setParameterList(openParamListName, ids);
    }
    query.setParameterList(securedParamListName, ids);
    String queryUsername = username == null ? UNLOGGED_IN_USER : username;
    query.setParameter(USERNAME_PARAM, queryUsername);

    logger.debug(query.getQueryString());
    List<Long> rtnVal = query.list();
    if (failureResponse == ResponseToFailedAuthorization.ThrowException && rtnVal.size() < ids.size()) {
        String idStr = joinIdList(ids);
        String message = makeUserReadableMessage(username, idStr);
        logger.error(message);
        throw new ForbiddenResourceException(message);
    }

    return rtnVal;
}

From source file:ubic.gemma.persistence.service.analysis.expression.diff.DifferentialExpressionResultDaoImpl.java

License:Apache License

@Override
public Map<Long, Map<Long, DiffExprGeneSearchResult>> findDiffExAnalysisResultIdsInResultSets(
        Collection<DiffExResultSetSummaryValueObject> resultSets, Collection<Long> geneIds) {

    Map<Long, Map<Long, DiffExprGeneSearchResult>> results = new HashMap<>();

    Session session = this.getSessionFactory().getCurrentSession();

    Map<Long, DiffExResultSetSummaryValueObject> resultSetIdsMap = EntityUtils.getIdMap(resultSets,
            "getResultSetId");

    Map<Long, Collection<Long>> foundInCache = this.fillFromCache(results, resultSetIdsMap.keySet(), geneIds);

    if (!foundInCache.isEmpty()) {
        AbstractDao.log.info("Results for " + foundInCache.size() + " resultsets found in cache");
    } else {//  w  w  w.  j  ava 2s . co  m
        AbstractDao.log.info("No results were in the cache");
    }

    Collection<Long> resultSetsNeeded = this.stripUnneededResultSets(foundInCache, resultSetIdsMap.keySet(),
            geneIds);

    // Are we finished?
    if (resultSetsNeeded.isEmpty()) {
        AbstractDao.log.info("All results were in the cache.");
        return results;
    }

    AbstractDao.log.info(foundInCache.size() + "/" + resultSetIdsMap.size()
            + " resultsSets had at least some cached results; still need to query " + resultSetsNeeded.size());

    assert !resultSetsNeeded.isEmpty();

    org.hibernate.SQLQuery queryObject = session.createSQLQuery(
            DifferentialExpressionResultDaoImpl.fetchBatchDifferentialExpressionAnalysisResultsByResultSetsAndGeneQuery);

    /*
     * These values have been tweaked to probe for performance issues.
     */
    int resultSetBatchSize = 50;
    int geneBatchSize = 100;

    if (resultSetsNeeded.size() > geneIds.size()) {
        resultSetBatchSize = Math.min(500, resultSetsNeeded.size());
        AbstractDao.log.info("Batching by result sets (" + resultSetsNeeded.size() + " resultSets); "
                + geneIds.size() + " genes; batch size=" + resultSetBatchSize);

    } else {
        geneBatchSize = Math.min(200, geneIds.size());
        AbstractDao.log.info("Batching by genes (" + geneIds.size() + " genes); " + resultSetsNeeded.size()
                + " resultSets; batch size=" + geneBatchSize);
    }

    final int numResultSetBatches = (int) Math.ceil(resultSetsNeeded.size() / resultSetBatchSize);

    queryObject.setFlushMode(FlushMode.MANUAL);

    StopWatch timer = new StopWatch();
    timer.start();
    int numResults = 0;
    long timeForFillingNonSig = 0;

    Map<Long, Map<Long, DiffExprGeneSearchResult>> resultsFromDb = new HashMap<>();

    int numResultSetBatchesDone = 0;

    // Iterate over batches of resultSets
    for (Collection<Long> resultSetIdBatch : new BatchIterator<>(resultSetsNeeded, resultSetBatchSize)) {

        if (AbstractDao.log.isDebugEnabled())
            AbstractDao.log.debug("Starting batch of resultsets: "
                    + StringUtils.abbreviate(StringUtils.join(resultSetIdBatch, ","), 100));

        /*
         * Get the probes using the CommonQueries gene2cs. Otherwise we (in effect) end up doing this over and over
         * again.
         */
        Map<Long, Collection<Long>> cs2GeneIdMap = this.getProbesForGenesInResultSetBatch(session, geneIds,
                resultSetIdsMap, resultSetIdBatch);

        queryObject.setParameterList("rs_ids", resultSetIdBatch);

        int numGeneBatchesDone = 0;
        final int numGeneBatches = (int) Math.ceil(cs2GeneIdMap.size() / geneBatchSize);

        StopWatch innerQt = new StopWatch();

        // iterate over batches of probes (genes)
        for (Collection<Long> probeBatch : new BatchIterator<>(cs2GeneIdMap.keySet(), geneBatchSize)) {

            if (AbstractDao.log.isDebugEnabled())
                AbstractDao.log.debug("Starting batch of probes: "
                        + StringUtils.abbreviate(StringUtils.join(probeBatch, ","), 100));

            // would it help to sort the probeBatch/
            List<Long> pbL = new Vector<>(probeBatch);
            Collections.sort(pbL);

            queryObject.setParameterList("probe_ids", pbL);

            innerQt.start();
            List<?> queryResult = queryObject.list();
            innerQt.stop();

            if (innerQt.getTime() > 2000) {
                // show the actual query with params.
                AbstractDao.log.info("Query time: " + innerQt.getTime() + "ms:\n "
                        + queryObject.getQueryString().replace(":probe_ids", StringUtils.join(probeBatch, ","))
                                .replace(":rs_ids", StringUtils.join(resultSetIdBatch, ",")));
            }
            innerQt.reset();

            /*
             * Each query tuple are the probe, result, resultsSet, qvalue, pvalue.
             */
            for (Object o : queryResult) {
                // Long resultSetId = ( ( BigInteger )((Object[])o)[2] ).longValue();
                // if (!resultSetId.equals)
                numResults += this.processResultTuple(o, resultsFromDb, cs2GeneIdMap);
            }

            if (timer.getTime() > 5000 && AbstractDao.log.isInfoEnabled()) {
                AbstractDao.log.info("Batch time: " + timer.getTime() + "ms; Fetched DiffEx " + numResults
                        + " results so far. " + numResultSetBatchesDone + "/" + numResultSetBatches
                        + " resultset batches completed. " + numGeneBatchesDone + "/" + numGeneBatches
                        + " gene batches done.");
                timer.reset();
                timer.start();
            }

            // Check if task was cancelled.
            if (Thread.currentThread().isInterrupted()) {
                throw new TaskCancelledException("Search was cancelled");
            }

            numGeneBatchesDone++;

            if (DifferentialExpressionResultDaoImpl.CORRECTED_PVALUE_THRESHOLD_TO_BE_CONSIDERED_DIFF_EX < 1.0) {
                timeForFillingNonSig += this.fillNonSignificant(pbL, resultSetIdsMap, resultsFromDb,
                        resultSetIdBatch, cs2GeneIdMap, session);
            }
        } // over probes.

        // Check if task was cancelled.
        if (Thread.currentThread().isInterrupted()) {
            throw new TaskCancelledException("Search was cancelled");
        }

        numResultSetBatchesDone++;

    }

    if (timer.getTime() > 1000 && AbstractDao.log.isInfoEnabled()) {
        AbstractDao.log.info("Fetching DiffEx from DB took total of " + timer.getTime() + " ms : geneIds="
                + StringUtils.abbreviate(StringUtils.join(geneIds, ","), 50) + " result set="
                + StringUtils.abbreviate(StringUtils.join(resultSetsNeeded, ","), 50));
        if (timeForFillingNonSig > 100) {
            AbstractDao.log.info("Filling in non-significant values: " + timeForFillingNonSig + "ms in total");
        }
    }

    // Add the DB results to the cached results.
    this.addToCache(resultsFromDb, resultSetsNeeded, geneIds);

    for (Long resultSetId : resultsFromDb.keySet()) {
        Map<Long, DiffExprGeneSearchResult> geneResults = resultsFromDb.get(resultSetId);
        if (results.containsKey(resultSetId)) {
            results.get(resultSetId).putAll(geneResults);
        } else {
            results.put(resultSetId, geneResults);
        }
    }

    return results;
}

From source file:ubic.gemma.persistence.util.EntityUtils.java

License:Apache License

/**
 * Populates parameters in query created using addGroupAndUserNameRestriction(boolean, boolean).
 *
 * @param queryObject    the query object created using the sql query with group and username restrictions.
 * @param sessionFactory session factory from the DAO that is using this method.
 *///from w w  w  .j a v a2 s .c om
public static void addUserAndGroupParameters(SQLQuery queryObject, SessionFactory sessionFactory) {
    if (SecurityUtil.isUserAnonymous()) {
        return;
    }
    String sqlQuery = queryObject.getQueryString();
    String userName = SecurityUtil.getCurrentUsername();

    // if user is member of any groups.
    if (sqlQuery.contains(":groups")) {
        //noinspection unchecked
        Collection<String> groups = sessionFactory.getCurrentSession().createQuery(
                "select ug.name from UserGroup ug inner join ug.groupMembers memb where memb.userName = :user")
                .setParameter("user", userName).list();
        queryObject.setParameterList("groups", groups);
    }

    if (sqlQuery.contains(":userName")) {
        queryObject.setParameter("userName", userName);
    }

}