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) = '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 --集合大小
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代码
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[]数组。