Hibernate 之 HQL
Hibernate 之 HQLUtil 工具类StringUtilsPageBeanBaseDao
TestHqlTest
DaoBookDaoBookDaoTest
Hibernate 之 HQL
什么是 hql? HQL 是 Hibernate Query Language 的缩写
hql 和 sql 区别 / 异同
HQL SQL
类名/属性 表名/列名
区分大小写,关键字不区分大小写 不区分大小写
别名 别名
?，从下标0开始计算位置(hibernate5之后不支持) ?，从顺序1开始计算位置
:命名参数 不支持:命名参数
面向对象的查询语言 面向结构查询语言
注意： QuerySyntaxException:book is not mapped
处理返回的结果集 ：
单个对象 select没有逗号Object[] b.bookId, b.bookNameMap new Map(b.bookId as bid, b.bookName as bname)new 构造方法(attr1,attr2) new Book(b.bookId, b.price) 单个列段
hql 中使用占位符
?占位符 从下标0开始计算位置 hibernate5之后不再支持
命名参数
聚合函数： sum avg max min count
hql 分页
int page = 2;// 页码：page
int row = 10;// 每页行数：rows
query.setFirstResult((page - 1) * row);// 设置起始记录下标
query.setMaxResults(row);// 设置返回的最大结果集
Util 工具类
StringUtils
package com
.dj
.five
.util
;
public class StringUtils {
private StringUtils() {
}
public static boolean isBlank(String s
) {
boolean b
= false;
if (null
== s
|| s
.trim().equals("")) {
b
= true;
}
return b
;
}
public static boolean isNotBlank(String s
) {
return !isBlank(s
);
}
}
PageBean
package com
.dj
.five
.util
;
import java
.util
.Map
;
import javax
.servlet
.http
.HttpServletRequest
;
public class PageBean {
private int page
= 1;
private int rows
= 3;
private int total
= 0;
private boolean pagination
= true;
private Map
<String
, String
[]> parameterMap
;
private String url
;
public void setRequest(HttpServletRequest req
) {
this.setPage(req
.getParameter("page"));
this.setRows(req
.getParameter("rows"));
this.setPagination(!"fasle".equals(req
.getParameter("pagination")));
this.setParameterMap(req
.getParameterMap());
this.setUrl(req
.getRequestURL().toString());
}
public int getMaxPage() {
return this.total
% this.rows
== 0 ? this.total
/ this.rows
: this.total
/ this.rows
+ 1;
}
public int nextPage() {
return this.page
< this.getMaxPage() ? this.page
+ 1 : this.getMaxPage();
}
public int previousPage() {
return this.page
> 1 ? this.page
- 1 : 1;
}
public PageBean() {
super();
}
public int getPage() {
return page
;
}
public void setPage(int page
) {
this.page
= page
;
}
public void setPage(String page
) {
this.page
= StringUtils
.isBlank(page
) ? this.page
: Integer
.valueOf(page
);
}
public int getRows() {
return rows
;
}
public void setRows(int rows
) {
this.rows
= rows
;
}
public void setRows(String rows
) {
this.rows
= StringUtils
.isBlank(rows
) ? this.rows
: Integer
.valueOf(rows
);
}
public int getTotal() {
return total
;
}
public void setTotal(int total
) {
this.total
= total
;
}
public void setTotal(String total
) {
this.total
= Integer
.parseInt(total
);
}
public boolean isPagination() {
return pagination
;
}
public void setPagination(boolean pagination
) {
this.pagination
= pagination
;
}
public Map
<String
, String
[]> getParameterMap() {
return parameterMap
;
}
public void setParameterMap(Map
<String
, String
[]> parameterMap
) {
this.parameterMap
= parameterMap
;
}
public String
getUrl() {
return url
;
}
public void setUrl(String url
) {
this.url
= url
;
}
public int getStartIndex() {
return (this.page
- 1) * this.rows
;
}
@Override
public String
toString() {
return "PageBean [page=" + page
+ ", rows=" + rows
+ ", total=" + total
+ ", pagination=" + pagination
+ ", parameterMap=" + parameterMap
+ ", url=" + url
+ "]";
}
}
BaseDao
package com
.dj
.five
.util
;
import java
.util
.Collection
;
import java
.util
.List
;
import java
.util
.Map
;
import java
.util
.Map
.Entry
;
import java
.util
.Set
;
import org
.hibernate
.Session
;
import org
.hibernate
.query
.Query
;
public class BaseDao {
public void setParam(Map
<String, Object> map
, Query query
) {
if(map
!= null
&& map
.size() > 0) {
Object value
= null
;
Set
<Entry
<String, Object>> entrySet
= map
.entrySet();
for (Entry
<String, Object> entry
: entrySet
) {
value
= entry
.getValue();
if(value
instanceof Object[]) {
query
.setParameterList(entry
.getKey(), (Object
[])value
);
}else if(value
instanceof Collection) {
query
.setParameterList(entry
.getKey(), (Collection
)value
);
}else {
query
.setParameter(entry
.getKey(), value
);
}
}
}
}
public String
getCountHql(String hql
) {
int index
= hql
.toUpperCase().indexOf("FROM");
return "select count(*) "+hql
.substring(index
);
}
public List
executeQuery(Session session
,Map
<String, Object> map
,String hql
,PageBean pageBean
) {
List list
= null
;
if(pageBean
!= null
&& pageBean
.isPagination()) {
String countHql
= getCountHql(hql
);
Query countQuery
= session
.createQuery(countHql
);
this.setParam(map
, countQuery
);
pageBean
.setTotal(countQuery
.getSingleResult().toString());
Query query
= session
.createQuery(hql
);
this.setParam(map
, query
);
query
.setFirstResult(pageBean
.getStartIndex());
query
.setMaxResults(pageBean
.getRows());
list
= query
.list();
}else {
Query query
= session
.createQuery(hql
);
this.setParam(map
, query
);
list
= query
.list();
}
return list
;
}
}
Test
HqlTest
package com
.dj
.five
.test
;
import java
.util
.Arrays
;
import java
.util
.List
;
import java
.util
.Map
;
import org
.hibernate
.Session
;
import org
.hibernate
.Transaction
;
import org
.hibernate
.query
.Query
;
import org
.junit
.After
;
import org
.junit
.Before
;
import org
.junit
.Test
;
import com
.dj
.four
.entity
.Book
;
import com
.dj
.two
.util
.SessionFactoryUtils
;
public class HqlTest {
private Session session
;
private Transaction transaction
;
@Before
public void before() {
session
= SessionFactoryUtils
.openSession();
transaction
= session
.beginTransaction();
}
@After
public void after() {
transaction
.commit();
session
.close();
}
@Test
public void testList1() {
Query query
= session
.createQuery("from Book");
List
<Book> list
= query
.list();
for (Book b
: list
) {
System
.out
.println(b
);
}
}
@Test
public void testList2() {
Query query
= session
.createQuery("select b.bookName as ss from Book b");
List
<String> list
= query
.list();
for (String b
: list
) {
System
.out
.println(b
);
}
}
@Test
public void testList3() {
Query query
= session
.createQuery("select b.bookId,b.bookName as ss from Book b");
List
<Object
[]> list
= query
.list();
for (Object
[] b
: list
) {
System
.out
.println(Arrays
.toString(b
));
}
}
@Test
public void testList4() {
Query query
= session
.createQuery("select new mAp(b.bookId,b.bookName) from Book b");
List
<Map> list
= query
.list();
for (Map b
: list
) {
System
.out
.println(b
);
}
}
@Test
public void testList5() {
Query query
= session
.createQuery("select new Book(b.bookId,b.bookName) from Book b");
List
<Book> list
= query
.list();
for (Book b
: list
) {
System
.out
.println(b
);
}
}
@Test
public void testList6() {
Query query
= session
.createQuery("from Book where bookId in (:bookIds)");
query
.setParameterList("bookIds", new Integer[] {1,2,4});
List
<Book> list
= query
.list();
for (Book b
: list
) {
System
.out
.println(b
);
}
}
@Test
public void testList7() {
Query query
= session
.createQuery("select o.orderNo,oi.quantity from Order o,OrderItem oi where o = oi.order");
List
<Object
[]> list
= query
.list();
for (Object
[] b
: list
) {
System
.out
.println(Arrays
.toString(b
));
}
}
@Test
public void testList8() {
Query query
= session
.createQuery("select count(*) from Book");
Long singleResult
= (Long
) query
.getSingleResult();
System
.out
.println(singleResult
);
}
@Test
public void testList9() {
Query query
= session
.createQuery("from Book");
query
.setFirstResult(2);
query
.setMaxResults(3);
List
<Book> list
= query
.list();
for (Book b
: list
) {
System
.out
.println(b
);
}
}
}
Dao
BookDao
重要代码：
public List
<Book> list1(Book book
,PageBean pageBean
) {
Session session
= SessionFactoryUtils
.openSession();
Transaction transaction
= session
.beginTransaction();
String bookName
= book
.getBookName();
String hql
= "from Book where 1=1 ";
if(StringUtils
.isNotBlank(bookName
)) {
hql
+= " and bookName like :bookName";
}
Query query
= session
.createQuery(hql
);
if(StringUtils
.isNotBlank(bookName
)) {
query
.setParameter("bookName", bookName
);
}
if(pageBean
!= null
&& pageBean
.isPagination()) {
query
.setFirstResult(pageBean
.getStartIndex());
query
.setMaxResults(pageBean
.getRows());
}
List
<Book> list
= query
.list();
transaction
.commit();
session
.close();
return list
;
}
public List
<Book> list2(Book book
,PageBean pageBean
) {
Session session
= SessionFactoryUtils
.openSession();
Transaction transaction
= session
.beginTransaction();
String bookName
= book
.getBookName();
Map
<String, Object> map
= new HashMap<>();
String hql
= "from Book where 1=1 ";
if(StringUtils
.isNotBlank(bookName
)) {
hql
+= " and bookName like :bookName";
map
.put("bookName", bookName
);
}
List
<Book> list
= super.executeQuery(session
, map
, hql
, pageBean
);
transaction
.commit();
session
.close();
return list
;
}
BookDaoTest
重要代码：
@Test
public void testList1() {
Book book
= new Book();
PageBean pageBean
= new PageBean();
book
.setBookName("%西%");
List
<Book> list
= this.bookDao
.list1(book
, pageBean
);
for (Book b
: list
) {
System
.out
.println(b
);
}
}
@Test
public void testList2() {
Book book
= new Book();
PageBean pageBean
= new PageBean();
pageBean
.setPagination(false);
List
<Book> list
= this.bookDao
.list2(book
, pageBean
);
for (Book b
: list
) {
System
.out
.println(b
);
}
}
