Ejemplo n.º 1
0
  /** 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);
  }
Ejemplo n.º 2
0
  /** 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());
    }
  }