hql查询语句

.1.from子句from Person表明从Person持久化类中选出全部的实例。

推荐：from Person as p2.select子句select from Person as pselect .firstName from Person as pselect new list(, p.address) from Person as pselect new ClassTest(, p.address) from Person as p (有前提)select as personName from Person as pselect new map( as personName) from Person as p (与new map()结合更普遍) 3.统计函数查询:1: count() 统计记录的条数2: min() 求最小值3: max() 求最大值4: sum() 求和4: avg() 求平均值//取得Student的数量Query query=session.createQuery("select count(*) from Student")//avg()取得Student平均年龄Query query=session.createQuery("select avg(s.age) from Student as s")//upper()方法将字符串转为大写Query query=session.createQuery("select upper() from Student as s")//去除重复行distinctQuery query=session.createQuery("select distinct s.age from Student as s") select count(*) from Personselect max(p.age) from Person as pselect || "" || p.address from Person as p4.多态查询from Person as pfrom ng.Object ofrom Named as n5.where子句from Person where name like "tom%"from Person as p where like "tom%"from Cat cat where like "kit%"select * from cat_table as table1 cat_table as table2 where table1.mate = table2.id and like "kit%"from Foo foo where foo.bar.baz.customer.address.city like "fuzhou%" from Cat cat, Cat rival where cat.mate = rival.mateselect cat, matefrom Cat cat, Cat matewhere cat.mate = matefrom Cat as cat where cat.id = 123from Cat as cat where cat.mate.id = 69from Person as personwhere person.id.country = 'AU'and person.id.medicareNumber = 123456from Account as accountwhere account.owner.id.country = 'AU'and account.owner.id.medicareNumber = 123456from Cat cat where cat.class = DomesticCatfrom Account as a where .firstName like "dd%" // 正确from Account as a where like "dd%" // 错误6.表达式=, <>, >, <, >=, <=, between, not between, in, not in, is, like等。

from DomesticCat cat where between 'A' and 'B'from DomesticCat cat where in ('Foo', 'Bar', 'Baz')from DomesticCat cat where not between 'A' and 'B'from DomesticCat cat where not in ('Foo', 'Bar', 'Baz')from DomesticCat cat where is nullfrom Person as p where p.address is not nulltrue 1, false 0from Cat cat where cat.alive = truefrom Cat cat where cat.kittens.size > 0from Cat cat where size(cat.kittens) > 0from Calendar cal where maxelement(cal.holidays) > current datefrom Order order where maxindex(order.items) > 100from Order order where minelement(order.items) > 10000//操作集合元素select mother from Cat as mother, Cat as kitwhere kit in elements(foo.kittens)//p的name属性等于集合中某个元素的name属性select p from NameList list, Person pwhere = some elements(s)//操作集合元素from Cat cat where exists elements(cat.kittens)from Player p where 3 > all elements(p.scores)from Show show where 'fizard' in indices(show.acts)//items是有序集合属性，items[0]代表第一个元素from Order order where order.items[0].id = 1234//holidays是map集合属性，holidays[national day]是代表其中第一个元素select person from Person person, Calendar calendarwhere calendar.holidays['national day'] = person.birthDayand person.nationality.calendar = calendar//下面同时使用list集合和map集合属性select item from Item item, Order orderwhere order.items[order.deliveredItemIndices[0]] = item and order.id = 11 select item from Item item, Order orderwhere order.items[maxindex(order.items)] = item and order.id = 11select item from Item item, Order orderwhere order.items[size(order.items) - 1] = itemselect custfrom Product prod,Store storeinner join store.customers custwhere = 'widget'and in ['Melbourne', 'Sydney']and prod = all elements(cust.currentOrder.lineItems)SELECT , cust.address, cust.phone, cust.id, cust.current_order FROM customers cust,stores store,locations loc,store_customers sc,product prodWHERE = 'widget'AND store.loc_id = loc.idAND IN ('Melbourne', 'Sydney')AND sc.store_id = store.idAND sc.cust_id = cust.idAND prod.id = ALL(SELECT item.prod_idFROM line_items item, orders oWHERE item.order_id = o.idAND cust.current_order = o.id)like语句：String hql = "from Users u where erName=? and erPass=? and erInfo like ?";Object param[] = { userName, password, '%'+info+'%' };7.order by子句from Person as porder by , p.agefrom Person as porder by asc, p.age desc8.group by子句select cat.color, sum(cat.weight), count(cat)from Cat catgroup by cat.color//select后出现的id处出现在group by之后，而name属性则出现在聚集函数中select foo.id, avg(name), max(name)from Foo foo join s namegroup by foo.idselect cat.color, sum(cat.weight), count(cat)from Cat catgroup by cat.colorhaving cat.color in (eg.Color.TABBY, eg.Color.BLACK)select catfrom Cat catjoin cat.kittens kittengroup by cathaving avg(kitten.weight) > 100order by count(kitten) asc, sum(kitten.weight) desc9.子查询:all 表示所有记录any 便是所有记录中的任意一条somy 与any用法一样in 等价于anyexists 表示子查询至少要返回一条数据all:from Team t where 22<all(select s.age from Student s)from Team t where all(select s.age from t.student s)>22from Cat as fatcatwhere fatcat.weight > (select avg(cat.weight) from DomesticCat cat)from Cat as catwhere not (, cat.color) in (select , cat.color from DomesticCat cat)10.fetch关键字from Person as p join p.scoresfrom Document fetch all properties order by namefrom Document doc fetch all properties where lower() like '%cat%'11.查询链接:与SQL查询一样,hql也支持连接查询,如内连接,外连接和交叉连接.支持的链接类型是从ANSI SQL中借鉴来的.1: inner jion (内连接)2: left outer join (左外连接)3: right outer join(右外连接)4: full join(全连接--不常用)inner jion 可以简写为join.正常情况下必须要建关联。