本文為【Spring Boot情境式網站開發指南：使用Spring Data JPA、Spring Security、Spring Web Flow】一書的【 第4章 Criteria API入門】延續，完整範例程式碼可至出版社下載
當JPQL使用以下查詢時：
SELECT e FROM Employee e WHERE e.salary LIKE '_500.0'
對應的Criteria API用法如下：@Test
public void Criteria_Like1() {
EntityManager em = emf.createEntityManager();
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery sql = cb.createQuery(Employee.class);
Root emp = sql.from(Employee.class);
sql.select(emp)
.where( cb.like( emp.get(Employee_.salary).as(String.class), "_500.0" ) );
List resultList = em.createQuery(sql).getResultList();
resultList.forEach(System.out::println);
em.close();
assertThat(resultList, containsInAnyOrder(employee4));
assertThat(resultList, hasSize(1));
}
行8：使用CriteriaBuilder的like()方法，第2個欄位指定like的樣式，第1個參數指定欄位。本例因為salary欄位是數字，要樣式比對必須是字串欄位：Predicate like(Expression<String> x, String pattern);因此使用as(String.class)方法將salary數字欄位值轉換為字串：emp.get(Employee_.salary).as(String.class)
使用LIKE搭配萬用字元「%」查詢
當JPQL使用以下查詢時：SELECT e FROM Employee e WHERE e.name LIKE 'J%'對應的Criteria API用法如下：
@Test
public void Criteria_Like2() {
EntityManager em = emf.createEntityManager();
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery sql = cb.createQuery(Employee.class);
Root emp = sql.from(Employee.class);
sql.select(emp)
.where( cb.like( emp.get(Employee_.name), "J%" ) );
List resultList = em.createQuery(sql).getResultList();
resultList.forEach(System.out::println);
em.close();
assertThat(resultList, containsInAnyOrder(employee1));
assertThat(resultList, hasSize(1));
}
使用NOT LIKE搭配命名變數查詢
當JPQL使用以下查詢時：SELECT e FROM Employee e WHERE e.name NOT LIKE :nameStartsWith對應的Criteria API用法如下：
@Test
public void Criteria_NotLikeNamedParams() {
EntityManager em = emf.createEntityManager();
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery sql = cb.createQuery(Employee.class);
Root emp = sql.from(Employee.class);
ParameterExpression nameParam =
cb.parameter(String.class, "nameStartsWith");
sql.select(emp)
.where( cb.notLike(emp.get(Employee_.name), nameParam) );
// .where( cb.like(emp.get(Employee_.name), nameParam).not() );
TypedQuery typedQuery = em.createQuery(sql);
typedQuery.setParameter("nameStartsWith", "J%");
List resultList = typedQuery.getResultList();
resultList.forEach(System.out::println);
em.close();
assertThat(resultList, containsInAnyOrder(employee2, employee3, employee4));
assertThat(resultList, hasSize(3));
}
行7-8：以ParameterExpression< String >建立欄位name的命名變數物件參考「nameParam」，其名稱為「nameStartsWith」。行10：
使用CriteriaBuilder的notLike()方法。欄位emp.get(Employee_.name)的限定條件為notLike (nameParam);行11：
也可以使用CriteriaBuilder的like()方法，並在結尾加上not()。行13：
以TypedQuery的setParameter()方法指定命名變數nameStartsWith並傳入字串樣式"J%"作為參數值。
使用LIKE並以ESCAPE跳脫萬用字元「_」查詢
當JPQL使用以下查詢時：SELECT e FROM Employee e WHERE e.dept LIKE '%@_%' ESCAPE '@'
對應的Criteria API用法如下：
@Test
public void Criteria_LikeEscape() {
EntityManager em = emf.createEntityManager();
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery sql = cb.createQuery(Employee.class);
Root emp = sql.from(Employee.class);
sql.select(emp)
.where(cb.like(emp.get(Employee_.dept), "%@_%", '@'));
List resultList = em.createQuery(sql).getResultList();
resultList.forEach(System.out::println);
em.close();
assertThat(resultList, containsInAnyOrder(employee4));
assertThat(resultList, hasSize(1));
}
行8：使用CriteriaBuilder的like()方法，因第2個參數要比對的是like樣式內的特殊字元「_」，因此由最後參數定義使用的跳脫字元：Predicate like(Expression<String> x, String pattern, char escapeChar);本例為「@」：cb.like( emp.get(Employee_.dept), "%@_%", '@' );
