org.hibernate.query.Query Java Examples

/**
 * Finds a single instance of a persistent object given a named query.
 * @param qryName The name of the query used to find the persistent object.
 * It should be formulated to ensure a single object is returned or an error
 * will occur.
 * @param qryParams Map of named bind parameters whose keys are Strings. The
 * map can also be null.
 * @param cacheable if we should cache the results of this object
 * @return Object found by named query or null if nothing found.
 */
protected Object lookupObjectByNamedQuery(String qryName, Map qryParams,
        boolean cacheable) {
    Object retval = null;
    Session session = null;

    try {
        session = HibernateFactory.getSession();

        Query query = session.getNamedQuery(qryName)
                .setCacheable(cacheable);
        bindParameters(query, qryParams);
        retval = query.uniqueResult();
    }
    catch (MappingException me) {
        throw new HibernateRuntimeException("Mapping not found for " + qryName, me);
    }
    catch (HibernateException he) {
        throw new HibernateRuntimeException("Executing query " + qryName +
                " with params " + qryParams + " failed", he);
    }

    return retval;
}
 

public String[] greaterThanCriteria() {
    final Session session = HibernateUtil.getHibernateSession();
    final CriteriaBuilder cb = session.getCriteriaBuilder();
    final CriteriaQuery<Item> cr = cb.createQuery(Item.class);
    final Root<Item> root = cr.from(Item.class);
    cr.select(root)
        .where(cb.gt(root.get("itemPrice"), 1000));
    // cr.add(Restrictions.gt("itemPrice", 1000));
    Query<Item> query = session.createQuery(cr);
    final List<Item> greaterThanItemsList = query.getResultList();
    final String greaterThanItems[] = new String[greaterThanItemsList.size()];
    for (int i = 0; i < greaterThanItemsList.size(); i++) {
        greaterThanItems[i] = greaterThanItemsList.get(i)
            .getItemName();
    }
    session.close();
    return greaterThanItems;
}
 

@Override
public void setAllRoomsAtDirty(String dirty) {

    try {
        session = dataSourceFactory.getSessionFactory().openSession();
        beginTransactionIfAllowed(session);
        Query<?> query = session.createQuery("UPDATE Room SET cleaningStatus=:dirty");
        query.setParameter("dirty", dirty);
        query.executeUpdate();
        
        logging.setMessage("RoomDaoImpl -> all rooms status updated to dirty successfully.");
    } catch (HibernateException e) {
        session.getTransaction().rollback();
        logging.setMessage("RoomDaoImpl -> "+e.getLocalizedMessage());
    }
    session.close();
}
 

