HQL 函数 操作符

HQL查询
节 10.01    单表HQL查询

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

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 '@%'  
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代码
FROM User u where lower(u.email) = '[e-mail]' --小写   
FROM User u where lower(u.email) = '[e-mail]' --大写   
FROM User u where concat(u.firstname,u.lastname) like 'G% K%' --拼接字符串   
FROM User u where size(u.bids) > 3 --集合大小  
FROM User u where lower(u.email) = '[e-mail]' --小写
FROM User u where lower(u.email) = '[e-mail]' --大写
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代码
FROM CUSTOMERS as customers WHERE customers.id=1   
ORDER BY customers.id FROM CUSTOMERS as customers   
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代码
SELECT user.username,user.password FROM UserInfo user  
SELECT user.username,user.password FROM UserInfo user
注：1、在使用Select子句查询时，要给表对象起一个别名。
    2、查询出的结果集是一个Object[]数组。