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

public boolean isRangeOverlapping(long start, long end) 
        throws DbException { 
    try {
        String sql = "SELECT COUNT(*) FROM ip_range WHERE (end_address >= ? AND end_address <= ?) OR (start_address <=  ? AND end_address >= ?) OR  (start_address >= ? AND start_address <= ?) OR (start_address >= ? AND end_address <= ?)";

        Query query = session.createSQLQuery(sql);
        query.setLong(0, start);   
        query.setLong(1, end);
        query.setLong(2, start);
        query.setLong(3, end);
        query.setLong(4, start);
        query.setLong(5, end);
        query.setLong(6, start);
        query.setLong(7, end);

        long result = ((BigInteger)query.list().get(0)).longValue();

        return query.list() != null && result > 0;
    }
    catch (Exception e) {
        throw handleException(e.getClass().getSimpleName() + " exception in isRangeOverlapping", e);
    }           
}
 

/**
 * Get validation by pk
 */
public AutomationValidation findValidationByPk(long avId) throws DatabaseException {
	log.debug("findValidationByPk({})", avId);
	String qs = "from AutomationValidation av where av.id=:id";
	Session session = null;

	try {
		session = HibernateUtil.getSessionFactory().openSession();
		Query q = session.createQuery(qs);
		q.setLong("id", avId);
		AutomationValidation ret = (AutomationValidation) q.setMaxResults(1).uniqueResult();
		initialize(ret);
		log.debug("findValidationByPk: {}", ret);
		return ret;
	} catch (HibernateException e) {
		throw new DatabaseException(e.getMessage(), e);
	} finally {
		HibernateUtil.close(session);
	}
}
 

/**
 * Find by pk
 */
public static MailAccount findByPk(long maId) throws DatabaseException {
	log.debug("findByPk({})", maId);
	String qs = "from MailAccount ma where ma.id=:id";
	Session session = null;

	try {
		session = HibernateUtil.getSessionFactory().openSession();
		Query q = session.createQuery(qs);
		q.setLong("id", maId);
		MailAccount ret = (MailAccount) q.setMaxResults(1).uniqueResult();
		log.debug("findByPk: {}", ret);
		return ret;
	} catch (HibernateException e) {
		throw new DatabaseException(e.getMessage(), e);
	} finally {
		HibernateUtil.close(session);
	}
}
 

/**
 * Returns count of all read msgs for given user that have been approved or
 * were authored by user
 * @param topicId
 * @param userId
 * @return
 */
public int findReadViewableMessageCountByTopicIdByUserId(final Long topicId, final String userId) {
	if (topicId == null || userId == null) {
        log.error("findReadViewableMessageCountByTopicIdByUserId failed with topicId: " + topicId + 
        			" and userId: " + userId);
        throw new IllegalArgumentException("Null Argument");
    }

    log.debug("findReadViewableMessageCountByTopicIdByUserId executing with topicId: " + topicId + 
    				" and userId: " + userId);

    HibernateCallback<Number> hcb = session -> {
        Query q = session.getNamedQuery(QUERY_COUNT_READ_VIEWABLE_BY_TOPIC_ID);
        q.setLong("topicId", topicId);
        q.setString("userId", userId);
        return (Number) q.uniqueResult();
    };

    return getHibernateTemplate().execute(hcb).intValue();
}
 

@SuppressWarnings({ "rawtypes", "unchecked" })
private List getAllAssignmentGradeRecordsForGbItem(final Long gradableObjectId,
		final Collection studentUids) {
	final HibernateCallback hc = new HibernateCallback() {
		@Override
		public Object doInHibernate(final Session session) throws HibernateException {
			if (studentUids.isEmpty()) {
				// If there are no enrollments, no need to execute the query.
				if (log.isInfoEnabled()) {
					log.info("No enrollments were specified.  Returning an empty List of grade records");
				}
				return new ArrayList();
			} else {
				final Query q = session.createQuery("from AssignmentGradeRecord as agr where agr.gradableObject.removed=false and " +
						"agr.gradableObject.id=:gradableObjectId order by agr.pointsEarned");
				q.setLong("gradableObjectId", gradableObjectId);
				return filterGradeRecordsByStudents(q.list(), studentUids);
			}
		}
	};
	return (List) getHibernateTemplate().execute(hc);
}
 

/**
 * Find by pk
 */
