HQL 参考

概述

• HQL（Hibernate Query Language）提供更加丰富灵活、强大的查询功能
• HQL 语句形式：select .... from .... where .... group by .... having .... order by ....
• 最简单的 HQL 只需要一个 FROM 就可以够成
• HQL 对大小写敏感，关键字不区分大小写
• HQL 是面向对象的查询语言

HQL 语句参考：

• Card为类名，不是表名，可写全路径

from Card

• 条件查询

from Card where cardName = 'DOTA'

• 模糊查询

from Card where cardName like '%林%'

• 条件查询

from Card c where c.createDate > '2011-08-08'

• betwwen and

from Card c where c.createDate between '2011-08-08' and '2022-11-11'

• 多条件查询 and

from Card c where c.createDate between '2011-08-08' and '2022-11-11' and c.cardname like '%林%'

• 更新

update Card as c set c.createDate = '2011-03-03' where c.cardType.cardTypeId = 3

• 删除

delete from Card c where c.createDate = '2011-03-04'

• 单个属性查询

select c.cardName from Card c where c.cardType.cardTypeId = 1

• 多个属性查询，其中cardTypeName直接通过card对象的cardType获取，省去SQL必须的多表连接

select c.cardName,c.cardType.cardTypeName,c.createDate from  Card as c where c.cardType.cardTypeId=1

• 多个属性查询，面向对象

select new Card(c.cardName,c.createDate) from Card as c 

• 函数查询

select count(*),max(c.createDate) from Card as c

• 排序

from Card as c order by c.createDate desc

• 分组

from Card as c group by c.cardType.cardTypeId 

• 单个对象查询，可以把对象查询出来

select c.cardType from Card as c

• 设置参数参数

select c.cardType from  Card as c where c.cardType.cardTypeId=:id

• inner join 查寻结果为多个对象集合

from Card as c inner join c.cardType

• left join 查寻结果为多个对象集合

from CardType as c left join c.cards

• right join 查寻结果为多个对象集合

from CardType as c right join c.cards    

• 子查询

from CardType as c where (select count(*) from c.cards>0)