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

@Override
   public int countAttemptsPerOption(Long optionUid) {
String COUNT_ATTEMPTS_BY_OPTION_UID = "SELECT count(*) "
	+ "FROM tl_laasse10_assessment_result AS result "
	+ "JOIN tl_laasse10_question_result AS questionResult ON result.uid = questionResult.result_uid "
	+ "JOIN tl_laasse10_option_answer AS optionAnswer ON questionResult.uid = optionAnswer.question_result_uid AND optionAnswer.answer_boolean=1 AND optionAnswer.question_option_uid = :optionUid "
	+ "WHERE (result.finish_date IS NOT NULL) AND result.latest=1";

NativeQuery<?> query = getSession().createNativeQuery(COUNT_ATTEMPTS_BY_OPTION_UID);
query.setParameter("optionUid", optionUid);
List list = query.list();
if (list == null || list.size() == 0) {
    return 0;
}
return ((Number) list.get(0)).intValue();
   }
 

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

@Override
   public void delete(Object object) {
// remove references to the removed page
WikiPage removedWikiPage = (WikiPage) object;
String title = removedWikiPage.getTitle();
String escapedTitle = WikiPageDTO.javaScriptEscape(title);
String codeToReplace = WikiPageDAO.CHANGE_WIKI_JAVASCRIPT_METHOD.replace("?", escapedTitle);
String replacementCode = "#";

NativeQuery<?> query = getSessionFactory().getCurrentSession().createNativeQuery(REMOVE_WIKI_REFERENCES);
query.setParameter("codeToReplace", codeToReplace);
query.setParameter("replacementCode", replacementCode);
query.setParameter("parentWikiUid", removedWikiPage.getParentWiki().getUid());

super.delete(object);
query.executeUpdate();
   }
 

@SuppressWarnings("unchecked")
   @Override
   public List<Number> getRawLeaderMarksByToolContentId(Long toolContentId) {
NativeQuery<?> query = getSession().createNativeQuery(LOAD_MARKS);
query.setParameter("toolContentId", toolContentId);
return (List<Number>) query.list();
   }
 

@Test
public void whenNamedNativeQueryIsCalledUsingGetNamedNativeQuery_ThenOk() {
    @SuppressWarnings("rawtypes")
    NativeQuery query = session.getNamedNativeQuery("DeptEmployee_FindByEmployeeName");
    query.setParameter("name", "John Wayne");
    DeptEmployee result = (DeptEmployee) query.getSingleResult();
    Assert.assertNotNull(result);
    Assert.assertEquals("001", result.getEmployeeNumber());
}
 

@SuppressWarnings("unchecked")
   @Override
   public List<Number> getRawLeaderMarksByToolContentId(Long toolContentId) {

NativeQuery<Number> query = getSession().createNativeQuery(LOAD_MARKS_FOR_LEADERS);
query.setParameter("toolContentId", toolContentId);
List<Number> list = query.list();
return list;
   }
 

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

@Override
   @SuppressWarnings("rawtypes")
   public int getCountUsersForOpenTextEntries(Long sessionUid, Long toolContentId, String searchStringVote,
    String searchStringUsername) {

NativeQuery query;
StringBuilder queryText = new StringBuilder(COUNT_USERS_OPEN_TEXT_BY_SESSION_UID);

if (sessionUid != null) {
    // get all the users who did an open text reply, restricting by session
    queryText.append(FIND_USER_OPEN_TEXT_SESSION_UID_ADD);
    buildCombinedSearch(searchStringVote, searchStringUsername, queryText);
    query = getSession().createSQLQuery(queryText.toString());
    query.setParameter("sessionUid", sessionUid);

} else {

    // get all the users for this content (more than one session potentially)
    queryText.append(FIND_USER_OPEN_TEXT_CONTENT_UID_ADD);
    buildCombinedSearch(searchStringVote, searchStringUsername, queryText);
    query = getSession().createSQLQuery(queryText.toString());
    query.setParameter("toolContentId", toolContentId);

}

List list = query.list();
if (list == null || list.size() == 0) {
    return 0;
}
return ((Number) list.get(0)).intValue();
   }
 

@Override
   public List<Number> getRawLeaderMarksByToolContentId(Long toolContentId) {
@SuppressWarnings("unchecked")
NativeQuery<Number> query = getSession().createNativeQuery(LOAD_MARKS_FOR_LEADERS);
query.setParameter("toolContentId", toolContentId);
List<Number> list = query.list();
return list;
   }
 

