【9.0】hql

 

备注：如果觉得本文太繁杂，可以点链接查看word文档笔记：链接：http://pan.baidu.com/s/1eR4VbxC 密码：u4vl

1、  hql hibernate query language hibernate的查询语言

 

2、  hql是完全面向对象的查询语言，可以理解继承，多态，关联等概念

 

3、  hql区分大小写。但是对应sql关键字（select,from where）不区分

 

4.Book类和Category类【如果使用注解】

package cn.siggy.pojo;

import java.util.Date;

import javax.persistence.CascadeType;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.Table;
//entity表示需要持久化的实体类
@Entity
//实体类 所对应的表
@Table
public class Book {
	//id主键
	@Id
	//指定 主键生成策略
	@GeneratedValue(strategy=GenerationType.AUTO)
	private int id;
	private String name;
	private double price;
	private String author;
	private Date pubDate;
	@ManyToOne(cascade=CascadeType.ALL)
	@JoinColumn(name="category_id")
	private Category category;
        /*get/set*/
}

package cn.siggy.pojo;

import java.util.HashSet;
import java.util.Set;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import javax.persistence.Table;

@Entity
@Table
public class Category {
	@Id
	@GeneratedValue(strategy=GenerationType.AUTO)
	private int id;
	private String name;
	@OneToMany(mappedBy="category")
	private Set<Book> books = new HashSet<Book>();
	/*get/set*/
}

　　

5.hibernate.cfg.xml【使用注解时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>
	<!--hibernate可选项 -->
	<property name="dialect">
		org.hibernate.dialect.MySQLDialect
	</property>
	<property name="show_sql">true</property>
	<property name="format_sql">true</property>
	<property name="hbm2ddl.auto">update</property>
	<!-- hbm文件 -->
	<mapping class="cn.siggy.pojo.Book" />
	<mapping class="cn.siggy.pojo.Category" />
</session-factory>
</hibernate-configuration>

　　

6.如果不使用注解【则必须配置*.hbm.xml映射文件】

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">
<!-- 
    Mapping file autogenerated by MyEclipse Persistence Tools
-->
<hibernate-mapping>
    <class name="cn.siggy.pojo.Book" table="book" catalog="hibernate4">
        <id name="id" type="java.lang.Integer">
            <column name="id" />
            <generator class="identity" />
        </id>
        <many-to-one name="category" class="cn.siggy.pojo.Category" fetch="select">
            <column name="category_id" />
        </many-to-one>
        <property name="author" type="java.lang.String">
            <column name="author" />
        </property>
        <property name="name" type="java.lang.String">
            <column name="name" />
        </property>
        <property name="price" type="java.lang.Double">
            <column name="price" precision="22" scale="0" not-null="true" />
        </property>
        <property name="pubDate" type="java.sql.Timestamp">
            <column name="pubDate" length="19" />
        </property>
        <!-- 使用过滤器 -->
        <filter name="bookFilter" condition="id=:id"></filter>
    </class>
     <!-- 命名查询  hql语句 名称不能重复，在整个项目中-->
    <query name="getByCategoryId">
    	<![CDATA[
    	from Book b where b.category.id=:id
    	]]>
    </query>
    <sql-query name="getAll">
    	<return alias="book" class="cn.siggy.pojo.Book"></return>
	    <![CDATA[
	    	select *
	    	 from book b;
	    ]]>
    </sql-query>
    <!-- 过滤器定义:定义参数 -->
    <filter-def name="bookFilter">
    	<filter-param name="id" type="integer"/>
    </filter-def>
   
</hibernate-mapping>

　　

Category.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">
<!-- 
    Mapping file autogenerated by MyEclipse Persistence Tools
-->
<hibernate-mapping>
    <class name="cn.siggy.pojo.Category" table="category" catalog="hibernate4">
        <id name="id" type="java.lang.Integer">
            <column name="id" />
            <generator class="identity" />
        </id>
        <property name="name" type="java.lang.String">
            <column name="name" />
        </property>
        <set name="books" inverse="true">
            <key>
                <column name="category_id" />
            </key>
            <one-to-many class="cn.siggy.pojo.Book" />
        </set>
    </class>
