Hibernate之HQL

　

qyqx

1. 什么是hql

HQL是Hibernate Query Language的缩写

2. hql和sql区别/异同

HQL	SQL
类名/属性	表名/列名
区分大小写,关键字不区分大小写	不区分大小写
别名	别名 as a
?，从下标0开始计算位置(hibernate5之后不支持)	?，从顺序1开始计算位置
:命名参数	不支持:命名参数
面向对象的查询语言	面向结构查询语言

注1：QuerySyntaxException:book is not mAPPed

3.案例:hql的基本功能演示和通用分页：

3.1 HqlTest.java

package com.cjq.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.cjq.four.entity.Book;
import com.cjq.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);
  }
 }
 
 /**
  * 查两个列段及以上，默认返回的是Object【】
  */
 @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));
  }
 }
 
 /**
  * 注意map是函数，所以不区分大小写，返回的是map集合
  */
 @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);
  }
 }
 
 /**
  * HQL语句支持占位符
  */
 @Test
 public void testList6() {
//  Query query = session.createQuery("from Book where bookId = :bookId");
//  query.setparameter("bookId", 1);
//  Book b = (Book) query.getSingleResult();
//  System.out.println(b);
  
  Query query = session.createQuery("from Book where bookId in (:bookIds)");
  query.setParameterList("bookIds", new integer[] {1,2,4});
//  List<Integer> params = new ArrayList<Integer>();
//  params.add(1);
//  params.add(2);
//  params.add(4);
//  query.setParameterList("bookIds", params);
  List<Book> list = query.list();
  for (Book b : list) {
   System.out.println(b);
  }
 }
 
 /**
  * HQL支持连接查询
  */
 @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));
  }
 }
 
 /**
  * HQL支持聚合函数
  */
 @Test
 public void testList8() {
  Query query = session.createQuery("select count(*) from Book");
  Long singleResult = (Long) query.getSingleResult();
  System.out.println(singleResult);
 }
 
 /**
  * HQL分页
  */
 @Test
 public void testList9() {
  Query query = session.createQuery("from Book");
  query.setFirstResult(5);
  query.setMaxResults(5);
  List<Book> list = query.list();
  for (Book b : list) {
   System.out.println(b);
  }
 }
}

结果如下：

Hibernate: 
    select
        book0_.book_id as book_id1_0_,
        book0_.book_name as book_nam2_0_,
        book0_.price as price3_0_ 
    from
        t_hibernate_book book0_
Book [bookId=1, bookName=西游记, price=50.0]
Book [bookId=2, bookName=红楼梦, price=50.0]
Book [bookId=7, bookName=杀神2, price=34.0]
Book [bookId=8, bookName=圣墟, price=66.0]
Book [bookId=9, bookName=圣墟2, price=66.0]
Book [bookId=12, bookName=不死不灭, price=45.0]
Book [bookId=13, bookName=我是至尊, price=66.0]
Book [bookId=15, bookName=灵域, price=88.0]
Book [bookId=16, bookName=灵域2, price=88.0]
Book [bookId=17, bookName=b, price=10.0]
Book [bookId=18, bookName=c, price=10.0]
Book [bookId=19, bookName=b, price=10.0]
Book [bookId=20, bookName=b, price=10.0]
Book [bookId=21, bookName=c, price=10.0]
Book [bookId=22, bookName=斗气化马, price=10.0]
Book [bookId=23, bookName=斗宗强者恐怖如斯, price=10.0]
Book [bookId=24, bookName=T226自传, price=10.0]
Book [bookId=25, bookName=T226c, price=10.0]
Book [bookId=26, bookName=胡歌1, price=12.0]
Book [bookId=27, bookName=胡歌2, price=12.0]

Hibernate: 
    select
        book0_.book_name as col_0_0_ 
    from
        t_hibernate_book book0_
西游记
红楼梦
杀神2
圣墟
圣墟2
不死不灭
我是至尊
灵域
灵域2
b
c
b
b
c
斗气化马
斗宗强者恐怖如斯
T226自传
T226c
胡歌1
胡歌2

Hibernate: 
    select
        book0_.book_id as col_0_0_,
        book0_.book_name as col_1_0_ 
    from
        t_hibernate_book book0_
