@Test
public void test_sql_hibernate_query_scalar_explicit_result_set_example() {
  doInJPA(this::entityManagerFactory, entityManager -> {
    Session session = entityManager.unwrap(Session.class);
    //tag::sql-hibernate-scalar-query-explicit-result-set-example[]
    List<Object[]> persons = session.createNativeQuery(
        "SELECT * FROM Person")
        .addScalar("id", LongType.INSTANCE)
        .addScalar("name", StringType.INSTANCE)
        .list();

    for (Object[] person : persons) {
      Long id = (Long) person[0];
      String name = (String) person[1];
    }
    //end::sql-hibernate-scalar-query-explicit-result-set-example[]
    assertEquals(3, persons.size());
  });
}
 

@Test
public void test_sql_hibernate_query_scalar_partial_explicit_result_set_example() {

  doInJPA(this::entityManagerFactory, entityManager -> {
    Session session = entityManager.unwrap(Session.class);
    //tag::sql-hibernate-scalar-query-partial-explicit-result-set-example[]
    List<Object[]> persons = session.createNativeQuery(
        "SELECT * FROM Person")
        .addScalar("id", LongType.INSTANCE)
        .addScalar("name")
        .list();

    for (Object[] person : persons) {
      Long id = (Long) person[0];
      String name = (String) person[1];
    }
    //end::sql-hibernate-scalar-query-partial-explicit-result-set-example[]
    assertEquals(3, persons.size());
  });
}
 

public Rank getRankById(final Long rankId) {
    if (log.isDebugEnabled()) {
        log.debug("getRankById: " + rankId + ")");
    }

    if (rankId == null) {
        throw new IllegalArgumentException("getRankById(): rankId is null");
    }

    if (!isRanksEnabled())
    {
        // This is 'warn' because it implies some code is aware of a rank, but ranks are disabled
        log.warn("getRankById invoked, but ranks are disabled");
        return null;
    }

    HibernateCallback<Rank> hcb = session -> {
        Query q = session.getNamedQuery(QUERY_BY_RANK_ID);
        q.setParameter("rankId", rankId, LongType.INSTANCE);
        return (Rank) q.uniqueResult();
    };

    Rank rank = getHibernateTemplate().execute(hcb);
    return rank;
}
 

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

	HibernateCallback hcb = session -> {
        Query q = session.getNamedQuery("findAuthoredStatsForStudentByTopicId");
        q.setParameter("topicId", topicId, LongType.INSTANCE);
        q.setParameter("userId", studentId, StringType.INSTANCE);
        return q.list();
    };
	List<UserStatistics> returnList = new ArrayList<UserStatistics>();
	List<Object[]> results = (List<Object[]>)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<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<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;
}
 

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

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

public List findMessagesByTopicId(final Long topicId) {
    if (topicId == null) {
        log.error("findMessagesByTopicId failed with topicId: null");
        throw new IllegalArgumentException("Null Argument");
    }

    log.debug("findMessagesByTopicId executing with topicId: " + topicId);

    HibernateCallback<List> hcb = session -> {
        Query q = session.getNamedQuery(QUERY_BY_TOPIC_ID);
        q.setParameter("topicId", topicId, LongType.INSTANCE);
        return q.list();
    };

    return getHibernateTemplate().execute(hcb);
}
 

public List findUndeletedMessagesByTopicId(final Long topicId) {
    if (topicId == null) {
        log.error("findUndeletedMessagesByTopicId failed with topicId: null");
        throw new IllegalArgumentException("Null Argument");
    }

    log.debug("findUndeletedMessagesByTopicId executing with topicId: " + topicId);

    HibernateCallback<List> hcb = session -> {
        Query q = session.getNamedQuery(QUERY_UNDELETED_MSG_BY_TOPIC_ID);
        q.setParameter("topicId", topicId, LongType.INSTANCE);
        return q.list();
    };

    return getHibernateTemplate().execute(hcb);
}
 

public UnreadStatus findUnreadStatusByUserId(final Long topicId, final Long messageId, final String userId){
	if (messageId == null || topicId == null || userId == null) {
        log.error("findUnreadStatusByUserId failed with topicId: " + topicId + ", messageId: " + messageId
        		+ ", userId: " + userId);
        throw new IllegalArgumentException("Null Argument");
    }

    log.debug("findUnreadStatus executing with topicId: " + topicId + ", messageId: " + messageId);

    HibernateCallback<UnreadStatus> hcb = session -> {
        Query q = session.getNamedQuery(QUERY_UNREAD_STATUS);
        q.setParameter("topicId", topicId, LongType.INSTANCE);
        q.setParameter("messageId", messageId, LongType.INSTANCE);
        q.setParameter("userId", userId, StringType.INSTANCE);
        return (UnreadStatus) q.uniqueResult();
    };

    return getHibernateTemplate().execute(hcb);
}
 

