HQL语句

HQL（Hibernate Query Language）
1.HQL语句形式：

select...from...where...group by...having...order by...

2.HQL语句大小写敏感，特别是持久化类及其属性的大小写
3.通过一个例子来熟悉HQL语法
持久化类：

Commodity:
package com.imooc.model;

import java.io.Serializable;

/**
 * 商品信息持久化类
 * 
 * @author Administrator
 * 
 */
public class Commodity implements Serializable {
    /**
     * 
     */
    private static final long serialVersionUID = 1L;
    private Long id;// 主键
    private String name;// 名称
    private Double price;// 价格
    private String unit;// 单位
    private String category;// 类别
    private String description;// 简介
    private Seller seller;// 商家

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    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 String getUnit() {
        return unit;
    }

    public void setUnit(String unit) {
        this.unit = unit;
    }

    public String getCategory() {
        return category;
    }

    public void setCategory(String category) {
        this.category = category;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

    public Seller getSeller() {
        return seller;
    }

    public void setSeller(Seller seller) {
        this.seller = seller;
    }

    public String toString() {
        return "主键:" + this.getId() + "    |   名称:" + this.getName()
                + "    |     价格:" + this.getPrice() + "    |   单位 :"
                + this.getUnit() + "    |   分类:" + this.getCategory()
                + "    |   经营商家:" + this.getSeller().getName() + "    |   简介:"
                + this.getDescription();
    }

}

Customer:
package com.imooc.model;

import java.io.Serializable;
import java.util.Date;

/**
 * 客户信息持久化类
 * 
 * @author Administrator
 * 
 */
public class Customer implements Serializable {

    /**
     * 
     */
    private static final long serialVersionUID = 1L;
    private Long id;// 主键
    private String name;// 姓名
    private String tel;// 电话
    private String address;// 地址
    private String email;// 电子邮箱
    private String sex;// 性别
    private String description;// 自我介绍
    private Integer age;// 年龄
    private Date birthday;// 出生日期

    public Customer() {

    }

    public Customer(Long id, String name, String tel, String sex, Integer age,
            String email, String address) {
        this.id = id;
        this.name = name;
        this.tel = tel;
        this.sex = sex;
        this.age = age;
        this.email = email;
        this.address = address;

    }

