mynotes/hibernate/201102-hibernate基础查询语句使用.md

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

   1. 单对象返回多字段

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

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

   // 必须存在 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();
   

   1. 单对象返回对象

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

3. 单对象返回多字段，多字段构建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=社会性动物}
   

   1. 命名参数

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

4. 连接查询

   // 注意：连接查询中的 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();
   

   1. 聚合函数

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

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

5. 分页

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

   1. 排序

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

6. 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 条件
   1. 比较运算符： 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 逻辑

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

   1. 直接使用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} 表示类的别名

3. 分页

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

   1. 排序

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

4. 聚合函数

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

   注意返回类型

   1. 连接查询

   // 查询心理类的、出版时间大于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设定有效。

5. 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

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