Java Code Examples for org.hibernate.query.Query#setFirstResult()

public Query buildQuery() throws ModelDBException {
  Query query = session.createQuery(getHQLQueryString());
  setParameterInQuery(query);

  if (this.pageNumber != null
      && this.pageLimit != null
      && this.pageNumber != 0
      && this.pageLimit != 0) {
    // Calculate number of documents to skip
    int skips = this.pageLimit * (this.pageNumber - 1);
    query.setFirstResult(skips);
    query.setMaxResults(this.pageLimit);
  }

  LOGGER.debug("Final find repository query : {}", query.getQueryString());
  return query;
}
 

@Sessional
@Override
public List<String> queryVersions(Project project, String matchWith, int count) {
	CriteriaBuilder builder = getSession().getCriteriaBuilder();
	CriteriaQuery<String> criteriaQuery = builder.createQuery(String.class);
	Root<Build> root = criteriaQuery.from(Build.class);
	criteriaQuery.select(root.get(Build.PROP_VERSION)).distinct(true);
	
	Collection<Predicate> predicates = getPredicates(project, root, builder);
	predicates.add(builder.like(
			builder.lower(root.get(Build.PROP_VERSION)), 
			"%" + matchWith.toLowerCase() + "%"));
	criteriaQuery.where(predicates.toArray(new Predicate[predicates.size()]));
	criteriaQuery.orderBy(builder.asc(root.get(Build.PROP_VERSION)));

	Query<String> query = getSession().createQuery(criteriaQuery);
	query.setFirstResult(0);
	query.setMaxResults(count);
	
	return query.getResultList();
}
 

@Sessional
@Override
public Collection<Long> queryIds(Project project, EntityQuery<Build> buildQuery, 
		int firstResult, int maxResults) {
	CriteriaBuilder builder = getSession().getCriteriaBuilder();
	CriteriaQuery<Long> criteriaQuery = builder.createQuery(Long.class);
	Root<Build> root = criteriaQuery.from(Build.class);
	criteriaQuery.select(root.get(Build.PROP_ID));

	criteriaQuery.where(getPredicates(project, buildQuery.getCriteria(), root, builder));

	applyOrders(root, criteriaQuery, builder, buildQuery);

	Query<Long> query = getSession().createQuery(criteriaQuery);
	query.setFirstResult(firstResult);
	query.setMaxResults(maxResults);
	
	return query.list();
}
 

