com.querydsl.jpa.JPQLQuery Java Examples

/**
 * Adds a global contains text filter on the provided attributes.
 * WARNING: this creates a very inefficient query. If you have many entity
 * instances to query, use instead an indexed text search solution for better
 * performance.
 * @param text the text to look for
 * @param query
 * @param globalSearchAttributes the list of attributes to perform the
 *        filter on
 * @return the updated query
 */
protected JPQLQuery<T> applyGlobalSearch(String text, JPQLQuery<T> query,
    Path<?>... globalSearchAttributes) {
  if (text != null && !StringUtils.isEmpty(text) && globalSearchAttributes.length > 0) {
    BooleanBuilder searchCondition = new BooleanBuilder();
    for (int i = 0; i < globalSearchAttributes.length; i++) {
      Path<?> path = globalSearchAttributes[i];
      if (path instanceof StringPath) {
        StringPath stringPath = (StringPath) path;
        searchCondition.or(stringPath.containsIgnoreCase(text));
      } else if (path instanceof NumberExpression) {
        searchCondition.or(((NumberExpression<?>) path).like("%".concat(text).concat("%")));
      }
    }
    return query.where(searchCondition);
  }
  return query;
}
 

@ExtDirectMethod(STORE_READ)
@Transactional(readOnly = true)
public ExtDirectStoreResult<User> read(ExtDirectStoreReadRequest request) {

	JPQLQuery<User> query = this.jpaQueryFactory.selectFrom(QUser.user);
	if (!request.getFilters().isEmpty()) {
		StringFilter filter = (StringFilter) request.getFilters().iterator().next();

		BooleanBuilder bb = new BooleanBuilder();
		bb.or(QUser.user.loginName.containsIgnoreCase(filter.getValue()));
		bb.or(QUser.user.lastName.containsIgnoreCase(filter.getValue()));
		bb.or(QUser.user.firstName.containsIgnoreCase(filter.getValue()));
		bb.or(QUser.user.email.containsIgnoreCase(filter.getValue()));

		query.where(bb);
	}
	query.where(QUser.user.deleted.isFalse());

	QuerydslUtil.addPagingAndSorting(query, request, User.class, QUser.user);
	QueryResults<User> searchResult = query.fetchResults();

	return new ExtDirectStoreResult<>(searchResult.getTotal(),
			searchResult.getResults());
}
 

public List<Feed> findNextUpdatable(int count, Date lastLoginThreshold) {
	JPQLQuery<Feed> query = query().selectFrom(feed);
	query.where(feed.disabledUntil.isNull().or(feed.disabledUntil.lt(new Date())));

	if (lastLoginThreshold != null) {
		QFeedSubscription subs = QFeedSubscription.feedSubscription;
		QUser user = QUser.user;

		JPQLQuery<Integer> subQuery = JPAExpressions.selectOne().from(subs);
		subQuery.join(subs.user, user).where(user.lastLogin.gt(lastLoginThreshold));
		query.where(subQuery.exists());
	}

	return query.orderBy(feed.disabledUntil.asc()).limit(count).distinct().fetch();
}
 

public List<FeedSubscription> findByCategory(User user, FeedCategory category) {
	JPQLQuery<FeedSubscription> query = query().selectFrom(sub).where(sub.user.eq(user));
	if (category == null) {
		query.where(sub.category.isNull());
	} else {
		query.where(sub.category.eq(category));
	}
	return initRelations(query.fetch());
}
 

public int deleteWithoutEntries(int max) {

		JPQLQuery<Integer> subQuery = JPAExpressions.selectOne().from(entry).where(entry.content.id.eq(content.id));
		List<FeedEntryContent> list = query().selectFrom(content).where(subQuery.notExists()).limit(max).fetch();

		int deleted = list.size();
		delete(list);
		return deleted;
	}
 

@Override
public List<CountByAccountAndType> countByAccountAndType(final Set<String> accountIds,
                                                         final Optional<DateTime> fromDate,
                                                         final Optional<DateTime> toDate,
                                                         final boolean resolved,
                                                         final boolean whitelisted) {
    final QViolationEntity qViolation = new QViolationEntity("v");
    final QViolationTypeEntity qType = new QViolationTypeEntity("t");


    final JPQLQuery<ViolationEntity> query = from(qViolation);
    query.join(qViolation.violationTypeEntity, qType);

    final Collection<Predicate> whereClause = newArrayList();

    if (!accountIds.isEmpty()) {
        whereClause.add(qViolation.accountId.in(accountIds));
    }

    fromDate.map(qViolation.created::after).ifPresent(whereClause::add);
    toDate.map(qViolation.created::before).ifPresent(whereClause::add);

    if (whitelisted) {
        whereClause.add(qViolation.ruleEntity.isNotNull());
    } else if (resolved) {
        whereClause.add(qViolation.comment.isNotNull());
        whereClause.add(qViolation.ruleEntity.isNull());
    } else {
        whereClause.add(qViolation.comment.isNull());
        whereClause.add(qViolation.ruleEntity.isNull());
    }

    query.where(allOf(whereClause));

    query.groupBy(qViolation.accountId, qType.id);
    query.orderBy(qViolation.accountId.asc(), qType.id.asc());

    return query.select(Projections.constructor(CountByAccountAndType.class, qViolation.accountId, qType.id, qViolation.id.count())).fetch();
}
 

