下面列出了javax.persistence.criteria.CriteriaQuery#where ( ) 实例代码,或者点击链接到github查看源代码,也可以在右侧发表评论。
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;
}
@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;
}
@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();
}
@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();
}
/**
* @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 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)));
}
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();
}
/**
* {@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);
}
/**
* 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();
}
/**
* 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();
}
/**
* 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 ) );
}
@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();
}
@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;
}
@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 );
}
@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);
}
/**
* 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();
}
/**
* 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;
}
@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();
}