/**
  * 废弃
  *
  * @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;
 }
  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;
  }
  /** 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);
  }
  public List<MyClass138> findByCriteria(MyClass138Criteria myClass138Criteria) {
    javax.persistence.criteria.CriteriaBuilder cb = this.entityManager.getCriteriaBuilder();
    javax.persistence.criteria.CriteriaQuery<MyClass138> c = cb.createQuery(MyClass138.class);
    javax.persistence.criteria.Root<MyClass138> emp = c.from(MyClass138.class);
    c.select(emp);

    List<javax.persistence.criteria.Predicate> criteria =
        new java.util.ArrayList<javax.persistence.criteria.Predicate>();

    if (myClass138Criteria.getId() != null) {
      javax.persistence.criteria.Expression<Long> p = emp.get("id");
      javax.persistence.criteria.Expression<Long> val = cb.parameter(Long.class, "id");
      criteria.add(cb.equal(p, val));
    }

    if (criteria.size() == 1) {
      c.where(criteria.get(0));
    } else {
      c.where(cb.and(criteria.toArray(new javax.persistence.criteria.Predicate[0])));
    }
    javax.persistence.TypedQuery<MyClass138> q = this.entityManager.createQuery(c);

    if (myClass138Criteria.getId() != null) {
      q.setParameter("id", myClass138Criteria.getId());
    }

    return new java.util.ArrayList<MyClass138>(q.getResultList());
  }
  @Override
  public 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;
  }
  public List<Movimentacao> pesquisarPorPeriodo(Calendar dataDe, Calendar dataAte) {
    if (naoExiste(dataDe)) {
      // Primeiro verificamos os campos que podemos usar
      return new ArrayList<Movimentacao>();
    }
    if (naoExiste(dataAte)) {
      // Primeiro verificamos os campos que podemos usar
      return new ArrayList<Movimentacao>();
    }

    CriteriaBuilder cb = getEm().getCriteriaBuilder();
    CriteriaQuery<Movimentacao> c = cb.createQuery(Movimentacao.class);
    Root<Movimentacao> tabelaDeMovimentacao = c.from(Movimentacao.class);
    c.select(tabelaDeMovimentacao);
    List<Predicate> criteria = new ArrayList<Predicate>();

    if (existe(dataDe)) {
      ParameterExpression<String> p = cb.parameter(String.class, "data");
      criteria.add(cb.greaterThan(tabelaDeMovimentacao.<String>get("data"), p));
    }

    if (existe(dataAte)) {
      ParameterExpression<String> p = cb.parameter(String.class, "data");
      criteria.add(cb.lessThan(tabelaDeMovimentacao.<String>get("data"), p));
    }

    if (criteria.size() == 0) {
      throw new RuntimeException("no criteria");
    } else if (criteria.size() == 1) {
      c.where(criteria.get(0));
    } else {
      c.where(cb.and(criteria.toArray(new Predicate[0])));
    }

    TypedQuery<Movimentacao> q = getEm().createQuery(c);

    if (existe(dataDe)) {
      q.setParameter("data", dataDe);
    }

    if (existe(dataAte)) {
      q.setParameter("data", dataAte);
    }

    return q.getResultList();
  }
Exemple #7
0
  protected final <T> ParameterExpression<T> createParameterExpression(
      Class<T> paramClass, String name) {
    final EntityManager entityManager = this.getEntityManager();
    final EntityManagerFactory entityManagerFactory = entityManager.getEntityManagerFactory();
    final CriteriaBuilder criteriaBuilder = entityManagerFactory.getCriteriaBuilder();

    return criteriaBuilder.parameter(paramClass, name);
  }
Exemple #8
0
 public Printable getObject(Map<String, Object> params) throws SQLException {
   String name = (String) params.get(Constants.GROUPNAME);
   try {
     CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
     CriteriaQuery<? extends Printable> criteriaQuery = criteriaBuilder.createQuery(Group.class);
     Root<? extends Printable> root = criteriaQuery.from(Group.class);
     ParameterExpression<String> parameters = criteriaBuilder.parameter(String.class);
     criteriaQuery.where(criteriaBuilder.equal(root.get("name"), parameters));
     TypedQuery<? extends Printable> query = em.createQuery(criteriaQuery);
     query.setParameter(parameters, name);
     return query.getSingleResult();
   } catch (Exception e) {
     throw new SQLException("Unable to execute getObject.", e);
   }
 }
  public List<Movimentacao> find(Movimentacao paramMovimentacao) {
    Long codigo = null;
    String descricao = null;
    TipoMovimento tipo = null;
    BigDecimal valor = null;
    if (existe(paramMovimentacao)) {
      // Primeiro verificamos os campos que podemos usar
      codigo = paramMovimentacao.getCodigo();
      descricao = paramMovimentacao.getDescricao();
      tipo = paramMovimentacao.getTipo();
      valor = paramMovimentacao.getValor();
    }

    CriteriaBuilder cb = getEm().getCriteriaBuilder();
    CriteriaQuery<Movimentacao> c = cb.createQuery(Movimentacao.class);
    Root<Movimentacao> tabelaDeMovimentacao = c.from(Movimentacao.class);
    c.select(tabelaDeMovimentacao);
    List<Predicate> criteria = new ArrayList<Predicate>();

    if (existe(codigo)) {
      ParameterExpression<String> p = cb.parameter(String.class, "codigo");
      criteria.add(cb.equal(tabelaDeMovimentacao.<String>get("codigo"), p));
    }

    if (existe(descricao)) {
      ParameterExpression<String> p = cb.parameter(String.class, "descricao");
      criteria.add(cb.equal(tabelaDeMovimentacao.<String>get("descricao"), p));
    }

    if (existe(tipo)) {
      ParameterExpression<String> p = cb.parameter(String.class, "tipo");
      criteria.add(cb.equal(tabelaDeMovimentacao.<String>get("tipo"), p));
    }

    if (existe(valor)) {
      ParameterExpression<String> p = cb.parameter(String.class, "valor");
      criteria.add(cb.equal(tabelaDeMovimentacao.<String>get("valor"), p));
    }

    if (criteria.size() == 0) {
      throw new RuntimeException("no criteria");
    } else if (criteria.size() == 1) {
      c.where(criteria.get(0));
    } else {
      c.where(cb.and(criteria.toArray(new Predicate[0])));
    }

    TypedQuery<Movimentacao> q = getEm().createQuery(c);

    if (existe(codigo)) {
      q.setParameter("codigo", codigo);
    }

    if (existe(descricao)) {
      q.setParameter("descricao", descricao);
    }

    if (existe(tipo)) {
      q.setParameter("tipo", tipo);
    }

    if (existe(valor)) {
      q.setParameter("valor", valor);
    }

    return q.getResultList();
  }
  /** This test demonstrates the use of like in where clauses */
  @Test
  public void testLike() {
    log.info("*** testLike() ***");

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

      // select c from Clerk c
      // where c.firstName like 'M%'
      Root<Clerk> c = qdef.from(Clerk.class);
      qdef.select(c).where(cb.like(c.<String>get("firstName"), "M%"));

      int rows = executeQuery(qdef).size();
      assertEquals("unexpected number of rows", 2, rows);
    }

    {
      CriteriaQuery<Clerk> qdef = cb.createQuery(Clerk.class);

      // select c from Clerk c
      // where c.firstName like :firstName
      Root<Clerk> c = qdef.from(Clerk.class);
      qdef.select(c)
          .where(cb.like(c.<String>get("firstName"), cb.parameter(String.class, "firstName")));
      TypedQuery<Clerk> query = em.createQuery(qdef).setParameter("firstName", "M%");
      List<Clerk> results = query.getResultList();
      for (Object o : results) {
        log.info("found result:" + o);
      }
      assertEquals("unexpected number of rows", 2, results.size());
    }

    {
      CriteriaQuery<Clerk> qdef = cb.createQuery(Clerk.class);

      // select c from Clerk c
      // where c.firstName like concat(:firstName,'%')
      Root<Clerk> c = qdef.from(Clerk.class);
      qdef.select(c)
          .where(
              cb.like(
                  c.<String>get("firstName"),
                  cb.concat(cb.parameter(String.class, "firstName"), "%")));
      TypedQuery<Clerk> query = em.createQuery(qdef).setParameter("firstName", "M");
      List<Clerk> results = query.getResultList();
      for (Object o : results) {
        log.info("found result:" + o);
      }
      assertEquals("unexpected number of rows", 2, results.size());
    }

    {
      CriteriaQuery<Clerk> qdef = cb.createQuery(Clerk.class);

      // select c from Clerk c
      // where c.firstName like '_anny'
      Root<Clerk> c = qdef.from(Clerk.class);
      qdef.select(c).where(cb.like(c.<String>get("firstName"), "_anny"));
      TypedQuery<Clerk> query = em.createQuery(qdef);
      List<Clerk> results = query.getResultList();
      for (Object o : results) {
        log.info("found result:" + o);
      }
      assertEquals("unexpected number of rows", 1, results.size());
    }
  }
