Hibernate HQL查询必知汇总
Sql代码
//HQL-Associations???
String hql = "select s.name, p.name, p.price from Product p inner join p.supplier as s";???
Query query = session.createQuery(hql);???
List results = query.list();??
//HQL-Associations
String hql = "select s.name, p.name, p.price from Product p inner join p.supplier as s";
Query query = session.createQuery(hql);
List results = query.list();Sql代码
//HQL-Delete??
String hql = "delete from Product where name = :name";???
Query query = session.createQuery(hql);???
query.setString("name","Product 1");???
int rowCount = query.executeUpdate();??
//HQL-Delete
String hql = "delete from Product where name = :name";
Query query = session.createQuery(hql);
query.setString("name","Product 1");
int rowCount = query.executeUpdate();Sql代码
//HQL-Function??
String hql = "select min(product.price), max(product.price) from Product product";???
Query query = session.createQuery(hql);???
List results = query.list();??
//HQL-Function
String hql = "select min(product.price), max(product.price) from Product product";
Query query = session.createQuery(hql);
List results = query.list();Sql代码
//HQL-Fetch Associations HQL Inner Join??
String hql = "from Supplier s inner join fetch s.products as p";???
Query query = session.createQuery(hql);???
List results = query.list();??
//HQL-Fetch Associations HQL Inner Join
String hql = "from Supplier s inner join fetch s.products as p";
Query query = session.createQuery(hql);
List results = query.list();Sql代码
//HQL-Named Parameters???
String hql = "from Product where price > :price";???
Query query = session.createQuery(hql);???
query.setDouble("price",2.0);???
List results = query.list();???
String hql = "from Product as product where product.supplier=:supplier";???
Query query = session.createQuery(hql);???
query.setEntity("supplier",supplier);???
List results = query.list();??
//HQL-Named Parameters
String hql = "from Product where price > :price";
Query query = session.createQuery(hql);
query.setDouble("price",2.0);
List results = query.list();
String hql = "from Product as product where product.supplier=:supplier";
Query query = session.createQuery(hql);
query.setEntity("supplier",supplier);
List results = query.list();Sql代码
//HQL-Update??
String hql = "update Supplier set name = :newName where name = :name";???
Query query = session.createQuery(hql);???
query.setString("name","Supplier Name 1");???
query.setString("newName","s1");???
int rowCount = query.executeUpdate();??
//HQL-Update
String hql = "update Supplier set name = :newName where name = :name";
Query query = session.createQuery(hql);
query.setString("name","Supplier Name 1");
query.setString("newName","s1");
int rowCount = query.executeUpdate();Sql代码
//HQL-where??
String hql = "from Product where price > 2.0 and name like 'P%'";???
Query query = session.createQuery(hql);???
List results = query.list();??
//HQL-where
String hql = "from Product where price > 2.0 and name like 'P%'";
Query query = session.createQuery(hql);
List results = query.list();Sql代码
//HQL-Map???
String hql = " select new map(usr.name as userName, usr.password as password) from User usr";???
Query query = session.createQuery(hql);???
List list = query.list();???
Map goods =(Map)list.get(0);??
//HQL-Map
String hql = " select new map(usr.name as userName, usr.password as password) from User usr";
Query query = session.createQuery(hql);
List list = query.list();
Map goods =(Map)list.get(0);
【注】
Sql代码
String hql = " select new map(usr.name as userName, usr.password as password)???
from com.jason.User usr";???
String hql = " select new map(usr.name as userName, usr.password as password)???
from com.jason.User usr";??
String hql = " select new map(usr.name as userName, usr.password as password)
?from com.jason.User usr";
String hql = " select new map(usr.name as userName, usr.password as password)
?from com.jason.User usr";由于from之前的空格，引起unexpected token: from
查询语句可以返回值为任何类型的属性或对象，包括返回类型为某种组件(Component)的属性:
Sql代码
select cust.name.firstName from Customer as cust???
select cat.mate from Cat cat??
select cust.name.firstName from Customer as cust
select cat.mate from Cat cat查询语句可以返回多个对象和（或）属性，存放在 Object[]队列中:
Sql代码
select mother, offspr, mate.name??
from DomesticCat as mother???
??? inner join mother.mate as mate???
??? left join mother.kittens as offspr??
select mother, offspr, mate.name
from DomesticCat as mother
??? inner join mother.mate as mate
??? left join mother.kittens as offspr
或存放在一个List对象中:
Sql代码
select new list(mother, offspr, mate.name)???
from DomesticCat as mother???
??? inner join mother.mate as mate???
??? left outer join mother.kittens as offspr??
select new list(mother, offspr, mate.name)
from DomesticCat as mother
??? inner join mother.mate as mate
??? left outer join mother.kittens as offspr也可能直接返回一个实际的类型安全的Java对象(假设类Family有一个合适的构造函数):
Sql代码
select new Family(mother, mate, offspr)???
from DomesticCat as mother???
??? join mother.mate as mate???
??? left join mother.kittens as offspr??
select new Family(mother, mate, offspr)
from DomesticCat as mother
??? join mother.mate as mate
??? left join mother.kittens as offspr
也可以使用关键字as给“被选择了的表达式”指派别名:
Sql代码
select max(bodyWeight) as max, min(bodyWeight) as min, count(*) as n???
from Cat cat??
select max(bodyWeight) as max, min(bodyWeight) as min, count(*) as n
from Cat cat这种做法在与子句select new map一起使用时最有用:
Sql代码
select new map( max(bodyWeight) as max, min(bodyWeight) as min, count(*) as n )???
from Cat cat
本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/zhutianxiang/archive/2010/07/06/5716058.aspx
?
=================================================================
?
genHql.setHqlByTimestamp(startTime, endTime);