Java Code Examples for org.hibernate.query.NativeQuery#list()

@SuppressWarnings({ "unchecked" })
   private SortedSet<Comment> getStickyByThreadIdNewestFirst(final Long rootTopicId, Integer sortBy, Integer userId) {

// the search to get to the top level is quite light, so get just the uids
// then build a complete set.
List<Number> threadUidList = getSession().createNativeQuery(SQL_QUERY_FIND_STICKY_BY_UID)
	.setParameter("rootUid", rootTopicId).list();

if (threadUidList != null && threadUidList.size() > 0) {
    NativeQuery<Object[]> query = getSession().createSQLQuery(SQL_QUERY_FIND_NEXT_THREAD_MESSAGES);
    query.addEntity("comment", Comment.class).addScalar("likes_total", IntegerType.INSTANCE)
	    .addScalar("user_vote", IntegerType.INSTANCE).setParameter("userId", userId != null ? userId : 0)
	    .setParameterList("threadIds", threadUidList);
    List<Object[]> results = query.list();
    return upgradeComments(results, sortBy);
}
return new TreeSet<Comment>();
   }
 

@Override
   public Object[] getStatsMarksForLeaders(Long toolContentId) {
NativeQuery<?> query = getSession().createNativeQuery(FIND_MARK_STATS)
	.addScalar("min_grade", FloatType.INSTANCE)
	.addScalar("avg_grade", FloatType.INSTANCE)
	.addScalar("max_grade", FloatType.INSTANCE)
	.addScalar("num_complete", IntegerType.INSTANCE);
query.setParameter("toolContentId", toolContentId);
@SuppressWarnings("unchecked")
List<Object[]> list = (List<Object[]>) query.list();
if ((list == null) || (list.size() == 0)) {
    return null;
} else {
    return (Object[]) list.get(0);
}
   }
 

@SuppressWarnings({ "unchecked" })
   private SortedSet<Comment> getNextThreadByThreadIdNewestFirst(final Long rootTopicId,
    final Long previousThreadMessageId, Integer numberOfThreads, Integer sortBy, Integer userId) {

// the search to get to the top level is quite light, so get just the uids
// then build a complete set.
List<Number> threadUidList = null;
if (previousThreadMessageId == null || previousThreadMessageId == 0L) {
    threadUidList = getSession().createSQLQuery(SQL_QUERY_FIND_FIRST_THREAD_TOP_BY_UID)
	    .setParameter("rootUid", rootTopicId).setMaxResults(numberOfThreads).list();
} else {
    threadUidList = getSession().createSQLQuery(SQL_QUERY_FIND_NEXT_THREAD_TOP).setParameter("rootUid", rootTopicId)
	    .setParameter("lastUid", previousThreadMessageId).setMaxResults(numberOfThreads).list();
}

if (threadUidList != null && threadUidList.size() > 0) {
    NativeQuery<Object[]> query = getSession().createNativeQuery(SQL_QUERY_FIND_NEXT_THREAD_MESSAGES);
    query.addEntity("comment", Comment.class).addScalar("likes_total", IntegerType.INSTANCE)
	    .addScalar("user_vote", IntegerType.INSTANCE).setParameter("userId", userId != null ? userId : 0)
	    .setParameterList("threadIds", threadUidList);
    List<Object[]> results = query.list();
    return upgradeComments(results, sortBy);
}
return new TreeSet<Comment>();
   }
 

@Override
   @SuppressWarnings("unchecked")
   public List<Object[]> getUsersWithPortraitsBySessionID(Long sessionId) {
final String LOAD_USERS_WITH_PORTRAITS_BY_SESSION_ID = "SELECT user.user_id, luser.portrait_uuid portraitId FROM tl_lamc11_que_usr user  " +
	" INNER JOIN tl_lamc11_session session ON user.mc_session_id=session.uid" +
	" INNER JOIN lams_user luser ON luser.user_id = user.que_usr_id" +
	" WHERE session.mc_session_id = :sessionId";

NativeQuery<Object[]> query = getSession().createNativeQuery(LOAD_USERS_WITH_PORTRAITS_BY_SESSION_ID);
query.setParameter("sessionId", sessionId);
List<Object[]> list = query.list();

ArrayList<Object[]> userDtos = new ArrayList<Object[]>();
if (list != null && list.size() > 0) {
    for (Object[] element : list) {

	Long userId = ((Number) element[0]).longValue();
	Long portraitId = element[1] == null ? null : ((Number) element[1]).longValue();

	Object[] userDto = new Object[2];
	userDto[0] = userId;
	userDto[0] = portraitId;
	userDtos.add(userDto);
    }

}

return userDtos;
   }
 

