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