@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;
  }
  public List<Programacion> getProgramacionSeguimiento(
      boolean distinct, EstadoProgramacion estadoProgramacion) {
    System.out.println(
        "00---------------------------------------------------------------------------00");

    CriteriaQuery<Programacion> cq =
        getEntityManager().getCriteriaBuilder().createQuery(Programacion.class);

    Root<Programacion> registro = cq.from(Programacion.class);

    Join<Programacion, AtencionProgramacion> join =
        registro.join(Programacion_.atencionProgramacionList);

    if (distinct) cq.distinct(true);

    cq.where(
        getEntityManager()
            .getCriteriaBuilder()
            .and(
                getEntityManager()
                    .getCriteriaBuilder()
                    .equal(registro.get(Programacion_.flgActivo), BigInteger.ONE),
                getEntityManager()
                    .getCriteriaBuilder()
                    .equal(
                        join.get(AtencionProgramacion_.estadoProgramacion), estadoProgramacion)));

    javax.persistence.Query q = getEntityManager().createQuery(cq);

    return q.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());
    }
  }
  /** 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 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());
    }
  }
  /** This test provides a demonstration for using the ANY subquery result evaluation */
  @Test
  public void testAny() {
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Clerk> qdef = cb.createQuery(Clerk.class);
    Root<Clerk> c = qdef.from(Clerk.class);
    qdef.select(c);

    // select c from Clerk c
    // where 125 < ANY " +
    // (select s.amount from c.sales s)",
    Subquery<BigDecimal> sqdef = qdef.subquery(BigDecimal.class);
    Root<Clerk> c1 = sqdef.from(Clerk.class);
    Join<Clerk, Sale> s = c1.join("sales");
    sqdef.select(s.<BigDecimal>get("amount")).where(cb.equal(c, c1));

    Predicate p1 = cb.lessThan(cb.literal(new BigDecimal(125)), cb.any(sqdef));

    qdef.where(p1);
    List<Clerk> results1 = executeQuery(qdef);
    assertEquals("unexpected number of rows", 2, results1.size());

    // select c from Clerk c
    // where 125 > ANY
    // (select s.amount from c.sales s)
    Predicate p2 = cb.greaterThan(cb.literal(new BigDecimal(125)), cb.any(sqdef));

    qdef.where(p2);
    List<Clerk> results2 = executeQuery(qdef);
    assertEquals("unexpected number of rows", 1, results2.size());
  }
  @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;
  }
  @Override
  public Collection<ProductListItem> findByParams(Map<String, Object> params) {

    CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();

    CriteriaQuery<ProductListItem> criteriaQuery =
        criteriaBuilder.createQuery(ProductListItem.class);

    Root<Product> enityRoot = criteriaQuery.from(Product.class);

    Predicate criteria = buildCriteria(criteriaBuilder, enityRoot, params);

    Join<Object, Object> categoryJoin = enityRoot.join("category", JoinType.LEFT);

    criteriaQuery
        .multiselect(
            enityRoot.<Long>get("id"),
            enityRoot.<String>get("name"),
            categoryJoin.<String>get("name"),
            enityRoot.<String>get("producer"),
            enityRoot.<BigDecimal>get("price"),
            enityRoot.<Calendar>get("createDate"))
        .where(criteria);

    String orderDir = (String) params.get("order_dir");
    if (orderDir == null) {
      orderDir = "desc";
    }
    String orderCol = (String) params.get("order_col");
    if (orderCol == null) {
      orderCol = "id";
    }

    Path<Object> col = null;
    if (orderCol.equals("category_name")) {
      col = categoryJoin.get("name");
    } else {
      col = enityRoot.get(orderCol);
    }

    if (orderDir.equals("asc")) {
      criteriaQuery.orderBy(criteriaBuilder.asc(col));
    } else if (orderDir.equals("desc")) {
      criteriaQuery.orderBy(criteriaBuilder.desc(col));
    }

    TypedQuery<ProductListItem> query = entityManager.createQuery(criteriaQuery);

    Integer pageSize = (Integer) params.get("page_size");
    Integer pageIndex = (Integer) params.get("page_index");

    if (pageSize != null && pageIndex != null) {
      query.setMaxResults(pageSize);
      query.setFirstResult(pageIndex * pageSize);
    }

    return query.getResultList();
  }
