一、HQL查询
HQL的应用最为广泛,是最为常用的,跟SQL语句差不多,区别是:
SQL基于表和字段查询,HQL基于类对象和属性查询
HQL的功能大概有:
属性查询、分组查询、动态实例查询(比较具备特点)、分页查询、条件查询、连接查询、子查询
属性查询
属性查询要注意的地方是,查询的结果会封装为一个Object数组
Session session = HibernateSessionFactory.getSession();
//这里的title和author是Books的属性
String hql = "select b.title, b.author from Books as b";
Query query = session.createQuery(hql);
List<Object> list = query.list();
Iterator<Object> iterator = list.iterator();
while(iterator.hasNext()){
Object[] object = (Object[]) iterator.next();
System.out.println(object[0] + " " + object[1]);
}
分组查询
分组查询和普通查询的操作没什么区别,查询语句也和sql语句一样:
select b.title, b.author from Books as b group by b.author
动态实例查询
把查询的结果映射到一个新的实体对象中
Session session = HibernateSessionFactory.getSession();
String hql = "select new Books(title, author) from Books";
Query query = session.createQuery(hql);
List<Books> list = query.list();
Iterator<Books> iterator = list.iterator();
while(iterator.hasNext()){
Books book = iterator.next();
System.out.println(book.getTitle() + " " +
book.getAuthor());
}
这里要注意,hql语句中,new Books(title, author) 在实体类中必须有这个构造方法,否则会报Unable to locate appropriate constructor错误
分页查询
分页查询主要是用到了Query接口的两个方法:
setFirstResult(int firstResult) 设定从第几个对象开始查询
setMaxResult(int maxResult) 设定一次返回多少个对象
Session session = HibernateSessionFactory.getSession();
String hql = "select new Books(title, author) from Books";
Query query = session.createQuery(hql);
query.setFirstResult(0);
query.setMaxResults(3);
List<Books> list = query.list();
Iterator<Books> iterator = list.iterator();
while(iterator.hasNext()){
Books book = iterator.next();
System.out.println(book.getTitle() + " " +
book.getAuthor());
}
其它查询
条件查询、连接查询、子查询和SQL语句差不多,只要谨记HQL是对类和属性进行查询
二、QBC查询
QBC主要用到了Criteria接口、一些工具类(Restrictions类、Order类、Projection类),功能没有HQL强大,也较为常用,但是不支持子查询。
Restrictions类:用于为criteria对象设置查询条件(多用于组合查询等)
Order类:用于为criteria对象设置排序方式(多用于排序查询)
Projection类:用于为查询结果进行聚合函数操作和分组操作(多用于分组查询和内置函数avg,count,max,min,sum)
Restrictions类的常用方法:
方法 | 作用 |
---|---|
Restrictions.eq | 等于 |
Restrictions.allEq | 使用Map,Key/Valu进行多个等于的比对 |
Restrictions.gt | 大于 |
Restrictions.ge | 大于等于 |
Restrictions.lt | 小于 |
Restrictions.le | 小于等于 |
Restrictions.between | 对应SQL的between |
Restrictions.like | 对应SQL的like |
Restrictions.in | 对应SQL的in |
Restrictions.and | and关系 |
Restrictions.or | or关系 |
Restrictions.sqlRestriction | SQL限定查询 |
Order类的常用方法:
方法 | 作用 |
---|---|
Order.asc | 升序 |
Order.desc | 降序 |
Projections类的常用方法
方法 | 作用 |
---|---|
Projections.avg | 求平均值 |
Projections.count | 统计某属性的数量 |
Projections.countDistinct | 统计某属性不同值的数量 |
Projections.groupProperty | 指定某个属性为分组属性 |
Projections.max | 求最大值 |
Projections.min | 求最小值 |
Projections.projectionList | 创建一个ProjectionList对象 |
Projections.rowCount | 查询结果集中的记录条数 |
Projections.sum | 求某属性的合计 |
使用步骤:
- 获得Session对象
- 创建Criteria对象
- 使用Restrictions、Order、Projection对象编写查询条件
- 把查询条件假如到Criteria对象
- 执行查询
Order类排序查询
Session session = HibernateSessionFactory.getSession();
Criteria criteria = session.createCriteria(Books.class);
criteria.addOrder(Order.desc("id"));
List<Books> list = criteria.list();
Iterator<Books> iterator = list.iterator();
while(iterator.hasNext()){
Books book = iterator.next();
System.out.println(book.getTitle());
}
Projection类分组查询
Session session = HibernateSessionFactory.getSession();
Criteria criteria = session.createCriteria(Books.class);
ProjectionList pList = Projections.projectionList();
//分组查询
pList.add(Projections.groupProperty("author"));
//每一组的数量
pList.add(Projections.rowCount());
criteria.setProjection(pList);
List list = criteria.list();
Iterator iterator = list.iterator();
while(iterator.hasNext()){
Object[] objects = (Object[]) iterator.next();
System.out.println(objects[0] + " " + objects[1]);
}
Restrictions对象设置查询条件
Session session = HibernateSessionFactory.getSession();
Criteria criteria = session.createCriteria(Books.class);
criteria.add(Restrictions.like("title", "Java", MatchMode.ANYWHERE));//模糊查询
List<Books> list = criteria.list();
Iterator<Books> iterator = list.iterator();
while(iterator.hasNext()){
Books next = iterator.next();
System.out.println(next.getTitle());
}
三、QBE查询
QBE称为举例查询,功能最弱,在某些特定的场合有用,例如在web层中,经常会把请求参数封装成一个vo,然后传递vo给dao查询数据,这时QBE比较好用
Session session = HibernateSessionFactory.getSession();
Criteria criteria = session.createCriteria(Books.class);
Books book = new Books();
book.setTitle("Java");
Example example = Example.create(book);//设置匹配例子
example.enableLike(MatchMode.ANYWHERE);//设置匹配模式模糊匹配
criteria.add(example);
List<Books> list = criteria.list();
Iterator<Books> iterator = list.iterator();
while(iterator.hasNext()){
Books next = iterator.next();
System.out.println(next.getTitle());
}
四、离线查询(不常用,了解即可)
HQL,QBC,QBE查询都是在线查询,在线查询的意思就是需要通过session来创建查询,而离线查询不需要通过session来创建查询,,即创建查询时不需使用session,创建查询后,可以用任何一个session来执行查询
DetachedCriteria query = DetachedCriteria.forClass(Books.class).add(Property.forName("title").eq("Java入门"));
Session session = HibernateSessionFactory.getSession();
List<Books> list = query.getExecutableCriteria(session).list();
Iterator<Books> iterator = list.iterator();
while(iterator.hasNext()){
Books next = iterator.next();
System.out.println(next.getTitle());
}