HIbernate--HQL举例+详解(二)

墨蓝 2021-08-13 15:59 324阅读 0赞

#### HQL检索 ####

HQL是面向对象的查询语言，它和SQL查询语言有些相似，但它使用的是类、对象和属性的概念，而没有表和字段的概念。在Hibernate提供的各种检索方式中，HQL是官方推荐的查询语言，也是使用最广泛的一种检索方式。  
它具有如下功能：

*  在查询语句中设定各种查询条件。
 *  支持投影查询，即仅检索出对象的部分属性。
 *  支持分页查询。
 *  支持分组查询，允许使用group by 和 having 关键字。
 *  提供内置聚集函数，如sum()、min()、max()。
 *  能够调用用户定义的SQL函数。
 *  支持子查询，即嵌套查询。
 *  支持动态绑定参数。

### 准备工作 ###

创建一个Customer实体类

package pers.zhang.domain;
    
    public class Customer { 
    	private Long cust_id;
    	private String cust_name;
    	private String cust_source;
    	private String cust_industry;
    	private String cust_level;
    	private String cust_linkman;
    	private String cust_phone;
    	private String cust_mobile;
    	
    	public Long getCust_id() { 
    		return cust_id;
    	}
    	public void setCust_id(Long cust_id) { 
    		this.cust_id = cust_id;
    	}
    	public String getCust_name() { 
    		return cust_name;
    	}
    	public void setCust_name(String cust_name) { 
    		this.cust_name = cust_name;
    	}
    	public String getCust_source() { 
    		return cust_source;
    	}
    	public void setCust_source(String cust_source) { 
    		this.cust_source = cust_source;
    	}
    	public String getCust_industry() { 
    		return cust_industry;
    	}
    	public void setCust_industry(String cust_industry) { 
    		this.cust_industry = cust_industry;
    	}
    	public String getCust_level() { 
    		return cust_level;
    	}
    	public void setCust_level(String cust_level) { 
    		this.cust_level = cust_level;
    	}
    	public String getCust_linkman() { 
    		return cust_linkman;
    	}
    	public void setCust_linkman(String cust_linkman) { 
    		this.cust_linkman = cust_linkman;
    	}
    	public String getCust_phone() { 
    		return cust_phone;
    	}
    	public void setCust_phone(String cust_phone) { 
    		this.cust_phone = cust_phone;
    	}
    	public String getCust_mobile() { 
    		return cust_mobile;
    	}
    	public void setCust_mobile(String cust_mobile) { 
    		this.cust_mobile = cust_mobile;
    	}
    	@Override
    	public String toString() { 
    		return "Customer [cust_id=" + cust_id + ", cust_name=" + cust_name + "]";
    	}
    }

配置ORM元数据：

<?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="pers.zhang.domain" >
    	<class name="Customer" table="cst_customer" >
    		<id name="cust_id" >
    			<generator class="identity"></generator>
    		</id>
    		<property name="cust_name" column="cust_name" ></property>
    		<property name="cust_source" column="cust_source" ></property>
    		<property name="cust_industry" column="cust_industry" ></property>
    		<property name="cust_level" column="cust_level" ></property>
    		<property name="cust_linkman" column="cust_linkman" ></property>
    		<property name="cust_phone" column="cust_phone" ></property>
    		<property name="cust_mobile" column="cust_mobile" ></property>
    	</class>
    </hibernate-mapping>

准备数据：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70]

#### 排序查询–返回List ####

*  order by 字句  
    查询返回的列表（list）可以按照一个返回的类或组件（components）中的任何属性（property）进行排序：  
    from Customer c order by c.cust\_name, c.cust\_id desc  
    可选的asc（默认）或desc关键字指明了按照升序或降序进行排序。

例子：

@Test
    	//排序查询
    	public void fun1(){ 
    		Session session = HibernateUtils.openSession();
    		Transaction tx = session.beginTransaction();
    		//----------------------------------------------------
    		String hql2 = " from Customer c order by c.cust_name, c.cust_id desc ";
    		
    		Query query = session.createQuery(hql2);
    		
    		List list = query.list();
    		
    		System.out.println(list);
    		//----------------------------------------------------
    		tx.commit();
    		session.close();
    	}

运行JUnit测试输出：