/**
 * @see org.sakaiproject.api.app.messageforums.MessageForumsMessageManager#getMessageByIdWithAttachments(java.lang.Long)
 */
public Message getMessageByIdWithAttachments(final Long messageId){
  
  if (messageId == null) {
    throw new IllegalArgumentException("Null Argument");
   }

   log.debug("getMessageByIdWithAttachments executing with messageId: " + messageId);
    

  HibernateCallback<Message> hcb = session -> {
    Query q = session.getNamedQuery(QUERY_BY_MESSAGE_ID_WITH_ATTACHMENTS);
    q.setParameter("id", messageId, LongType.INSTANCE);
    return (Message) q.uniqueResult();
  };

  return getHibernateTemplate().execute(hcb);
}
 

public void getChildMsgs(final Long messageId, List returnList)
{
	List tempList;

    HibernateCallback<List> hcb = session -> {
        Query q = session.getNamedQuery(QUERY_CHILD_MESSAGES);
        Query qOrdered = session.createQuery(q.getQueryString());

        qOrdered.setParameter("messageId", messageId, LongType.INSTANCE);

        return qOrdered.list();
    };
  
  tempList = getHibernateTemplate().execute(hcb);
  if(tempList != null)
  {
  	for(int i=0; i<tempList.size(); i++)
  	{
  		getChildMsgs(((Message)tempList.get(i)).getId(), returnList);
  		returnList.add((Message) tempList.get(i));
  	}
  }
}
 

public List findMovedMessagesByTopicId(final Long topicId) {
	if (topicId == null) {
		log.error("findMovedMessagesByTopicId failed with topicId: " + topicId);
		throw new IllegalArgumentException("Null Argument");
	}

	if (log.isDebugEnabled()) log.debug("findMovedMessagesByTopicId executing with topicId: " + topicId);

	HibernateCallback<List> hcb = session -> {
           Query q = session.getNamedQuery(QUERY_MOVED_MESSAGES_BY_TOPICID);
           q.setParameter("topicId", topicId, LongType.INSTANCE);
           return q.list();
       };

	return getHibernateTemplate().execute(hcb);
}
 

public List findMovedHistoryByMessageId(final Long messageid){
	if (messageid == null) {
		log.error("findMovedHistoryByMessageId failed with messageid: " + messageid);
		throw new IllegalArgumentException("Null Argument");
	}

	if (log.isDebugEnabled()) log.debug("findMovedHistoryByMessageId executing with messageid: " + messageid);

	HibernateCallback<List> hcb = session -> {
           Query q = session.getNamedQuery(QUERY_MOVED_HISTORY_BY_MESSAGEID);
           q.setParameter("messageId", messageid, LongType.INSTANCE);
           return q.list();
       };

	return getHibernateTemplate().execute(hcb);

}
 

/**
 * findLargestSyllabusPosition finds the largest syllabus data position for an item
 * @param syllabusItem
 */