[1, 西游记]
[2, 红楼梦]
[7, 杀神2]
[8, 圣墟]
[9, 圣墟2]
[12, 不死不灭]
[13, 我是至尊]
[15, 灵域]
[16, 灵域2]
[17, b]
[18, c]
[19, b]
[20, b]
[21, c]
[22, 斗气化马]
[23, 斗宗强者恐怖如斯]
[24, T226自传]
[25, T226c]
[26, 胡歌1]
[27, 胡歌2]

Hibernate: 
    select
        book0_.book_id as col_0_0_,
        book0_.book_name as col_1_0_ 
    from
        t_hibernate_book book0_
{0=1, 1=西游记}
{0=2, 1=红楼梦}
{0=7, 1=杀神2}
{0=8, 1=圣墟}
{0=9, 1=圣墟2}
{0=12, 1=不死不灭}
{0=13, 1=我是至尊}
{0=15, 1=灵域}
{0=16, 1=灵域2}
{0=17, 1=b}
{0=18, 1=c}
{0=19, 1=b}
{0=20, 1=b}
{0=21, 1=c}
{0=22, 1=斗气化马}
{0=23, 1=斗宗强者恐怖如斯}
{0=24, 1=T226自传}
{0=25, 1=T226c}
{0=26, 1=胡歌1}
{0=27, 1=胡歌2}

Hibernate: 
    select
        book0_.book_id as col_0_0_,
        book0_.book_name as col_1_0_ 
    from
        t_hibernate_book book0_
Book [bookId=1, bookName=西游记, price=null]
Book [bookId=2, bookName=红楼梦, price=null]
Book [bookId=7, bookName=杀神2, price=null]
Book [bookId=8, bookName=圣墟, price=null]
Book [bookId=9, bookName=圣墟2, price=null]
Book [bookId=12, bookName=不死不灭, price=null]
Book [bookId=13, bookName=我是至尊, price=null]
Book [bookId=15, bookName=灵域, price=null]
Book [bookId=16, bookName=灵域2, price=null]
Book [bookId=17, bookName=b, price=null]
Book [bookId=18, bookName=c, price=null]
Book [bookId=19, bookName=b, price=null]
Book [bookId=20, bookName=b, price=null]
Book [bookId=21, bookName=c, price=null]
Book [bookId=22, bookName=斗气化马, price=null]
Book [bookId=23, bookName=斗宗强者恐怖如斯, price=null]
Book [bookId=24, bookName=T226自传, price=null]
Book [bookId=25, bookName=T226c, price=null]
Book [bookId=26, bookName=胡歌1, price=null]
Book [bookId=27, bookName=胡歌2, price=null]

Hibernate: 
    select
        book0_.book_id as book_id1_0_,
        book0_.book_name as book_nam2_0_,
        book0_.price as price3_0_ 
    from
        t_hibernate_book book0_ 
    where
        book0_.book_id in (
            ? , ? , ?
        )
Book [bookId=1, bookName=西游记, price=50.0]
Book [bookId=2, bookName=红楼梦, price=50.0]

Hibernate: 
    select
        order0_.order_no as col_0_0_,
        orderitem1_.quantity as col_1_0_ 
    from
        t_hibernate_order order0_ cross 
    join
        t_hibernate_order_item orderitem1_ 
    where
        order0_.order_id=orderitem1_.oid
[P7, 11]
[P7, 11]
[P7, 11]
[P7, 11]
[P7, 11]
[P7, 11]
[P7, 78]
[P7, 56]
[P101, 20]
[P101, 21]
[P101, 22]
[P101, 23]
[P101, 24]
[P101, 25]
[P20, 20]
[P20, 21]
[P20, 22]
[P20, 23]
[P20, 24]
[P20, 25]
[T226, 20]
[T226, 21]
[T226, 22]

Hibernate: 
    select
        count(*) as col_0_0_ 
    from
        t_hibernate_book book0_
20

Hibernate: 
    select
        book0_.book_id as book_id1_0_,
        book0_.book_name as book_nam2_0_,
        book0_.price as price3_0_ 
    from
        t_hibernate_book book0_ limit ?,
        ?
Book [bookId=12, bookName=不死不灭, price=45.0]
Book [bookId=13, bookName=我是至尊, price=66.0]
Book [bookId=15, bookName=灵域, price=88.0]
Book [bookId=16, bookName=灵域2, price=88.0]
Book [bookId=17, bookName=b, price=10.0]

3.2用HQL写一个通用分页

util包：

PageBean.java