Exemple #9
0
  /**
   * Returns an existing join for the given attribute if one already exists or creates a new one if
   * not.
   *
   * @param from the {@link From} to get the current joins from.
   * @param attribute the {@link Attribute} to look for in the current joins.
   * @return will never be {@literal null}.
   */
  private static Join<?, ?> getOrCreateJoin(From<?, ?> from, String attribute) {

    for (Join<?, ?> join : from.getJoins()) {

      boolean sameName = join.getAttribute().getName().equals(attribute);

      if (sameName && join.getJoinType().equals(JoinType.LEFT)) {
        return join;
      }
    }

    return from.join(attribute, JoinType.LEFT);
  }
Exemple #10
0
  public List<Agent> getAllAgentProfilCreateur() {
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Agent> cq = cb.createQuery(Agent.class);
    Root<Agent> agentRoot = cq.from(Agent.class);
    Join<Agent, Utilisateur> utilisateurJoin = agentRoot.join("utilisateur");
    Join<Profil, Agent> profilJoin = utilisateurJoin.join("profil");
    Predicate pAll = cb.equal(profilJoin.get("droitCreationFiche"), true);
    pAll = cb.and(pAll, cb.equal(utilisateurJoin.get("actif"), true));

    cq.where(pAll);
    cq.orderBy(cb.asc(agentRoot.get("nom")));
    TypedQuery<Agent> agentsQuery = em.createQuery(cq.distinct(true));
    return agentsQuery.getResultList();
  }