@SuppressWarnings("unchecked")
   @Override
   public List<Object[]> getDetailedRatingsComments(Long toolContentId, Long toolSessionId, Long criteriaId, Long itemId ) {
NativeQuery<?> query = getSession().createNativeQuery(SELECT_ALL_RATINGS_COMMENTS_LEFT_FOR_ITEM)
	.setParameter("toolContentId", toolContentId)
	.setParameter("toolSessionId", toolSessionId)
	.setParameter("ratingCriteriaId", criteriaId)
	.setParameter("itemId", itemId);
return (List<Object[]>) query.list();
   }
 

@Override
   public List<AssessmentUserDTO> getFirstTotalScoresByContentId(Long toolContentId) {
final String FIRST_SCORES_BY_CONTENT_ID = "SELECT user.user_id, res.grade "
	+ "FROM tl_laasse10_assessment_result AS res "
	+ "JOIN tl_laasse10_user AS user ON res.user_uid = user.uid "
	+ "JOIN tl_laasse10_assessment AS assess ON res.assessment_uid = assess.uid AND assess.content_id = :contentId "
	+ "INNER JOIN (SELECT user_uid, MIN(start_date) AS startDate FROM tl_laasse10_assessment_result WHERE finish_date IS NOT NULL GROUP BY user_uid) firstRes "
	+ "ON (res.user_uid = firstRes.user_uid AND res.start_date = firstRes.startDate) GROUP BY res.user_uid";

NativeQuery<?> query = getSession().createNativeQuery(FIRST_SCORES_BY_CONTENT_ID);
query.setParameter("contentId", toolContentId);
@SuppressWarnings("unchecked")
List<Object[]> list = (List<Object[]>) query.list();
return convertResultsToAssessmentUserDTOList(list);
   }
 

@Override
   @SuppressWarnings("unchecked")
   public List<StatisticDTO> getLeaderStatisticsBySession(final Long contentId) {
NativeQuery<StatisticDTO> query = getSession().createNativeQuery(GET_LEADER_STATISTICS);
query.addScalar("sessionId", LongType.INSTANCE).addScalar("sessionName", StringType.INSTANCE)
	.addScalar("totalUploadedFiles", IntegerType.INSTANCE).addScalar("markedCount", IntegerType.INSTANCE)
	.setParameter("contentId", contentId).setResultTransformer(Transformers.aliasToBean(StatisticDTO.class));

List<StatisticDTO> list = query.list();
for (StatisticDTO dto : list) {
    dto.setNotMarkedCount(dto.getTotalUploadedFiles() - dto.getMarkedCount());
}
return list;
   }
 

@Override
   @SuppressWarnings("unchecked")
   public SortedSet<Comment> getThreadByThreadId(Long threadCommentId, Integer sortBy, Integer userId) {
NativeQuery<Object[]> query = getSession().createNativeQuery(SQL_QUERY_GET_COMPLETE_THREAD);
query.addEntity("comment", Comment.class).addScalar("likes_total", IntegerType.INSTANCE)
	.addScalar("user_vote", IntegerType.INSTANCE).setParameter("userId", userId != null ? userId : 0)
	.setParameter("threadId", threadCommentId);
List<Object[]> results = query.list();
return upgradeComments(results, sortBy);
   }
 

@Override
   @SuppressWarnings("unchecked")
   public List<StatisticDTO> getStatisticsBySession(final Long contentId) {
NativeQuery<StatisticDTO> query = getSession().createNativeQuery(GET_STATISTICS);
query.addScalar("sessionId", LongType.INSTANCE).addScalar("sessionName", StringType.INSTANCE)
	.addScalar("totalUploadedFiles", IntegerType.INSTANCE).addScalar("markedCount", IntegerType.INSTANCE)
	.setParameter("contentId", contentId).setResultTransformer(Transformers.aliasToBean(StatisticDTO.class));

List<StatisticDTO> list = query.list();
for (StatisticDTO dto : list) {
    dto.setNotMarkedCount(dto.getTotalUploadedFiles() - dto.getMarkedCount());
}
return list;
   }
 

