JPA-6 JPQL语言

---

1. 查询所有实体

@Test
public void testHelloJPQL(){
    String jpql = "FROM Customer c WHERE c.age > ?";
    Query query = entityManager.createQuery(jpql);
    query.setParameter(1, 1); //占位符的索引是从 1 开始
    List<Customer> customers = query.getResultList();
    System.out.println(customers.size());
}

---

2. 查询部分属性

//默认情况下, 若只查询部分属性, 则将返回Object[]类型的结果 或者 Object[]类型的List
//也可在实体类中创建对应构造器, 然后在JPQL语句中利用对应的构造器返回实体类的对象，如new Customer(c.lastName, c.age)
@Test
public void testPartlyProperties(){
    String jpql = "SELECT new Customer(c.lastName, c.age) FROM Customer c WHERE c.id > ?";
    List result = entityManager.createQuery(jpql).setParameter(1, 1).getResultList();
    System.out.println(result);
}

---

3. createNamedQuery()

// createNamedQuery()适用于在实体类前使用@NamedQuery标记的查询语句
// 如@NamedQuery(name="testNamedQuery", query="FROM Customer c WHERE c.id = ?")
@Test
public void testNamedQuery(){
    Query query = entityManager.createNamedQuery("testNamedQuery").setParameter(1, 3);
    Customer customer = (Customer) query.getSingleResult();
    System.out.println(customer);
}

---

4. createNativeQuery()

//createNativeQuery()适用于本地SQL语句查询
@Test
public void testNativeQuery(){
    String sql = "SELECT age FROM jpa_cutomers WHERE id = ?";
    Query query = entityManager.createNativeQuery(sql).setParameter(1, 3);
    Object result = query.getSingleResult();
    System.out.println(result);
}

---

5. 查询缓存

//使用Hibernate的查询缓存，需要在配置文件中启用查询缓存
@Test
public void testQueryCache(){
    String jpql = "FROM Customer c WHERE c.age > ?";
    Query query = entityManager.createQuery(jpql).setHint(QueryHints.HINT_CACHEABLE, true).setParameter(1, 1);
    List<Customer> customers = query.getResultList();
    System.out.println(customers.size());
    // 查询缓存即实现只查询一次
    query = entityManager.createQuery(jpql).setHint(QueryHints.HINT_CACHEABLE, true).setParameter(1, 1);
    customers = query.getResultList();
    System.out.println(customers.size());
}

---

6. OrderBy与GroupBy

//查询order数量大于2的Customer
@Test
public void testGroupBy(){
    String jpql = "SELECT o.customer FROM Order o "
            + "GROUP BY o.customer "
            + "HAVING count(o.id) >= 2";
    List<Customer> customers = entityManager.createQuery(jpql).getResultList();
    System.out.println(customers);
}

@Test
public void testOrderBy(){
    String jpql = "FROM Customer c WHERE c.age > ? ORDER BY c.age DESC";
    Query query = entityManager.createQuery(jpql).setParameter(1, 1);
    List<Customer> customers = query.getResultList();
    System.out.println(customers.size());
}

---

7. createNamedQuery()

// JPQL的关联查询同HQL的关联查询
@Test
public void testLeftOuterJoinFetch(){
    String jpql = "FROM Customer c LEFT OUTER JOIN FETCH c.orders WHERE c.id = ?";

    Customer customer = 
            (Customer) entityManager.createQuery(jpql).setParameter(1, 12).getSingleResult();
    System.out.println(customer.getLastName());
    System.out.println(customer.getOrders().size());
//  String jpql = "FROM Customer c LEFT OUTER JOIN c.orders WHERE c.id = ?";    
//  List<Object[]> result = entityManager.createQuery(jpql).setParameter(1, 12).getResultList();
//  System.out.println(result);
}

---

8. 子查询

@Test
public void testSubQuery(){
    //查询所有 Customer 的 lastName 为 YY 的 Order
    String jpql = "SELECT o FROM Order o "
            + "WHERE o.customer = (SELECT c FROM Customer c WHERE c.lastName = ?)";
    Query query = entityManager.createQuery(jpql).setParameter(1, "YY");
    List<Order> orders = query.getResultList();
    System.out.println(orders.size());
}

---

9. 內建函数

//使用JPQL内建的函数
@Test
public void testJpqlFunction(){
    String jpql = "SELECT lower(c.email) FROM Customer c";
    List<String> emails = entityManager.createQuery(jpql).getResultList();
    System.out.println(emails);
}

---

10. UPDATE与DELETE

//可以使用JPQL完成UPDATE和DELETE操作
@Test
public void testExecuteUpdate(){
    String jpql = "UPDATE Customer c SET c.lastName = ? WHERE c.id = ?";
    Query query = entityManager.createQuery(jpql).setParameter(1, "YYY").setParameter(2, 12);
    query.executeUpdate();
}

---

11. 附录