from SQL to HQL
首先一点就是用HQL作的是对象查询,而SQL是表查询.如果把表看成一个独立的二维数组的话,那么对象因为在一开始就被OR-mapping了,在做HQL查询时一定要避免SQL思维.(其实这种”避免”经常遇到).
可以这么理解,hibernate在做对象关系映射(or-mapping)时已经将表之间的关联确定,所以在遇到SQL里连表的情况时(如, a.clientId = b.clientId…),就要看对象映射时是否已经确定了这两个表之间的关系(one-to-one,one-to-many,单向还是双向).举个例 子.
SQL:
select
a.Client_ID,
a.User_Reference,
a.Long_Name,
b.Securities_ID,
b.User_Reference,
b.SECURITIES_TYPE,
b.Long_Name,
c.Mapped_ID
from
CM_M_Clients a,
CM_M_Securities b,
CM_M_Securities_ID_Mappings c,
CM_T_Portfolios d
where
a.Client_ID = d.Client_ID and
b.Securities_ID = d.Securities_ID and
c.Securities_ID = b.Securities_ID and
c.Securities_Domain = ‘SEC ID MAPPINGS’ and
c.Subject = ‘ISIN’ and
d.Portfolio_Date = to_date(’03/26/2008′,’MM/DD/YY’) and
a.Client_ID = ‘50′ and
b.securities_ID = ‘600′
在”翻译”这个SQL语句的时候,首先看:
a.Client_ID = d.Client_ID and
b.Securities_ID = d.Securities_ID and
c.Securities_ID = b.Securities_ID
这 几个连表操作说明了四个表之间的关系, 之后我们要看的是四个对象是如何被映射的,也就是对象之间存在什么样的关系.(我这里的假设是-你不是很清楚对象之间是怎么映射的),通过看hbm或者annotation,我们发现对象之间的关系是:
( 四个对象: Security, SecurityIdMapping,Client, Portfolio)
Security – SecurityIdMapping: 双向一对多
Security – Portfolio :单向一对多(portfolio有一个security的外键)
Client [...]
