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

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

@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 List<TrackedEntityInstance> getTrackedEntityInstances( TrackedEntityInstanceQueryParams params )
{
    String hql = buildTrackedEntityInstanceHql( params );

    //If it is a sync job running a query, I need to adjust an HQL a bit, because I am adding 2 joins and don't want duplicates in results
    if ( params.isSynchronizationQuery() )
    {
        hql = hql.replaceFirst( "select tei from", "select distinct tei from" );
    }

    Query<TrackedEntityInstance> query = getQuery( hql );

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

    return query.list();
}
 

@Override
public Reservation getLastReservation() {
    Reservation lastReservation = null;
    try {
        session = dataSourceFactory.getSessionFactory().openSession();
        beginTransactionIfAllowed(session);
        Query<Reservation> query = session.createQuery("from Reservation order by Id DESC", Reservation.class);
        query.setMaxResults(1);
        lastReservation = query.getSingleResult();

        logging.setMessage("ReservationDaoImpl -> fetching last reservation...");

    } catch (NoResultException e) {
        logging.setMessage("ReservationDaoImpl Error -> " + e.getLocalizedMessage());
    } finally {
        session.close();
    }
    return lastReservation;
}
 

@Override
public Reservation findReservationByAgencyRefNo(String agencyRefNo) {
    Reservation reservationByRef = null;
    try {
        session = dataSourceFactory.getSessionFactory().openSession();
        beginTransactionIfAllowed(session);
        Query<Reservation> query = session.createQuery("from Reservation where agencyRefNo=:agencyRefNo", Reservation.class);
        query.setParameter("agencyRefNo", agencyRefNo);
        query.setMaxResults(1);
        reservationByRef = query.getSingleResult();

        logging.setMessage("ReservationDaoImpl -> fetching reservation by agency referance number...");

    } catch (NoResultException e) {
        final InformationFrame frame = new InformationFrame();
        frame.setMessage("There is no reservation with this agency referance number!");
        frame.setVisible(true);
    } finally {
        session.close();
    }
    return reservationByRef;
}
 

@Override
public Customer getSinlgeCustomerByReservId(long id, String name) {
	Customer theCustomer = null;
	try {
		session = dataSourceFactory.getSessionFactory().openSession();
		beginTransactionIfAllowed(session);
		Query<Customer> query = session.createQuery("from Customer where ReservationId=:theId and FirstName=:name", Customer.class);
		query.setParameter("theId", id);
		query.setParameter("name", name);
		query.setMaxResults(1);
		
		theCustomer = query.getSingleResult();
		logging.setMessage("CustomerDaoImpl -> fetched customer successfully :"+theCustomer.toString());
                       
	} catch (NoResultException e) {
		final InformationFrame frame = new InformationFrame();
		frame.setMessage("Customers not found!");
		frame.setVisible(true);
	} finally {
		session.close();
	}
          return theCustomer;
}
 

@Override
public Customer findCustomerByName(String name, String lastName) {
               Customer customer = null;
	try {
		
		session = dataSourceFactory.getSessionFactory().openSession();
		beginTransactionIfAllowed(session);
		Query<Customer> query = session.createQuery("from Customer where FirstName=:name and LastName=:lastName", Customer.class);
		query.setParameter("name", name);
		query.setParameter("lastName", lastName);
		query.setMaxResults(1);
                       
		customer = query.getSingleResult();
		logging.setMessage("CustomerDaoImpl -> fetching customer with name :" + name);
	} catch (NoResultException e) {
		final InformationFrame frame = new InformationFrame();
		frame.setMessage("Customers not found! :" +e.getLocalizedMessage());
		frame.setVisible(true);
	} finally {
		session.close();
	}
               return customer;
}
 

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

/**
 * {@inheritDoc}
 */
@Override
public T findSingleByCriteria(final String eCriteria, final Object... parameters) {
    Query query = sessionFactory.getCurrentSession().createQuery(eCriteria != null ? this.selectAllHql.concat(eCriteria) : this.selectAllHql);
    setQueryParameters(query, parameters);
    query.setMaxResults(1);
    final List<T> rez = query.list();
    if (!rez.isEmpty()) {
        return rez.get(0);
    }
    return null;
}
 

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

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

@Override
public Optional<Reservation> findReservationById(long theId) {
	session = dataSourceFactory.getSessionFactory().openSession();
	beginTransactionIfAllowed(session);
	final Query<Reservation> query = session.createQuery("from Reservation where id=:theId", Reservation.class);
	query.setParameter("theId", theId);
	query.setMaxResults(1);
	Optional<Reservation> theReservation = query.uniqueResultOptional();
	session.close();
	logging.setMessage("ReservationDaoImpl -> fetching reservation by Id...");

	return theReservation;
}
 

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

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

/**
 * {@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 Stream<T> getResultStream() {
    Query<T> query = session.createQuery(jql, type);
    if (fetchSize >= 0) {
        query.setFetchSize(fetchSize);
    }
    if (maxResults >= 0) {
        query.setMaxResults(maxResults);
    }
    query.setReadOnly(true);
    parameters.entrySet().stream().forEach(e -> query.setParameter(e.getKey(), e.getValue()));
    return query.stream();
}
 

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

/**
 * 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
protected void initQueryFromNamedDefinition(Query query, NamedQueryDefinition namedQueryDefinition) {
	super.initQueryFromNamedDefinition( query, namedQueryDefinition );

	if ( namedQueryDefinition.isCacheable() ) {
		query.setHint( QueryHints.HINT_CACHEABLE, true );
		if ( namedQueryDefinition.getCacheRegion() != null ) {
			query.setHint( QueryHints.HINT_CACHE_REGION, namedQueryDefinition.getCacheRegion() );
		}
	}

	if ( namedQueryDefinition.getCacheMode() != null ) {
		query.setHint( QueryHints.HINT_CACHE_MODE, namedQueryDefinition.getCacheMode() );
	}

	if ( namedQueryDefinition.isReadOnly() ) {
		query.setHint( QueryHints.HINT_READONLY, true );
	}

	if ( namedQueryDefinition.getTimeout() != null ) {
		query.setHint( QueryHints.SPEC_HINT_TIMEOUT, namedQueryDefinition.getTimeout() * 1000 );
	}

	if ( namedQueryDefinition.getFetchSize() != null ) {
		query.setHint( QueryHints.HINT_FETCH_SIZE, namedQueryDefinition.getFetchSize() );
	}

	if ( namedQueryDefinition.getComment() != null ) {
		query.setHint( QueryHints.HINT_COMMENT, namedQueryDefinition.getComment() );
	}

	if ( namedQueryDefinition.getFirstResult() != null ) {
		query.setFirstResult( namedQueryDefinition.getFirstResult() );
	}

	if ( namedQueryDefinition.getMaxResults() != null ) {
		query.setMaxResults( namedQueryDefinition.getMaxResults() );
	}

	if ( namedQueryDefinition.getLockOptions() != null ) {
		if ( namedQueryDefinition.getLockOptions().getLockMode() != null ) {
			query.setLockMode(
					LockModeTypeHelper.getLockModeType( namedQueryDefinition.getLockOptions().getLockMode() )
			);
		}
	}

	if ( namedQueryDefinition.getFlushMode() != null ) {
		query.setHibernateFlushMode( namedQueryDefinition.getFlushMode() );
	}
}
 

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