Exemple #11
0
  @Test
  @Transactional
  public void testCountCriteria() {
    EntityManager em = bookDao.getEntityManager();
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Book> criteria = cb.createQuery(Book.class);

    Root<Book> root = criteria.from(Book.class);
    Join<Book, Author> join = root.join("author");
    criteria.where(join.isNotNull());

    CriteriaQuery<Long> countCriteria = JpaUtils.countCriteria(em, criteria);
    Long result = em.createQuery(countCriteria).getSingleResult();
    log.debug("Count: " + result);
  }
  /** This test provides an example collection path using an INNER JOIN */
  @Test
  public void testCollectionPathExpressionsInnerJoin() {
    log.info("*** testCollectionPathExpressionsInnerJoin ***");

    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Date> qdef = cb.createQuery(Date.class);

    // select sale.date from Clerk c JOIN c.sales sale
    Root<Clerk> c = qdef.from(Clerk.class);
    Join<Clerk, Sale> sale = c.join("sales", JoinType.INNER);
    qdef.select(sale.<Date>get("date"));

    int rows = executeQuery(qdef).size();
    assertTrue("unexpected number of sales:" + rows, rows > 0);
  }
  /**
   * Method findByUniqueKey.
   *
   * @param idTradingday Integer
   * @param idContract Integer
   * @param startPeriod Date
   * @param endPeriod Date
   * @return Candle
   */
  public Candle findByUniqueKey(
      Integer idTradingday, Integer idContract, Date startPeriod, Date endPeriod, Integer barSize) {

    try {
      EntityManager entityManager = EntityManagerHelper.getEntityManager();
      entityManager.getTransaction().begin();
      CriteriaBuilder builder = entityManager.getCriteriaBuilder();
      CriteriaQuery<Candle> query = builder.createQuery(Candle.class);
      Root<Candle> from = query.from(Candle.class);
      query.select(from);
      List<Predicate> predicates = new ArrayList<Predicate>();

      if (null != idTradingday) {
        Join<Candle, Tradingday> tradingday = from.join("tradingday");
        Predicate predicate = builder.equal(tradingday.get("idTradingDay"), idTradingday);
        predicates.add(predicate);
      }
      if (null != idContract) {
        Join<Candle, Contract> contract = from.join("contract");
        Predicate predicate = builder.equal(contract.get("idContract"), idContract);
        predicates.add(predicate);
      }
      if (null != startPeriod) {
        Predicate predicate = builder.equal(from.get("startPeriod"), startPeriod);
        predicates.add(predicate);
      }
      if (null != endPeriod) {
        Predicate predicate = builder.equal(from.get("endPeriod"), endPeriod);
        predicates.add(predicate);
      }
      if (null != barSize) {
        Predicate predicate = builder.equal(from.get("barSize"), barSize);
        predicates.add(predicate);
      }
      query.where(predicates.toArray(new Predicate[] {}));
      TypedQuery<Candle> typedQuery = entityManager.createQuery(query);
      List<Candle> items = typedQuery.getResultList();
      entityManager.getTransaction().commit();
      if (items.size() > 0) {
        return items.get(0);
      }
      return null;
    } catch (RuntimeException re) {
      throw re;
    } finally {
      EntityManagerHelper.close();
    }
  }
  public List<Employee> getEmployeesUsingStringBasedQuery() {

    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Employee> c = cb.createQuery(Employee.class);
    Root<Employee> emp = c.from(Employee.class);
    Subquery<Integer> sq = c.subquery(Integer.class);
    Root<Department> dept = sq.from(Department.class);
    Join<Employee, Project> project = dept.join("employees").join("projects");
    sq.select(dept.<Integer>get("id"))
        .distinct(true)
        .where(cb.like(project.<String>get("name"), "QA%"));
    c.select(emp).where(cb.in(emp.get("dept").get("id")).value(sq));

    TypedQuery<Employee> q = em.createQuery(c);
    return q.getResultList();
  }
  @SuppressWarnings({"unchecked"})
  private void renderJoins(
      StringBuilder jpaqlQuery, RenderingContext renderingContext, Collection<Join<?, ?>> joins) {
    if (joins == null) {
      return;
    }

    for (Join join : joins) {
      ((FromImplementor) join).prepareAlias(renderingContext);
      jpaqlQuery
          .append(renderJoinType(join.getJoinType()))
          .append(((FromImplementor) join).renderTableExpression(renderingContext));
      renderJoins(jpaqlQuery, renderingContext, join.getJoins());
      renderFetches(jpaqlQuery, renderingContext, join.getFetches());
    }
  }
  public List<Employee> getEmployeesUsingCanonicalMetamodelQuery() {

    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Employee> c = cb.createQuery(Employee.class);
    Root<Employee> emp = c.from(Employee.class);
    Subquery<Integer> sq = c.subquery(Integer.class);
    Root<Department> dept = sq.from(Department.class);
    Join<Employee, Project> project = dept.join(Department_.employees).join(Employee_.projects);
    sq.select(dept.get(Department_.id))
        .distinct(true)
        .where(cb.like(project.get(Project_.name), "QA%"));
    c.select(emp).where(cb.in(emp.get(Employee_.dept).get(Department_.id)).value(sq));

    TypedQuery<Employee> q = em.createQuery(c);
    return q.getResultList();
  }
  @SuppressWarnings("unchecked")
  public Collection<Transacao> listarTransacoesPorConta(Long identificador) {

    CriteriaBuilder criteria = this.manager.getCriteriaBuilder();
    CriteriaQuery<Transacao> cq = criteria.createQuery(Transacao.class);
    Root<Transacao> from = cq.from(Transacao.class);

    Join<Transacao, Conta> contaDebito = from.join("contaDebito", JoinType.LEFT);
    Join<Transacao, Conta> contaCredito = from.join("contaCredito", JoinType.LEFT);

    cq.where(
        criteria.or(
            criteria.equal(contaDebito.get("identificador"), identificador),
            criteria.equal(contaCredito.get("identificador"), identificador)));
    return manager.createQuery(cq).getResultList();
  }
 @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();
 }
  /**
   * Method findCandlesByContractDateRangeBarSize.
   *
   * @param idContract Integer
   * @param startDate Date
   * @param endDate Date
   * @param barSize Integer
   * @return List<Candle>
   */
  public List<Candle> findCandlesByContractDateRangeBarSize(
      Integer idContract, Date startOpenDate, Date endOpenDate, Integer barSize) {

    try {
      EntityManager entityManager = EntityManagerHelper.getEntityManager();
      entityManager.getTransaction().begin();
      CriteriaBuilder builder = entityManager.getCriteriaBuilder();
      CriteriaQuery<Candle> query = builder.createQuery(Candle.class);
      Root<Candle> from = query.from(Candle.class);
      query.select(from);
      query.orderBy(builder.asc(from.get("startPeriod")));
      List<Predicate> predicates = new ArrayList<Predicate>();
      if (null != idContract) {
        Join<Candle, Contract> contract = from.join("contract");
        Predicate predicateContract = builder.equal(contract.get("idContract"), idContract);
        predicates.add(predicateContract);
      }
      if (null != startOpenDate) {
        Join<Candle, Tradingday> tradingdayOpenDate = from.join("tradingday");
        Predicate predicateStartDate =
            builder.greaterThanOrEqualTo(
                tradingdayOpenDate.get("open").as(Date.class), startOpenDate);
        predicates.add(predicateStartDate);
        Predicate predicateEndDate =
            builder.lessThanOrEqualTo(tradingdayOpenDate.get("open").as(Date.class), endOpenDate);
        predicates.add(predicateEndDate);
      }

      if (null != barSize) {
        Predicate predicate = builder.equal(from.get("barSize"), barSize);
        predicates.add(predicate);
      }

      query.where(predicates.toArray(new Predicate[] {}));
      TypedQuery<Candle> typedQuery = entityManager.createQuery(query);
      List<Candle> items = typedQuery.getResultList();
      entityManager.getTransaction().commit();
      return items;

    } catch (RuntimeException re) {
      EntityManagerHelper.rollback();
      throw re;
    } finally {
      EntityManagerHelper.close();
    }
  }
 @SuppressWarnings("unchecked")
 private <X, T, V extends T, K extends JoinImplementor> K treat(
     Join<X, T> join, Class<V> type, BiFunction<Join<X, T>, Class<V>, K> f) {
   final Set<Join<X, ?>> joins = join.getParent().getJoins();
   final K treatAs = f.apply(join, type);
   joins.add(treatAs);
   return treatAs;
 }
  @SuppressWarnings("unchecked")
  public static Predicate[] deviceListPredicates(
      CriteriaBuilder cb,
      Root<Device> from,
      Optional<String> name,
      Optional<String> namePattern,
      Optional<String> status,
      Optional<Long> networkId,
      Optional<String> networkName,
      Optional<Long> deviceClassId,
      Optional<String> deviceClassName,
      Optional<String> deviceClassVersion,
      Optional<HivePrincipal> principal) {
    final List<Predicate> predicates = new LinkedList<>();

    name.ifPresent(n -> predicates.add(cb.equal(from.<String>get("name"), n)));
    namePattern.ifPresent(np -> predicates.add(cb.like(from.<String>get("name"), np)));
    status.ifPresent(s -> predicates.add(cb.equal(from.<String>get("status"), s)));

    final Join<Device, Network> networkJoin = (Join) from.fetch("network", JoinType.LEFT);
    networkId.ifPresent(nId -> predicates.add(cb.equal(networkJoin.<Long>get("id"), nId)));
    networkName.ifPresent(
        nName -> predicates.add(cb.equal(networkJoin.<String>get("name"), nName)));

    final Join<Device, DeviceClass> dcJoin = (Join) from.fetch("deviceClass", JoinType.LEFT);
    deviceClassId.ifPresent(dcId -> predicates.add(cb.equal(dcJoin.<Long>get("id"), dcId)));
    deviceClassName.ifPresent(
        dcName -> predicates.add(cb.equal(dcJoin.<String>get("name"), dcName)));
    deviceClassVersion.ifPresent(
        dcVersion -> predicates.add(cb.equal(dcJoin.<String>get("version"), dcVersion)));

    predicates.addAll(deviceSpecificPrincipalPredicates(cb, from, principal));

    return predicates.toArray(new Predicate[predicates.size()]);
  }
  /**
   * Method findByContractAndDateRange.
   *
   * @param idContract Integer
   * @param startPeriod Date
   * @param endPeriod Date
   * @return List<Candle>
   */
  public List<Candle> findByContractAndDateRange(
      Integer idContract, Date startPeriod, Date endPeriod, Integer barSize) {

    try {
      EntityManager entityManager = EntityManagerHelper.getEntityManager();
      entityManager.getTransaction().begin();
      CriteriaBuilder builder = entityManager.getCriteriaBuilder();
      CriteriaQuery<Candle> query = builder.createQuery(Candle.class);
      Root<Candle> from = query.from(Candle.class);
      query.select(from);
      List<Predicate> predicates = new ArrayList<Predicate>();

      if (null != idContract) {
        Join<Candle, Contract> contract = from.join("contract");
        Predicate predicate = builder.equal(contract.get("idContract"), idContract);
        predicates.add(predicate);
      }
      if (null != startPeriod) {
        Expression<Date> start = from.get("startPeriod");
        Predicate predicate = builder.greaterThanOrEqualTo(start, startPeriod);
        predicates.add(predicate);
      }
      if (null != endPeriod) {
        Expression<Date> end = from.get("endPeriod");
        Predicate predicate = builder.lessThanOrEqualTo(end, endPeriod);
        predicates.add(predicate);
      }
      if (null != barSize) {
        Predicate predicate = builder.equal(from.get("barSize"), barSize);
        predicates.add(predicate);
      }
      query.where(predicates.toArray(new Predicate[] {}));
      TypedQuery<Candle> typedQuery = entityManager.createQuery(query);
      List<Candle> items = typedQuery.getResultList();
      entityManager.getTransaction().commit();
      return items;

    } catch (RuntimeException re) {
      EntityManagerHelper.rollback();
      throw re;
    } finally {
      EntityManagerHelper.close();
    }
  }
