HQL查询-分页-条件-连接-过滤使用 小鱼儿 2022-06-12 10:47 154阅读 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> <!-- 是否打印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(); } } } 具体的结果自己运行一下就可以了。 [Center]: /images/20220612/91ef3300c9704083947889b9ddd34d05.png [Hibernate]: http://blog.csdn.net/u011159417/article/details/75014665
还没有评论,来说两句吧...