hibernate之hql
1. 什么是hql
HQL是Hibernate Query Language的缩写
查全部
2. hql和sql区别/异同
HQL SQL
类名/属性 表名/列名
区分大小写,关键字不区分大小写 不区分大小写
别名 别名
?,从下标0开始计算位置(hibernate5之后不支持) ?,从顺序1开始计算位置
:命名参数 不支持:命名参数
面向对象的查询语言 面向结构查询语言
public List<Book> list1(Book book,PageBean pagebean) { Session session = SessionFactoryUtils.openSession(); Transaction transaction = session.beginTransaction(); //下面代码处理的是book实体类的条件查询 String bookName=book.getBookName(); //sql语句where后面能写true hql不能出现true 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 list=query.list(); transaction.commit(); session.close(); return list; }
package com.five;
import java.util.Collection;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;
import org.hibernate.Query;
import org.hibernate.Session;
/**
* hibernate的通用查询的Dao层
* 思想是完全借鉴于sql的BaseDao
* select * from t_mvc_book where bname lik %升序%
* 将原生的hql语句以from关键字进行截取
* @author 11
*
*/
public class BaseDao {
/**
* 给hibernate中的query对象中的命名参数列表赋值
* @param query
* @param map
*/
public void setparam(Query query,Map<String, Object> map) {
if (map!=null && map.size()>0) {
Set<Entry<String, Object>> entryset=map.entrySet();
for (Entry<String, Object> entry : entryset) {
Object value=entry.getValue();
if (value instanceof Collection) {
query.setParameter(entry.getKey(), (Collection)value);
}
else if(value instanceof Object[]) {
query.setParameter(entry.getKey(), (Object[])value);
}
else {
query.setParameter(entry.getKey(), value);
}
}
}
}
public String getCountHql(String hql) {
return "select count(*)"+hql.substring(hql.toUpperCase().indexOf("FROM")) ;
}
/**
*
* @param session 当前会话
* @param hql 带命名参数的hql语句
* @param map 命名参数对应的值的集合
* @param pagebean 分页实体类
* @return
*/
public List executeQuery(Session session,String hql,Map<String, Object> map,PageBean pagebean) {
List list=null;
Query query=null;
if (pagebean!=null && pagebean.isPagination()) {
String counthql=getCountHql(hql);
Query countquery=session.createQuery(counthql);
this.setparam(countquery, map);
pagebean.setTotal(countquery.getSingleResult().toString());
query=session.createQuery(hql);
this.setparam(query, map);
query.setFirstResult(pagebean.getStartIndex());
query.setMaxResults(pagebean.getRows());
list=query.list();
}
else {
query=session.createQuery(hql);
this.setparam(query, map);
list =query.list();
}
return list;
}
}
然后再是实体类book
package com.four;
import java.io.Serializable;
import java.util.HashSet;
import java.util.Set;
public class Book implements Serializable{
// book_id int primary key auto_increment,
// book_name varchar(50) not null,
// price float not null
private Integer bookId;
private String bookName;
private Float price;
private Set<Category> categories = new HashSet<Category>();
private Integer initCategories = 0;
public Integer getInitCategories() {
return initCategories;
}
public void setInitCategories(Integer initCategories) {
this.initCategories = initCategories;
}
public Integer getBookId() {
return bookId;
}
public void setBookId(Integer bookId) {
this.bookId = bookId;
}
public String getBookName() {
return bookName;
}
public void setBookName(String bookName) {
this.bookName = bookName;
}
public Float getPrice() {
return price;
}
public void setPrice(Float price) {
this.price = price;
}
public Set<Category> getCategories() {
return categories;
}
public void setCategories(Set<Category> categories) {
this.categories = categories;
}
@Override
public String toString() {
return "Book [bookId=" + bookId + ", bookName=" + bookName + ", price=" + price + "]";
}
public Book(Integer bookId, String bookName) {
super();
this.bookId = bookId;
this.bookName = bookName;
}
public Book() {
super();
}
}
接下来两个测试方法:
public void testList1() {
Book book = new Book();
// book.setBookName("%西游记%");
PageBean pagebean=new PageBean();
List<Book> list1= this.bookDao.list1(book, pagebean);
for (Book book2 : list1) {
System.out.println(book2);
}
}
@Test
public void testList2() {
Book book = new Book();
book.setBookName("%西游记%");
PageBean pagebean=new PageBean();
List<Book> list1= this.bookDao.list2(book, pagebean);
for (Book book2 : list1) {
System.out.println(book2);
}
}
list2: