HQL函数、操作符（以防忘记了）

注意：要使用HQL的函数功能，必须采用Hibernate3的查询分析器，即：

   hibernate.query.factory_class=org.hibernate.hql.ast.ASTQueryTranslatorFactory 

 

---

 

 

 

原文地址:http://wangtong40.javaeye.com/blog/355624,请支持原创

HQL查询
节 10.01    单表HQL查询

Sql代码

1. FROM CUSTOMERS as customers  

   FROM CUSTOMERS as customers

节 10.02    Where子句
(a)    比较表达式Sql代码

1. FROM CUSTOMERS as customers WHERE customers.id=1   
2. FROM CUSTOMERS as customers WHERE customers.amount between 1 and 10   
3. FROM CUSTOMERS as customers WHERE customers.amount >100   
4. FROM CUSTOMERS as customers WHERE customers.email in ('foo@bar','bar@foo')   
5. FROM CUSTOMERS as customers WHERE customers.email IS NULL  
6. FROM CUSTOMERS as customers WHERE customers.email IS NOT NULL  
7. FROM CUSTOMERS as customers WHERE customers.email LIKE '@%'  

FROM CUSTOMERS as customers WHERE customers.id=1
FROM CUSTOMERS as customers WHERE customers.amount between 1 and 10
FROM CUSTOMERS as customers WHERE customers.amount >100
FROM CUSTOMERS as customers WHERE customers.email in ('foo@bar','bar@foo')
FROM CUSTOMERS as customers WHERE customers.email IS NULL
FROM CUSTOMERS as customers WHERE customers.email IS NOT NULL
FROM CUSTOMERS as customers WHERE customers.email LIKE '@%'

 

 

HQL 操作符
HQL 常用操作符	描述
.	导航路径表达式操作符
+,-	一元正负号
*,/	乘除法
+,-	加减法
=,<>,<,>,>=,<=,[NOT]BETWEEN[NOT] LIKE,[NOT] IN,IS [NOT] NULL,IS [NOT] EMPTY	二元比较操作符
NOT,AND,OR	逻辑操作符

HQL操作符



(b)    HQL常用函数

Sql代码

1. FROM User u where lower(u.email) = 'foo@163.com' --小写   
2. FROM User u where lower(u.email) = 'foo@163.com' --大写   
3. FROM User u where concat(u.firstname,u.lastname) like 'G% K%' --拼接字符串   
4. FROM User u where size(u.bids) > 3 --集合大小  

FROM User u where lower(u.email) = 'foo@163.com' --小写
FROM User u where lower(u.email) = 'foo@163.com' --大写
FROM User u where concat(u.firstname,u.lastname) like 'G% K%' --拼接字符串
FROM User u where size(u.bids) > 3 --集合大小

 

HQL 函数
HQL 常用操作符	描述
UPPER(s),LOWER(s)	大小写
CONCAT(s1,s2)	连接字符串
SUBSTRING(s,offset,length)	取子串
TRIM()	去空格
LENGTH(s)	长度
LOCATE(search,s,offset)	搜索位置
ABS(n),SQRT(n),MOD(dividend,divisor)	绝对值，开方，除法
SIZE	大小写
BIT_LENGTH(s)	位数
CUTTENT_DATE()	当前时间
CURRENT_TIME()
CURRENT_TIMESTAMP()
SECOND(d)	获取时间的不同部分
MINUTE(d)
HOUR(d)
DAY(d)
MONTH(d)
YEAR(d)
CAST(t as TYPE)	类型转换

HQL函数

节 10.03    Order表达式

 

Sql代码

1. FROM CUSTOMERS as customers WHERE customers.id=1   
2. ORDER BY customers.id FROM CUSTOMERS as customers   
3. ORDER BY customers.lastname asc,firstname asc  

FROM CUSTOMERS as customers WHERE customers.id=1 
ORDER BY customers.id FROM CUSTOMERS as customers 
ORDER BY customers.lastname asc,firstname asc

节 10.04    投影查询

Sql代码

1. SELECT user.username,user.password FROM UserInfo user  

SELECT user.username,user.password FROM UserInfo user

注：1、在使用Select子句查询时，要给表对象起一个别名。
    2、查询出的结果集是一个Object[]数组。