public static CronTab findByPk(long ctId) throws DatabaseException {
	log.debug("findByPk({})", ctId);
	String qs = "from CronTab ct where ct.id=:id";
	Session session = null;

	try {
		session = HibernateUtil.getSessionFactory().openSession();
		Query q = session.createQuery(qs);
		q.setLong("id", ctId);
		CronTab ret = (CronTab) q.setMaxResults(1).uniqueResult();
		log.debug("findByPk: {}", ret);
		return ret;
	} catch (HibernateException e) {
		throw new DatabaseException(e.getMessage(), e);
	} finally {
		HibernateUtil.close(session);
	}
}
 

public static CourseOffering findBySessionSubjAreaAbbvCourseNbrTitle(Long acadSessionId, String subjAreaAbbv, String courseNbr, String title) {

        InstructionalOfferingDAO iDao = new InstructionalOfferingDAO();
        org.hibernate.Session hibSession = iDao.getSession();
        
        String sql = " from CourseOffering co " +
                     " where co.subjectArea.subjectAreaAbbreviation=:subjArea" +
                     " and co.courseNbr = :crsNbr" +
                     " and co.title = :title" +
                     " and co.instructionalOffering.session.uniqueId = :acadSessionId";
        Query query = hibSession.createQuery(sql);
        query.setString("crsNbr", courseNbr);
        query.setString("subjArea", subjAreaAbbv);
        query.setLong("acadSessionId", acadSessionId.longValue());
        query.setString("title", title);
        
        return (CourseOffering)query.uniqueResult();

    }
 

/**
 * Update the k8s_pods_containers table with number of Pods&Containers
 * @param envId
 * @param compName
 * @param numOfPods
 * @param parentCompName
 * @param numOfCont
 */
public static void updatePodsAndContainers(final int envId, String compName, int numOfPods, String parentCompName, int numOfCont){
	final java.sql.Date todayDate = new java.sql.Date(Calendar.getInstance().getTimeInMillis());;
	final int compId = getComponentId(compName, parentCompName);
	if(compId == 0){
		logger.info("Component Name = " + compName + "; Parent Component Name = " + parentCompName + "; is not available in the DB");
		return;
	}
	Session session = HibernateConfig.getSessionFactory().getCurrentSession();
	Transaction txn = session.beginTransaction();
	Query query = session.createQuery(HQLConstants.UPDATE_K8S_PODS_CONT_DETAILS);
	query.setInteger("totalPods", numOfPods);
	query.setInteger("totalContainers", numOfCont);
	query.setLong("compId", compId);
	query.setLong("environmentId", envId);
	query.setDate("stsDate", todayDate);
	query.executeUpdate();
	txn.commit();
}
 

public AssessmentGradingData getLastAssessmentGradingByAgentId(final Long publishedAssessmentId, final String agentIdString) {
    AssessmentGradingData ag = null;

    final HibernateCallback<List<AssessmentGradingData>> hcb = session -> {
        Query q = session.createQuery(
                "from AssessmentGradingData a where a.publishedAssessmentId = :id and a.agentId = :agent order by a.submittedDate desc");
        q.setLong("id", publishedAssessmentId);
        q.setString("agent", agentIdString);
        return q.list();
    };
    List<AssessmentGradingData> assessmentGradings = getHibernateTemplate().execute(hcb);

    if (!assessmentGradings.isEmpty()) {
        ag = assessmentGradings.get(0);
        ag.setItemGradingSet(getItemGradingSet(ag.getAssessmentGradingId()));
    }
    return ag;
}
 

/**
 * Update the tps_latency_history/k8s_tps_latency_history table with tps & Latency values. 
 * 
 */
public static void updateTpsLatHistory(final int envId, int componentId, float tpsVaule, float latencyValue, String platform){
	final java.sql.Date todayDate = new java.sql.Date(Calendar.getInstance().getTimeInMillis());
	
	String queryStr = null;
	if(platform.equals("K8s")){
		queryStr = HQLConstants.UPDATE_K8S_TPS_LAT_HISTORY;
	}else if(platform.equals("Mesos")){
		queryStr = HQLConstants.UPDATE_MESOS_TPS_LAT_HISTORY;
	}else{
		return;
	}
	Session session = HibernateConfig.getSessionFactory().getCurrentSession();
	Transaction txn = session.beginTransaction();
	Query query = session.createQuery(queryStr);
	query.setFloat("tpsVaule", tpsVaule);
	query.setFloat("latencyValue", latencyValue);
	query.setLong("compId", componentId);
	query.setLong("environmentId", envId);
	query.setDate("statusDate", todayDate);
	query.executeUpdate();
	txn.commit();
}
 