@Override
   @SuppressWarnings("unchecked")
   public List<VoteStatsDTO> getStatisticsBySession(Long toolContentId) {

NativeQuery<VoteStatsDTO> query = getSession().createSQLQuery(GET_STATISTICS);
query.addScalar("sessionUid", LongType.INSTANCE).addScalar("sessionName", StringType.INSTANCE)
	.addScalar("countUsersComplete", IntegerType.INSTANCE).setParameter("contentId", toolContentId)
	.setResultTransformer(Transformers.aliasToBean(VoteStatsDTO.class));

return query.list();
   }
 

@Override
   @SuppressWarnings("unchecked")
   /** Returns < [surveySession, numUsers] ... [surveySession, numUsers]> */
   public List<Object[]> getStatisticsBySession(final Long contentId) {

NativeQuery<Object[]> query = getSession().createNativeQuery(GET_STATISTICS);
query.addEntity(SurveySession.class).addScalar("numUsers", IntegerType.INSTANCE).setParameter("contentId",
	contentId);
return query.list();
   }
 

@Override
   @SuppressWarnings("unchecked")
   /**
    * Will return List<[ForumUser, String], [ForumUser, String], ... , [ForumUser, String]>
    * where the String is the notebook entry. No notebook entries needed? Will return "null" in their place.
    */
   public List<Object[]> getUsersForTablesorter(final Long sessionId, int page, int size, int sorting,
    String searchString, boolean getNotebookEntries, ICoreNotebookService coreNotebookService,
    IUserManagementService userManagementService) {
String sortingOrder;
boolean sortOnMessage;
switch (sorting) {
    case ForumConstants.SORT_BY_USER_NAME_ASC:
	sortingOrder = "user.last_name ASC, user.first_name ASC";
	sortOnMessage = false;
	break;
    case ForumConstants.SORT_BY_USER_NAME_DESC:
	sortingOrder = "user.last_name DESC, user.first_name DESC";
	sortOnMessage = false;
	break;
    case ForumConstants.SORT_BY_LAST_POSTING_ASC:
	sortingOrder = " MAX(message.update_date) ASC";
	sortOnMessage = true;
	break;
    case ForumConstants.SORT_BY_LAST_POSTING_DESC:
	sortingOrder = " MAX(message.update_date) DESC";
	sortOnMessage = true;
	break;
    case ForumConstants.SORT_BY_NUMBER_OF_POSTS_ASC:
	sortingOrder = " COUNT(message.uid) ASC";
	sortOnMessage = true;
	break;
    case ForumConstants.SORT_BY_NUMBER_OF_POSTS_DESC:
	sortingOrder = " COUNT(message.uid) DESC";
	sortOnMessage = true;
	break;
    case ForumConstants.SORT_BY_MARKED_ASC:
	sortingOrder = " AVG(report.mark) ASC";
	sortOnMessage = true;
	break;
    case ForumConstants.SORT_BY_MARKED_DESC:
	sortingOrder = " AVG(report.mark) DESC";
	sortOnMessage = true;
	break;
    case ForumConstants.SORT_BY_NO:
    default:
	sortingOrder = "user.uid";
	sortOnMessage = false;
}

// If the session uses notebook, then get the SQL to join across to get the entries
String[] notebookEntryStrings = null;
if (getNotebookEntries) {
    notebookEntryStrings = coreNotebookService.getNotebookEntrySQLStrings(sessionId.toString(),
	    ForumConstants.TOOL_SIGNATURE, "user.user_id");
}

String[] portraitStrings = userManagementService.getPortraitSQL("user.user_id");

// Basic select for the user records
StringBuilder queryText = new StringBuilder();

queryText.append("SELECT user.* ");
queryText.append(notebookEntryStrings != null ? notebookEntryStrings[0] : ", NULL notebookEntry");
queryText.append(portraitStrings[0]);
queryText.append(" FROM tl_lafrum11_forum_user user ");
queryText.append(portraitStrings[1]);
queryText.append(
	" JOIN tl_lafrum11_tool_session session ON user.session_id = session.uid and session.session_id = :sessionId");

if (sortOnMessage) {
    queryText.append(" LEFT JOIN tl_lafrum11_message message ON message.create_by = user.uid");
    if (sorting == ForumConstants.SORT_BY_MARKED_ASC || sorting == ForumConstants.SORT_BY_MARKED_DESC) {
	queryText.append(" LEFT JOIN tl_lafrum11_report report ON report.uid = message.report_id");
    }
}

// If filtering by name add a name based where clause (LDEV-3779: must come before the Notebook JOIN statement)
buildNameSearch(queryText, searchString);

// If using notebook, add the notebook join
if (notebookEntryStrings != null) {
    queryText.append(notebookEntryStrings[1]);
}

if (sortOnMessage) {
    queryText.append(" GROUP BY user.user_id");
}

// Now specify the sort based on the switch statement above.
queryText.append(" ORDER BY " + sortingOrder);

NativeQuery<Object[]> query = getSession().createNativeQuery(queryText.toString());
query.addEntity("user", ForumUser.class).addScalar("notebookEntry", StringType.INSTANCE)
	.addScalar("portraitId", IntegerType.INSTANCE)
	.setParameter("sessionId", sessionId.longValue()).setFirstResult(page * size).setMaxResults(size);
return query.list();

   }
 

private Object executeWithEntityManager(NativeQueryInfo info) {
    EntityManager entityManager = ApplicationContextProvider.getApplicationContext().getBean(EntityManager.class);
    Session session = entityManager.unwrap(Session.class);
    NativeQuery<?> query;
    if (info.isEntity()) {
        query = session.createNativeQuery(info.getSql(), info.getAliasToBean());
    } else {
        query = session.createNativeQuery(info.getSql());
    }

    addParameterJpa(query, info);

    if (info.hasPagination()) {
        query.setFirstResult(info.getFirstResult());
        query.setMaxResults(info.getMaxResult());
    }

    query.getQueryString();

    if (!info.isJavaObject() && !info.isEntity()) {
        query.setResultTransformer(Transformers.aliasToBean(info.getAliasToBean()));
    }
    if (info.getReturnType().getSimpleName().equals(Void.TYPE.getName())) {
        query.executeUpdate();
        return null;
    }

    if (info.returnTypeIsOptional()) {
        return getOptionalReturn(query::getSingleResult);
    }

    if (info.isSingleResult()) {
        return query.getSingleResult();
    }

    List<?> resultList = query.list();
    if (info.isPagination()) {
        return new PageImpl(resultList, info.getPageable(), getTotalRecords(info, session));
    }
    return resultList;
}
 

@Override
   public List<User> getUsersByGroup(Long lessonId, Long activityId, Long groupId, int page, int size, String sortBy,
    String sortOrder, String searchString) {
final String LOAD_LEARNERS_ORDERED_BY_NAME = "SELECT DISTINCT user.* " + " FROM lams_user_group ug "
	+ " INNER JOIN lams_user user ON ug.user_id=user.user_id " + " WHERE ug.group_id=:groupId "
	+ " AND (CONCAT(user.last_name, ' ', user.first_name) LIKE CONCAT('%', :searchString, '%')) "
	+ " ORDER BY CONCAT(user.last_name, ' ', user.first_name) " + sortOrder;

//when :sortBy='timeTaken'
final String LOAD_LEARNERS_ORDERED_BY_TIME_TAKEN_GROUP = "SELECT DISTINCT user.* " + " FROM lams_user_group ug "
	+ " INNER JOIN lams_user user ON ug.user_id=user.user_id "
	+ " LEFT OUTER JOIN lams_learner_progress progress "
	+ " INNER JOIN lams_progress_completed completedActivityProgress "
	+ " ON completedActivityProgress.learner_progress_id=progress.learner_progress_id "
	+ " AND completedActivityProgress.activity_id=:activityId " + " ON progress.user_id=user.user_id "
	+ " WHERE ug.group_id=:groupId "
	+ " AND (CONCAT(user.last_name, ' ', user.first_name) LIKE CONCAT('%', :searchString, '%')) "
	+ " ORDER BY TIMEDIFF(completedActivityProgress.completed_date_time, completedActivityProgress.start_date_time) "
	+ sortOrder;

//when :sortBy='mark'
final String LOAD_LEARNERS_ORDERED_BY_MARK_GROUP = "SELECT DISTINCT user.* " + " FROM lams_user_group ug "
	+ " INNER JOIN lams_user user ON ug.user_id=user.user_id "
	+ " LEFT OUTER JOIN lams_gradebook_user_activity gradebookUserActivity "
	+ " ON user.user_id=gradebookUserActivity.user_id AND gradebookUserActivity.activity_id =:activityId "
	+ " WHERE ug.group_id=:groupId "
	+ " AND (CONCAT(user.last_name, ' ', user.first_name) LIKE CONCAT('%', :searchString, '%')) "
	+ "ORDER BY gradebookUserActivity.mark " + sortOrder;

String queryString;
if (sortBy.equals("timeTaken")) {
    queryString = LOAD_LEARNERS_ORDERED_BY_TIME_TAKEN_GROUP;
} else if (sortBy.equals("mark")) {
    queryString = LOAD_LEARNERS_ORDERED_BY_MARK_GROUP;
} else {
    queryString = LOAD_LEARNERS_ORDERED_BY_NAME;
}

@SuppressWarnings("unchecked")
NativeQuery<User> query = getSession().createSQLQuery(queryString);
query.addEntity(User.class);
if (sortBy.equals("timeTaken") || sortBy.equals("mark")) {
    query.setParameter("activityId", activityId);
}
query.setParameter("groupId", groupId);
// support for custom search from a toolbar
searchString = searchString == null ? "" : searchString;
query.setParameter("searchString", searchString);
query.setFirstResult(page * size);
query.setMaxResults(size);
return query.list();
   }
 

/**
 * {@inheritDoc}
 */
@Override
public List executeNativeQuery(final String nativeQuery) {
    NativeQuery sqlQuery = sessionFactory.getCurrentSession().createNativeQuery(nativeQuery);
    return sqlQuery.list();
}
 

@SuppressWarnings("unchecked")
   @Override
   public List<Object[]> getRatingsComments(Long toolContentId, Long toolSessionId, RatingCriteria criteria, Long userId, Integer page,
    Integer size, int sorting, String searchString, boolean getByUser, IRatingService coreRatingService,
    IUserManagementService userManagementService) {

String sortingOrder = "";
switch (sorting) {
    case PeerreviewConstants.SORT_BY_NO:
	sortingOrder = " ORDER BY user.user_id";
	break;
    case PeerreviewConstants.SORT_BY_USERNAME_ASC:
	sortingOrder = " ORDER BY user.first_name ASC";
	break;
    case PeerreviewConstants.SORT_BY_USERNAME_DESC:
	sortingOrder = " ORDER BY user.first_name DESC";
	break;
    case PeerreviewConstants.SORT_BY_AVERAGE_RESULT_ASC:
	if ( criteria.isCommentRating() ) {
	    sortingOrder = " ORDER BY rating.comment ASC" ;
	} else {
	    sortingOrder = " ORDER BY rating.average_rating ASC, rating.comment ASC ";
	}
	break;
    case PeerreviewConstants.SORT_BY_AVERAGE_RESULT_DESC:
	if ( criteria.isCommentRating() ) {
	    sortingOrder = " ORDER BY rating.comment DESC" ;
	} else {
	    sortingOrder = " ORDER BY rating.average_rating DESC, rating.comment ASC ";
	}
	break;
}

String[] portraitStrings = userManagementService.getPortraitSQL("user.user_id");

   	StringBuilder bldr =  new StringBuilder(FIND_USER_RATINGS_COMMENTS_SELECT);
   	bldr.append(portraitStrings[0]);
   	bldr.append(FIND_USER_RATINGS_COMMENTS1);
   	bldr.append(portraitStrings[1]);
   	bldr.append(FIND_USER_RATINGS_COMMENTS2);
   	bldr.append(coreRatingService.getRatingSelectJoinSQL(criteria.getRatingStyle(), getByUser));
   	bldr.append(FIND_USER_RATINGS_COMMENTS3);
   	if ( ! getByUser) 
   	    bldr.append("WHERE user.user_id = :userId ");
   	
   	buildNameSearch(searchString, bldr, ! getByUser);

   	bldr.append(sortingOrder);
   	
String queryString = bldr.toString();
NativeQuery<?> query = getSession().createNativeQuery(queryString)
	.setParameter("toolContentId", toolContentId)
	.setParameter("toolSessionId", toolSessionId)
	.setParameter("ratingCriteriaId", criteria.getRatingCriteriaId());
if ( queryString.contains(":userId") ) {
	query.setParameter("userId", userId);
}
if ( page != null && size != null ) {
    query.setFirstResult(page * size).setMaxResults(size);
}
return (List<Object[]>) query.list();
   }
 

@Override
   public List<User> getUsersByLesson(Long lessonId, int page, int size, String sortBy, String sortOrder,
    String searchString) {

final String LOAD_LEARNERS_ORDERED_BY_NAME = "SELECT DISTINCT user.* "
	+ " FROM lams_lesson lesson, lams_group g, lams_user_group ug "
	+ " INNER JOIN lams_user user ON ug.user_id=user.user_id " + " WHERE lesson.lesson_id = :lessonId "
	+ " AND lesson.class_grouping_id=g.grouping_id " + " AND ug.group_id=g.group_id "
	+ " AND (CONCAT(user.last_name, ' ', user.first_name) LIKE CONCAT('%', :searchString, '%')) "
	+ "ORDER BY CONCAT(user.last_name, ' ', user.first_name) " + sortOrder;

//when :sortBy='timeTaken'
final String LOAD_LEARNERS_ORDERED_BY_TIME_TAKEN = "SELECT DISTINCT user.* "
	+ " FROM lams_lesson lesson, lams_group g, lams_user_group ug "
	+ " INNER JOIN lams_user user ON ug.user_id=user.user_id "
	+ " LEFT OUTER JOIN lams_learner_progress progress "
	+ " ON progress.user_id=user.user_id AND progress.lesson_id=:lessonId "
	+ " WHERE lesson.lesson_id = :lessonId " + " AND lesson.class_grouping_id=g.grouping_id "
	+ " AND ug.group_id=g.group_id "
	+ " AND (CONCAT(user.last_name, ' ', user.first_name) LIKE CONCAT('%', :searchString, '%')) "
	+ "ORDER BY TIMEDIFF(progress.finish_date_time, progress.start_date_time) " + sortOrder;

//when :sortBy='mark'
final String LOAD_LEARNERS_ORDERED_BY_MARK = "SELECT DISTINCT user.* "
	+ " FROM lams_lesson lesson, lams_group g, lams_user_group ug "
	+ " INNER JOIN lams_user user ON ug.user_id=user.user_id "
	+ " LEFT OUTER JOIN lams_gradebook_user_lesson gradebookUserLesson "
	+ " ON user.user_id=gradebookUserLesson.user_id AND gradebookUserLesson.lesson_id =:lessonId "
	+ " WHERE lesson.lesson_id = :lessonId " + " AND lesson.class_grouping_id=g.grouping_id "
	+ " AND ug.group_id=g.group_id "
	+ " AND (CONCAT(user.last_name, ' ', user.first_name) LIKE CONCAT('%', :searchString, '%')) "
	+ " ORDER BY gradebookUserLesson.mark " + sortOrder;

//when :sortBy='feedback'
final String LOAD_LEARNERS_ORDERED_BY_FEEDBACK = "SELECT DISTINCT user.* "
	+ " FROM lams_lesson lesson, lams_group g, lams_user_group ug "
	+ " INNER JOIN lams_user user ON ug.user_id=user.user_id "
	+ " LEFT OUTER JOIN lams_gradebook_user_lesson gradebookUserLesson "
	+ " ON user.user_id=gradebookUserLesson.user_id AND gradebookUserLesson.lesson_id =:lessonId "
	+ " WHERE lesson.lesson_id = :lessonId " + " AND lesson.class_grouping_id=g.grouping_id "
	+ " AND ug.group_id=g.group_id "
	+ " AND (CONCAT(user.last_name, ' ', user.first_name) LIKE CONCAT('%', :searchString, '%')) "
	+ " ORDER BY gradebookUserLesson.feedback " + sortOrder;

String queryString;
if (sortBy.equals("timeTaken")) {
    queryString = LOAD_LEARNERS_ORDERED_BY_TIME_TAKEN;
} else if (sortBy.equals("mark")) {
    queryString = LOAD_LEARNERS_ORDERED_BY_MARK;
} else if (sortBy.equals("feedback")) {
    queryString = LOAD_LEARNERS_ORDERED_BY_FEEDBACK;
} else {
    queryString = LOAD_LEARNERS_ORDERED_BY_NAME;
}

@SuppressWarnings("unchecked")
NativeQuery<User> query = getSession().createSQLQuery(queryString);
query.addEntity(User.class);
query.setParameter("lessonId", lessonId);
// support for custom search from a toolbar
searchString = searchString == null ? "" : searchString;
query.setParameter("searchString", searchString);
query.setFirstResult(page * size);
query.setMaxResults(size);
return query.list();
   }
 

@SuppressWarnings("unchecked")
   @Override
   public List<Object[]> getCommentsCounts(Long toolContentId, Long toolSessionId, RatingCriteria criteria,
    Integer page, Integer size, int sorting, String searchString,
    IUserManagementService userManagementService) {
String sortingOrder = "";
switch (sorting) {
    case PeerreviewConstants.SORT_BY_NO:
	sortingOrder = " ORDER BY user.user_id";
	break;
    case PeerreviewConstants.SORT_BY_USERNAME_ASC:
	sortingOrder = " ORDER BY user.first_name ASC";
	break;
    case PeerreviewConstants.SORT_BY_USERNAME_DESC:
	sortingOrder = " ORDER BY user.first_name DESC";
	break;
    case PeerreviewConstants.SORT_BY_AVERAGE_RESULT_ASC:
	sortingOrder = " ORDER BY rating.comment_count ASC";
	break;
    case PeerreviewConstants.SORT_BY_AVERAGE_RESULT_DESC:
	sortingOrder = " ORDER BY rating.comment_count DESC";
}

String[] portraitStrings = userManagementService.getPortraitSQL("user.user_id");

   	StringBuilder bldr =  new StringBuilder(COUNT_COMMENTS_FOR_SESSION_SELECT)
   		.append(portraitStrings[0])
   		.append(COUNT_COMMENTS_FOR_SESSION_FROM)
   		.append(portraitStrings[1])
   		.append(COUNT_COMMENTS_FOR_SESSION_RATING_JOIN);
   	buildNameSearch(searchString, bldr, false);
   	bldr.append(sortingOrder);
   	
String queryString = bldr.toString();
NativeQuery<?> query = getSession().createNativeQuery(queryString)
	.setParameter("toolContentId", toolContentId)
	.setParameter("toolSessionId", toolSessionId)
	.setParameter("ratingCriteriaId", criteria.getRatingCriteriaId());
if ( page != null && size != null ) {
    query.setFirstResult(page * size).setMaxResults(size);
}
return (List<Object[]>) query.list();
   }
 

