HQL

 

1.HQL:Hibernate Query Language Hibernate查询语言
2.HQL:是面向对象的查询语言 （映射配置的持久化类及其属性）SQL 数据库表
3.HQL语句形式
from... where... group by... having...
order by...
4.HQL:是面向对象的查询语言,对Java类与属性大小写敏感
5.HQL对关键字不区分大小写
Query接口
1.Session的createQuery()方法创建Query实例
2.createQuery方法包含一个HQL语句参数，createQuery(hql)

 

通过Object[]返回查询结果 ， （如果查询一个属性，则返回的是一个对象，不是对象数组）

@Test
	public void testSelectClauseObjectArray() {
		String hql = " select s.name,s.tel,s.website from Seller as s";
		Query query = session.createQuery(hql);
		List<Object[]> list = query.list();   //如果只查询一个属性，返回对象，而不是对象数组
		for (Object[] obj : list) {
			System.out.print("名字："+obj[0]);
			System.out.print("tel:"+obj[1]);
			System.out.println("网址："+obj[2]);
		}
	}

通过List返回查询结果 ---------new List()

        String hql = " select new List(s.name,s.tel,s.website) from Seller as s";
 

@Test
	public void testSelectClauseList() {
		String hql = " select new List(s.name,s.tel,s.website) from Seller as s";
		Query query = session.createQuery(hql);
		List<List> lists = query.list();
		for (List list : lists) {
			System.out.print("name:"+list.get(0));
			System.out.print("tel:"+list.get(1));
			System.out.println("website:"+list.get(2));
		}
	}

通过Map返回查询结果

1.select子句中使用new map指定

2.key值为索引值，字符串类型

@Test
	public void testSelectClauseMap() {
		String hql = " select new map(s.name,s.tel,s.website) from Seller as s ";
		Query query = session.createQuery(hql);
		List<Map> list = query.list();
		for (Map map : list) {
			System.out.println("name:"+map.get("0"));
			System.out.println("tel:"+map.get("1"));
			System.out.println("website:"+map.get("2"));
		}
	}

通过设置key为别名，通过别名获取数据

@Test
	public void testSelectClauseMap() {
		String hql = " select new map(s.name as name,s.tel as tel,s.website as website) from Seller as s ";  //设置属性别名
		Query query = session.createQuery(hql);
		List<Map> list = query.list();
		for (Map map : list) {
			System.out.println("name:"+map.get("name")); //通过别名获取数据 
			System.out.println("tel:"+map.get("tel"));
			System.out.println("website:"+map.get("website"));
		}
	}

以自定义类型返回

1.持久化类中定义对应的构造器

2.select子句中调用定义的构造器

//现在持久化类中创建构造函数！！！（自定义的构造器）
public Seller(String name, String tel, String website) {
		this.name = name;
		this.tel = tel;
		this.website = website;
	}

@Test
	public void testSelectClauseSelf() {
		String hql = " select new Seller(s.name,s.tel,s.website) from Seller as s ";
		Query query = session.createQuery(hql);
		List<Seller> list = query.list();
		for (Seller s : list) {
			System.out.println("name:"+s.getName());
			System.out.println("tel:"+s.getTel());
			System.out.println("website:"+s.getWebsite());
		}
	}

distinct关键字

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

String hql = " select distinct c.sex from Customer as c ";

比较运算

String hql = " from Commodity as c where c.price > 500 ";

c.description is null //为空的数据

c.description = null  //等于null的数据

c.description <> null //不等于null的数据

范围运算

1.[not] in (列表)

2.[not] between 值1 and 值2

from Customer as c where c.age not in (20,25) //查询age不为20，25的数据

from Customer as c where c.age not between 20 and 25 //查询age不在20-25之间的数据

字符串模式匹配

1.like 关键字

2.通配符 %、_

from Customer as c where c.name like '张_' 

from Customer as c where c.address like '%北京%'

逻辑运算

1.and (逻辑与)、or (逻辑或)

2.not (逻辑非) 取反

from Commodity as c where c.price between 500 and 5000 and c.name like '%电脑%' //两个查询条件中间用and

集合运算

1.is [not] empty 集合[不]为空，不包含任何元素     ------------>exists

2.member of 元素属于集合    -----------> in

from Orderform as o where o.orderItems is not empty  //查询该集合不为空的数据

四则运算

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

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

from Commodity as c where c.price*5>3000

查询单个对象

1.Query接口的uniqueResult方法

2.where子句条件的设置 （设置查询条件返回对象仅有一个或者不存在）

public void testWhere() {
		String hql = " from Customer as c where c.name='张三' ";
		Query query = session.createQuery(hql);
		Customer c = (Customer) query.uniqueResult();  //********
		System.out.println(c.getName());
	}

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

1.升序排序 asc

2.降序排序 desc

from Commodity as c order by c.price  //默认为升序

from Commodity as c order by c.price desc //降序

from Commodity as c order by seller.id asc,c.price desc,c.name asc  //先按照id，再按照价格，再按照名字排序