public boolean getHasGradingData(final Long publishedAssessmentId) {
    final HibernateCallback<List<AssessmentGradingData>> hcb = session -> {
        Query q = session.createQuery(
                "from AssessmentGradingData a where a.publishedAssessmentId = :id");
        q.setLong("id", publishedAssessmentId);
        return q.list();
    };
    List<AssessmentGradingData> assessmentGradings = getHibernateTemplate().execute(hcb);
    return !assessmentGradings.isEmpty();
}
 

/**
    * (non-Javadoc)
    * 
    * @see org.lamsfoundation.lams.learningdesign.dao.ITransitionDAO#getTransitionByToActivityID(java.lang.Long)
    */
   @Override
   public Transition getTransitionByToActivityID(Long toActivityID) {
if (toActivityID != null) {
    Query query = getSessionFactory().getCurrentSession().createQuery(FIND_BY_TO_ACTIVITY);
    query.setLong("activity_id", toActivityID.longValue());
    return (Transition) query.uniqueResult();
}
return null;
   }
 

@SuppressWarnings("unchecked")
public List<AssessmentGradingData> getAllSubmissions(final String publishedId) {
    final HibernateCallback<List<AssessmentGradingData>> hcb = session -> {
        Query q = session.createQuery(
                "from AssessmentGradingData a where a.publishedAssessmentId = :id and a.forGrade = :forgrade and a.status > :status");
        q.setLong("id", Long.parseLong(publishedId));
        q.setBoolean("forgrade", true);
        q.setInteger("status", AssessmentGradingData.REMOVED);
        return q.list();
    };
    return getHibernateTemplate().execute(hcb);
}
 

public List<AssessmentGradingData> getAllAssessmentGradingByAgentId(final Long publishedAssessmentId, final String agentIdString) {

        final HibernateCallback<List<AssessmentGradingData>> hcb = session -> {
            Query q = session.createQuery(
                    "from AssessmentGradingData a where a.publishedAssessmentId = :id and a.agentId = :agent and a.forGrade = :forgrade and a.status > :status order by a.submittedDate desc");
            q.setLong("id", publishedAssessmentId);
            q.setString("agent", agentIdString);
            q.setBoolean("forgrade", true);
            q.setInteger("status", AssessmentGradingData.REMOVED);
            return q.list();
        };
        return getHibernateTemplate().execute(hcb);
    }
 

public List<AssessmentGradingData> getAllAssessmentGradingByAgentId(final Long publishedAssessmentId, final String agentIdString) {

        final HibernateCallback<List<AssessmentGradingData>> hcb = session -> {
            Query q = session.createQuery(
                    "from AssessmentGradingData a where a.publishedAssessmentId = :id and a.agentId = :agent and a.forGrade = :forgrade and a.status > :status order by a.submittedDate desc");
            q.setLong("id", publishedAssessmentId);
            q.setString("agent", agentIdString);
            q.setBoolean("forgrade", true);
            q.setInteger("status", AssessmentGradingData.REMOVED);
            return q.list();
        };
        return getHibernateTemplate().execute(hcb);
    }
 

public long getNumberOfModifiedOrganizations(Date startDate, Date endDate) 
        throws DbException { 
    try {    
        /** No. of modified organizations. */        
        String sql = "SELECT COUNT(*) FROM organization WHERE last_modified >= ? AND last_modified <= ? AND created <  last_modified";
        Query query = session.createSQLQuery(sql);
        query.setLong(0, SqlUtil.convertTimestamp(startDate));
        query.setLong(1, SqlUtil.convertTimestamp(endDate));

        return (query.list() != null) ? ((BigInteger)query.list().get(0)).longValue() : 0;
    }
    catch (Exception e) {
        throw handleException(e.getClass().getSimpleName() + " exception in getNumberOfModifiedOrganizations", e);
    }   
}
 

public List<AssessmentGradingData> getUnSubmittedAssessmentGradingDataList(final Long publishedAssessmentId, final String agentIdString) {
    final HibernateCallback<List<AssessmentGradingData>> hcb = session -> {
        Query q = session.createQuery(
                "from AssessmentGradingData a where a.publishedAssessmentId = :id and a.agentId = :agent and a.forGrade = :forgrade and a.status > :status order by a.attemptDate desc");
        q.setLong("id", publishedAssessmentId);
        q.setString("agent", agentIdString);
        q.setBoolean("forgrade", false);
        q.setInteger("status", AssessmentGradingData.REMOVED);
        return q.list();
    };
    return getHibernateTemplate().execute(hcb);
}
 

