Java Code Examples for javax.persistence.criteria.CriteriaQuery#where()

@Decision(DELETE)
public RolePermissionsRequest delete(RolePermissionsRequest deleteRequest, Role role, EntityManager em) {
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Permission> query = cb.createQuery(Permission.class);
    Root<Permission> permissionRoot = query.from(Permission.class);
    query.where(permissionRoot.get("name").in(deleteRequest));
    List<Permission> permissions = em.createQuery(query)
            .setHint("javax.persistence.cache.storeMode", CacheStoreMode.REFRESH)
            .getResultList();

    EntityTransactionManager tx = new EntityTransactionManager(em);

    tx.required(() -> {
        HashSet<Permission> rolePermissions = new HashSet<>(role.getPermissions());
        rolePermissions.removeAll(permissions);
        role.setPermissions(new ArrayList<>(rolePermissions));
    });

    return deleteRequest;
}
 

private <T> TypedQuery<T> doQueryCrossRes(Class<T> domainClazz, CrossResRequest request, boolean selectCount) {
    CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    EntityGraph<T> rootEg = entityManager.createEntityGraph(domainClazz);

    CriteriaQuery query = cb.createQuery(domainClazz);
    Root<T> root = query.from(domainClazz);
    if (selectCount) {
        query.select(cb.count(root));
    }
    List<Predicate> predicates = new LinkedList<>();
    queryJoin(cb, query, root, request.getRootFilterPath(), rootEg, null, predicates);

    if (predicates.size() > 0) {
        if (FilterRelationship.Or.equals(request.getFilterRs())) {
            query.where(cb.or(predicates.toArray(new Predicate[0])));
        } else {
            query.where(cb.and(predicates.toArray(new Predicate[0])));
        }
    }
    TypedQuery<T> typedQuery = entityManager.createQuery(query);
    if (!selectCount) {
        typedQuery.setHint("javax.persistence.fetchgraph", rootEg);
    }
    return typedQuery;
}
 

@Override
public Map<String, Long> selectCountsTriedByProblemJids(Set<String> problemJids) {
    if (problemJids.isEmpty()) {
        return Collections.emptyMap();
    }

    CriteriaBuilder cb = currentSession().getCriteriaBuilder();
    CriteriaQuery<Tuple> cq = cb.createTupleQuery();
    Root<StatsUserProblemModel> root = cq.from(getEntityClass());

    cq.select(cb.tuple(
            root.get(StatsUserProblemModel_.problemJid),
            cb.count(root)));

    cq.where(
            root.get(StatsUserProblemModel_.problemJid).in(problemJids));

    cq.groupBy(
            root.get(StatsUserProblemModel_.problemJid));

    return currentSession().createQuery(cq).getResultList()
            .stream()
            .collect(Collectors.toMap(tuple -> tuple.get(0, String.class), tuple -> tuple.get(1, Long.class)));
}
 

@Override
public List<UserVO> list(String login, String loginPattern,
                          Integer role, Integer status,
                          String sortField, boolean sortOrderAsc,
                          Integer take, Integer skip) {
    CriteriaBuilder cb = criteriaBuilder();
    CriteriaQuery<User> cq = cb.createQuery(User.class);
    Root<User> from = cq.from(User.class);

    Predicate[] predicates = CriteriaHelper.userListPredicates(cb, from, ofNullable(login), ofNullable(loginPattern), ofNullable(role), ofNullable(status));
    cq.where(predicates);
    CriteriaHelper.order(cb, cq, from, ofNullable(sortField), Boolean.TRUE.equals(sortOrderAsc));

    TypedQuery<User> query = createQuery(cq);
    cacheQuery(query, of(CacheConfig.refresh()));
    ofNullable(take).ifPresent(query::setMaxResults);
    ofNullable(skip).ifPresent(query::setFirstResult);
    return query.getResultList().stream().map(User::convertToVo).collect(Collectors.toList());
}
 