public static void addSorting(JPQLQuery<?> query, ExtDirectStoreReadRequest request,
		Class<?> clazz, EntityPathBase<?> entityPathBase,
		Map<String, String> mapGuiColumn2Dbfield, Set<String> sortIgnoreProperties) {

	if (!request.getSorters().isEmpty()) {
		query.orderBy(createOrderSpecifiers(request, clazz, entityPathBase,
				mapGuiColumn2Dbfield, sortIgnoreProperties));
	}

}
 

public static void addPagingAndSorting(JPQLQuery<?> query,
		ExtDirectStoreReadRequest request, Class<?> clazz,
		EntityPathBase<?> entityPathBase, Map<String, String> mapGuiColumn2Dbfield,
		Set<String> sortIgnoreProperties) {

	if (request.getStart() != null && request.getLimit() != null
			&& request.getLimit() > 0) {
		query.offset(request.getStart()).limit(request.getLimit());
	}

	addSorting(query, request, clazz, entityPathBase, mapGuiColumn2Dbfield,
			sortIgnoreProperties);
}
 

/**
 * Test setup, creates a new {@link QueryDslRepositorySupportExt} to use in the test methods.
 */
@Before
public void setUp() throws Exception {
  support = new QueryDslRepositorySupportExt<Object>(Object.class) {
    @Override
    protected JPQLQuery<Object> applyPagination(Pageable pageable, JPQLQuery<Object> query) {
      return query;
    }
  };
}
 

@Override
protected JPQLQuery<?> createCountQuery(Predicate... predicate) {
  return CountQueryDetector.proxy(super.createCountQuery(predicate));
}
 

static JPQLQuery<?> proxy(JPQLQuery<?> countQuery) {
  ProxyFactory proxyFactory = new ProxyFactory(countQuery);
  proxyFactory.addAdvice(INSTANCE);
  return (JPQLQuery<?>) proxyFactory.getProxy();
}
 

public static void addPagingAndSorting(JPQLQuery<?> query,
		ExtDirectStoreReadRequest request, Class<?> clazz,
		EntityPathBase<?> entityPathBase) {
	addPagingAndSorting(query, request, clazz, entityPathBase,
			Collections.<String, String>emptyMap(), Collections.<String>emptySet());
}
 

public static void addSorting(JPQLQuery<?> query, ExtDirectStoreReadRequest request,
		Class<?> clazz, EntityPathBase<?> entityPathBase) {
	addSorting(query, request, clazz, entityPathBase,
			Collections.<String, String>emptyMap(), Collections.<String>emptySet());
}
 