@Override
@SuppressWarnings("unchecked")
public boolean exists( PotentialDuplicate potentialDuplicate )
{
    NativeQuery<BigInteger> query;
    if ( potentialDuplicate.getTeiA() == null )
    {
        return false;
    }

    if ( potentialDuplicate.getTeiB() == null )
    {
        query = getSession().createNativeQuery( "select count(potentialduplicateid) from potentialduplicate pd " +
            "where pd.teiA = :teia limit 1" );
        query.setParameter( "teia", potentialDuplicate.getTeiA() );
    }
    else
    {
        query = getSession().createNativeQuery( "select count(potentialduplicateid) from potentialduplicate pd " +
            "where (pd.teiA = :teia and pd.teiB = :teib) or (pd.teiA = :teib and pd.teiB = :teia) limit 1" );

        query.setParameter( "teia", potentialDuplicate.getTeiA() );
        query.setParameter( "teib", potentialDuplicate.getTeiB() );
    }

    return query.getSingleResult().intValue() != 0;
}
 

@Override
   @SuppressWarnings({ "unchecked", "rawtypes", "deprecation" })
   public Collection<TaskListUserDTO> getPagedUsersBySession(Long sessionId, int page, int size, String sortBy,
    String sortOrder, String searchString, IUserManagementService userManagementService) {

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

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

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

//group by userId as long as it returns all completed visitLogs for each user
HashMap<Long, TaskListUserDTO> userIdToUserDto = new LinkedHashMap<Long, TaskListUserDTO>();
if (list != null && list.size() > 0) {
    for (Object[] element : list) {

	Long userId = ((Number) element[0]).longValue();
	String fullName = (String) element[1];
	boolean isVerifiedByMonitor =  element[2] == null ? false : (Boolean) element[2];
	Long completedTaskUid = element[3] == null ? 0 : ((Number) element[3]).longValue();
	Long portraitId = element[4] == null ? null : ((Number) element[4]).longValue();

	TaskListUserDTO userDto = (userIdToUserDto.get(userId) == null) ? new TaskListUserDTO()
		: userIdToUserDto.get(userId);
	userDto.setUserId(userId);
	userDto.setFullName(fullName);
	userDto.setVerifiedByMonitor(isVerifiedByMonitor);
	userDto.getCompletedTaskUids().add(completedTaskUid);
	userDto.setPortraitId(portraitId);

	userIdToUserDto.put(userId, userDto);
    }
}

return userIdToUserDto.values();
   }
 

@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
   @SuppressWarnings("unchecked")
   /**
    * Gets the basic details about an attempt for a nomination. questionUid must not be null, sessionUid may be NULL.
    * This is
    * unusual for these methods - usually sessionId may not be null. In this case if sessionUid is null then you get
    * the values for the whole class, not just the group.
    *
    * Will return List<[login (String), fullname(String), attemptTime(Timestamp]>
    */
   public List<Object[]> getUserAttemptsForTablesorter(Long sessionUid, Long questionUid, int page, int size,
    int sorting, String searchString, IUserManagementService userManagementService) {
String sortingOrder;
switch (sorting) {
    case VoteAppConstants.SORT_BY_NAME_ASC:
	sortingOrder = "user.fullname ASC";
	break;
    case VoteAppConstants.SORT_BY_NAME_DESC:
	sortingOrder = "user.fullname DESC";
	break;
    case VoteAppConstants.SORT_BY_DATE_ASC:
	sortingOrder = "attempt.attempt_time ASC";
	break;
    case VoteAppConstants.SORT_BY_DATE_DESC:
	sortingOrder = "attempt.attempt_time DESC";
	break;
    default:
	sortingOrder = "user.uid";
}

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

// Basic select for the user records
StringBuilder queryText = new StringBuilder(FIND_USER_ANSWERS_BY_QUESTION_UID_SELECT).append(portraitStrings[0])
	.append(FIND_USER_ANSWERS_BY_QUESTION_UID_FROM).append(portraitStrings[1]);

if (sessionUid != null) {
    queryText.append(FIND_USER_ANSWERS_BY_QUESTION_UID_SESSION_ADDITION);
}

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

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

NativeQuery<Object[]> query = getSession().createSQLQuery(queryText.toString());
query.addScalar("user_id", IntegerType.INSTANCE).addScalar("username", StringType.INSTANCE)
	.addScalar("fullname", StringType.INSTANCE).addScalar("attemptTime", TimestampType.INSTANCE)
	.addScalar("portraitId", IntegerType.INSTANCE).setParameter("questionUid", questionUid)
	.setFirstResult(page * size).setMaxResults(size);
if (sessionUid != null) {
    query.setParameter("sessionUid", sessionUid);
}

return query.list();
   }
 