private List<Application> list(Business business, EffectivePerson effectivePerson, String applicationCategory)
		throws Exception {
	EntityManager em = business.entityManagerContainer().get(Application.class);
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<Application> cq = cb.createQuery(Application.class);
	Root<Application> root = cq.from(Application.class);
	cq.select(root);
	Predicate p = cb.equal(root.get(Application_.applicationCategory), applicationCategory);
	if ((!effectivePerson.isManager()) && (!business.organization().person().hasRole(effectivePerson,
			OrganizationDefinition.ProcessPlatformManager))) {
		p = cb.and(p,
				cb.or(cb.isMember(effectivePerson.getDistinguishedName(), root.get(Application_.controllerList)),
						cb.equal(root.get(Application_.creatorPerson), effectivePerson.getDistinguishedName())));
	}
	cq.where(p);
	return em.createQuery(cq).getResultList();
}
 

/**
 * @see io.apiman.manager.api.core.IStorageQuery#getUserMemberships(java.lang.String, java.lang.String)
 */
@Override
public Set<RoleMembershipBean> getUserMemberships(String userId, String organizationId) throws StorageException {
    Set<RoleMembershipBean> memberships = new HashSet<>();
    beginTx();
    try {
        EntityManager entityManager = getActiveEntityManager();
        CriteriaBuilder builder = entityManager.getCriteriaBuilder();
        CriteriaQuery<RoleMembershipBean> criteriaQuery = builder.createQuery(RoleMembershipBean.class);
        Root<RoleMembershipBean> from = criteriaQuery.from(RoleMembershipBean.class);
        criteriaQuery.where(
                builder.equal(from.get("userId"), userId),
                builder.equal(from.get("organizationId"), organizationId) );
        TypedQuery<RoleMembershipBean> typedQuery = entityManager.createQuery(criteriaQuery);
        List<RoleMembershipBean> resultList = typedQuery.getResultList();
        memberships.addAll(resultList);
        return memberships;
    } catch (Throwable t) {
        logger.error(t.getMessage(), t);
        throw new StorageException(t);
    } finally {
        rollbackTx();
    }
}
 

@Override
public void lock(long id) {
	CriteriaBuilder cb = entityManager.getCriteriaBuilder();
	CriteriaQuery<Long> query = cb.createQuery(Long.class);
	Root<Post> root = query.from(Post.class);
	query.select(root.get(Post_.id));
	query.where(cb.equal(root.get(Post_.id), id));
	entityManager.createQuery(query).setLockMode(LockModeType.PESSIMISTIC_WRITE).getSingleResult();
}
 

/**
 * List Subscriptions SCC IDs by Credential
 * @param c the credential to query
 * @return list of scc subscription ids
 */
public static List<Long> listSubscriptionsIdsByCredentials(Credentials c) {
    CriteriaBuilder builder = getSession().getCriteriaBuilder();
    CriteriaQuery<SCCSubscription> query = builder.createQuery(SCCSubscription.class);
    Root<SCCSubscription> root = query.from(SCCSubscription.class);
    query.where(builder.equal(root.get("credentials"), c));
    List<Long> result = new ArrayList<>();
    for (SCCSubscription sub : getSession().createQuery(query).getResultList()) {
        result.add(sub.getSccId());
    }
    return result;
}
 

/**
 * Lookup a {@link SCCRepository} by its name
 * @param name the name
 * @return the repository if found
 */
public static Optional<SCCRepository> lookupRepositoryByName(String name) {
    CriteriaBuilder builder = getSession().getCriteriaBuilder();
    CriteriaQuery<SCCRepository> select = builder.createQuery(SCCRepository.class);
    Root<SCCRepository> root = select.from(SCCRepository.class);
    select.where(builder.equal(root.get("name"), name));
    return getSession().createQuery(select).uniqueResultOptional();
}
 

