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，所以我们两个方法运行的结果是一样的

谢谢观看！！！