@Override
   @SuppressWarnings("unchecked")
   /**
    * Gets the details about an open text entry. Either sessionUid or toolContentId must be supplied - if sessionUid is
    * supplied
    * then it will be restricted to that session. Due to the large number of fields needed, a DTO will be returned.
    *
    * Will return List<OpenTextAnswerDTO>
    */
   public List<OpenTextAnswerDTO> getUserOpenTextAttemptsForTablesorter(Long sessionUid, Long toolContentId, int page,
    int size, int sorting, String searchStringVote, String searchStringUsername,
    IUserManagementService userManagementService) {
String sortingOrder;
switch (sorting) {
    case VoteAppConstants.SORT_BY_NAME_ASC:
	sortingOrder = "user.fullname ASC";
	break;
    case VoteAppConstants.SORT_BY_NAME_DESC:
	sortingOrder = "user.fullname DESC";
	break;
    case VoteAppConstants.SORT_BY_DATE_ASC:
	sortingOrder = "attempt.attempt_time ASC";
	break;
    case VoteAppConstants.SORT_BY_DATE_DESC:
	sortingOrder = "attempt.attempt_time DESC";
	break;
    case VoteAppConstants.SORT_BY_ENTRY_ASC:
	sortingOrder = "attempt.userEntry ASC";
	break;
    case VoteAppConstants.SORT_BY_ENTRY_DESC:
	sortingOrder = "attempt.userEntry DESC";
	break;
    case VoteAppConstants.SORT_BY_VISIBLE_ASC:
	sortingOrder = "attempt.visible ASC";
	break;
    case VoteAppConstants.SORT_BY_VISIBLE_DESC:
	sortingOrder = "attempt.visible DESC";
	break;
    default:
	sortingOrder = "user.uid";
}

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

// Basic select for the user records
StringBuilder queryText = new StringBuilder(FIND_USER_OPEN_TEXT_SELECT).append(portraitStrings[0])
	.append(FIND_USER_OPEN_TEXT_FROM);

if (sessionUid != null) {
    queryText.append(FIND_USER_OPEN_TEXT_SESSION_UID_ADD);
} else {
    queryText.append(FIND_USER_OPEN_TEXT_CONTENT_UID_ADD);
}
queryText.append(portraitStrings[1]);

// If filtering by name/entry add a where clause
buildCombinedSearch(searchStringVote, searchStringUsername, queryText);

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

NativeQuery<OpenTextAnswerDTO> query = getSession().createSQLQuery(queryText.toString());
query.addScalar("userUid", LongType.INSTANCE).addScalar("login", StringType.INSTANCE)
	.addScalar("fullName", StringType.INSTANCE).addScalar("userEntryUid", LongType.INSTANCE)
	.addScalar("userEntry", StringType.INSTANCE).addScalar("attemptTime", TimestampType.INSTANCE)
	.addScalar("visible", BooleanType.INSTANCE).addScalar("portraitId", LongType.INSTANCE)
	.setFirstResult(page * size).setMaxResults(size)
	.setResultTransformer(Transformers.aliasToBean(OpenTextAnswerDTO.class));

if (sessionUid != null) {
    query.setParameter("sessionUid", sessionUid);
} else {
    query.setParameter("toolContentId", toolContentId);
}

return query.list();
   }
 

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

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

StringBuilder bldr = new StringBuilder(LOAD_USERS_ORDERED_BY_SESSION_QUESTION_SELECT)
	.append(portraitStrings[0])
	.append(LOAD_USERS_ORDERED_BY_SESSION_QUESTION_FROM)
	.append(portraitStrings[1])
	.append(LOAD_USERS_ORDERED_BY_SESSION_QUESTION_JOIN);
if ( "grade".equalsIgnoreCase(sortBy) )
    bldr.append(LOAD_USERS_ORDERED_ORDER_BY_RESULT);
else
    bldr.append(LOAD_USERS_ORDERED_ORDER_BY_NAME);
