Hibernate之HQL
HQL是Hibernate Query Language的缩写
HQL和SQL区别/异同
数据库中hql与sql的区别:
1、sql 面向数据库表查询。
2、hql 面向对象查询。
3、hql : from 后面跟的 类名+类对象 where 后 用 对象的属性做条件。
4、sql: from 后面跟的是表名 where 后 用表中字段做条件查询。
5、在Hibernate中使用查询时,一般使用Hql查询语句。
6、HQL(Hibernate Query Language),即Hibernate的查询语言跟SQL非常相像。不过HQL与SQL的最根本的区别,就是它是面向对象的。
在Hibernate中使用查询时,一般使用Hql查询语句。
HQL | SQL |
类名/属性 | 表名/列名 |
区分大小写,关键字不区分大小写 | 不区分大小写 |
?,从下标0开始计算位置(hibernate5之后不支持) ? | 从顺序1开始计算位置 |
:命名参数 | 不支持:命名参数 |
面向对象的查询语言 | 面向结构查询语言 |
HQL实例测试HqlTest
返回对象(多个)
1 public class HqlTest { 2 private Session session; 3 private Transaction transaction; 4 5 @Before 6 public void before() { 7 session = SessionFactoryUtils.openSession(); 8 transaction = session.beginTransaction(); 9 } 10 11 @After 12 public void after() { 13 transaction.commit(); 14 session.close(); 15 } 16 17 /** 18 * 返回对象(多个) 19 */ 20 @Test 21 public void testList1() { 22 Query query = session.createQuery("from Book"); 23 List<Book> list = query.list(); 24 for (Book b : list) { 25 System.out.println(b); 26 } 27 }
返回单个列段,用字符串就可以接受
1 @Test 2 public void testList2() { 3 Query query = session.createQuery("select b.bookName as ss from Book b"); 4 List<String> list = query.list(); 5 for (String b : list) { 6 System.out.println(b); 7 } 8 }
查两个列段及以上,默认返回的是Object【】
1 @Test 2 public void testList3() { 3 Query query = session.createQuery("select b.bookId,b.bookName as ss from Book b"); 4 List<Object[]> list = query.list(); 5 for (Object[] b : list) { 6 System.out.println(Arrays.toString(b)); 7 } 8 }
注意map是函数,所以不区分大小写,返回的是map集合
1 @Test 2 public void testList4() { 3 Query query = session.createQuery("select new mAp(b.bookId,b.bookName) from Book b"); 4 List<Map> list = query.list(); 5 for (Map b : list) { 6 System.out.println(b); 7 } 8 }
查两个列段及以上,也可返回对象,前提是有对应的构造函数
1 @Test 2 public void testList5() { 3 Query query = session.createQuery("select new Book(b.bookId,b.bookName) from Book b"); 4 List<Book> list = query.list(); 5 for (Book b : list) { 6 System.out.println(b); 7 } 8 }
HQL语句支持占位符
query对象相当于preparestatement
1 @Test 2 public void testList6() { 3 // Query query = session.createQuery("from Book where bookId = :bookId"); 4 // query.setParameter("bookId", 1); 5 // Book b = (Book) query.getSingleResult(); 6 // System.out.println(b); 7 8 Query query = session.createQuery("from Book where bookId in (:bookIds)"); 9 query.setParameterList("bookIds", new Integer[] {1,2,4}); 10 // List<Integer> params = new ArrayList<Integer>(); 11 // params.add(1); 12 // params.add(2); 13 // params.add(4); 14 // query.setParameterList("bookIds", params); 15 List<Book> list = query.list(); 16 for (Book b : list) { 17 System.out.println(b); 18 } 19 } 20
HQL支持连接查询
1 @Test 2 public void testList7() { 3 Query query = session.createQuery("select o.orderNo,oi.quantity from Order o,OrderItem oi where o = oi.order"); 4 List<Object[]> list = query.list(); 5 for (Object[] b : list) { 6 System.out.println(Arrays.toString(b)); 7 } 8 }
HQL支持聚合函数
1 @Test 2 public void testList8() { 3 Query query = session.createQuery("select count(*) from Book"); 4 Long singleResult = (Long) query.getSingleResult(); 5 System.out.println(singleResult); 6 } 7
HQL分页
1 @Test 2 public void testList9() { 3 Query query = session.createQuery("from Book"); 4 query.setFirstResult(2); 5 query.setMaxResults(3); 6 List<Book> list = query.list(); 7 for (Book b : list) { 8 System.out.println(b); 9 } 10 } 11 }
HQL分页
导入分页工具类:PageBean
原生BaseDao 以及StringUtils
通用查询分页
BookDao
1 /********************通用查询分页***********************/ 2 public List<Book> list1(Book book,PageBean pageBean) { 3 Session session = SessionFactoryUtils.openSession(); 4 Transaction transaction = session.beginTransaction(); 5 6 //下面代码处理的是book实体类的条件查询 7 String bookName = book.getBookName(); 8 //SQL语句where后面可以直接写true,而HQL语句不能出现true 9 String hql = "from Book where 1=1"; 10 if (StringUtils.isNotBlank(bookName)) { 11 hql += "and bookName like :bookName"; 12 } 13 Query query = session.createQuery(hql); 14 if (StringUtils.isNotBlank(bookName)) { 15 query.setParameter("bookName", bookName); 16 } 17 18 //处理分页 19 if (pageBean != null && pageBean.isPagination()) { 20 query.setFetchSize(pageBean.getStartIndex()); 21 query.setMaxResults(pageBean.getRows()); 22 } 23 24 List list = query.list(); 25 26 transaction.commit(); 27 session.close(); 28 return list; 29 30 31 }
测试BookDao中的通用查询分页方法
1 @Test 2 public void testList1() { 3 Book book = new Book(); 4 book.setBookName("%b%"); 5 PageBean pageBean = new PageBean(); 6 7 List<Book> list1 = this.bookDao.list1(book, pageBean); 8 for (Book b : list1) { 9 System.out.println(b); 10 } 11 12 }
1 @Test 2 public void testList1() { 3 Book book = new Book(); 4 //book.setBookName("%b%"); 5 PageBean pageBean = new PageBean(); 6 pageBean.setPage(2); 7 List<Book> list1 = this.bookDao.list1(book, pageBean); 8 for (Book b : list1) { 9 System.out.println(b); 10 } 11 12 } 13
BaseDao编写
1 package com.MavenHibernate.HQL.util; 2 3 import java.util.Collection; 4 import java.util.List; 5 import java.util.Map; 6 import java.util.Map.Entry; 7 import java.util.Set; 8 9 import org.hibernate.Session; 10 import org.hibernate.query.Query; 11 12 import com.MavenHibernate.util.PageBean; 13 14 15 /** 16 * hebernate的通用查询Dao层 17 * 思想完全借鉴于sql的BaseDao 18 * sql=select * from t_mvc_book where bname like '%圣墟%' 19 * countsql=select count(1) from (sql) t; 20 * pagesql=sql limit start,offset 21 * 22 * hql: 23 * hql=selct * from Book where bookName like :bookName 24 * hql=from Book where bookName like :bookName 25 * 思想:原生的hql语句以from关键字进行截取 26 * countHql=select count(*) from Book where bookName like :bookName; 27 * pageHql不需要写:应为Hibernate已经封装了内置的分页接口 28 * getFirstResult 29 * getMaxResult 30 * @author jt 31 * 32 */ 33 public class BaseDao { 34 35 /** 36 * 给hiberbate中Query对象中的命名参数列表赋值 37 * @param query 38 * @param map 39 */ 40 public void setparam(Query query,Map<String, Object> map) { 41 if(map!=null && map.size()>0) { 42 Set<Entry<String,Object>> entrySet = map.entrySet(); 43 44 for (Entry<String, Object> entry : entrySet) { 45 Object value = entry.getValue(); 46 if(value instanceof Collection) 47 query.setParameterList(entry.getKey(), (Collection)value); 48 else if(value instanceof Object[]) 49 50 query.setParameterList(entry.getKey(), (Object[])value); 51 else 52 query.setParameter(entry.getKey(), value); 53 54 } 55 56 57 } 58 } 59 60 /** 61 * hql: 62 * hql=select * from Book where bookName like :bookName 63 * hql=from Book where bookName like :bookName 64 * 思想:将原生态的hql语句以from关键字进行截取 65 * counthql =select count(*) from Book where bookName like :bookName; 66 * @param hql 67 * @return 68 */ 69 public String getCountHql(String hql) { 70 return "select count(*)"+ hql.substring(hql.toUpperCase().indexOf("FROM")); 71 } 72 73 /** 74 * 通用hql语句查询 75 * @param session 当前会话 76 * @param hql 带命名参数的hql语句 77 * @param map 命名参数对应的值的集合 78 * @param pageBean 分页实体类 79 * @return 80 */ 81 public List executeQuery(Session session,String hql,Map<String, Object> map,PageBean pageBean) { 82 List list=null; 83 Query query=null; 84 if(pageBean!=null&&pageBean.isPagination()) { 85 String countHql = getCountHql(hql); 86 Query createQuery = session.createQuery(countHql); 87 this.setparam(createQuery, map); 88 pageBean.setTotal(createQuery.getSingleResult().toString()); 89 query = session.createQuery(hql); 90 this.setparam(query, map); 91 query.setFirstResult(pageBean.getStartIndex()); 92 query.setMaxResults(pageBean.getRows()); 93 list=query.list(); 94 }else { 95 query = session.createQuery(hql); 96 this.setparam(query, map); 97 list=query.list(); 98 } 99 return list; 100 } 101 102 }