HQL语句
概述
HQL是Hibernate Query Language的缩写,提供更加丰富灵活、更为强大的查询能力;HQL更接近SQL语句查询语法。
用法
使用经典的的数据库表关系模型学生选课:student(学生表)、course(课表)、sc(成绩表)
1) FROM子句的使用
from子句的查询可以使用全限定名进行查询,也可以使用别名进行查询。
/**
* @Title: testFrom
* @Description: from子句的使用
* @throws
*/
@Test
public void testFrom() {
// 使用全限定名查询
// String hql = "from demo.hql.domain.Student";
// 查询主体必须和持久化类保持一致
// String hql = "from Student";
// 使用别名,方便hql语句的其他部分通过别名引用该类
// String hql = "from Student as s";
// 使用别名可以不使用as关键字
String hql = "from Student s";
Query query = session.createQuery(hql);
List<Student> students = query.list();
for (Student student : students) {
System.out.println(student);
}
}
2) 限定FROM子句的返回值
以Object[]形式返回选择的属性
/**
* @Title: testSelectObjectArray
* @Description: 以Object[]形式返回选择的属性
* @throws
*/
@Test
public void testSelectObjectArray() {
String hql = "select s.sname,s.ssex from Student s";
Query query = session.createQuery(hql);
List<Object[]> list = query.list();
for (Object[] objects : list) {
System.out.println("name:" + objects[0] + "age:" + objects[1]);
}
// 如果返回的结果只有一个的话,就不能用Object[]来接收,否则会报错
/*String hql = "select s.sname from Student s";
Query query = session.createQuery(hql);
List<Object> list = query.list();
for (Object object : list) {
System.out.println("name:" + object);
}*/
}
以List形式返回选择的属性
/**
* @Title: testSelectList
* @Description: 以List形式返回选择的属性
* @throws
*/
@Test
public void testSelectList() {
String hql = "select new list(s.sname,s.ssex,s.sage) from Student s";
Query query = session.createQuery(hql);
List<List> lists = query.list();
for (List list : lists) {
System.out.println("name:" + list.get(0) + " sex:" + list.get(1) + " age:" + list.get(2));
}
}
以map的形式返回结果
/**
* @Title: testSelectMap
* @Description: 以map的形式返回结果
* @throws
*/
@Test
public void testSelectMap() {
String hql = "select new map(s.sname,s.ssex,s.sage) from Student s";
Query query = session.createQuery(hql);
List<Map> maps = query.list();
// 返回结果的 key 就是查询的顺序 value 就是返回的结果
for (Map map : maps) {
System.out.println("name:" + map.get("0") + " sex:" + map.get("1") + " age:" + map.get("2"));
}
}
以自定义类型返回结果
/**
* @Title: testSelectCustomType
* @Description: 以自定义类型返回结果
* @throws
*/
@Test
public void testSelectCustomType() {
// 自定义类中必须有相应的构造方法
String hql = "select new Student(s.sname,s.ssex,s.sclass) from Student s";
Query query = session.createQuery(hql);
List<Student> students = query.list();
for (Student student : students) {
System.out.println("name:" + student.getSname() + " sex:" + student.getSsex() + " class:" + student.getSclass());
}
}
distinct关键字去除重复元素
/**
* @Title: testSelectDistinct
* @Description: 使用distinct关键字去除重复元素
* @throws
*/
@Test
public void testSelectDistinct() {
String hql = "select distinct s.sclass from Student s";
Query query = session.createQuery(hql);
List<Object> objects = query.list();
for (Object object : objects) {
System.out.println(object);
}
}
3) WHERE子句
where子句中有比较运算、范围运算、字符串匹配运算、逻辑运算、集合运算、四则运算
/**
* @Title: testWhere
* @Description: where子句
* @throws
*/
@Test
public void testWhere() {
/*
* 比较运算
* 运算符 = <> < > >= <=
* null 值判断 is null/is not null
*/
// String hql = "from Student s where s.sage > 20";
// String hql = "from Student s where s.sclass <> null";
/*
* 范围运算
* in / not in (候选值列表)
* between / not between 值1 and 值2
*/
// String hql = "from Student s where s.sage in (20,25)";
// String hql = "from Student s where s.sage not between 20 and 25";
/*
* 字符串模式匹配
* like关键字
* 通配符 %:匹配任意个字符 _:匹配一个字符
*/
// String hql = "from Student s where s.sname like '_二'";
// String hql = "from Student s where s.sname like '_二%'";
/*
* 逻辑运算
* and or not
*/
// String hql = "from Student s where s.sname like '_二' or s.sage = 20";
/*
* 集合运算
* is empty / is not empty 集合为空/不为空 empty ——> exists
* member of 元素属于集合 member of ——> in
*/
// String hql = "from Student s where s.scs is empty";
/*
* 四则运算
* + - * /
*/
String hql = "from Student s where s.sage + 2 > 21";
Query query = session.createQuery(hql);
List<Student> students = query.list();
for (Student student : students) {
System.out.println(student);
}
}
4)ORDER BY 对查询结果进行排序
/**
* @Title: testOrderBy
* @Description: order by 子句对查询结果进行排序
* @throws
*/
@Test
public void testOrderBy() {
// String hql = "from Student s order by s.sage";
String hql = "from Student s order by s.sage asc , s.sid desc";
Query query = session.createQuery(hql);
List<Student> students = query.list();
for (Student student : students) {
System.out.println(student);
}
}
5) uniqueResult方法查询单个对象
/**
* @Title: testUniqueResult
* @Description: uniqueResult方法查询单个对象,如果有多个结果会报错
* @throws
*/
@Test
public void testUniqueResult() {
String hql = "from Student s where s.sid = 1";
Query query = session.createQuery(hql);
Student student = (Student) query.uniqueResult();
System.out.println(student);
}