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();
}
}
}

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