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

public long getTotalSiteVisitsForUser(String siteId, String userId) {
	if(siteId == null || userId == null){
		throw new IllegalArgumentException("Null siteId or userId");
	}else{
		final String hql = "select sum(es.count) " +
				"from EventStatImpl as es " +
				"where es.siteId = :siteid " +
				"and es.userId = :userid " +
				"and es.eventId = 'pres.begin' ";

		HibernateCallback<Long> hcb = session -> {
			Query q = session.createQuery(hql);
			q.setString("siteid", siteId);
			q.setString("userid", userId);
			List<Long> res = q.list();
			if(res.size() > 0 && res.get(0) != null) return res.get(0);
			else return 0L;
		};
		try{
			return getHibernateTemplate().execute(hcb);
		}catch(ClassCastException e) {
			log.error("Cannot get total site visits for user: {} on site: {}", userId, siteId);
			return 0l;
		}
	}
}
 

/**
 * Find latest by node
 */
public static WikiPage findLatestByNode(String uuid) throws DatabaseException {
	log.debug("findLatestByNode({})", uuid);
	String qs = "from WikiPage wp where wp.node=:uuid and wp.deleted=false order by wp.date desc";
	Session session = null;

	try {
		session = HibernateUtil.getSessionFactory().openSession();
		Query q = session.createQuery(qs);
		q.setString("uuid", uuid);
		WikiPage ret = (WikiPage) q.setMaxResults(1).uniqueResult();
		log.debug("findLatestByNode: {}", ret);
		return ret;
	} catch (HibernateException e) {
		throw new DatabaseException(e.getMessage(), e);
	} finally {
		HibernateUtil.close(session);
	}
}
 

/**
 * Find by user
 */
public static Profile findByUser(String user) throws DatabaseException {
	log.debug("findByUser({})", user);
	String qs = "select profile from UserConfig uc where uc.user=:user";
	Session session = null;

	try {
		session = HibernateUtil.getSessionFactory().openSession();
		Query q = session.createQuery(qs);
		q.setString("user", user);
		Profile ret = (Profile) q.setMaxResults(1).uniqueResult();
		log.debug("findByUser: {}", ret);
		return ret;
	} catch (HibernateException e) {
		throw new DatabaseException(e.getMessage(), e);
	} finally {
		HibernateUtil.close(session);
	}
}
 

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

/**
 * FOR SYNOPTIC TOOL:
 * 		Returns the count of discussion forum messages grouped by site for sites with
 * 		Forum topics that don't have membership items in the db
 */
public List<Object []> findDiscussionForumMessageCountsForTopicsWithMissingPermsForAllSites(final List<String> siteList) {
	if (siteList == null) {
        log.error("findDiscussionForumMessageCountsForTopicsWithMissingPermsForAllSites failed with null site list.");
        throw new IllegalArgumentException("Null Argument");
	}	
    
	HibernateCallback<List<Object[]>> hcb = session -> {
        Query q = session.getNamedQuery("findDiscussionForumMessageCountsForTopicsWithMissingPermsForAllSites");
         q.setParameterList("siteList", siteList);
         q.setString("userId", getCurrentUser());
        return q.list();
    };

    return getHibernateTemplate().execute(hcb);
}
 

/**
 * FOR SYNOPTIC TOOL:
 * 		Returns the count of discussion forum messages grouped by site
 */
public List findDiscussionForumMessageCountsForAllSitesByPermissionLevelName(final List siteList, final List roleList) {
	if (siteList == null) {
        log.error("findDiscussionForumMessageCountsForAllSitesByPermissionLevelName failed with null site list.");
        throw new IllegalArgumentException("Null Argument");
	}	
    
	HibernateCallback<List> hcb = session -> {
        Query q = session.getNamedQuery("findDiscussionForumMessageCountsForAllSitesByPermissionLevelName");
         q.setParameterList("siteList", siteList);
         q.setParameterList("roleList", roleList);
         q.setString("userId", getCurrentUser());
         q.setString("customTypeUuid", typeManager.getCustomLevelType());
        return q.list();
    };

    return getHibernateTemplate().execute(hcb);
}
 

