HQL 查询语句

pengjing033 贡献于2012-08-20

作者 Jason  创建于2010-12-16 08:05:00   修改者Jason  修改于2010-12-16 08:05:00字数4862

文档摘要:.1.from子句fromPerson表明从Person持久化类中选出全部的实例。
关键词:

. 1.from子句 from Person 表明从Person持久化类中选出全部的实例。 推荐:from Person as p 2.select子句 select p.name from Person as p select p.name.firstName from Person as p select new list(p.name, p.address) from Person as p select new ClassTest(p.name, p.address) from Person as p (有前提) select p.name as personName from Person as p select new map(p.name 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(s.name) from Student as s") //去除重复行distinct Query query=session.createQuery("select distinct s.age from Student as s") select count(*) from Person select max(p.age) from Person as p select p.name || "" || p.address from Person as p 4.多态查询 from Person as p from java.lang.Object o from Named as n 5.where子句 from Person where name like "tom%" from Person as p where p.name like "tom%" from Cat cat where cat.mate.name like "kit%" select * from cat_table as table1 cat_table as table2 where table1.mate = table2.id and table1.name like "kit%" from Foo foo where foo.bar.baz.customer.address.city like "fuzhou%" from Cat cat, Cat rival where cat.mate = rival.mate select cat, mate from Cat cat, Cat mate where cat.mate = mate from Cat as cat where cat.id = 123 from Cat as cat where cat.mate.id = 69 from Person as person where person.id.country = 'AU' and person.id.medicareNumber = 123456 from Account as account where account.owner.id.country = 'AU' and account.owner.id.medicareNumber = 123456 from Cat cat where cat.class = DomesticCat from Account as a where a.person.name.firstName like "dd%" // 正确 from Account as a where a.person.name like "dd%" // 错误 6.表达式 =, <>, >, <, >=, <=, between, not between, in, not in, is, like等。 from DomesticCat cat where cat.name between 'A' and 'B' from DomesticCat cat where cat.name in ('Foo', 'Bar', 'Baz') from DomesticCat cat where cat.name not between 'A' and 'B' from DomesticCat cat where cat.name not in ('Foo', 'Bar', 'Baz') from DomesticCat cat where cat.name is null from Person as p where p.address is not null true 1, false 0 from Cat cat where cat.alive = true from Cat cat where cat.kittens.size > 0 from Cat cat where size(cat.kittens) > 0 from Calendar cal where maxelement(cal.holidays) > current date from Order order where maxindex(order.items) > 100 from Order order where minelement(order.items) > 10000 //操作集合元素 select mother from Cat as mother, Cat as kit where kit in elements(foo.kittens) //p的name属性等于集合中某个元素的name属性 select p from NameList list, Person p where p.name = some elements(list.names) //操作集合元素 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 calendar where calendar.holidays['national day'] = person.birthDay and person.nationality.calendar = calendar //下面同时使用list集合和map集合属性 select item from Item item, Order order where order.items[order.deliveredItemIndices[0]] = item and order.id = 11 select item from Item item, Order order where order.items[maxindex(order.items)] = item and order.id = 11 select item from Item item, Order order where order.items[size(order.items) - 1] = item select cust from Product prod, Store store inner join store.customers cust where prod.name = 'widget' and store.location.name in ['Melbourne', 'Sydney'] and prod = all elements(cust.currentOrder.lineItems) SELECT cust.name, cust.address, cust.phone, cust.id, cust.current_order FROM customers cust, stores store, locations loc, store_customers sc, product prod WHERE prod.name = 'widget' AND store.loc_id = loc.id AND loc.name IN ('Melbourne', 'Sydney') AND sc.store_id = store.id AND sc.cust_id = cust.id AND prod.id = ALL( SELECT item.prod_id FROM line_items item, orders o WHERE item.order_id = o.id AND cust.current_order = o.id ) like语句: String hql = "from Users u where u.userName=? and u.userPass=? and u.userInfo like ?"; Object param[] = { userName, password, '%'+info+'%' }; 7.order by子句 from Person as p order by p.name, p.age from Person as p order by p.name asc, p.age desc 8.group by子句 select cat.color, sum(cat.weight), count(cat) from Cat cat group by cat.color //select后出现的id处出现在group by之后,而name属性则出现在聚集函数中 select foo.id, avg(name), max(name) from Foo foo join foo.names name group by foo.id select cat.color, sum(cat.weight), count(cat) from Cat cat group by cat.color having cat.color in (eg.Color.TABBY, eg.Color.BLACK) select cat from Cat cat join cat.kittens kitten group by cat having avg(kitten.weight) > 100 order by count(kitten) asc, sum(kitten.weight) desc 9.子查询: all 表示所有记录 any 便是所有记录中的任意一条 somy 与any用法一样 in 等价于any exists 表示子查询至少要返回一条数据 all: from Team t where 2222 from Cat as fatcat where fatcat.weight > (select avg(cat.weight) from DomesticCat cat) from Cat as cat where not (cat.name, cat.color) in ( select cat.name, cat.color from DomesticCat cat ) 10.fetch关键字 from Person as p join p.scores from Document fetch all properties order by name from Document doc fetch all properties where lower(doc.name) like '%cat%' 11.查询链接: 与SQL查询一样,hql也支持连接查询,如内连接,外连接和交叉连接.支持的链接类型是从ANSI SQL中借鉴来的. 1: inner jion (内连接) 2: left outer join (左外连接) 3: right outer join(右外连接) 4: full join(全连接--不常用) inner jion 可以简写为join. 正常情况下必须要建关联。

下载文档到电脑,查找使用更方便

文档的实际排版效果,会与网站的显示效果略有不同!!

需要 8 金币 [ 分享文档获得金币 ] 0 人已下载

下载文档