Exemple #23
0
  public List<Historico> buscaTodosHistoricos(
      String keyArduino, int initResult, int maxResult, String atributo, int ordem) {

    CriteriaBuilder builder = entityManager.getCriteriaBuilder(); // builder
    CriteriaQuery<Historico> query = builder.createQuery(Historico.class); // retorno da query
    Root<Historico> historico = query.from(Historico.class); // classe base onde será feito o select

    /** Alias */
    Join<Historico, Dispositivo> dispo = historico.join("dispositivo");
    Join<Dispositivo, Casa> casa = dispo.join("casa");
    Join<Casa, Arduino> ard = casa.join("arduino");

    Order order = null;
    if (ordem > 0) {
      order = builder.asc(historico.get(atributo)); // ordem crescente
    } else {
      order = builder.desc(historico.get(atributo)); // ordem decrescente
    }

    TypedQuery<Historico> typedQuery =
        entityManager.createQuery(
            query
                .select(historico)
                .where(builder.equal(ard.get("key"), keyArduino))
                .orderBy(order));

    typedQuery.setFirstResult(initResult);
    typedQuery.setMaxResults(maxResult);

    return typedQuery.getResultList();
  }
  /**
   * Method findCandleCount.
   *
   * @param idTradingday Integer
   * @param idContract Integer
   * @return Long
   */
  public Long findCandleCount(Integer idTradingday, Integer idContract) {

    try {
      EntityManager entityManager = EntityManagerHelper.getEntityManager();
      entityManager.getTransaction().begin();
      CriteriaBuilder builder = entityManager.getCriteriaBuilder();
      CriteriaQuery<Object> query = builder.createQuery();
      Root<Candle> from = query.from(Candle.class);
      Expression<Long> id = from.get("idCandle");
      Expression<Long> minExpression = builder.count(id);

      List<Predicate> predicates = new ArrayList<Predicate>();

      if (null != idTradingday) {
        Join<Candle, Tradingday> tradingday = from.join("tradingday");
        Predicate predicate = builder.equal(tradingday.get("idTradingDay"), idTradingday);
        predicates.add(predicate);
      }
      if (null != idContract) {
        Join<Candle, Contract> contract = from.join("contract");
        Predicate predicate = builder.equal(contract.get("idContract"), idContract);
        predicates.add(predicate);
      }
      query.where(predicates.toArray(new Predicate[] {}));
      CriteriaQuery<Object> select = query.select(minExpression);
      TypedQuery<Object> typedQuery = entityManager.createQuery(select);
      Object item = typedQuery.getSingleResult();
      entityManager.getTransaction().commit();
      if (null == item) item = new Long(0);

      return (Long) item;

    } catch (RuntimeException re) {
      EntityManagerHelper.rollback();
      throw re;
    } finally {
      EntityManagerHelper.close();
    }
  }
  public static Predicate[] accessKeyListPredicates(
      CriteriaBuilder cb,
      Root<AccessKey> from,
      Long userId,
      Optional<String> labelOpt,
      Optional<String> labelPatten,
      Optional<Integer> typeOpt) {
    List<Predicate> predicates = new LinkedList<>();

    Join user = (Join) from.fetch("user", JoinType.LEFT);
    predicates.add(cb.equal(user.get("id"), userId));

    if (labelPatten.isPresent()) {
      labelPatten.ifPresent(pattern -> predicates.add(cb.like(from.get("label"), pattern)));
    } else {
      labelOpt.ifPresent(label -> predicates.add(cb.equal(from.get("label"), label)));
    }

    typeOpt.ifPresent(type -> predicates.add(cb.equal(from.get("type"), type)));

    return predicates.toArray(new Predicate[predicates.size()]);
  }