</hibernate-mapping>

　　

7.测试代码

package cn.siggy.test;

import java.util.ArrayList;
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.criterion.Example;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.hibernate.tool.hbm2ddl.SchemaExport;
import org.junit.Test;

import cn.siggy.pojo.Book;
import cn.siggy.pojo.Category;
import cn.siggy.util.HibernateUtil;
@SuppressWarnings("unchecked")
public class HibernateTest {
	@Test
	public void testCreateDB(){
		//3.x
		//Configuration cfg = new AnnotationConfiguration().configure();
		Configuration cfg = new Configuration().configure();
		SchemaExport se = new SchemaExport(cfg);
		se.create(true, true);
	}
	@Test
	public void testSave(){
		Session session = HibernateUtil.getSession();
		
		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(60.5);
		book.setAuthor("莫言");
		book.setPubDate(new Date());
		book.setCategory(category);
		
		Book book1 = new Book();
		book1.setName("傲慢与偏见");
		book1.setPrice(80);
		book1.setAuthor("简.奥斯汀");
		book1.setPubDate(new Date());
		book1.setCategory(category1);
		
		Book book2 = new Book();
		book2.setName("中国历史");
		book2.setPrice(30);
		book2.setAuthor("人民");
		book2.setPubDate(new Date());
		book2.setCategory(category1);
		
		Book book3 = new Book();
		book3.setName("飘渺之旅");
		book3.setPrice(70);
		book3.setAuthor("萧鼎");
		book3.setPubDate(new Date());
		book3.setCategory(category2);
		Book book4 = new Book();
		book4.setName("蓝血人");
		book4.setPrice(60);
		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);
		
		
		