Exemple #11
0
  private List<User> getUsersUsingCriteria(
      String fromUserId,
      String toUserId,
      String userName,
      String email,
      String qq,
      String weibo,
      String phoneNumber,
      String fromCreationDate,
      String toCreationDate)
      throws ParseException {
    if (QueryUtil.queryParameterProvided(fromUserId)
        && !QueryUtil.queryParameterProvided(toUserId)) {
      toUserId = fromUserId;
    }
    if (QueryUtil.queryParameterProvided(toUserId)
        && !QueryUtil.queryParameterProvided(fromUserId)) {
      fromUserId = toUserId;
    }

    EntityManager em = emf.createEntityManager();
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<User> c = cb.createQuery(User.class);
    Root<User> user = c.from(User.class);
    c.select(user);
    c.orderBy(cb.asc(user.get("userId")));
    c.distinct(true);
    List<Predicate> criteria = new ArrayList<>();

    if (QueryUtil.queryParameterProvided(fromUserId)) {
      ParameterExpression<String> p = cb.parameter(String.class, "fromUserId");
      criteria.add(cb.greaterThanOrEqualTo(user.<String>get("userId"), p));
    }

    if (QueryUtil.queryParameterProvided(toUserId)) {
      ParameterExpression<String> p = cb.parameter(String.class, "toUserId");
      criteria.add(cb.lessThanOrEqualTo(user.<String>get("userId"), p));
    }

    if (QueryUtil.queryParameterProvided(userName)) {
      ParameterExpression<String> p = cb.parameter(String.class, "userName");
      if (userName.contains(QueryUtil.WILDCARDS)) {
        criteria.add(cb.like(user.<String>get("userName"), p));
      } else {
        criteria.add(cb.equal(user.get("userName"), p));
      }
    }

    if (QueryUtil.queryParameterProvided(fromCreationDate)) {
      ParameterExpression<Calendar> p = cb.parameter(Calendar.class, "fromCreationDate");
      criteria.add(cb.greaterThanOrEqualTo(user.<Calendar>get("creationDate"), p));
    }

    if (QueryUtil.queryParameterProvided(toCreationDate)) {
      ParameterExpression<Calendar> p = cb.parameter(Calendar.class, "toCreationDate");
      criteria.add(cb.lessThanOrEqualTo(user.<Calendar>get("creationDate"), p));
    }

    if (QueryUtil.queryParameterProvided(email)) {
      ParameterExpression<String> p = cb.parameter(String.class, "email");
      if (email.contains(QueryUtil.WILDCARDS)) {
        criteria.add(cb.like(user.<String>get("email"), p));
      } else {
        criteria.add(cb.equal(user.get("email"), p));
      }
    }
    if (QueryUtil.queryParameterProvided(qq)) {
      ParameterExpression<String> p = cb.parameter(String.class, "qq");
      if (qq.contains(QueryUtil.WILDCARDS)) {
        criteria.add(cb.like(user.<String>get("qq"), p));
      } else {
        criteria.add(cb.equal(user.get("qq"), p));
      }
    }

    if (QueryUtil.queryParameterProvided(weibo)) {
      ParameterExpression<String> p = cb.parameter(String.class, "weibo");
      if (weibo.contains(QueryUtil.WILDCARDS)) {
        criteria.add(cb.like(user.<String>get("weibo"), p));
      } else {
        criteria.add(cb.equal(user.get("weibo"), p));
      }
    }

    if (QueryUtil.queryParameterProvided(phoneNumber)) {
      ParameterExpression<String> p = cb.parameter(String.class, "phoneNumber");
      if (phoneNumber.contains(QueryUtil.WILDCARDS)) {
        criteria.add(cb.like(user.<String>get("phoneNumber"), p));
      } else {
        criteria.add(cb.equal(user.get("phoneNumber"), p));
      }
    }

    if (criteria.size() == 1) {
      c.where(criteria.get(0));
    } else if (criteria.size() > 1) {
      c.where(cb.and(criteria.toArray(new Predicate[0])));
    }
    TypedQuery<User> q = em.createQuery(c);

    if (QueryUtil.queryParameterProvided(fromUserId)) {
      q.setParameter("fromUserId", fromUserId);
    }

    if (QueryUtil.queryParameterProvided(toUserId)) {
      q.setParameter("toUserId", toUserId);
    }

    if (QueryUtil.queryParameterProvided(userName)) {
      q.setParameter("userName", userName.replace(QueryUtil.WILDCARDS, QueryUtil.PERCENTAGE));
    }

    if (QueryUtil.queryParameterProvided(email)) {
      q.setParameter("email", email.replace(QueryUtil.WILDCARDS, QueryUtil.PERCENTAGE));
    }
    if (QueryUtil.queryParameterProvided(qq)) {
      q.setParameter("qq", qq.replace(QueryUtil.WILDCARDS, QueryUtil.PERCENTAGE));
    }

    if (QueryUtil.queryParameterProvided(weibo)) {
      q.setParameter("weibo", weibo.replace(QueryUtil.WILDCARDS, QueryUtil.PERCENTAGE));
    }

    if (QueryUtil.queryParameterProvided(phoneNumber)) {
      q.setParameter("phoneNumber", phoneNumber.replace(QueryUtil.WILDCARDS, QueryUtil.PERCENTAGE));
    }

    if (QueryUtil.queryParameterProvided(fromCreationDate)) {
      Calendar fromDate = Calendar.getInstance();
      fromDate.setTime(new SimpleDateFormat("yyyy-MM-dd").parse(fromCreationDate));
      q.setParameter("fromCreationDate", fromDate);
    }

    if (QueryUtil.queryParameterProvided(toCreationDate)) {
      Calendar toDate = Calendar.getInstance();
      toDate.setTime(new SimpleDateFormat("yyyy-MM-dd").parse(toCreationDate));
      q.setParameter("toCreationDate", toDate);
    }

    return q.getResultList();
  }