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());        }    }}