Hql数据查询基础

最新推荐文章于 2018-10-04 16:20:16 发布

不爱吃汤圆的汤圆坨坨

最新推荐文章于 2018-10-04 16:20:16 发布

阅读量414

点赞数

分类专栏： hibernate

本文链接：https://blog.csdn.net/wenwen360360/article/details/54934607

版权

hibernate 专栏收录该内容

10 篇文章 0 订阅

订阅专栏

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集合中存放符合查询条件的持久化对象

测试例子:

@Test
	public 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[]

例子：

@Test
	public 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 : 1
sname : lyh
sid : 2
sname : pha

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

@Test
	public 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 : 1
sid : 2

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

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

例子：

@Test
	public 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 :1
name : lyh
sid :2
name : pha

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

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

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

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

@Test
	public 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 : 1
name : lyh
sid : 2
name : 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;
	}

然后进行测试方法：

@Test
	public 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 : 1
name : lyh
sid : 2
name : pha

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

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

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

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

测试：

@Test
	public 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

测试：

@Test
	public 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>1
2

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

测试;

@Test
	public 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 null
name : xqh
birthday : 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 null
name : lyh
birthday : 2017-02-09 00:03:24.0
name : pha
birthday : 2017-02-09 00:03:24.0

2、范围运算

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

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

测试：

@Test
	public 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("===============");
		}
	}

测试结果：

@Test
	public 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

测试：

@Test
	public 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 4
sid : 2
name : pha
===============
sid : 3
name : xqh
===============

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

3、字符串模式匹配

1）、like关键字

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

测试：

@Test
	public 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 : 3
name : 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：

@Test
	public 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 : 3
name : xqh
============

测试or

@Test
	public 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 : 1
name : lyh
============
sid : 2
name : pha
============
sid : 3
name : xqh
============

5、集合运算

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

2）、member of 元素属于集合

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

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

测试：

@Test
	public 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>9
sid : 2
name : pha
birthday : 2017-02-09 00:03:24.0
============
sid : 3
name : xqh
birthday : null
============

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

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

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

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

测试:

@Test
	public 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=3
sid : 3
name : xqh
birthday : null

而如果查询结果多于一个

测试：

@Test
	public 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

测试：

@Test
	public 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 desc
sid : 3
name : xqh
birthday : null
============
sid : 2
name : pha
birthday : 2017-02-09 00:03:24.0
============
sid : 1
name : lyh
birthday : 2017-02-09 00:03:24.0
============

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

测试

@Test
	public 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 desc
sid : 3
name : xqh
birthday : 2017-02-08 00:33:08.0
============
sid : 2
name : pha
birthday : 2017-02-09 00:03:24.0
============
sid : 1
name : lyh
birthday : 2017-02-09 00:03:24.0
============