Java Code Examples for org.hibernate.Query#list()

@Override
public List<SbiTag> loadTagsByDatasetId(SbiDataSetId dsId) {
	logger.debug("IN");
	Session session = null;
	List<SbiTag> toReturn = new ArrayList<>();
	try {
		session = getSession();
		List<Integer> tagIds = session.createQuery(
				"select dst.dsTagId.tagId from SbiDatasetTag dst where dst.dsTagId.dsId = :dsId and dst.dsTagId.versionNum = :versionNum and dst.dsTagId.organization = :organization")
				.setInteger("dsId", dsId.getDsId()).setInteger("versionNum", dsId.getVersionNum()).setString("organization", dsId.getOrganization()).list();
		if (!tagIds.isEmpty()) {
			Query query = session.createQuery("from SbiTag t where t.tagId in (:tagIds)");
			query.setParameterList("tagIds", tagIds);
			toReturn = query.list();
		}
	} catch (HibernateException e) {
		logException(e);
		throw new RuntimeException(e);
	} finally {
		if (session != null && session.isOpen())
			session.close();
	}
	logger.debug("OUT");
	return toReturn;
}
 

private List<PrivacyRecord> getViewableStateList(final String contextId, final Boolean viewable, final String recordType, final List userIds)
{
	if(contextId == null || viewable == null || recordType == null || userIds == null)
	{
    throw new IllegalArgumentException("Null Argument in getViewableStateList");
	}
	
  HibernateCallback<List<PrivacyRecord>> hcb = session -> {
    Query q = session.getNamedQuery(QUERY_BY_CONTEXT_VIEWABLE_TYPE_IDLIST);
    q.setString(CONTEXT_ID, contextId);
    q.setBoolean(VIEWABLE, viewable);
    q.setString(RECORD_TYPE, recordType);
    q.setParameterList("userIds", userIds);
    return q.list();
  };

  return getHibernateTemplate().execute(hcb);
}
 

@Override
public ArrayList<BIObjDataSet> getObjectNotDetailDataset(Integer objectId, Session currSession) throws EMFUserError {
	logger.debug("IN");
	ArrayList<BIObjDataSet> toReturn = new ArrayList<BIObjDataSet>();

	String hql = "from SbiObjDataSet s where s.sbiObject.biobjId = " + objectId + " AND (isDetail =" + false + " OR isDetail is null)";
	Query hqlQuery = currSession.createQuery(hql);

	List hibObjectPars = hqlQuery.list();

	Iterator it = hibObjectPars.iterator();
	int count = 1;
	while (it.hasNext()) {
		BIObjDataSet aBIObjectDataSet = toBIObjDataSet((SbiObjDataSet) it.next());
		toReturn.add(aBIObjectDataSet);
	}

	logger.debug("OUT");

	return toReturn;
}
 

@Override
public void init(SourceBean config, Session hibernateSession) {
	logger.debug("IN");
	try {
		String hql = "from SbiDataSource";
		Query hqlQuery = hibernateSession.createQuery(hql);
		List dataSources = hqlQuery.list();
		if (dataSources.isEmpty()) {
			logger.info("DataSource table is empty. Starting populating DataSources...");
			writeDefaultDatasources(hibernateSession);
		} else {
			logger.debug("DataSource table is already populated. No operations needed.");
		}
	} catch (Throwable t) {
		throw new SpagoBIRuntimeException("An unexpected error occured while initializing DataSources", t);
	} finally {
		logger.debug("OUT");
	}
}
 

public List<UserStatistics> findAuthoredStatsForStudentByForumId(final String studentId, final Long topicId) {
	if (log.isDebugEnabled()) log.debug("findAuthoredStatsForStudentByForumId()");

	HibernateCallback<List<Object[]>> hcb = session -> {
        Query q = session.getNamedQuery("findAuthoredStatsForStudentByForumId");
        q.setParameter("forumId", topicId, LongType.INSTANCE);
        q.setParameter("userId", studentId, StringType.INSTANCE);
        return q.list();
    };
	List<UserStatistics> returnList = new ArrayList<UserStatistics>();
	List<Object[]> results = getHibernateTemplate().execute(hcb);
	for(Object[] result : results){
		UserStatistics stat = new UserStatistics((String) result[0], (String) result[1], (Date) result[2], (String) result[3], 
				((Integer) result[4]).toString(), ((Integer) result[5]).toString(), ((Integer) result[6]).toString(), studentId);
		returnList.add(stat);
	}
	return returnList;
}
 