Hibernate: 
        select
            customer0_.cust_id as cust_id1_0_,
            customer0_.cust_name as cust_nam2_0_,
            customer0_.cust_source as cust_sou3_0_,
            customer0_.cust_industry as cust_ind4_0_,
            customer0_.cust_level as cust_lev5_0_,
            customer0_.cust_linkman as cust_lin6_0_,
            customer0_.cust_phone as cust_pho7_0_,
            customer0_.cust_mobile as cust_mob8_0_ 
        from
            cst_customer customer0_ 
        order by
            customer0_.cust_name,
            customer0_.cust_id desc
    [Customer [cust_id=1, cust_name=Google], 
     Customer [cust_id=3, cust_name=百度], 
     Customer [cust_id=2, cust_name=联想], 
     Customer [cust_id=5, cust_name=腾讯], 
     Customer [cust_id=4, cust_name=阿里巴巴]]

#### 分组查询–返回LIst ####

*  group by子句  
    一个返回聚集值（aggregate values）的查询可以按照一个返回的类或组件（components）中的任何属性（property）进行分组：  
    select c.cust\_level, count(\*) from Customer c group by c.cust\_level
 *  having子句  
    having子句在这里也允许使用。  
    select c.cust\_level, count(\*) from Customer c group by c.cust\_level having c.cust\_level > 1  
    如果底层的数据库支持的话（例如不能在MySQL中使用），SQL的一般函数与聚集函数也可以出现在having字句中。  
     注 意 ： g r o u p b y 和 o r d e r b y 子 句 中 都 不 能 包 含 算 数 表 达 式 。 \\color\{red\}\{注意：group by 和 order by 子句中都不能包含算数表达式。\} 注意：groupby和orderby子句中都不能包含算数表达式。

例子：

//分组查询
    	@Test
    	public void fun2(){ 
    		Session session = HibernateUtils.openSession();
    		Transaction tx = session.beginTransaction();
    		//----------------------------------------------------
    		//按客户等级分组查询
    		String hql1 = "select c.cust_level, count(*) from Customer c group by c.cust_level";
    
    		//按客户等级分组查询，且等级大于1
    		String hql2 = "select c.cust_level, count(*) from Customer c group by c.cust_level having c.cust_level > 1"; 
    
    		
    		Query query1 = session.createQuery(hql1);
    		Query query2 = session.createQuery(hql2);
    		
    		List<Object> list1 = query1.list();
    		List<Object> list2 = query2.list();
    		
    		System.out.println("客户等级" + " " + "个数");
    		for(Object o : list1) { 
    			Object[] objs = (Object[]) o;
    			System.out.println(objs[0] + " " + objs[1]);
    		}
    		
    		System.out.println("客户等级" + " " + "个数");
    		for(Object o : list2) { 
    			Object[] objs = (Object[]) o;
    			System.out.println(objs[0] + " " + objs[1]);
    		}
    		//----------------------------------------------------
    		tx.commit();
    		session.close();
    		
    	}

运行JUnit测试输出：

Hibernate: 
        select
            customer0_.cust_level as col_0_0_,
            count(*) as col_1_0_ 
        from
            cst_customer customer0_ 
        group by
            customer0_.cust_level
    Hibernate: 
        select
            customer0_.cust_level as col_0_0_,
            count(*) as col_1_0_ 
        from
            cst_customer customer0_ 
        group by
            customer0_.cust_level 
        having
            customer0_.cust_level>1
    客户等级 个数
    1      2
    2      2
    3      1
    客户等级 个数
    2      2
    3      1

#### 统计查询 ####

*  聚集函数  
    HQL查询可以返回作用于属性之上的聚集函数的计算结果：  
    select max(c.cust\_level) from Customer  
    受支持的聚集函数如下：  
    avg(…), sum(…), min(…), max(…)  
    count(\*)  
    count(…), count(distinct…), count(all…)  
    可以选择子句中使用数学操作符、连接以及经过验证的SQL函数：  
    关键字distinct 与 all 也可以使用，它们具有与SQL相同的语义。

例子：

