org.hibernate.Query Java Examples

@SuppressWarnings("unchecked")
public List<LogEntry> listLogEntries(long logJobId, boolean usePrimaryOrg, int startIndex,
        int noOfRecords) 
                throws DbException { 

    try {
        Query query = null;

        if (usePrimaryOrg) {
            query = session.createQuery("from LogEntry where Job.Id = ? order by Organization, Id asc");
        }
        else {
            query = session.createQuery("from LogEntry where Job.Id = ? order by Organization2, Id asc");
        }
        query.setLong(0, logJobId);
        query.setMaxResults(noOfRecords);
        query.setFirstResult(startIndex);

        return query.list();
    } 
    catch (Exception e) {
        throw handleException(e.getClass().getSimpleName() + " exception in listLogEntries", e);
    }
}
 

@SuppressWarnings("unchecked")
public List<T> find(String hql, Object[] param, Integer page, Integer rows) {
	if (page == null || page < 1) {
		page = 1;
	}
	if (rows == null || rows < 1) {
		rows = 10;
	}

	Query q = this.getCurrentSession().createQuery(hql);

	if (param != null && param.length > 0) {
		for (int i = 0; i < param.length; i++) {
			q.setParameter(i, param[i]);
		}
	}
	return q.setFirstResult((page - 1) * rows).setMaxResults(rows).list();
}
 

private boolean isForumLocked(final Long id) {
    if (id == null) {
        log.error("isForumLocked failed with id: null");
        throw new IllegalArgumentException("Null Argument");
    }

    log.debug("isForumLocked executing with id: " + id);

    HibernateCallback<Boolean> hcb = session -> {
        Query q = session.getNamedQuery("findForumLockedAttribute");
        q.setLong("id", id);
        return (Boolean) q.uniqueResult();
    };

    return getHibernateTemplate().execute(hcb);
}
 

private Map<String, SbiConfig> getConfParametersFromDatabase(Session hibernateSession) {
	Map<String, SbiConfig> existingParametersMap;

	logger.debug("IN");

	existingParametersMap = null;
	try {
		String hql = "from SbiConfig";
		Query hqlQuery = hibernateSession.createQuery(hql);
		List<SbiConfig> configurationParametersFromDatabase = hqlQuery.list();
		existingParametersMap = new HashMap<String, SbiConfig>();
		for (SbiConfig configurationParameterFromDatabase : configurationParametersFromDatabase) {
			existingParametersMap.put(configurationParameterFromDatabase.getLabel(), configurationParameterFromDatabase);
		}
	} catch (Throwable t) {
		throw new SpagoBIRuntimeException("An unexpected error occured while loading configuration parameters from database", t);
	} finally {
		logger.debug("OUT");
	}

	return existingParametersMap;
}
 

/**
   * 
   * @param topicId
   * @param searchText
   * @return
   */
  
  public List findPvtMsgsBySearchText(final String typeUuid, final String searchText, 
        final Date searchFromDate, final Date searchToDate, final boolean searchByText,
        final boolean searchByAuthor, final boolean searchByBody, final boolean searchByLabel, final boolean searchByDate) {

    log.debug("findPvtMsgsBySearchText executing with searchText: " + searchText);

    HibernateCallback<List> hcb = session -> {
        Query q = session.getNamedQuery("findPvtMsgsBySearchText");
        q.setParameter("searchText", "%" + searchText + "%");
        q.setParameter("searchByText", convertBooleanToInteger(searchByText));
        q.setParameter("searchByAuthor", convertBooleanToInteger(searchByAuthor));
        q.setParameter("searchByBody", convertBooleanToInteger(searchByBody));
        q.setParameter("searchByLabel", convertBooleanToInteger(searchByLabel));
        q.setParameter("searchByDate", convertBooleanToInteger(searchByDate));
        q.setParameter("searchFromDate", (searchFromDate == null) ? new Date(0) : searchFromDate);
        q.setParameter("searchToDate", (searchToDate == null) ? new Date(System.currentTimeMillis()) : searchToDate);
        q.setParameter("userId", getCurrentUser());
        q.setParameter("contextId", toolManager.getCurrentPlacement().getContext());
        q.setParameter("typeUuid", typeUuid);
        return q.list();
    };

    return getHibernateTemplate().execute(hcb);
}
 

/**
 * Find all wiki pages by title
 */