@Override
   /**
    * @see org.lamsfoundation.lams.usermanagement.service.IUserManagementService#getUsersFromOrganisation(int)
    */
   public List<User> getUsersFromOrganisation(Integer orgId, int page, int size, String sortOrder,
    String searchString) {
final String LOAD_LEARNERS_BY_ORG = "SELECT uo.user FROM UserOrganisation uo"
	+ " WHERE uo.organisation.organisationId=:orgId"
	+ " AND CONCAT(uo.user.lastName, ' ', uo.user.firstName) LIKE CONCAT('%', :searchString, '%') "
	+ " ORDER BY uo.user.lastName " + sortOrder + " , uo.user.firstName " + sortOrder;

Query<User> query = getSession().createQuery(LOAD_LEARNERS_BY_ORG, User.class);
query.setParameter("orgId", orgId);
// 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
   @SuppressWarnings("unchecked")
   public List<Subscription> getLessonEventSubscriptions(Long lessonId, Integer userId, boolean pendingOnly,
    Integer limit, Integer offset) {
String query = EventDAOHibernate.GET_LESSON_EVENT_SUBSCRIPTIONS;
if (lessonId != null) {
    query += " AND s.event.eventSessionId = :lessonId";
}
if (pendingOnly) {
    query += " AND (s.lastOperationMessage IS NULL OR s.lastOperationMessage != '"
	    + DeliveryMethodNotification.LAST_OPERATION_SEEN + "')";
}
query += " ORDER BY ISNULL(s.lastOperationMessage) DESC, uid DESC";
Query queryObject = getSession().createQuery(query);
queryObject.setParameter("userId", userId);
if (lessonId != null) {
    queryObject.setParameter("lessonId", lessonId);
}
if (limit != null) {
    queryObject.setMaxResults(limit);
}
if (offset != null) {
    queryObject.setFirstResult(offset);
}
return queryObject.list();
   }
 

@Override
public Page<UserRatingModel> selectTopPagedByTime(Instant time, SelectionOptions options) {
    Query<Long> countQuery = currentSession().createQuery(
            "SELECT COUNT(*) FROM jophiel_user_rating t1 "
                    + "LEFT OUTER JOIN jophiel_user_rating t2 "
                    + "ON (t1.userJid = t2.userJid AND t1.time < t2.time AND t2.time < :time) "
                    + "WHERE t1.time < :time AND t2.userJid IS NULL ",
            Long.class);

    countQuery.setParameter("time", time);
    long count = countQuery.getSingleResult();

    Query<UserRatingModel> dataQuery = currentSession().createQuery(
            "SELECT t1 FROM jophiel_user_rating t1 "
                    + "LEFT OUTER JOIN jophiel_user_rating t2 "
                    + "ON (t1.userJid = t2.userJid AND t1.time < t2.time AND t2.time < :time) "
                    + "WHERE t1.time < :time AND t2.userJid IS NULL "
                    + "ORDER BY t1.publicRating DESC",
            UserRatingModel.class);

    dataQuery.setParameter("time", time);
    if (options.getPageSize() > 0) {
        dataQuery.setFirstResult(options.getPageSize() * (options.getPage() - 1));
        dataQuery.setMaxResults(options.getPageSize());
    }

    List<UserRatingModel> page = dataQuery.getResultList();

    return new Page.Builder<UserRatingModel>()
            .page(page)
            .totalCount(count)
            .build();
}
 

@Override
public List<ProgramMessage> getProgramMessages( ProgramMessageQueryParams params )
{
    Query<ProgramMessage> query = getHqlQuery( params );

    if ( params.hasPaging() )
    {
        query.setFirstResult( params.getPage() );
        query.setMaxResults( params.getPageSize() );
    }

    return query.list();
}
 

@Override
public List<ProgramInstance> getProgramInstances( ProgramInstanceQueryParams params )
{
    String hql = buildProgramInstanceHql( params );

    Query<ProgramInstance> query = getQuery( hql );

    if ( params.isPaging() )
    {
        query.setFirstResult( params.getOffset() );
        query.setMaxResults( params.getPageSizeWithDefault() );
    }

    return query.list();
}
 

/**
 * {@inheritDoc}
 */
@Override
@SuppressWarnings("unchecked")
public List<T> findRangeByNamedQuery(final String namedQueryName,
                                     final int firstResult,
                                     final int maxResults,
                                     final Object... parameters) {
    Query query = sessionFactory.getCurrentSession().getNamedQuery(namedQueryName);
    query.setFirstResult(firstResult);
    query.setMaxResults(maxResults);
    setQueryParameters(query, parameters);
    return query.list();
}
 

/**
 * {@inheritDoc}
 */
@Override
@SuppressWarnings("unchecked")
public List<T> findRangeByCriteria(final String eCriteria, final int firstResult, final int maxResults, final Object... parameters) {
    Query query = sessionFactory.getCurrentSession().createQuery(eCriteria != null ? this.selectAllHql.concat(eCriteria) : this.selectAllHql);
    setQueryParameters(query, parameters);
    query.setFirstResult(firstResult);
    query.setMaxResults(maxResults);
    return query.list();
}
 

@Override
@SuppressWarnings( "unchecked" )
public < T > List< T > selectByHQL( String _db , String _hql , int _first , int _limit , String[] _param , Object[] _value,
				Class< T > _cls ) {
	Session session = _get_session( _db );
	Query< T > query = session.createQuery( _hql );
	query.setFirstResult( _first );
	query.setMaxResults( _limit );

	_set_parameter( query, _param, _value );
	return query.getResultList();
}
 

/**
 * {@inheritDoc}
 */
@Override
@SuppressWarnings("unchecked")
public List<T> findRangeByCriteria(final String eCriteria, final int firstResult, final int maxResults, final Object... parameters) {
    Query query = sessionFactory.getCurrentSession().createQuery(eCriteria != null ? this.selectAllHql.concat(eCriteria) : this.selectAllHql);
    setQueryParameters(query, parameters);
    query.setFirstResult(firstResult);
    query.setMaxResults(maxResults);
    return query.list();
}
 

protected void initQueryFromNamedDefinition(Query query, NamedQueryDefinition nqd) {
	// todo : cacheable and readonly should be Boolean rather than boolean...
	query.setCacheable( nqd.isCacheable() );
	query.setCacheRegion( nqd.getCacheRegion() );
	query.setReadOnly( nqd.isReadOnly() );

	if ( nqd.getTimeout() != null ) {
		query.setTimeout( nqd.getTimeout() );
	}
	if ( nqd.getFetchSize() != null ) {
		query.setFetchSize( nqd.getFetchSize() );
	}
	if ( nqd.getCacheMode() != null ) {
		query.setCacheMode( nqd.getCacheMode() );
	}
	if ( nqd.getComment() != null ) {
		query.setComment( nqd.getComment() );
	}
	if ( nqd.getFirstResult() != null ) {
		query.setFirstResult( nqd.getFirstResult() );
	}
	if ( nqd.getMaxResults() != null ) {
		query.setMaxResults( nqd.getMaxResults() );
	}
	if ( nqd.getFlushMode() != null ) {
		query.setHibernateFlushMode( nqd.getFlushMode() );
	}
}
 

/**
 * {@inheritDoc}
 */
@Override
public List<T> findRangeByQuery(final String hsqlQuery, final int firstResult, final int maxResults, final Object... parameters) {
    Query query = sessionFactory.getCurrentSession().createQuery(hsqlQuery);
    query.setFirstResult(firstResult);
    query.setMaxResults(maxResults);
    setQueryParameters(query, parameters);
    return query.list();
}
 

/**
 * {@inheritDoc}
 */
@Override
public List<T> findRangeByQuery(final String hsqlQuery, final int firstResult, final int maxResults, final Object... parameters) {
    Query query = sessionFactory.getCurrentSession().createQuery(hsqlQuery);
    query.setFirstResult(firstResult);
    query.setMaxResults(maxResults);
    setQueryParameters(query, parameters);
    return query.list();
}
 

@Override
public CommitPaginationDTO fetchCommitEntityList(
    Session session, ListCommitsRequest request, Long repoId) throws ModelDBException {
  StringBuilder commitQueryBuilder =
      new StringBuilder(
          "SELECT cm FROM "
              + CommitEntity.class.getSimpleName()
              + " cm LEFT JOIN cm.repository repo WHERE repo.id = :repoId ");
  if (!request.getCommitBase().isEmpty()) {
    CommitEntity baseCommitEntity =
        Optional.ofNullable(session.get(CommitEntity.class, request.getCommitBase()))
            .orElseThrow(
                () ->
                    new ModelDBException(
                        "Couldn't find base commit by sha : " + request.getCommitBase(),
                        Code.NOT_FOUND));
    Long baseTime = baseCommitEntity.getDate_created();
    commitQueryBuilder.append(" AND cm.date_created >= " + baseTime);
  }

  if (!request.getCommitHead().isEmpty()) {
    CommitEntity headCommitEntity =
        Optional.ofNullable(session.get(CommitEntity.class, request.getCommitHead()))
            .orElseThrow(
                () ->
                    new ModelDBException(
                        "Couldn't find head commit by sha : " + request.getCommitHead(),
                        Code.NOT_FOUND));
    Long headTime = headCommitEntity.getDate_created();
    commitQueryBuilder.append(" AND cm.date_created <= " + headTime);
  }

  Query<CommitEntity> commitEntityQuery =
      session.createQuery(commitQueryBuilder.append(" ORDER BY cm.date_created DESC").toString());
  commitEntityQuery.setParameter("repoId", repoId);
  if (request.hasPagination()) {
    int pageLimit = request.getPagination().getPageLimit();
    final int startPosition = (request.getPagination().getPageNumber() - 1) * pageLimit;
    commitEntityQuery.setFirstResult(startPosition);
    commitEntityQuery.setMaxResults(pageLimit);
  }

  Query countQuery = session.createQuery(commitQueryBuilder.toString());
  countQuery.setParameter("repoId", repoId);
  // TODO: improve query into count query
  Long totalRecords = (long) countQuery.list().size();

  CommitPaginationDTO commitPaginationDTO = new CommitPaginationDTO();
  commitPaginationDTO.setCommitEntities(commitEntityQuery.list());
  commitPaginationDTO.setTotalRecords(totalRecords);
  return commitPaginationDTO;
}
 

private Map<String, Object> getRootShaListByCommitsOrRepos(
    Session session, FindRepositoriesBlobs request) {

  Map<String, Object> parametersMap = new HashMap<>();

  String alias = "cm";
  StringBuilder rootQueryStringBuilder =
      new StringBuilder(" FROM ")
          .append(CommitEntity.class.getSimpleName())
          .append(" ")
          .append(alias)
          .append(" ");

  StringBuilder joinClause = new StringBuilder();
  joinClause
      .append(" INNER JOIN ")
      .append(InternalFolderElementEntity.class.getSimpleName())
      .append(" folderElm ")
      .append(" ON ");
  joinClause.append("folderElm.folder_hash = ").append(alias).append(".rootSha ");

  List<String> whereClauseList = new ArrayList<>();
  if (!request.getRepoIdsList().isEmpty()) {
    whereClauseList.add(alias + ".repository.id IN (:repoIds) ");
    parametersMap.put("repoIds", request.getRepoIdsList());
  }
  if (!request.getCommitsList().isEmpty()) {
    whereClauseList.add(alias + ".commit_hash IN (:commitHashList)");
    parametersMap.put("commitHashList", request.getCommitsList());
  }
  StringBuilder whereClause = new StringBuilder();
  setPredicatesWithQueryOperator(whereClause, " AND ", whereClauseList.toArray(new String[0]));

  // Order by clause
  StringBuilder orderClause =
      new StringBuilder(" ORDER BY ")
          .append(alias)
          .append(".")
          .append(ModelDBConstants.DATE_CREATED)
          .append(" DESC");

  StringBuilder finalQueryBuilder = new StringBuilder();
  if (!joinClause.toString().isEmpty()) {
    finalQueryBuilder.append("SELECT ").append(alias).append(".rootSha ");
  }
  finalQueryBuilder.append(rootQueryStringBuilder);
  finalQueryBuilder.append(joinClause);
  if (!whereClause.toString().isEmpty()) {
    finalQueryBuilder.append(" WHERE ").append(whereClause);
  }
  finalQueryBuilder.append(orderClause);

  // Build count query
  StringBuilder countQueryBuilder = new StringBuilder();
  if (!joinClause.toString().isEmpty()) {
    countQueryBuilder.append("SELECT COUNT(").append(alias).append(") ");
  } else {
    countQueryBuilder.append("SELECT COUNT(*) ");
  }
  countQueryBuilder.append(rootQueryStringBuilder);
  countQueryBuilder.append(joinClause);
  if (!whereClause.toString().isEmpty()) {
    countQueryBuilder.append(" WHERE ").append(whereClause);
  }

  Query query = session.createQuery(finalQueryBuilder.toString());
  Query countQuery = session.createQuery(countQueryBuilder.toString());
  if (!parametersMap.isEmpty()) {
    parametersMap.forEach(
        (key, value) -> {
          if (value instanceof List) {
            List<Object> objectList = (List<Object>) value;
            query.setParameterList(key, objectList);
            countQuery.setParameterList(key, objectList);
          } else {
            query.setParameter(key, value);
            countQuery.setParameter(key, value);
          }
        });
  }

  LOGGER.debug("Final find commit root_sha query : {}", query.getQueryString());
  if (request.getPageNumber() != 0 && request.getPageLimit() != 0) {
    // Calculate number of documents to skip
    int skips = request.getPageLimit() * (request.getPageNumber() - 1);
    query.setFirstResult(skips);
    query.setMaxResults(request.getPageLimit());
  }
  List<String> resultSet = query.list();

  Map<String, Object> responseMap = new HashMap<>();
  responseMap.put("result", new HashSet<>(resultSet));
  responseMap.put("count", countQuery.uniqueResult());
  return responseMap;
}
 

/**
 * Populates criteria arguments for the given target class and arguments map
 *
 * @param entity The {@link org.grails.datastore.mapping.model.PersistentEntity} instance
 * @param query  The criteria instance
 * @param argMap The arguments map
 */
@SuppressWarnings("rawtypes")
public static void populateArgumentsForCriteria(
        PersistentEntity entity,
        Query query,
        Map argMap,
        ConversionService conversionService,
        boolean useDefaultMapping) {
    Integer maxParam = null;
    Integer offsetParam = null;
    if (argMap.containsKey(DynamicFinder.ARGUMENT_MAX)) {
        maxParam = conversionService.convert(argMap.get(DynamicFinder.ARGUMENT_MAX), Integer.class);
    }
    if (argMap.containsKey(DynamicFinder.ARGUMENT_OFFSET)) {
        offsetParam = conversionService.convert(argMap.get(DynamicFinder.ARGUMENT_OFFSET), Integer.class);
    }
    if (argMap.containsKey(DynamicFinder.ARGUMENT_FETCH_SIZE)) {
        query.setFetchSize(conversionService.convert(argMap.get(DynamicFinder.ARGUMENT_FETCH_SIZE), Integer.class));
    }
    if (argMap.containsKey(DynamicFinder.ARGUMENT_TIMEOUT)) {
        query.setTimeout(conversionService.convert(argMap.get(DynamicFinder.ARGUMENT_TIMEOUT), Integer.class));
    }
    if (argMap.containsKey(DynamicFinder.ARGUMENT_FLUSH_MODE)) {
        query.setHibernateFlushMode(convertFlushMode(argMap.get(DynamicFinder.ARGUMENT_FLUSH_MODE)));
    }
    if (argMap.containsKey(DynamicFinder.ARGUMENT_READ_ONLY)) {
        query.setReadOnly(ClassUtils.getBooleanFromMap(DynamicFinder.ARGUMENT_READ_ONLY, argMap));
    }

    final int max = maxParam == null ? -1 : maxParam;
    final int offset = offsetParam == null ? -1 : offsetParam;
    if (max > -1) {
        query.setMaxResults(max);
    }
    if (offset > -1) {
        query.setFirstResult(offset);
    }
    if (ClassUtils.getBooleanFromMap(DynamicFinder.ARGUMENT_LOCK, argMap)) {
        query.setLockMode(LockModeType.PESSIMISTIC_WRITE);
        query.setCacheable(false);
    } else {
        if (argMap.containsKey(DynamicFinder.ARGUMENT_CACHE)) {
            query.setCacheable(ClassUtils.getBooleanFromMap(DynamicFinder.ARGUMENT_CACHE, argMap));
        } else {
            cacheCriteriaByMapping(entity.getJavaClass(), query);
        }
    }

}
 

@Override
@SuppressWarnings( "unchecked" )
public List<MessageConversation> getMessageConversations( User user, MessageConversationStatus status,
    boolean followUpOnly, boolean unreadOnly,
    Integer first, Integer max )
{
    Assert.notNull( user, "User must be specified" );

    getSession().enableFilter( "userMessageUser" ).setParameter( "userid", user.getId() );

    String hql = "from MessageConversation mc " +
        "inner join mc.userMessages as um " +
        "left join mc.user as ui " +
        "left join mc.lastSender as ls ";

    if ( status != null )
    {
        hql += "where status = :status ";
    }

    if ( followUpOnly )
    {
        hql += (status != null ? "and" : "where") + " um.followUp = true ";
    }

    if ( unreadOnly )
    {
        hql += (status != null || followUpOnly ? "and" : "where") + " um.read = false ";
    }

    hql += "order by mc.lastMessage desc ";

    Query query = getQuery( hql );

    if ( status != null )
    {
        query.setParameter( "status", status.name() );
    }

    if ( first != null && max != null )
    {
        query.setFirstResult( first );
        query.setMaxResults( max );
    }

    return (List<MessageConversation>) query.list()
        .stream()
        .map( o -> mapRowToMessageConversations( (Object[]) o ) )
        .collect( Collectors.toList() );
}
 

@Override
public DatasetVersion createDatasetVersion(
    CreateDatasetVersion request, Dataset dataset, UserInfo userInfo)
    throws InvalidProtocolBufferException {

  try (Session session = ModelDBHibernateUtil.getSessionFactory().openSession()) {
    createDatasetVersionLock.lock();

    String lastDatasetVersionQueryStr =
        DATASET_VERSION_BY_DATA_SET_IDS_QUERY + " ORDER BY ds.version DESC";
    Query lastDatasetVersionQuery = session.createQuery(lastDatasetVersionQueryStr);
    lastDatasetVersionQuery.setParameterList(
        "datasetIds", Collections.singletonList(dataset.getId()));
    lastDatasetVersionQuery.setFirstResult(0);
    lastDatasetVersionQuery.setMaxResults(1);
    List<DatasetVersionEntity> datasetVersionEntities = lastDatasetVersionQuery.list();
    DatasetVersion existingDatasetVersion =
        datasetVersionEntities.size() > 0 ? datasetVersionEntities.get(0).getProtoObject() : null;

    List<DatasetVersion> datasetVersionList =
        getDatasetVersionFromRequest(authService, request, userInfo, existingDatasetVersion);

    if (datasetVersionList.size() == 1) {
      return datasetVersionList.get(0);
    } else {
      DatasetVersion datasetVersion = datasetVersionList.get(1);
      if (checkDatasetVersionAlreadyExist(session, datasetVersion)) {
        Status status =
            Status.newBuilder()
                .setCode(Code.ALREADY_EXISTS_VALUE)
                .setMessage(
                    "Dataset Version being logged already exists. existing datasetVersion : "
                        + datasetVersion.getVersion()
                        + " in dataset "
                        + datasetVersion.getDatasetId())
                .build();
        throw StatusProto.toStatusRuntimeException(status);
      }
      createRoleBindingsForDatasetVersion(userInfo, datasetVersion);

      DatasetVersionEntity datasetVersionEntity =
          RdbmsUtils.generateDatasetVersionEntity(datasetVersion);
      Transaction transaction = session.beginTransaction();
      session.save(datasetVersionEntity);
      transaction.commit();
      LOGGER.debug("DatasetVersion created successfully");
      return datasetVersionEntity.getProtoObject();
    }
  } catch (Exception ex) {
    if (ModelDBUtils.needToRetry(ex)) {
      if (createDatasetVersionLock.isLocked()) {
        createDatasetVersionLock.unlock();
      }
      return createDatasetVersion(request, dataset, userInfo);
    } else {
      throw ex;
    }
  } finally {
    if (createDatasetVersionLock.isLocked()) {
      createDatasetVersionLock.unlock();
    }
  }
}