public List<Company> retrieveCompanys(String name, String category) {
EntityManager em = createEntityManager();
List<Company> company = null;
try {
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Company> cq = cb.createQuery(Company.class);
Root<Company> c = cq.from(Company.class);
List<Predicate> conditions = new ArrayList<Predicate>();
if (name != null) {
Predicate nameCondition = cb.like(c.get(Company_.name), "%" + name + "%");
conditions.add(nameCondition);
}
if (category != null) {
Path<CompanyCategory> companyCategoryPath = c.get(Company_.category);
Path<String> companyCategoryNamePath =
companyCategoryPath.get(CompanyCategory_.categoryName);
Predicate categoryCondition = cb.like(companyCategoryNamePath, category + "%");
conditions.add(categoryCondition);
}
cq.where(conditions.toArray(new Predicate[0]));
TypedQuery<Company> query = em.createQuery(cq);
company = query.getResultList();
} finally {
em.close();
}
return company;
}
/**
* 废弃
*
* @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;
}
/** This test provides an example collection path using an LEFT OUTER JOIN */
@Test
public void testOuterJoin() {
log.info("*** testOuterJoin() ***");
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Object[]> qdef = cb.createQuery(Object[].class);
// select c.id, c.firstName, sale.amount
// from Clerk c
// LEFT JOIN c.sales sale
Root<Clerk> c = qdef.from(Clerk.class);
Join<Clerk, Sale> sale = c.join("sales", JoinType.LEFT);
qdef.select(cb.array(c.get("id"), c.get("firstName"), sale.get("amount")));
TypedQuery<Object[]> query = em.createQuery(qdef);
List<Object[]> results = query.getResultList();
assertTrue("no results", results.size() > 0);
for (Object[] result : results) {
assertEquals("unexpected result length", 3, result.length);
Long id = (Long) result[0];
String name = (String) result[1];
BigDecimal amount = (BigDecimal) result[2];
log.info("clerk.id=" + id + ", clerk.firstName=" + name + ", amount=" + amount);
}
}
public List<Service> findByContract(Contract contract) {
CriteriaBuilder cb = getEntityManager().getCriteriaBuilder();
CriteriaQuery<Service> cq = cb.createQuery(Service.class);
Root<Service> rt = cq.from(Service.class);
cq.select(rt).where(cb.equal(rt.get(Service_.contract), contract));
return getEntityManager().createQuery(cq).getResultList();
}
@Test
@Transactional
public void testCopy() {
EntityManager em = bookDao.getEntityManager();
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Book> criteria = cb.createQuery(Book.class);
// Fetch join
Root<Book> root = criteria.from(Book.class);
Path<String> path = root.join("author").<String>get("name");
root.fetch("author");
criteria.select(root);
// SubQuery
Subquery<String> sq = criteria.subquery(String.class);
Root<Author> author = sq.from(Author.class);
sq.select(author.<String>get("name"));
sq.where(cb.equal(author.<String>get("name"), "Rod"));
criteria.where(cb.in(path).value(sq));
CriteriaQuery<Book> copy = cb.createQuery(Book.class);
JpaUtils.copyCriteria(criteria, copy);
List<Book> copyBooks = em.createQuery(copy).getResultList();
List<Book> books = em.createQuery(criteria).getResultList();
assertEquals(books, copyBooks);
}
@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;
}
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;
}
@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();
}
@Override
public List<T> getList() {
CriteriaQuery<T> query = em.getCriteriaBuilder().createQuery(getEntityClass());
Root<T> root = query.from(getEntityClass());
query.select(root);
return em.createQuery(query).getResultList();
}
/** This test provides a demonstration of using a math formual within the where clause. */
@Test
public void testFormulas() {
log.info("*** testFormulas() ***");
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Number> qdef = cb.createQuery(Number.class);
// select count(s) from Sale s
// where (s.amount * :tax) > :amount"
Root<Sale> s = qdef.from(Sale.class);
qdef.select(cb.count(s))
.where(
cb.greaterThan(
cb.prod(s.<BigDecimal>get("amount"), cb.parameter(BigDecimal.class, "tax")),
new BigDecimal(10.0)));
TypedQuery<Number> query = em.createQuery(qdef);
// keep raising taxes until somebody pays $10.00 in tax
double tax = 0.05;
for (;
query.setParameter("tax", new BigDecimal(tax)).getSingleResult().intValue() == 0;
tax += 0.01) {
log.debug("tax=" + NumberFormat.getPercentInstance().format(tax));
}
log.info("raise taxes to: " + NumberFormat.getPercentInstance().format(tax));
assertEquals("unexpected level for tax:" + tax, 0.07, tax, .01);
}
@Override
public <T extends StoredObject> List<T> getStoredObjects(final Class<T> tClass) {
List<T> list = Collections.emptyList();
emLock.lock();
try {
final Future<List<T>> future =
executorService.submit(
() -> {
final CriteriaBuilder cb = em.getCriteriaBuilder();
final CriteriaQuery<T> cq = cb.createQuery(tClass);
final Root<T> root = cq.from(tClass);
cq.select(root);
final TypedQuery<T> q = em.createQuery(cq);
return new ArrayList<>(q.getResultList());
});
list = future.get();
} catch (InterruptedException | ExecutionException e) {
logger.log(Level.SEVERE, e.getLocalizedMessage(), e);
} finally {
emLock.unlock();
}
return stripMarkedForRemoval(list);
}
/** Load business rules */
private void loadList() {
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<BusinessRule> c = cb.createQuery(BusinessRule.class);
Root<BusinessRule> obj = c.from(BusinessRule.class);
c.select(obj).where(cb.equal(obj.get("project"), this.project)).orderBy(cb.asc(obj.get("id")));
this.businessRules = em.createQuery(c).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();
}
public int count() {
javax.persistence.criteria.CriteriaQuery<Long> cq =
getEntityManager().getCriteriaBuilder().createQuery(Long.class);
javax.persistence.criteria.Root<E> rt = cq.from(entityClass);
cq.select(getEntityManager().getCriteriaBuilder().count(rt));
return getEntityManager().createQuery(cq).getSingleResult().intValue();
}
@Override
public ReferenceNumberType getByCode(String code) {
ReferenceNumberType org = null;
try {
CriteriaBuilder builder = em.getCriteriaBuilder();
CriteriaQuery<ReferenceNumberType> query = builder.createQuery(ReferenceNumberType.class);
Root<ReferenceNumberType> rootEntity = query.from(ReferenceNumberType.class);
ParameterExpression<String> p = builder.parameter(String.class);
query.select(rootEntity).where(builder.equal(rootEntity.get("code"), p));
TypedQuery<ReferenceNumberType> typedQuery = em.createQuery(query);
typedQuery.setParameter(p, code);
org = typedQuery.getSingleResult();
// TypedQuery<ReferenceNumberType> q = em.createQuery("select o from
// com.conx.logistics.mdm.domain.referencenumber.ReferenceNumberType o WHERE o.code =
// :code",ReferenceNumberType.class);
// q.setParameter("code", code);
// org = q.getSingleResult();
} catch (NoResultException e) {
} catch (Exception e) {
e.printStackTrace();
} catch (Error e) {
StringWriter sw = new StringWriter();
e.printStackTrace(new PrintWriter(sw));
String stacktrace = sw.toString();
logger.error(stacktrace);
}
return org;
}
@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();
}
private int getNumberOfInstancesEnded(final ProcessIdentifier identifier) {
final CriteriaBuilder cb = em.getCriteriaBuilder();
final CriteriaQuery<Long> cq = cb.createQuery(Long.class);
final Root<MeasuredProcessInstance> processInstanceRoot =
cq.from(MeasuredProcessInstance.class);
cq.where(
cb.equal(
processInstanceRoot
.get(MeasuredProcessInstance_.identifier)
.get(ProcessInstanceIdentifier_.metricsId),
identifier.getMetricsId()),
cb.equal(
processInstanceRoot
.get(MeasuredProcessInstance_.identifier)
.get(ProcessInstanceIdentifier_.packageName),
identifier.getPackageName()),
cb.equal(
processInstanceRoot
.get(MeasuredProcessInstance_.identifier)
.get(ProcessInstanceIdentifier_.processId),
identifier.getProcessId()),
cb.isNotNull(processInstanceRoot.get(MeasuredProcessInstance_.endingTime)));
cq.select(cb.countDistinct(processInstanceRoot));
return em.createQuery(cq).getSingleResult().intValue();
}
/** load authUsers */
private void loadList() {
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<AuthUser> c = cb.createQuery(AuthUser.class);
Root<AuthUser> obj = c.from(AuthUser.class);
c.select(obj).orderBy(cb.asc(obj.get("name")));
this.authUsers = em.createQuery(c).getResultList();
}
/** This test provides a demonstration of using an explicit subquery */
@Test
public void testSubqueries() {
log.info("*** testSubqueries() ***");
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Customer> qdef = cb.createQuery(Customer.class);
// select c from Customer c
// where c.id IN
// (select s.buyerId from Sale s
// where s.amount > 100)
// form subquery
Subquery<Long> sqdef = qdef.subquery(Long.class);
Root<Sale> s = sqdef.from(Sale.class);
sqdef
.select(s.<Long>get("buyerId"))
.where(cb.greaterThan(s.<BigDecimal>get("amount"), new BigDecimal(100)));
// form outer query
Root<Customer> c = qdef.from(Customer.class);
qdef.select(c).where(cb.in(c.get("id")).value(sqdef));
int rows = executeQuery(qdef).size();
assertEquals("unexpected number of rows", 1, rows);
}
/**
* Count the objects in the database
*
* @return Number of objects
*/
public int count() {
CriteriaBuilder b = getBuilder();
CriteriaQuery<Long> criteria = b.createQuery(Long.class);
criteria.select(b.count(criteria.from(domainClass)));
return em.createQuery(criteria).getSingleResult().intValue();
}
@Override
public List<Point> getList(EntityManager em) {
CriteriaQuery cq = em.getCriteriaBuilder().createQuery();
cq.select(cq.from(Point.class));
Query q = em.createQuery(cq);
return q.getResultList();
}
public List<Artikel> findArtikelByIds(List<Long> ids) {
if (ids == null || ids.isEmpty()) {
return Collections.emptyList();
}
/*
* SELECT a FROM Artikel a WHERE a.id = ? OR a.id = ? OR ...
*/
final CriteriaBuilder builder = em.getCriteriaBuilder();
final CriteriaQuery<Artikel> criteriaQuery = builder.createQuery(Artikel.class);
final Root<Artikel> a = criteriaQuery.from(Artikel.class);
final Path<Long> idPath = a.get("id");
// final Path<String> idPath = a.get(Artikel_.id); // Metamodel-Klassen funktionieren nicht mit
// Eclipse
Predicate pred = null;
if (ids.size() == 1) {
// Genau 1 id: kein OR notwendig
pred = builder.equal(idPath, ids.get(0));
} else {
// Mind. 2x id, durch OR verknuepft
final Predicate[] equals = new Predicate[ids.size()];
int i = 0;
for (Long id : ids) {
equals[i++] = builder.equal(idPath, id);
}
pred = builder.or(equals);
}
criteriaQuery.where(pred);
return em.createQuery(criteriaQuery).getResultList();
}
public void testMetamodelOnClauseOverCollection() {
EntityManager em = createEntityManager();
Query query =
em.createQuery("Select e from Employee e join e.phoneNumbers p on p.areaCode = '613'");
List baseResult = query.getResultList();
CriteriaBuilder qb = em.getCriteriaBuilder();
CriteriaQuery<Employee> cq = qb.createQuery(Employee.class);
Metamodel metamodel = em.getMetamodel();
EntityType<Employee> entityEmp_ = metamodel.entity(Employee.class);
EntityType<PhoneNumber> entityPhone_ = metamodel.entity(PhoneNumber.class);
Root<Employee> root = cq.from(entityEmp_);
Join phoneNumber = root.join(entityEmp_.getCollection("phoneNumbers"));
phoneNumber.on(qb.equal(phoneNumber.get(entityPhone_.getSingularAttribute("areaCode")), "613"));
List testResult = em.createQuery(cq).getResultList();
clearCache();
closeEntityManager(em);
if (baseResult.size() != testResult.size()) {
fail(
"Criteria query using ON clause did not match JPQL results; "
+ baseResult.size()
+ " were expected, while criteria query returned "
+ testResult.size());
}
}
public void testMetamodelOnClauseWithLeftJoin() {
EntityManager em = createEntityManager();
Query query =
em.createQuery(
"Select e from Employee e left join e.address a on a.city = 'Ottawa' "
+ "where a.postalCode is not null");
List baseResult = query.getResultList();
Metamodel metamodel = em.getMetamodel();
EntityType<Employee> entityEmp_ = metamodel.entity(Employee.class);
EntityType<Address> entityAddr_ = metamodel.entity(Address.class);
CriteriaBuilder qb = em.getCriteriaBuilder();
CriteriaQuery<Employee> cq = qb.createQuery(Employee.class);
Root<Employee> root = cq.from(entityEmp_);
Join address = root.join(entityEmp_.getSingularAttribute("address"), JoinType.LEFT);
address.on(qb.equal(address.get(entityAddr_.getSingularAttribute("city")), "Ottawa"));
cq.where(qb.isNotNull(address.get(entityAddr_.getSingularAttribute("postalCode"))));
List testResult = em.createQuery(cq).getResultList();
clearCache();
closeEntityManager(em);
if (baseResult.size() != testResult.size()) {
fail(
"Criteria query using ON clause with a left join did not match JPQL results; "
+ baseResult.size()
+ " were expected, while criteria query returned "
+ testResult.size());
}
}
public int count(List<Filtro> filtros) {
javax.persistence.criteria.CriteriaQuery cq =
getEntityManager().getCriteriaBuilder().createQuery();
javax.persistence.criteria.Root<BaseEntity> rt = cq.from(entityClass);
CriteriaBuilder builder = getEntityManager().getCriteriaBuilder();
if (filtros != null) {
ArrayList<Predicate> predicatesList = new ArrayList<>();
for (Filtro filtro : filtros) {
switch (filtro.getTipoFiltro()) {
case Equals:
predicatesList.add(builder.equal(rt.get(filtro.getDescricao()), filtro.getValor()));
break;
case LessEqual:
Path<Date> date = rt.get(filtro.getDescricao());
predicatesList.add(builder.lessThanOrEqualTo(date, ((Date) filtro.getValor())));
break;
case GreaterEqual:
Path<Date> date2 = rt.get(filtro.getDescricao());
predicatesList.add(builder.greaterThanOrEqualTo(date2, ((Date) filtro.getValor())));
break;
default:
predicatesList.add(builder.equal(rt.get(filtro.getDescricao()), filtro.getValor()));
break;
}
}
cq.where(predicatesList.<Predicate>toArray(new Predicate[predicatesList.size()]));
}
cq.select(builder.count(rt));
javax.persistence.Query q = getEntityManager().createQuery(cq);
return ((Long) q.getSingleResult()).intValue();
}
public List<Equipment> findAllOrderedByProductionDate() {
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Equipment> criteria = cb.createQuery(Equipment.class);
Root<Equipment> equipment = criteria.from(Equipment.class);
criteria.select(equipment).orderBy(cb.desc(equipment.get("productionDate")));
return em.createQuery(criteria).getResultList();
}
/**
* Service method to return all StockCategory from database
*
* @return
*/
public List<StockCategory> findAllStockCategories() {
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<StockCategory> criteria = criteriaBuilder.createQuery(StockCategory.class);
Root<StockCategory> stockCategory = criteria.from(StockCategory.class);
criteria.select(stockCategory);
return entityManager.createQuery(criteria).getResultList();
}
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());
}
public int getVoteCount() {
CriteriaQuery cq = em.getCriteriaBuilder().createQuery();
Root<Vote> rt = cq.from(Vote.class);
cq.select(em.getCriteriaBuilder().count(rt));
Query q = em.createQuery(cq);
return ((Long) q.getSingleResult()).intValue();
}
public List<Gestion> findAll() {
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Gestion> criteria = cb.createQuery(Gestion.class);
Root<Gestion> gestion = criteria.from(Gestion.class);
criteria.select(gestion);
return em.createQuery(criteria).getResultList();
}