Beispiel #1
11
  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;
  }
Beispiel #2
1
 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;
  }
Beispiel #18
0
 @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();
 }
Beispiel #20
0
 @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();
 }
Beispiel #21
0
  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();
  }
Beispiel #22
0
  @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);
  }
Beispiel #23
0
  @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();
  }