JPQL

1. JPQL语句

1. 查询全部：设置参数时索引从 1 开始

   /**
    * 查询所有：query.setParameter索引从 1 开始
    */
   @Test
   public void selectTest() {
       String jpql = "from User u where u.age > ?";
       TypedQuery<User> query = entityManager.createQuery(jpql, User.class);
       List<User> list = query.setParameter(1, 22).getResultList();
       list.forEach(System.out::println);
   }
   

2. 查询部分属性：位置参数：?位置编号的数值

   /**
    * 查询部分属性：默认返回Object[]
    */
   @Test
   public void selectPartlyPropertiesTest01() {
       String jpql = "select u.lastName, u.age from User u where u.age > ?1";
       Query query = entityManager.createQuery(jpql);
       List list = query.setParameter(1, 22).getResultList();
       list.forEach(System.out::println);
   }
   
   /**
    * 查询部分属性
    *  实体类必须提供对应的构造方法
    */
   @Test
   public void selectPartlyPropertiesTest02() {
       String jpql = "select new User(u.lastName, u.age) from User u where u.age > ?1";
       TypedQuery<User> query = entityManager.createQuery(jpql, User.class);
       List<User> list = query.setParameter(1, 27).getResultList();
       list.forEach(System.out::println);
   }
   

3. namedQuery：查询语句通过@NamedQuery写在实体类上

   // 实体类上的注解。命名参数：语法 ---> :自定义的参数名称
   @NamedQuery(name = "namedQuery", query = "from User u where u.id = :id")
   

   /**
    * jpql语句可以使用 @NamedQuery 写在实体类上
    */
   @Test
   public void namedQueryTest() {
       TypedQuery<User> query = entityManager.createNamedQuery("namedQuery", User.class);
       User user = query.setParameter("id", 2L).getSingleResult();
       System.out.println(user);
   }
   

4. 查询缓存

   1. 需要配置<property name="hibernate.cache.use_query_cache" value="true"/>

   2. 通过setHint方法

      /**
       * 查询缓存
       * 	发送两条sql变为发送一条sql
       */
      @Test
      public void queryCacheTest() {
          String sql = "select * from t_user";
          Query query = entityManager.createNativeQuery(sql, User.class)
              .setHint(QueryHints.HINT_CACHEABLE, true);
          List list = query.getResultList();
          System.out.println(list.size());
          query = entityManager.createNativeQuery(sql, User.class)
              .setHint(QueryHints.HINT_CACHEABLE, true);
          list = query.getResultList();
          System.out.println(list.size());
      }
      

5. 排序

   /**
    * order by
    */
   @Test
   public void selectOrderTest() {
       String jpql = "from User u where u.age > :age order by u.id";
       TypedQuery<User> query = entityManager.createQuery(jpql, User.class);
       List<User> list = query.setParameter("age", 22).getResultList();
       list.forEach(System.out::println);
   }
   

6. 分组

   /**
    * group by
    */
   @Test
   public void selectGroupTest() {
       String jpql = "select u.age, count(u.id) as count from User u "
           + "group by u.age having u.age > :age ";
       TypedQuery<Object[]> query = entityManager.createQuery(jpql, Object[].class);
       List<Object[]> list = query.setParameter("age", 22).getResultList();
       list.forEach(o -> System.out.println(Arrays.toString(o)));
   }
   

7. 左外连接（与hibernate一样）：不加fetch返回的是对象数组

   /**
    * 左外连接
    */
   @Test
   public void relationalSelectTest_LeftOuterJoin() {
       String jpql = "from User u left outer join fetch u.orders where u.age > :age";
       TypedQuery<User> query = entityManager.createQuery(jpql, User.class);
       List<User> list = query.setParameter("age", 22).getResultList();
       list.forEach(System.out::println);
       for (User user : list) {
           System.out.println(user.getOrders());
       }
   }
   

8. 子查询

   /**
    * 子查询
    */
   @Test
   public void relationalSelectTest_subSelect() {
       String jpql = "from Order o where o.user in (from User u where u.age = :age)";
       TypedQuery<Order> query = entityManager.createQuery(jpql, Order.class);
       List<Order> list = query.setParameter("age", 22).getResultList();
       list.forEach(System.out::println);
   }
   

9. 更新

   /**
    * update
    */
   @Test
   public void updateTest() {
       String jpql = "update User u set u.lastName = :lastName where u.id = :id";
       Query query = entityManager.createQuery(jpql);
       query.setParameter("lastName", "john");
       query.setParameter("id", 1L);
       query.executeUpdate();
   }
   

10. 删除

    /**
     * delete
     */
    @Test
    public void deleteTest() {
        String jpql = "delete Order o where o.id = :id";
        Query query = entityManager.createQuery(jpql);
        query.setParameter("id", 1L);
        query.executeUpdate();
    }
    

2. 内建函数

1. concat(String s1, String s2)：字符串合并/连接
2. substring(String s, int length)：取字符串
3. trim([leading|trailing|both,] [char c,] String s)：从字符串去掉首/尾指定的字符或空格
4. lower(String s)：将字符串转换为小写
5. upper(String s)：将字符串转换为大写
6. length(String s)：求字符串长度
7. locate(String s1, String s2 [,int start])：从第一个字符串查找第二个字符串出现的位置。未找到返回0
8. 算数：abs()、mod、sqrt、size【用于求集合的元素个数】
9. 日期：current_date、current_time、current_timestamp：不需要参数

3. 其他方法

1. 分页相关
   • Query setFirstResult(int startPosition)：设置第一条记录的索引
   • Query setMaxResults(int maxResult)：设置查询几条

---

本文代码：点击此处