@Override
public Page<ViolationEntity> queryViolations(final List<String> accounts,
                                             final DateTime from,
                                             final DateTime to,
                                             final Long lastViolation,
                                             final boolean checked,
                                             final Integer severity,
                                             final Integer priority,
                                             final Boolean auditRelevant,
                                             final List<String> types,
                                             final boolean whitelisted,
                                             final List<String> applicationIds,
                                             final List<String> applicationVersionIds,
                                             final Pageable pageable) {

    final QViolationEntity qViolationEntity = QViolationEntity.violationEntity;
    final QViolationTypeEntity qViolationTypeEntity = QViolationTypeEntity.violationTypeEntity;

    final JPQLQuery<ViolationEntity> query = from(qViolationEntity).leftJoin(qViolationEntity.violationTypeEntity, qViolationTypeEntity);

    final List<Predicate> predicates = newArrayList();

    if (accounts != null) {
        predicates.add(qViolationEntity.accountId.in(accounts));
    }

    if (from != null) {
        predicates.add(qViolationEntity.created.after(from));
    }

    if (to != null) {
        predicates.add(qViolationEntity.created.before(to));
    }

    if (lastViolation != null) {
        predicates.add(qViolationEntity.id.goe(lastViolation));
    }

    if (whitelisted) {
        predicates.add(qViolationEntity.ruleEntity.isNotNull());
    } else if (checked) {
        predicates.add(qViolationEntity.comment.isNotNull());
        predicates.add(qViolationEntity.ruleEntity.isNull());
    } else {
        predicates.add(qViolationEntity.comment.isNull());
        predicates.add(qViolationEntity.ruleEntity.isNull());
    }

    if (severity != null) {
        predicates.add(qViolationTypeEntity.violationSeverity.eq(severity));
    }

    if (priority != null) {
        predicates.add(qViolationTypeEntity.priority.eq(priority));
    }

    if (auditRelevant != null) {

        predicates.add(qViolationTypeEntity.isAuditRelevant.eq(auditRelevant));
    }

    if (types != null && !types.isEmpty()) {
        predicates.add(qViolationEntity.violationTypeEntity.id.in(types));
    }

    if (applicationIds != null && !applicationIds.isEmpty()) {
        predicates.add(qViolationEntity.application.name.in(applicationIds));
    }

    if (applicationVersionIds != null && !applicationVersionIds.isEmpty()) {
        predicates.add(qViolationEntity.applicationVersion.name.in(applicationVersionIds));
    }

    final long total = query.where(allOf(predicates)).fetchCount();

    final Sort sort = pageable.getSort();
    final Sort fixedSort = (sort == null || isEmpty(sort)) ? SORT_BY_ID : sort;
    final PageRequest fixedPage = new PageRequest(pageable.getPageNumber(), pageable.getPageSize(), fixedSort);

    getQuerydsl().applyPagination(fixedPage, query);

    final List<ViolationEntity> list;
    list = total > 0 ? query.where(allOf(predicates)).fetch() : emptyList();

    return new PageImpl<>(list, fixedPage, total);
}
 

/**
 * Applies the given {@link Pageable} to the given {@link JPQLQuery}.
 * Allows to map the attributes to order as provided in the {@link Pageable}
 * to real entity attributes. This might be used to work with projections
 * or DTOs whose attributes don't have the same name as the entity ones.
 *
 * It allows to map to more than one entity attribute. As an example, if
 * the DTO used to create the {@link Pageable} has a fullName attribute, you
 * could map that attribute to two entity attributes: name and surname.
 * In this case, the {@link Pageable} defines an order by a fullName
 * attribute, but que query will order by name and surname instead.
 *
 * @param pageable the ordering and paging
 * @param query
 * @param attributeMapping definition of a mapping of order attribute names
 *        to real entity ones
 * @return the updated query
 */
protected JPQLQuery<T> applyPagination(Pageable pageable, JPQLQuery<T> query,
    Map<String, Path<?>[]> attributeMapping) {

  if (pageable == null) {
    return query;
  }

  Pageable mappedPageable;
  Sort sort = pageable.getSort();
  if (sort != null) {
    List<Sort.Order> mappedOrders = new ArrayList<Sort.Order>();
    for (Sort.Order order : sort) {
      if (!attributeMapping.containsKey(order.getProperty())) {
        LOG.warn(
            "The property (%1) is not included in the attributeMapping, will order "
                + "using the property as it is",
            order.getProperty());
        mappedOrders.add(order);
      } else {
        Path<?>[] paths = attributeMapping.get(order.getProperty());
        for (Path<?> path : paths) {
          Sort.Order mappedOrder =
              new Sort.Order(order.getDirection(), preparePropertyPath(path));
          mappedOrders.add(mappedOrder);
        }
      }
    }
    if (mappedOrders.isEmpty()) {
      // No properties to order by are available, so don't apply ordering and return the query
      // as it is
      return query;
    }
    mappedPageable =
        new PageRequest(pageable.getPageNumber(), pageable.getPageSize(), new Sort(mappedOrders));
    return applyPagination(mappedPageable, query);
  } else {
    return applyPagination(pageable, query);
  }

}
 

@SuppressWarnings("unchecked")
@Override
protected JPQLQuery<T> createQuery(Predicate... predicate) {
    return (JPQLQuery<T>) super.createQuery(predicate);
}
 

/**
 * Adds a global contains text filter on the provided attributes.
 * WARNING: this creates a very inefficient query. If you have many entity
 * instances to query, use instead an indexed text search solution for better
 * performance.
 * @param globalSearch Contains the text to look for
 * @param query
 * @param globalSearchAttributes the list of attributes to perform the
 *        filter on
 * @return the updated query
 */
protected JPQLQuery<T> applyGlobalSearch(GlobalSearch globalSearch, JPQLQuery<T> query,
    Path<?>... globalSearchAttributes) {
  if (globalSearch != null) {
    String txt = globalSearch.getText();
    return applyGlobalSearch(txt, query, globalSearchAttributes);
  }
  return query;
}
 

/**
 * Adds to a query an order by the entity identifier related to this repository.
 * This is useful as the default last order in queries where pagination is
 * applied, so you have always an absolute order. Otherwise, the order
 * of the results depends on the database criteria, which might change
 * even between pages, returning confusing results for the user.
 * @param query
 * @return the updated query
 */