		Transaction tx = session.beginTransaction();
		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.getName()+"---"+book.getCategory().getName());
		
		tx.commit();
		HibernateUtil.closeSession();
	}
	//hql--hibernate query language hibernate查询语言
	//查询单个属性 
	@Test
	public void testQuery1(){
		//查询所有书名
		//创建Query对象
		Session session = HibernateUtil.getSession();
		Transaction tx = session.beginTransaction();
		//Book 大写 表示的是  cn.siggy.pojo.Book类
		//name表示的 Book类中的属性名
		String hql="select name from Book";
		Query query = session.createQuery(hql);
		//list()方法返回查询结果
		//返回结果的类型 是根据查询的列决定的
		List<String> list = query.list();
		for(String bookname:list){
			System.out.println(bookname);
		}
		tx.commit();
		HibernateUtil.closeSession();
	}
	//查询多个属性
	@Test
	public void testQuery2(){
		//查询所有书 的名称和价格
		//创建Query对象
		Session session = HibernateUtil.getSession();
		Transaction tx = session.beginTransaction();
		//Book 大写 表示的是  cn.siggy.pojo.Book类
		//name表示的 Book类中的属性名
		//list()方法返回查询结果
		//查询多个列时  返回结果是数组集合 数组中元素的类型 是由查询列来决定
		List<Object[]> list = session.createQuery("select name,price from Book").list();
		for(Object[] objs:list){
			System.out.println(objs[0]+"--"+objs[1]);
		}
		tx.commit();
		HibernateUtil.closeSession();
	}
	//查询多个列时 将查询结果封装为对象集合
	@Test
	public void testQuery3(){
		//查询所有书 的名称和价格
		//创建Query对象
		Session session = HibernateUtil.getSession();
		Transaction tx = session.beginTransaction();
		//Book 大写 表示的是  cn.siggy.pojo.Book类
		//name表示的 Book类中的属性名
		//list()方法返回查询结果
		//查询多个列时  返回结果是数组集合 数组中元素的类型 是由查询列来决定
		List<Book> list = session.createQuery("select new Book(name,price) from Book").list();
		for(Book b:list){
			System.out.println(b);
		}
		tx.commit();
		HibernateUtil.closeSession();
	}
	//别名的使用
	@Test
	public void testQuery4(){
		//查询所有书 的名称和价格
		//创建Query对象
		Session session = HibernateUtil.getSession();
		Transaction tx = session.beginTransaction();
		//Book 大写 表示的是  cn.siggy.pojo.Book类
		//name表示的 Book类中的属性名
		//list()方法返回查询结果
		//查询多个列时  返回结果是数组集合 数组中元素的类型 是由查询列来决定
		List<Book> list = session.createQuery("select new Book(b.name,b.price) from Book as b").list();
		for(Book b:list){
			System.out.println(b);
		}
		tx.commit();
		HibernateUtil.closeSession();
	}
	//查询所有列
	@Test
	public void testQuery5(){
		//查询所有书 的名称和价格
		//创建Query对象
		Session session = HibernateUtil.getSession();
		Transaction tx = session.beginTransaction();
		List<Book> list = session.createQuery("from Book").list();
		for(Book b:list){
			System.out.println(b);
		}
		tx.commit();
		HibernateUtil.closeSession();
	}
	//查询所有列2 不能使用*  需要使用别名
	@Test
	public void testQuery6(){
		//查询所有书 的名称和价格
		//创建Query对象
		Session session = HibernateUtil.getSession();
		Transaction tx = session.beginTransaction();
		List<Book> list = session.createQuery("select b from Book b").list();
		for(Book b:list){
			System.out.println(b);
		}
		tx.commit();
		HibernateUtil.closeSession();
	}
	//条件查询  占位符 从0开始
	@Test
	public void testQuery7(){
		//查询所有书 的名称和价格
		//创建Query对象
		Session session = HibernateUtil.getSession();
		Transaction tx = session.beginTransaction();
		List<Book> list = session.createQuery("from Book b where id<?")
						.setInteger(0, 4)
						.list();
		for(Book b:list){
			System.out.println(b);
		}
		tx.commit();
		HibernateUtil.closeSession();
	}
	//条件查询  占位符 从0开始
	//setParameter不用理会参数类型
	@Test
	public void testQuery8(){
		//查询所有书 的名称和价格
		//创建Query对象
		Session session = HibernateUtil.getSession();
		Transaction tx = session.beginTransaction();
		List<Book> list = session.createQuery("from Book b where id<?")
						.setParameter(0, 4)
						.list();
		for(Book b:list){
			System.out.println(b);
		}
		tx.commit();
		HibernateUtil.closeSession();
	}
	//条件查询 命名查询--设置条件参数的名称  以冒号开头后更名称  设置参数时 只需指定名
	@Test
	public void testQuery9(){
		//查询所有书 的名称和价格
		//创建Query对象
		Session session = HibernateUtil.getSession();
		Transaction tx = session.beginTransaction();
		List<Book> list = session.createQuery("from Book b where id<:id")
						.setParameter("id", 4)
						.list();
		for(Book b:list){
			System.out.println(b);
		}
		tx.commit();
		HibernateUtil.closeSession();
	}
	//分页查询
	@Test
	public void testQuery10(){
		//查询所有书 的名称和价格
		//创建Query对象
		Session session = HibernateUtil.getSession();
		Transaction tx = session.beginTransaction();
		List<Book> list = session.createQuery("from Book b")
						.setFirstResult(3)//开始显示的记录下标(currentPage-1)*pageSize
						.setMaxResults(3)//设置每页记录数pageSize
						.list();
		for(Book b:list){
			System.out.println(b);
		}
		tx.commit();
		HibernateUtil.closeSession();
	}
	//聚合函数--统计查询
	//结果唯一
	@Test
	public void testQuery11(){
		//查询图书总数
		Session session = HibernateUtil.getSession();
		Transaction tx = session.beginTransaction();
		//int,long
		Number count = (Number)session.createQuery("select max(b.price) from Book b")
						.uniqueResult();
		System.out.println("总数："+count.byteValue());
		tx.commit();
		HibernateUtil.closeSession();
	}
	//分组查询
	@Test
	public void testQuery12(){
		//查询图书总数
		Session session = HibernateUtil.getSession();
		Transaction tx = session.beginTransaction();
		//int,long
		List<Object[]> list = session.createQuery("select b.category.name,count(b.id) from Book b group by b.category.name")
						.list();
		for(Object[] objs:list){
			System.out.println(objs[0]+"--"+objs[1]);
		}
		tx.commit();
		HibernateUtil.closeSession();
	}
	//排序
	@Test
	public void testQuery13(){
		//查询图书总数
		Session session = HibernateUtil.getSession();
		Transaction tx = session.beginTransaction();
		//int,long
		List<Book> list = session.createQuery("from Book order by price desc")
						.list();
		for(Book b:list){
			System.out.println(b);
		}
		tx.commit();
		HibernateUtil.closeSession();
	}
	//对象导航--连接查询
	@Test
	public void testQuery14(){
		//查询 "仙侠"的书籍信息
		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 b:list){
			System.out.println(b);
		}
		tx.commit();
		HibernateUtil.closeSession();
	}
	//左外链接
	@Test
	public void testQuery15(){
		//查询 "仙侠"的书籍信息
		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();
	}
	//过滤器的使用--过滤查询---为查询加上某些条件
	/* 1、定义过滤器
	 * 2、使用：加条件
	 * 3、在查询时候 使得过滤器生效
	 * */
	@Test
	public void testQuery16(){
		//查询 "仙侠"的书籍信息
		Session session = HibernateUtil.getSession();
		Transaction tx = session.beginTransaction();
		//启用过滤器
		session.enableFilter("bf").setParameter("id", 4);
		List<Book> list =session.createQuery("from Book").list();
		for(Book b:list){
			System.out.println(b);
		}
		tx.commit();
		HibernateUtil.closeSession();
	}
	//命名查询NamedQuery
	@Test
	public void testQuery17(){
		Session session = HibernateUtil.getSession();
		Transaction tx = session.beginTransaction();
		List<Book> list =session.getNamedQuery("getByCategoryId")
						.setInteger("id", 3)
						.list();
		for(Book b:list){
			System.out.println(b);
		}
		tx.commit();
		HibernateUtil.closeSession();
	}
	//本地查询 SQL查询
	@Test
	public void testQuery18(){
		Session session = HibernateUtil.getSession();
		Transaction tx = session.beginTransaction();
		String sql="select Name,Price from BOOK";
		List<Object[]> list =session.createSQLQuery(sql)
						.list();
		for(Object[]  b:list){
			System.out.println(b[0]+"-"+b[1]);
		}
		tx.commit();
		HibernateUtil.closeSession();
	}
	//如果查询是所有列
	@Test
	public void testQuery19(){
		Session session = HibernateUtil.getSession();
		Transaction tx = session.beginTransaction();
		String sql="select * from BOOK";
		List<Book> list =session.createSQLQuery(sql)
						.addEntity(Book.class)
						.list();
		for(Book b:list){
			System.out.println(b);
		}
		tx.commit();
		HibernateUtil.closeSession();
	}
	@Test
	public void testQuery20(){
		Session session = HibernateUtil.getSession();
		Transaction tx = session.beginTransaction();
		List<Book> list =session.getNamedQuery("getAll")
				.list();
				for(Book b:list){
					System.out.println(b+"-"+b.getCategory().getId());
				}
		tx.commit();
		HibernateUtil.closeSession();
	}
	//对象查询  Critera查询
	//查询对象集合
	@Test
	public void testQuery21(){
		Session session = HibernateUtil.getSession();
		Transaction tx = session.beginTransaction();
		List<Book> list =session.createCriteria(Book.class)
				.list();
				for(Book b:list){
					System.out.println(b+"-"+b.getCategory().getId());
				}
		tx.commit();
		HibernateUtil.closeSession();
	}
	//条件
	@Test
	public void testQuery22(){
		Session session = HibernateUtil.getSession();
		Transaction tx = session.beginTransaction();
		
		List<Book> list =session.createCriteria(Book.class)
				.add(Restrictions.eq("id", 1))
				.list();
				for(Book b:list){
					System.out.println(b+"-"+b.getCategory().getId());
				}
		tx.commit();
		HibernateUtil.closeSession();
	}
	//in
	@Test
	public void testQuery23(){
		Session session = HibernateUtil.getSession();
		Transaction tx = session.beginTransaction();
		List<Integer> inl = new ArrayList<Integer>();
		inl.add(1);
		inl.add(3);
		List<Book> list =session.createCriteria(Book.class)
				.add(Restrictions.in("id", inl))
				.list();
				for(Book b:list){
					System.out.println(b+"-"+b.getCategory().getId());
				}
		tx.commit();
		HibernateUtil.closeSession();
	}
	//like查询
	@Test
	public void testQuery24(){
		Session session = HibernateUtil.getSession();
		Transaction tx = session.beginTransaction();
		List<Book> list =session.createCriteria(Book.class)
				//.add(Restrictions.like("name", "中%"))
				//.add(Restrictions.like("name", "的", MatchMode.EXACT))
				//ilike ignoreCase like忽略大小写
				.add(Restrictions.ilike("name", "%的%"))
				.list();
				for(Book b:list){
					System.out.println(b+"-"+b.getCategory().getId());
				}
		tx.commit();
		HibernateUtil.closeSession();
	}
	//or
	@Test
	public void testQuery25(){
		Session session = HibernateUtil.getSession();
		Transaction tx = session.beginTransaction();
		List<Book> list =session.createCriteria(Book.class)
				//.add(Restrictions.like("name", "中%"))
				//.add(Restrictions.like("name", "的", MatchMode.EXACT))
				//ilike ignoreCase like忽略大小写
				.add(Restrictions.or(Restrictions.eq("id", 1)
						, Restrictions.lt("price", 60.0)))
				.list();
				for(Book b:list){
					System.out.println(b+"-"+b.getCategory().getId());
				}
		tx.commit();
		HibernateUtil.closeSession();
	}
	//排序
	@Test
	public void testQuery26(){
		Session session = HibernateUtil.getSession();
		Transaction tx = session.beginTransaction();
		List<Book> list =session.createCriteria(Book.class)
				.addOrder(Order.asc("price"))
				.list();
				for(Book b:list){
					System.out.println(b+"-"+b.getCategory().getId());
				}
		tx.commit();
		HibernateUtil.closeSession();
	}
	//投影--函数
	@Test
	public void testQuery27(){
		Session session = HibernateUtil.getSession();
		Transaction tx = session.beginTransaction();
		Object count =session.createCriteria(Book.class)
				.setProjection(Projections.rowCount())
				.uniqueResult();
		System.out.println(count);
		tx.commit();
		HibernateUtil.closeSession();
	}
	@Test
	public void testQuery28(){
		Session session = HibernateUtil.getSession();
		Transaction tx = session.beginTransaction();
		List<Object[]> objs =session.createCriteria(Book.class)
				.setProjection(Projections.projectionList()
						.add(Projections.max("price"))
						.add(Projections.avg("price"))
						)
				.list();
		for(Object[] obj:objs){
			System.out.println(obj[0]+"---"+obj[1]);
		}
		tx.commit();
		HibernateUtil.closeSession();
	}
	//分组
	@Test
	public void testQuery29(){
		Session session = HibernateUtil.getSession();
		Transaction tx = session.beginTransaction();
		List<Object[]> objs =session.createCriteria(Book.class,"b")
				.createCriteria("b.category", "c")
				.setProjection(Projections.projectionList()
						.add(Projections.rowCount())
						.add(Projections.groupProperty("c.name"))
						)
				.list();
		for(Object[] obj:objs){
			System.out.println(obj[0]+"---"+obj[1]);
		}
		tx.commit();
		HibernateUtil.closeSession();
	}
	//example查询
	@Test
	public void testQuery30(){
		Session session = HibernateUtil.getSession();
		Book book = new Book();
		book.setName("傲慢与偏见");
		book.setPrice(80);
		Transaction tx = session.beginTransaction();
		List<Book> list =session.createCriteria(Book.class)
				.add(Example.create(book))
				.list();
		for(Book b:list){
			System.out.println(b+"-"+b.getCategory().getId());
		}
		tx.commit();
		HibernateUtil.closeSession();
	}
}