@Test
    	//统计查询
    	public void fun5(){ 
    		Session session = HibernateUtils.openSession();
    		Transaction tx = session.beginTransaction();
    		//----------------------------------------------------
    		String hql1 = " select count(*) from cn.itcast.domain.Customer ";
    		String hql2 = " select sum(cust_id) from cn.itcast.domain.Customer ";
    		String hql3 = " select avg(cust_id) from cn.itcast.domain.Customer ";
    		String hql4 = " select max(cust_id) from cn.itcast.domain.Customer ";
    		String hql5 = " select min(cust_id) from cn.itcast.domain.Customer ";
    		
    		Query query1 = session.createQuery(hql1);
    		Query query2 = session.createQuery(hql2);
    		Query query3 = session.createQuery(hql3);
    		Query query4 = session.createQuery(hql4);
    		Query query5 = session.createQuery(hql5);
    		
    		Number number1  = (Number) query1.uniqueResult();
    		Number number2  = (Number) query2.uniqueResult();
    		Number number3  = (Number) query3.uniqueResult();
    		Number number4  = (Number) query4.uniqueResult();
    		Number number5  = (Number) query5.uniqueResult();
    		
    		System.out.println("总条数：" + number1);
    		System.out.println("id的和：" + number2);
    		System.out.println("id的平均值：" + number3);
    		System.out.println("id的最大值：" + number4);
    		System.out.println("id的最小值：" + number5);
    		//----------------------------------------------------
    		tx.commit();
    		session.close();
    		
    	}

运行JUnit测试输出：

Hibernate: 
        select
            count(*) as col_0_0_ 
        from
            cst_customer customer0_
    Hibernate: 
        select
            sum(customer0_.cust_id) as col_0_0_ 
        from
            cst_customer customer0_
    Hibernate: 
        select
            avg(customer0_.cust_id) as col_0_0_ 
        from
            cst_customer customer0_
    Hibernate: 
        select
            max(customer0_.cust_id) as col_0_0_ 
        from
            cst_customer customer0_
    Hibernate: 
        select
            min(customer0_.cust_id) as col_0_0_ 
        from
            cst_customer customer0_
    总条数：5
    id的和：15
    id的平均值：3.0
    id的最大值：5
    id的最小值：1

#### 投影查询 ####

简单到发指…即查询某一列或多列。  
需要了解的是HQL提供了一种**投影的构造方式查询**，格式如下：  
select new Customer(cust\_id, cust\_name, cust\_level) from Customer  
 同 时 ， 需 要 在 C u s t o m e r 实 体 类 中 添 加 对 应 的 构 造 函 数 ， 再 添 加 一 个 显 式 的 空 参 构 造 ！ \\color\{red\}\{同时，需要在Customer实体类中添加对应的构造函数，再添加一个显式的空参构造！\} 同时，需要在Customer实体类中添加对应的构造函数，再添加一个显式的空参构造！

public Customer() { 
    }
    public Customer(Long cust_id, String cust_name, String cust_level) { 
    		super();
    		this.cust_id = cust_id;
    		this.cust_name = cust_name;
    		this.cust_level = cust_level;
    }

例子：

@Test
    	//投影查询
    	public void fun6(){ 
    		Session session = HibernateUtils.openSession();
    		Transaction tx = session.beginTransaction();
    		//----------------------------------------------------
    		String hql1 = " select cust_name from Customer ";//单行投影
    		String hql2 = " select cust_name,cust_id from Customer ";//多行投影
    		String hql3 = "select new Customer(cust_id, cust_name, cust_level) from Customer";//构造投影
    		
    		Query query = session.createQuery(hql3);
    		
    		List<Customer> list = query.list();
    		
    		for(Customer c : list) { 
    			System.out.println(c.getCust_id() + " " + c.getCust_name() + " " + c.getCust_level());
    		}
    		
    		//----------------------------------------------------
    		tx.commit();
    		session.close();
    		
    	}

运行JUnit测试输出：

Hibernate: 
        select
            customer0_.cust_id as col_0_0_,
            customer0_.cust_name as col_1_0_,
            customer0_.cust_level as col_2_0_ 
        from
            cst_customer customer0_
    1 Google 2
    2 联想 3
    3 百度 2
    4 阿里巴巴 1
    5 腾讯 1

#### 多态查询 ####

在HQL中提供了型如：from Object 这样的查询。  
该查询回返回所有Object的实例及其子类的实例，及所有的表。  
Hibernate可以在from子句中指定任何Java 类或接口。查询会返回继承了该类的所有持久化子类的实例或返回声明了该接口的所有持久化类的实例。

感觉没什么用。。。不再举例。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70]: /images/20210813/20163c8e4eb9430e8ede4c384cd422cf.png