@SuppressWarnings({"rawtypes", "unchecked"})
protected JPQLQuery<T> applyOrderById(JPQLQuery<T> query) {
  PathBuilder<Object> idPath = getEntityId();

  return query.orderBy(new OrderSpecifier(Order.ASC, idPath, NullHandling.NullsFirst));
}
 

/**
 * Loads a page of data with the provided pagination criteria. It allows to
 * load full entities as well as projections.
 *
 * TODO: the current implementation expects the query to have already applied
 * the paging and sorting criteria, which is not what one could expect from
 * the method signature.
 *
 * Sample loading entities:
 *
 * <pre class="code">
 * loadPage(query, pageable, QEmployee.employee);
 * </pre>
 *
 * Sample with a projection:
 *
 * <pre class="code">
 * loadPage(query, pageable, Projections.constructor(EmployeeInfo.class,
 *    employee.id, employee.firstName, employee.lastName, employee.phone, employee.extension,
 *    employee.supervisor.id, employee.supervisor.firstName, employee.supervisor.lastName));
 * </pre>
 *
 * @param <M> the data type to load, usually a JPA Entity or a projection bean
 * @param query the query with the pagination and ordering criteria already applied
 * @param pageable the already applied pagination and ordering criteria
 * @param expression the entity or projection to build with the query data
 * @return the loaded data page
 */
protected <M> Page<M> loadPage(JPQLQuery<T> query, Pageable pageable, Expression<M> expression) {
  long totalFound = query.fetchCount();
  List<M> results = query.select(expression).fetch();
  return new PageImpl<M>(results, pageable, totalFound);
}
 

@Override
public Page<LifecycleEntity> findByApplicationNameAndVersion(final String name, final String version, final Pageable pageable) {

    final QLifecycleEntity qLifecycleEntity = QLifecycleEntity.lifecycleEntity;
    final QApplicationEntity qApplicationEntity = QApplicationEntity.applicationEntity;
    final QVersionEntity qVersionEntity = QVersionEntity.versionEntity;


    final JPQLQuery<LifecycleEntity> query = from(qLifecycleEntity).leftJoin(qLifecycleEntity.applicationEntity, qApplicationEntity);

    if (version != null && isNotEmpty(version)) {
        query.join(qLifecycleEntity.versionEntity, qVersionEntity);
        query.where(qVersionEntity.name.eq(version));
    }

    query.where(qApplicationEntity.name.eq(name));

    final long total = query.fetchCount();

    query.groupBy(qLifecycleEntity.versionEntity,
            qLifecycleEntity.instanceId,
            qLifecycleEntity.created,
            qLifecycleEntity.id,
            qApplicationEntity.id);

    if (version != null && isNotEmpty(version)) {
        query.groupBy(qVersionEntity.id);
    }


    final Sort sort = pageable.getSort();
    final Sort fixedSort = (sort == null || isEmpty(sort)) ? SORT_BY_CREATED : sort;

    final PageRequest pageRequest = new PageRequest(pageable.getPageNumber(), pageable.getPageSize(), fixedSort);

    getQuerydsl().applyPagination(pageRequest, query);

    final List<LifecycleEntity> lifecycleEntities = total > 0 ? query.fetch() : emptyList();

    return new PageImpl<>(lifecycleEntities, pageRequest, total);

}
 

/**
 * Applies the given {@link Pageable} to the given {@link JPQLQuery}.
 * Allows to map the attributes to order as provided in the {@link Pageable}
 * to real entity attributes. This might be used to work with projections
 * or DTOs whose attributes don't have the same name as the entity ones.
 *
 * It allows to map to more than one entity attribute. As an example, if
 * the DTO used to create the {@link Pageable} has a fullName attribute, you
 * could map that attribute to two entity attributes: name and surname.
 * In this case, the {@link Pageable} defines an order by a fullName
 * attribute, but que query will order by name and surname instead.
 *
 * @param pageable the ordering and paging
 * @param query
 * @param mapping definition of a mapping of order attribute names to
 *        real entity ones
 * @return the updated query
 */
protected JPQLQuery<T> applyPagination(Pageable pageable, JPQLQuery<T> query,
    AttributeMappingBuilder mapping) {
  return applyPagination(pageable, query, mapping.asMap());
}
 

/**
 * Applies the given {@link Pageable} to the given {@link JPQLQuery}.
 *
 * @param pageable the ordering and paging information
 * @param query the query to apply to
 * @return the updated query
 */
protected JPQLQuery<T> applyPagination(Pageable pageable, JPQLQuery<T> query) {
  return getQuerydsl().applyPagination(pageable, query);
}