@Override
   @SuppressWarnings("unchecked")
   /**
    * Will return List<[DacoUser, Integer (record count), String (notebook entry)], [DacoUser, Integer, String], ... ,
    * [DacoUser, Integer, String]>
    * where the String is the notebook entry. No notebook entries needed? Will return "null" in their place.
    */
   public List<Object[]> getUsersForTablesorter(final Long sessionId, int page, int size, int sorting,
    String searchString, boolean getNotebookEntries, ICoreNotebookService coreNotebookService,
    IUserManagementService userManagementService) {

String sortingOrder;
switch (sorting) {
    case DacoConstants.SORT_BY_USER_NAME_ASC:
	sortingOrder = "user.last_name ASC, user.first_name ASC";
	break;
    case DacoConstants.SORT_BY_USER_NAME_DESC:
	sortingOrder = "user.last_name DESC, user.first_name DESC";
	break;
    case DacoConstants.SORT_BY_NUM_RECORDS_ASC:
	sortingOrder = "record_count ASC";
	break;
    case DacoConstants.SORT_BY_NUM_RECORDS_DESC:
	sortingOrder = "record_count DESC";
	break;
    case DacoConstants.SORT_BY_NO:
    default:
	sortingOrder = "user.uid";
}

// If the session uses notebook, then get the SQL to join across to get the entries
String[] notebookEntryStrings = null;
if (getNotebookEntries) {
    notebookEntryStrings = coreNotebookService.getNotebookEntrySQLStrings(sessionId.toString(),
	    DacoConstants.TOOL_SIGNATURE, "user.user_id");
}

String[] portraitStrings = userManagementService.getPortraitSQL("user.user_id");

// Basic select for the user records
StringBuilder queryText = new StringBuilder();

queryText.append("SELECT user.* ")
	.append(notebookEntryStrings != null ? notebookEntryStrings[0] : ", NULL notebookEntry")
	.append(", COUNT(DISTINCT(record_id)) record_count ")
	.append(portraitStrings[0])
	.append(" FROM tl_ladaco10_users user ")
	.append(portraitStrings[1])
	.append(	" JOIN tl_ladaco10_sessions sess on user.session_uid = sess.uid and sess.session_id = :sessionId");

// If filtering by name add a name based where clause
buildNameSearch(queryText, searchString);

queryText.append(" LEFT JOIN tl_ladaco10_answers ans ON ans.user_uid = user.uid");

// If using notebook, add the notebook join
if (notebookEntryStrings != null) {
    queryText.append(notebookEntryStrings[1]);
}

queryText.append(" GROUP BY user.uid");

// Now specify the sort based on the switch statement above.
queryText.append(" ORDER BY " + sortingOrder);

NativeQuery<Object[]> query = getSession().createNativeQuery(queryText.toString());
query.addEntity("user", DacoUser.class).addScalar("record_count", IntegerType.INSTANCE)
	.addScalar("notebookEntry", StringType.INSTANCE)
	.addScalar("portraitId", IntegerType.INSTANCE)
	.setParameter("sessionId", sessionId.longValue())
	.setFirstResult(page * size).setMaxResults(size);
return query.list();

   }
 

@SuppressWarnings("unchecked")
   @Override
   public List<McUserMarkDTO> getPagedUsersBySession(Long sessionId, int page, int size, String sortBy,
    String sortOrder, String searchString, IUserManagementService userManagementService) {

String[] portraitStrings = userManagementService.getPortraitSQL("user.que_usr_id");

StringBuilder bldr = new StringBuilder(LOAD_USERS_SELECT)
	.append(portraitStrings[0])
	.append(LOAD_USERS_FROM)
	.append(portraitStrings[1])
	.append(LOAD_USERS_JOINWHERE)
	.append(sortOrder);

NativeQuery<Object[]> query = getSession().createSQLQuery(bldr.toString());
query.setParameter("sessionId", sessionId);
// support for custom search from a toolbar
searchString = searchString == null ? "" : searchString;
query.setParameter("searchString", searchString);
query.setParameter("sortBy", sortBy);
query.setFirstResult(page * size);
query.setMaxResults(size);
List<Object[]> list = query.list();

ArrayList<McUserMarkDTO> userDtos = new ArrayList<McUserMarkDTO>();
if (list != null && list.size() > 0) {
    for (Object[] element : list) {

	Long userUid = ((Number) element[0]).longValue();
	Long userId = ((Number) element[1]).longValue();
	String fullName = (String) element[2];
	Integer totalMark = element[3] == null ? 0 : ((Number) element[3]).intValue();
	Long portraitId = element[4] == null ? null : ((Number) element[4]).longValue();

	McUserMarkDTO userDto = new McUserMarkDTO();
	userDto.setQueUsrId(userUid.toString());
	userDto.setUserId(userId.toString());
	userDto.setFullName(fullName);
	userDto.setTotalMark(new Long(totalMark));
	userDto.setPortraitId(portraitId==null ? null : portraitId.toString());
	userDtos.add(userDto);
    }

}

return userDtos;
   }