@SuppressWarnings("unchecked")
public static <T> TypedQuery<Long> getCountQuery(CriteriaQuery<T> cq) {
	Class<T> domainClass = cq.getResultType();
	EntityManager em = getEntityManager(domainClass);
	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<Long> countCq = cb.createQuery(Long.class);
	Root<T> root;
	if (cq.getRestriction() != null) {
		countCq.where(cq.getRestriction());
	}
	if (cq.getGroupRestriction() != null) {
		countCq.having(cq.getGroupRestriction());
	}
	if (cq.getRoots().isEmpty()) {
		root = countCq.from(domainClass);
	} else {
		countCq.getRoots().addAll(cq.getRoots());
		root = (Root<T>) countCq.getRoots().iterator().next();
	}
	countCq.groupBy(cq.getGroupList());
	if (cq.isDistinct()) {
		countCq.select(cb.countDistinct(root));
	} else {
		countCq.select(cb.count(root));
	}

	return em.createQuery(countCq);
}
 

@Override
public List<AttributeValue> getAllValuesByAttributes( List<Attribute> attributes )
{
    CriteriaBuilder builder = getCriteriaBuilder();

    CriteriaQuery<String> query = builder.createQuery( String.class );
    Root<T> root = query.from( getClazz() );

    CriteriaBuilder.Coalesce<String> coalesce = builder.coalesce();
    attributes.stream().forEach( attribute ->
        coalesce.value(
            builder.function( FUNCTION_JSONB_EXTRACT_PATH, String.class, root.get( "attributeValues" ) ,
                builder.literal( attribute.getUid() )  ) ) );

    query.select( coalesce );

    List<Predicate> predicates = attributes.stream()
        .map( attribute ->
            builder.isNotNull(
                builder.function( FUNCTION_JSONB_EXTRACT_PATH, String.class, root.get( "attributeValues" ),
                    builder.literal( attribute.getUid() ) ) ) )
        .collect( Collectors.toList() );

    query.where(  builder.or( predicates.toArray( new Predicate[ predicates.size() ] ) ) ) ;

    List<String> result = getSession().createQuery( query ).list();

    return JsonAttributeValueBinaryType.convertListJsonToListObject( result );
}
 

@Decision(PUT)
public OtpKey create(UserPermissionPrincipal principal, EntityManager em) {
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<User> userQuery = cb.createQuery(User.class);
    Root<User> userRoot = userQuery.from(User.class);
    userQuery.where(cb.equal(userRoot.get("account"), principal.getName()));
    User user = em.createQuery(userQuery).getResultStream().findAny().orElseThrow();
    OtpKey otpKey = builder(new OtpKey())
            .set(OtpKey::setKey, RandomUtils.generateRandomString(20, config.getSecureRandom()).getBytes())
            .set(OtpKey::setUser, user)
            .build();
    EntityTransactionManager tx = new EntityTransactionManager(em);
    tx.required(() -> em.persist(otpKey));
    return otpKey;
}
 

@Test
public void callFunction() {
	log.info("... callFunction ...");

	EntityManager em = emf.createEntityManager();
	em.getTransaction().begin();

	CriteriaBuilder cb = em.getCriteriaBuilder();
	CriteriaQuery<Book> cq = cb.createQuery(Book.class);
	Root<Book> root = cq.from(Book.class);
	
	ParameterExpression<Double> doubleParam1 = cb.parameter(Double.class);
	ParameterExpression<Double> doubleParam2 = cb.parameter(Double.class);
	cq.where(cb.greaterThan(doubleParam2, cb.function("calculate", Double.class, root.get(Book_.price), doubleParam1)));

	TypedQuery<Book> q = em.createQuery(cq);
	q.setParameter(doubleParam1, 10.0D);
	q.setParameter(doubleParam2, 40.0D);
	List<Book> books = q.getResultList();
	
	for (Book b : books) {
		log.info(b);
	}

	em.getTransaction().commit();
	em.close();
}
 

