解決方案之一是：
- [ERROR] /D:/_myBooks/book4/book-source/c03/jpa-advanced/target/generated-sources/annotations/entity/Staff_.java:[9,17] duplicate class: entity.Staff_
- [ERROR] /D:/_myBooks/book4/book-source/c03/jpa-advanced/target/generated-sources/annotations/entity/Employee_.java:[12,17] duplicate class: entity.Employee_
當JPQL使用以下查詢時：
- key()：取得Map物件的key值，等同JPQL的KEY敘述。
- value()：取得Map物件的value值，等同JPQL的VALUE敘述。
- entry()：同時取得Map物件的key與value值，等同JPQL的ENTRY敘述。
對應的Criteria API用法如下：
- TypedQuery
typedQuery = em.createQuery( "SELECT c.name, KEY(map), VALUE(map) FROM Customer c JOIN c.itemQtyMap map", Tuple.class);
- @Test
- public void Criteria_GetMapKeyValueByTuple() {
- EntityManager em = emf.createEntityManager();
- CriteriaBuilder cb = em.getCriteriaBuilder();
- CriteriaQuery
sql = cb.createTupleQuery(); Root cust = sql.from(Customer.class); MapJoin itemQtyMap = cust.join(Customer_.itemQtyMap); sql.multiselect(cust.get(Customer_.name), itemQtyMap.key(), itemQtyMap.value() ); TypedQuery typedQuery = em.createQuery(sql); List resultList = typedQuery.getResultList(); List stringList = resultList.stream() .map(t -> t.get(0) + ", " + t.get(1) + ", " + t.get(2)) .collect(Collectors.toList()); stringList.forEach(System.out::println); assertThat(stringList, containsInAnyOrder("jim, computer, 1", "jim, mouse, 4", "colin, notebook, 3")); }
使用Tuple封裝查詢結果的複數欄位。
使用Root<Customer> cust的join()方法聯結Map型態的欄位，並回傳MapJoin<Customer, String, Integer>物件。介面MapJoin<1, 2, 3>的泛型指定3種型態：
1. Entity型態，本例為Customer。
2. Map欄位的key型態，本例為String。
3. Map欄位的value型態，本例為Integer。
使用CriteriaBuilder物件的multiselect()方法查詢name欄位, Map欄位的key欄位由itemQtyMap.key()表示，Map欄位的value欄位由itemQtyMap.value()表示。當JPQL使用以下查詢時：
- TypedQuery
- @Test
- public void Criteria_GetEntryByMapEntry() {
- EntityManager em = emf.createEntityManager();
- CriteriaBuilder cb = em.getCriteriaBuilder();
- CriteriaQuery
使用Map.Entry封裝查詢後的Map的key與value欄位。
使用CriteriaBuilder物件的select()方法查詢itemQtyMap.entry()，將同時取得key與value欄位。值得注意的是，本例在5.2.10.Final的hibernate版本下將拋出ClassCastException的例外，使用較新版本則無此問題。
對應的Criteria API用法如下：
- SELECT DISTINCT e FROM Employee1 e
- INNER JOIN FETCH e.tasks t
- @Test
- public void Criteria_InnerJoinFetch() {
- EntityManager em = emf.createEntityManager();
- CriteriaBuilder cb = em.getCriteriaBuilder();
- CriteriaQuery
sql = cb.createQuery(Employee1.class); Root emp1 = sql.from(Employee1.class); Fetch task = emp1.fetch(Employee1_.tasks, JoinType.INNER); sql.select(emp1) .distinct(true); TypedQuery typedQuery = em.createQuery(sql); List resultList = typedQuery.getResultList(); resultList.forEach(System.out::println); assertThat(resultList, containsInAnyOrder(employee1, employee2, employee3)); assertThat(resultList, hasSize(3)); assertEquals(1, keyWordCount(out.getLog(), "Hibernate:")); }
使用Root<Employee1> emp1的fetch()方法並指定：
1. 聯結「欄位」，本例為Employee1_.tasks。
2. 聯結「型態」，本例為JoinType.INNER。
可以得到Fetch<Employee1, Task>物件，和先前Join<Employee1, Task>物件參考相似。本例並無使用該物件參考，實際上並不需要特別宣告物件參考指向方法執行後的回傳結果，只是單純讓讀者了解fetch()方法回傳型態為Fetch物件。
使用sql.select(emp1)看似只查詢Employee1類別，但使用fetch()方法已經一次取回Employee1類別與其關聯的Task類別的所有欄位資料，因此呼叫emp1.getTasks()將可以直接由本地端JVM取得資料，不需要依賴
@ManyToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
private List<Task> tasks;
的宣告逐筆至遠端資料庫中取回資料。優點是可以減少資料庫的shared lock狀況並提升效能，但必須衡量是否有需要這些資料，以及記憶體使用狀況。
對應的Criteria API用法如下。注意行8使用fetch()方法並指定聯結型態為JoinType.LEFT：
- SELECT DISTINCT e FROM Employee1 e
- LEFT JOIN FETCH e.tasks t
- @Test
- public void Criteria_LeftJoinFetch() {
- EntityManager em = emf.createEntityManager();
- CriteriaBuilder cb = em.getCriteriaBuilder();
- CriteriaQuery
sql = cb.createQuery(Employee1.class); Root emp1 = sql.from(Employee1.class); Fetch task = emp1.fetch(Employee1_.tasks, JoinType.LEFT); sql.select(emp1) .distinct(true); TypedQuery typedQuery = em.createQuery(sql); List resultList = typedQuery.getResultList(); resultList.forEach(System.out::println); assertThat(resultList, containsInAnyOrder(employee1, employee2, employee3, employee4)); assertThat(resultList, hasSize(4)); assertEquals(1, keyWordCount(out.getLog(), "Hibernate:")); }
對應的Criteria API用法如下：
- SELECT DISTINCT e FROM Employee e
- INNER JOIN e.tasks t
說明
- public void Criteria_InnnerJoin() {
- EntityManager em = emf.createEntityManager();
- CriteriaBuilder cb = em.getCriteriaBuilder();
- CriteriaQuery
sql = cb.createQuery(Employee.class); Root emp = sql.from(Employee.class); emp.join(Employee_.tasks); sql.select(emp) .distinct(true); TypedQuery typedQuery = em.createQuery(sql); List resultList = typedQuery.getResultList(); resultList.forEach(System.out::println); em.close(); assertThat(resultList, containsInAnyOrder(employee1, employee2, employee3)); assertThat(resultList, hasSize(3)); }
emp.join(Employee_.tasks); 等價於Employee e INNER JOIN e.tasks t
對應的Criteria API用法如下：
- SELECT DISTINCT e FROM Employee e
- INNER JOIN e.tasks t
- WHERE t.name='Denise'
說明：
- @Test
- public void Criteria_InnerJoinWhere1() {
- EntityManager em = emf.createEntityManager();
- CriteriaBuilder cb = em.getCriteriaBuilder();
- CriteriaQuery
sql = cb.createQuery(Employee.class); Root emp = sql.from(Employee.class); Join task = emp.join(Employee_.tasks); sql.select(emp) .where(cb.equal(task.get(Task_.name), "Denise")) .distinct(true); TypedQuery typedQuery = em.createQuery(sql); List resultList = typedQuery.getResultList(); resultList.forEach(System.out::println); em.close(); assertThat(resultList, containsInAnyOrder(employee1, employee3)); assertThat(resultList, hasSize(2)); }
因WHERE子句需要INNER JOIN的表格的欄位加以限制條件，因此需要使用變數Join<Employee, Task> task承接Root<Employee>呼叫join()方法後回傳的物件。相似於Root<Employee>，但用Join<Employee, Task>；變數名稱慣例上使用第2個泛型型態Task來命名，本例為「task」。
若需要連續JOIN數個表格，如Entity類別A需要JOIN類別B與C，則依此類推：
Root<A> a = sql.from(A.class);
Join<A, C> c = a.join(A_.b).join(B_.c);
where(cb.equal(task.get(Task_.name), "Denise"))等價於「WHERE t.name='Denise'」
對應的Criteria API用法如下：
- SELECT DISTINCT e FROM Employee e
- INNER JOIN e.tasks t
- WHERE t.name = e.name
說明：
- @Test
- public void Criteria_InnerJoinWhere2() {
- EntityManager em = emf.createEntityManager();
- CriteriaBuilder cb = em.getCriteriaBuilder();
- CriteriaQuery
sql = cb.createQuery(Employee.class); Root emp = sql.from(Employee.class); ListJoin task = emp.join(Employee_.tasks); sql.select(emp) .where( cb.equal( task.get(Task_.name), emp.get(Employee_.name) ) ); TypedQuery typedQuery = em.createQuery(sql); List resultList = typedQuery.getResultList(); resultList.forEach(System.out::println); em.close(); assertThat(resultList, containsInAnyOrder(employee2)); assertThat(resultList, hasSize(1)); }
where( cb.equal( task.get(Task_.name), emp.get(Employee_.name) ) );等價於「WHERE t.name = e.name」
等價於使用以下Criteria API查詢：
- SELECT NEW entity.EmployeeInfo(e.name, e.salary) FROM Employee e
說明
- @Test
- public void Criteria_MultiColumnsWithConstructor1() {
- EntityManager em = emf.createEntityManager();
- CriteriaBuilder cb = em.getCriteriaBuilder();
- CriteriaQuery
sql = cb.createQuery(EmployeeInfo.class); Root emp = sql.from(Employee.class); sql.select( cb.construct(EmployeeInfo.class, emp.get(Employee_.name), emp.get(Employee_.salary)) ); TypedQuery typedQuery = em.createQuery(sql); List resultList = typedQuery.getResultList(); resultList.forEach(System.out::println); em.close(); assertThat(resultList, containsInAnyOrder(myEmp1, myEmp2, myEmp3, myEmp4, myEmp5)); }
CriteriaQuery須指定查詢結果以EmployeeInfo呈現。
TypedQuery也指定查詢結果以EmployeeInfo呈現。
對應的Criteria API用法如下。本例中將原本欄位改名，因此由Tuple資料結構取出資料須用別名(alias)：
- TypedQuery
query = em.createQuery( "SELECT e.name, e.salary FROM Employee e", Tuple.class);
- @Test
- public void Criteria_Tuple() {
- EntityManager em = emf.createEntityManager();
- CriteriaBuilder cb = em.getCriteriaBuilder();
- CriteriaQuery
sql = cb.createTupleQuery(); Root emp = sql.from(Employee.class); sql.multiselect( emp.get(Employee_.name).alias("employeeName"), emp.get(Employee_.salary).alias("salary") ); TypedQuery typedQuery = em.createQuery(sql); List resultList = typedQuery.getResultList(); // get data by alias for (Tuple tuple : resultList) { String employeeName = tuple.get("employeeName", String.class); Double salary = tuple.get("salary", Double.class); System.out.println(employeeName + "_" + salary); } em.close(); // validate result List stringList = resultList.stream() .map(t -> t.get("employeeName", String.class) + "_" + t.get("salary", Double.class)) .collect(Collectors.toList()); assertThat(stringList,containsInAnyOrder("Jim_3000.0", "Rose_4000.0", "Denise_1500.0", "Mike_2000.0")); }
CriteriaQuery物件指定使用Tuple型態封裝複數欄位的查詢結果。
欄位name的別名使用alias()方法設定為"employeeName"。
欄位salary的別名使用alias()方法設定為"salary"。
TypedQuery物件同時指定型態為Tuple，後續所有物件若使用泛型者皆同。
Tuple型態物件使用get()方法取出需指定名稱(別名)和型態：
tuple.get("employeeName", String.class);
tuple.get("salary", Double.class);
2. 使用位置數字
- Query query = em.createQuery(
- "SELECT e FROM Employee e WHERE e.dept = :deptName" );
- query.setParameter("deptName", "IT");
Criteria API建立ParameterExpression物件並以泛型指定查詢參數型態作為「替代字符」，後續再以setParameter()方法的繫結該「替代字符」與「真實參數值」。繫結方式也分為兩種，分別示範如下。
- Query query = em.createQuery(
- "SELECT e FROM Employee e WHERE e.dept = ?1" );
- query.setParameter(1, "IT");
【說明】
- @Test
- public void Criteria_NoNamedParam() {
- EntityManager em = emf.createEntityManager();
- CriteriaBuilder cb = em.getCriteriaBuilder();
- CriteriaQuery
sql = cb.createQuery(Employee.class); Root emp = sql.from(Employee.class); ParameterExpression param = cb.parameter(String.class); Expression expr = cb.equal(emp.get(Employee_.name), param); sql.select(emp) .where(expr); TypedQuery typedQuery = em.createQuery(sql); typedQuery.setParameter(param, "Jim"); Employee p = typedQuery.getSingleResult(); System.out.println(p); assertEquals(e1, p); em.close(); }
使用CriteriaBuilder的parameter()方法並指定參數型態為String以建立ParameterExpression<String>物件，且物件參考名稱為param。
CriteriaQuery的where ()方法依然需要傳入Expression<Boolean>物件，本例使用CriteriaBuilder的equal()方法建構。
Expression<Boolean>物件的第二個參數過去使用字面常量，使用繫結變量須改傳入ParameterExpression的物件參考param。
TypedQuery執行查詢時，使用方法setParameter(param, "Jim")指定前述物件參考param與參數值的繫結關係。
兩者結果不變。
- @Test
- public void Criteria_NamedParam() {
- // the same codes
- ParameterExpression
param = cb.parameter(String.class, “myName”); // the same codes typedQuery.setParameter("myName", "Jim"); // the same codes }
等價於使用以下Criteria API查詢。因為只查詢一個欄位dept，行7使用CriteriaQuery的distinct(true)方法只會針對該欄位過濾重複值：
- SELECT DISTINCT e.dept FROM Employee e
- @Test
- public void Criteria_DistinctColumn() {
- EntityManager em = emf.createEntityManager();
- CriteriaQuery
sql = em.getCriteriaBuilder().createQuery(String.class); Root emp = sql.from(Employee.class); sql.select(emp.get(Employee_.dept)) .distinct(true); TypedQuery typedQuery = em.createQuery(sql); List resultList = typedQuery.getResultList(); resultList.forEach(System.out::println); assertThat(resultList, contains("IT", "Admin")); }
等價於使用以下Criteria API查詢。多欄位的結果使用Object[ ]呈現：
- SELECT DISTINCT e.dept, e.salary FROM Employee e
- ORDER BY e.dept ASC, e.salary DESC
- @Test
- public void Criteria_DistinctMultiColumns() {
- EntityManager em = emf.createEntityManager();
- CriteriaBuilder cb = em.getCriteriaBuilder();
- CriteriaQuery
前述範例等價於使用以下JPQL查詢：
- @Test
- public void Criteria_OrderByAsc() {
- EntityManager em = emf.createEntityManager();
- CriteriaBuilder cb = em.getCriteriaBuilder();
- CriteriaQuery
sql = cb.createQuery(Employee.class); Root emp = sql.from(Employee.class); sql.select(emp); Order salaryOrder = cb.asc(emp.get(Employee_.salary)); // Order salaryOrder = cb.desc(emp.get(Employee_.salary)); sql.orderBy(salaryOrder); TypedQuery typedQuery = em.createQuery(sql); List resultList = typedQuery.getResultList(); resultList.forEach(System.out::println); em.close(); assertThat(resultList, contains(employee3, employee1, employee2)); }
- SELECT e FROM Employee e ORDER BY e.salary
- // SELECT e FROM Employee e ORDER BY e.salary ASC
前述範例等價於使用以下JPQL查詢：
- @Test
- public void Criteria_OrderByMultiColumns() {
- EntityManager em = emf.createEntityManager();
- CriteriaBuilder cb = em.getCriteriaBuilder();
- CriteriaQuery
sql = cb.createQuery(Employee.class); Root emp = sql.from(Employee.class); sql.select(emp) .orderBy( cb.asc(emp.get(Employee_.dept)), cb.desc(emp.get(Employee_.salary)) ); TypedQuery typedQuery = em.createQuery(sql); List resultList = typedQuery.getResultList(); resultList.forEach(System.out::println); em.close(); assertThat(resultList, contains(employee2, employee1, employee3)); }
- SELECT e FROM Employee e ORDER BY e. dept ASC, e.salary DESC
等價於使用以下Criteria API查詢，且行8顯示否定用法：
- SELECT e FROM Employee e WHERE e.name = 'Rose'
- @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)); }
等價於使用以下Criteria API查詢。比較數字使用方法gt()表示greater-than：
- SELECT e FROM Employee e WHERE e.salary > 2000.0
- @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)); }
等價於使用以下Criteria API查詢。Entity欄位型態為enum可以直接使用equal()方法：
- SELECT e FROM Employee e WHERE e.role = entity.Role.IT
- @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)); }
等價於使用以下Criteria API查詢。比較日期時，無法直接比較日期格式如Timestamp，必須借助CriteriaBuilder的literal()方法轉換為Expression<Timestamp>後再套用到greaterThanOrEqualTo()，如範例程式碼行7~10：
- SELECT e FROM Employee e WHERE e.joinDate > '2017-05-01'
- @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)); }
等價於使用以下Criteria API查詢。若Entity的Boolean欄位為true，可以使用範例程式碼行9的方法isTrue()或行10直接省略，兩者效果相同；若欄位值要求為false則使用行11的方法isFalse()：
- SELECT e FROM Employee e WHERE e.fullTime = true
- @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)); }
- SELECT e.name FROM Employee e
【說明】
- @Test
- public void criteria_SelectSingle() {
- EntityManager em = emf.createEntityManager();
- CriteriaBuilder cb = em.getCriteriaBuilder();
- CriteriaQuery
sql = cb.createQuery(String.class); Root emp = sql.from(Employee.class); Path name = emp.get("name"); sql.select(name); TypedQuery typedQuery = em.createQuery(sql); List resultList = typedQuery.getResultList(); resultList.forEach(System.out::println); em.close(); assertThat(resultList, hasSize(3)); }
5
|
CriteriaQuery指定要回傳的資料型態，欄位name的型態為String。
|
7-8
|
CriteriaQuery的select()方法接受Expression型態的物件，Path為其子型態，也可以使用Expression<String>宣告變數。
|
以下範例行7與行8為使用Criteria API的作法。因為CriteriaBuilder的方法array()接受「可變動個數參數(varargs)」，且介面Selection是介面Expression的父型態，因此方法參數可接受個數變動的Expression物件：
- SELECT e.id, e.name FROM Employee e
- CompoundSelection
- @Test
- public void criteria_SelectArray() {
- EntityManager em = emf.createEntityManager();
- CriteriaBuilder cb = em.getCriteriaBuilder();
- CriteriaQuery
5
|
CriteriaQuery指定查詢後得到的資料型態，可使用Object[]同時將欄位id, name的資料封裝。
|
7-8
|
CriteriaQuery的select()方法接受多個Expression型態的物件。 |
7
|
欄位名稱使用String描述。
|
8
|
欄位名稱使用metamodel類別Employee1_的靜態欄位name描述。
|
此時不需要使用CriteriaBuilder的array()方法結合欄位，直接列舉即可：
- CriteriaQuery
multiselect(Selection... elections);
- @Test
- public void criteria_MultiSelect() {
- EntityManager em = emf.createEntityManager();
- CriteriaBuilder cb = em.getCriteriaBuilder();
- CriteriaQuery