Exemple #26
0
  public void consultarPessoaComCriteria(
      String pCPF, String pEndereco, String pNome, String pDtNascimento) {
    EntityManager manager = UtilJPA.getEntityManager();

    CriteriaBuilder builderCriteria = manager.getCriteriaBuilder();
    CriteriaQuery<Pessoa> queryCriteria = builderCriteria.createQuery(Pessoa.class);
    Root<Pessoa> pessoa = queryCriteria.from(Pessoa.class);

    List<Predicate> condicoesWhere = new ArrayList<>();

    if (BibliotecaFuncoes.isStringValida(pCPF)) {
      Path<String> atributoCPF = pessoa.get("cpf");
      queryCriteria.where(builderCriteria.equal(atributoCPF, pCPF));
    }

    if (BibliotecaFuncoes.isStringValida(pEndereco)) {
      Path<String> atributoEndereco = pessoa.get("endereco");
      queryCriteria.where(builderCriteria.equal(atributoEndereco, pEndereco));
    }

    if (BibliotecaFuncoes.isStringValida(pNome)) {
      Join<Pessoa, DadosPessoais> dadosPessoa = pessoa.join("dadosPessoais");
      queryCriteria.where(
          builderCriteria.and(builderCriteria.equal(dadosPessoa.get("nome"), pNome)));
    }

    Predicate[] arrayCondicionais = condicoesWhere.toArray(new Predicate[condicoesWhere.size()]);

    Predicate todosOsCondicionais = builderCriteria.and(arrayCondicionais);

    queryCriteria.where(todosOsCondicionais);

    TypedQuery<Pessoa> queryPessoa = manager.createQuery(queryCriteria);

    Collection<Pessoa> pessoas = queryPessoa.getResultList();

    manager.close();
  }
  @SuppressWarnings("unchecked")
  protected SetJoin<UserEntity, ExtensionFieldValueEntity> createOrGetJoin(
      String alias,
      Root<UserEntity> root,
      SetAttribute<UserEntity, ExtensionFieldValueEntity> attribute) {

    for (Join<UserEntity, ?> currentJoin : root.getJoins()) {
      if (currentJoin.getAlias() == null) {
        // if alias is null, it is not an alias for an extension join, so we ignore it
        continue;
      }

      if (currentJoin.getAlias().equals(alias)) {
        return (SetJoin<UserEntity, ExtensionFieldValueEntity>) currentJoin;
      }
    }

    final SetJoin<UserEntity, ExtensionFieldValueEntity> join = root.join(attribute, JoinType.LEFT);

    join.alias(alias);

    return join;
  }
  @SuppressWarnings("unchecked")
  private static List<Predicate> deviceSpecificPrincipalPredicates(
      CriteriaBuilder cb, Root<Device> from, Optional<HivePrincipal> principal) {
    final List<Predicate> predicates = new LinkedList<>();
    final Join<Device, Network> networkJoin = (Join) from.fetch("network", JoinType.LEFT);
    final Join<Device, Network> usersJoin = (Join) networkJoin.fetch("users", JoinType.LEFT);
    from.fetch("deviceClass", JoinType.LEFT); // need this fetch to populate deviceClass
    principal.ifPresent(
        p -> {
          User user = p.getUser();
          if (user == null && p.getKey() != null) {
            user = p.getKey().getUser();
          }
          if (user != null && !user.isAdmin()) {
            predicates.add(cb.equal(usersJoin.<Long>get("id"), user.getId()));
          }

          if (p.getDevice() != null) {
            predicates.add(cb.equal(from.<Long>get("id"), p.getDevice().getId()));
          }

          if (p.getKey() != null) {
            for (AccessKeyBasedFilterForDevices extraFilter :
                AccessKeyBasedFilterForDevices.createExtraFilters(p.getKey().getPermissions())) {
              if (extraFilter.getDeviceGuids() != null) {
                predicates.add(from.<String>get("guid").in(extraFilter.getDeviceGuids()));
              }
              if (extraFilter.getNetworkIds() != null) {
                predicates.add(networkJoin.<Long>get("id").in(extraFilter.getNetworkIds()));
              }
            }
          }
        });

    return predicates;
  }