public Map<Long, List<Long>> getLastAssessmentGradingByPublishedItem(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();
        log.debug("**** itemId=" + itemId + ", gradingId=" + gradingId + ", agentId=" + g.getAgentId() + ", score=" + g
                .getFinalScore());
        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;
}
 

private boolean removeMessages(Instant olderThan, String serviceID, int limit) {
    flusher.flush();
    Session session = null;
    Transaction tx = null;
    boolean successful = false;

    try {
        session = sessionFactory.openSession();
        tx = session.beginTransaction();
        String hql = "delete StoredMessage where id < :lastID";

        if(olderThan != null) {
            hql += " and arrived < :olderThan";
        }

        if(serviceID != null) {
            hql += " and serviceId = :serviceID";
        }

        Query query = session.createQuery(hql);
        query.setLong("lastID", getFirstMessageID(session, serviceID) + limit);

        if(olderThan != null) {
            query.setString("olderThan", TimestampToString.instantToString(olderThan));
        }

        if(serviceID != null) {
            query.setString("serviceID", serviceID);
        }

        successful = query.executeUpdate() > 0;
        tx.commit();
        return successful;
    } catch(HibernateException e) {
        if(tx != null) {
            tx.rollback();
        }

        throw new StorageException("Failed to remove messages", e);
    } finally {
        if(session != null) {
            session.close();
        }
    }

}
 

@SuppressWarnings({ "unchecked", "rawtypes" })
public List getPointsEarnedCourseGradeRecords(final CourseGrade courseGrade, final Collection studentUids) {
	final HibernateCallback hc = new HibernateCallback() {
		@Override
		public Object doInHibernate(final Session session) throws HibernateException {
			if (studentUids == null || studentUids.isEmpty()) {
				if (log.isInfoEnabled()) {
					log.info("Returning no grade records for an empty collection of student UIDs");
				}
				return new ArrayList();
			}

			final Query q = session.createQuery("from CourseGradeRecord as cgr where cgr.gradableObject.id=:gradableObjectId");
			q.setLong("gradableObjectId", courseGrade.getId());
			final List records = filterAndPopulateCourseGradeRecordsByStudents(courseGrade, q.list(), studentUids);

			final Long gradebookId = courseGrade.getGradebook().getId();
			final Gradebook gradebook = getGradebook(gradebookId);
			final List cates = getCategories(gradebookId);

			// get all of the AssignmentGradeRecords here to avoid repeated db calls
			final Map<String, List<AssignmentGradeRecord>> gradeRecMap = getGradeRecordMapForStudents(gradebookId, studentUids);

			// get all of the counted assignments
			final List<GradebookAssignment> assignments = getCountedAssignments(session, gradebookId);
			final List<GradebookAssignment> countedAssigns = new ArrayList<>();
			if (assignments != null) {
				for (final GradebookAssignment assign : assignments) {
					// extra check to account for new features like extra credit
					if (assign.isIncludedInCalculations()) {
						countedAssigns.add(assign);
					}
				}
			}
			// double totalPointsPossible = getTotalPointsInternal(gradebookId, session);
			// if(log.isDebugEnabled()) log.debug("Total points = " + totalPointsPossible);

			for (final Iterator iter = records.iterator(); iter.hasNext();) {
				final CourseGradeRecord cgr = (CourseGradeRecord) iter.next();
				// double totalPointsEarned = getTotalPointsEarnedInternal(gradebookId, cgr.getStudentId(), session);
				final List<AssignmentGradeRecord> studentGradeRecs = gradeRecMap.get(cgr.getStudentId());

				applyDropScores(studentGradeRecs);
				final List totalEarned = getTotalPointsEarnedInternal(cgr.getStudentId(), gradebook, cates, studentGradeRecs,
						countedAssigns);
				final double totalPointsEarned = ((Double) totalEarned.get(0));
				final double literalTotalPointsEarned = ((Double) totalEarned.get(1));
				final double extraPointsEarned = ((Double) totalEarned.get(2));
				final double totalPointsPossible = getTotalPointsInternal(gradebook, cates, cgr.getStudentId(), studentGradeRecs,
						countedAssigns, false);
				cgr.initNonpersistentFields(totalPointsPossible, totalPointsEarned, literalTotalPointsEarned, extraPointsEarned);
				if (log.isDebugEnabled()) {
					log.debug("Points earned = " + cgr.getPointsEarned());
				}
				if (log.isDebugEnabled()) {
					log.debug("Points possible = " + cgr.getTotalPointsPossible());
				}
			}

			return records;
		}
	};
	return (List) getHibernateTemplate().execute(hc);
}