/**
* 废弃
*
* @param user
* @param pageable
* @return
*/
public Page<User> getUsersByCondition(User user, Pageable pageable) {
System.out.println(user);
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<User> cq = cb.createQuery(User.class);
Root<User> root = cq.from(User.class);
cq.select(root);
// 使用like的时候可以不用判断是否有该参数传来，因为like %% 就等于没有这个限制条件
Predicate condition1 =
cb.like(root.get("username").as(String.class), cb.parameter(String.class, "username"));
Predicate condition2 =
cb.like(root.get("userAlias").as(String.class), cb.parameter(String.class, "userAlias"));
// Predicate
// condition3=cb.equal(root.get("createTime").as(Date.class),cb.parameter(String.class,
// "createTime"));
cq.where(condition1, condition2);
cb.and(condition1, condition2);
TypedQuery<User> query = em.createQuery(cq);
query.setParameter("username", "%" + user.getUsername() + "%");
query.setParameter("userAlias", "%" + user.getUserAlias() + "%");
query.setFirstResult(pageable.getOffset());
query.setMaxResults(pageable.getPageSize());
List<User> users = query.getResultList();
Page<User> page = new PageImpl<User>(users, pageable, 10);
return page;
}
@Override
public List<ClassTeacherMap> findClassesBySchoolAndMember(
DictSchool school, Member member, ClassStatus classStatus) {
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<ClassTeacherMap> criteriaQuery =
criteriaBuilder.createQuery(ClassTeacherMap.class);
Root<ClassTeacherMap> root = criteriaQuery.from(ClassTeacherMap.class);
criteriaQuery.select(root);
Predicate restrictions = criteriaBuilder.conjunction();
if (school != null) {
restrictions =
criteriaBuilder.and(
restrictions, criteriaBuilder.equal(root.get("dictClass").get("dictSchool"), school));
}
if (member != null) {
restrictions =
criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("member"), member));
}
if (classStatus != null) {
restrictions =
criteriaBuilder.and(
restrictions,
criteriaBuilder.equal(root.get("dictClass").get("classStatus"), classStatus));
}
criteriaQuery.where(restrictions);
return entityManager
.createQuery(criteriaQuery)
.setFlushMode(FlushModeType.COMMIT)
.getResultList();
}
@Override
public List<Category> getCategoryByParameters(Map<String, Object> parameters) {
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Category> cq = cb.createQuery(Category.class);
Root<Category> r = cq.from(Category.class);
Join<RssUser, Category> ru = r.join("rssUserList", JoinType.LEFT);
Join<RssUser, Rss> rss = ru.join("rss", JoinType.LEFT);
Predicate p = cb.conjunction();
for (Map.Entry<String, Object> param : parameters.entrySet()) {
if (param.getKey().equals("rssId")) {
p = cb.and(p, cb.equal(rss.get("rssId"), param.getValue()));
} else if (param.getKey().equals("guid")) {
p = cb.and(p, cb.equal(r.get(param.getKey()), param.getValue()));
}
}
p = cb.or(p, cb.equal(r.get("categoryId"), DEFAULT_CATEGORY_ID));
cq.distinct(true);
cq.multiselect().where(p);
TypedQuery typedQuery = em.createQuery(cq);
List<Category> resultList = typedQuery.getResultList();
return resultList;
}
/**
* Gets the all predicates.
*
* @param criteriaBuilder the criteria builder
* @return the all predicates
*/
public Predicate[] getAllPredicates(CriteriaBuilder criteriaBuilder) {
List<Predicate> allPredicates = new ArrayList<Predicate>();
for (PredicateGroup predicateGroup : predicates.values()) {
List<Predicate> andPredicates = predicateGroup.getAndPredicates();
if (!andPredicates.isEmpty()) {
allPredicates.add(
criteriaBuilder.and(andPredicates.toArray(new Predicate[andPredicates.size()])));
}
List<Predicate> orPredicates = predicateGroup.getOrPredicates();
if (!orPredicates.isEmpty()) {
allPredicates.add(
criteriaBuilder.or(orPredicates.toArray(new Predicate[orPredicates.size()])));
}
}
if (!andWithOrPredicates.isEmpty()) {
List<Predicate> allAndOrPredicates = new ArrayList<Predicate>();
for (PredicateGroup predicateGroup : andWithOrPredicates.values()) {
List<Predicate> andPredicates = predicateGroup.getAndPredicates();
allAndOrPredicates.add(
criteriaBuilder.and(andPredicates.toArray(new Predicate[andPredicates.size()])));
}
allPredicates.add(
criteriaBuilder.or(allAndOrPredicates.toArray(new Predicate[allAndOrPredicates.size()])));
}
return allPredicates.toArray(new Predicate[allPredicates.size()]);
}
@Override
public List<Train> find(TrainFilter trainFilter) {
EntityManager em = getEntityManager();
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Train> cq = cb.createQuery(Train.class);
Root<Train> from = cq.from(Train.class);
cq.select(from);
if (trainFilter.getId() != null) {
Predicate idEqual = cb.equal(from.get(Train_.id), trainFilter.getId());
cq.where(cb.and(idEqual));
}
if (trainFilter.getName() != null) {
Predicate idEqual = cb.equal(from.get(Train_.id), trainFilter.getName());
cq.where(cb.and(idEqual));
}
if (trainFilter.getSortProperty() != null) {
cq.orderBy(new OrderImpl(from.get(trainFilter.getSortProperty()), trainFilter.isSortOrder()));
}
TypedQuery<Train> q = em.createQuery(cq);
if (trainFilter.getOffset() != null && trainFilter.getLimit() != null) {
q.setFirstResult(trainFilter.getOffset());
q.setMaxResults(trainFilter.getLimit());
}
List<Train> allitems = q.getResultList();
return allitems;
}
public List<Goods> findList(
ProductCategory productCategory,
Boolean isMarketable,
Goods.GenerateMethod generateMethod,
Date beginDate,
Date endDate,
Integer first,
Integer count) {
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<Goods> criteriaQuery = criteriaBuilder.createQuery(Goods.class);
Root<Goods> root = criteriaQuery.from(Goods.class);
criteriaQuery.select(root);
Predicate restrictions = criteriaBuilder.conjunction();
if (productCategory != null) {
Subquery<ProductCategory> subquery = criteriaQuery.subquery(ProductCategory.class);
Root<ProductCategory> subqueryRoot = subquery.from(ProductCategory.class);
subquery.select(subqueryRoot);
subquery.where(
criteriaBuilder.or(
criteriaBuilder.equal(subqueryRoot, productCategory),
criteriaBuilder.like(
subqueryRoot.<String>get("treePath"),
"%"
+ ProductCategory.TREE_PATH_SEPARATOR
+ productCategory.getId()
+ ProductCategory.TREE_PATH_SEPARATOR
+ "%")));
restrictions =
criteriaBuilder.and(
restrictions, criteriaBuilder.in(root.get("productCategory")).value(subquery));
}
if (isMarketable != null) {
restrictions =
criteriaBuilder.and(
restrictions, criteriaBuilder.equal(root.get("isMarketable"), isMarketable));
}
if (generateMethod != null) {
restrictions =
criteriaBuilder.and(
restrictions, criteriaBuilder.equal(root.get("generateMethod"), generateMethod));
}
if (beginDate != null) {
restrictions =
criteriaBuilder.and(
restrictions,
criteriaBuilder.greaterThanOrEqualTo(root.<Date>get("createDate"), beginDate));
}
if (endDate != null) {
restrictions =
criteriaBuilder.and(
restrictions,
criteriaBuilder.lessThanOrEqualTo(root.<Date>get("createDate"), endDate));
}
criteriaQuery.where(restrictions);
return super.findList(criteriaQuery, first, count, null, null);
}
public List<Object[]> findPurchaseList(Date beginDate, Date endDate, Integer count) {
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<Object[]> criteriaQuery = criteriaBuilder.createQuery(Object[].class);
Root<Member> member = criteriaQuery.from(Member.class);
Join<Product, Order> orders = member.join("orders");
criteriaQuery.multiselect(
member.get("id"),
member.get("username"),
member.get("email"),
member.get("point"),
member.get("amount"),
member.get("balance"),
criteriaBuilder.sum(orders.<BigDecimal>get("amountPaid")));
Predicate restrictions = criteriaBuilder.conjunction();
if (beginDate != null) {
restrictions =
criteriaBuilder.and(
restrictions,
criteriaBuilder.greaterThanOrEqualTo(orders.<Date>get("createDate"), beginDate));
}
if (endDate != null) {
restrictions =
criteriaBuilder.and(
restrictions,
criteriaBuilder.lessThanOrEqualTo(orders.<Date>get("createDate"), endDate));
}
restrictions =
criteriaBuilder.and(
restrictions,
criteriaBuilder.equal(orders.get("orderStatus"), Order.OrderStatus.completed),
criteriaBuilder.equal(orders.get("paymentStatus"), Order.PaymentStatus.paid));
criteriaQuery.where(restrictions);
criteriaQuery.groupBy(
member.get("id"),
member.get("username"),
member.get("email"),
member.get("point"),
member.get("amount"),
member.get("balance"));
criteriaQuery.orderBy(
criteriaBuilder.desc(criteriaBuilder.sum(orders.<BigDecimal>get("amountPaid"))));
TypedQuery<Object[]> query =
entityManager.createQuery(criteriaQuery).setFlushMode(FlushModeType.COMMIT);
if (count != null && count >= 0) {
query.setMaxResults(count);
}
return query.getResultList();
}
@Override
public final List<Product> findByFeatures(final Map<Integer, List<String>> featureValues) {
CriteriaBuilder builder = manager.getCriteriaBuilder();
CriteriaQuery<Product> criteriaQuery = builder.createQuery(Product.class);
Root<Product> product = criteriaQuery.from(Product.class);
Path<ProductFeature> feature = product.join("features");
CriteriaQuery<Product> select = criteriaQuery.select(product);
Predicate featurePredicate = builder.disjunction();
for (final Map.Entry<Integer, List<String>> fValue : featureValues.entrySet()) {
Predicate equalFeatureId = builder.equal(feature.get("featureId"), fValue.getKey());
List<String> values = fValue.getValue();
Predicate equalsValues = builder.disjunction();
for (String value : values) {
Predicate equalFeatureVal = builder.equal(feature.get("value"), value);
equalsValues = builder.or(equalsValues, equalFeatureVal);
}
featurePredicate = builder.or(featurePredicate, builder.and(equalFeatureId, equalsValues));
}
select.where(featurePredicate);
select.groupBy(product.get("id"));
select.having(builder.equal(builder.count(product), featureValues.size()));
TypedQuery<Product> query = manager.createQuery(criteriaQuery);
return query.getResultList();
}
private Predicate buildSimplePredicate(Path<T> path, String name, String value) {
Predicate predicate;
CriteriaBuilder criteriaBuilder = getEntityManager().getCriteriaBuilder();
if (isMultipleOrValue(value)) {
// name=value1,value,...,valueN
// => name=value1 OR name=value OR ... OR name=valueN
List<String> valueList = convertMultipleOrValueToList(value);
List<Predicate> orPredicates = new ArrayList<Predicate>();
for (String currentValue : valueList) {
Predicate orPredicate = buildPredicateWithOperator(path, name, currentValue);
orPredicates.add(orPredicate);
}
predicate = criteriaBuilder.or(orPredicates.toArray(new Predicate[orPredicates.size()]));
} else if (isMultipleAndValue(value)) {
// name=(subname1=value1&subname2=value&...&subnameN=valueN)
// => name.subname1=value1 AND name.subname2=value AND ... AND name.subnameN=valueN
List<Map.Entry<String, String>> subFieldNameValue = convertMultipleAndValue(value);
List<Predicate> andPredicates = new ArrayList<Predicate>();
Path<T> root = path.get(name);
for (Map.Entry<String, String> entry : subFieldNameValue) {
String currentsubFieldName = entry.getKey();
String currentValue = entry.getValue();
Predicate andPredicate = buildPredicate(root, currentsubFieldName, currentValue);
andPredicates.add(andPredicate);
}
predicate = criteriaBuilder.and(andPredicates.toArray(new Predicate[andPredicates.size()]));
} else {
// name=value
predicate = buildPredicateWithOperator(path, name, value);
}
return predicate;
}
private List<Vote> findVoteEntitiesByPollAltIds(
Long pollId, Long altId, boolean all, int maxResults, int firstResult) {
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Vote> q = cb.createQuery(Vote.class);
Root<Vote> vote = q.from(Vote.class);
ParameterExpression<Long> pid = cb.parameter(Long.class);
ParameterExpression<Long> aid = cb.parameter(Long.class);
q.select(vote)
.where(
cb.and(
cb.equal(vote.get(Vote_.alternative).get(Alternative_.poll).get(Poll_.id), pid),
cb.equal(vote.get(Vote_.alternative).get(Alternative_.id), aid)));
TypedQuery<Vote> query = em.createQuery(q);
query.setParameter(pid, pollId).setParameter(aid, altId);
if (!all) {
query.setMaxResults(maxResults);
query.setFirstResult(firstResult);
}
List<Vote> results = query.getResultList();
System.out.println(">>>>>>>>>>>>>>>>>>>> Votes: " + results);
return results;
}
public List<MyClass138> findByCriteria(MyClass138Criteria myClass138Criteria) {
javax.persistence.criteria.CriteriaBuilder cb = this.entityManager.getCriteriaBuilder();
javax.persistence.criteria.CriteriaQuery<MyClass138> c = cb.createQuery(MyClass138.class);
javax.persistence.criteria.Root<MyClass138> emp = c.from(MyClass138.class);
c.select(emp);
List<javax.persistence.criteria.Predicate> criteria =
new java.util.ArrayList<javax.persistence.criteria.Predicate>();
if (myClass138Criteria.getId() != null) {
javax.persistence.criteria.Expression<Long> p = emp.get("id");
javax.persistence.criteria.Expression<Long> val = cb.parameter(Long.class, "id");
criteria.add(cb.equal(p, val));
}
if (criteria.size() == 1) {
c.where(criteria.get(0));
} else {
c.where(cb.and(criteria.toArray(new javax.persistence.criteria.Predicate[0])));
}
javax.persistence.TypedQuery<MyClass138> q = this.entityManager.createQuery(c);
if (myClass138Criteria.getId() != null) {
q.setParameter("id", myClass138Criteria.getId());
}
return new java.util.ArrayList<MyClass138>(q.getResultList());
}
@Override
public final List<T> findListByProperty(
final Object[] values, final SingularAttribute<T, ? extends Object>... properties) {
final CriteriaQuery<T> criteriaQuery = criteriaBuilder.createQuery(persistentClass);
final Root<T> root = criteriaQuery.from(persistentClass);
criteriaQuery.select(root);
final Object value = values[0];
final SingularAttribute<T, ? extends Object> property = properties[0];
Predicate condition;
condition =
QueryHelper.equalsIgnoreCaseIfStringPredicate(criteriaBuilder, root, value, property);
if (values.length > 1) {
for (int i = 1; i < properties.length; i++) {
final SingularAttribute<T, ? extends Object> property2 = properties[i];
final Object value2 = values[i];
final Predicate condition2 =
QueryHelper.equalsIgnoreCaseIfStringPredicate(criteriaBuilder, root, value2, property2);
condition = criteriaBuilder.and(condition, condition2);
}
}
criteriaQuery.where(condition);
final TypedQuery<T> typedQuery = getEntityManager().createQuery(criteriaQuery);
addCacheHints(typedQuery, "findListByProperty");
return typedQuery.getResultList();
}
public Optional<NsiV2Message> findOneForUpdate(
NsiV2Message.Role role,
NsiV2Message.Type type,
String requesterNsa,
String providerNsa,
String correlationId) {
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<NsiV2Message> query = cb.createQuery(NsiV2Message.class);
Root<NsiV2Message> root = query.from(NsiV2Message.class);
query.where(
cb.and(
cb.equal(root.get(NsiV2Message_.role), role),
cb.equal(root.get(NsiV2Message_.type), type),
cb.equal(root.get(NsiV2Message_.requesterNsa), requesterNsa),
cb.equal(root.get(NsiV2Message_.providerNsa), providerNsa),
cb.equal(root.get(NsiV2Message_.correlationId), correlationId)));
return entityManager
.createQuery(query)
.setLockMode(LockModeType.PESSIMISTIC_WRITE)
.setMaxResults(1)
.getResultList()
.stream()
.findFirst();
}
protected static Predicate getPredicate(
final Class clazz,
final Restriction searchTerms,
Root<Persistable> root,
CriteriaBuilder cb) {
LinkedList<Predicate> predicates = new LinkedList<Predicate>();
Predicate predicate;
// process child restrictions
if (!CollectionUtils.isEmpty(searchTerms.getRestrictions())) {
for (Restriction restriction : searchTerms.getRestrictions()) {
predicates.add(getPredicate(clazz, restriction, root, cb));
}
}
// process main restriction
if (StringUtils.isNotBlank(searchTerms.getField())) {
String propertyName = searchTerms.getField();
addPredicate(
clazz,
root,
cb,
predicates,
searchTerms.getValues().toArray(new String[searchTerms.getValues().size()]),
propertyName);
}
if (searchTerms.getJunction().equals(Restriction.Junction.OR)) {
predicate = cb.or(predicates.toArray(new Predicate[predicates.size()]));
} else {
predicate = cb.and(predicates.toArray(new Predicate[predicates.size()]));
}
return predicate;
}
protected static Predicate getPredicate(
final Class clazz,
final Map<String, String[]> searchTerms,
Root<Persistable> root,
CriteriaBuilder cb) {
LinkedList<Predicate> predicates = new LinkedList<Predicate>();
Predicate predicate;
if (!CollectionUtils.isEmpty(searchTerms)) {
Set<String> propertyNames = searchTerms.keySet();
// put aside nested AND/OR param groups
NestedJunctions junctions = new NestedJunctions();
for (String propertyName : propertyNames) {
String[] values = searchTerms.get(propertyName);
if (!junctions.addIfNestedJunction(propertyName, values)) {
addPredicate(clazz, root, cb, predicates, values, propertyName);
}
}
// add nested AND/OR param groups
Map<String, Map<String, String[]>> andJunctions = junctions.getAndJunctions();
addJunctionedParams(clazz, root, cb, predicates, andJunctions, AND);
Map<String, Map<String, String[]>> orJunctions = junctions.getOrJunctions();
addJunctionedParams(clazz, root, cb, predicates, orJunctions, OR);
}
if (searchTerms.containsKey(SEARCH_MODE)
&& searchTerms.get(SEARCH_MODE)[0].equalsIgnoreCase(OR)) {
predicate = cb.or(predicates.toArray(new Predicate[predicates.size()]));
} else {
predicate = cb.and(predicates.toArray(new Predicate[predicates.size()]));
}
return predicate;
}
@Override
public List<TeamStage> getList() {
EntityManager em = EMF.getInstance().createEntityManager();
CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
CriteriaQuery<TeamStage> cq = criteriaBuilder.createQuery(TeamStage.class);
Root<TeamStage> teamStage = cq.from(TeamStage.class);
Predicate criteria = null;
if (this.teamName != null) {
criteria = criteriaBuilder.equal(teamStage.get("teamName"), this.teamName);
}
if (this.stageName != null) {
criteria =
criteriaBuilder.and(
criteria, criteriaBuilder.equal(teamStage.get("stageName"), this.stageName));
}
if (this.stageBranch != null) {
criteria =
criteriaBuilder.and(
criteria, criteriaBuilder.equal(teamStage.get("stageBranch"), this.stageBranch));
}
if (this.stageOrder == TEAM_ENDED_GAME) {
Predicate equalEnded = criteriaBuilder.equal(teamStage.get("stageOrder"), this.stageOrder);
criteria = criteria != null ? criteriaBuilder.and(criteria, equalEnded) : equalEnded;
} else {
Predicate notEqualOrder =
criteriaBuilder.notEqual(teamStage.get("stageOrder"), TEAM_ENDED_GAME);
criteria = criteria != null ? criteriaBuilder.and(criteria, notEqualOrder) : notEqualOrder;
}
if (criteria != null) {
cq.where(criteria);
}
cq.select(teamStage);
cq.orderBy(
criteriaBuilder.desc(teamStage.get("stageOrder")),
criteriaBuilder.asc(teamStage.get("stageDate")));
em.getTransaction().begin();
List<TeamStage> resultList = em.createQuery(cq).getResultList();
em.getTransaction().commit();
return resultList;
}
@SuppressWarnings({"rawtypes", "unchecked"})
@Override
public int obterQtdRegistros(Map<String, String> filters) throws Exception {
CriteriaBuilder cb = entity.getCriteriaBuilder();
CriteriaQuery q = cb.createQuery(EEATVolume.class);
Root<EEATVolume> c = q.from(EEATVolume.class);
/*
Join<EEATVolume, RegionalProxy> greg = c.join("regionalProxy");
Join<EEATVolume, UnidadeNegocioProxy> uneg = c.join("unidadeNegocioProxy");
Join<EEATVolume, MunicipioProxy> muni = c.join("municipioProxy");
Join<EEATVolume, LocalidadeProxy> loca = c.join("localidadeProxy");
*/
Join<EEATVolume, EEAT> eat = c.join("eeat");
q.select(cb.count(c));
if (filters != null && !filters.isEmpty()) {
Predicate[] predicates = new Predicate[filters.size()];
int i = 0;
for (Map.Entry<String, String> entry : filters.entrySet()) {
String key = entry.getKey();
String val = entry.getValue();
Expression<String> path;
try {
/*
if (key.equals("regionalProxy.nome")) path = greg.get("nome");
else if (key.equals("unidadeNegocioProxy.nome")) path = uneg.get("nome");
else if (key.equals("municipioProxy.nome")) path = muni.get("nome");
else if (key.equals("localidadeProxy.nome")) path = loca.get("nome");
*/
if (key.equals("eeat.descricao")) path = eat.get("descricao");
else path = c.get(key);
if (key.equals("referencia")) {
SimpleDateFormat formataData = new SimpleDateFormat("MM/yyyy");
Date dataConsumo = formataData.parse(val);
predicates[i] = cb.and(cb.equal(path, dataConsumo));
} else {
predicates[i] = cb.and(cb.like(cb.lower(path), "%" + val.toLowerCase() + "%"));
}
} catch (SecurityException ex) {
ex.printStackTrace();
}
i++;
}
q.where(predicates);
}
Query query = entity.createQuery(q);
return ((Long) query.getSingleResult()).intValue();
}
public BankAccount findBy(Beneficiary beneficiary, String bankAccountNumber, String bankCode) {
CriteriaBuilder cb = getEntityManager().getCriteriaBuilder();
CriteriaQuery<BankAccount> cq = cb.createQuery(BankAccount.class);
Root<BankAccount> rt = cq.from(BankAccount.class);
Predicate p = cb.conjunction();
p = cb.and(p, cb.equal(rt.get(BankAccount_.owner), beneficiary));
p = cb.and(p, cb.equal(rt.get(BankAccount_.bank).get(LegalPerson_.code), bankCode));
p = cb.and(p, cb.equal(rt.get(BankAccount_.accountNumber), bankAccountNumber));
cq.select(rt);
cq.where(p);
TypedQuery<BankAccount> q = getEntityManager().createQuery(cq);
return q.getSingleResult();
}
public List<Project> getProjectsByNameAndId(final String projectName, final long id) {
CriteriaQuery<Project> q = criteriaBuilder.createQuery(Project.class);
Root<Project> p = q.from(Project.class);
q.where(
criteriaBuilder.and(
criteriaBuilder.equal(p.get(Project_.name), projectName),
criteriaBuilder.notEqual(p.get(Project_.id), id)));
return crudService.createQuery(q).getResultList();
}
@SuppressWarnings("unchecked")
public Predicate getCanReadOrgPredicate(
User user,
OrgType type,
CriteriaBuilder cb,
CriteriaQuery<Organization> query,
boolean containRoot) {
// 一。获得可管理的顶层机构，不区分机构类型
List<Organization> topCanReadOrgs = getTopCanReadOrgs(user);
Root<Organization> from;
if (query.getRoots().size() > 0) {
from = (Root<Organization>) query.getRoots().iterator().next();
} else {
from = query.from(Organization.class);
}
if (topCanReadOrgs.size() == 0) {
return cb.isNull(from);
}
// 二。应用条件
// 1.机构范围限制(如果有全部数据权限不做限制)
Subquery<Organization> subquery = query.subquery(Organization.class);
Root<Organization> subfrom = subquery.from(Organization.class);
subquery.select(subfrom);
ListJoin<Organization, Organization> join =
subfrom.join(Organization_.ancestors, JoinType.INNER);
In<String> subin = cb.in(join.get(Organization_.id));
for (Organization o : topCanReadOrgs) {
subin = subin.value(o.getId());
}
// 2.应用机构类别
if (type != null) {
Predicate p = cb.equal(subfrom.get(Organization_.orgType), type);
subquery.where(cb.and(subin, p));
} else {
subquery.where(subin);
}
// 3.增加祖先节点
if (containRoot) {
In<String> in = cb.in(from.get(Organization_.id));
boolean hasdata = false;
for (Organization o : topCanReadOrgs) {
Organization parento = o.getParent();
while (parento != null) {
hasdata = true;
in = in.value(parento.getId());
parento = parento.getParent();
}
}
if (hasdata) {
return cb.or(cb.in(from).value(subquery), in);
}
}
return cb.in(from).value(subquery);
}
@NotNull(message = "{kundeSERV.notFound.criteria}")
public List<AbstractKunde> findKundenByCriteria(
String email, String nachname, String vorname, String plz, Date regDate) {
// SELECT DISTINCT k
// FROM AbstractKunde k
// WHERE email = ? AND nachname = ? AND k.adresse.plz = ? and seit = ?
final CriteriaBuilder builder = em.getCriteriaBuilder();
final CriteriaQuery<AbstractKunde> criteriaQuery = builder.createQuery(AbstractKunde.class);
final Root<? extends AbstractKunde> k = criteriaQuery.from(AbstractKunde.class);
Predicate pred = null;
if (email != null) {
final Path<String> emailPath = k.get(AbstractKunde_.email);
final String likeMail = "%" + email + "%";
pred = builder.like(emailPath, likeMail);
}
if (nachname != null) {
final Path<String> nachnamePath = k.get(AbstractKunde_.nachname);
final String likeNachname = "%" + nachname + "%";
final Predicate tmpPred = builder.like(nachnamePath, likeNachname);
pred = pred == null ? tmpPred : builder.and(pred, tmpPred);
}
if (vorname != null) {
final Path<String> vornamePath = k.get(AbstractKunde_.vorname);
final String likeVorname = "%" + vorname + "%";
final Predicate tmpPred = builder.like(vornamePath, likeVorname);
pred = pred == null ? tmpPred : builder.and(pred, tmpPred);
}
if (plz != null) {
final Path<String> plzPath = k.get(AbstractKunde_.adresse).get(Adresse_.plz);
final String likePlz = "%" + plz + "%";
final Predicate tmpPred = builder.like(plzPath, likePlz);
pred = pred == null ? tmpPred : builder.and(pred, tmpPred);
}
if (regDate != null) {
final Path<Date> regPath = k.get(AbstractKunde_.registrierdatum);
final Predicate tmpPred = builder.equal(regPath, regDate);
pred = pred == null ? tmpPred : builder.and(pred, tmpPred);
}
criteriaQuery.where(pred).distinct(true);
return em.createQuery(criteriaQuery).getResultList();
}
public static void main(String[] args) {
EntityManagerFactory emf = null;
EntityManager em = null;
EntityTransaction tx = null;
try {
emf = Persistence.createEntityManagerFactory("oguz-finances");
em = emf.createEntityManager();
tx = em.getTransaction();
tx.begin();
int pageNumber = 3;
int pageSize = 2;
// Bank bank = createBank();
// em.persist(bank);
Bank bank = em.find(Bank.class, 1L);
em.detach(bank);
// System.out.println(em.contains(bank));
// bank.setName("Another Demonstration2");
Bank bank2 = em.merge(bank);
// System.out.println(em.contains(bank2));
// System.out.println(bank2.getName());
bank.setName("doesnt occur");
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Transaction> criteriaQuery = cb.createQuery(Transaction.class);
Root<Transaction> root = criteriaQuery.from(Transaction.class);
Path<BigDecimal> amountPath = root.get("amount");
Path<String> transactionType = root.get("transactionType");
criteriaQuery
.select(root)
.where(
cb.and(
cb.le(amountPath, new BigDecimal("20.00")),
cb.equal(transactionType, "Withdrawl")));
TypedQuery<Transaction> query = em.createQuery(criteriaQuery);
// query.setFirstResult((pageNumber-1)*pageSize);
// query.setMaxResults(pageSize);
List<Transaction> transactions = query.getResultList();
for (Transaction t : transactions) {
System.out.println(t.getTitle());
}
tx.commit();
} catch (Exception e) {
tx.rollback();
e.printStackTrace();
} finally {
em.close();
emf.close();
}
}
/**
* This test provides a demonstration of using logical AND, OR, and NOT within a query where
* clause
*/
@Test
public void testLogical() {
log.info("*** testLogical() ***");
CriteriaBuilder cb = em.getCriteriaBuilder();
{
CriteriaQuery<Customer> qdef = cb.createQuery(Customer.class);
// select c from Customer c
// where (c.firstName='cat' AND c.lastName='inhat')
// OR c.firstName='thing'
Root<Customer> c = qdef.from(Customer.class);
qdef.select(c)
.where(
cb.or(
cb.and(cb.equal(c.get("firstName"), "cat"), cb.equal(c.get("lastName"), "inhat")),
cb.equal(c.get("firstName"), "thing")));
int rows = executeQuery(qdef).size();
assertEquals("unexpected number of rows", 3, rows);
}
{
CriteriaQuery<Customer> qdef = cb.createQuery(Customer.class);
// select c from Customer c
// where (NOT (c.firstName='cat' AND c.lastName='inhat'))
// OR c.firstName='thing'
Root<Customer> c = qdef.from(Customer.class);
qdef.select(c)
.where(
cb.or(
cb.not(
cb.and(
cb.equal(c.get("firstName"), "cat"),
cb.equal(c.get("lastName"), "inhat"))),
cb.equal(c.get("firstName"), "thing")));
int rows = executeQuery(qdef).size();
assertEquals("unexpected number of rows", 2, rows);
}
}
@SuppressWarnings("CPD-START")
public List<Scenario> getScenariosByName(final long projectId, final String scenarioName) {
CriteriaQuery<Scenario> q = criteriaBuilder.createQuery(Scenario.class);
Root<Scenario> s = q.from(Scenario.class);
q.where(
criteriaBuilder.and(
criteriaBuilder.equal(s.get(Scenario_.name), scenarioName),
criteriaBuilder.equal(s.get(Scenario_.project).get(Project_.id), projectId)));
return crudService.createQuery(q).getResultList();
}
@Override
public Long count(
Coupon coupon, Member member, Boolean hasBegun, Boolean hasExpired, Boolean isUsed) {
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<CouponCode> criteriaQuery = criteriaBuilder.createQuery(CouponCode.class);
Root<CouponCode> root = criteriaQuery.from(CouponCode.class);
criteriaQuery.select(root);
Predicate restrictions = criteriaBuilder.conjunction();
Path<Coupon> couponPath = root.get("coupon");
if (coupon != null) {
restrictions = criteriaBuilder.and(restrictions, criteriaBuilder.equal(couponPath, coupon));
}
if (member != null) {
restrictions =
criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("member"), member));
}
if (hasBegun != null) {
if (hasBegun) {
restrictions =
criteriaBuilder.and(
restrictions,
criteriaBuilder.or(
couponPath.get("beginDate").isNull(),
criteriaBuilder.lessThanOrEqualTo(
couponPath.<Date>get("beginDate"), new Date())));
} else {
restrictions =
criteriaBuilder.and(
restrictions,
couponPath.get("beginDate").isNotNull(),
criteriaBuilder.greaterThan(couponPath.<Date>get("beginDate"), new Date()));
}
}
if (hasExpired != null) {
if (hasExpired) {
restrictions =
criteriaBuilder.and(
restrictions,
couponPath.get("endDate").isNotNull(),
criteriaBuilder.lessThan(couponPath.<Date>get("endDate"), new Date()));
} else {
restrictions =
criteriaBuilder.and(
restrictions,
criteriaBuilder.or(
couponPath.get("endDate").isNull(),
criteriaBuilder.greaterThanOrEqualTo(
couponPath.<Date>get("endDate"), new Date())));
}
}
if (isUsed != null) {
restrictions =
criteriaBuilder.and(restrictions, criteriaBuilder.equal(root.get("isUsed"), isUsed));
}
criteriaQuery.where(restrictions);
return super.count(criteriaQuery, null);
}
@Override
public PadHardVersion getVersion(Pad pad, long versionId) {
CriteriaBuilder builder = getCriteriaBuilder();
CriteriaQuery<PadHardVersion> query = builder.createQuery(PadHardVersion.class);
Root<PadHardVersion> version = query.from(PadHardVersion.class);
query.where(
builder.and(
builder.equal(version.get(PadHardVersion_.pad), pad),
builder.equal(version.get(PadHardVersion_.version), versionId)));
return getSingleResult(query);
}
@Override
public List<M> findByContestJidSinceTime(String contestJid, long time) {
CriteriaBuilder cb = JPA.em().getCriteriaBuilder();
CriteriaQuery<M> query = cb.createQuery(getModelClass());
Root<M> root = query.from(getModelClass());
query.where(
cb.and(cb.equal(root.get("contestJid"), contestJid), cb.le(root.get("timeCreate"), time)));
return JPA.em().createQuery(query).getResultList();
}
public List<Familia> getPorSituacaoELetra(Situacao situacao, String c) {
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Familia> cq = cb.createQuery(Familia.class);
Root<Familia> rt = cq.from(Familia.class);
cq.where(
cb.and(
cb.equal(rt.get("situacao"), situacao),
cb.like(cb.upper(rt.get(Familia_.nome)), c.toUpperCase() + "%")));
cq.orderBy(cb.asc(rt.get(Familia_.nome)));
return em.createQuery(cq).getResultList();
}
private static Predicate buildPredicate(
String name,
String email,
String phone,
String type,
String region,
String district,
String locality,
String streetToSearch,
Root<Organization> root,
CriteriaBuilder cb) {
Predicate queryPredicate = cb.conjunction();
if (StringUtils.isNotEmpty(name)) {
queryPredicate = cb.and(cb.like(root.get("name"), "%" + name + "%"), queryPredicate);
}
if (StringUtils.isNotEmpty(email)) {
queryPredicate = cb.and(cb.like(root.get("email"), "%" + email + "%"), queryPredicate);
}
if (StringUtils.isNotEmpty(phone)) {
queryPredicate = cb.and(cb.like(root.get("phone"), "%" + phone + "%"), queryPredicate);
}
if (StringUtils.isNotEmpty(type)) {
OrganizationType organizationType = OrganizationType.valueOf(type.trim());
queryPredicate =
cb.and(cb.isMember(organizationType, root.get("organizationTypes")), queryPredicate);
}
if (StringUtils.isNotEmpty(region)) {
queryPredicate =
cb.and(cb.like(root.get("address").get("region"), "%" + region + "%"), queryPredicate);
}
if (StringUtils.isNotEmpty(district)) {
queryPredicate =
cb.and(
cb.like(root.get("address").get("district"), "%" + district + "%"), queryPredicate);
}
if (StringUtils.isNotEmpty(locality)) {
queryPredicate =
cb.and(
cb.like(root.get("address").get("locality"), "%" + locality + "%"), queryPredicate);
}
if (StringUtils.isNotEmpty(streetToSearch)) {
queryPredicate =
cb.and(
cb.like(root.get("address").get("street"), "%" + streetToSearch + "%"),
queryPredicate);
}
return queryPredicate;
}
public BankAccount findBy(String bankAccountNumber, Person owner)
throws BankAccountNotFoundException {
CriteriaBuilder cb = getEntityManager().getCriteriaBuilder();
CriteriaQuery<BankAccount> cq = cb.createQuery(BankAccount.class);
Root<BankAccount> rt = cq.from(BankAccount.class);
Predicate p = cb.conjunction();
p = cb.and(p, cb.equal(rt.get(BankAccount_.accountNumber), bankAccountNumber));
p = cb.and(p, cb.equal(rt.get(BankAccount_.owner), owner));
cq.select(rt);
cq.where(p);
try {
return getEntityManager().createQuery(cq).getSingleResult();
} catch (NoResultException ex) {
throw new BankAccountNotFoundException(bankAccountNumber);
}
}