@Override
public List<Integer> loadSelectedProductTypesIds(String tenant) throws EMFUserError {
	logger.debug("IN");
	Session aSession = null;
	Transaction tx = null;
	try {
		aSession = getSession();
		tx = aSession.beginTransaction();
		Query hibQuery = aSession
				.createQuery("select p.sbiProductType.productTypeId from SbiOrganizationProductType p where p.sbiOrganizations.name = :tenantName");
		hibQuery.setString("tenantName", tenant);
		ArrayList<Integer> result = (ArrayList<Integer>) hibQuery.list();
		return result;
	} catch (HibernateException he) {
		logger.error(he.getMessage(), he);
		if (tx != null)
			tx.rollback();
		throw new SpagoBIRuntimeException("Error getting Tenant Product Types", he);
	} finally {
		logger.debug("OUT");
		if (aSession != null) {
			if (aSession.isOpen())
				aSession.close();
		}
	}
}
 

@Override
public List<SbiI18NMessages> getI18NMessages(String languageName) {
	logger.debug("IN");

	List<SbiI18NMessages> toReturn = null;

	Session aSession = null;
	Transaction tx = null;

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

		Integer domainId = getSbiDomainId(languageName, aSession);
		String tenant = getTenant();
		String hql = "from SbiI18NMessages m where m.languageCd = :languageCd and m.commonInfo.organization = :organization";
		Query query = aSession.createQuery(hql);
		query.setInteger("languageCd", domainId);
		query.setString("organization", tenant);

		toReturn = query.list();

	} catch (HibernateException he) {
		logger.error(he.getMessage(), he);
		if (tx != null)
			tx.rollback();
		throw new RuntimeException();
	} finally {
		if (aSession != null) {
			if (aSession.isOpen())
				aSession.close();
		}
	}
	logger.debug("OUT.toReturn=" + toReturn);
	return toReturn;

}
 

/**
 * Find current document version
 */
public NodeDocumentVersion findCurrentVersion(Session session, String docUuid) throws HibernateException {
	log.debug("findCurrentVersion({})", docUuid);
	String qs = "from NodeDocumentVersion ndv where ndv.parent=:parent and ndv.current=:current";
	Query q = session.createQuery(qs).setCacheable(true);
	q.setString("parent", docUuid);
	q.setBoolean("current", true);
	NodeDocumentVersion currentVersion = (NodeDocumentVersion) q.setMaxResults(1).uniqueResult();
	return currentVersion;
}
 

/**
 * FOR SYNOPTIC TOOL:
 * 		Returns the count of discussion forum messages grouped by topics within a site
 * 		Used by sites that are grouped
 */
public List findDiscussionForumMessageCountsForGroupedSitesByTopic(final List siteList, final List roleList) {
    
	HibernateCallback<List> hcb = session -> {
        Query q = session.getNamedQuery("findDiscussionForumMessageCountsForGroupedSitesByTopic");
        q.setParameterList("siteList", siteList);
        q.setParameterList("roleList", roleList);
        q.setString("userId", getCurrentUser());
        q.setString("customTypeUuid", typeManager.getCustomLevelType());
        return q.list();
    };
    
    return getHibernateTemplate().execute(hcb);
}
 

@Override
public User getUserByEmail(String email) {
	String sql = "from User where email=:email";
	//String sql = "select id from User where email=:email";
	Session session = sessionFactory.getCurrentSession();
	Query query = session.createQuery(sql);
	query.setString("email", email);
	List<User> users = query.list();
	if (users.size() == 0) {
		return null;
	} else {
		return users.get(0);
	}
}
 

private long getFirstMessageID(Session session, String serviceID) {
    String hql = "select msg.id from StoredMessage msg";

    if(serviceID != null) {
        hql += " where msg.serviceId = :serviceID";
    }

    Query query = session.createQuery(hql + " order by msg.id asc");

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

    return (long)ObjectUtils.defaultIfNull(query.setMaxResults(1).uniqueResult(), -1L);
}
 

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

    return getHibernateTemplate().execute(hcb);
}
 

public List getReceivedUuidByContextId(final List siteList) {
    if (siteList == null) {
        throw new IllegalArgumentException("Null Argument");
    }      

   HibernateCallback<List> hcb = session -> {
       Query q = session.getNamedQuery(QUERY_RECEIVED_UUID_BY_CONTEXT_ID);
       q.setParameterList("siteList", siteList);
       q.setString("userId", getCurrentUser());
       return q.list();
   };

    return getHibernateTemplate().execute(hcb);
 
}
 