@Override
public String getTotalCashLiraPostingsForOneDay(String today) {
    String totalCash = null;
    try {
        session = dataSourceFactory.getSessionFactory().openSession();
        beginTransactionIfAllowed(session);
        Query<String> query = session.createQuery("select sum(price) from Posting where currency = 'TURKISH LIRA' and dateTime >= :today", String.class);
        query.setParameter("today", today);
        totalCash = query.getSingleResult();

        logging.setMessage("PostingDaoImpl -> fetching total cash lira posting for one day...");

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

private void saveBranch(
    Session session, String commitSHA, String branch, RepositoryEntity repository)
    throws ModelDBException {
  boolean exists =
      VersioningUtils.commitRepositoryMappingExists(session, commitSHA, repository.getId());
  if (!exists) {
    throw new ModelDBException(
        "Commit_hash and repository_id mapping not found for repository "
            + repository.getId()
            + " and commit "
            + commitSHA,
        Code.NOT_FOUND);
  }

  Query query = session.createQuery(CHECK_BRANCH_IN_REPOSITORY_HQL);
  query.setParameter("repositoryId", repository.getId());
  query.setParameter("branch", branch);
  BranchEntity branchEntity = (BranchEntity) query.uniqueResult();
  if (branchEntity != null) {
    if (branchEntity.getCommit_hash().equals(commitSHA)) return;
    session.delete(branchEntity);
  }

  branchEntity = new BranchEntity(repository.getId(), commitSHA, branch);
  session.save(branchEntity);
}
 

/**
 * Return an iterable containing all archived discussion pages for
 * the given article page. The most recent discussion page is not included.
 * The most recent discussion page can be obtained with {@link #getDiscussionPage(Page)}.
 * <br>
 * The provided page Object must not be a discussion page itself! If it is
 * a discussion page, is returned unchanged.
 *
 * @param articlePage the article page for which a discussion archives should be retrieved
 * @return An iterable with the discussion archive page objects for the given article page object
 * @throws WikiApiException If no page or redirect with this title exists or title could not be properly parsed.
 */
public Iterable<Page> getDiscussionArchives(Page articlePage) throws WikiApiException{
    String articleTitle = articlePage.getTitle().getWikiStyleTitle();
	if(!articleTitle.startsWith(WikiConstants.DISCUSSION_PREFIX)){
		articleTitle=WikiConstants.DISCUSSION_PREFIX+articleTitle;
	}

	Session session = this.__getHibernateSession();
    session.beginTransaction();

    List<Page> discussionArchives = new LinkedList<Page>();

    Query query = session.createQuery("SELECT pageID FROM PageMapLine where name like :name");
    query.setParameter("name", articleTitle+"/%", StringType.INSTANCE);
    Iterator results = query.list().iterator();

    session.getTransaction().commit();

    while (results.hasNext()) {
        int pageID = (Integer) results.next();
        discussionArchives.add(getPage(pageID));
    }

    return discussionArchives;

}
 

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

@Override
public List<Reservation> getReservsAsWaitlist(String reservDate) {
    List<Reservation> reservationsList = null;
    try {
        session = dataSourceFactory.getSessionFactory().openSession();
        beginTransactionIfAllowed(session);
        Query<Reservation> query = session.createQuery("from Reservation "
                + "where bookStatus = 'WAITLIST' and checkinDate=:today", Reservation.class);
        query.setParameter("today", reservDate);
        reservationsList = query.getResultList();

        logging.setMessage("ReservationDaoImpl -> fetching all waiting reservations...");

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

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

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

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

}
 

@Override
   public Integer getCountLearnersByLesson(long lessonId, String searchPhrase) {
StringBuilder queryTextBuilder = new StringBuilder("SELECT COUNT(*) ")
	.append(LessonDAO.LOAD_LEARNERS_BY_LESSON);
if (!StringUtils.isBlank(searchPhrase)) {
    String[] tokens = searchPhrase.trim().split("\\s+");
    for (String token : tokens) {
	token = StringEscapeUtils.escapeSql(token).replace("\\", "\\\\");
	queryTextBuilder.append(" AND (users.firstName LIKE '%").append(token)
		.append("%' OR users.lastName LIKE '%").append(token).append("%' OR users.login LIKE '%")
		.append(token).append("%')");
    }
}

Query query = getSession().createQuery(queryTextBuilder.toString()).setLong("lessonId", lessonId);
Object value = query.uniqueResult();
return ((Number) value).intValue();
   }
 

public ToolsLayout getToolLayout(String toolName, String toolCategory) {
	Query<ToolsLayout> createQuery = getSession().createQuery(
			"FROM ToolsLayout TL WHERE TL.toolName = :toolName AND TL.toolCategory = :toolCategory",
			ToolsLayout.class);
	createQuery.setParameter("toolName", toolName);
	createQuery.setParameter("toolCategory", toolCategory);
	ToolsLayout toolLayout = null;
	try{
		toolLayout = createQuery.getSingleResult();
	}catch(Exception e){
		throw new RuntimeException("Exception while retrieving data"+e);
	}
	terminateSession();
	terminateSessionFactory();
	return toolLayout;
}
 

/**
    * Gets all lessons that are active for a learner.
    *
    * @param learner
    *            a User that identifies the learner.
    * @return a List with all active lessons in it.
    */
   @Override
   public List getActiveLessonsForLearner(User learner) {

Query query = getSession().getNamedQuery("activeLessonsAllOrganisations");
query.setInteger("userId", learner.getUserId().intValue());
List result = query.list();
return result;
   }
 

public <R> R uniqueData(String name, Class<R> queryType, Object... parameters) {
	return getHibernateTemplate().executeWithNativeSession(new HibernateCallback<R>() {

		@Override
		public R doInHibernate(Session session) throws HibernateException {
			Query<?> query = session.createNamedQuery(name, queryType);
			for (int index = 0; index < parameters.length; index++) {
				query.setParameter(index, parameters[index]);
			}
			R value = (R) query.getSingleResult();
			return value;
		}

	});
}
 

@Test
public void whenNamedNativeQueryIsCalledUsingCreateNamedQuery_ThenOk() {
    Query<DeptEmployee> query = session.createNamedQuery("DeptEmployee_FindByEmployeeName", DeptEmployee.class);
    query.setParameter("name", "John Wayne");
    DeptEmployee result = query.getSingleResult();
    Assert.assertNotNull(result);
    Assert.assertEquals("001", result.getEmployeeNumber());
}
 

private PostWithXminAndXmax getPost(EntityManager entityManager, Integer id) {
    List<PostWithXminAndXmax> result = (List<PostWithXminAndXmax>) entityManager.createNativeQuery(
        "SELECT " +
        "    id, title, CAST(xmin AS text), CAST(xmax AS text) " +
        "FROM Post " +
        "WHERE id = :id")
    .setParameter("id", id)
    .unwrap(Query.class)
    .setResultTransformer(new AliasToBeanResultTransformer(PostWithXminAndXmax.class))
    .getResultList();
    return !result.isEmpty() ? result.get(0) : null;
}
 

/**
 * {@inheritDoc}
 */
@Override
public int executeUpdate(final String namedQueryName, final Object... parameters) {
    final Query query = sessionFactory.getCurrentSession().getNamedQuery(namedQueryName);
    setQueryParameters(query, parameters);
    return query.executeUpdate();
}
 

@Transactional
@Override
public void create(Build build) {
	Preconditions.checkArgument(build.isNew());
	build.setNumberScope(build.getProject().getForkRoot());
	Query<?> query = getSession().createQuery(String.format("select max(%s) from Build where %s=:numberScope", 
			Build.PROP_NUMBER, Build.PROP_NUMBER_SCOPE));
	query.setParameter("numberScope", build.getNumberScope());
	build.setNumber(getNextNumber(build.getNumberScope(), query));
	save(build);
	for (BuildParam param: build.getParams())
		buildParamManager.save(param);
	for (BuildDependence dependence: build.getDependencies())
		buildDependenceManager.save(dependence);
}
 

/**
 * Check if there is a pending service pack migration in the schedule. Return the
 * action ID if available or null otherwise.
 * @param serverId server
 * @return ID of a possibly scheduled migration or null.
 */
public static Action isMigrationScheduledForServer(Long serverId) {
    Action ret = null;
    Query query = HibernateFactory.getSession().getNamedQuery(
            "ServerAction.findPendingKickstartsForServer");
    query.setParameter("serverId", serverId);
    query.setParameter("label", "distupgrade.upgrade");
    List<ServerAction> list = query.list();
    if (list != null && list.size() > 0) {
        ret = list.get(0).getParentAction();
    }
    return ret;
}
 

@Override
@SuppressWarnings("unchecked")
public Collection<String> getLinksFromCategory(MCRCategoryID id) {
    Query<?> q = HIB_CONNECTION_INSTANCE.getNamedQuery(NAMED_QUERY_NAMESPACE + "ObjectIDByCategory");
    q.setCacheable(true);
    q.setParameter("id", id);
    q.setReadOnly(true);
    return (Collection<String>) q.getResultList();
}
 

@Override
public int countMapDashboardItems( Map map )
{
    Query<Long> query = getTypedQuery( "select count(distinct c) from DashboardItem c where c.map=:map" );
    query.setParameter( "map", map );

    return query.getSingleResult().intValue();
}
 

/**
 * Delete old data from tables
 */
@Before
public void before() {
	try(final Session session = sessionFactory.openSession()) {
		session.beginTransaction();

		for(final String tablename : Arrays.asList("BitfinexOrder", "Trade")) {
			@SuppressWarnings("rawtypes")
			final Query query = session.createQuery("delete from " + tablename);
			query.executeUpdate();
		}
		
		session.close();
	}
}
 

@Test
public void testFilterEndToEnd() throws IOException {
    Resource resource = validObject();
    resource.setUnique("filterUnique");
    resource.setId(4);
    resourceCrud.create(resource);
    resourceCrud.create(validObject());

    long tenSecondsFromNow = Calendar.getInstance().getTimeInMillis() + 10000;
    long tenSecondsBefore = Calendar.getInstance().getTimeInMillis() - 10000;

    Map<String, String> map = new HashMap<>();

    map.put("id_equals", "4");
    map.put("date_gt", "" + tenSecondsBefore);
    map.put("date_lte", "" + tenSecondsFromNow);
    map.put("unique_contains", "filt");

    Request request = mockedContextWithQueryMap(map).request();

    List<Resource> resourceResult = resourceCrud.find(
            new com.elepy.dao.Query(Filters.and(request.filtersForModel(Resource.class)))
    );

    assertThat(resourceResult.size()).isEqualTo(1);

    assertThat(resourceResult.get(0).getUnique()).isEqualTo("filterUnique");
}
 

@Override
@SuppressWarnings("unchecked")
public Collection<MCRCategoryID> getLinksFromReference(MCRCategLinkReference reference) {
    Query<?> q = HIB_CONNECTION_INSTANCE.getNamedQuery(NAMED_QUERY_NAMESPACE + "categoriesByObjectID");
    q.setCacheable(true);
    q.setParameter("id", reference.getObjectID());
    q.setParameter("type", reference.getType());
    q.setReadOnly(true);
    return (Collection<MCRCategoryID>) q.getResultList();
}
 

public static List<Subject> subjectsContainingSubject(SubjectType subjectType, Subject subject) {
	return HibernateUtil.withSession(session -> {
		Query query = session.createQuery("from Subject where subjectType = :subjectType and contains(shape, :geom) = true", Subject.class);
		query.setParameter("subjectType", subjectType);
		query.setParameter("geom", subject.getShape());
		query.setCacheable(true);
		return (List<Subject>) query.getResultList();
	});
}
 

@Override
public List<Classroom> findAllClassrooms() {
	// TODO Auto-generated method stub
	String hql = "from Classroom";
	Query<?> query = sessionFactory.getCurrentSession().createQuery(hql);
	@SuppressWarnings("unchecked")
	List<Classroom> list = (List<Classroom>) query.getResultList();
	return list;
}
 

@Override
public List<KeyValue> getProjectAttributes(
    String projectId, List<String> attributeKeyList, Boolean getAll)
    throws InvalidProtocolBufferException {
  try (Session session = ModelDBHibernateUtil.getSessionFactory().openSession()) {
    if (getAll) {
      ProjectEntity projectObj = session.get(ProjectEntity.class, projectId);
      if (projectObj == null) {
        String errorMessage = "Project not found for given ID: " + projectId;
        LOGGER.info(errorMessage);
        Status status =
            Status.newBuilder().setCode(Code.NOT_FOUND_VALUE).setMessage(errorMessage).build();
        throw StatusProto.toStatusRuntimeException(status);
      }
      return projectObj.getProtoObject().getAttributesList();
    } else {
      Query query = session.createQuery(GET_PROJECT_ATTR_BY_KEYS_HQL);
      query.setParameterList("keys", attributeKeyList);
      query.setParameter(ModelDBConstants.PROJECT_ID_STR, projectId);
      query.setParameter("fieldType", ModelDBConstants.ATTRIBUTES);

      @SuppressWarnings("unchecked")
      List<AttributeEntity> attributeEntities = query.list();
      return RdbmsUtils.convertAttributeEntityListFromAttributes(attributeEntities);
    }
  } catch (Exception ex) {
    if (ModelDBUtils.needToRetry(ex)) {
      return getProjectAttributes(projectId, attributeKeyList, getAll);
    } else {
      throw ex;
    }
  }
}
 

@Override
public Project setProjectVisibility(String projectId, ProjectVisibility projectVisibility)
    throws InvalidProtocolBufferException {
  try (Session session = ModelDBHibernateUtil.getSessionFactory().openSession()) {
    Query query = session.createQuery(GET_PROJECT_BY_ID_HQL);
    query.setParameter("id", projectId);
    ProjectEntity projectEntity = (ProjectEntity) query.uniqueResult();

    Integer oldVisibilityInt = projectEntity.getProject_visibility();
    ProjectVisibility oldVisibility = ProjectVisibility.PRIVATE;
    if (oldVisibilityInt != null) {
      oldVisibility = ProjectVisibility.forNumber(oldVisibilityInt);
    }
    if (!oldVisibility.equals(projectVisibility)) {
      projectEntity.setProject_visibility(projectVisibility.ordinal());
      projectEntity.setDate_updated(Calendar.getInstance().getTimeInMillis());
      Transaction transaction = session.beginTransaction();
      session.update(projectEntity);
      transaction.commit();
      deleteOldVisibilityBasedBinding(
          oldVisibility,
          projectId,
          projectEntity.getWorkspace_type(),
          projectEntity.getWorkspace());
      createNewVisibilityBasedBinding(
          projectVisibility,
          projectId,
          projectEntity.getWorkspace_type(),
          projectEntity.getWorkspace());
    }
    LOGGER.debug(ModelDBMessages.GETTING_PROJECT_BY_ID_MSG_STR);
    return projectEntity.getProtoObject();
  } catch (Exception ex) {
    if (ModelDBUtils.needToRetry(ex)) {
      return setProjectVisibility(projectId, projectVisibility);
    } else {
      throw ex;
    }
  }
}
 

public List<EntityDefinition> fetchAllEntityDefination() {
	Query<EntityDefinition> createQuery = getSession().createQuery("FROM EntityDefinition PM ORDER BY PM.levelName",
			EntityDefinition.class);
	List<EntityDefinition> resultList = createQuery.getResultList();
	terminateSession();
	terminateSessionFactory();
	return resultList;
}
 

@Override
public long countAnalyticalObjects( Indicator indicator )
{
    Query<Long> query = getTypedQuery( "select count(distinct c) from " + clazz.getName()
        + " c join c.dataDimensionItems d where d.indicator = :indicator" );
    query.setParameter( "indicator", indicator );
    return query.uniqueResult();
}
 

public boolean deleteHierarchyDetails(String hierarchyName) {
	Query<HierarchyDetails> createQuery = getSession().createQuery(
			"FROM HierarchyDetails HD WHERE HD.hierarchyName = :hierarchyName",
			HierarchyDetails.class);
	createQuery.setParameter("hierarchyName", hierarchyName);
	HierarchyDetails hierarchyDetails = createQuery.getSingleResult();
	getSession().beginTransaction();
	getSession().delete(hierarchyDetails);
	getSession().getTransaction().commit();
	terminateSession();
	terminateSessionFactory();
	return true;
}