List of usage examples for org.hibernate SQLQuery getQueryString
String getQueryString();
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); } }