public List getLastSubmittedAssessmentGradingList(final Long publishedAssessmentId) {

        final HibernateCallback<List<AssessmentGradingData>> hcb = session -> {
            Query q = session.createQuery(
                    "select a from AssessmentGradingData a left join fetch a.assessmentGradingAttachmentSet " +
                            "where a.publishedAssessmentId = :id and a.forGrade = :forgrade order by a.agentId asc, a.submittedDate desc");
            q.setLong("id", publishedAssessmentId);
            q.setBoolean("forgrade", true);
            return q.list();
        };
        List<AssessmentGradingData> assessmentGradings = getHibernateTemplate().execute(hcb);

        return new ArrayList<>(assessmentGradings.stream()
                .collect(Collectors.toMap(AssessmentGradingData::getAgentId, p -> p, (p, q) -> p))
                .values());
    }
 

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

public List<QuestionPoolData> getAllPoolsForTransfer(final List<Long> selectedPoolIds) {  
 final HibernateCallback<List> hcb = session -> {
        Query q = session.createQuery("FROM QuestionPoolData a WHERE a.questionPoolId IN (:ids)");
        q. setParameterList("ids", selectedPoolIds);
        return q.list();
    };
 return getHibernateTemplate().execute(hcb);
}
 

@Override
public List getListExecutionNotes(Integer biobjId, String execIdentif) throws Exception {
	List lstObjNote = new ArrayList();
	// ObjNote objNote = null;
	Session aSession = null;
	Transaction tx = null;
	try {
		aSession = getSession();
		tx = aSession.beginTransaction();
		// String hql = "from SbiObjNotes son where son.sbiObject.biobjId = " + biobjId +
		// " and son.execReq = '"+execIdentif+"'";

		String hql = "from SbiObjNotes son where son.sbiObject.biobjId = ? and son.execReq = ?";

		Query query = aSession.createQuery(hql);
		query.setInteger(0, biobjId.intValue());
		query.setString(1, execIdentif);

		/*
		 * SbiObjNotes hibObjNote = (SbiObjNotes)query.uniqueResult(); if(hibObjNote!=null) { objNote = toObjNote(hibObjNote); }
		 */
		List result = query.list();
		Iterator it = result.iterator();
		while (it.hasNext()) {
			lstObjNote.add(toObjNote((SbiObjNotes) it.next()));
		}
		tx.commit();
	} catch (HibernateException he) {
		logException(he);
		if (tx != null)
			tx.rollback();
		throw new EMFUserError(EMFErrorSeverity.ERROR, 100);
	} finally {
		if (aSession != null) {
			if (aSession.isOpen())
				aSession.close();
		}
	}
	return lstObjNote;
}
 

@SuppressWarnings("unchecked")
public List<ActionInfoRow> generateTaggedActionsInfo(AggregateReportParameters params) {
    String queryString = "select new com.exactpro.sf.embedded.statistics.storage.reporting.ActionInfoRow( "
            + "AR.rank, AR.description, AR.failReason, A.name, M.name, S.name, AR.tag, AR.status, AR.hash) "
            + "from ActionRun as AR "
            + "join AR.tcRun as TCR "
            + "left join AR.action as A "
            + "left join AR.service as S "
            + "left join AR.msgType as M "

            + "where TCR.id = :tcrId and AR.tag is not null "
            + "order by AR.rank";

    Session session = null;

    try {
        session = sessionFactory.openSession();
        Query query = session.createQuery(queryString);
        query.setParameter("tcrId", params.getTestCaseRunId());
        List<ActionInfoRow> list = query.list();
        list.sort((o1, o2) -> o1.getTag().compareTo(o2.getTag()));

        //TODO: Remove escaping after release 3.1, because escaping is executed on save data
        return escapeDesription(list);
    } finally {
        if(session != null) {
            session.close();
        }
    }
}
 

public List<Object[]> findAuthoredMessageCountForAllStudents() {
	if (log.isDebugEnabled()) log.debug("findAuthoredMessageCountForAllStudents executing");
	
    HibernateCallback<List<Object[]>> hcb = session -> {
        Query q = session.getNamedQuery("findAuthoredMessageCountForAllStudents");
        q.setString("contextId", getContextId());
        return q.list();
    };

    return getHibernateTemplate().execute(hcb);
}
 

/**
    * @see org.lamsfoundation.lams.tool.dao.IToolSessionDAO#getToolSessionsByLesson(org.lamsfoundation.lams.lesson.Lesson)
    */
   @Override
   public List getToolSessionsByLesson(final Lesson lesson) {

Query query = getSessionFactory().getCurrentSession().createQuery(ToolSessionDAO.LOAD_TOOL_SESSION_BY_LESSON);
query.setParameter("lesson", lesson);
return query.list();

   }
 

