Hibernate中HQL函数汇总及其说明

Hibernate中HQL函数汇总及其说明

(2012-07-06 20:37:16)

转载▼

标签： it

分类：技术

Criteria查询对查询条件进行了面向对象封装，符合编程人员的思维方式，不过HQL(Hibernate QueryLanguage)查询提供了更加丰富的和灵活的查询特性，因此Hibernate将HQL查询方式立为官方推荐的标准查询方式，HQL查询在涵盖Criteria查询的所有功能的前提下，提供了类似标准SQL语句的查询方式，同时也提供了更加面向对象的封装。以下就是HQL的一些常用函数，有了这些函数，我们就可以拥有更多的灵活性，比如HQL如何取余，HQL如何返回集合的最值，HQL字符串处理，HQL时间、日期处理等。

函数名称	说明	类型	支持	使用方法	备注
ABS(n)	取绝对值	数学函数	JPAQL HQL	ABS(column_name[数字类型对象属性])
SQRT(n)	取平方根	数学函数	JPAQL HQL	SQRT(column_name[数字类型对象属性])
MOD(x,y)	取余数	数学函数	JPAQL HQL	MOD([对象属性(数字)或值],[对象属性（数字）或值])	数字必须是整型，返回参数1/参数2得的余数
SIZE(c)	方法集合内对象数量	集合函数	JPAQL HQL
MINELEMENT(c)	返回集合中最小元素	集合函数	HQL
MAXELEMENT(c)	返回集合中最大元素	集合函数	HQL
MININDEX(c)	返回索引集合最小索引	集合函数	HQL
MAXINDEX(c)	返回索引集合最大索引	集合函数	HQL
MAX(n)	返回最大值	集合函数	JPQHQL HQL
MIN(n)	返回最小值	集合函数	JPQHQL HQL
COUNT(n)	返回计数	集合函数	JPQHQL HQL
CONCAT(s1,s2)	连接连个字符串	字符串函数	JPQHQL HQL	CONCAT([对象属性],[对象属性])
SUBSTRING(s,offset,length)	返回子串	字符串函数	JPQHQL HQL	SUBSTRING([要截取的字符串属性字段]，开始位置，截取长度)
TRIM([[BOTH/LEADING/TRAILING]] charFROM s)	默认去掉字符串两面的空格	字符串函数	JPQHQL HQL	TRIM([字符串对象属性列])	将字段两端的空格去掉
LOWER(s)	小写	字符串函数	JPQHQL HQL	LOWER([字符串对象属性列])	将该列结果含有的字母全部大写
UPPER(s)	大写	字符串函数	JPQHQL HQL	UPPER([字符串对象属性列])	将该列结果含有的字母全部大写
LENGTH(s)	返回字符串长度	字符串函数	JPQHQL HQL	LENGTH(字段名)	返回字段内容的长度，包括数字。null值返回null.
CURRENT_DATE()	返回数据库当前日期	时间函数	JPAQL HQL	CURRENT_DATE()	返回数据库当前日期
CURRENT_TIME()	时间	时间函数	JPAQL HQL	CURRENT_TIME()	返回数据库当前时间
SECOND(d)	从日期中提取具体秒	时间函数	HQL	SECOND(时间字段)	空的时候返回null
MINUTE(d)	从日期中提取具体分	时间函数	HQL	MINUTE(时间字段)	空的时候返回null
HOUR(d)	从日期中提取具体小时	时间函数	HQL	HOUR(时间字段	空的时候返回null
DAY(d)	从日期中提取具体天	时间函数	HQL	DAY(时间字段)	空的时候返回null
MONTH(d)	从日期中提取具体月	时间函数	HQL	MONTH(时间字段)	空的时候返回null
YEAR(d)	从日期中提取具体年	时间函数	HQL	YEAR(时间字段)	空的时候返回null

 

-----------------------------------------------------------------------------------------------------------------------------------------

Hql的常用函数
一、substring函數
此函數可以用在HQL語句的columns部分和where後面，主要目的可以對數據庫字段中的某數據進行按條件截取,並動態組合成自已想要的數據格式，具體使用方法如:
example: oldSid:812108M00003 -> newSid:08-M00003
hql = "select substring(oldSid,5, 2)+'-'+substring(oldSid,7,6) as newSid from Table";

substring()函數一般帶有三個參數,第一個為要格式化的源字段，第二個為開始執行的起始位置，(下標從臺開始),第三個參數為從開始位置起要截取幾位數據,所有上面語句的結果為:
newSid:08-M00003

另substring函數也可以用在where語句後面如:
hql = "from Table a where substring(a.description, 1, 3) = 'cat'";


二、子查詢的用法
假設現在有兩張表Company(公司表),Employee(員工表),關係一個公司有多個員工,所以一個company記錄應該對應多條employee紀錄.即在employee有一個 FK 指向Company的 PK.
condition:找出每一個公司中員工工資(pay)最高的那一位.
hql:
select (select max(e.pay) from c.Employee e) from Company c
note:select後面的子查詢必須寫在()中,
其運行後對應的sql語句為:
select
(select
max(e.pay)
from
Employee e
where
e.HSUID=c.HSUID) as col_0_0_
from
Company c;

hql中的子查詢也可以帶查詢條件如下面也可以寫成:
select (select max(e.pay) from a.employee e where e.name=:name) from company a
select
(select
max(e.pay)
from
Employee e
where
e.HSUID=c.HSUID and e.name = '01') as col_0_0_
from
Company c;


三、coalesce函数的用法

COALESCE
返回其参数中第一个非空表达式。

语法
COALESCE (expression [ ,...n ])

参数
expression

任何类型的表达式。
n

表示可以指定多个表达式的占位符。所有表达式必须是相同类型，或者可以隐性转换为相同的类型。  
返回类型
将相同的值作为 expression 返回。

注释
如果所有自变量均为 NULL，则 COALESCE 返回 NULL 值。

COALESCE(expression1,...n) 与此 CASE 函数等价：

CASE
WHEN (expression1 IS NOT NULL) THEN expression1
...
WHEN (expressionN IS NOT NULL) THEN expressionN
ELSE NULL  
   
示例:表score代表某同学本次考试各科成绩的得分.结构如下:
語文     數學     英語
58        NULL     95
95         NULL     0
NULL      NULL      34
NULL      NULL      NULL
0           95       NULL
0          0        0
NULL       95      95
95         NULL     NULL

hql:
select a from score a where coalesce(a.chinese,a.math,a.english) = 95;
執行後結果為:
95         NULL     0
NULL       95      95
95         NULL     NULL

(3 rows affected)
第一行因為a.chinese(語文)不空,所以where a.chinese = 95;return false;
第三行因為a.english = 34,所以return false;
第五行因為a.chinese = 0;標誌也不為空,所以return false;

四. ISNULL 函数和 NULLIF 函数



SQL Server里的 ISNULL 与 ASP 中的 IsNull不同，SQL Server 中有两个参数，语法：

ISNULL(check_expression, replacement_value)

check_expression 与 replacement_value 数据类型必须一致
如果 check_expression 为 NULL，则返回 replacement_value
如果 check_expression 不为 NULL，则返回 check_expression
NULLIF 用于检查两个表达式，语法：
NULLIF(expression, expression)

如果两个 expression 相等，则返回 NULL，该 NULL 为第一个 expression 的数据类型
如果两个 expression 不相等，则返回第一个 expression

示例程式:

函数表score代表某学生某此考試的數學和中文成績.數據如下:
中文     數學
25         36
15         59
68         68
85         45
96          95
現要查詢某同學的兩門成績,要求分數不相等,如相等,則要求中文成績要超過85分以上.
select a from Score a where nullif(a.chinese,a.math) >= 85;

返回結果:
85         45
最後一條雖然第門功課成績都是well,但由於a.chinese == a.math,所以函數nullif會返回NULL,
五. 于日期有关的数据函数(EXTRACT)
EXTRACT

ANSI标准中EXTRACT函数允许选取日期和时间中任意段或任意间隔的值,Teradata中EXTRACT函数支持日期数据中选取年.月.日.从时间数据中选取小时.分钟和秒,同时Hibernate也支持相应的函数运算.
示例:

SELECT DATE;   96/11/07
SELECT EXTRACT(YEAR FROM DATE);     1996
SELECT EXTRACT(MONTH FROM DATE+30); 12
SLEECT EXTRACT(DAY FROM DATE+2);   09
SELECT TIME;   14:52:32
SELECT EXTRACT(HOUR FROM TIME); 14
SELECT EXTRACT(SECOND FROM TIME+30);   INVALID TIME

hql:
select extract(second from a.orderTime) from ApOrderRequest a;


六.LOCATE函数的用法

locate函数返回第一个string在第二个string中的起始位置,如果第一个string或第二个string为空,则结果反回空(NULL),如果第一个字串中第二个中没有找见,则返回零(0),

语法介绍:
LOCATE( string1, string2 [, start] )

string1:
任何字串表达式,此字串作为一个字串对象去字串2中进行批配.

string2:
任何字串表达式,此字串包括第一个字串.

start:
从字串string2中搜寻string1是的起始位置,假如没有设置,或为NULL,或所赋值小于壹,则统一从1开始搜寻.

Examples:

DE>SELECT LOCATE( 'Peter', 'Peter Steve Tom' )DE>


七.CAST函数的用法（ 有问题，见http://blog.csdn.net/dac55300424/article/details/9857845）

CAST 函数 [数据类型转换]

功能:
返回转换为提供的数据类型的表达式的值。
语法:

CAST(expression AS data type)

参数:
expression     要转换的表达式

data type       目标数据类型

用法:

如果未指定字符串类型的长度，数据库服务器将选择适当的长度。如果没有为十进制转换指定精度和小数位数，则数据库服务器将选择适当的值。

示例
下面的函数确保字符串被用作日期：
SELECT CAST( '2000-10-31' AS DATE )

计算表达式 DE>1 + 2DE> 的值，并将结果转换为单字符字符串。
SELECT CAST( 1 + 2 AS CHAR )

可以使用 CAST 函数缩短字符串：