package com.cjq.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;
 // 获取上一次访问后台的url
 private String url; /**
  * 初始化pagebean
  * 
  * @param req
  */
 public void setRequest(HttpServletRequest req) {
  this.setPage(req.getparameter("page"));
  this.setRows(req.getParameter("rows"));
  // 只有jsp页面上填写pagination=false才是不分页
  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;
 } /**
  * 获得起始记录的下标
  * 
  * @return
  */
 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 + "]";
 }}

StringUtils.java

package com.cjq.five.util;
public class StringUtils {
 // 私有的构造方法，保护此类不能在外部实例化
 private StringUtils() {
 } /**
  * 如果字符串等于null或去空格后等于""，则返回true，否则返回false
  * 
  * @param s
  * @return
  */
 public static boolean isBlank(String s) {
  boolean b = false;
  if (null == s || s.trim().equals("")) {
   b = true;
  }
  return b;
 }
 
 /**
  * 如果字符串不等于null或去空格后不等于""，则返回true，否则返回false
  * 
  * @param s
  * @return
  */
 public static boolean isNotBlank(String s) {
  return !isBlank(s);
 }}

basedao.java

package com.cjq.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;/**
 * hibernate的通用查询Dao层 思想完全借鉴与sql的BaseDao
 *
 */public class BaseDao {
 /**
  * 给hibernate中Query对象中的命名参数列表赋值
  * 
  * @param query
  * @param map
  */
 public void setParem(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.setParameterList(entry.getKey(), (Collection) value);
    else if (value instanceof Object[])
     query.setParameterList(entry.getKey(), (Object[]) value);
    else
     query.setParameter(entry.getKey(), value);
   }
  }
 } public String getCountHql(String hql) {
  return "select count(*) " + hql.substring(hql.toUpperCase().indexof("FROM"));
 } /**
  * 通用hql语句查询
  * 
  * @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.setParem(countQuery, map);
   pageBean.setTotal(countQuery.getSingleResult().toString());
   query = session.createQuery(hql);
   this.setParem(query, map);
   query.setFirstResult(pageBean.getStartIndex());
   query.setMaxResults(pageBean.getRows());
   list = query.list();  } else {
   query = session.createQuery(hql);
   this.setParem(query, map);
   list = query.list();
  }
  return list;
 }
}

dao包：

BookDao.java

package com.cjq.five.dao;
import java.util.HashMap;
import java.util.List;
import java.util.Map;import org.hibernate.Hibernate;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.query.Query;import com.cjq.five.util.BaseDao;
import com.cjq.five.util.PageBean;
import com.cjq.five.util.StringUtils;
import com.cjq.four.entity.Book;
import com.cjq.four.entity.Category;
import com.cjq.two.util.SessionFactoryUtils;public class BookDao extends BaseDao {
 public Integer addBook(Book book) {
  Session session = SessionFactoryUtils.openSession();
  Transaction transaction = session.beginTransaction();
  Integer bid = (Integer) session.save(book);
  transaction.commit();
  session.close();
  return bid;
 } public Integer addCategory(Category category) {
  Session session = SessionFactoryUtils.openSession();
  Transaction transaction = session.beginTransaction();
  Integer cid = (Integer) session.save(category);
  transaction.commit();
  session.close();
  return cid;
 } public Category getCategory(Category category) {
  Session session = SessionFactoryUtils.openSession();
  Transaction transaction = session.beginTransaction();
  Category c = session.get(Category.class, category.getCategoryId());
  transaction.commit();
  session.close();
  return c;
 } public Book getBook(Book book) {
  Session session = SessionFactoryUtils.openSession();
  Transaction transaction = session.beginTransaction();
  Book b = session.get(Book.class, book.getBookId());
  if (b != null && new Integer(1).equals(book.getInitCategories())) {
   Hibernate.initialize(b.getCategories());
  }
  transaction.commit();
  session.close();
  return b;
 } public void delBook(Book book) {
  Session session = SessionFactoryUtils.openSession();
  Transaction transaction = session.beginTransaction();
  session.delete(book);
  transaction.commit();
  session.close();
 } public void delCategory(Category category) {
  Session session = SessionFactoryUtils.openSession();
  Transaction transaction = session.beginTransaction();
  Category c = session.get(Category.class, category.getCategoryId());
  if (c != null) {
   for (Book b : c.getBooks()) {
    // 通过在被控方通过主控方来解除关联关系，最后被控方再做删除
    b.getCategories().remove(c);
   }
  }
  session.delete(c);
  transaction.commit();
  session.close();
 } /********************** 通用查询 ************************/
 public List<Book> list1(Book book, PageBean pageBean) {
  Session session = SessionFactoryUtils.openSession();
  Transaction transaction = session.beginTransaction();
  // 下面代码处理的是book实体类的条件查询
  String bookName = book.getBookName();
  // sql语句where后面key直接写true,而回去了不能出现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; } public List<Book> list2(Book book, PageBean pageBean) {
  Session session = SessionFactoryUtils.openSession();
  Transaction transaction = session.beginTransaction();
  // 下面代码处理的是book实体类的条件查询
  String bookName = book.getBookName();
  Map<String, Object> map = new HashMap<String, Object>();
  // sql语句where后面key直接写true,而回去了不能出现true
  String hql = "from Book where 1=1";
  if (StringUtils.isNotBlank(bookName)) {
   hql += " and bookName like :bookName";
   map.put("bookName", bookName);
  }  List list = super.executeQuery(session, hql, map, pageBean);
  transaction.commit();
  session.close();
  return list; }
}

