HQL查询-分页-条件-连接-过滤使用

HQL(Hibernate Query Language)是hibernate自带的查询语言，进行了面向对象的分装，今天就来学习一下，

新建一个java项目，结构如下：

jar包和hibernate官网使用，参见《Hibernate环境搭建和配置》

实体类Book代码：

package com.myeclipse.pojo;import java.util.Date;public class Book {private int id;private String author;private String name;private double price;private Date pubDate;private Category category;public int getId() {return id;}public void setId(int id) {this.id = id;}public String getAuthor() {return author;}public void setAuthor(String author) {this.author = author;}public String getName() {return name;}public void setName(String name) {this.name = name;}public double getPrice() {return price;}public void setPrice(double price) {this.price = price;}public Date getPubDate() {return pubDate;}public void setPubDate(Date pubDate) {this.pubDate = pubDate;}public Category getCategory() {return category;}public void setCategory(Category category) {this.category = category;}@Overridepublic String toString() {return "Book [id=" + id + ", author=" + author + ", name=" + name+ ", price=" + price + ", pubDate=" + pubDate + "]";}}

Book.hbm.xml代码如下：

<?xml version="1.0" encoding="utf-8"?><!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN""http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd"><hibernate-mapping package="com.myeclipse.pojo"><class name="Book" table="t_book"><id name="id"><generator class="identity" /></id><many-to-one name="category" class="Category" column="category_id" /><property name="author" /><property name="name" column="book_name" /><property name="price" /><property name="pubDate" /><!-- 使用过滤器 --><filter name="bookFilter" condition="id=:id"></filter></class><!-- 过滤器定义 : 定义参数 --><filter-def name="bookFilter"><filter-param name="id" type="integer" /></filter-def></hibernate-mapping>

Category实体类代码：

