下面列出了org.hibernate.Query#setString ( ) 实例代码,或者点击链接到github查看源代码,也可以在右侧发表评论。
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;
}
}
}
/**
* 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);
}
@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();
}
}
}
/**
* 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);
}
}
/**
* 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);
}
}
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
*/
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);
}
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);
}
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<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);
}
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);
}
@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);
}
}
/**
* 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);
}
/**
* 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;
}
@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;
}
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();
}
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;
}
/**
* @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;
}
/**
* 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<>();
}
}
@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;
}