1. 什么是hql
HQL是Hibernate Query Language的缩写
2. hql和sql区别/异同
<一> hql:①类名/属性 区分大小写,关键字不区分大小写
②占位符 ?,从下标0开始计算位置(hibernate5之后不支持)
③ :命名参数
④面向对象的查询语言
<二> sql:①表名/列名 不区分大小写
②占位符 ?,从顺序1开始计算位置
③不支持:命名参数
④面向结构查询语言
注1:QuerySyntaxException:book is not mapped
3. 处理返回的结果集
3.1 单个对象
select没有逗号
3.2 Object[]
b.bookId, b.bookName
3.3 Map
new Map(b.bookId as bid, b.bookName as bname)
3.4 new 构造方法(attr1,attr2)
new Book(b.bookId, b.price)
结果处理情况一:
直接利用对象进行数据的接收
jdbc:操作的是数据库表
hql :操作的是实体类
@Test
public void testList1() {
String hql = "from Book";
// Query -->preparestatement
Query query = session.createQuery(hql);
List<Book> list = query.list();
for (Book book : list) {
System.out.println(book);
}
}
结果处理情况二:
查一列
jdbc:查的是表中某列,列 名不区分大小写
hql :查的是实体类的某一属性,严格区分大小写
@Test
public void testList2() {
String hql = "select bookName from Book";
List<String> list = session.createQuery(hql).list();
for (String bname : list) {
System.out.println(bname);
}
}
结果处理情况三 :Object[]
@Test
public void testList3() {
String hql = "select bookId,bookName from Book";
List<Object[]> list = session.createQuery(hql).list();
for (Object[] book : list) {
System.out.println(Arrays.toString(book));
}
}
结果处理函数:利用hibernate内置函数进行结果处理
@Test
public void testList4() {
String hql = "select new map(bookId,bookName) from Book";
List<Map> list = session.createQuery(hql).list();
for (Map book : list) {
System.out.println(book);
}
}
结果处理函数:利用构造方法进行结果处理
当你给某一个类提供有参构造器顺便加上无参构造器
@Test
public void testList5() {
String hql = "select new Book(bookId,bookName) from Book";
List<Book> list = session.createQuery(hql).list();
for (Book book : list) {
System.out.println(book);
}
}
4. hql中使用占位符 命名参数
4.1 ?占位符
从下标0开始计算位置
hibernate5之后不再支持?占位符
jdbc:select *from t_hibernate_book where book_id=?
hql:from Book where bookId = ? 或者 :bookId
@Test
public void testList6() {
// String hql = "from Book where bookId = :bookId";
// Query query = session.createQuery(hql);
// query.setParameter("bookId", 2);
// Book book = (Book) query.getSingleResult();
// System.out.println(book);
// String hql = "from Book where price >:min and price <:max";
// Query query = session.createQuery(hql);
// query.setParameter("min", 44f);
// query.setParameter("max", 54f);
// List<Book> list = query.list();
// for (Book book : list) {
// System.out.println(book);
// }
String hql = "from Book where bookId in (:bookIds)";
Query query = session.createQuery(hql);
List<Integer> params = new ArrayList<>();
params.add(1);
params.add(2);
params.add(3);
query.setParameterList("bookIds", params);
List<Book> list = query.list();
for (Book book : list) {
System.out.println(book);
}
}
5. 连接查询
@Test
public void testList7() {
// String hql = "select o.orderNo,oi.productId from Order o,OrderItem oi where o.orderId = oi.order.orderId";
String hql = "select o.orderNo,oi.productId from Order o,OrderItem oi where o = oi.order";
Query query = session.createQuery(hql);
List<Object[]> list = query.list();
for (Object[] objects : list) {
System.out.println(Arrays.toString(objects));
}
}
6. 聚合函数
sum
avg
max
min
count
@Test
public void testList8() {
String hql = "select count(*) from Book";
Object result = session.createQuery(hql).getSingleResult();
System.out.println(result);
}
7. hql分页
int page = 2;// 页码:page
int row = 10;// 每页行数:rows
query.setFirstResult((page - 1) * row);// 设置起始记录下标
query.setMaxResults(row);// 设置返回的最大结果集
@Test
public void testList9() {
String hql = "from Book where bookName like :bookName";
int rows = 3;
int page = 1;
Query query = session.createQuery(hql);
query.setParameter("bookName", "%西游%");
query.setFirstResult((page-1)*rows);
query.setMaxResults(rows);
List<Book> list = query.list();
for (Book book : list) {
System.out.println(book);
}
}