bldr.append(sortOrder);

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

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

	Long questionResultUid = ((Number) element[0]).longValue();
	String firstName = (String) element[1];
	String lastName = (String) element[2];
	String login = (String) element[3];
	float grade = element[4] == null ? 0 : ((Number) element[4]).floatValue();
	Long portraitId  = element[5] == null ? null : ((Number) element[5]).longValue();

	AssessmentUserDTO userDto = new AssessmentUserDTO();
	userDto.setQuestionResultUid(questionResultUid);
	;
	userDto.setFirstName(firstName);
	userDto.setLastName(lastName);
	userDto.setLogin(login);
	userDto.setGrade(grade);
	userDto.setPortraitId(portraitId);
	userDtos.add(userDto);
    }

}

return userDtos;
   }
 

@Override
   @SuppressWarnings({ "unchecked", "rawtypes", "deprecation" })
   public Collection<TaskListUserDTO> getPagedUsersBySessionAndItem(Long sessionId, Long taskListItemUid, int page,
    int size, String sortBy, String sortOrder, String searchString) {

String LOAD_USERS = "SELECT user.user_id, CONCAT(user.last_name, ' ', user.first_name), visitLog.complete, visitLog.access_date"
	+ " FROM tl_latask10_user user" + " INNER JOIN tl_latask10_session session"
	+ " ON user.session_uid=session.uid" +

	" LEFT OUTER JOIN tl_latask10_item_log visitLog " + " ON visitLog.user_uid = user.uid"
	+ "   AND visitLog.taskList_item_uid = :taskListItemUid" +

	" WHERE session.session_id = :sessionId "
	+ " AND (CONCAT(user.last_name, ' ', user.first_name) LIKE CONCAT('%', :searchString, '%')) "
	+ " ORDER BY " + " CASE "
	+ " WHEN :sortBy='userName' THEN CONCAT(user.last_name, ' ', user.first_name) "
	+ " WHEN :sortBy='completed' THEN visitLog.complete "
	+ " WHEN :sortBy='accessDate' THEN visitLog.access_date " + " END " + sortOrder;

NativeQuery query = getSession().createNativeQuery(LOAD_USERS);
query.setParameter("sessionId", sessionId);
query.setParameter("taskListItemUid", taskListItemUid);
// 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();

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

	Long userId = ((Number) element[0]).longValue();
	String fullName = (String) element[1];
	boolean isCompleted = element[2] == null ? false : (Boolean) element[2];
	Date accessDate = element[3] == null ? null : new Date(((Timestamp) element[3]).getTime());

	TaskListUserDTO userDto = new TaskListUserDTO();
	userDto.setUserId(userId);
	userDto.setFullName(fullName);
	userDto.setCompleted(isCompleted);
	userDto.setAccessDate(accessDate);
	;

	userDtos.add(userDto);
    }
}

return userDtos;
   }
 

@SuppressWarnings("unchecked")
   @Override
   public List<AssessmentUserDTO> getPagedUsersBySession(Long sessionId, int page, int size, String sortBy,
    String sortOrder, String searchString, IUserManagementService userManagementService) {
String[] portraitStrings = userManagementService.getPortraitSQL("user.user_id");

StringBuilder bldr = new StringBuilder(LOAD_USERS_ORDERED_BY_SESSION_SELECT)
	.append(portraitStrings[0])
	.append(LOAD_USERS_ORDERED_BY_SESSION_FROM)
	.append(portraitStrings[1])
	.append(LOAD_USERS_ORDERED_BY_SESSION_JOIN);
if ( "total".equalsIgnoreCase(sortBy) )
    bldr.append(LOAD_USERS_ORDERED_ORDER_BY_TOTAL);
else
    bldr.append(LOAD_USERS_ORDERED_ORDER_BY_NAME);
bldr.append(sortOrder);

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

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

	Long userId = ((Number) element[0]).longValue();
	String firstName = (String) element[1];
	String lastName = (String) element[2];
	String login = (String) element[3];
	float grade = element[4] == null ? 0 : ((Number) element[4]).floatValue();
	Long portraitId = element[5] == null ? null : ((Number) element[5]).longValue();

	AssessmentUserDTO userDto = new AssessmentUserDTO();
	userDto.setUserId(userId);
	userDto.setFirstName(firstName);
	userDto.setLastName(lastName);
	userDto.setLogin(login);
	userDto.setGrade(grade);
	userDto.setPortraitId(portraitId);
	userDtos.add(userDto);
    }

}

return userDtos;
   }
 

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

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