Hibernate HQL查询必知汇总
Sql代码
//HQL-Associations
Stringhql="selects.name,p.name,p.pricefromProductpinnerjoinp.supplierass";
Queryquery=session.createQuery(hql);
Listresults=query.list();
//HQL-Associations
Stringhql="selects.name,p.name,p.pricefromProductpinnerjoinp.supplierass";
Queryquery=session.createQuery(hql);
Listresults=query.list();Sql代码
//HQL-Delete
Stringhql="deletefromProductwherename=:name";
Queryquery=session.createQuery(hql);
query.setString("name","Product1");
introwCount=query.executeUpdate();
//HQL-Delete
Stringhql="deletefromProductwherename=:name";
Queryquery=session.createQuery(hql);
query.setString("name","Product1");
introwCount=query.executeUpdate();Sql代码
//HQL-Function
Stringhql="selectmin(product.price),max(product.price)fromProductproduct";
Queryquery=session.createQuery(hql);
Listresults=query.list();
//HQL-Function
Stringhql="selectmin(product.price),max(product.price)fromProductproduct";
Queryquery=session.createQuery(hql);
Listresults=query.list();Sql代码
//HQL-FetchAssociationsHQLInnerJoin
Stringhql="fromSuppliersinnerjoinfetchs.productsasp";
Queryquery=session.createQuery(hql);
Listresults=query.list();
//HQL-FetchAssociationsHQLInnerJoin
Stringhql="fromSuppliersinnerjoinfetchs.productsasp";
Queryquery=session.createQuery(hql);
Listresults=query.list();Sql代码
//HQL-NamedParameters
Stringhql="fromProductwhereprice>:price";
Queryquery=session.createQuery(hql);
query.setDouble("price",2.0);
Listresults=query.list();
Stringhql="fromProductasproductwhereproduct.supplier=:supplier";
Queryquery=session.createQuery(hql);
query.setEntity("supplier",supplier);
Listresults=query.list();
//HQL-NamedParameters
Stringhql="fromProductwhereprice>:price";
Queryquery=session.createQuery(hql);
query.setDouble("price",2.0);
Listresults=query.list();
Stringhql="fromProductasproductwhereproduct.supplier=:supplier";
Queryquery=session.createQuery(hql);
query.setEntity("supplier",supplier);
Listresults=query.list();Sql代码
//HQL-Update
Stringhql="updateSuppliersetname=:newNamewherename=:name";
Queryquery=session.createQuery(hql);
query.setString("name","SupplierName1");
query.setString("newName","s1");
introwCount=query.executeUpdate();
//HQL-Update
Stringhql="updateSuppliersetname=:newNamewherename=:name";
Queryquery=session.createQuery(hql);
query.setString("name","SupplierName1");
query.setString("newName","s1");
introwCount=query.executeUpdate();Sql代码
//HQL-where
Stringhql="fromProductwhereprice>2.0andnamelike'P%'";
Queryquery=session.createQuery(hql);
Listresults=query.list();
//HQL-where
Stringhql="fromProductwhereprice>2.0andnamelike'P%'";
Queryquery=session.createQuery(hql);
Listresults=query.list();Sql代码
//HQL-Map
Stringhql="selectnewmap(usr.nameasuserName,usr.passwordaspassword)fromUserusr";
Queryquery=session.createQuery(hql);
Listlist=query.list();
Mapgoods=(Map)list.get(0);
//HQL-Map
Stringhql="selectnewmap(usr.nameasuserName,usr.passwordaspassword)fromUserusr";
Queryquery=session.createQuery(hql);
Listlist=query.list();
Mapgoods=(Map)list.get(0);
【注】Sql代码
Stringhql="selectnewmap(usr.nameasuserName,usr.passwordaspassword)
fromcom.jason.Userusr";
Stringhql="selectnewmap(usr.nameasuserName,usr.passwordaspassword)
fromcom.jason.Userusr";
Stringhql="selectnewmap(usr.nameasuserName,usr.passwordaspassword)
fromcom.jason.Userusr";
Stringhql="selectnewmap(usr.nameasuserName,usr.passwordaspassword)
from com.jason.User usr";由于from之前的空格，引起unexpected token: from查询语句可以返回值为任何类型的属性或对象，包括返回类型为某种组件(Component)的属性:
Sql代码
selectcust.name.firstNamefromCustomerascust
selectcat.matefromCatcat
selectcust.name.firstNamefromCustomerascust
select cat.mate from Cat cat查询语句可以返回多个对象和（或）属性，存放在 Object[]队列中:Sql代码
selectmother,offspr,mate.name
fromDomesticCatasmother
innerjoinmother.mateasmate
leftjoinmother.kittensasoffspr
selectmother,offspr,mate.name
fromDomesticCatasmother
innerjoinmother.mateasmate
leftjoinmother.kittensasoffspr
或存放在一个List对象中:Sql代码
selectnewlist(mother,offspr,mate.name)
fromDomesticCatasmother
innerjoinmother.mateasmate
leftouterjoinmother.kittensasoffspr
selectnewlist(mother,offspr,mate.name)
fromDomesticCatasmother
innerjoinmother.mateasmate
left outer join mother.kittens as offspr也可能直接返回一个实际的类型安全的Java对象(假设类Family有一个合适的构造函数):Sql代码
selectnewFamily(mother,mate,offspr)
fromDomesticCatasmother
joinmother.mateasmate
leftjoinmother.kittensasoffspr
selectnewFamily(mother,mate,offspr)
fromDomesticCatasmother
joinmother.mateasmate
leftjoinmother.kittensasoffspr
也可以使用关键字as给“被选择了的表达式”指派别名:Sql代码
selectmax(bodyWeight)asmax,min(bodyWeight)asmin,count(*)asn
fromCatcat
selectmax(bodyWeight)asmax,min(bodyWeight)asmin,count(*)asn
from Cat cat这种做法在与子句select new map一起使用时最有用:Sql代码
selectnewmap(max(bodyWeight)asmax,min(bodyWeight)asmin,count(*)asn)
from Cat cat本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/zhutianxiang/archive/2010/07/06/5716058.aspx
=================================================================
Hibernate中使用Hql查询出一定时间段的记录 本人参考了网上的一些例子，希望可以给大家帮助。呵呵～ importjava.sql.Timestamp; importjava.text.SimpleDateFormat; importjava.util.Calendar; importjava.util.Date; publicclassGenHql{ privateStringhql=""; publicstaticvoidmain(Stringargs[]){ GenHqlgenHql=newGenHql(); TimestampstartTime=Timestamp.valueOf("2010-03-1219:11:40"); TimestampendTime=Timestamp.valueOf("2010-03-1619:11:40"); genHql.setHqlByTimestamp(startTime); Stringhql1="select*fromTesttwhere1=1"+genHql.getHql(); genHql.setHqlByTimestamp(startTime,endTime); Stringhql2="select*fromTesttwhere1=1"+genHql.getHql(); System.out.println("开始时间到现在"+hql1); System.out.println("开始时间到结束时间"+hql2); } //根据开始时间和结束时间生成hql语句 publicvoidsetHqlByTimestamp(TimestampstartTime,TimestampendTime){ StringbeginDate=""; StringendDate=""; SimpleDateFormatf=newSimpleDateFormat("yyyy-MM-ddHH:mm:ss"); if(startTime!=null&&startTime.toString()!=""){ //startTime=Timestamp.valueOf("2010-03-1219:11:40"); Calendarcal=Calendar.getInstance(); cal.setTime(startTime); Datebegin=cal.getTime(); beginDate=f.format(begin); hql+="andt.timestamp>=to_date('"+beginDate +"','YYYY-MM-DDHH24:MI:SS')"; } if(endTime==null||endTime.toString().equals("")){ Datedate=newDate(); endDate=f.format(date); hql+="andt.timestamp<=to_date('"+endDate +"','YYYY-MM-DDHH24:MI:SS')"; }else{ //startTime=Timestamp.valueOf("2010-03-1219:11:40"); Calendarcal=Calendar.getInstance(); cal.setTime(endTime); Dateend=cal.getTime(); endDate=f.format(end); hql+="andt.timestamp<=to_date('"+endDate +"','YYYY-MM-DDHH24:MI:SS')"; } System.out.println(hql); } //根据开始时间生成hql语句 publicvoidsetHqlByTimestamp(TimestampstartTime){ StringbeginDate=""; StringendDate=""; hql=""; SimpleDateFormatf=newSimpleDateFormat("yyyy-MM-ddHH:mm:ss"); if(startTime!=null&&startTime.toString()!=""){ //startTime=Timestamp.valueOf("2010-03-1219:11:40"); Calendarcal=Calendar.getInstance(); cal.setTime(startTime); Datebegin=cal.getTime(); beginDate=f.format(begin); hql="andt.timestamp>=to_date('"+beginDate +"','YYYY-MM-DDHH24:MI:SS')"; } } publicStringgetHql(){ returnhql; } publicvoidsetHql(Stringhql){ this.hql=hql; } } |