Hibernate的hql查询语句详细总结

   Hql即Hibernate Query Language,是一个面向对象的查询语言，是基于对象进行查询的，不是基于数据库中表，字段的。

1、准备

首先有两个类TBook类和Category类，两者之间是多对一的关系。

两个类对应两张表，我们先保存一些数据。

@Testpublic void save() {    Session session = null;    Transaction tran = null;    try {        session = SessionUtil.getSession();        tran = session.beginTransaction();        Category c1 = new Category();        c1.setCategory("文学");        Category c2 = new Category();        c2.setCategory("历史");        Category c3 = new Category();        c3.setCategory("武侠");        Category c4 = new Category();        c4.setCategory("科幻");        Category c5 = new Category();        c5.setCategory("言情");        TBook book1 = new TBook();        book1.setBName("红楼梦");        book1.setAuthor("曹雪芹");        book1.setBPrice(60);        book1.setPubDate(new Date());        book1.setCategory(c1);        TBook book2 = new TBook();        book2.setBName("资治通鉴");        book2.setAuthor("司马光");        book2.setBPrice(63);        book2.setPubDate(new Date());        book2.setCategory(c2);        TBook book3 = new TBook();        book3.setBName("射雕英雄传");        book3.setAuthor("金庸");        book3.setBPrice(73);        book3.setPubDate(new Date());        book3.setCategory(c3);        TBook book4 = new TBook();        book4.setBName("百年孤独");        book4.setAuthor("马尔克斯");        book4.setBPrice(56);        book4.setPubDate(new Date());        book4.setCategory(c4);        TBook book5 = new TBook();        book5.setBName("傲慢与偏见");        book5.setAuthor("简奥斯丁");        book5.setBPrice(60);        book5.setPubDate(new Date());        book5.setCategory(c5);        TBook book6 = new TBook();        book6.setBName("西游记");        book6.setAuthor("罗贯中");        book6.setBPrice(68);        book6.setPubDate(new Date());        book6.setCategory(c1);        session.save(book1);        session.save(book2);        session.save(book3);        session.save(book4);        session.save(book5);        session.save(book6);        tran.commit();    } catch (Exception ex) {        ex.printStackTrace();    } finally {        session.close();    }}

表中详细数据

2、Hql查询

2.1、查询单一属性

@Testpublic void query1(){    Session session = null;    try {        session = SessionUtil.getSession();        List<String> list = session.createQuery("select BName from TBook").list();        for (String name : list) {            System.out.println(name);        }    } catch (Exception e) {        e.printStackTrace();    }finally{        SessionUtil.close(session);    }}

红楼梦资治通鉴射雕英雄传百年孤独傲慢与偏见西游记

2.2、返回多个属性的查询

@Testpublic void query2(){    Session session = null;    try {        session = SessionUtil.getSession();        List<Object[]> list = session.createQuery("select BName,BPrice from TBook")                            .list();        for(Object[] book : list){            System.out.println("书名："+book[0]+"\t\t价格："+book[1]);        }    } catch (Exception e) {        e.printStackTrace();    }finally{        SessionUtil.close(session);    }}

书名：红楼梦      价格：60书名：资治通鉴     价格：63书名：射雕英雄传    价格：73书名：百年孤独     价格：56书名：傲慢与偏见    价格：60书名：西游记      价格：68

注意查询多个属性的时候，查询到的是一个对象数据的集合。

另一种方式

@Test    public void query3(){        Session session = null;        try {            session = SessionUtil.getSession();            List<TBook> list = session.createQuery("select new TBook(BName,BPrice) from TBook").list();            for(TBook book : list){                System.out.println(book.getBName()+"---"+book.getBPrice());            }        } catch (Exception e) {            e.printStackTrace();        }finally{            SessionUtil.close(session);        }    }

既然Hql中用到了new TBook(BName,BPrice)，那么你的pojo类中必然要有一个这样的构造函数。

2.3、查询全部属性

@Testpublic void query3(){    Session session = null;    try {        session = SessionUtil.getSession();        List<TBook> list = session.createQuery("from TBook").list();        for(TBook book : list){            System.out.println(book);        }    } catch (Exception e) {        e.printStackTrace();    }finally{        SessionUtil.close(session);    }}

TBook [id=1, author=曹雪芹, BName=红楼梦, BPrice=60, pubDate=2017-06-10 14:51:57.0]TBook [id=2, author=司马光, BName=资治通鉴, BPrice=63, pubDate=2017-06-10 14:51:57.0]TBook [id=3, author=金庸, BName=射雕英雄传, BPrice=73, pubDate=2017-06-10 14:51:57.0]TBook [id=4, author=马尔克斯, BName=百年孤独, BPrice=56, pubDate=2017-06-10 14:51:57.0]TBook [id=5, author=简奥斯丁, BName=傲慢与偏见, BPrice=60, pubDate=2017-06-10 14:51:57.0]TBook [id=6, author=罗贯中, BName=西游记, BPrice=68, pubDate=2017-06-10 14:51:57.0]