    public Customer(Long id, String name) {
        this.id = id;
        this.name = name;
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getTel() {
        return tel;
    }

    public void setTel(String tel) {
        this.tel = tel;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    public String toString() {
        return "主键:" + this.getId() + "    |    姓名:" + this.getName()
                + "    |     电话:" + this.getTel() + "    |   性别 :"
                + this.getSex() + "    |   年龄:" + this.getAge()
                + "    |   出生日期:" + this.getBirthday() + "    |    地址:"
                + this.getAddress() + "    |   电子邮箱:" + this.getEmail()
                + "    |   自我介绍:" + this.getDescription();
    }

}

Order:
package com.imooc.model;

import java.io.Serializable;
import java.util.Date;
import java.util.Set;

/**
 * 订 单信息持久化类
 * 
 * @author Administrator
 * 
 */
public class Order implements Serializable {
    /**
     * 
     */
    private static final long serialVersionUID = 1L;
    private Long id;// 主键
    private Customer customer;// 客户
    private Date tradeDate;// 交易日期
    private String status;// 订单状态
    private Double amount;// 订单金额
    private Set<OrderItem> orderItems;// 订单明细

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public Customer getCustomer() {
        return customer;
    }

    public void setCustomer(Customer customer) {
        this.customer = customer;
    }

    public Date getTradeDate() {
        return tradeDate;
    }

    public void setTradeDate(Date tradeDate) {
        this.tradeDate = tradeDate;
    }

    public String getStatus() {
        return status;
    }

    public void setStatus(String status) {
        this.status = status;
    }

    public Double getAmount() {
        return amount;
    }

    public void setAmount(Double amount) {
        this.amount = amount;
    }

    public Set<OrderItem> getOrderItems() {
        return orderItems;
    }

    public void setOrderItems(Set<OrderItem> orderItems) {
        this.orderItems = orderItems;
    }

    public String toString() {
        return "订单主键:" + this.getId() + "    |    客户:"
                + this.getCustomer().getName() + "    |     交易日期:"
                + this.getTradeDate() + "    |   订单状态 :" + this.getStatus()
                + "    |   订单金额:" + this.getAmount();
    }

}

OrderItem:
package com.imooc.model;

import java.io.Serializable;

/**
 * 订单明细信息持久化类
 * 
 * @author Administrator
 * 
 */
public class OrderItem implements Serializable {
    /**
     * 
     */
    private static final long serialVersionUID = 1L;
    private Long id;// 主键
    private Order order;
    private Commodity commodity;// 订单商品
    private Double discount;// 折扣
    private Double actPrice;// 价格
    private Double amount;// 数量
    private Integer position;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public Commodity getCommodity() {
        return commodity;
    }

    public void setCommodity(Commodity commodity) {
        this.commodity = commodity;
    }

    public Double getDiscount() {
        return discount;
    }

    public void setDiscount(Double discount) {
        this.discount = discount;
    }

    public Double getActPrice() {
        return actPrice;
    }

    public void setActPrice(Double actPrice) {
        this.actPrice = actPrice;
    }

    public Double getAmount() {
        return amount;
    }

    public void setAmount(Double amount) {
        this.amount = amount;
    }

    public Order getOrder() {
        return order;
    }

    public void setOrder(Order order) {
        this.order = order;
    }

    public Integer getPosition() {
        return position;
    }

    public void setPosition(Integer position) {
        this.position = position;
    }

    public String toString() {
        return "订单明细主键:" + this.getId() + "    |    商品:"
                + this.getCommodity().getName() + "    |     折扣:"
                + this.getDiscount() + "    |   价格 :" + this.getActPrice()
                + "    |   数量:" + this.getAmount();
    }

}

Seller:
package com.imooc.model;

import java.io.Serializable;

/**
 * 商家信息持久化类
 * 
 * @author Administrator
 * 
 */
public class Seller implements Serializable {
    /**
     * 
     */
    private static final long serialVersionUID = 1L;
    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) {
        super();
        this.name = name;
        this.tel = tel;
        this.address = address;
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getTel() {
        return tel;
    }

    public void setTel(String tel) {
        this.tel = tel;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public String getWebsite() {
        return website;
    }

    public void setWebsite(String website) {
        this.website = website;
    }

    public Integer getStar() {
        return star;
    }

    public void setStar(Integer star) {
        this.star = star;
    }

    public String getBusiness() {
        return business;
    }

    public void setBusiness(String business) {
        this.business = business;
    }

    public String toString() {
        return "主键:" + this.getId() + "    |    名称:" + this.getName()
                + "    |     电话:" + this.getTel() + "    |   星级 :"
                + this.getStar() + "    |   网址:" + this.getWebsite()
                + "    |    地址:" + this.getAddress() + "    |   经营范围:"
                + this.getBusiness();
    }
}

生成mapping配置文件后，就可以开始测试了。

package com.imooc.test;

import java.util.List;
import java.util.Map;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;
import org.hibernate.service.ServiceRegistry;
import org.hibernate.service.ServiceRegistryBuilder;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import com.imooc.model.Commodity;
import com.imooc.model.Customer;
import com.imooc.model.Order;
import com.imooc.model.Seller;

@SuppressWarnings("unchecked")
public class SellerTest {

    private SessionFactory sessionFactory;
    private Session session;
//  private Transaction transaction;

    @Before
    public void init(){
        Configuration config = new Configuration().configure();
        ServiceRegistry serviceRegisterety = new ServiceRegistryBuilder().applySettings(config.getProperties()).buildServiceRegistry();
        sessionFactory = config.buildSessionFactory(serviceRegisterety);
        session = sessionFactory.openSession();
//      transaction = session.beginTransaction();
    }

    @After
    public void destory(){
//      transaction.commit();
        session.close();
        sessionFactory.close();
    }

    @Test
    public void testSeller(){
        String hql = "from Seller";
        Query query = session.createQuery(hql);
        List<Seller> sellers = query.list();
        for (Seller seller : sellers) {
            System.out.println(seller.toString());
        }
    }

    @Test
    public void testCustomer(){
        String hql = "from Customer";
        Query query = session.createQuery(hql);
        List<Customer> customs = query.list();
        for (Customer customer : customs) {
            System.out.println(customer.toString());
        }
    }

    @Test
    public void testCommodity(){
        String hql = "from Commodity";
        Query query = session.createQuery(hql);
        List<Commodity> commdities = query.list();
        for (Commodity commodity : commdities) {
            System.out.println(commodity.toString());
        }
    }

    /**
     * 通过Object[]的方式返回查询结果
     */
    @Test
    public void testSelectClauseObjectArray(){
        String hql = "select s.name,s.tel,s.address,s.star from Seller as s ";
        Query query = session.createQuery(hql);
        List<Object[]> list = query.list();
        for (Object[] obj : list) {
            System.out.println(obj[0] + " " + obj[1] + " " + obj[2] + " " + obj[3]);
        }
    }

    /**
     * 通过list的方式返回查询结果
     */
    @Test
    public void testSelectClauseList(){
        String hql = "select new list(s.name,s.tel,s.address) from Seller as s ";
        Query query = session.createQuery(hql);
        List<List> lists = query.list();
        for (List list : lists) {
            System.out.println(list.get(0) + " " + list.get(1) + " " + list.get(2));
        }
    }

    /**
     * 通过map的方式返回查询结果
     */
    @Test
    public void testSelectClauseMap(){
        String hql = "select new map(s.name as name,s.tel as tel) from Seller as s";
        Query query = session.createQuery(hql);
        List<Map> maps = query.list();
        for (Map map : maps) {
            System.out.println(map.get("name") + " " + map.get("tel"));
        }
    }

    /**
     * 通过自定义类型返回查询结果
     */
    @Test
    public void testSelectClauseSelf(){
        String hql = "select new Seller(s.name,s.tel,s.address) from Seller as s ";
        Query query = session.createQuery(hql);
        List<Seller> sellers = query.list();
        for (Seller seller : sellers) {
            System.out.println(seller.getName() + " " + seller.getTel() + " " + seller.getAddress());
        }
    }

    /**
     * 使用distinct关键字去除查询结果中的重复元素
     */
    @Test
    public void testDistince(){
        String hql = "select distinct c.sex from Customer as c ";
        Query query = session.createQuery(hql);
        List<Object> sex = query.list();
        for (Object obj : sex) {
            System.out.println(obj);
        }
    }

    /**
     * 比较运算符,=, >, <, <>, <=, >= 
     */
    @Test
    public void testWhere1(){
        String hql = "from Commodity as c where c.price > 400 ";
        Query query = session.createQuery(hql);
        List<Commodity> commdities = query.list();
        for (Commodity commodity : commdities) {
            System.out.println(commodity.toString());
        }
    }

    /**
     * null值运算,与sql不同的是可以使用=null或<>null
     */
    @Test
    public void testWhere2(){
        String hql = "from Commodity as c where c.description is null ";
        Query query = session.createQuery(hql);
        List<Commodity> commdities = query.list();
        for (Commodity commodity : commdities) {
            System.out.println(commodity.toString());
        }
    }

    /**
     * 范围运算[not] in (范围列表)
     * [not] between 值1 and 值2
     */
    @Test
    public void testWhere3(){
        String hql = "from Customer as c where c.age in (20,40) ";
        Query query = session.createQuery(hql);
        List<Customer> customers = query.list();
        for (Customer customer : customers) {
            System.out.println(customer.toString());
        }
    }

    /**
     * 字符串模式匹配
     * like关键字
     * 通配符%, _
     */
    @Test
    public void testWhere4(){
        String hql = "from Customer as c where c.name like '张_'";
        Query query = session.createQuery(hql);
        List<Customer> customers = query.list();
        for (Customer customer : customers) {
            System.out.println(customer.toString());
        }
    }

    /**
     * 逻辑运算
     * and(逻辑与),or(逻辑或),not(逻辑非)
     */
    @Test
    public void testWhere5(){
        String hql = "from Commodity as c where c.price between 100 and 5000 and c.category like '%电脑%'";
        Query query = session.createQuery(hql);
        List<Commodity> commodities = query.list();
        for (Commodity commodity : commodities) {
            System.out.println(commodity.toString());
        }
    }

    /**
     * 集合运算
     * is [not] empty 集合[不]为空,不包含任何元素----->sql:exit运算
     * member of 元素属于集合--------->sql:in运算
     */
    @Test
    public void testWhere6(){
        String hql = "from Order as 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.toString());
        }
    }

    /**
     * 四则运算
     * + - * /
     * 可以在select和where中使用
     */
    @Test
    public void testWhere7(){
        String hql = "from Commodity as c where c.price*5 > 3000 ";
        Query query = session.createQuery(hql);
        List<Commodity> commodities = query.list();
        for (Commodity commodity : commodities) {
            System.out.println(commodity.toString());
        }
    }

    /**
     * 查询单个对象
     * Query接口中的uniqueResult方法，返回一个实例对象
     * where子句条件设置注意：查询结果应为1个或null 
     */
    @Test
    public void testWhere8(){
        String hql = "from Customer as c where c.name = '张三'";
        Query query = session.createQuery(hql);
        Customer customer = (Customer) query.uniqueResult();
        System.out.println(customer.toString());
    }

    /**
     * 排序order by
     * 升序：asc 降序：desc
     */
    @Test
    public void testWhere9(){
        String hql = "from Commodity order by price desc,id desc ";
        Query query = session.createQuery(hql);
        List<Commodity> commodities = query.list();
        for (Commodity commodity : commodities) {
            System.out.println(commodity.toString());
        }
    }



}