public List getModeratedTopicsInSite(final String contextId) {

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

        log.debug("getModeratedTopicsInSite executing with contextId: " + contextId);

        HibernateCallback<List> hcb = session -> {
            Query q = session.getNamedQuery(QUERY_GET_ALL_MOD_TOPICS_IN_SITE);
            q.setString("contextId", contextId);
            return q.list();
        };
        
        Topic tempTopic = 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 Topic) {
              tempTopic = (Topic) HibernateUtils.unproxy(results[0]);
              tempTopic.setBaseForum((BaseForum) HibernateUtils.unproxy(results[1]));
            } else {
              tempTopic = (Topic) HibernateUtils.unproxy(results[1]);
              tempTopic.setBaseForum((BaseForum) HibernateUtils.unproxy(results[0]));
            }
            resultSet.add(tempTopic);
          }
        }
        return Util.setToList(resultSet);
    }
 

private List<ChangeLog> findChangeLog(LastChangesRequest request) {
	String from = "ChangeLog l";
	
	String where = "l.objectType = :type and l.objectUniqueId = :id";
	
	String groupBy = null;
	
	String orderBy = "l.timeStamp desc";
	
	if (Location.class.getName().equals(request.getObjectType())) {
		if ("true".equalsIgnoreCase(request.getOption("multi-session"))) {
			from = "ChangeLog l, Location r1, Location r2";
			where = "l.objectType in (:type, :roomType, :locType) and r1.uniqueId = :id and r2.permanentId = r1.permanentId and r2.uniqueId = l.objectUniqueId";
		} else {
			where = "l.objectType in (:type, :roomType, :locType) and l.objectUniqueId = :id";
		}
	}

	if (request.hasOption("operation")) {
		where += " and l.operationString = :operation";
	}
	
	if (request.hasOption("page")) {
		where += " and l.sourceString = :source";
	}
	
	String query = "select l from " + from + " where " + where + (groupBy == null ? "" : " group by " + groupBy ) + " order by " + orderBy;
	
	Query q = ChangeLogDAO.getInstance().getSession().createQuery(query);
	
	if (request.hasOption("limit"))
		q.setMaxResults(Integer.valueOf(request.getOption("limit")));
	else
		q.setMaxResults(ApplicationProperty.LastChangesLimit.intValue());
	
	if (request.hasOption("operation")) {
		q.setString("operation", request.getOption("operation").toUpperCase());
	}
	
	if (request.hasOption("page")) {
		q.setString("source", request.getOption("page").replace(' ', '_').toUpperCase());
	}
	
	if (Location.class.getName().equals(request.getObjectType())) {
		q.setString("roomType", Room.class.getName());
		q.setString("locType", NonUniversityLocation.class.getName());
	}

	return q.setString("type", request.getObjectType()).setLong("id", request.getObjectId()).setCacheable(true).list();
}
 