public Set<ItemGradingData> getItemGradingSet(final Long assessmentGradingId) {

        final HibernateCallback<List<ItemGradingData>> hcb = session -> {
            Query q = session.createQuery("from ItemGradingData i where i.assessmentGradingId = :id");
            q.setLong("id", assessmentGradingId);
            return q.list();
        };
        List<ItemGradingData> itemGradings = getHibernateTemplate().execute(hcb);

        return new HashSet<>(itemGradings);
    }
 

/**
 * Load all maps.
 *
 * @return the list
 *
 * @throws EMFUserError
 *             the EMF user error
 *
 * @see it.eng.spagobi.geo.bo.dao.IEngineDAO#loadAllEngines()
 */
@Override
public List loadAllMaps() throws EMFUserError {
	Session tmpSession = null;
	Transaction tx = null;
	List realResult = new ArrayList();
	try {
		tmpSession = getSession();
		tx = tmpSession.beginTransaction();

		Query hibQuery = tmpSession.createQuery(" from SbiGeoMaps");

		List hibList = hibQuery.list();
		Iterator it = hibList.iterator();
		while (it.hasNext()) {
			SbiGeoMaps hibMap = (SbiGeoMaps) it.next();
			if (hibMap != null) {
				GeoMap biMap = hibMap.toGeoMap();
				realResult.add(biMap);
			}
		}
		tx.commit();
	} catch (HibernateException he) {
		logException(he);

		if (tx != null)
			tx.rollback();

		throw new EMFUserError(EMFErrorSeverity.ERROR, 100);

	} finally {

		if (tmpSession != null) {
			if (tmpSession.isOpen())
				tmpSession.close();
		}

	}
	return realResult;
}
 

public SolutionGridModel(String solutionIdsStr, SubjectArea sa, org.hibernate.Session hibSession, TimetableGridContext context) {
	super(sResourceTypeSubjectArea, sa.getUniqueId().longValue());
	setName(sa.getSubjectAreaAbbreviation());
	setFirstDay(context.getFirstDay());
	Query q = hibSession.createQuery("select distinct a from Assignment as a inner join a.clazz.schedulingSubpart.instrOfferingConfig.instructionalOffering.courseOfferings as o inner join o.subjectArea as sa where " +
			"a.solution.uniqueId in ("+solutionIdsStr+") and sa.uniqueId=:resourceId and " +
			"o.isControl=true");
	q.setCacheable(true);
	q.setLong("resourceId", sa.getUniqueId().longValue());
	List a = q.list();
	setSize(a.size());
	init(a,hibSession,context);
}
 

public Map<Long, List<Long>> getAverageAssessmentGradingByPublishedItem(final Long publishedAssessmentId) {
    Map<Long, List<Long>> h = new HashMap<>();

    final HibernateCallback<List<AssessmentGradingData>> hcb = session -> {
        Query q = session.createQuery(
                "select new AssessmentGradingData(" +
                        " a.assessmentGradingId, p.itemId, " +
                        " a.agentId, a.finalScore, a.submittedDate) " +
                        " from ItemGradingData i, AssessmentGradingData a," +
                        " PublishedItemData p where " +
                        " i.assessmentGradingId = a.assessmentGradingId and i.publishedItemId = p.itemId and " +
                        " a.publishedAssessmentId = :id and a.status > :status" +
                        " order by a.agentId asc, a.submittedDate desc"
        );
        q.setLong("id", publishedAssessmentId);
        q.setInteger("status", AssessmentGradingData.REMOVED);
        return q.list();
    };

    List<AssessmentGradingData> assessmentGradings = getHibernateTemplate().execute(hcb);

    String currentAgent = "";
    Date submittedDate = null;
    for (int i = 0; i < assessmentGradings.size(); i++) {
        AssessmentGradingData g = assessmentGradings.get(i);
        Long itemId = g.getPublishedItemId();
        Long gradingId = g.getAssessmentGradingId();
        if (i == 0) {
            currentAgent = g.getAgentId();
            submittedDate = g.getSubmittedDate();
        }
        if (currentAgent.equals(g.getAgentId())
                && ((submittedDate == null && g.getSubmittedDate() == null)
                || (submittedDate != null && submittedDate.equals(g.getSubmittedDate())))) {
            List<Long> o = h.get(itemId);
            if (o != null) {
                o.add(gradingId);
            } else {
                List<Long> gradingIds = new ArrayList<>();
                gradingIds.add(gradingId);
                h.put(itemId, gradingIds);
            }
        }
        if (!currentAgent.equals(g.getAgentId())) {
            currentAgent = g.getAgentId();
            submittedDate = g.getSubmittedDate();
        }
    }
    return h;
}
 

