[hibernate最新文档](https://docs.jboss.org/hibernate/orm/5.2/userguide/html_single/Hibernate_User_Guide.html)
[项目仓库地址](https://gitee.com/anyway2025/guide)项目201102-hibernateSelect
## 数据准备
```sql
-- 创建数据表
drop table if exists t_book;
create table t_book(
    id int(11) primary key auto_increment,
    name varchar(100) comment '名称',
    author varchar(50) comment '作者',
    publish_date datetime comment '出版日期',
    price decimal(10, 2) comment '价格',
    catalog_id int(11) comment '分类id'
) comment '书籍表';

drop table if exists t_book_catalog;
create table t_book_catalog(
    id int(11) primary key auto_increment,
    name varchar(100) comment '名称'
) comment '书籍分类表';

-- 插入数据
insert into t_book_catalog(name) values 
('经典'), ('心理'), ('科幻');

insert into t_book(name, author, publish_date, price, catalog_id) values 
('三体', '刘慈欣', '2009-11-23', 19.3, 3),
('流浪地球', '刘慈欣', '2007-01-08', 15, 3),
('西游记', '吴承恩', '1630-01-13', 5, 1),
('象与骑象人', '乔纳森·海特', '2012-7-4', 39.9, 2),
('社会性动物', '艾略特·阿伦森', '1960-5-4', 32.9, 2);
```

**实体类Book**

```java
@Entity
@Table(name = "t_book", schema = "test", catalog = "")
public class Book {
    @Id
    @Column(name = "id")
    private int id;

    @Basic
    @Column(name = "name")
    private String name;

    @Basic
    @Column(name = "author")
    private String author;

    @Basic
    @Column(name = "publish_date")
    private Timestamp publishDate;

    @Basic
    @Column(name = "price")
    private BigDecimal price;

    @Transient
    private Integer catalogId;

    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "catalog_id", referencedColumnName = "id")
    private BookCatalog bookCatalog;
    
    ...
}
```

**实体类BookCatalog:**

```java
@Entity
@Table(name = "t_book_catalog", schema = "test", catalog = "")
public class BookCatalog {
    @Id
    @Column(name = "id")
    private int id;

    @Basic
    @Column(name = "name")
    private String name;

    @OneToMany(mappedBy = "bookCatalog", fetch = FetchType.EAGER)
    private List<Book> bookList;
    
    ...
}
```


## Query 和 Criteria 对比
HQL是 Hibernate Query Language 的缩写。

|                   | HQL                                       | SQL                       |
| ----------------- | -------------------------------- | --------------------- |
| 目标              | 面向对象                                  | 面向数据库表                   |
| 基本格式          | from + 类名 + 别名 + where + 对象的属性   | from + 表名 + where + 表中字段 |
| 是否区分大小写    | 是，关键字不区分                          | 否                             |
| 下标              | 下标从0开始（hibernate5后不再支持占位符） | 下标从1开始                    |
| 是否支持:命名参数 | 支持                                      | 不支持                         |


## Query使用
1. 单对象返回单字段

    ```java
    String hql = "SELECT b.name as bookName FROM Book b";
    Query query = this.session.createQuery(hql);
    List<String> bookNameList = query.list();
    ```

2. 单对象返回多字段

    ```java
    String hql = "SELECT b.id, b.name as bookName FROM Book b";
    Query query = this.session.createQuery(hql);
    List<Object[]> bookInfoList = query.list();
    ```

3. 单对象返回多字段，多字段构建返回对象

    ```java
    // 必须存在 public Book(Integer id, String name) 这样的构造函数
    String hql = "SELECT new Book(b.id, b.name) FROM Book b";
    Query query = session.createQuery(hql);
    List<Book> bookList = query.list();
    ```

4. 单对象返回对象

    ```java
    String hql = "FROM Book b";
    Query query = session.createQuery(hql);
    List<Book> bookList = query.list();
    ```

5. 单对象返回多字段，多字段构建Map

	 ```java
	String hql = "SELECT new Map(b.id,b.name) FROM Book b";
	Query query = session.createQuery(hql);
	List<Map> list = query.list();
	for (Map b : list) {
		System.out.println(b.toString());
	}
	 ```
    输出结果：
    ```
    {0=1, 1=三体}
	{0=2, 1=流浪地球}
	{0=3, 1=西游记}
	{0=4, 1=象与骑象人}
	{0=5, 1=社会性动物}
	```

6. 命名参数

     ```java
     // 1. 基础类型
     String hql = "FROM Book WHERE id = :bookId";
     Query query = session.createQuery(hql);
     query.setParameter("bookId", 1);
     Book b = (Book) query.getSingleResult();
     
     // 2. 数组
     String hql = "FROM Book WHERE id IN (:bookIds)";
     Query query = session.createQuery(hql);
     query.setParameterList("bookIds", new Integer[]{1, 3, 5});
     List<Book> bookList = query.list();
     // 3. 列表
     String hql = "FROM Book WHERE id IN (:bookIds)";
     Query query = session.createQuery(hql);
     List<Integer> bookIdList = new ArrayList<>();
     bookIdList.add(1);
     bookIdList.add(3);
     bookIdList.add(5);
     query.setParameterList("bookIds", bookIdList);
     List<Book> bookList = query.list();
     ```
	
	> query#getSingleResult() 和  query#uniqueResult() 的区别：
	> 两者都返回一条结果；不同的是，在查询结果为空时前者抛出异常，后者则返回null

7. 连接查询

	 ```java
	// 注意：连接查询中的 bookCatalog 是关联对象
	String hql1 = "SELECT b.id, b.name, bc.name as catalog_name FROM Book b JOIN BookCatalog bc ON bc.id=b.bookCatalog";
	String hql2 = "SELECT b.id, b.name, bc.name as catalog_name FROM Book b, BookCatalog bc WHERE bc.id=b.bookCatalog";
	Query query = session.createQuery(hql2);
	List<Object[]> bookInfoList = query.list();
	 ```

8. 聚合函数

     ```java
     String hql = "SELECT COUNT(*) FROM Book";
     Query query = session.createQuery(hql);
     Long count = (Long) query.getSingleResult();
     ```
	> 还有其他聚合函数 sum、avg、max、min、count

9. 分页

     ```java
     // 页码从1开始
     int pageNo = 3;
     int pageSize = 2;
     //结果集第一条记录，从0开始
     int offset = (pageNo-1) * pageSize;
     
     String hql = "FROM Book";
     Query query = session.createQuery(hql);
     query.setFirstResult(offset);
     query.setMaxResults(pageSize);
     List<Book> bookList = query.list();
     ```

10. 排序

      ```java
      String hql = "FROM Book ORDER BY price DESC";
      Query query = session.createQuery(hql);
      List<Book> bookList = query.list();
      ```

11. session.createSqlQuery

      ```java
      String sql = "SELECT name FROM t_book";
      Query query = this.session.createSQLQuery(sql);
      List<String> list = query.list();
      
      String sql = "SELECT * FROM t_book";
      Query query = this.session.createSQLQuery(sql);
      List<Object[]> list = query.list();
      ```

      **createSqlQuery(sql) 的返回值类型 和  createQuery(hql) 的是相同的，所以Query的方法也能用。比如：分页、命名参数 **

      ```java
      String sql = "SELECT book.* FROM t_book book WHERE book.price>:price";
      Query query = this.session.createSQLQuery(sql);
      query.setParameter("price", "10");
      query.setFirstResult(0);
      query.setMaxResults(2);
      List<Object[]> bookInfoList = query.list();
      ```

      

## Criteria使用
Hibernate5.2之后，Criteria标准化查询差不多都转到使用JPA包了，因此像session.createCriteria()等方法都提示了已过时。[Hibernate5.2文档](http://docs.jboss.org/hibernate/orm/5.2/userguide/html_single/Hibernate_User_Guide.html#criteria-typedquery-entity)** [Criteria 5.2前后API和使用](https://blog.csdn.net/baidu_27414099/article/details/104440300)

> 参考： https://www.cnblogs.com/lukelook/p/9692429.html
> QBC：Query By Criteria
> Criteria是一种比HQL更面向对象的查询方式

1. 最简单的例子：

    ```java
    Criteria criteria = session.createCriteria(Book.class);
    List<Book> bookList = criteria.list();
    ```

	**主要类：**
	* Criteria
	* Criterion 规则
	* Order 排序
	* Restrictions 条件

2. 比较运算符：
	```java 
	Criteria criteria = this.session.createCriteria(Book.class);
    //Criterion criterion = Restrictions.eq("id", 3);
    //criteria.add(criterion);
    criteria.add(Restrictions.gt("price", new BigDecimal(30)));
    List<Book> bookList = criteria.list();
	```

	更多运算符： 
	| SQL运算符           | Restrictions 方法                 | 说明                   |
	| ------------------- | ------------------------- | ------------------- |
	| =                   | Restrictions.eq()                         | 等于                   |
	| <>                  | Restrictions.not(Exprission.eq())         | 不等于                 |
	| >                   | Restrictions.gt()                         | 大于                   |
	| >=                  | Restrictions.ge()                         | 大于等于               |
	| <                   | Restrictions.lt()                         | 小于                   |
	| <=                  | Restrictions.le()                         | 小于等于               |
	| is null             | Restrictions.isnull()                     | 等于空值               |
	| is not null         | Restrictions.isNotNull()                  | 非空值                 |
	| like                | Restrictions.like()                       | 字符串模式匹配         |
	| and                 | Restrictions.and()                        | 逻辑与                 |
	| and                 | Restrictions.conjunction()                | 逻辑与                 |
	| or                  | Restrictions.or()                         | 逻辑或                 |
	| or                  | Restrictions.disjunction()                | 逻辑或                 |
	| not                 | Restrictions.not()                        | 逻辑非                 |
	| in(列表)            | Restrictions.in()                         | 等于列表中的某一个值   |
	| ont in(列表)        | Restrictions.not(Restrictions.in())       | 不等于列表中任意一个值 |
	| between x and y     | Restrictions.between()                    | 闭区间xy中的任意值     |
	| not between x and y | Restrictions.not(Restrictions..between()) | 小于值X或者大于值y     |

2. and 和 or 逻辑

    ```java
    Criteria criteria = this.session.createCriteria(Book.class);
    Date minDate = new SimpleDateFormat("yyyy-MM-dd").parse("2008-10-23");
    Criterion gtPublishDateCr = Restrictions.gt("publishDate", minDate);
    Criterion eqAuthorCr = Restrictions.eq("author", "刘慈欣");
    LogicalExpression andExp = Restrictions.and(gtPublishDateCr, eqAuthorCr);
    criteria.add(andExp);
    List<Book> bookList = criteria.list();
    ```

3. 直接使用SQL

    ```java
    Criteria criteria = this.session.createCriteria(Book.class);
    Criterion gtPriceCr = Restrictions.sqlRestriction("{alias}.price > ?", new BigDecimal(20), StandardBasicTypes.BIG_DECIMAL);
    criteria.add(gtPriceCr);
    List<Book> bookList = criteria.list();
    ```

    {alias} 表示类的别名

4. 分页

    ```java
    // 页码从1开始
    int pageNo = 1;
    int pageSize = 2;
    //结果集第一条记录，从0开始
    int offset = (pageNo-1) * pageSize;
    Criteria cr = session.createCriteria(Book.class);
    cr.setFirstResult(offset);
    cr.setMaxResults(pageSize);
    List<Book> bookList = cr.list();
    ```

5. 排序

    ```java
    Criteria cr = session.createCriteria(Book.class);
    cr.add(Restrictions.like("author", "刘慈欣"));
    cr.addOrder(Order.asc("publishDate"));
    List<Book> bookList = cr.list();
    ```

6. 聚合函数

    ```java
    Criteria cr = session.createCriteria(Book.class);
    //cr.setProjection(Projections.rowCount());
    //cr.setProjection(Projections.sum("price"));
    //cr.setProjection(Projections.countDistinct("author"));
    //cr.setProjection(Projections.max("price"));
    //cr.setProjection(Projections.min("price"));
    cr.setProjection(Projections.avg("price"));
    Double avgPrice = (Double) cr.uniqueResult();
    ```

	> 注意返回类型

7. 连接查询

    ```java
    // 查询心理类的、出版时间大于2010年的书籍
    Criteria cr = session.createCriteria(Book.class);
    Date gtDate = new SimpleDateFormat("yyyy-MM-dd").parse("2010-01-01");
    cr.add(Restrictions.gt("publishDate", gtDate));
    cr.createCriteria("bookCatalog")
        .add(Restrictions.eq("name", "心理"));
    List<Book> bookList = cr.list();
    ```

    **使用createAlias()**

    ```java
    Date gtDate = new SimpleDateFormat("yyyy-MM-dd").parse("2010-01-01");
    //查询心理类的、出版时间大于2010年的书籍
    Criteria cr = session.createCriteria(Book.class, "b");
    cr.createAlias("bookCatalog", "bc");
    cr.add(Restrictions.gt("b.publishDate", gtDate));
    cr.add(Restrictions.eq("bc.name", "心理"));
    List<Book> bookList = cr.list();
    for (Book b : bookList) {
        System.out.println(b.toString());
    }
    ```

    **设置关联对象的加载策略**

    ```java
    //查询心理的书籍分类
    Criteria cr = session.createCriteria(BookCatalog.class);
    cr.setFetchMode("bookList", FetchMode.JOIN);
    cr.add(Restrictions.eq("name", "心理"));
    List<BookCatalog> bookCatalogList = cr.list();
    //使用Set接口的实现类HashSet来过滤重复的数据（HashSet中不会存储相同的值）
    HashSet<BookCatalog> bookCatalogSet = new HashSet<BookCatalog>(bookCatalogList);
    ```

	> `criteria.setFetchModel("propName", FetchMode.JOIN)` 等同于在关联对象上`@ManyToOne(fetch = FetchType.EAGER)`  或 `@OneToMany(fetch = FetchType.EAGER)`

	| 加载策略            | 作用                                                         |
	| ------------------- | ------------------------------------------------------------ |
	| FetchType.LAZY      | 懒加载，在访问关联对象的时候加载(即从数据库读入内存)         |
	| FetchType.EAGER     | 立刻加载，在查询主对象的时候同时加载关联对象                 |
	| FetchMode.JOIN      | 始终立刻加载，使用外连(outer join)查询的同时加载关联对象，忽略FetchType.LAZY设定 |
	| FetchMode.SELECT    | 默认懒加载(除非设定关联属性lazy=false)<br/>当访问每一个关联对象时加载该对象，会累计产生N+1条sql语句 |
	| FetchMode.SUBSELECT | 默认懒加载(除非设定关联属性lazy=false) <br/>在访问第一个关联对象时加载所有的关联对象。会累计产生两条sql语句。且FetchType设定有效。 |


8. Example

    ```java
    // Example类允许你通过一个给定实例 构建一个条件查询
    Book book = new Book();
    book.setAuthor("森");
    //注意：如何实体类中如果有int 或者doubole 类型的值而没有设置值，一定要加 excludeZeroes() 条件
    Example eqAuthorExample = Example.create(book).excludeZeroes().enableLike(MatchMode.ANYWHERE);
    // 查询
    Criteria cr = session.createCriteria(Book.class);
    cr.add(eqAuthorExample);
    List<Book> bookList = cr.list();
    ```

	>* Example#excludeZeroes() 排除等于0值的属性
	>* Example#excludeProperty("propName") 排除指定的属性
	>* Example#ignoreCase() 忽略大小写
	>* Example#enableLike() 开启like

10. Projections 投影

    ```java
    // Projections是 Projection 的实例工厂。我们通过调用 setProjection()应用投影到一个查询
    Criteria cr = session.createCriteria(Book.class);
    cr.setProjection(Projections.rowCount());
    cr.add(Restrictions.gt("price", new BigDecimal(20)));
    List list = cr.list();
    // 输出结果：[2]
    ```

    **多个统计投影:**

    ```java
    // 当执行多个统计投影时，会获取一个List，并且是一个Object类型的List，其中依次包含所有的统计投影结果。
    Criteria cr = session.createCriteria(Book.class);
    ProjectionList projectionList = Projections.projectionList();
    projectionList.add(Projections.rowCount());
    projectionList.add(Projections.avg("price"));
    projectionList.add(Projections.max("price"));
    projectionList.add(Projections.min("price"));
    projectionList.add(Projections.sum("price"));
    projectionList.add(Projections.count("author"));
    projectionList.add(Projections.countDistinct("author"));
    cr.setProjection(projectionList);
    List<Object[]> list = cr.list();
    for(Object[] arr : list) {
        System.out.println(Arrays.asList(arr));
    }
    // 输出结果：[5, 22.42, 39.90, 5.00, 112.10, 5, 4]
    ```

    **别名 alias :**

    ```java
    Criteria cr = session.createCriteria(Book.class);
    cr.setProjection(Projections.alias(Projections.groupProperty("author"), "aur"));
    cr.addOrder(Order.asc("aur"));
    List list = cr.list();
    System.out.println(list);
    
    Criteria cr = session.createCriteria(Book.class);
    cr.setProjection(Projections.groupProperty("author").as("aur"));
    cr.addOrder(Order.asc("aur"));
    List list = cr.list();
    System.out.println(list);
    
    //输出结果：[乔纳森·海特, 刘慈欣, 吴承恩, 艾略特·阿伦森]
    ```