HQL语法

转载 2017年01月03日 08:10:26

HQL:Hibernate Query Language
HQL是完全面向对象的查询语言,因此可以支持继承和多态等特征。

$下面介绍HQL语句的语法
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.聚集函数
avg,count,max,min,sum
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.表达式
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
<property name="hibernate.query.substitutions">true 1, false 0</property>
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
    )

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.子查询
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%''


HQL语法与详细解释[整理]

 Hibernate查询语言为HQL(Hibernate Query Language),可以直接使用实体类名及属性。HQL语法类似于SQL,有SQL的关键词如select、from、order...
  • qiuzhi__ke
  • qiuzhi__ke
  • 2015年12月14日 19:04
  • 1720

HQL 语法 限制-where子句

比较运算 1.=,、>=、 2.null值判断  ----  is [not] null  x = null ------> x   is   null x x is not null hq...
  • ouhuanyixie8627
  • ouhuanyixie8627
  • 2017年02月22日 22:06
  • 882

hql语句中like查询 对于 / ' " % 的处理

public List searchByParas(String authId, String resourceId, Integer state, Integer value, String...
  • a9529lty
  • a9529lty
  • 2011年08月15日 14:01
  • 6239

Hibernate查询语言,HQL语法与使用

Hibernate装备了一种极为有力的查询语言,(有意地)看上去很像SQL。但是别被语法蒙蔽,HQL是完全面向对象的,具备继承、多态和关联等特性。 10.1. 大小写敏感性(Ca...
  • xxfigo
  • xxfigo
  • 2012年01月17日 09:07
  • 2603

hibernate_day04_13_HQL多表查询(内连接和迫切内连接)

//1 创建query对象 Query query = session.createQuery("from Customer c inner join c.setLinkMan"); //...
  • huanglianggu
  • huanglianggu
  • 2017年11月27日 20:30
  • 85

在Hibernate中使用HQL通过Join进行关联查询

在Hibernate中使用HQL通过Join进行关联查询
  • a9529lty
  • a9529lty
  • 2011年03月10日 22:22
  • 22655

HQL语句的语法

1.from子句 from Person 表明从Person持久化类中选出全部的实例。 推荐:from Person as p 2.select子句 select p.name from...
  • wang01jie11
  • wang01jie11
  • 2012年11月26日 14:51
  • 106

Join用法,HQL的方法,Hibernate中的fetch

转自:http://www.blogjava.net/alwayscy/archive/2007/07/26/132577.html Join用法: 主要有Inner Join 及 O...
  • ernest201210
  • ernest201210
  • 2013年12月26日 10:29
  • 2601

【SSH】hibernate之hql学习

在敲SSH过程中,对于Dao层的查询方法接触了“hql查询”、“离线查询”和“命名查询”,对数据库持久化操作使用更多的是hql方式查询,现对其总结。 一、Hql是什么?       Hql全名Hibe...
  • u013047584
  • u013047584
  • 2016年05月21日 16:39
  • 1087

HQL语法

HQL 例如对于TUser类1.实体查询String hql = " from TUser"; 执行这条语句会返回TUser以及TUser子类的纪录。注:  如果 TUser 类具有外键, 查询会报错...
  • freeglad
  • freeglad
  • 2008年09月26日 10:04
  • 672
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:HQL语法
举报原因:
原因补充:

(最多只允许输入30个字)