Exemple #29
0
 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();
 }
Exemple #30
0
  public static void main(String[] args) {

    conex = new Conexao();

    CriteriaBuilder builder = conex.getEm().getCriteriaBuilder();

    // CONSULTA 1
    // Todos os cursos com ch > 3600

    CriteriaQuery<Curso> query = builder.createQuery(Curso.class);
    Root<Curso> curso = query.from(Curso.class);
    Predicate pred = builder.gt(curso.get("cargaHoraria"), 3600);

    query.select(curso);

    query.where(pred);
    TypedQuery<Curso> tpQuery = conex.getEm().createQuery(query);
    List<Curso> CursoList = tpQuery.getResultList();

    System.out.println();
    System.out.println("***************************************");
    System.out.println("Todos os cursos com carga horaria maior que 3600:");
    System.out.println();

    for (Curso c : CursoList) {
      System.out.println(c.getNomeCurso() + " = Carga horaria: " + c.getCargaHoraria());
    }
    System.out.println("---------------------------------------");

    // CONSULTA 2
    // Todos as turmas do periodo da tarde e noite

    CriteriaQuery<Turma> criteriaQuery2 = builder.createQuery(Turma.class);
    Root<Turma> turma = criteriaQuery2.from(Turma.class);
    Predicate pred1 = builder.equal(turma.get("periodo"), "Noturno");
    Predicate pred2 = builder.equal(turma.get("periodo"), "Vespertino");

    Predicate p1orP2 = builder.or(pred1, pred2);

    criteriaQuery2.select(turma);

    criteriaQuery2.where(p1orP2);
    TypedQuery<Turma> query2 = conex.getEm().createQuery(criteriaQuery2);
    List<Turma> turmasConsulta2 = query2.getResultList();

    System.out.println();
    System.out.println("***************************************");
    System.out.println("Todos as turmas do periodo vespertino e noturno:");
    System.out.println();

    for (Turma turm : turmasConsulta2) {
      System.out.println(
          "Turma: "
              + turm.getIdTurma()
              + " - data de inicio: "
              + turm.getDataInicio()
              + " - data de termino: "
              + turm.getDataTermino());
    }

    System.out.println("---------------------------------------");

    // CONSULTA 3
    // todas as turmas com situação = "Aberta" e	curso = "Ciência da Computação“;

    CriteriaQuery<Turma> criteriaQuery3 = builder.createQuery(Turma.class);
    Root<Turma> turma2 = criteriaQuery3.from(Turma.class);
    Join<Turma, Curso> cursoJoin = turma2.join("curso");

    Predicate pred3 = builder.equal(turma2.get("situacao"), true);
    Predicate pred4 = builder.equal(cursoJoin.get("nomeCurso"), "Ciencias da Computacao");
    Predicate p3andP4 = builder.and(pred3, pred4);
    criteriaQuery3.select(turma2);
    criteriaQuery3.where(p3andP4);

    TypedQuery<Turma> query3 = conex.getEm().createQuery(criteriaQuery3);
    List<Turma> turmasConsulta3 = query3.getResultList();

    System.out.println();
    System.out.println("***************************************");
    System.out.println("Todos as turmas abertas do curso Ciencias da Computacao:");
    System.out.println();
    for (Turma turm : turmasConsulta3) {
      System.out.println(
          "Turma: "
              + turm.getIdTurma()
              + ", Curso: "
              + turm.getCurso().getNomeCurso()
              + " - data de inicio: "
              + turm.getDataInicio()
              + " - data de termino: "
              + turm.getDataTermino());
    }
    System.out.println("---------------------------------------");

    // CONSULTA 4
    // todos os professores que lecionam no curso de "Bacharelado em Tecnologia da Informação“;
    CriteriaQuery<Professor> c4 = builder.createQuery(Professor.class);

    Root<Professor> prof = c4.from(Professor.class);

    Join<Professor, Turma> profTurma = prof.join("turmas");
    Join<Turma, Curso> turmaCurso = profTurma.join("curso");

    Predicate p1 =
        builder.equal(turmaCurso.get("nomeCurso"), "Bacharelado em Tecnologia da Informacao");

    c4.select(prof);
    c4.where(p1);

    TypedQuery<Professor> q4 = conex.getEm().createQuery(c4);
    List<Professor> profsC4 = q4.getResultList();

    System.out.println();
    System.out.println("***************************************");
    System.out.println("Todos os professores do Bacharelado em Tecnologia da Informação");
    System.out.println();
    for (Professor prof4 : profsC4) {
      System.out.println("Professor: " + prof4.getNome() + ", matricula: " + prof4.getMatricula());
    }
    System.out.println("---------------------------------------");

    // CONSULTA 5
    // todos os  professores de turmas com periodo = Tarde e com cursos de carga horária > 4000
    CriteriaQuery<Professor> c5 = builder.createQuery(Professor.class);

    Root<Professor> prof5 = c5.from(Professor.class);

    Join<Professor, Turma> profTurma5 = prof5.join("turmas");
    Join<Turma, Curso> turmaCurso5 = profTurma5.join("curso");

    Predicate p1c5 = builder.equal(profTurma5.get("periodo"), "Vespertino");
    Predicate p2c5 = builder.gt(turmaCurso5.get("cargaHoraria"), 4000);

    Predicate pgeral = builder.and(p1c5, p2c5);

    c5.select(prof5);
    c5.where(pgeral);
    c5.distinct(true);

    TypedQuery<Professor> q5 = conex.getEm().createQuery(c5);
    List<Professor> profsC5 = q5.getResultList();

    System.out.println();
    System.out.println("***************************************");
    System.out.println(
        "Todos os professores de turmas com periodo = Tarde e com cursos de carga horária > 4000");
    System.out.println();
    for (Professor p5 : profsC5) {
      System.out.println("Professor: " + p5.getNome() + ", matricula: " + p5.getMatricula());
    }
    System.out.println("---------------------------------------");

    /*
    http://www.objectdb.com/java/jpa/query/jpql/logical
    */
  }