hibernate基础查询语句使用

hibernate最新文档
项目仓库地址项目201102-hibernateSelect

数据准备

-- 创建数据表
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

@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:

@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. 单对象返回单字段

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

2. 单对象返回多字段

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

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

   // 必须存在 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. 单对象返回对象

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

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

   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. 命名参数

   // 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. 连接查询

   // 注意：连接查询中的 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. 聚合函数

   String hql = "SELECT COUNT(*) FROM Book";
   Query query = session.createQuery(hql);
   Long count = (Long) query.getSingleResult();
   

   还有其他聚合函数 sum、avg、max、min、count

9. 分页

   // 页码从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. 排序

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

11. session.createSqlQuery

    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的方法也能用。比如：分页、命名参数 **

    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文档** Criteria 5.2前后API和使用

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

1. 最简单的例子：

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

   主要类：

   • Criteria
   • Criterion 规则
   • Order 排序
   • Restrictions 条件

2. 比较运算符：

   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

3. and 和 or 逻辑

   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();
   

4. 直接使用SQL

   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} 表示类的别名

5. 分页

   // 页码从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();
   

6. 排序

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

7. 聚合函数

   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();
   

   注意返回类型

8. 连接查询

   // 查询心理类的、出版时间大于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()

   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());
   }
   

   设置关联对象的加载策略

   //查询心理的书籍分类
   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) 当访问每一个关联对象时加载该对象，会累计产生N+1条sql语句
   FetchMode.SUBSELECT	默认懒加载(除非设定关联属性lazy=false) 在访问第一个关联对象时加载所有的关联对象。会累计产生两条sql语句。且FetchType设定有效。

9. Example

   // 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 投影

    // 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]
    

    多个统计投影:

    // 当执行多个统计投影时，会获取一个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 :

    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);
    
    //输出结果：[乔纳森·海特, 刘慈欣, 吴承恩, 艾略特·阿伦森]