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

小鱼儿 2022-06-12 10:47 245阅读 0赞

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

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

![Center][]

jar包和hibernate官网使用，参见《[*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>
    	
    	<property name="show_sql">true</property>
    	
    	<property name="format_sql">true</property>
    	
    	<property name="hbm2ddl.auto">update</property>
    	
    	<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();
    		}
    
    	}
    
    	
    }

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

[Center]: /images/20220612/91ef3300c9704083947889b9ddd34d05.png
[Hibernate]: http://blog.csdn.net/u011159417/article/details/75014665