/** 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());
  }
  @Test
  public void testQuotientAndMultiply() {
    EntityManager em = getOrCreateEntityManager();
    em.getTransaction().begin();
    CriteriaQuery<Number> criteria = builder.createQuery(Number.class);
    criteria.from(Product.class);
    criteria.select(
        builder.quot(
            builder.prod(
                builder.literal(BigDecimal.valueOf(10.0)),
                builder.literal(BigDecimal.valueOf(5.0))),
            BigDecimal.valueOf(2.0)));
    Number result = em.createQuery(criteria).getSingleResult();
    assertEquals(25.0d, result.doubleValue(), 0.1d);

    criteria.select(
        builder.prod(
            builder.quot(
                builder.literal(BigDecimal.valueOf(10.0)),
                builder.literal(BigDecimal.valueOf(5.0))),
            BigDecimal.valueOf(2.0)));
    result = em.createQuery(criteria).getSingleResult();
    assertEquals(4.0d, result.doubleValue(), 0.1d);

    em.getTransaction().commit();
    em.close();
  }
 @Test
 public void testDiff() {
   EntityManager em = getOrCreateEntityManager();
   em.getTransaction().begin();
   CriteriaQuery<Integer> criteria = builder.createQuery(Integer.class);
   criteria.from(Product.class);
   criteria.select(builder.diff(builder.literal(5), builder.literal(2)));
   Integer result = em.createQuery(criteria).getSingleResult();
   assertEquals(Integer.valueOf(3), result);
   em.getTransaction().commit();
   em.close();
 }
Exemple #4
0
  /** @author Romain Wurtz <*****@*****.**> */
  public List<Predicate> createPredicates(
      Root<User> from, CriteriaBuilder criteriaBuilder, Map<String, String> filters) {
    List<Predicate> predicates = new ArrayList<Predicate>();
    for (Iterator<String> it = filters.keySet().iterator(); it.hasNext(); ) {
      String filterProperty = it.next();
      String filterValue = filters.get(filterProperty);

      Expression<String> literal = criteriaBuilder.literal("%" + (String) filterValue + "%");
      predicates.add(criteriaBuilder.like(from.<String>get(filterProperty), literal));
    }
    return predicates;
  }
  /** This test method demonstrates several date functions */
  @Test
  @SuppressWarnings({"rawtypes", "unchecked"})
  public void testStringFunctions() {
    log.info("*** testStringFunctions() ***");

    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery qdef = cb.createQuery();
    Root<Customer> c = qdef.from(Customer.class);

    // select c from Customer c
    // where c.firstName='CAT'
    qdef.select(c).where(cb.equal(c.get("firstName"), "CAT"));
    int rows = executeQuery(qdef).size();
    assertEquals("unexpected number of rows", 0, rows);

    // select c from Customer c
    // where c.firstName=LOWER('CAT')"
    qdef.select(c).where(cb.equal(c.get("firstName"), cb.lower(cb.literal("CAT"))));
    rows = executeQuery(qdef).size();
    assertEquals("unexpected number of rows", 1, rows);

    // select UPPER(c.firstName) from Customer c
    // where c.firstName=LOWER('CAT')
    qdef.select(cb.upper(c.<String>get("firstName")))
        .where(cb.equal(c.get("firstName"), cb.lower(cb.literal("CAT"))));
    rows = executeQuery(qdef).size();
    assertEquals("unexpected number of rows", 1, rows);

    // TODO: determine why SQL generated without quotes here with H2Dialect
    /*
    Caused by: org.h2.jdbc.JdbcSQLException: Column "C" not found; SQL statement:
    select trim(LEADING c from customer0_.FIRST_NAME) as col_0_0_ from JPAQL_CUSTOMER customer0_
    where customer0_.FIRST_NAME=?
    */

    // select TRIM(LEADING 'c' FROM c.firstName) from Customer c
    // where c.firstName='cat')
    qdef.select(cb.trim(Trimspec.LEADING, 'c', c.<String>get("firstName")))
        .where(cb.equal(c.get("firstName"), "cat"));
    //        List<String> result = executeQuery(qdef);
    //        assertEquals("unexpected number of rows", 1, rows);
    //        assertEquals("unexpected value", "at", result.get(0));

    // select c from Customer c
    // where CONCAT(CONCAT(c.firstName,' '),c.lastName) ='cat inhat')
    qdef.select(c)
        .where(
            cb.equal(
                cb.concat(cb.concat(c.<String>get("firstName"), " "), c.<String>get("lastName")),
                "cat inhat"));
    rows = executeQuery(qdef).size();
    assertEquals("unexpected number of rows:" + rows, 1, rows);

    // select c from Customer c
    // where LENGTH(c.firstName) = 3
    qdef.select(c).where(cb.equal(cb.length(c.<String>get("firstName")), 3));
    rows = executeQuery(qdef).size();
    assertEquals("unexpected number of rows:" + rows, 1, rows);

    // select c from Customer c " +
    // where LOCATE('cat',c.firstName,2) > 0",
    qdef.select(c).where(cb.greaterThan(cb.locate(c.<String>get("firstName"), "cat", 2), 0));
    rows = executeQuery(qdef).size();
    assertEquals("unexpected number of rows:" + rows, 0, rows);

    // select c from Customer c
    // where LOCATE('at',c.firstName,2) > 1
    qdef.select(c).where(cb.greaterThan(cb.locate(c.<String>get("firstName"), "at", 2), 1));
    rows = executeQuery(qdef).size();
    assertEquals("unexpected number of rows:" + rows, 1, rows);

    // select SUBSTRING(c.firstName,2,2) from Customer c " +
    // where c.firstName = 'cat'",
    qdef.select(cb.substring(c.<String>get("firstName"), 2, 2))
        .where(cb.equal(c.get("firstName"), "cat"));
    List<String> result = executeQuery(qdef);
    assertEquals("unexpected number of rows", 1, rows);
    assertEquals("unexpected value", "at", result.get(0));

    // select c from Customer c
    // where SUBSTRING(c.firstName,2,2) = 'at'
    qdef.select(c).where(cb.equal(cb.substring(c.<String>get("firstName"), 2, 2), "at"));
    rows = executeQuery(qdef).size();
    assertEquals("unexpected number of rows:" + rows, 1, rows);
  }