WHERE條件值使用字面常量(Literal)

字面常量使用字串

使用以下JPQL查詢時：

SELECT e FROM Employee e WHERE e.name = 'Rose'

等價於使用以下Criteria API查詢，且行8顯示否定用法：

@Test 
public void Criteria_EqualString() {
  EntityManager em = emf.createEntityManager();
  CriteriaBuilder cb = em.getCriteriaBuilder();
  CriteriaQuery sql = cb.createQuery(Employee.class);
  Root emp = sql.from(Employee.class);
  Expression expr = cb.equal(emp.get(Employee_.name), "Rose");
// Expression expr = cb.equal(emp.get(Employee_.name), "Rose").not();
  sql.select(emp).where(expr);
  TypedQuery tq = em.createQuery(sql);
  List resultList = tq.getResultList();
  resultList.forEach(System.out::println);
  assertThat(resultList, containsInAnyOrder(employee2));
}

字面常量使用數字

使用以下JPQL查詢時：

SELECT e FROM Employee e WHERE e.salary > 2000.0

等價於使用以下Criteria API查詢。比較數字使用方法gt()表示greater-than：

@Test 
public void Criteria_GreaterNumber() {
    EntityManager em = emf.createEntityManager();
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery sql = cb.createQuery(Employee.class);
    Root emp = sql.from(Employee.class);
    Expression expr = cb.gt(emp.get(Employee_.salary), 2000.0);
    sql.select(emp).where(expr);
    TypedQuery typedQuery = em.createQuery(sql);
    List resultList = typedQuery.getResultList();
    resultList.forEach(System.out::println);
    assertThat(resultList, containsInAnyOrder(employee1, employee2));
}

字面常量使用enum

使用以下JPQL查詢時：

SELECT e FROM Employee e WHERE e.role = entity.Role.IT

等價於使用以下Criteria API查詢。Entity欄位型態為enum可以直接使用equal()方法：

@Test 
public void Criteria_EqualEnum() {
    EntityManager em = emf.createEntityManager();
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery sql = cb.createQuery(Employee.class);
    Root emp = sql.from(Employee.class);
    Expression expr = cb.equal(emp.get(Employee_.role), Role.IT);
    sql.select(emp).where(expr);
    TypedQuery tq = em.createQuery(sql);
    List resultList = tq.getResultList();
    resultList.forEach(System.out::println);
    assertThat(resultList, containsInAnyOrder(employee1, employee3));
}

字面常量使用日期

使用以下JPQL查詢時：

SELECT e FROM Employee e WHERE e.joinDate > '2017-05-01'

等價於使用以下Criteria API查詢。比較日期時，無法直接比較日期格式如Timestamp，必須借助CriteriaBuilder的literal()方法轉換為Expression<Timestamp>後再套用到greaterThanOrEqualTo()，如範例程式碼行7~10：

@Test 
public void Criteria_GreaterDate() {
    EntityManager em = emf.createEntityManager();
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery sql = cb.createQuery(Employee.class);
    Root emp = sql.from(Employee.class);
    Expression timestampExpr = 
       cb.literal( localToTimeStamp(LocalDate.of(2017, 05, 01)) );
    Expression expr = 
       cb.greaterThanOrEqualTo(emp.get(Employee_.joinDate), timestampExpr);
    sql.select(emp).where(expr);
    TypedQuery typedQuery = em.createQuery(sql);
    List resultList = typedQuery.getResultList();
    resultList.forEach(System.out::println);
    assertThat(resultList, containsInAnyOrder(employee4, employee2));
}

字面常量使用boolean

使用以下JPQL查詢時：

SELECT e FROM Employee e WHERE e.fullTime = true

等價於使用以下Criteria API查詢。若Entity的Boolean欄位為true，可以使用範例程式碼行9的方法isTrue()或行10直接省略，兩者效果相同；若欄位值要求為false則使用行11的方法isFalse()：

@Test  
public void Criteria_BooleanField() {
    EntityManager em = emf.createEntityManager();
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery sql = cb.createQuery(Employee.class);
    Root emp = sql.from(Employee.class);
    Expression adultExpression = emp.get(Employee_.fullTime);
    sql.select(emp)
        .where(cb.isTrue(adultExpression));
    //  .where(adultExpression);
    //  .where(cb.isFalse(adultExpression));
    TypedQuery typedQuery = em.createQuery(sql);
    List resultList = typedQuery.getResultList();
    resultList.forEach(System.out::println);
    assertThat(resultList, hasItems(employee1));
    assertThat(resultList, hasItems(employee3));
}