@Override
public List getMyRecentlyUsed(String userId, int limit) throws EMFUserError {
	logger.debug("IN");
	Session aSession = null;
	Transaction tx = null;
	List toReturn = new ArrayList();
	if (userId == null || userId.trim().equals("")) {
		logger.warn("The user id in input is null or empty.");
		return toReturn;
	}
	try {
		aSession = getSession();
		tx = aSession.beginTransaction();
		StringBuffer hql = new StringBuffer();
		hql.append("select ");
		hql.append("		max(a.requestTime), ");
		hql.append("		a.sbiObject.biobjId, ");
		hql.append("		a.sbiObject.label, ");
		hql.append("		a.sbiObject.name, ");
		hql.append("		a.sbiObject.descr, ");
		hql.append("		a.sbiObject.objectTypeCode, ");
		hql.append("		a.subObjId, ");
		hql.append("		a.subObjName, ");
		hql.append("		coalesce(str(a.documentParameters), 'No parameters'), ");
		hql.append("		a.sbiEngine.name, ");
		hql.append("		a.sbiObject.previewFile ");
		hql.append("from ");
		hql.append("		SbiAudit a ");
		hql.append("where 	");
		hql.append("		a.sbiObject is not null and ");
		hql.append("		a.sbiEngine is not null and ");
		hql.append("		a.sbiObject.label not like 'SBI_%' and ");
		hql.append("		a.userName = ? and ");
		hql.append("		(a.sbiSubObject is null or a.sbiSubObject.subObjId = a.subObjId) ");
		hql.append("group by 	a.sbiObject.biobjId, ");
		hql.append("			a.sbiObject.label, ");
		hql.append("			a.sbiObject.name, ");
		hql.append("			a.sbiObject.descr, ");
		hql.append("			a.sbiObject.objectTypeCode, ");
		hql.append("			a.subObjId, ");
		hql.append("			a.subObjName, ");
		hql.append("			coalesce(str(a.documentParameters), 'No parameters'), ");
		hql.append("			a.sbiEngine.name, ");
		hql.append("		a.sbiObject.previewFile ");
		hql.append("order by max(a.requestTime) desc ");
		Query hqlQuery = aSession.createQuery(hql.toString());
		hqlQuery.setString(0, userId);
		hqlQuery.setMaxResults(limit);
		List result = hqlQuery.list();
		Iterator resultIt = result.iterator();
		while (resultIt.hasNext()) {
			Object[] row = (Object[]) resultIt.next();
			toReturn.add(toHotLink(row));
		}
	} catch (Exception ex) {
		logger.error(ex);
		if (tx != null)
			tx.rollback();
		throw new EMFUserError(EMFErrorSeverity.ERROR, 100);
	} finally {
		if (aSession != null) {
			if (aSession.isOpen())
				aSession.close();
		}
		logger.debug("OUT");
	}
	return toReturn;
}
 

/**
 * This returns a hashmap of all the latest item entries, keyed by
 * item id for easy retrieval.
 *
 * @param publishedId
 * @param agentId
 * @return
 */
public Map<Long, List<ItemGradingData>> getLastItemGradingData(final Long publishedId, final String agentId) {
    try {
        final HibernateCallback<List<AssessmentGradingData>> hcb = session -> {
            // I am debating should I use (a.forGrade=false and a.status=NO_SUBMISSION) or attemptDate is not null
            Query q = session.createQuery(
                    "from AssessmentGradingData a where a.publishedAssessmentId = :id " +
                            "and a.agentId = :agent and a.forGrade = :forgrade and a.status <> :status and a.status <> :removed " +
                            "order by a.submittedDate DESC");
            q.setLong("id", publishedId);
            q.setString("agent", agentId);
            q.setBoolean("forgrade", false);
            q.setInteger("status", AssessmentGradingData.NO_SUBMISSION);
            q.setInteger("removed", AssessmentGradingData.REMOVED);
            return q.list();
        };
        List<AssessmentGradingData> scores = getHibernateTemplate().execute(hcb);

        if (scores.isEmpty()) {
            return new HashMap<>();
        }
        HashMap<Long, List<ItemGradingData>> map = new HashMap<>();
        AssessmentGradingData gdata = scores.get(0);
        // initialize itemGradingSet
        gdata.setItemGradingSet(getItemGradingSet(gdata.getAssessmentGradingId()));
        if (gdata.getForGrade()) {
            return new HashMap<>();
        }
        for (ItemGradingData data : gdata.getItemGradingSet()) {
            List<ItemGradingData> thisone = map.get(data.getPublishedItemId());
            if (thisone == null) {
                thisone = new ArrayList<>();
            }
            thisone.add(data);
            map.put(data.getPublishedItemId(), thisone);
        }
        return map;
    } catch (Exception e) {
        log.warn(e.getMessage(), e);
        return new HashMap<>();
    }
}
 

/**
 * @param scope Sent from DatasetResource <br>
 *              Can be: "all", "owned", "enterprise" and "shared", depends on Tab from Workspace/Datasets (MyDataset, Shared, Enterprise, All)
 */
