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

/**
 * Get the current number of container from k8s_obj_pods
 * This table holds the pods&containers of K8s Objects other than deployment.
 * @param envId
 * @param platform
 * @return
 */
public static Map<String, Integer> getK8sObjPodsContDetails(int envId) {
	final java.sql.Date todayDate = new java.sql.Date(Calendar.getInstance().getTimeInMillis());;
	Session session = HibernateConfig.getSessionFactory().getCurrentSession();
	Transaction txn = session.beginTransaction();
	Query numApisQuery = session.createQuery(HQLConstants.QUERY_GET_K8S_OBJECT_PODS);
	numApisQuery.setParameter("environmentId", envId);
	numApisQuery.setParameter("statusDate", todayDate);
	@SuppressWarnings("unchecked")
	List<Object[]> objPodsList = numApisQuery.list();
	Map<String, Integer> mapObjPods = new HashMap<String, Integer>();
	for (Object[] tRow : objPodsList) {
		mapObjPods.put((String)tRow[0], ((Integer)tRow[1]));
	}
	txn.commit();
	return mapObjPods;
}
 

/**
 * {@inheritDoc}
 */
@Override
public List<GradingEvent> getGradingEvents(final String studentId, final long assignmentId) {

	if (log.isDebugEnabled()) {
		log.debug("getGradingEvents called for studentId:" + studentId);
	}

	List<GradingEvent> rval = new ArrayList<>();

	if (studentId == null) {
		log.debug("No student id was specified.  Returning an empty GradingEvents object");
		return rval;
	}

	final HibernateCallback<List<GradingEvent>> hc = session -> {
		final Query q = session
				.createQuery("from GradingEvent as ge where ge.studentId=:studentId and ge.gradableObject.id=:assignmentId order by date_graded desc");
		q.setParameter("studentId", studentId);
		q.setParameter("assignmentId", assignmentId);
		return q.list();
	};

	rval = getHibernateTemplate().execute(hc);
	return rval;
}
 

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

	HibernateCallback<List<Object[]>> hcb = session -> {
        Query q = session.getNamedQuery("findReadStatsForStudentByTopicId");
        q.setParameter("userId", studentId, StringType.INSTANCE);
        q.setParameter("topicId", topicId, LongType.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;
}
 

/**
 * getSyllabusItemByContextId finds a SyllabusItem
 * @param contextId
 * @return SyllabusItem
 *        
 */
public SyllabusItem getSyllabusItemByContextId(final String contextId)
{
  if (contextId == null)
  {
    throw new IllegalArgumentException("Null Argument");
  }
        
  HibernateCallback<SyllabusItem> hcb = session -> {
    Query q = session.getNamedQuery(QUERY_BY_CONTEXTID);
    q.setParameter(CONTEXT_ID, contextId, StringType.INSTANCE);
    return (SyllabusItem) q.uniqueResult();
  };
      
  return getHibernateTemplate().execute(hcb);
}
 

private MembershipCmImpl getMembership(final String userId, final AbstractMembershipContainerCmImpl container) {
	final String lcUserId = StringUtils.lowerCase(userId);
	// This may be a dynamic proxy.  In that case, make sure we're using the class
	// that hibernate understands.
	final String className = Hibernate.getClass(container).getName();

	final StringBuilder sb = new StringBuilder("select mbr from MembershipCmImpl as mbr, ");
	sb.append(className);
       sb.append(" as container where mbr.memberContainer=container ");
       sb.append("and container.eid=:eid ");
   	sb.append("and mbr.userId=:userId");
   	
	HibernateCallback hc = new HibernateCallback() {
		public Object doInHibernate(Session session) throws HibernateException {
			Query q = session.createQuery(sb.toString());
			q.setParameter("eid", container.getEid());
			q.setParameter("userId", lcUserId);
			return q.uniqueResult();
		}
	};
	return (MembershipCmImpl)getHibernateTemplate().execute(hc);
}
 

/**
 * 设置sql or hql查询条件
 *
 * @param query
 * @param queryWrapper
 */
@SuppressWarnings("rawtypes")
protected void setParameter(Query query, QueryWrapper queryWrapper) {
	// TODO Auto-generated method stub
	if (queryWrapper != null) {
		Map<String, Object> map = queryWrapper.getParameterMap();
		String[] manes = query.getNamedParameters();
		for (String name : manes) {
			Object value = map.get(name);
			if (value instanceof Collection) {
				query.setParameterList(name, (Collection) value);
			} else if (value instanceof Object[]) {
				query.setParameterList(name, (Object[]) value);
			} else {
				query.setParameter(name, value);
			}
		}
	}
}
 

/**
 * helper method to get messages by type
 * @param typeUuid
 * @return message list
 */
public List getMessagesByType(final String typeUuid, final String orderField,
    final String order)
{

  if (log.isDebugEnabled())
  {
    log.debug("getMessagesByType(typeUuid:" + typeUuid + ", orderField: "
        + orderField + ", order:" + order + ")");
  }

  HibernateCallback<List> hcb = session -> {
    Query q = session.getNamedQuery(QUERY_MESSAGES_BY_USER_TYPE_AND_CONTEXT);
    Query qOrdered = session.createQuery(q.getQueryString() + " order by "
        + orderField + " " + order);

    qOrdered.setParameter("userId", getCurrentUser(), StringType.INSTANCE);
    qOrdered.setParameter("typeUuid", typeUuid, StringType.INSTANCE);
    qOrdered.setParameter("contextId", getContextId(), StringType.INSTANCE);
    return qOrdered.list();
  };

  return getHibernateTemplate().execute(hcb);
}
 

/**
 * Needed to get course offering eid in the output for an enrollment set.
 */
public EnrollmentSetCmImpl getEnrollmentSetByEid(final String eid) {
  HibernateCallback hc = session -> {
    StringBuilder hql = new StringBuilder();
    hql.append("from ").append(EnrollmentSetCmImpl.class.getName()).append(" as obj where obj.eid=:eid");
    Query q = session.createQuery(hql.toString());
    q.setParameter("eid", eid);
    EnrollmentSetCmImpl result = (EnrollmentSetCmImpl) q.uniqueResult();
    if (result == null) {
      throw new IdNotFoundException(eid, EnrollmentSetCmImpl.class.getName());
    }
    Hibernate.initialize(result.getCourseOffering());
    return result;
  };
  return (EnrollmentSetCmImpl) getHibernateTemplate().execute(hc);
}
 

/**
	 * {@inheritDoc}
	 */
@Override
public ProfileKudos getKudos(final String userUuid) {
			
	final HibernateCallback<ProfileKudos> hcb = session -> {
           final Query q = session.getNamedQuery(QUERY_GET_KUDOS_RECORD);
           q.setParameter(USER_UUID, userUuid, StringType.INSTANCE);
           q.setMaxResults(1);
           return (ProfileKudos) q.uniqueResult();
       };
  	
  	return getHibernateTemplate().execute(hcb);
}
 

/**
 * 检查用户是否存在
 * */
public TSUser findUserByAccountAndPassword(String username,String inpassword) {
	String password = PasswordUtil.encrypt(username, inpassword, PasswordUtil.getStaticSalt());
	String query = "from TSUser u where u.userName = :username and u.password=:passowrd";
	Query queryObject = getSession().createQuery(query);
	queryObject.setParameter("username", username);
	queryObject.setParameter("passowrd", password);
	@SuppressWarnings("unchecked")
	List<TSUser> users = queryObject.list();
	if (users != null && users.size() > 0) {
		return users.get(0);
	}
	return null;
}
 

public Set<CourseOffering> getEquivalentCourseOfferings(String courseOfferingEid) throws IdNotFoundException {
	final CourseOfferingCmImpl courseOffering = (CourseOfferingCmImpl)getCourseOffering(courseOfferingEid);
	HibernateCallback<List<CourseOffering>> hc = session -> {
           Query q = session.getNamedQuery("findEquivalentCourseOfferings");
           q.setParameter("crossListing", courseOffering.getCrossListing());
           q.setParameter("courseOffering", courseOffering);
           return q.list();
       };
	return new HashSet<>(getHibernateTemplate().execute(hc));
}
 

/**
 * Checks if a key already exists for a given url, if so returns it else returns null
 * @param url
 * @return
 */
private String getExistingKey(final String url) {

	if (StringUtils.isBlank(url)) {
		return null;
	}
	
	//first check cache
	String value = (String) cache.get(url);
	if (value != null) {
		return value;
	}
	
	//then check db
	RandomisedUrl randomisedUrl = null;
	
	HibernateCallback<RandomisedUrl> hcb = session -> {
           Query q = session.getNamedQuery(QUERY_GET_KEY);
           q.setParameter(URL, url, StringType.INSTANCE);
           q.setMaxResults(1);
           return (RandomisedUrl) q.uniqueResult();
     };

	//will be either a RandomisedUrl or null
	randomisedUrl = getHibernateTemplate().execute(hcb);
	if(randomisedUrl == null) {
		return null;
	}
	
	//add to cache
	String key = randomisedUrl.getKey();
	addToCache(url, key);

	return key;
}
 

public Project getProject(long id) {
	final Session s = openSession();
	s.getTransaction().begin();
	final Query q = s.createQuery( "FROM Project WHERE id = :id" );
	q.setParameter( "id", id );
	q.setCacheable( true );
	final Project project = (Project) q.uniqueResult();
	s.getTransaction().commit();
	return project;
}
 

private void setAliasParameter(Query query, Map<String, Object> alias) {
    if (alias != null) {
        Set<String> keys = alias.keySet();
        for (String key : keys) {
            Object val = alias.get(key);
            if (val instanceof Collection) {
                // 查询条件是列表
                query.setParameterList(key, (Collection) val);
            } else {
                query.setParameter(key, val);
            }
        }
    }
}
 

public int countByChannelId(int channelId) {
	String hql = "select count(*) from Content bean"
			+ " join bean.channel channel,Channel parent"
			+ " where channel.lft between parent.lft and parent.rgt"
			+ " and channel.site.id=parent.site.id"
			+ " and parent.id=:parentId";
	Query query = getSession().createQuery(hql);
	query.setParameter("parentId", channelId);
	return ((Number) (query.iterate().next())).intValue();
}
 

public List getAllMembershipItemsForForumsForSite(final Long areaId)
{
	if (log.isDebugEnabled())
	{
		log.debug("getAllMembershipItemsForForumsForSite executing");
	}
	
	HibernateCallback<List> hcb = session -> {
         Query q = session.getNamedQuery(QUERY_BY_AREA_ALL_FORUMS_MEMBERSHIP);
         q.setParameter("areaId", areaId, LongType.INSTANCE);
         return q.list();
       };
				
   return getHibernateTemplate().execute(hcb);
}
 

/**
	 * {@inheritDoc}
	 */
@Override
public List<WallItem> getWallItemsForUser(final String userUuid) {
	
	final HibernateCallback<List<WallItem>> hcb = session -> {
           final Query q = session.getNamedQuery(QUERY_GET_WALL_ITEMS);
           q.setParameter(USER_UUID, userUuid, StringType.INSTANCE);
           return q.list();
       };
  	
  	return getHibernateTemplate().execute(hcb);
}
 

@Override
public void cutFollower(int eid) {
	try{
		Session s = HibernateSessionFactory.getSession();
		String hql="update EventVo vo set vo.followerscount=vo.followerscount - 1 where vo.eid=:eid";
		Query query=s.createQuery(hql);
		query.setParameter("eid",eid);
		query.executeUpdate();
		Transaction t = s.beginTransaction();
		t.commit();
		s.close();
   }catch(Exception e){
	   	e.printStackTrace();
   }
}
 

/**
 * Description: <br>
 * 
 * @author 王伟<br>
 * @taskId <br>
 * @param hql
 * @param param
 * @return
 * @throws DaoException <br>
 */

@Override
public <T> List<T> findHql(final String hql, final Object... param) throws DaoException {
    Query q = getSession().createQuery(hql);
    if (param != null && param.length > 0) {
        for (int i = 0; i < param.length; i++) {
            q.setParameter(i, param[i]);
        }
    }
    return q.list();
}
 

@SuppressWarnings("unchecked")
public List<AggregatedReportRow> generateAggregatedTaggedReport(AggregateReportParameters params) {
    String queryString = "select "
            + "MR.id, "
            + "T.testCaseId, "
            + "M.name, "
            + "TCR.description, "
            + "SF.host, "
            + "MR.startTime, "
            + "MR.finishTime, "
            + "TCR.status, "
            + "TCR.failReason, "
            + "TCR.id "

            + "from TestCaseRun as TCR "
            + "join TCR.matrixRun as MR "
            + "join MR.matrix as M "
            + "join MR.sfInstance as SF "
            + "join TCR.testCase as T "
            + "where MR.startTime >= :from and MR.finishTime <= :to ";

    if (params.getTags() != null && !params.getTags().isEmpty()) {
        queryString += "and 1 <= " +
                        "(select count(distinct TCRT.tag.id) from "
                        + "TestCaseRun as TCR2 "
                        + "join TCR2.tags as TCRT "
                        + "where TCR2.id = TCR.id "
                        + "and TCRT.tag.id in (:tcrTags)) + " +
                        "(select count(distinct T.id) from "
                        + "MatrixRun as MR2 "
                        + "join MR2.tags as T "
                        + "where MR2.id = MR.id "
                        + "and T.id in (:mrTags)) ";
    }

    Session session = null;
    try {
        session = sessionFactory.openSession();
        Query query = session.createQuery(queryString);

        query.setParameter("from", params.getFrom());
        query.setParameter("to", params.getTo());

        if(params.getTags() != null && !params.getTags().isEmpty()) {
            Object[] ids = tagsToIds(params.getTags());
            query.setParameterList("tcrTags", ids);
            query.setParameterList("mrTags", ids);
        }

        return parseAggregatedTaggedReportResult(query.list());
    } finally {
        if(session != null) {
            session.close();
        }
    }
}