Hibernate_HQL
20170327 Hibernate_HQL压缩包参照代码
HQL定义
1.HQL:Hibernate query language,Hibernate查询语言。
2.HQL是面向对象的查询语言。
HQL 查询主体，映射配置的持久化类及其属性
SQL 查询主题，数据库表
3.HQL提供了丰富灵活的查询特性，Hibernate官方推荐查询方式
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
ＨＱＬ语句形式：
from子句是必须的，最简单的ＨＱＬ语句可以只有from子句。clause 美 [klɔz] n. 从句，子句
select from where groupby having orderby
子句 子句 子句 子句 子句 子句
----------------------------------
初学HQL注意的问题
1..HQL是面向对象的查询语言，对Java类与属性大小写敏感
注意：ＨＱＬ与SQL形式像似，本质不同，一定注意。
2.HQL对关键字不区分大小写，但是习惯上为了美观全部小写
====查询准备========================================
Query接口
org.hibernate.Query接口
1.Query接口定义有执行查询的方法
Hibernate
HQL--------------解析HQL语句/配置信息------------>SQL语句
2.Query接口支持方法链编程风格，使程序代码更为简洁
Query实例创建
1.Session的createQuery()方法创建Query实例
2.createQuery()方法包含一个语句参数，createQuery(hql)
Query执行查询
1.Query接口的list()方法执行HQL查询
2.List()方法返回结果数据类型为java.util.List,List集合中存放符合查询条件的持久化对象
JUnit测试类的@Test都需要一下两个，用来获得和关闭session
@Before
public void setUp() throws Exception {
session = HibernateSessionFactory.getCurrentSession();
}
@After
public void tearDown() throws Exception {
session.close();
}
@Test
public void testSelectClauseSelf(){
String hql = " from Seller";
Query query = session.createQuery(hql);
List<Seller> sellers = query.list();
for(Seller seller : sellers){
System.out.println(seller.tostring());
}
}
====From子句==================================
From子句：
HQL语句最简形式
from指定了HQL语句查询主体-持久化类及其属性
@Test
public void testFromClause(){
String hql = " from Customer ";
Query query = session.createQuery(hql);
List<Customer> customers = query.list();
for(Customer customer : customers){
System.out.println("name:"+customer.getName());
}
}
---------------------------------
public class Commodity implements Serializable {
private Long id;// 主键
private String name;// 名称
private Double price;// 价格
private String unit;// 单位
private String category;// 类别
private String description;// 简介
private Seller seller;// 商家
+getter setter
------------------------
@Test懒加载状态，在需要使用外键信息时如（a)时，才查询
public void testFromClause(){
String hql = " from Commodity ";
Query query = session.createQuery(hql);
List<Commodity> commodities = query.list();
for(Commodity c : commodities){
System.out.println("name:"+c.getName());
System.out.println("seller's name :"+c.getSeller().getName());//（a)
}
}
---from子句中持久化类的引用----------------------------------------------
1.不需要引用持久化类的全限定名，直接引入类名
全限定名com.imooc.model.Seller（一定要指明权限定名，Java环境才知道去哪获取这个类）
类名：from Seller
2.auto-import(自动引入)缺省情况（Hibernate根据配置信息自动导入）
@Test
public void testSelectClauseSelf(){
String hql = " from Seller";
//String hql = " from com.imooc.model.Seller";//结果一致
Query query = session.createQuery(hql);
List<Seller> sellers = query.list();
for(Seller seller : sellers){
System.out.println(seller.tostring());
}
}
----from 子句别名的使用-------------------------------------------------
1.为被查询的类指定别名
2.在HQL语句其他部分通过别名引用该类（多用于查询多个持久化类的时候）
3.别名命名习惯
别名与持久化类名相同from Seller 别名：1 seller ，2单字母 s
@Test
public void testFromClause(){
String hql = " from Seller s ";//from Seller as seller
//from Seller s,Customer c
Query query = session.createQuery(hql);
List<Seller> sellers = query.list();
for(Seller seller : sellers){
System.out.println("name:"+seller.getName());
}
}
===Select 子句======================================
1.返回Object[]:未指定返回值类型时，默认返回Object[]
用select指定需要查询的字段
/*查询多个属性时
* 1.name 2.tel 3.address 4.star
*/
@Test
public void testSelectClauseObjectArray(){
String hql = " select s.name s.address from Seller s ";
Query query = session.createQuery(hql);
List<Object[]> list = query.list();
for(Object[] objs : list){
System.out.println("name:"+objs[0]);
System.out.println("address:"+objs[1]);
}
}
//查询一个属性时
@Test
public void testSelectClauseObjectArray(){
String hql = " select s.name from Seller s ";
Query query = session.createQuery(hql);
List<Object> list = query.list();
for(Object obj : list){
System.out.println("name:"+obj);
}
}
----------------------------------------
通过list返回：
1.select子句中使用new list指定
List Object[] Map,根据习惯选择返回方式
@Test
public void testSelectClauseList(){
String hql = " select new list(s.name,s.tel,s.address) from Seller s ";
Query query = session.createQuery(hql);
List<List> lists = query.list();
for(List list : lists){
System.out.println("name : "+list.get(0));
System.out.println("tel:"+list.get(1));
System.out.println("address:"+list.get(2));
}
}
通过Map返回：
1.select子句中使用new map指定
2.key值为索引值(属性在HQL中的位置信息)，字符串类型
@Test
public void testSelectClauseMap(){
String hql = " select new map(s.name,s.tel,s.address) from Seller s ";
Query query =session.createQuery(hql);
List<Map> maps = query.list();
for(Map map : maps){
System.out.println("name:"+map.get("0"));
System.out.println("tel:"+map.get("1"));
System.out.println("address:"+map.get("2"));
}
}
//Map获得查询结果是，可以考虑用别名获取属性信息
String hql = " select new map(s.name as name,s.tel as tel,s.address as address) from Seller s ";
System.out.println("name:"+map.get("name"));
System.out.println("tel:"+map.get("tel"));
System.out.println("address:"+map.get("address"));
----通过自定义类型返回查询结果--------------------------------------------------
1.持久化类中定义相应的构造器
2.select子句中调用定义的构造器contructor
1)contructor
public class Seller implements Serializable {
private Long id;// 主键
private String name;// 名称
private String tel;// 电话
private String address;// 地址
private String website;// 网站
private Integer star;// 星级
private String business;// 经营范围
public Seller(){
}
public Seller(String name,String tel,String address){
this.name = name;
this.tel = tel;
this.address = address;
}
2)test
@Test
public void testSelectClauseSelf(){
String hql = " select new Seller(s.name,s.tel,s.address) from Seller s ";
Query query = session.createQuery(hql);
List<Seller> sellers = query.list();
for(Seller seller : sellers){
System.out.println("name: "+seller.getName());
System.out.println("tel:"+seller.getTel());
System.out.println("address:"+seller.getAddress());
}
}
--- 持久化类中无参构造方法的必要性-------
HQL中没有指定构造器时，会使用默认构造器（无参构造器default contrustor）
增加自定义构造器时，一定不要忘记无参构造器
--- distinct关键字------
1.使用distinct去除查询结果中的重复元素
select sex from Customer c=> men,men,wemon
select distinct sex from Custmor c=>men ,wemon
@Test
public void testDistinct(){
String hql = "select distinct c.sex from Customer c ";
Query query = session.createQuery(hql);
List<Object> list = query.list();
for(Object obj : list){
System.out.println(obj);
}
}
====限制-where子句=================================
where子句：逻辑表达式，设置查询条件，限制返回结果
--比较运算-----------------------
1.=,<>,<,>,>=,<=
2.null值判断-is [not] null
HQL支持=null，<>null判断
HQL SQL
x=null x is null
x<>null x is not null
@Test
public void testWhere1(){
String hql = " from Commodity c where c.price<=200 ";
Query query = session.createQuery(hql);
List<Commodity> commodities = query.list();
for(Commodity c : commodities){
System.out.println("name:"+c.getName());
System.out.println("price:"+c.getPrice());
}
}
--null值判断-----------------------
@Test
public void testWhere1(){
String hql = " from Commodity c where c.description is null ";// '= null' is ok as well
Query query = session.createQuery(hql);
List<Commodity> commodities = query.list();
for(Commodity c : commodities){
System.out.println("name:"+c.getName());
System.out.println("description:"+c.getDescription());
}
}
--范围运算-----------------------
1.[not] in (列表)
1.[not] between value1 and value2
public void testWhere2(){
String hql = " from Customer c where c.age in(20,40) ";
//String hql = " from Customer c where c.age not between 20 and 40 ";
Query query = session.createQuery(hql);
List<Customer> customers = query.list();
for(Customer c: customers){
System.out.println("name:"+c.getName());
System.out.println("age:"+c.getAge());
}
}
--字符串模式匹配-----------------------
1.like关键字 通过通配符设置
2.通配符%:匹配任意字符,_:匹配一个字符
@Test
public void testWhere3(){
String hql = " from Customer c where c.name like '张_'";
//String hql = " from Customer c where c.address like '%北京%'";
Query query = session.createQuery(hql);
List<Customer> customers = query.list();
for(Customer c : customers){
System.out.println("name:"+c.getName());
System.out.println("address :"+ c.getAddress());
}
}
--逻辑运算-----------------------
逻辑运算符
1.and(逻辑与),or(逻辑或)
2.not(逻辑非)
@Test
public void testWhere2(){
String hql = " from Commodity c where c.price between 1000 and 5000 and c.category like '%电脑%' or name like '%硬盘%' ";
Query query = session.createQuery(hql);
List<Commodity> commodities = query.list();
for(Commodity c : commodities){
System.out.println("name:"+c.getName());
System.out.println("category:"+c.getCategory());
System.out.println("price:"+c.getPrice());
}
}
--集合运算-----------------------
HQL的特殊功能
1.is [not] empty 集合[不]为空，不包含任何元素
2.member of 元素属于集合
HQL SQL
empty -> exists
member of -> in
@Test
public void testWhere1(){
String hql = " from Order o where o.orderItems is not empty ";
Query query = session.createQuery(hql);
List<Order> orders = query.list();
for(Order order : orders){
System.out.println(order.getCustomer().getName());
System.out.println(order.getAmount());
System.out.println(order.getTradeDate());
}
}
--四则运算----------------------
1.HQL语句中也可以使用+-*/四则运算
2.四则运算可以在where子句和select子句中使用
@Test
public void testWhere4(){
String hql = " from Commodity c where c.price*5>3000 ";
Query query = session.createQuery(hql);
List<Commodity> commodities = query.list();
for(Commodity c : commodities){
System.out.println("name:"+c.getName());
System.out.println("price:"+c.getPrice()*5);
}
}
--查询单个对象----------------------
1.Query接口的uniqueResult方法，返回实例对象，不返回list
2.where子句条件设置(限定为单个结果，如果不能返回独立结果或没有结果，则执行中出异常终止运行)
public void testWhere4(){
String hql = " from Customer c where c.name = "张三" ";
Query query = session.createQuery(hql);
Customer c = (Customer)query.uniqueResult();
System.out.println(c.getName());
}
===排序orderby 子句==========================================
1.使用orderby 子句对查询结果进行排序
1.升序 asc（不指定时默认升序）
2.降序 desc
@Test
public void testOrderby(){
String hql = " from Commodity order by seller.id asc,price desc,name asc ";
Query query = session.createQuery(hql);
List<Commodity> commodities = query.list();
for(Commodity c : commodities){
System.out.println("name:"+c.getName());
System.out.println("price:"+c.getPrice());
}
}
=================
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
1.ＨＱＬ语句形式：
from子句是必须的，最简单的ＨＱＬ语句可以只有from子句。clause 美 [klɔz] n. 从句，子句
select from where groupby having orderby
子句 子句 子句 子句 子句 子句
----------------------------------
2.初学HQL注意的问题
1..HQL是面向对象的查询语言，对Java类与属性大小写敏感
注意：ＨＱＬ与SQL形式像似，本质不同，一定注意。
2.HQL对关键字不区分大小写，但是习惯上为了美观全部小写
3.别名的使用，命名规则可参照java
4.select子句使用自定义类返回选择属性，持久化类构造器处理(注意默认构造器)
其他参考资料：
http://www.blogjava.net/algz/articles/191835.html
http://blog.163.com/hbsi_zhouwufeng/blog/static/438725362008106113129903/