@Override
public List<IDataSet> loadDatasetsByTags(UserProfile user, List<Integer> tagIds, String scope) {
	logger.debug("IN");
	List<IDataSet> toReturn = new ArrayList<>();
	Session session = null;
	Set<Domain> categoryList = null;
	String owner = null;
	String domain = null;
	String[] domains = null;
	try {
		Assert.assertNotNull(user, "UserProfile object cannot be null");

		StringBuffer statement = new StringBuffer("select distinct(dst.dataSet) from SbiDatasetTag dst where dst.dataSet.active = ? ");

		if (scope.equalsIgnoreCase("owned") || scope.equalsIgnoreCase("shared")) {
			owner = user.getUserId().toString();
			if (owner != null) {
				if (scope.equalsIgnoreCase("owned"))
					statement.append("and dst.dataSet.owner = :owner ");
				else
					statement.append("and dst.dataSet.owner != :owner ");
			}
		}

		if (scope.equalsIgnoreCase("enterprise") || scope.equalsIgnoreCase("shared") || scope.equalsIgnoreCase("all")) {
			statement.append("and dst.dataSet.scope.valueCd = :domain ");
			if (scope.equalsIgnoreCase("enterprise"))
				domain = scope.toUpperCase();
			else if (scope.equalsIgnoreCase("shared"))
				domain = "USER";
			else {
				domains = new String[2];
				domains[0] = "USER";
				domains[1] = "ENTERPRISE";
				statement.append("and (dst.dataSet.scope.valueCd = :user or dst.dataSet.scope.valueCd = :enterprise) ");
			}

			categoryList = UserUtilities.getDataSetCategoriesByUser(user);
			if (categoryList != null && !categoryList.isEmpty()) {
				statement.append("and dst.dataSet.category.valueCd in (:categories) ");
			}
		}

		if (!tagIds.isEmpty()) {
			statement.append("and dst.dsTagId.tagId in (:tagIds)");
		}

		session = getSession();
		Query query = session.createQuery(statement.toString());

		// Always get active versions
		query.setBoolean(0, true);

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

		if (domain != null)
			query.setString("domain", domain);

		if (domains != null && domains.length > 0) {
			query.setString("user", domains[0]);
			query.setString("enterprise", domains[1]);
		}

		if (categoryList != null && !categoryList.isEmpty()) {
			Iterator<Domain> it = categoryList.iterator();
			List<String> categoryValues = new ArrayList<>();
			while (it.hasNext()) {
				categoryValues.add(it.next().getValueName());
			}

			query.setParameterList("categories", categoryValues);
		}

		if (!tagIds.isEmpty()) {
			query.setParameterList("tagIds", tagIds);
		}

		toReturn = executeQuery(query, session);
	} catch (Exception e) {
		logger.error("An error has occured while filtering Enterprise Datasets by Tags", e);
		throw new SpagoBIDAOException("An unexpected error has occured while filtering Datasets by Tags", e);
	} finally {
		if (session != null && session.isOpen())
			session.close();
	}

	logger.debug("OUT");
	return toReturn;
}
 

public AssessmentGradingData getHighestSubmittedAssessmentGrading(final Long publishedAssessmentId, final String agentId, Long assessmentGradingId) {
    AssessmentGradingData ag = null;

    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.finalScore desc, a.submittedDate desc");
        q.setLong("id", publishedAssessmentId);
        q.setString("agent", agentId);
        q.setBoolean("forgrade", true);
        q.setInteger("status", AssessmentGradingData.REMOVED);
        return q.list();
    };
    List<AssessmentGradingData> assessmentGradings = getHibernateTemplate().execute(hcb);

    if (assessmentGradingId == null) {
        if (assessmentGradings.size() > 0) {
            ag = assessmentGradings.get(0);
        }
    } else {
        for (int i = 0; i < assessmentGradings.size(); i++) {
            AssessmentGradingData agd = assessmentGradings.get(i);
            if (agd.getAssessmentGradingId().compareTo(assessmentGradingId) == 0) {
                ag = agd;
                ag.setItemGradingSet(getItemGradingSet(agd.getAssessmentGradingId()));
                break;
            }
        }
    }

    // get AssessmentGradingAttachments
    List<AssessmentGradingAttachment> attachments = new ArrayList<AssessmentGradingAttachment>();
    if (ag != null) {
        Map<Long, List<AssessmentGradingAttachment>> map = getAssessmentGradingAttachmentMap(publishedAssessmentId);
        if (map != null && map.containsKey(ag.getAssessmentGradingId())) {
            attachments = map.get(ag.getAssessmentGradingId());
        }
        ag.setAssessmentGradingAttachmentList(attachments);
    }
    return ag;
}