public Integer findLargestSyllabusPosition(final SyllabusItem syllabusItem)      
{
  if (syllabusItem == null)
  {
    throw new IllegalArgumentException("Null Argument");
  }
  else
  {
    HibernateCallback<Integer> hcb = session -> {
      Query q = session.getNamedQuery(QUERY_LARGEST_POSITION);
      q.setParameter(FOREIGN_KEY, syllabusItem.getSurrogateKey(), LongType.INSTANCE);

      Integer position = (Integer) q.uniqueResult();

      if (position == null){
        return 0;
      }
      else{
        return position;
      }

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

public Collection<Assignment> getAssignments(Collection<Long> solutionId) {
	if (solutionId.isEmpty()) return getCommitedAssignments();
	List<Assignment> ret = new ArrayList<Assignment>(new LocationDAO().getSession().createQuery(
			"select a from Assignment a inner join a.rooms r where " +
			"r.uniqueId = :locationId and a.solution.uniqueId in (:solutionIds)")
			.setLong("locationId", getUniqueId())
			.setParameterList("solutionIds", solutionId, new LongType())
			.setCacheable(true).list());
	ret.addAll(new LocationDAO().getSession().createQuery(
			"select a from Assignment a inner join a.rooms r where " +
            "r.uniqueId = :locationId and a.solution.commited = true and " +
			"a.solution.owner.uniqueId not in (select s.owner.uniqueId from Solution s where s.uniqueId in (:solutionIds))")
			.setLong("locationId", getUniqueId())
			.setParameterList("solutionIds", solutionId, new LongType())
            .setCacheable(true).list());
	return ret;
}
 

public Rank getRankById(final Long rankId) {
    if (log.isDebugEnabled()) {
        log.debug("getRankById: " + rankId + ")");
    }

    if (rankId == null) {
        throw new IllegalArgumentException("getRankById(): rankId is null");
    }

    if (!isRanksEnabled())
    {
        // This is 'warn' because it implies some code is aware of a rank, but ranks are disabled
        log.warn("getRankById invoked, but ranks are disabled");
        return null;
    }

    HibernateCallback<Rank> hcb = session -> {
        Query q = session.getNamedQuery(QUERY_BY_RANK_ID);
        q.setParameter("rankId", rankId, LongType.INSTANCE);
        return (Rank) q.uniqueResult();
    };

    Rank rank = getHibernateTemplate().execute(hcb);
    return rank;
}
 

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

	HibernateCallback hcb = session -> {
        Query q = session.getNamedQuery("findAuthoredStatsForStudentByTopicId");
        q.setParameter("topicId", topicId, LongType.INSTANCE);
        q.setParameter("userId", studentId, StringType.INSTANCE);
        return q.list();
    };
	List<UserStatistics> returnList = new ArrayList<UserStatistics>();
	List<Object[]> results = (List<Object[]>)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<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<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;
}
 

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

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

public List findMessagesByTopicId(final Long topicId) {
    if (topicId == null) {
        log.error("findMessagesByTopicId failed with topicId: null");
        throw new IllegalArgumentException("Null Argument");
    }

    log.debug("findMessagesByTopicId executing with topicId: " + topicId);

    HibernateCallback<List> hcb = session -> {
        Query q = session.getNamedQuery(QUERY_BY_TOPIC_ID);
        q.setParameter("topicId", topicId, LongType.INSTANCE);
        return q.list();
    };

    return getHibernateTemplate().execute(hcb);
}
 

public List findUndeletedMessagesByTopicId(final Long topicId) {
    if (topicId == null) {
        log.error("findUndeletedMessagesByTopicId failed with topicId: null");
        throw new IllegalArgumentException("Null Argument");
    }

    log.debug("findUndeletedMessagesByTopicId executing with topicId: " + topicId);

    HibernateCallback<List> hcb = session -> {
        Query q = session.getNamedQuery(QUERY_UNDELETED_MSG_BY_TOPIC_ID);
        q.setParameter("topicId", topicId, LongType.INSTANCE);
        return q.list();
    };

    return getHibernateTemplate().execute(hcb);
}
 

public UnreadStatus findUnreadStatusByUserId(final Long topicId, final Long messageId, final String userId){
	if (messageId == null || topicId == null || userId == null) {
        log.error("findUnreadStatusByUserId failed with topicId: " + topicId + ", messageId: " + messageId
        		+ ", userId: " + userId);
        throw new IllegalArgumentException("Null Argument");
    }

    log.debug("findUnreadStatus executing with topicId: " + topicId + ", messageId: " + messageId);

    HibernateCallback<UnreadStatus> hcb = session -> {
        Query q = session.getNamedQuery(QUERY_UNREAD_STATUS);
        q.setParameter("topicId", topicId, LongType.INSTANCE);
        q.setParameter("messageId", messageId, LongType.INSTANCE);
        q.setParameter("userId", userId, StringType.INSTANCE);
        return (UnreadStatus) q.uniqueResult();
    };

    return getHibernateTemplate().execute(hcb);
}
 

/**
 * @see org.sakaiproject.api.app.messageforums.MessageForumsMessageManager#getMessageByIdWithAttachments(java.lang.Long)
 */
public Message getMessageByIdWithAttachments(final Long messageId){
  
  if (messageId == null) {
    throw new IllegalArgumentException("Null Argument");
   }

   log.debug("getMessageByIdWithAttachments executing with messageId: " + messageId);
    

  HibernateCallback<Message> hcb = session -> {
    Query q = session.getNamedQuery(QUERY_BY_MESSAGE_ID_WITH_ATTACHMENTS);
    q.setParameter("id", messageId, LongType.INSTANCE);
    return (Message) q.uniqueResult();
  };

  return getHibernateTemplate().execute(hcb);
}
 

public void getChildMsgs(final Long messageId, List returnList)
{
	List tempList;

    HibernateCallback<List> hcb = session -> {
        Query q = session.getNamedQuery(QUERY_CHILD_MESSAGES);
        Query qOrdered = session.createQuery(q.getQueryString());

        qOrdered.setParameter("messageId", messageId, LongType.INSTANCE);

        return qOrdered.list();
    };
  
  tempList = getHibernateTemplate().execute(hcb);
  if(tempList != null)
  {
  	for(int i=0; i<tempList.size(); i++)
  	{
  		getChildMsgs(((Message)tempList.get(i)).getId(), returnList);
  		returnList.add((Message) tempList.get(i));
  	}
  }
}
 

public List findMovedMessagesByTopicId(final Long topicId) {
	if (topicId == null) {
		log.error("findMovedMessagesByTopicId failed with topicId: " + topicId);
		throw new IllegalArgumentException("Null Argument");
	}

	if (log.isDebugEnabled()) log.debug("findMovedMessagesByTopicId executing with topicId: " + topicId);

	HibernateCallback<List> hcb = session -> {
           Query q = session.getNamedQuery(QUERY_MOVED_MESSAGES_BY_TOPICID);
           q.setParameter("topicId", topicId, LongType.INSTANCE);
           return q.list();
       };

	return getHibernateTemplate().execute(hcb);
}
 

public List findMovedHistoryByMessageId(final Long messageid){
	if (messageid == null) {
		log.error("findMovedHistoryByMessageId failed with messageid: " + messageid);
		throw new IllegalArgumentException("Null Argument");
	}

	if (log.isDebugEnabled()) log.debug("findMovedHistoryByMessageId executing with messageid: " + messageid);

	HibernateCallback<List> hcb = session -> {
           Query q = session.getNamedQuery(QUERY_MOVED_HISTORY_BY_MESSAGEID);
           q.setParameter("messageId", messageid, LongType.INSTANCE);
           return q.list();
       };

	return getHibernateTemplate().execute(hcb);

}
 

/**
 * findLargestSyllabusPosition finds the largest syllabus data position for an item
 * @param syllabusItem
 */
public Integer findLargestSyllabusPosition(final SyllabusItem syllabusItem)      
{
  if (syllabusItem == null)
  {
    throw new IllegalArgumentException("Null Argument");
  }
  else
  {
    HibernateCallback<Integer> hcb = session -> {
      Query q = session.getNamedQuery(QUERY_LARGEST_POSITION);
      q.setParameter(FOREIGN_KEY, syllabusItem.getSurrogateKey(), LongType.INSTANCE);

      Integer position = (Integer) q.uniqueResult();

      if (position == null){
        return 0;
      }
      else{
        return position;
      }

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

private List<Long> fromInclusiveModificationsForPipelineRange(Session session,
                                                              String pipelineName,
                                                              Integer fromCounter,
                                                              Integer toCounter) {
    String pipelineIdsSql = queryExtensions.queryFromInclusiveModificationsForPipelineRange(pipelineName, fromCounter, toCounter);
    SQLQuery pipelineIdsQuery = session.createSQLQuery(pipelineIdsSql);
    final List ids = pipelineIdsQuery.list();
    if (ids.isEmpty()) {
        return new ArrayList<>();
    }

    String minMaxQuery = " SELECT mods1.materialId as materialId, min(mods1.id) as min, max(mods1.id) as max"
            + " FROM modifications mods1 "
            + "     INNER JOIN pipelineMaterialRevisions pmr ON (mods1.id >= pmr.actualFromRevisionId AND mods1.id <= pmr.toRevisionId) AND mods1.materialId = pmr.materialId "
            + " WHERE pmr.pipelineId IN (:ids) "
            + " GROUP BY mods1.materialId";

    SQLQuery query = session.createSQLQuery("SELECT mods.id "
            + " FROM modifications mods"
            + "     INNER JOIN (" + minMaxQuery + ") as edges on edges.materialId = mods.materialId and mods.id >= min and mods.id <= max"
            + " ORDER BY mods.materialId ASC, mods.id DESC");
    query.addScalar("id", new LongType());
    query.setParameterList("ids", ids);

    return query.list();
}