@Override
public Optional<DetailedEvent> getDetailedEventById(final long id)
{
	if (!statMan.isDisplayDetailedEvents())
	{
		return Optional.empty();
	}

	HibernateCallback<Optional<DetailedEvent>> hcb = session ->
	{
		Query q = session.createQuery(HQL_BY_ID);
		q.setLong("id", id);
		if (log.isDebugEnabled())
		{
			log.debug("getDetailedEvents(): " + q.getQueryString());
		}

		List<Object[]> records = q.list();
		if (records.size() > 1)
		{
			log.error("getDetailedEvents(): query for id " + id + " returned more than one result.");
			return Optional.empty();
		}
		else if (records.isEmpty())
		{
			return Optional.empty();
		}

		Object[] record = records.get(0);
		String userID = (String) record[1];
		String siteID = (String) record[5];
		// Only return the event if the current user is is allowed to track, and the target user is allowed to be tracked in the site
		if (statsAuthz.canCurrentUserTrackInSite(siteID) && statsAuthz.canUserBeTracked(siteID, userID))
		{
			DetailedEvent de = new DetailedEventImpl();
			de.setId((Long) record[0]);
			de.setUserId(userID);
			de.setEventDate((Date) record[2]);
			de.setEventId((String) record[3]);
			de.setEventRef((String) record[4]);
			de.setSiteId(siteID);

			// do not return if anonymous
			if (!regServ.getAnonymousEventIds().contains(de.getEventId()))
			{
				return Optional.of(de);
			}
		}

		return Optional.empty();
	};

	return getHibernateTemplate().execute(hcb);

}
 

public List getForumByTypeAndContext(final String typeUuid) {

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

     HibernateCallback<List> hcb = session -> {
         Query q = session.getNamedQuery(QUERY_BY_TYPE_AND_CONTEXT);
         q.setString("typeUuid", typeUuid);
         q.setString("contextId", getContextId());
         return q.list();
     };

      BaseForum tempForum = null;
      Set resultSet = new HashSet();
      List temp = getHibernateTemplate().execute(hcb);
            
      for (Iterator i = temp.iterator(); i.hasNext();)
      {
        Object[] results = (Object[]) i.next();        
            
        if (results != null) {
          if (results[0] instanceof BaseForum) {
            tempForum = (BaseForum)results[0];
            tempForum.setArea((Area)results[1]);            
          } else {
            tempForum = (BaseForum)results[1];
            tempForum.setArea((Area)results[0]);
          }
          resultSet.add(tempForum);
        }
      }
      
      List resultList = Util.setToList(resultSet);
      Collections.sort(resultList, FORUM_SORT_INDEX_CREATED_DATE_COMPARATOR_DESC);
      
      // Now that the list is sorted, lets index the forums
      int sort_index = 1;
      for(Iterator i = resultList.iterator(); i.hasNext(); ) {
         tempForum = (BaseForum)i.next();
         
         tempForum.setSortIndex(Integer.valueOf(sort_index++));
      }
      
      return resultList;      
    }
 

