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

@Override
public List<ValidationResult> getValidationResults( OrganisationUnit orgUnit,
    boolean includeOrgUnitDescendants, Collection<ValidationRule> validationRules, Collection<Period> periods )
{
    if ( isEmpty( validationRules ) || isEmpty( periods ) )
    {
        return new ArrayList<>();
    }

    String orgUnitFilter = orgUnit == null ? "" : "vr.organisationUnit.path like :orgUnitPath and ";

    Query<ValidationResult> query = getQuery( "from ValidationResult vr where " + orgUnitFilter + "vr.validationRule in :validationRules and vr.period in :periods " );

    if ( orgUnit != null )
    {
        query.setParameter( "orgUnitPath", orgUnit.getPath()
            + ( includeOrgUnitDescendants ? "%" : "" ) );
    }

    query.setParameter( "validationRules", validationRules );
    query.setParameter( "periods", periods );

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

@SuppressWarnings("unchecked")
private void fillStatus(Project project, Collection<ObjectId> commitIds, 
		Map<ObjectId, Map<String, Collection<Status>>> commitStatuses) {
	Query<?> query = getSession().createQuery("select commitHash, jobName, status from Build "
			+ "where project=:project and commitHash in :commitHashes");
	query.setParameter("project", project);
	query.setParameter("commitHashes", commitIds.stream().map(it->it.name()).collect(Collectors.toList()));
	for (Object[] row: (List<Object[]>)query.list()) {
		ObjectId commitId = ObjectId.fromString((String) row[0]);
		String jobName = (String) row[1];
		Status status = (Status) row[2];
		Map<String, Collection<Status>> commitStatus = commitStatuses.get(commitId);
		if (commitStatus == null) {
			commitStatus = new HashMap<>();
			commitStatuses.put(commitId, commitStatus);
		}
		Collection<Status> jobStatus = commitStatus.get(jobName);
		if (jobStatus == null) {
			jobStatus = new HashSet<>();
			commitStatus.put(jobName, jobStatus);
		}
		jobStatus.add(status);
	}
}
 

private static S3DatasetBlob getS3Blob(Session session, String blobHash) throws ModelDBException {
  String s3ComponentQueryHQL =
      "From "
          + S3DatasetComponentBlobEntity.class.getSimpleName()
          + " s3 WHERE s3.id.s3_dataset_blob_id = :blobShas";

  Query<S3DatasetComponentBlobEntity> s3ComponentQuery = session.createQuery(s3ComponentQueryHQL);
  s3ComponentQuery.setParameter("blobShas", blobHash);
  List<S3DatasetComponentBlobEntity> datasetComponentBlobEntities = s3ComponentQuery.list();

  if (datasetComponentBlobEntities != null && datasetComponentBlobEntities.size() > 0) {
    List<S3DatasetComponentBlob> componentBlobs =
        datasetComponentBlobEntities.stream()
            .map(S3DatasetComponentBlobEntity::toProto)
            .collect(Collectors.toList());
    return S3DatasetBlob.newBuilder().addAllComponents(componentBlobs).build();
  } else {
    throw new ModelDBException("S3 dataset Blob not found", Status.Code.NOT_FOUND);
  }
}
 

/**
 * {@inheritDoc}
 */
@Override
public List<Object> findByQuery(final String hsqlQuery, final Object... parameters) {
    Query query = sessionFactory.getCurrentSession().createQuery(hsqlQuery);
    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();
}
 

/**
 * {@inheritDoc}
 */
@Override
@SuppressWarnings("unchecked")
public List<T> findByNamedQueryForUpdate(final String namedQueryName, final int timeout, final Object... parameters) {
    Query query = sessionFactory.getCurrentSession().getNamedQuery(namedQueryName);
    LockOptions opts = new LockOptions(LockMode.PESSIMISTIC_WRITE);
    opts.setTimeOut(timeout);
    query.setLockOptions(opts);
    if (parameters != null) {
        setQueryParameters(query, parameters);
    }
    return query.list();
}
 

@Override
   public List getLatest(ChatSession chatSession, Integer max, boolean orderAsc) {
try {
    Query query = getSessionFactory().getCurrentSession().createQuery(
	    ChatMessageDAO.SQL_QUERY_FIND_MESSAGE_BY_SESSION_ORDER_BY_DATE + (orderAsc ? "asc" : "desc"));
    query.setParameter("chatSessionUid", chatSession.getUid());
    if (max != null) {
	query.setMaxResults(max);
    }
    return query.list();
} catch (HibernateException he) {
    logger.error("getLatest: hibernate exception");
    return null;
}
   }
 

public List<?> doFindByNamedQuery(final String queryName, final Object... values) {
Query queryObject = getSession().getNamedQuery(queryName);
if (values != null) {
    for (int i = 0; i < values.length; i++) {
	queryObject.setParameter(i, values[i]);
    }
}
return queryObject.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();
}
 

public List<Interpretation> getInterpretations( User user )
{
    String hql = "select distinct i from Interpretation i left join i.comments c " +
        "where i.user = :user or c.user = :user order by i.lastUpdated desc";

    Query<Interpretation> query = getQuery( hql )
        .setParameter( "user", user )
        .setCacheable( cacheable );

    return query.list();
}
 

@SuppressWarnings({ "unchecked", "rawtypes" })
@Override
public List findByQuery(String query, Object[] values, Integer max) throws PersistenceException {
	List<Object> coll = new ArrayList<Object>();
	try {
		log.debug("Execute query: {}", query);
		Query queryObject = prepareQuery(query, values, max);
		coll = (List<Object>) queryObject.list();
		return coll;
	} catch (Throwable e) {
		throw new PersistenceException(e);
	}
}
 

@SuppressWarnings("unchecked")
@Sessional
@Listen
public void on(SystemStarted event) {
	logger.info("Caching issue info...");
	
	Query<?> query = dao.getSession().createQuery("select id, project.id, number from Issue");
	for (Object[] fields: (List<Object[]>)query.list()) {
		Long issueId = (Long) fields[0];
		issues.put(issueId, new IssueFacade(issueId, (Long)fields[1], (Long)fields[2]));
	}
}
 

private static List<User> realLookupByIds(Collection<Long> ids) {
    Session session = HibernateFactory.getSession();
    Query query = session.getNamedQuery("User.findByIds")
            .setParameterList("userIds", ids);
    return query.list();
}
 

public static void migration(Map<String, Object> propertiesMap) {
  LOGGER.debug("Artifact path migration start");
  Map<String, Object> artifactStoreConfigMap =
      (Map<String, Object>) propertiesMap.get(ModelDBConstants.ARTIFACT_STORE_CONFIG);

  String artifactStoreType =
      (String) artifactStoreConfigMap.get(ModelDBConstants.ARTIFACT_STORE_TYPE);
  String storeTypePathPrefix = null;
  if (artifactStoreType.equals(ModelDBConstants.S3)) {
    Map<String, Object> s3ConfigMap =
        (Map<String, Object>) artifactStoreConfigMap.get(ModelDBConstants.S3);
    String cloudBucketName = (String) s3ConfigMap.get(ModelDBConstants.CLOUD_BUCKET_NAME);
    LOGGER.trace("S3 cloud bucket name {}", cloudBucketName);
    storeTypePathPrefix = "s3://" + cloudBucketName + ModelDBConstants.PATH_DELIMITER;
  } else if (artifactStoreType.equals(ModelDBConstants.NFS)) {
    Map<String, Object> nfsConfigMap =
        (Map<String, Object>) artifactStoreConfigMap.get(ModelDBConstants.NFS);
    String rootDir = (String) nfsConfigMap.get(ModelDBConstants.NFS_ROOT_PATH);
    LOGGER.trace("NFS server root path {}", rootDir);
    storeTypePathPrefix = "nfs://" + rootDir + ModelDBConstants.PATH_DELIMITER;
  }

  Long count = getArtifactEntityCount();

  int lowerBound = 0;
  final int pagesize = 500;
  LOGGER.debug("Total artifactEntities {}", count);

  while (lowerBound < count) {

    try (Session session = ModelDBHibernateUtil.getSessionFactory().openSession()) {
      Transaction transaction = session.beginTransaction();
      String queryString =
          "FROM ArtifactEntity ae WHERE ae."
              + ModelDBConstants.STORE_TYPE_PATH
              + " IS NULL ORDER BY ae.id asc";
      Query query = session.createQuery(queryString);
      query.setFirstResult(lowerBound);
      query.setMaxResults(pagesize);
      List<ArtifactEntity> artifactEntities = query.list();
      for (ArtifactEntity artifactEntity : artifactEntities) {
        if (!artifactEntity.getPath_only()) {
          String storeTypePath = storeTypePathPrefix + artifactEntity.getPath();
          artifactEntity.setStore_type_path(storeTypePath);
          session.update(artifactEntity);
        }
      }
      LOGGER.debug("finished processing page lower boundary {}", lowerBound);
      transaction.commit();
      lowerBound += pagesize;
    }
  }

  LOGGER.debug("Artifact path migration stop");
}
 

@Override
public List<ExperimentRun> getExperimentRuns(List<KeyValue> keyValues)
    throws InvalidProtocolBufferException {
  try (Session session = ModelDBHibernateUtil.getSessionFactory().openSession()) {
    StringBuilder stringQueryBuilder = new StringBuilder("From ExperimentRunEntity er where ");
    Map<String, Object> paramMap = new HashMap<>();
    for (int index = 0; index < keyValues.size(); index++) {
      KeyValue keyValue = keyValues.get(index);
      Value value = keyValue.getValue();
      String key = keyValue.getKey();

      switch (value.getKindCase()) {
        case NUMBER_VALUE:
          paramMap.put(key, value.getNumberValue());
          break;
        case STRING_VALUE:
          paramMap.put(key, value.getStringValue());
          break;
        case BOOL_VALUE:
          paramMap.put(key, value.getBoolValue());
          break;
        default:
          Status invalidValueTypeError =
              Status.newBuilder()
                  .setCode(Code.UNIMPLEMENTED_VALUE)
                  .setMessage(
                      "Unknown 'Value' type recognized, valid 'Value' type are NUMBER_VALUE, STRING_VALUE, BOOL_VALUE")
                  .build();
          throw StatusProto.toStatusRuntimeException(invalidValueTypeError);
      }
      stringQueryBuilder.append(" er." + key + " = :" + key);
      if (index < keyValues.size() - 1) {
        stringQueryBuilder.append(" AND ");
      }
    }
    Query query =
        session.createQuery(
            stringQueryBuilder.toString() + " AND er." + ModelDBConstants.DELETED + " = false ");
    for (Map.Entry<String, Object> paramEntry : paramMap.entrySet()) {
      query.setParameter(paramEntry.getKey(), paramEntry.getValue());
    }
    List<ExperimentRunEntity> experimentRunObjList = query.list();
    return RdbmsUtils.convertExperimentRunsFromExperimentRunEntityList(experimentRunObjList);
  } catch (Exception ex) {
    if (ModelDBUtils.needToRetry(ex)) {
      return getExperimentRuns(keyValues);
    } else {
      throw ex;
    }
  }
}
 

@SuppressWarnings("unchecked")
   @Override
   public List<Object[]> getPagedUsers(Long toolSessionId, Integer page, Integer size, int sorting,
    String searchString) {

String GET_USERS_FOR_SESSION = "SELECT user.uid, user.hidden, CONCAT(user.firstName, ' ', user.lastName) FROM "
	+ PeerreviewUser.class.getName() + " user WHERE user.session.sessionId = :toolSessionId ";

String sortingOrder = "";
switch (sorting) {
    case PeerreviewConstants.SORT_BY_NO:
	sortingOrder = " ORDER BY user.uid";
	break;
    case PeerreviewConstants.SORT_BY_USERNAME_ASC:
	sortingOrder = " ORDER BY user.firstName ASC";
	break;
    case PeerreviewConstants.SORT_BY_USERNAME_DESC:
	sortingOrder = " ORDER BY user.firstName DESC";
	break;
}

   	StringBuilder bldr =  new StringBuilder(GET_USERS_FOR_SESSION);
if (!StringUtils.isBlank(searchString)) {
    String[] tokens = searchString.trim().split("\\s+");
    for (String token : tokens) {
	String escToken = StringEscapeUtils.escapeSql(token);
	bldr.append(" AND ( ").append("user.firstName LIKE '%").append(escToken)
		.append("%' OR user.lastName LIKE '%").append(escToken).append("%' OR user.loginName LIKE '%")
		.append(escToken).append("%') ");
    }
}
   	bldr.append(sortingOrder);
   	
String queryString = bldr.toString();
Query query = getSession().createQuery(queryString)
	.setParameter("toolSessionId", toolSessionId);
if ( page != null && size != null ) {
    query.setFirstResult(page * size).setMaxResults(size);
}
return (List<Object[]>) query.list();
   }
 

/**
 * returns a list of projectIds accessible to the user passed as an argument within the workspace
 * passed as an argument. For no auth returns the list of non deleted projects
 */
@Override
public List<String> getWorkspaceProjectIDs(String workspaceName, UserInfo currentLoginUserInfo)
    throws InvalidProtocolBufferException {
  if (!roleService.IsImplemented()) {
    try (Session session = ModelDBHibernateUtil.getSessionFactory().openSession()) {
      return session.createQuery(NON_DELETED_PROJECT_IDS).list();
    }
  } else {

    // get list of accessible projects
    @SuppressWarnings("unchecked")
    List<String> accessibleProjectIds =
        roleService.getAccessibleResourceIds(
            null,
            new CollaboratorUser(authService, currentLoginUserInfo),
            ProjectVisibility.PRIVATE,
            ModelDBServiceResourceTypes.PROJECT,
            Collections.EMPTY_LIST);
    LOGGER.debug(
        "accessible Project Ids in function getWorkspaceProjectIDs : {}", accessibleProjectIds);

    // resolve workspace
    WorkspaceDTO workspaceDTO =
        roleService.getWorkspaceDTOByWorkspaceName(currentLoginUserInfo, workspaceName);

    List<String> resultProjects = new LinkedList<String>();
    try (Session session = ModelDBHibernateUtil.getSessionFactory().openSession()) {
      @SuppressWarnings("unchecked")
      Query<String> query = session.createQuery(IDS_FILTERED_BY_WORKSPACE);
      query.setParameterList(ModelDBConstants.PROJECT_IDS, accessibleProjectIds);
      query.setParameter(ModelDBConstants.WORKSPACE, workspaceDTO.getWorkspaceId());
      query.setParameter(
          ModelDBConstants.WORKSPACE_TYPE, workspaceDTO.getWorkspaceType().getNumber());
      resultProjects = query.list();

      // in personal workspace show projects directly shared
      if (workspaceDTO
          .getWorkspaceName()
          .equals(authService.getUsernameFromUserInfo(currentLoginUserInfo))) {
        LOGGER.debug("Workspace and current login user match");
        List<String> directlySharedProjects =
            roleService.getSelfDirectlyAllowedResources(
                ModelDBServiceResourceTypes.PROJECT, ModelDBServiceActions.READ);
        query = session.createQuery(NON_DELETED_PROJECT_IDS_BY_IDS);
        query.setParameterList(ModelDBConstants.PROJECT_IDS, directlySharedProjects);
        resultProjects.addAll(query.list());
        LOGGER.debug(
            "accessible directlySharedProjects Ids in function getWorkspaceProjectIDs : {}",
            directlySharedProjects);
      }
    }
    LOGGER.debug(
        "Total accessible project Ids in function getWorkspaceProjectIDs : {}", resultProjects);
    return resultProjects;
  }
}