使用HQL数据查询遇到的问题
1.XXX is not mapped [from XXX]
参考Hibernate error - QuerySyntaxException: users is not mapped [from users]
原因是:In the HQL , you should use the java class name and property name of the mapped @Entity instead of the actual table name and column name
2.HQL设置参数
请参考Hibernate的查询 HQL查询 参数,分页,分组
增加参数为分两种,一中是占位符方式:
/**
* 占位符参数
*/
@SuppressWarnings("unchecked")
public static void test4(){
Session session = HibernateSessionFactory.currentSession();
String hql = "from User u where u.id=?";
Query query = session.createQuery(hql);
query.setParameter(0, 2);
List<User> user = query.list();
System.out.println("------------SQL执行完毕---------------");
for (User us : user) {
System.out.println(us.getName());
}
}
注意是从 0 开始,如果你的 id 是整型,那么必须设置一个整数,如果你设置参数是 “2” 是不行的!
也可以使用命名参数:
/**
* 命名参数
*/
@SuppressWarnings("unchecked")
public static void test5(){
Session session = HibernateSessionFactory.currentSession();
String hql = "from User u where u.id=:id";
Query query = session.createQuery(hql);
query.setInteger("id", 2);
List<User> user = query.list();
System.out.println("------------SQL执行完毕---------------");
for (User us : user) {
System.out.println(us.getName());
}
}
Hibernate——HQL数据查询
Hibernate Query Language,Hibernate查询语言。
HQL是面向对象的查询语言。HQL的查询主题是映射配置的持久化类及其属性。SQL查询主体是数据库表。
HQL语句
HQL语句形式
HQL是面向对象的查询语言,对Java类与属性大小写敏感。
HQL对关键字不区分大小写。
准备查询
org.hibernate.Query接口定义有执行查询的方法。
Query实例的创建
1.Session的createQuery()方法创建Query实例
2.createQuery方法包含一个HQL语句参数,createQuery(hql)
Query执行查询
1.Query接口的list()方法执行HQL查询
2.list()方法返回结果数据类型为java.util.List,List集合中存放符合查询条件的持久化对象
String hql = " from Seller ";
Query query = session.createQuery(hql);
List<Seller> sellers = query.list();
for (Seller seller : sellers) {
System.out.println(seller);
}
检索对象from子句
from子句简单查询
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());
}
}
from子句中持久化类的引用
1. 不需要引入持久化类的全限定名,直接引入类名
from子句中别名的应用
String hql = " from Seller as seller ";
或者
String hql = " from Seller s, Customer c ";
选择select子句
以Object[]形式返回
1.select子句中未指定返回数据类型,默认为Object[]
String hql = " select s.name, s.tel from Seller s ";
Query query = session.createQuery(hql);
List<Object[]> list = query.list();
for(Object[] obj : list){
System.out.println("name:"+obj[0]);
System.out.println("tel:"+obj[1]);
}
如果select语句中只有一个属性,返回的则是一个Object对象,不是数组
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形式返回
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返回查询结果
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"));
}
通过别名获取信息
String hql = " select new map(s.name as name,s.tel as tel,s.address as address) from Seller s ";
Query query =session.createQuery(hql);
List<Map> maps = query.list();
for(Map map : maps){
System.out.println("name:"+map.get("name"));
System.out.println("tel:"+map.get("tel"));
System.out.println("address:"+map.get("address"));
}
以自定义类型返回查询结果
首先要在持久化类中定义对应的构造器
public Seller(String name,String tel,String address){
this.name = name;
this.tel = tel;
this.address = address;
}
然后在select子句中调用定义的构造器
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());
}
通过distinct返回不重复的查询结果
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子句
比较运算
比较运算时将持久化类的属性与给定的查询条件进行比较
=、<>、 <、 >、 >= 、<=
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); }
null值判断 is [not] null
x = null -> x is null
x <> null -> x is not nullString hql = " from Commodity c where c.description is null "; 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()); }
范围运算
[not] in (列表)
String hql = " from Customer c where c.age not in (20, 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()); }
[not] between 值1 and 值2
String hql = " from Customer c where c.age 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()); }
字符串模式匹配
使用like关键字,通配符%匹配任意个字符,_匹配一个字符
//匹配姓张的,并且只有两个字
String hql = " from Customer c where c.name like '张_' ";
Query query = session.createQuery(hql);
List<Customer> customers = query.list();
或者
//匹配地址中有‘北京’
String hql = " from Customer c where c.address like '%北京%' ";
Query query = session.createQuery(hql);
List<Customer> customers = query.list();
逻辑运算
and、or 、not
String hql = " from Commodity c where c.price between 100 and 5000 or c.category 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());
}
集合元素
is [not] empty 集合[不]为空,不包含任何元素。empty -> exists
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()); }
member of 元素属于集合。member of -> in
四则运算
//5件商品的价格大于3000
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);
}
查询单个对象
String hql = " from Customer c where c.name='张三' ";
Query query = session.createQuery(hql);
Customer c = (Customer)query.uniqueResult();
System.out.println(c.getName());
order by子句
- 升序排序 asc
降序排序 desc
String hql = " from Commodity order by price 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()); }
设置多个排序
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());
}