其中hql也可以写完整的，那样要使用别名，即select t from TBook t

2.4、条件查询之拼接查询语句

@Testpublic void query4(){    Session session = null;    try {        session = SessionUtil.getSession();        List<TBook> list = session.createQuery("from TBook where id<"+4).list();                    for(TBook book : list){            System.out.println(book);           }    } catch (Exception e) {        e.printStackTrace();    }finally{        SessionUtil.close(session);    }}

2.5、条件查询之占位符方式

@Testpublic void query5(){    Session session = null;    try {        session = SessionUtil.getSession();        List<TBook> list = session.createQuery("from TBook where id<?")                            .setInteger(0,3).list();        for(TBook book : list){            System.out.println(book);        }    } catch (Exception e) {        e.printStackTrace();    }finally{        SessionUtil.close(session);    }}

注意下表从0开始

此后的查询结果不再罗列。

2.6、条件查询之基于别名的查询

@Testpublic void query6(){    Session session = null;    try {        session = SessionUtil.getSession();        List<TBook> list = session.createQuery("from TBook where id<:id")                .setParameter("id",4).list();        for(TBook book : list){            System.out.println(book);        }    } catch (Exception e) {        e.printStackTrace();    }finally{        SessionUtil.close(session);    }}

2.7、分页查询

只需要设置起始位置和最大的查询数据个数即可，注意不包括设置的起始位置。

@Testpublic void query7(){    Session session = null;    try {        session = SessionUtil.getSession();        List<TBook> list = session.createQuery("from TBook")                .setFirstResult(0).setMaxResults(4).list();        for(TBook book : list){            System.out.println(book);        }    } catch (Exception e) {        e.printStackTrace();    }finally{        SessionUtil.close(session);    }}

结果：

TBook [id=1, author=曹雪芹, BName=红楼梦, BPrice=60, pubDate=2017-06-10 14:51:57.0]TBook [id=2, author=司马光, BName=资治通鉴, BPrice=63, pubDate=2017-06-10 14:51:57.0]TBook [id=3, author=金庸, BName=射雕英雄传, BPrice=73, pubDate=2017-06-10 14:51:57.0]TBook [id=4, author=马尔克斯, BName=百年孤独, BPrice=56, pubDate=2017-06-10 14:51:57.0]

2.8、聚合查询之count

@Testpublic void query8(){    Session session = null;    try {        session = SessionUtil.getSession();        //Object count = session.createQuery("select count(*) from TBook").uniqueResult();        Number count = (Number)session.createQuery("select count(*) from TBook").uniqueResult();        System.out.println(count.intValue());    } catch (Exception e) {        e.printStackTrace();    }finally{        SessionUtil.close(session);    }}

2.9、聚合查询之avg

@Testpublic void query9(){    Session session = null;    try {        session = SessionUtil.getSession();        Number priceavg = (Number)session.createQuery("select avg(BPrice) from TBook").uniqueResult();        System.out.println(priceavg.doubleValue());    } catch (Exception e) {        e.printStackTrace();    }finally{        SessionUtil.close(session);    }}

2.10、排序查询

@Testpublic void query10(){    Session session = null;    try {        session = SessionUtil.getSession();        List<TBook> list = session.createQuery("from TBook order by BPrice").list();        for(TBook book : list){            System.out.println(book);        }    } catch (Exception e) {        e.printStackTrace();    }finally{        SessionUtil.close(session);    }}

结果：

TBook [id=4, author=马尔克斯, BName=百年孤独, BPrice=56, pubDate=2017-06-10 14:51:57.0]TBook [id=1, author=曹雪芹, BName=红楼梦, BPrice=60, pubDate=2017-06-10 14:51:57.0]TBook [id=5, author=简奥斯丁, BName=傲慢与偏见, BPrice=60, pubDate=2017-06-10 14:51:57.0]TBook [id=2, author=司马光, BName=资治通鉴, BPrice=63, pubDate=2017-06-10 14:51:57.0]TBook [id=6, author=罗贯中, BName=西游记, BPrice=68, pubDate=2017-06-10 14:51:57.0]TBook [id=3, author=金庸, BName=射雕英雄传, BPrice=73, pubDate=2017-06-10 14:51:57.0]

2.11、分组查询

@Testpublic void query11(){    Session session = null;    try {        session = SessionUtil.getSession();        List<Object[]> list = session.createQuery("select t.category.category,count(t.BName) from TBook t "                + "group by t.category.category").list();        for (Object[] book : list) {            System.out.println(book[0]+"-----"+book[1]);        }    } catch (Exception e) {        e.printStackTrace();    }finally{        SessionUtil.close(session);    }}

结果：

历史-----1文学-----2武侠-----1科幻-----1言情-----1