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;
	}
	@Override
	public 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 {

	@Test
	public void testCreateDB() {
		Configuration cfg = new Configuration().configure();
		SchemaExport se = new SchemaExport(cfg);
		// 第一个参数：是否生成ddl脚本
		// 第二个参数：是否执行到数据库中
		se.create(true, true);
	}

	@Test
	public 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();

	}

	@Test
	public 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();

	}

	/**
	 * 查询所有书名
	 */
	@Test
	public 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);
		}

	}

	/**
	 * 查询返回多个列
	 */
	@Test
	public 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]);
		}

	}

	/**
	 * 查询返回对象
	 */
	@Test
	public 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();

	}

	/**
	 * 查询所有列
	 */
	@Test
	public 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开始
	 */
	@Test
	public 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开始
	 */
	@Test
	public 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时，将该名称放进去即可
	 */
	@Test
	public 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();
		}

	}

	/**
	 * 分页查询
	 */
	@Test
	public void testQueryPaging() {
		try {
			Session session = HibernateUtil.getSession();
			Transaction tx = session.beginTransaction();
			String hql = " from Book ";
			// setFirstResults:数据从第几个开始显示（currentPage-1）*PageSize
			// setMaxResults：每页显示的数据数量PageSize
			Query 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();
		}

	}

	/**
	 * 聚合函数----统计查询
	 * 结果唯一
	 */
	@Test
	public 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();
		}

	}


	/**
	 * 分组查询
	 */
	@Test
	public 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();
		}

	}
	
	/**
	 * 排序查询
	 */
	@Test
	public 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();
		}

	}

	/**
	 * 对象导航
	 */
	@Test
	public 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();
		}

	}
	
	/**
	 * 左外连接
	 */
	@Test
	public 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、查询时，是过滤器生效
	 */
	@Test
	public 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();
		}

	}

	
}

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