hibernate--HQL
hibernate--HQL
1. 什么是hql
HQL是Hibernate Query Language的缩写,提供更加丰富灵活、更为强大的查询能力;
HQL更接近SQL语句查询语法。
2. hql和sql区别/异同
HQL SQL
类名/属性 表名/列名
区分大小写,关键字不区分大小写 不区分大小写
别名 别名
?,从下标0开始计算位置(hibernate5之后不支持) ?,从顺序1开始计算位置
:命名参数 不支持:命名参数
面向对象的查询语言 面向结构查询语言
3. 处理返回的结果集
3.1 单个对象
select没有逗号
3.2 Object[],一般返回的是两个或多个列段
b.bookId, b.bookName
3.3 Map
new Map(b.bookId as bid, b.bookName as bname)
3.4 new 构造方法(attr1,attr2)
new Book(b.bookId, b.price)
单个列段
3.5 测试代码
1 package com.yuan.five.test; 2 3 import java.util.Arrays; 4 import java.util.List; 5 import java.util.Map; 6 7 import org.hibernate.Session; 8 import org.hibernate.Transaction; 9 import org.hibernate.query.Query; 10 import org.junit.After; 11 import org.junit.Before; 12 import org.junit.Test; 13 14 import com.yuan.four.entity.Book; 15 import com.yuan.two.util.SessionFactoryUtils; 16 17 public class HqlTest { 18 private Session session; 19 private Transaction transaction; 20 21 @Before 22 public void before() { 23 session = SessionFactoryUtils.openSession(); 24 transaction = session.beginTransaction(); 25 } 26 27 @After 28 public void after() { 29 transaction.commit(); 30 session.close(); 31 } 32 33 /** 34 * 返回对象(多个) 35 */ 36 @Test 37 public void testList1() { 38 Query query = session.createQuery("from Book"); 39 List<Book> list = query.list(); 40 for (Book b : list) { 41 System.out.println(b); 42 } 43 } 44 45 /** 46 * 返回单个列段,用字符串就可以接受 47 */ 48 @Test 49 public void testList2() { 50 Query query = session.createQuery("select b.bookName as ss from Book b"); 51 List<String> list = query.list(); 52 for (String b : list) { 53 System.out.println(b); 54 } 55 } 56 57 /** 58 * 查两个列段及以上,默认返回的是Object【】 59 */ 60 @Test 61 public void testList3() { 62 Query query = session.createQuery("select b.bookId,b.bookName as ss from Book b"); 63 List<Object[]> list = query.list(); 64 for (Object[] b : list) { 65 System.out.println(Arrays.toString(b)); 66 } 67 } 68 69 /** 70 * 注意map是函数,所以不区分大小写,返回的是map集合 71 */ 72 @Test 73 public void testList4() { 74 Query query = session.createQuery("select new mAp(b.bookId,b.bookName) from Book b"); 75 List<Map> list = query.list(); 76 for (Map b : list) { 77 System.out.println(b); 78 } 79 } 80 81 /** 82 * 查两个列段及以上,也可返回对象,前提是有对应的构造函数 83 */ 84 @Test 85 public void testList5() { 86 Query query = session.createQuery("select new Book(b.bookId,b.bookName) from Book b"); 87 List<Book> list = query.list(); 88 for (Book b : list) { 89 System.out.println(b); 90 } 91 } 92 93 /** 94 * HQL语句支持占位符 95 * 96 * query对象相当于perparestatement 97 */ 98 @Test 99 public void testList6() { 100 // Query query = session.createQuery("from Book where bookId = :bookId"); 101 // query.setParameter("bookId", 1); 102 // Book b = (Book) query.getSingleResult(); 103 // System.out.println(b); 104 105 Query query = session.createQuery("from Book where bookId in (:bookIds)"); 106 query.setParameterList("bookIds", new Integer[] {1,2,4}); 107 // List<Integer> params = new ArrayList<Integer>(); 108 // params.add(1); 109 // params.add(2); 110 // params.add(4); 111 // query.setParameterList("bookIds", params); 112 List<Book> list = query.list(); 113 for (Book b : list) { 114 System.out.println(b); 115 } 116 } 117 118 /** 119 * HQL支持连接查询 120 */ 121 @Test 122 public void testList7() { 123 Query query = session.createQuery("select o.orderNo,oi.quantity from Order o,OrderItem oi where o = oi.order"); 124 List<Object[]> list = query.list(); 125 for (Object[] b : list) { 126 System.out.println(Arrays.toString(b)); 127 } 128 } 129 130 /** 131 * HQL支持聚合函数 132 * 如: 133 * count、sum、avg 134 */ 135 @Test 136 public void testList8() { 137 Query query = session.createQuery("select count(*) from Book"); 138 //getSingleResult:查询返回单行单列的数据 139 Long singleResult = (Long) query.getSingleResult(); 140 System.out.println(singleResult); 141 } 142 143 /** 144 * sql=select * from t_hibernate_book 145 * sql+limit start,offset 146 * 1 1,2 147 * 2 10,10 148 * 3 20,10 149 * 150 * FirstResult:代表起始下标 start 151 * MaxResults:代表页大小 offset 152 * HQL分页 153 */ 154 @Test 155 public void testList9() { 156 Query query = session.createQuery("from Book"); 157 query.setFirstResult(2); 158 query.setMaxResults(3); 159 List<Book> list = query.list(); 160 for (Book b : list) { 161 System.out.println(b); 162 } 163 } 164 }
4. hql的分页查询
4.1 PageBean
package com.yuan.four.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 + "]"; } }
4.2 StringUtils
1 package com.yuan.five.util; 2 3 public class StringUtils { 4 // 私有的构造方法,保护此类不能在外部实例化 5 private StringUtils() { 6 } 7 8 /** 9 * 如果字符串等于null或去空格后等于"",则返回true,否则返回false 10 * 11 * @param s 12 * @return 13 */ 14 public static boolean isBlank(String s) { 15 boolean b = false; 16 if (null == s || s.trim().equals("")) { 17 b = true; 18 } 19 return b; 20 } 21 22 /** 23 * 如果字符串不等于null或去空格后不等于"",则返回true,否则返回false 24 * 25 * @param s 26 * @return 27 */ 28 public static boolean isNotBlank(String s) { 29 return !isBlank(s); 30 } 31 32 }
4.3 BookDao
1 package com.yuan.four.dao; 2 3 4 import java.util.HashMap; 5 import java.util.List; 6 import java.util.Map; 7 8 import org.hibernate.Hibernate; 9 import org.hibernate.Session; 10 import org.hibernate.Transaction; 11 import org.hibernate.query.Query; 12 13 import com.yuan.five.util.BaseDao; 14 import com.yuan.four.entity.Book; 15 import com.yuan.four.entity.Category; 16 import com.yuan.four.util.PageBean; 17 import com.yuan.four.util.StringUtils; 18 import com.yuan.two.util.SessionFactoryUtils; 19 20 21 public class BookDao extends BaseDao { 22 public Integer addBook(Book book) { 23 Session session = SessionFactoryUtils.openSession(); 24 Transaction transaction = session.beginTransaction(); 25 Integer bid = (Integer) session.save(book); 26 transaction.commit(); 27 session.close(); 28 return bid; 29 } 30 31 public Integer addCategory(Category category) { 32 Session session = SessionFactoryUtils.openSession(); 33 Transaction transaction = session.beginTransaction(); 34 Integer cid = (Integer) session.save(category); 35 transaction.commit(); 36 session.close(); 37 return cid; 38 } 39 40 public Category getCategory(Category category) { 41 Session session = SessionFactoryUtils.openSession(); 42 Transaction transaction = session.beginTransaction(); 43 Category c = session.get(Category.class, category.getCategoryId()); 44 transaction.commit(); 45 session.close(); 46 return c; 47 } 48 49 public Book getBook(Book book) { 50 Session session = SessionFactoryUtils.openSession(); 51 Transaction transaction = session.beginTransaction(); 52 Book b = session.get(Book.class, book.getBookId()); 53 if (b != null && new Integer(1).equals(book.getInitCategories())) { 54 Hibernate.initialize(b.getCategories()); 55 } 56 transaction.commit(); 57 session.close(); 58 return b; 59 } 60 61 public void delBook(Book book) { 62 Session session = SessionFactoryUtils.openSession(); 63 Transaction transaction = session.beginTransaction(); 64 session.delete(book); 65 transaction.commit(); 66 session.close(); 67 } 68 69 public void delCategory(Category category) { 70 Session session = SessionFactoryUtils.openSession(); 71 Transaction transaction = session.beginTransaction(); 72 Category c = session.get(Category.class, category.getCategoryId()); 73 if(c!=null) { 74 for (Book b : c.getBooks()) { 75 // 通过在被控方通过主控方来解除关联关系,最后被控方再做删除 76 b.getCategories().remove(c); 77 } 78 } 79 session.delete(c); 80 transaction.commit(); 81 session.close(); 82 } 83 /*******************通用查询******************** 84 */ 85 public List<Book> list1(Book book, PageBean pageBean){ 86 Session session = SessionFactoryUtils.openSession(); 87 Transaction transaction = session.beginTransaction(); 88 //下面代码处理的是bookName实体类的条件查询 89 String bookName = book.getBookName(); 90 //sql语句where后面可以直接写true,而hql语句不能出现true, 91 String hql = "from Book where 1=1 "; 92 if(StringUtils.isNotBlank(bookName)) { 93 hql += " and bookName like :bookName"; 94 } 95 Query query = session.createQuery(hql); 96 if(StringUtils.isNotBlank(bookName)) { 97 query.setParameter("bookName", bookName); 98 } 99 //处理分页 100 if(pageBean != null && pageBean.isPagination()) { 101 query.setFirstResult(pageBean.getStartIndex()); 102 query.setMaxResults(pageBean.getRows()); 103 } 104 105 List list = query.list(); 106 107 108 transaction.commit(); 109 session.close(); 110 return list; 111 } 112 113 114 public List<Book> list2(Book book, PageBean pageBean){ 115 Session session = SessionFactoryUtils.openSession(); 116 Transaction transaction = session.beginTransaction(); 117 118 //下面代码处理的是bookName实体类的条件查询 119 String bookName = book.getBookName(); 120 Map<String, Object> map = new HashMap<String, Object>(); 121 //sql语句where后面可以直接写true,而hql语句不能出现true, 122 String hql = "from Book where 1=1 "; 123 if(StringUtils.isNotBlank(bookName)) { 124 hql += " and bookName like :bookName"; 125 map.put("bookName", bookName); 126 } 127 List list =super.executeQuery(session, hql, map, pageBean); 128 transaction.commit(); 129 session.close(); 130 return list; 131 } 132 133 134 135 }
4.4 最后是本篇的重点 BaseDao
1 package com.yuan.five.util; 2 3 import java.util.ArrayList; 4 import java.util.Collection; 5 import java.util.List; 6 import java.util.Map; 7 import java.util.Map.Entry; 8 import java.util.Set; 9 10 import org.hibernate.Session; 11 import org.hibernate.query.Query; 12 13 import com.yuan.four.util.PageBean; 14 15 /** 16 * hibernate的通用查询dao层 17 * 18 * 思想完全借鉴于sql的BaseDao 19 * sql = select * from t_mvc_book where bname like '%圣墟%' 20 * countSql = select count(1) from (sql)t; 21 * pageSql = sql limit start,offset 22 * 23 * hql: 24 * hql = select * from Book where bookName like :bookName 25 * hql = from Book where bookName like :bookName 26 * 将原生的hql语句以from关键字进行截取 27 * countHql = select count(*) from Book where bookName like :bookName 28 * pageHql不需要写,hibernate已经封装了内置的分页接口 29 * getFirstResult 30 * getMaxResult 31 * @author ly
32 * 33 */ 34 public class BaseDao { 35 36 /** 37 * 给hibernate中Query对象中的命名参数列表赋值 38 * @param query 39 * @param map 40 */ 41 public void setParam(Query query,Map<String, Object> map) { 42 if(map !=null && map.size()>0) { 43 Set<Entry<String, Object>> entrySet = map.entrySet(); 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 query.setParameterList(entry.getKey(), (Collection)value); 50 else 51 query.setParameter(entry.getKey(), value); 52 } 53 } 54 } 55 56 /** 57 * hql = select * from Book where bookName like :bookName 58 * hql = from Book where bookName like :bookName 59 * 将原生的hql语句以from关键字进行截取 60 * countHql = select count(*) from Book where bookName like :bookName 61 * @param hql 62 * @return 63 */ 64 public String getCountHql(String hql) { 65 return "select count(*) " + hql.substring(hql.toUpperCase().indexOf("FROM")); 66 } 67 68 /** 69 * 通用hql语句查询 70 * @param session 当前会话 71 * @param hql 带命名参数的hql语句 72 * @param map 命名参数对应的值的集合 73 * @param pageBean 分页实体类 74 * @return 75 */ 76 public List executeQuery(Session session,String hql,Map<String, Object> map,PageBean pageBean ) { 77 List list = null; 78 Query query = null; 79 if(pageBean != null && pageBean.isPagination()) { 80 String countHql = getCountHql(hql); 81 Query countQuery = session.createQuery(countHql); 82 this.setParam(countQuery, map); 83 pageBean.setTotal(countQuery.getSingleResult().toString()); 84 85 query = session.createQuery(hql); 86 this.setParam(query, map); 87 query.setFirstResult(pageBean.getStartIndex()); 88 query.setMaxResults(pageBean.getRows()); 89 list = query.list(); 90 }else { 91 query = session.createQuery(hql); 92 this.setParam(query, map); 93 list = query.list(); 94 } 95 return null; 96 } 97 98 99 100 }
4.6 测试类
1 package com.yuan.four.dao; 2 3 import java.util.List; 4 5 import org.junit.Test; 6 7 import com.yuan.five.util.BaseDao; 8 import com.yuan.four.entity.Book; 9 import com.yuan.four.entity.Category; 10 import com.yuan.four.util.PageBean; 11 12 13 public class BookDaoTest { 14 private BookDao bookDao = new BookDao(); 15 16 @Test 17 public void testGetBook() { 18 Book book = new Book(); 19 book.setBookId(8); 20 book.setInitCategories(1); 21 Book b = this.bookDao.getBook(book ); 22 System.out.println(b.getBookName()); 23 System.out.println(b.getCategories()); 24 } 25 26 /** 27 * book.hbm.xml inverse=false 28 * category.hbm.xml inverse=true 29 * 数据添加正常 30 * 书籍表、桥接表各新增一条数据 31 */ 32 @Test 33 public void test1() { 34 Book book = new Book(); 35 book.setBookName("yuan"); 36 book.setPrice(10f); 37 Category category = new Category(); 38 category.setCategoryId(5); 39 // 直接将category对象加入到新建的book中是错误的,因为此时的category是临时态的,hibernate是不会管理的 40 // book.getCategories().add(category); 41 Category c = this.bookDao.getCategory(category); 42 43 // c.getBooks().add(book); 44 book.getCategories().add(c); 45 this.bookDao.addBook(book); 46 } 47 48 /** 49 * book.hbm.xml inverse=true 50 * category.hbm.xml inverse=true 51 * 只增加书籍表数据 52 * 桥接表不加数据 53 * 原因:双方都没有去维护关系 54 */ 55 @Test 56 public void test2() { 57 Book book = new Book(); 58 book.setBookName("yuan2"); 59 book.setPrice(10f); 60 Category category = new Category(); 61 category.setCategoryId(5); 62 Category c = this.bookDao.getCategory(category); 63 64 book.getCategories().add(c); 65 this.bookDao.addBook(book); 66 // c.getBooks().add(book); 67 } 68 69 @Test 70 public void testList1() { 71 Book book = new Book(); 72 //查询 73 // book.setBookName("%yuan%"); 74 PageBean pageBean = new PageBean(); 75 //分页的第几页 76 pageBean.setPage(2); 77 List<Book> list1 = this.bookDao.list1(book, pageBean); 78 for (Book b : list1) { 79 System.out.println(b); 80 } 81 } 82 83 84 @Test 85 public void testList2() { 86 Book book = new Book(); 87 // book.setBookName("%yuan%"); 88 PageBean pageBean = new PageBean(); 89 pageBean.setPage(2); 90 List<Book> list1 = this.bookDao.list1(book, pageBean); 91 for (Book b : list1) { 92 System.out.println(b); 93 } 94 } 95 96 97 }
testList1跟testList2的区别是testList1测试的时候BookDao不需要继承BaseDao
而testList2测试则是依据BaseDao来实现的查询分页,BookDao需要继承BaseDao,所以我们两个方法运行的结果是一样的
谢谢观看!!!