@SuppressWarnings("unchecked")
public static List<WikiPage> findAllHistoricByTitle(String title) throws DatabaseException {
	log.debug("findAllHistoricByTitle({})");
	String qs = "select wkp from WikiPage wkp where wkp.title=:title order by wkp.date desc";
	Session session = null;

	try {
		session = HibernateUtil.getSessionFactory().openSession();
		Query q = session.createQuery(qs);
		q.setString("title", title);
		List<WikiPage> ret = q.list();

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

/**
 * Prepare the given Query object, applying cache settings and/or
 * a transaction timeout.
 * @param queryObject the Query object to prepare
 * @see #setCacheQueries
 * @see #setQueryCacheRegion
 * @see SessionFactoryUtils#applyTransactionTimeout
 */
protected void prepareQuery(Query queryObject) {
	if (isCacheQueries()) {
		queryObject.setCacheable(true);
		if (getQueryCacheRegion() != null) {
			queryObject.setCacheRegion(getQueryCacheRegion());
		}
	}
	if (getFetchSize() > 0) {
		queryObject.setFetchSize(getFetchSize());
	}
	if (getMaxResults() > 0) {
		queryObject.setMaxResults(getMaxResults());
	}
	SessionFactoryUtils.applyTransactionTimeout(queryObject, getSessionFactory());
}
 

@Override
public SbiMetaTableColumn loadTableColumnByNameAndTable(Session session, String name, Integer tableId) throws EMFUserError {
	logger.debug("IN");

	SbiMetaTableColumn toReturn = null;
	Session tmpSession = session;

	try {
		String hql = " from SbiMetaTableColumn c where c.name = ? and c.sbiMetaTable.tableId = ? ";
		Query aQuery = tmpSession.createQuery(hql);
		aQuery.setString(0, name);
		aQuery.setInteger(1, tableId);
		toReturn = (SbiMetaTableColumn) aQuery.uniqueResult();

		if (toReturn == null)
			return null;
	} catch (HibernateException he) {
		logException(he);
		throw new HibernateException(he);
	} finally {
		logger.debug("OUT");
	}
	return toReturn;
}
 

@Override
@Transactional(propagation = Propagation.REQUIRED)
public Line getAllFileListForPie(String ip) {
    Session session = getSession();
    Query query = session.createQuery(" from DownloadFileRecord  f where f.src_ip='"+ip+"'  order by f.accessCount desc");
    query.setMaxResults(10);
    List<DownloadFileRecord> list=query.list();
    Line sc = new Line(ip);
    sc.setName(ip);
    for(DownloadFileRecord downloadFileRecord:list){
        Fdfs_file f=getFileByFileId(downloadFileRecord.getFileId().substring(1));
        sc.getData().add(new Object[]{f.getFile_name(),downloadFileRecord.getAccessCount()});

    }
    return sc;
}
 

@SuppressWarnings("unchecked")
private void deleteProcessInstanceJobs(Context context,ProcessInstance processInstance,boolean isRoot) {
	Session session=context.getSession();
	SchedulerService schedulerService=(SchedulerService)context.getApplicationContext().getBean(SchedulerService.BEAN_ID);
	String hql="from "+Task.class.getName()+" where ";
	if(isRoot){
		hql+="rootProcessInstanceId=:rootProcessInstanceId";
	}else{
		hql+="processInstanceId=:processInstanceId";			
	}
	Query query=session.createQuery(hql);
	if(isRoot){
		query.setLong("rootProcessInstanceId", processInstance.getRootId());
	}else{
		query.setLong("processInstanceId", processInstance.getId());
	}
	List<Task> tasks=query.list();
	for(Task t:tasks){
		if(t.getType().equals(TaskType.Participative)){
			hql="delete "+TaskParticipator.class.getName()+" where taskId=:taskId";
			session.createQuery(hql).setLong("taskId", t.getId()).executeUpdate();				
		}
		schedulerService.removeReminderJob(t);
		session.delete(t);
	}
}
 

@SuppressWarnings("unchecked")
@Override
public Map<Long, Long> getTrainingCountForCrisis(Long crisisID) {

	Map<Long, Long> countMap = new HashMap<Long, Long>();
	try {
		Session session = getCurrentSession();
		Query query = session.createSQLQuery(NativeQueryUtil.TRAINING_COUNT_FOR_CRISIS);
		query.setParameter("crisisID", crisisID.intValue());
		
		List<Object[]> rows = query.list();
		for (Object[] row : rows) {
			countMap.put(((BigInteger)row[0]).longValue(), ((BigInteger)row[1]).longValue());
		}
			
	} catch (Exception e) {
		logger.error("exception", e);
	}
	
	return countMap;
}
 

@Override
public String getNextVersionNumber(Session session, NodeDocument nDoc, NodeDocumentVersion nDocVer, int increment) {
	String versionNumber = nDocVer.getName();
	int nextVerNumber = Integer.parseInt(versionNumber);
	Query q = session.createQuery(qs);
	NodeDocumentVersion ndv = null;

	do {
		nextVerNumber++;
		q.setString("parent", nDoc.getUuid());
		q.setString("name", String.valueOf(nextVerNumber));
		ndv = (NodeDocumentVersion) q.setMaxResults(1).uniqueResult();
	} while (ndv != null);

	return String.format(Config.VERSION_NUMERATION_FORMAT, nextVerNumber);
}
 

int delete(DBTransaction trx, String query, Object[] values, Type[] types) {
    int deleted = 0;

    if (trx.isRolledBack() || trx.isCommitted()) { // some program bug
        throw new DBRuntimeException("cannot delete in a transaction that is rolledback or committed " + values);
    }
    try {
        // old: deleted = getSession().delete(query, values, types);
        Session si = getSession();
        Query qu = si.createQuery(query);
        qu.setParameters(values, types);
        List foundToDel = qu.list();
        deleted = foundToDel.size();
        for (int i = 0; i < deleted; i++) {
            si.delete(foundToDel.get(i));
        }

        if (log.isDebugEnabled()) {
            logQuery("delete (trans " + trx.hashCode() + ")", values, types, query);
        }
    } catch (HibernateException e) { // we have some error
        trx.setErrorAndRollback(e);
        throw new DBRuntimeException("Could not delete object: " + values, e);
    }
    return deleted;
}
 

/**
 * Find by pk
 */
@SuppressWarnings("unchecked")
public List<Omr> findAll() throws DatabaseException {
	log.debug("findAll()");
	String qs = "from Omr om order by om.name";
	Session session = null;

	try {
		session = HibernateUtil.getSessionFactory().openSession();
		Query q = session.createQuery(qs);
		List<Omr> ret = q.list();
		initializeOMR(ret);
		log.debug("findAll: {}", ret);
		return ret;
	} catch (HibernateException e) {
		throw new DatabaseException(e.getMessage(), e);
	} finally {
		HibernateUtil.close(session);
	}
}
 

/**
 * Deletes the corresponding {@link ScheduledMessage}s from ScheduledMessages table in one shot.
 *
 * @param messageIdsToDelete List of {@link ScheduledMessage} Ids
 */
@Transactional
@SelectDataSource(storage = Storage.SCHEDULER)
public int deleteInBatch(List<SmIdAndTaskIdPair> messageIdsToDelete) {
    StringBuilder queryBuilder = new StringBuilder();
    queryBuilder.append("delete from ScheduledMessage where (stateMachineId,taskId) in (");
    messageIdsToDelete.forEach(smIdAndTaskIdPair -> {
        queryBuilder.append("(\'")
                .append(smIdAndTaskIdPair.getSmId())
                .append("\',\'").append(smIdAndTaskIdPair.getTaskId())
                .append("\'),");
    });
    queryBuilder.setCharAt(queryBuilder.length() - 1, ')');
    final Query deleteQuery = currentSession().createQuery(queryBuilder.toString());
    return deleteQuery.executeUpdate();
}
 

/**
 * Find by pk
 */
@SuppressWarnings("unchecked")
public static List<Profile> findAll(boolean filterByActive) throws DatabaseException {
	log.debug("findAll()");
	String qs = "from Profile prf " + (filterByActive ? "where prf.active=:active" : "");
	Session session = null;

	try {
		session = HibernateUtil.getSessionFactory().openSession();
		Query q = session.createQuery(qs);

		if (filterByActive) {
			q.setBoolean("active", true);
		}

		List<Profile> ret = q.list();
		log.debug("findAll: {}", ret);
		return ret;
	} catch (HibernateException e) {
		throw new DatabaseException(e.getMessage(), e);
	} finally {
		HibernateUtil.close(session);
	}
}
 

/**
 * Get dashboard stats
 */
@SuppressWarnings("unchecked")
public Dashboard findByPk(int dsId) throws DatabaseException {
	log.debug("findByPk({})", dsId);
	String qs = "from Dashboard db where db.id=:id";
	Session session = null;

	try {
		session = HibernateUtil.getSessionFactory().openSession();
		Query q = session.createQuery(qs);
		q.setInteger("id", dsId);
		List<Dashboard> results = q.list(); // uniqueResult
		Dashboard ret = null;

		if (results.size() == 1) {
			ret = results.get(0);
		}

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

/**
 * 
 * @param hql传入的hql语句
 * @param pageCode当前页
 * @param pageSize每页显示大小
 * @return
 */
public List doSplitPage(final String hql,final int pageCode,final int pageSize){
    //调用模板的execute方法，参数是实现了HibernateCallback接口的匿名类，
    return (List) this.getHibernateTemplate().execute(new HibernateCallback(){
        //重写其doInHibernate方法返回一个object对象，
        public Object doInHibernate(Session session)
                throws HibernateException, SQLException {
            //创建query对象
            Query query=session.createQuery(hql);
            //返回其执行了分布方法的list
            return query.setFirstResult((pageCode-1)*pageSize).setMaxResults(pageSize).list();
             
        }
         
    });
     
}
 

public PrivateForum getPrivateForumByOwnerArea(final String owner, final Area area) {

      if (owner == null || area == null) {
          throw new IllegalArgumentException("Null Argument");
      }

      log.debug("getForumByOwnerArea executing with owner: " + owner + " and area:" + area);

      HibernateCallback<PrivateForum> hcb = session -> {
          Query q = session.getNamedQuery(QUERY_BY_FORUM_OWNER_AREA);
          q.setString("owner", owner);
          q.setParameter("area", area);
          return (PrivateForum) q.uniqueResult();
      };

      return getHibernateTemplate().execute(hcb);
    }
 

@Override
public List<?> find(final String queryString, final Object... values) throws DataAccessException {
	return executeWithNativeSession(new HibernateCallback<List<?>>() {
		@Override
		public List<?> doInHibernate(Session session) throws HibernateException {
			Query queryObject = session.createQuery(queryString);
			prepareQuery(queryObject);
			if (values != null) {
				for (int i = 0; i < values.length; i++) {
					queryObject.setParameter(i, values[i]);
				}
			}
			return queryObject.list();
		}
	});
}
 

@Override
   public Integer getCountRoleForOrg(Integer[] roleIds, Integer orgId, String searchPhrase) {
StringBuilder queryTextBuilder = new StringBuilder(RoleDAO.COUNT_ROLE_FOR_ORG);
if (!StringUtils.isBlank(searchPhrase)) {
    String[] tokens = searchPhrase.trim().split("\\s+");
    for (String token : tokens) {
	queryTextBuilder.append(" AND (uor.userOrganisation.user.firstName LIKE '%").append(token)
		.append("%' OR uor.userOrganisation.user.lastName LIKE '%").append(token)
		.append("%' OR uor.userOrganisation.user.login LIKE '%").append(token).append("%')");
    }
}

Query query = getSession().createQuery(queryTextBuilder.toString());
query.setParameterList("roleIds", roleIds);
query.setInteger("orgId", orgId.intValue());
Object value = query.uniqueResult();
return new Integer(((Number) value).intValue());
   }
 

public UnreadStatus findUnreadStatusByUserId(final Long topicId, final Long messageId, final String userId){
	if (messageId == null || topicId == null || userId == null) {
        log.error("findUnreadStatusByUserId failed with topicId: " + topicId + ", messageId: " + messageId
        		+ ", userId: " + userId);
        throw new IllegalArgumentException("Null Argument");
    }

    log.debug("findUnreadStatus executing with topicId: " + topicId + ", messageId: " + messageId);

    HibernateCallback<UnreadStatus> hcb = session -> {
        Query q = session.getNamedQuery(QUERY_UNREAD_STATUS);
        q.setParameter("topicId", topicId, LongType.INSTANCE);
        q.setParameter("messageId", messageId, LongType.INSTANCE);
        q.setParameter("userId", userId, StringType.INSTANCE);
        return (UnreadStatus) q.uniqueResult();
    };

    return getHibernateTemplate().execute(hcb);
}
 

/**
 * 获取分页记录HqlQuery
 * 
 * @param cq
 * @param isOffset
 * @return
 */
@SuppressWarnings("unchecked")
public PageList getPageList(final HqlQuery hqlQuery,
		final boolean needParameter) {

	Query query = getSession().createQuery(hqlQuery.getQueryString());
	if (needParameter) {
		query.setParameters(hqlQuery.getParam(),
				(Type[]) hqlQuery.getTypes());
	}
	int allCounts = query.list().size();
	int curPageNO = hqlQuery.getCurPage();
	int offset = PagerUtil.getOffset(allCounts, curPageNO,
			hqlQuery.getPageSize());
	String toolBar = PagerUtil.getBar(hqlQuery.getMyaction(), allCounts,
			curPageNO, hqlQuery.getPageSize(), hqlQuery.getMap());
	query.setFirstResult(offset);
	query.setMaxResults(hqlQuery.getPageSize());
	return new PageList(query.list(), toolBar, offset, curPageNO, allCounts);
}
 

@Override
public ProgressThread loadProgressThreadById(Integer progressThreadId) throws EMFUserError {
	logger.debug("IN");
	ProgressThread toReturn = null;

	Session aSession = null;
	Transaction tx = null;

	try {
		aSession = getSession();
		tx = aSession.beginTransaction();

		Query hibPT = aSession.createQuery("from SbiProgressThread h where h.progressThreadId = ?");
		hibPT.setInteger(0, progressThreadId);
		SbiProgressThread sbiProgressThread = (SbiProgressThread) hibPT.uniqueResult();
		if (sbiProgressThread != null) {
			toReturn = toProgressThread(sbiProgressThread);
		}
		tx.commit();

	} catch (HibernateException he) {
		logger.error("Error while loading Progress Thread with progresThreadId", he);
		if (tx != null)
			tx.rollback();
		throw new EMFUserError(EMFErrorSeverity.ERROR, 100);
	} finally {
		if (aSession != null) {
			if (aSession.isOpen())
				aSession.close();
			// logger.debug("OUT");
		}
	}
	// logger.debug("OUT");
	return toReturn;
}
 

/**
 * 
 * @param typeUuid
 * @return the PermissionLevel for the given typeUuid. Returns null if no
 * PermissionLevel found.
 */
private PermissionLevel getDefaultPermissionLevel(final String typeUuid){

 if (typeUuid == null) {      
  throw new IllegalArgumentException("Null Argument");
 }

 if (log.isDebugEnabled()){
  log.debug("getDefaultPermissionLevel executing with typeUuid: " + typeUuid);
 }

 PermissionLevel level = null;

 if(defaultPermissionsMap != null && defaultPermissionsMap.containsKey(typeUuid)) {
  // check to see if it is already in the map that was created at startup
  level =  ((PermissionLevel)defaultPermissionsMap.get(typeUuid)).clone();
  if (log.isDebugEnabled()) log.debug("got Default PermissionLevel from defaultPermissionsMap as " + level);
 
 } else {
  // retrieve it from the table
  HibernateCallback<PermissionLevel> hcb = session -> {
            Query q = session.getNamedQuery(QUERY_BY_TYPE_UUID);
            q.setParameter("typeUuid", typeUuid);

            return (PermissionLevel) q.uniqueResult();
        };

  level = getHibernateTemplate().execute(hcb);
  if (log.isDebugEnabled()) log.debug("Returned Permission Level from query was "+level);
 }

 return level;

}
 

public List<DomainAttribute> findByDomainNameAndFootprint(String domainName, int footprintId) {
    String hql = "from DomainAttribute where domain.name = :domainName and footprint_id = :footprintId";
    Query query = HibernateUtil.getSession().createQuery(hql);
    query.setParameter("domainName", domainName);
    query.setParameter("footprintId", footprintId);
    return query.list();
}
 

public int countByChannelId(Integer channelId) {
	String hql = "select count(*) from CmsTopic bean"
			+ " where bean.channel.id=:channelId";
	Query query = getSession().createQuery(hql);
	query.setParameter("channelId", channelId);
	return ((Number) query.iterate().next()).hashCode();
}
 

public List getUsernamesInGradebook(final Gradebook gradebook) {
	if (gradebook == null) {
        throw new IllegalArgumentException("Null gradebook passed to getUsernamesInGradebook");
       }

	HibernateCallback hcb = session -> {
         Query q = session.getNamedQuery("findUsernamesInGradebook");
         q.setParameter("gradebook", gradebook);
         return q.list();
       };
        
    return (List) getHibernateTemplate().execute(hcb);  
}
 

@SuppressWarnings("unchecked")
private <K> List<K> getQueryList(String queryName, Map<String, Object> queryParameters) {
    Query query = getNamedQuery(queryName);
    for (String key : queryParameters.keySet()) {
        /** TODO: REVIEW PUBLISH PERFORMANCE (used general solution when parameter is collection): bb/11.03.2012 **/
        if (queryParameters.get(key) instanceof Collection) {
            if (!((Collection<T>) queryParameters.get(key)).isEmpty()) {
                query.setParameterList(key, ((Collection<T>) queryParameters.get(key)));
            }
        } else {
            query.setParameter(key, queryParameters.get(key));
        }
    }
    return query.list();
}
 

public void delByPersona(PersonaEntity entity) {
	EntityManager manager = getManager();
	Session delegate = (Session) manager.getDelegate();
	Query query = delegate.createQuery("DELETE from LobbyEntrantEntity obj WHERE obj.persona = :persona ");
	query.setParameter("persona", entity);
	query.executeUpdate();
}