Hql数据查询基础

1、了解HQL定义以及HQL语句形式

HQL定义

1）、Hibernate Query  Language ，Hibernate查询语言

2）、HQL是面向对象的查询语言

HQL 映射配置的持久化类及其属性

   SQL 数据库表

3）、HQL提供了丰富灵活的查询特性，Hibernate官方推荐查询方式

HQL语句形式

          select..

from...

where...

group by...

having....

order by... 

初学HQL注意的问题

1)、HQL是面向对象的查询语言，对JAVA类与属性大小写敏感

2)、HQL对关键字不区分大小写（也就是上面的HQL语句形式下面的关键字），但是习惯小写

准备查询

1）、org.hibernate.Query接口

①、Query接口定义有执行查询的方法

②、Query接口支持方法链编程风格，使得程序代码更为简洁

查询参数的动态设置，特别是多个参数的设置

2）、Query实例的创建

①、Session的createQuery()方法创建Query实例

②、createQuery方法包含一个HQL语句参数，createQuery(hql)

3）、执行查询

①、Query接口的list()方法执行HQL查询

②、list()方法返回结果数据类型为java.util.List，List集合中存放符合查询条件的持久化对象

测试例子:

@Testpublic void testStudent(){Session session=HibernateUtil.getSession();String hql="from Student";Query query=session.createQuery(hql);List<Student> students=query.list();for(Student student:students){System.out.println(student);}HibernateUtil.closeSession(session);}

运行结果：

Hibernate:     select        student0_.sid as sid1_0_,        student0_.birthday as birthday2_0_,        student0_.gender as gender3_0_,        student0_.major as major4_0_,        student0_.name as name5_0_     from        Student student0_Hibernate:     select        teachers0_.sid as sid1_0_1_,        teachers0_.tid as tid2_2_1_,        teacher1_.tid as tid1_1_0_,        teacher1_.tname as tname2_1_0_     from        teachers_students teachers0_     inner join        Teacher teacher1_             on teachers0_.tid=teacher1_.tid     where        teachers0_.sid=?Student [sid=1, name=lyh, gender=男, birthday=2017-02-09 00:03:24.0, major=软件工程, teachers=[Teacher [tid=002, tname=xqh], Teacher [tid=001, tname=txr]]]Hibernate:     select        teachers0_.sid as sid1_0_1_,        teachers0_.tid as tid2_2_1_,        teacher1_.tid as tid1_1_0_,        teacher1_.tname as tname2_1_0_     from        teachers_students teachers0_     inner join        Teacher teacher1_             on teachers0_.tid=teacher1_.tid     where        teachers0_.sid=?Student [sid=2, name=pha, gender=男, birthday=2017-02-09 00:03:24.0, major=软件工程, teachers=[Teacher [tid=002, tname=xqh], Teacher [tid=001, tname=txr]]]

2、掌握Query对象的使用

from子句

1）、HQL语句最简形式

2）、from指定了HQL语句查询主体——持久化类及其属性

from子句中持久化类的引用

不需要引入持久化类的全限定名，直接引入类名

如Student的权限定名为 ：com.txr.entity.Student

而查询只需要 from Student

而直接用类名即可查询是因为Hibernate的auto-import(自动引入)缺省情况根据Hibernate映射配置自动导入

from子句中别名的应用

1）、为被查询的类指定别名 用as(也可以省略)

2）、在HQL语句其他部分通过别名引用该类

3）、别名命名习惯  一般别名与持久化类名称相同 如String hql=“from Student as student”;

选择——select子句

1）、以Object[]形式返回选择的属性

select子句中未指定返回数据类型，默认为Object[]

例子：

@Testpublic void testSelectClauseObject(){Session session=HibernateUtil.getSession();String hql ="select s.sid,s.name from Student s";Query query=session.createQuery(hql);List<Object[]>list = query.list();for(Object[] objs:list){System.out.println("sid : "+objs[0]);System.out.println("sname : "+objs[1]);}}

运行结果：

Hibernate:     select        student0_.sid as col_0_0_,        student0_.name as col_1_0_     from        Student student0_sid : 1sname : lyhsid : 2sname : pha

注意：但是当你只查询一个字段时会报如下错误

