1、什么是hql?
HQL是Hibernate Query Language的缩写
2、处理返回的结果集:
2-1:直接利用对象进行数据接收
2-2:String[] :
public void testList2() {
String hql = "select bookName from Book";
List<String> list = session.createQuery(hql).list();
for (String bname : list) {
System.out.println(bname);
}
}
2-3: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));
}
}
2-4:Map:利用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);
}
}
2-5:利用构造函数方法进行处理结果集
①、当你给某一个类提供有参构造器,随便加上无参构造器
②、构造方法赋值的原理(完善MVC框架的通用CRUD)
@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);
}
}
3:. hql中使用占位符
参数名:xxx
3-1:查询一个id为2 的
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);
3-2:查找价格在22-46之间的,
String hql = "from Book where price > :min and price < :max ";
Query query = session.createQuery(hql);
query.setParameter("min", 22f);
query.setParameter("max", 46f);
List<Book> list = query.list();
for (Book book : list) {
System.out.println(book);
}
3-3:查找某数在多少之间:
比较简单的方式:query.setParameterList(“bookIds”, new Integer[] {2,3,4});
String hql = "from Book where bookId in (:bookIds)";
Query query = session.createQuery(hql);
// query.setParameterList("bookIds", new Integer[] {2,3,4});
List<Integer> params = new ArrayList<Integer>();
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);
}
4、分页::
4-1:普通分页:
String hql = "from Book";
int rows = 3;
int page = 2;
Query query = session.createQuery(hql);
query.setFirstResult((page-1)*rows);
query.setMaxResults(rows);
List<Book> list = query.list();
for (Book book : list) {
System.out.println(book);
}
4-2:带查询的分页:
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);
}
注意:
页码:page , 每页行数:rows
query.setFirstResult((page - 1) * row);// 设置起始记录下标
query.setMaxResults(rows);// 设置返回的最大结果集
5、hql 与sql 的区别?
hql | sql |
---|---|
类名/属性 | 表名/列名 |
区分大小写,关键字不区分大小写 | 不区分大小写 |
别名 | 别名 |
?,从下标0开始计算位置(hibernate5之后不支持) | ?,从顺序1开始计算位置 |
:命名参数 | 不支持:命名参数 |
面向对象的查询语言 | 面向结构查询语言 |