@Override
public Map<String, Map<Integer, List<Snapshot>>> getSnapshotsBySchedulation(String schedulationName, boolean collate, boolean loadContent)
		throws EMFUserError {
	Map<String, Map<Integer, List<Snapshot>>> snaps = new HashMap<String, Map<Integer, List<Snapshot>>>();
	List<List<Snapshot>> documentLIstLIst = new ArrayList<List<Snapshot>>();// supporting list that is the copy of the lists in snaps. it is used to
																			// fascicolate

	Session aSession = null;

	try {
		aSession = getSession();

		String hql = "";
		if (loadContent) {
			hql = "from SbiSnapshots ss where ss.scheduler = ? order by ss.sbiObject.biobjId";
		} else {
			hql = "select distinct snap.snapId, snap.name, snap.description, snap.creationDate, snap.sbiObject, "
					+ "snap.schedulation, snap.schedulationStartDate, snap.contentType " + "from SbiSnapshots as snap"
					+ " where snap.scheduler = ?  order by snap.sbiObject.biobjId";
		}

		Query query = aSession.createQuery(hql);
		query.setString(0, schedulationName);

		List hibSnaps = query.list();
		Iterator iterHibSnaps = hibSnaps.iterator();

		while (iterHibSnaps.hasNext()) {
			// the list is sorted by schedulation date and grouped by document id
			// we are interested to get oldest schedulation for each document
			// with same schedulation name
			Snapshot snap = null;
			if (loadContent) {
				SbiSnapshots hibSnap = (SbiSnapshots) iterHibSnaps.next();
				snap = toSnapshot(hibSnap);
			} else {
				Object[] snapValues = (Object[]) iterHibSnaps.next();
				snap = new Snapshot();
				if (snapValues[0] != null) {
					snap.setId((Integer) snapValues[0]);
				}
				if (snapValues[1] != null) {
					snap.setName((String) snapValues[1]);
				}
				if (snapValues[2] != null) {
					snap.setDescription((String) snapValues[2]);
				}
				if (snapValues[3] != null) {
					snap.setDateCreation(((Date) snapValues[3]));
					snap.setTime(DATE_FORMATTER.format((Date) snapValues[3]));
				}
				if (snapValues[4] != null) {
					snap.setBiobjId(((SbiObjects) snapValues[4]).getBiobjId());
				}
				if (snapValues[5] != null) {
					snap.setSchedulation((String) snapValues[5]);
				}
				if (snapValues[6] != null) {
					snap.setSchedulationStartDate((Integer) snapValues[6]);
				}
				if (snapValues[7] != null) {
					snap.setContentType((String) snapValues[7]);
				}
			}

			String schedulation = snap.getSchedulation();

			Map<Integer, List<Snapshot>> snapForSchedulation = snaps.get(schedulation);
			if (snapForSchedulation == null) {
				snapForSchedulation = new HashMap<>();
				snaps.put(schedulation, snapForSchedulation);
			}

			Integer schedulationTime = snap.getSchedulationStartDate();
			List<Snapshot> snapForSchedulationAndTime = snapForSchedulation.get(schedulationTime);
			if (snapForSchedulationAndTime == null) {
				snapForSchedulationAndTime = new ArrayList<Snapshot>();
				snapForSchedulation.put(schedulationTime, snapForSchedulationAndTime);
				documentLIstLIst.add(snapForSchedulationAndTime);
			}

			snapForSchedulationAndTime.add(snap);
		}

		if (collate) {
			collateSnapshot(documentLIstLIst);
		}

	} catch (HibernateException he) {
		logException(he);
		throw new EMFUserError(EMFErrorSeverity.ERROR, 100);
	} finally {
		if (aSession != null) {
			if (aSession.isOpen())
				aSession.close();
		}
	}
	return snaps;
}
 

public Map<Long, List<Long>> getHighestAssessmentGradingByPublishedItem(final Long publishedAssessmentId) {
    Map<Long, List<Long>> h = new HashMap<>();

    final HibernateCallback<List<AssessmentGradingData>> hcb = session -> {
        Query q = session.createQuery(
                "select new AssessmentGradingData(" +
                        " a.assessmentGradingId, p.itemId, " +
                        " a.agentId, a.finalScore, a.submittedDate) " +
                        " from ItemGradingData i, AssessmentGradingData a, " +
                        " PublishedItemData p where " +
                        " i.assessmentGradingId = a.assessmentGradingId and i.publishedItemId = p.itemId and " +
                        " a.publishedAssessmentId = :id and a.status > :status " +
                        " order by a.agentId asc, a.finalScore desc");
        q.setLong("id", publishedAssessmentId);
        q.setInteger("status", AssessmentGradingData.REMOVED);
        return q.list();
    };
    List<AssessmentGradingData> assessmentGradings = getHibernateTemplate().execute(hcb);

    String currentAgent = "";
    Double finalScore = null;
    for (int i = 0; i < assessmentGradings.size(); i++) {
        AssessmentGradingData g = (AssessmentGradingData) assessmentGradings.get(i);
        Long itemId = g.getPublishedItemId();
        Long gradingId = g.getAssessmentGradingId();
        log.debug("**** itemId=" + itemId + ", gradingId=" + gradingId + ", agentId=" + g.getAgentId() + ", score=" + g
                .getFinalScore());
        if (i == 0) {
            currentAgent = g.getAgentId();
            finalScore = g.getFinalScore();
        }
        if (currentAgent.equals(g.getAgentId())
                && ((finalScore == null && g.getFinalScore() == null)
                || (finalScore != null && finalScore.equals(g.getFinalScore())))) {
            List<Long> o = h.get(itemId);
            if (o != null) {
                o.add(gradingId);
            } else {
                List<Long> gradingIds = new ArrayList<>();
                gradingIds.add(gradingId);
                h.put(itemId, gradingIds);
            }
        }
        if (!currentAgent.equals(g.getAgentId())) {
            currentAgent = g.getAgentId();
            finalScore = g.getFinalScore();
        }
    }
    return h;
}