@Testpublic void testSelectClauseObject(){Session session=HibernateUtil.getSession();String hql ="select s.sid from Student s";Query query=session.createQuery(hql);List<Object[]>list = query.list();for(Object[] objs:list){System.out.println("sid : "+objs[0]);//System.out.println("sname : "+objs[1]);}}

错误报告如下：

hql语言执行如下：

Hibernate:     select        student0_.sid as col_0_0_     from        Student student0_

这是因为如果查询的是一个字段那么返回的查询结果就不是一个数组而是一个对象，这就要求你将Object[]改为Object才行

改完后的运行结果为：

Hibernate:     select        student0_.sid as col_0_0_     from        Student student0_sid : 1sid : 2

2）、以List形式返回选择的属性

①、select子句中使用new list 指定

例子：

@Testpublic void testSelectClauseList(){Session session =HibernateUtil.getSession();String hql = "select new list(s.sid,s.name )from Student s";Query query=session.createQuery(hql);List<List> lists=query.list();for(List list:lists){System.out.println("sid :"+list.get(0));System.out.println("name : "+list.get(1));}}

运行结果;

Hibernate:     select        student0_.sid as col_0_0_,        student0_.name as col_1_0_     from        Student student0_sid :1name : lyhsid :2name : pha

List 、Object []、Map都是Hibernate提供的查询返回形式，只是看你习惯属于

3）、以map形式返回选择的属性

①、select子句中使用new map指定

②、key值为索引值，字符串类型

@Testpublic void testSelectClauseMap(){Session session=HibernateUtil.getSession();String hql="select new map(s.sid,s.name) from Student s";Query query=session.createQuery(hql);List<Map> maps=query.list();for(Map map:maps){System.out.println("sid : "+map.get("0"));System.out.println("name : "+map.get("1"));}HibernateUtil.closeSession(session);}

运行结果：

Hibernate:     select        student0_.sid as col_0_0_,        student0_.name as col_1_0_     from        Student student0_sid : 1name : lyhsid : 2name : pha

除了用序数进行获取也可用别名来获取：

String hql="select new map(s.sid as sid,s.name as name) from Student s";

那么后面就可以改为

map.get("sid");map.get("name");

4）、以自定义类型返回选择的属性

①、持久化类中定义对应的构造器

②、select子句中调用定义的构造器

首先在Student类中定义构造器

public Student(int sid,String sname){this.sid=sid;this.name=name;}

然后进行测试方法：

@Testpublic void testSelectCLauseSelf(){String hql="select new Student(s.sid,s.name) from Student s";Query query=session.createQuery(hql);List<Student> students=query.list();for(Student s:students){System.out.println("sid : "+s.getSid());System.out.println("name : "+s.getName());}}

运行结果：

Hibernate:     select        student0_.sid as col_0_0_,        student0_.name as col_1_0_     from        Student student0_sid : 1name : lyhsid : 2name : pha

注意：定义了自定义构造器还需要无参构造器

这是因为在我们的HQL语句中当指定了我们的查询目标持久化类，会用我们指定的持久化类来进行我们的数据创建以及封装，但是如果没有指定相应的构造器的话Hibernate会调用默认的构造器

5）、获取独特的结果——distinct关键字

1、使用distinct关键字去除查询结果中的重复元素

测试：

@Testpublic void testDistinct(){String hql="select distinct s.major from Student s";Query query =session.createQuery(hql);List<Object> list=query.list();for(Object ob:list){System.out.println(ob);}}

结果：

Hibernate:     select        distinct student0_.major as col_0_0_     from        Student student0_软件工程

限制——where子句

1、比较运算

1）.=、<>、<、>、>=、<=

HQL——————SQL

x = null    ——————x is null

x <> null —————— x is not null

测试：

@Testpublic void testWhere1(){String hql = "from Student s where s.sid>1";Query query=session.createQuery(hql);List<Student> list=query.list();for(Student s:list){System.out.println(s.getSid());}}

测试结果：

Hibernate:     select        student0_.sid as sid1_0_,        student0_.birthday as birthday2_0_,        student0_.gender as gender3_0_,        student0_.major as major4_0_,        student0_.name as name5_0_     from        Student student0_     where        student0_.sid>12

2）.null值判断——is[not] null

测试;

@Testpublic void testNull(){String hql = "from Student s where s.birthday is null";Query query=session.createQuery(hql);List<Student> list=query.list();for(Student s:list){System.out.println("name : "+s.getName());System.out.println("birthday : "+s.getBirthday());}}

测试结果：

Hibernate:     select        student0_.sid as sid1_0_,        student0_.birthday as birthday2_0_,        student0_.gender as gender3_0_,        student0_.major as major4_0_,        student0_.name as name5_0_     from        Student student0_     where        student0_.birthday is nullname : xqhbirthday : null

而如果将hql语句中is null换位 = null查询结果一样。

改为is not null则结果为，它等同于<>null

Hibernate:     select        student0_.sid as sid1_0_,        student0_.birthday as birthday2_0_,        student0_.gender as gender3_0_,        student0_.major as major4_0_,        student0_.name as name5_0_     from        Student student0_     where        student0_.birthday is not nullname : lyhbirthday : 2017-02-09 00:03:24.0name : phabirthday : 2017-02-09 00:03:24.0

2、范围运算

即属性值在给定的范围之内

1）、[not] in(列表)【这个列表可以是候选列表也可以是一个子查询】

测试：

@Testpublic void testWhere2(){String hql="from Student s where s.sid in(2,3)";Query query=session.createQuery(hql);List<Student> list=query.list();for(Student s :list){System.out.println("sid : "+s.getSid());System.out.println("name : "+s.getName());System.out.println("===============");}}

测试结果：

@Testpublic void testWhere2(){String hql="from Student s where s.sid in(2,3)";Query query=session.createQuery(hql);List<Student> list=query.list();for(Student s :list){System.out.println("sid : "+s.getSid());System.out.println("name : "+s.getName());System.out.println("===============");}}

当然也可以用not in ()进行取反

2）[not] between 值1 and 值2

   

测试：

@Testpublic void testWhere2(){String hql="from Student s where s.sid between 2 and 4";Query query=session.createQuery(hql);List<Student> list=query.list();for(Student s :list){System.out.println("sid : "+s.getSid());System.out.println("name : "+s.getName());System.out.println("===============");}}

测试结果：

Hibernate:     select        student0_.sid as sid1_0_,        student0_.birthday as birthday2_0_,        student0_.gender as gender3_0_,        student0_.major as major4_0_,        student0_.name as name5_0_     from        Student student0_     where        student0_.sid between 2 and 4sid : 2name : pha===============sid : 3name : xqh===============

也可以用not between XX and XX来查询不在XX区间的数据

3、字符串模式匹配

1）、like关键字

2）、通配符%、_    【%:任意字符，_一个字符】

测试：

@Testpublic void testLike(){String hql="from Student s where s.name like '%q%'";Query query =session.createQuery(hql);List<Student> list=query.list();for(Student s : list){System.out.println("sid : "+s.getSid());System.out.println("name : "+s.getName());System.out.println("============");}}

测试结果：

Hibernate:     select        student0_.sid as sid1_0_,        student0_.birthday as birthday2_0_,        student0_.gender as gender3_0_,        student0_.major as major4_0_,        student0_.name as name5_0_     from        Student student0_     where        student0_.name like '%q%'sid : 3name : xqh============

注意 ：①、在HQL语句中from Student中Student一定要与类名大小写一致否则会报如下错误

②、hql语句中 like字段后面字符串一定要加单引号否则会报如下错误

String hql="from student s where s.name like'%q%'";

    4、逻辑运算

1）and(逻辑与)【true and true =>true，false and true =>false】、or(逻辑或)【false or false =>false，false or true =>true】

2）not(逻辑非)【取反】

测试 and：

@Testpublic void testWhere3(){String hql="from Student where sid=3 and name like '%h%'";Query query=session.createQuery(hql);List<Student> list=query.list();for(Student s : list){System.out.println("sid : "+s.getSid());System.out.println("name : "+s.getName());System.out.println("============");}}

测试结果：

Hibernate:     select        student0_.sid as sid1_0_,        student0_.birthday as birthday2_0_,        student0_.gender as gender3_0_,        student0_.major as major4_0_,        student0_.name as name5_0_     from        Student student0_     where        student0_.sid=3         and (            student0_.name like '%h%'        )sid : 3name : xqh============

测试or

@Testpublic void testWhere3(){String hql="from Student where sid=3 or name like '%h%'";Query query=session.createQuery(hql);List<Student> list=query.list();for(Student s : list){System.out.println("sid : "+s.getSid());System.out.println("name : "+s.getName());System.out.println("============");}}

测试结果

Hibernate:     select        student0_.sid as sid1_0_,        student0_.birthday as birthday2_0_,        student0_.gender as gender3_0_,        student0_.major as major4_0_,        student0_.name as name5_0_     from        Student student0_     where        student0_.sid=3         or student0_.name like '%h%'sid : 1name : lyh============sid : 2name : pha============sid : 3name : xqh============

5、集合运算

1）、is [not] empty 集合[不]为空，不包含任何元素

2）、member of 元素属于集合

6、在HQL中使用+-*/运算符

1）、HQL语句中也可以使用+-*/四则运算

测试：

@Testpublic void testWhere5(){String hql ="from Student where sid*5>9";Query query=session.createQuery(hql);List<Student> list=query.list();for(Student s : list){System.out.println("sid : "+s.getSid());System.out.println("name : "+s.getName());System.out.println("birthday : "+s.getBirthday());System.out.println("============");}}

测试结果

Hibernate:     select        student0_.sid as sid1_0_,        student0_.birthday as birthday2_0_,        student0_.gender as gender3_0_,        student0_.major as major4_0_,        student0_.name as name5_0_     from        Student student0_     where        student0_.sid*5>9sid : 2name : phabirthday : 2017-02-09 00:03:24.0============sid : 3name : xqhbirthday : null============

2）、四则运算可以在where子句和select子句中使用

7、查询单个对象（uniqueResult方法）

1）、Query接口的uniqueResult方法 返回的是一个实例对象而不再是一个List集合

2）、where子句条件设置（当where条件设置不当查询出的结果多余一个则程序报错终止运行）

测试:

@Testpublic void testWhere6(){String hql ="from Student where sid=3";Query query=session.createQuery(hql);Student s=(Student)query.uniqueResult();System.out.println("sid : "+s.getSid());System.out.println("name : "+s.getName());System.out.println("birthday : "+s.getBirthday());}

测试结果

Hibernate:     select        student0_.sid as sid1_0_,        student0_.birthday as birthday2_0_,        student0_.gender as gender3_0_,        student0_.major as major4_0_,        student0_.name as name5_0_     from        Student student0_     where        student0_.sid=3sid : 3name : xqhbirthday : null

而如果查询结果多于一个

测试：

@Testpublic void testWhere6(){String hql ="from Student where sid>1";Query query=session.createQuery(hql);Student s=(Student)query.uniqueResult();System.out.println("sid : "+s.getSid());System.out.println("name : "+s.getName());System.out.println("birthday : "+s.getBirthday());}

运行结果发送一条sql语句并报错：

Hibernate:     select        student0_.sid as sid1_0_,        student0_.birthday as birthday2_0_,        student0_.gender as gender3_0_,        student0_.major as major4_0_,        student0_.name as name5_0_     from        Student student0_     where        student0_.sid>1

排序——order by 子句

1）、使用order by 子句对查询结果排序

1、升序排序 asc  2、降序排序 desc

测试：

@Testpublic void testOrderby(){String hql ="from Student order by sid desc";Query query=session.createQuery(hql);List<Student> list=query.list();for(Student s : list){System.out.println("sid : "+s.getSid());System.out.println("name : "+s.getName());System.out.println("birthday : "+s.getBirthday());System.out.println("============");}}

测试结果：

Hibernate:     select        student0_.sid as sid1_0_,        student0_.birthday as birthday2_0_,        student0_.gender as gender3_0_,        student0_.major as major4_0_,        student0_.name as name5_0_     from        Student student0_     order by        student0_.sid descsid : 3name : xqhbirthday : null============sid : 2name : phabirthday : 2017-02-09 00:03:24.0============sid : 1name : lyhbirthday : 2017-02-09 00:03:24.0============

2）、设置多个规则排序查询，order by后面的多个规则用,隔开

测试

@Testpublic void testOrderby(){String hql ="from Student order by birthday asc,sid desc";Query query=session.createQuery(hql);List<Student> list=query.list();for(Student s : list){System.out.println("sid : "+s.getSid());System.out.println("name : "+s.getName());System.out.println("birthday : "+s.getBirthday());System.out.println("============");}}

测试结果

Hibernate:     select        student0_.sid as sid1_0_,        student0_.birthday as birthday2_0_,        student0_.gender as gender3_0_,        student0_.major as major4_0_,        student0_.name as name5_0_     from        Student student0_     order by        student0_.birthday asc,        student0_.sid descsid : 3name : xqhbirthday : 2017-02-08 00:33:08.0============sid : 2name : phabirthday : 2017-02-09 00:03:24.0============sid : 1name : lyhbirthday : 2017-02-09 00:03:24.0============