测试类：

BookDaoTest.java

package com.cjq.five.dao;
import java.util.List;import org.junit.Test;import com.cjq.five.util.BaseDao;
import com.cjq.five.util.PageBean;
import com.cjq.four.entity.Book;
import com.cjq.four.entity.Category;public class BookDaoTest {
 private BookDao bookDao = new BookDao(); @Test
 public void testGetBook() {
  Book book = new Book();
  book.setBookId(8);
  book.setInitCategories(1);
  Book b = this.bookDao.getBook(book);
  System.out.println(b.getBookName());
  System.out.println(b.getCategories());
 } /**
  * book.hbm.xml inverse=fasle category.hbm.xml inverse=true 数据添加正常
  * 书籍表、桥接表各新增一条数据
  */
 @Test
 public void test1() {
  Book book = new Book();
  book.setBookName("风年");
  book.setPrice(10f);
  Category category = new Category();
  category.setCategoryId(5);
  // 直接将category对象加入到新建的book中是错误的，因为此时的category是临时态的，hibernate是不会管理的
  // book.getCategories().add(category);
  Category c = this.bookDao.getCategory(category);  // c.getBooks().add(book);
  book.getCategories().add(c);
  this.bookDao.addBook(book);
 } /**
  * book.hbm.xml inverse=true category.hbm.xml inverse=true 只增加书籍表数据 桥接表不加数据
  * 原因：双方都没有去维护关系
  */
 @Test
 public void test2() {
  Book book = new Book();
  book.setBookName("c");
  book.setPrice(10f);
  Category category = new Category();
  category.setCategoryId(5);
  Category c = this.bookDao.getCategory(category);  book.getCategories().add(c);
  this.bookDao.addBook(book);
  // c.getBooks().add(book);
 } @Test
 public void testList1() {
  Book book = new Book();
  book.setBookName("%龙象%");
  PageBean pageBean = new PageBean();
  List<Book> list1 = this.bookDao.list1(book, pageBean);
  for (Book b : list1) {
   System.out.println(b);
  }
 } @Test
 public void testList2() {
  Book book = new Book();
  book.setBookName("%龙象%");
  PageBean pageBean = new PageBean();
  // pageBean.setPage(2);
  List<Book> list1 = this.bookDao.list2(book, pageBean);
  for (Book b : list1) {
   System.out.println(b);
  }
 }}

结果如下：

文章创建于: 2019-09-06 10:52:33

相关阅读

Hibernate Criteria多表查询

Hibernate Criteria多表查询Hibernate有多种查询方式，如果想要多表查询，有3种方法1.HQL查询（写连接语句）2.Native SQL（手写原生SQL语句

通俗解释Hibernate中SessionFactory和Session是否是线

******************************************************************* SessionFactory接口负责Hibernate的初始化和建立Sessio

C# ORM NHibernate实践

前阵有个应用程序项目需要连接数据库mysql，想着单纯写sql语句做CRUD太麻烦了，就想起了ORM技术，简单点说就是把数据库的表映射成对象，

Hibernate 二级缓存和查询缓存

一级缓存:   1,在session上面有一个一级缓存;一级缓存的生命周期和session相同,一级缓存最大生命周期就是一个线程;在web环境下

hibernate uniqueResult方法

数据库中根据你的查询条件只会返回唯一结果，就可以用uniqueResult这个方法！否则就用list（）；其返回类型为Object uniqueResult()方法通