/**
 * Get executable TypedQuery from JpaQueryParameter.
 *
 * @return executable TypedQuery
 */
protected final TypedQuery<T> getTypedQuery( CriteriaBuilder builder, JpaQueryParameters<T> parameters )
{
    List<Function<Root<T>, Predicate>> predicateProviders = parameters.getPredicates();
    List<Function<Root<T>, Order>> orderProviders = parameters.getOrders();
    preProcessPredicates( builder, predicateProviders );

    CriteriaQuery<T> query = builder.createQuery( getClazz() );
    Root<T> root = query.from( getClazz() );
    query.select( root );

    if ( !predicateProviders.isEmpty() )
    {
        List<Predicate> predicates = predicateProviders.stream().map( t -> t.apply( root ) ).collect( Collectors.toList() );
        query.where( predicates.toArray( new Predicate[0] ) );
    }

    if ( !orderProviders.isEmpty() )
    {
        List<Order> orders = orderProviders.stream().map( o -> o.apply( root ) ).collect( Collectors.toList() );
        query.orderBy( orders );
    }

    TypedQuery<T> typedQuery = getExecutableTypedQuery( query );

    if ( parameters.hasFirstResult() )
    {
        typedQuery.setFirstResult( parameters.getFirstResult() );
    }

    if ( parameters.hasMaxResult() )
    {
        typedQuery.setMaxResults( parameters.getMaxResults() );
    }

    return typedQuery
        .setHint( QueryHints.CACHEABLE, parameters.isCacheable( cacheable ) );
}
 

/**
 * Return a Repository
 * @param sccId the scc id
 * @return a SCCRepository
 */
public static Optional<SCCRepository> lookupRepositoryBySccId(Long sccId) {
    CriteriaBuilder builder = getSession().getCriteriaBuilder();
    CriteriaQuery<SCCRepository> select = builder.createQuery(SCCRepository.class);
    Root<SCCRepository> root = select.from(SCCRepository.class);
    select.where(builder.equal(root.get("sccId"), sccId));
    return getSession().createQuery(select).uniqueResultOptional();
}
 

/**
 * Builds iterator for jobs with given {@link MCRJobStatus} or <code>null</code> for all jobs.
 */
public Iterator<MCRJob> iterator(MCRJobStatus status) {
    if (!running) {
        List<MCRJob> empty = Collections.emptyList();
        return empty.iterator();
    }
    EntityManager em = MCREntityManagerProvider.getCurrentEntityManager();

    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<MCRJob> cq = cb.createQuery(MCRJob.class);
    Root<MCRJob> root = cq.from(MCRJob.class);

    List<Predicate> predicates = new ArrayList<>();
    if (status != null) {
        predicates.add(cb.equal(root.get("status"), status));
    }
    if (action != null) {
        predicates.add(cb.equal(root.get("action"), action));
    }
    cq.where(cb.and(predicates.toArray(new Predicate[] {})));
    cq.orderBy(cb.asc(root.get("added")));
    cq.distinct(true);

    TypedQuery<MCRJob> query = em.createQuery(cq);

    return query.getResultList().iterator();
}
 

/**
 * {@inheritDoc}
 */
@Override
public TypedQuery createQuery(Class queryClazz, TranslationContext criteria) {
    CriteriaQuery jpaQuery = criteria.query;
    // it is important to not create an empty or predicate
    if (!criteria.predicates.isEmpty()) {
        jpaQuery = jpaQuery.where(criteria.getCriteriaPredicate());
    }
    return entityManager.createQuery(jpaQuery);
}
 

public List<PersistedMessageBO> findByValues(Map<String, String> criteriaValues, int maxRows) {
    CriteriaBuilder builder = entityManager.getCriteriaBuilder();
    CriteriaQuery<PersistedMessageBO> query = builder.createQuery(PersistedMessageBO.class);
    Root<PersistedMessageBO> message = query.from(PersistedMessageBO.class);
    Predicate predicate = builder.conjunction();
    for (Map.Entry<String, String> entry : criteriaValues.entrySet()) {
        predicate = builder.and(predicate, builder.equal(message.get(entry.getKey()), entry.getValue()));
    }
    query.where(predicate);
    TypedQuery<PersistedMessageBO> typedQuery = entityManager.createQuery(query);
    return typedQuery.getResultList();
}
 

@Test
public void testCriteriaEntityQueryWithParam(TestContext context) {
	Author author1 = new Author("Iain M. Banks");
	Author author2 = new Author("Neal Stephenson");
	Book book1 = new Book("1-85723-235-6", "Feersum Endjinn", author1);
	Book book2 = new Book("0-380-97346-4", "Cryptonomicon", author2);
	Book book3 = new Book("0-553-08853-X", "Snow Crash", author2);
	author1.books.add(book1);
	author2.books.add(book2);
	author2.books.add(book3);

	CriteriaBuilder builder = getSessionFactory().getCriteriaBuilder();
	CriteriaQuery<Book> query = builder.createQuery(Book.class);
	Root<Book> b = query.from(Book.class);
	b.fetch("author");
	ParameterExpression<String> t = builder.parameter(String.class);
	query.where( builder.equal( b.get("title"), t ) );
	query.orderBy( builder.asc( b.get("isbn") ) );

	CriteriaUpdate<Book> update = builder.createCriteriaUpdate(Book.class);
	b = update.from(Book.class);
	update.where( builder.equal( b.get("title"), t ) );
	update.set( b.get("title"), "XXX" );

	CriteriaDelete<Book> delete = builder.createCriteriaDelete(Book.class);
	b = delete.from(Book.class);
	delete.where( builder.equal( b.get("title"), t ) );

	test(context,
			openSession()
					.thenCompose( session -> session.persist(author1, author2) )
					.thenCompose( session -> session.flush() )
					.whenComplete( (session,err) -> session.close() )
					.thenCompose( v -> openSession() )
					.thenCompose( session -> session.createQuery(query)
							.setParameter( t, "Snow Crash")
							.getResultList() )
					.thenAccept( books -> {
						context.assertEquals( 1, books.size() );
						books.forEach( book -> {
							context.assertNotNull( book.id );
							context.assertNotNull( book.title );
							context.assertNotNull( book.isbn );
							context.assertEquals( "Snow Crash", book.title );
						} );
					} )

					.thenCompose( v -> openSession() )
					.thenCompose( session -> session.createQuery(update)
							.setParameter( t, "Snow Crash")
							.executeUpdate() )
					.thenCompose( v -> openSession() )
					.thenCompose( session -> session.createQuery(delete)
							.setParameter( t, "Snow Crash")
							.executeUpdate() )
	);
}
 

@Override
public <V> List<T> findOrderedByPropertyListByEmbeddedProperty(final SingularAttribute<T, V> property,
		final Class<V> clazz2, final SingularAttribute<V, ? extends Object> property2, final Object value,
		final SingularAttribute<T, ? extends Object> orderByProperty) {
	final CriteriaQuery<T> criteriaQuery = criteriaBuilder
			.createQuery(persistentClass);
	final Root<T> root = criteriaQuery.from(persistentClass);
	criteriaQuery.select(root);

	if (orderByProperty != null) {
		criteriaQuery.orderBy(criteriaBuilder.desc(root.get(orderByProperty)));
	}


	final Join<T, V> join = root.join(property);

	final Path<? extends Object> path = join.get(property2);

	final Predicate condition = criteriaBuilder.equal(path, value);

	criteriaQuery.where(condition);

	final TypedQuery<T> typedQuery = entityManager
			.createQuery(criteriaQuery);

	addCacheHints(typedQuery, "findListByEmbeddedProperty." + persistentClass.getSimpleName());


	return typedQuery.getResultList();
}