/**
 * 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;
}
 

@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();
}
 

@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;
}
 

@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);
}
 

@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());
   }
 

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);
	}
}
 

/**
 * 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();
             
        }
         
    });
     
}
 

@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);
    }
}
 

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);
}
 

@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();
		}
	});
}
 

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);
    }
 

/**
 * 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();
}
 

/**
 * 获取分页记录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);
}
 

@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;
}
 

@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);
	}
}
 

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;
}
 

/**
 * 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);
	}
}
 

/**
   * 
   * @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);
}
 

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);
}
 

@Before
public void setup(){
    factory = mock(SessionFactory.class);
    session = mock(Session.class);
    when(factory.openSession()).thenReturn(session);
    when(session.beginTransaction()).thenReturn(mock(Transaction.class));
    when(session.createQuery(Mockito.anyString())).thenReturn(mock(Query.class));
}
 

public List<IpAddress> findByHostAndFootprint(String hostName, int footprintId) {
    String hql = "from IpAddress where hosts.name = :hostName and footprint.id = :footprintId";
    Query query = HibernateUtil.getSession().createQuery(hql);
    query.setParameter("hostName", hostName);
    query.setParameter("footprintId", footprintId);
    return query.list();
}
 

public JobResult loadJobResult(final JobId jobId) {
    return executeReadOnlyTransaction((SessionWork<JobResult>) session -> {
        long id = jobId(jobId);

        JobData job = session.get(JobData.class, id);

        if (job == null) {
            return null;
        }

        Query query = session.getNamedQuery("loadJobResult").setParameter("job", job);

        return loadJobResult(session, query, job, jobId);
    });
}
 

@Override
public void unregisterEvent(Integer id, String user) throws EMFUserError {
	Session aSession = null;
	Transaction tx = null;
	String hql = null;
	Query hqlQuery = null;

	try {
		aSession = getSession();
		tx = aSession.beginTransaction();
		SbiEvents hibEvent = new SbiEvents(id, user);
		aSession.delete(hibEvent);
		tx.commit();
	} catch (HibernateException he) {
		logException(he);
		if (tx != null)
			tx.rollback();
		throw new EMFUserError(EMFErrorSeverity.ERROR, 100);
	} catch (Exception ex) {
		logException(ex);
		if (tx != null)
			tx.rollback();
		throw new EMFUserError(EMFErrorSeverity.ERROR, 100);
	} finally {
		if (aSession != null) {
			if (aSession.isOpen())
				aSession.close();
		}
	}
}
 

@Retryable
@Transactional(propagation = Propagation.REQUIRES_NEW)
public int updateLastNotifiedDateByIds(Set<Long> ids, Date date) {
    Query query = genericDao.getNamedQuery(Subscription.UPDATE_LASTNOTIFIEDDATE_BY_IDS);
    query.setParameterList("ids", ids);
    query.setTimestamp("date", date);
    return query.executeUpdate();
}
 

@SuppressWarnings("unchecked")
private List<TaskResult> loadTaskResultAllAttempts(Session session, TaskId taskId) {
    DBTaskId dbTaskId = taskId(taskId);

    TaskData task = session.load(TaskData.class, dbTaskId);
    Query query = session.getNamedQuery("loadTasksResultByTaskAsc").setParameter("task", task);

    return ((List<TaskResultData>) query.list()).stream()
                                                .map(resultData -> resultData.toTaskResult(taskId))
                                                .collect(Collectors.toList());
}
 

/**
 * 为查询对象设定参数，注意：如果是JPA，则索引位要加一
 * @param query
 * @param params
 */
protected void setParameter(Query query, Object[] params){
	if(params != null){
		for (int i = 0; i < params.length; i++) {
			query.setParameter(i, params[i]);
		}
	}
}