package com.myeclipse.pojo;import java.util.HashSet;import java.util.Set;public class Category{private int id;private String name;private Set<Book> books = new HashSet<Book>();public int getId() {return id;}public void setId(int id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public Set<Book> getBooks() {return books;}public void setBooks(Set<Book> books) {this.books = books;}}

Category.hbm.xml代码如下：

<?xml version="1.0"?><!DOCTYPE hibernate-mapping PUBLIC    "-//Hibernate/Hibernate Mapping DTD 3.0//EN""http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd"><hibernate-mapping package="com.myeclipse.pojo"><class name="Category" ><id name="id" ><generator class="identity" /></id><property name="name" /><set name="books" inverse="true"><key><column name="category_id" /></key><one-to-many class="Book" /></set></class></hibernate-mapping>

hibernate.cfg.xml代码：

<!DOCTYPE hibernate-configuration PUBLIC"-//Hibernate/Hibernate Configuration DTD 3.0//EN""http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd"><hibernate-configuration><session-factory><!-- 配置数据库连接信息 --><property name="connection.driver_class">com.mysql.jdbc.Driver</property><property name="connection.url">jdbc:mysql:///hibernate4</property><property name="connection.username">root</property><property name="connection.password">root</property><!-- 数据库方言 --><property name="hibernate.dialect">org.hibernate.dialect.MySQL5Dialect</property><!-- 是否打印sql语句 --><property name="show_sql">true</property><!-- 格式化sql语句 --><property name="format_sql">true</property><!-- 数据库更新方式： 1、create：每次更新都先把原有数据库表删除，然后创建该表；2、create-drop:使用create-drop时，在显示关闭SessionFacroty时（sessionFactory.close()），将drop掉数据库Schema（表） 3、validate：检测；4、update（常用）：如果表不存在则创建，如果存在就不创建--><property name="hbm2ddl.auto">update</property><!-- hbm映射文件 --><mapping resource="com/myeclipse/pojo/Book.hbm.xml"/><mapping resource="com/myeclipse/pojo/Category.hbm.xml"/></session-factory></hibernate-configuration>

HibernateUtil代码：

package com.robert.util;import org.hibernate.Session;import org.hibernate.SessionFactory;import org.hibernate.boot.registry.StandardServiceRegistryBuilder;import org.hibernate.cfg.Configuration;/** * hibernate工具类 */public class HibernateUtil {private static Configuration cfg = null;private static SessionFactory factory = null;private static Session session = null ;static {init();}/** * 初始化获得Configuration和SessionFacroty对象 */public static void init() {cfg = new Configuration().configure();factory = cfg.buildSessionFactory(new StandardServiceRegistryBuilder().applySettings(cfg.getProperties()).build());}/** * 获得Session对象 * @return */public static Session getSession() {if (factory != null){return session = factory.openSession();}init();return session = factory.openSession();}/** * 关闭Session */public static void closeSession() {if(session!=null && session.isOpen())session.close();}}

HibernateTest测试类代码，包含创建数据库表，保存数据，查询

package com.ghibernate.test;import java.util.Date;import java.util.List;import org.hibernate.Query;import org.hibernate.Session;import org.hibernate.Transaction;import org.hibernate.cfg.Configuration;import org.hibernate.tool.hbm2ddl.SchemaExport;import org.junit.Test;import com.myeclipse.pojo.Book;import com.myeclipse.pojo.Category;import com.robert.util.HibernateUtil;public class HibernateTest {@Testpublic void testCreateDB() {Configuration cfg = new Configuration().configure();SchemaExport se = new SchemaExport(cfg);// 第一个参数：是否生成ddl脚本// 第二个参数：是否执行到数据库中se.create(true, true);}@Testpublic void testSave() {Session session = HibernateUtil.getSession();Transaction tx = session.beginTransaction();Category category = new Category();category.setName("文学");Category category1 = new Category();category1.setName("历史");Category category2 = new Category();category2.setName("仙侠");Category category3 = new Category();category3.setName("科幻");Category category4 = new Category();category4.setName("恐怖");Book book = new Book();book.setName("读者");book.setPrice(5.6);book.setAuthor("众人");book.setPubDate(new Date());book.setCategory(category);Book book1 = new Book();book1.setName("傲慢与偏见");book1.setPrice(80.0);book1.setAuthor("简.奥斯汀");book1.setPubDate(new Date());book1.setCategory(category1);Book book2 = new Book();book2.setName("中国历史");book2.setPrice(30.0);book2.setAuthor("人民出版社");book2.setPubDate(new Date());book2.setCategory(category1);Book book3 = new Book();book3.setName("翩眇之旅");book3.setPrice(70.0);book3.setAuthor("萧鼎");book3.setPubDate(new Date());book3.setCategory(category2);Book book4 = new Book();book4.setName("蓝血人");book4.setPrice(60.0);book4.setAuthor("卫斯理");book4.setPubDate(new Date());book4.setCategory(category3);Book book5 = new Book();book5.setName("我的大学");book5.setPrice(60.5);book5.setAuthor("高尔基");book5.setPubDate(new Date());book5.setCategory(category);session.save(book);session.save(book1);session.save(book2);session.save(book3);session.save(book4);session.save(book5);session.save(category4);tx.commit();HibernateUtil.closeSession();}@Testpublic void testGet() {Session session = HibernateUtil.getSession();Transaction tx = session.beginTransaction();Book book = (Book) session.get(Book.class, 1);System.out.println("book_name=" + book.getName() + "-----category="+ book.getCategory().getName());tx.commit();HibernateUtil.closeSession();}/** * 查询所有书名 */@Testpublic void testQuery() {Session session = HibernateUtil.getSession();String hql = "select name from Book";Query query = session.createQuery(hql);List<String> list = query.list();for (String bookname : list) {System.out.println(bookname);}}/** * 查询返回多个列 */@Testpublic void testQueryMoreElements() {Session session = HibernateUtil.getSession();String hql = "select name, price from Book";Query query = session.createQuery(hql);// 查询多个列时，返回结果是数组集合，数组中元素的类型是有查询列来决定的List<Object[]> list = query.list();for (Object[] objs : list) {System.out.println(objs[0] + "--------" + objs[1]);}}/** * 查询返回对象 */@Testpublic void testQueryObject() {Session session = HibernateUtil.getSession();Transaction tx = session.beginTransaction();String hql = "select new Book(name, price) from Book";Query query = session.createQuery(hql);// 查询多个列时，返回结果是数组集合，数组中元素的类型是有查询列来决定的List<Book> list = query.list();for (Book book : list) {System.out.println(book);}tx.commit();HibernateUtil.closeSession();}/** * 查询所有列 */@Testpublic void testQueryAll() {Session session = HibernateUtil.getSession();Transaction tx = session.beginTransaction();String hql = " from Book";Query query = session.createQuery(hql);List<Book> list = query.list();for (Book book : list) {System.out.println(book);}tx.commit();HibernateUtil.closeSession();}/** * 条件查询:使用占位符，从0开始 */@Testpublic void testQueryWhereConfition() {try {Session session = HibernateUtil.getSession();Transaction tx = session.beginTransaction();String hql = " from Book where id < ? or price < ?";// String hql = " from Book where id < ? and price < ?" ;// setInteger：第一个参数是0表示第一个从占位符，第二个参数表示第一个占位符的值// setDouble：第一个参数是1，表示第二个占位符，第二个参数表示第二个占位符的值Query query = session.createQuery(hql).setInteger(0, 4).setDouble(1, 400);;List<Book> list = query.list();for (Book book : list) {System.out.println(book);}tx.commit();HibernateUtil.closeSession();} catch (Exception e) {e.printStackTrace();}}/** * 条件查询:使用占位符，从0开始 */@Testpublic void testQueryWhereSetParameter() {try {Session session = HibernateUtil.getSession();Transaction tx = session.beginTransaction();String hql = " from Book where id < ? ";// setParameter不用管参数的类型Query query = session.createQuery(hql).setParameter(0, 4);List<Book> list = query.list();for (Book book : list) {System.out.println(book);}tx.commit();HibernateUtil.closeSession();} catch (Exception e) {e.printStackTrace();}}/** * 条件查询之命名查询,以冒号开头，后跟名称，在setParameter时，将该名称放进去即可 */@Testpublic void testQueryWhereSetName() {try {Session session = HibernateUtil.getSession();Transaction tx = session.beginTransaction();String hql = " from Book where id <:id ";// setParameter不用管参数的类型Query query = session.createQuery(hql).setParameter("id", 4);List<Book> list = query.list();for (Book book : list) {System.out.println(book);}tx.commit();HibernateUtil.closeSession();} catch (Exception e) {e.printStackTrace();}}/** * 分页查询 */@Testpublic void testQueryPaging() {try {Session session = HibernateUtil.getSession();Transaction tx = session.beginTransaction();String hql = " from Book ";// setFirstResults:数据从第几个开始显示（currentPage-1）*PageSize// setMaxResults：每页显示的数据数量PageSizeQuery query = session.createQuery(hql).setFirstResult(3).setMaxResults(3);List<Book> list = query.list();for (Book book : list) {System.out.println(book);}tx.commit();HibernateUtil.closeSession();} catch (Exception e) {e.printStackTrace();}}/** * 聚合函数----统计查询 * 结果唯一 */@Testpublic void testQueryStatistics() {try {Session session = HibernateUtil.getSession();Transaction tx = session.beginTransaction();String hql = " select count(b.name) from Book b ";Object count = session.createQuery(hql).uniqueResult() ;System.out.println("总数："+count);tx.commit();HibernateUtil.closeSession();} catch (Exception e) {e.printStackTrace();}}/** * 分组查询 */@Testpublic void testQueryGroupBy() {try {Session session = HibernateUtil.getSession();Transaction tx = session.beginTransaction();String hql = " select b.category.name  , count(b.id) from Book b group by b.category.name ";List<Object[]> list = session.createQuery(hql).list() ;for (Object[] objs : list) {System.out.println(objs[0]+"---"+objs[1]);}tx.commit();HibernateUtil.closeSession();} catch (Exception e) {e.printStackTrace();}}/** * 排序查询 */@Testpublic void testQueryOrderby() {try {Session session = HibernateUtil.getSession();Transaction tx = session.beginTransaction();String hql = " from Book b Order by b.price desc ";List<Book> list = session.createQuery(hql).list() ;for (Book book : list) {System.out.println(book);}tx.commit();HibernateUtil.closeSession();} catch (Exception e) {e.printStackTrace();}}/** * 对象导航 */@Testpublic void testQueryNavigation() {try {Session session = HibernateUtil.getSession();Transaction tx = session.beginTransaction();//查询“仙侠”类的书籍信息String hql = " from Book b where b.category.name =:name  ";hql = " select b from Book b join b.category c where c.name =:name" ;hql = " select b from Book b inner join b.category c where c.name =:name" ;List<Book> list = session.createQuery(hql).setString("name", "仙侠").list() ;for (Book book : list) {System.out.println(book);}tx.commit();HibernateUtil.closeSession();} catch (Exception e) {e.printStackTrace();}}/** * 左外连接 */@Testpublic void testQueryLeftJoin() {try {Session session = HibernateUtil.getSession();Transaction tx = session.beginTransaction();//查询“仙侠”类的书籍信息String hql = " select c.name , b.name from Category c left outer join c.books b ";List<Object[]> list = session.createQuery(hql).list() ;for (Object[] objs : list) {System.out.println(objs[0]+"---"+objs[1]);}tx.commit();HibernateUtil.closeSession();} catch (Exception e) {e.printStackTrace();}}/** * 过滤器的使用--过滤查询--为查询加上某些条件 * 过滤器的步骤： * 1、定义过滤器； * 2、使用过滤器-加条件； * 3、查询时，是过滤器生效 */@Testpublic void testQueryFilter() {try {Session session = HibernateUtil.getSession();Transaction tx = session.beginTransaction();//启用过滤器session.enableFilter("bookFilter").setParameter("id", 4) ;//查询“仙侠”类的书籍信息String hql = " from Book b ";List<Book> list = session.createQuery(hql).list() ;for (Book book : list) {System.out.println(book);}tx.commit();HibernateUtil.closeSession();} catch (Exception e) {e.printStackTrace();}}}

具体的结果自己运行一下就可以了。