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;
}
public int getFuncaoCount() {
EntityManager em = getEntityManager();
try {
CriteriaQuery cq = em.getCriteriaBuilder().createQuery();
Root<Funcao> rt = cq.from(Funcao.class);
cq.select(em.getCriteriaBuilder().count(rt));
Query q = em.createQuery(cq);
return ((Long) q.getSingleResult()).intValue();
} finally {
em.close();
}
}
/**
* 废弃
*
* @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 Set<String> getTagsSuggest(String url) {
String hashUrl = MD5Util.md5Hex(url);
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Tuple> query = cb.createTupleQuery();
Root<Bookmark> root = query.from(Bookmark.class);
// joins
SetJoin<Bookmark, String> tagsJoin = root.joinSet("tags");
query.select(cb.tuple(tagsJoin.alias("tag"), cb.count(tagsJoin).alias("count_tags")));
query.where(cb.equal(root.get("hashUrl"), hashUrl));
query.groupBy(tagsJoin);
query.orderBy(cb.desc(cb.count(tagsJoin)));
// top five tags
List<Tuple> result = entityManager.createQuery(query).setMaxResults(5).getResultList();
Set<String> topTags = new HashSet<String>();
for (Tuple t : result) {
topTags.add((String) t.get("tag"));
}
return topTags;
}
public static CriteriaQuery<Organization> buildSearchQuery(
String name,
String email,
String phone,
String type,
String region,
String district,
String locality,
String streetToSearch,
String sortCriteria,
String sortOrder,
EntityManager em) {
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Organization> criteriaQuery = cb.createQuery(Organization.class);
Root<Organization> root = criteriaQuery.from(Organization.class);
Predicate predicate =
ArchivalOrganizationsQueryConstructorAdmin.buildPredicate(
name, email, type, phone, region, district, locality, streetToSearch, root, cb);
if ((sortCriteria != null) && (sortOrder != null)) {
criteriaQuery.orderBy(
SortCriteriaOrganization.valueOf(sortCriteria.toUpperCase())
.getSortOrder(root, cb, sortOrder));
} else {
criteriaQuery.orderBy(cb.desc(root.get("id")));
}
criteriaQuery.select(root);
criteriaQuery.where(predicate);
return criteriaQuery;
}
@Test
public void testParameterInParameterList() {
// Yes, this test makes no semantic sense. But the JPA TCK does it...
// it causes a problem on Derby, which does not like the form "... where ? in (?,?)"
// Derby wants one side or the other to be CAST (I assume so it can check typing).
EntityManager em = getOrCreateEntityManager();
em.getTransaction().begin();
CriteriaQuery<MultiTypedBasicAttributesEntity> criteria =
em.getCriteriaBuilder().createQuery(MultiTypedBasicAttributesEntity.class);
criteria.from(MultiTypedBasicAttributesEntity.class);
criteria.where(
em.getCriteriaBuilder()
.in(em.getCriteriaBuilder().parameter(Long.class, "p1"))
.value(em.getCriteriaBuilder().parameter(Long.class, "p2"))
.value(em.getCriteriaBuilder().parameter(Long.class, "p3")));
TypedQuery<MultiTypedBasicAttributesEntity> query = em.createQuery(criteria);
query.setParameter("p1", 1L);
query.setParameter("p2", 2L);
query.setParameter("p3", 3L);
query.getResultList();
em.getTransaction().commit();
em.close();
}
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();
}
@Transactional
public Set<Region> getRegions() {
CriteriaBuilder b = em.getCriteriaBuilder();
CriteriaQuery<Region> criteria = em.getCriteriaBuilder().createQuery(Region.class);
Root<Region> root = criteria.from(Region.class);
criteria.select(root);
TypedQuery<Region> query = em.createQuery(criteria);
return new TreeSet<Region>(query.getResultList());
}
@Transactional
public Region getRegion(String region) {
CriteriaBuilder b = em.getCriteriaBuilder();
CriteriaQuery<Region> criteria = em.getCriteriaBuilder().createQuery(Region.class);
Root<Region> root = criteria.from(Region.class);
criteria.select(root);
criteria.where(b.equal(root.get("name"), region));
TypedQuery<Region> query = em.createQuery(criteria);
return query.getSingleResult();
}
@Transactional
public List<Userrr> getAll() {
CriteriaBuilder b = em.getCriteriaBuilder();
CriteriaQuery<Userrr> criteria = em.getCriteriaBuilder().createQuery(Userrr.class);
Root<Userrr> root = criteria.from(Userrr.class);
criteria.select(root);
criteria.where(b.notEqual(root.get("rolee"), "ADMIN"));
TypedQuery<Userrr> query = em.createQuery(criteria);
return query.getResultList();
}
@Transactional
public Userrr getUser(String username) {
CriteriaBuilder b = em.getCriteriaBuilder();
CriteriaQuery<Userrr> criteria = em.getCriteriaBuilder().createQuery(Userrr.class);
Root<Userrr> root = criteria.from(Userrr.class);
criteria.select(root);
criteria.where(b.equal(root.get("username"), username));
TypedQuery<Userrr> query = em.createQuery(criteria);
return query.getSingleResult();
}
public int getProgramaExigePapelPessoaCount() {
EntityManager em = getEntityManager();
try {
CriteriaQuery cq = em.getCriteriaBuilder().createQuery();
Root<ProgramaExigePapelPessoa> rt = cq.from(ProgramaExigePapelPessoa.class);
cq.select(em.getCriteriaBuilder().count(rt));
Query q = em.createQuery(cq);
return ((Long) q.getSingleResult()).intValue();
} finally {
em.close();
}
}
public int getSumAtcPresentacionfarmaceuticaCount() {
EntityManager em = getEntityManager();
try {
CriteriaQuery cq = em.getCriteriaBuilder().createQuery();
Root<SumAtcPresentacionfarmaceutica> rt = cq.from(SumAtcPresentacionfarmaceutica.class);
cq.select(em.getCriteriaBuilder().count(rt));
Query q = em.createQuery(cq);
return ((Long) q.getSingleResult()).intValue();
} finally {
em.close();
}
}
public int getTipoEstadoNormatividadCount() {
EntityManager em = getEntityManager();
try {
CriteriaQuery cq = em.getCriteriaBuilder().createQuery();
Root<TipoEstadoNormatividad> rt = cq.from(TipoEstadoNormatividad.class);
cq.select(em.getCriteriaBuilder().count(rt));
Query q = em.createQuery(cq);
return ((Long) q.getSingleResult()).intValue();
} finally {
em.close();
}
}
@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;
}
/**
* 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();
}
@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;
}
@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();
}
/** 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();
}
@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();
}
@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 Map<String, Long> countTagsByUser(String userName) {
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Tuple> query = cb.createTupleQuery();
Root<Bookmark> root = query.from(Bookmark.class);
// joins
SetJoin<Bookmark, String> tagsJoin = root.joinSet("tags");
Join<Bookmark, User> userJoin = root.join("user");
query.select(cb.tuple(tagsJoin.alias("tag"), cb.count(tagsJoin).alias("count_tags")));
query.where(cb.equal(userJoin.get("userName"), userName));
query.groupBy(tagsJoin);
query.orderBy(cb.desc(cb.count(tagsJoin)));
List<Tuple> result = entityManager.createQuery(query).setMaxResults(30).getResultList();
Map<String, Long> tagRanking = new TreeMap<String, Long>();
for (Tuple t : result) {
tagRanking.put((String) t.get("tag"), (Long) t.get("count_tags"));
}
return tagRanking;
}
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();
}
@Override
public CriteriaBuilder getCriteriaBuilder() {
EntityManager em = getCurrent();
if (em != null) {
return em.getCriteriaBuilder();
}
em = createEntityManager();
try {
return em.getCriteriaBuilder();
} finally {
freeEntityManager(em);
}
}
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());
}
}
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;
}
private Predicate[] getSearchPredicates(Root<Author> root) {
CriteriaBuilder builder = em.getCriteriaBuilder();
List<Predicate> predicatesList = new ArrayList<>();
String firstname = example.getFirstname();
if (firstname != null && !"".equals(firstname)) {
predicatesList.add(
builder.like(
builder.lower(root.<String>get("firstname")), '%' + firstname.toLowerCase() + '%'));
}
String surname = example.getSurname();
if (surname != null && !"".equals(surname)) {
predicatesList.add(
builder.like(
builder.lower(root.<String>get("surname")), '%' + surname.toLowerCase() + '%'));
}
String bio = example.getBio();
if (bio != null && !"".equals(bio)) {
predicatesList.add(
builder.like(builder.lower(root.<String>get("bio")), '%' + bio.toLowerCase() + '%'));
}
String twitter = example.getTwitter();
if (twitter != null && !"".equals(twitter)) {
predicatesList.add(
builder.like(
builder.lower(root.<String>get("twitter")), '%' + twitter.toLowerCase() + '%'));
}
return predicatesList.toArray(new Predicate[predicatesList.size()]);
}
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 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();
}