参考 《Hibernate实战(第二版)》
JPA的查询语言
- JPA的查询语言分为:JPQL查询语言和条件查询API(CriteriaQuery)
- 这里主要示例JPQL查询语言写法
//实体表介绍: 商品Item 商品类别Category 买家用户User 出价单Bid
//Item与Category多对多,Item与User一对多,Item与Bid一对多,User与Bid一对多
//基础语法
//select i.ID, i.NAME, ... from Item i
from Item
select i from item i
select i from item as
select i from item i where i.name = 'Foo'
//限制
select b from Bid b where b.amout between 99 and 110
select b from Bid b where b.amout > 100
select u from User u where u.username in ('johndoe','janeroe')
select i from Item i where i.buyNowPrice is null
select i from Item i where i.buyNowPrice is not null
select u from User u where u.username like 'john%'
select u from User u where u.username not like 'john%'
select i from Item i where (i.name like 'Fo%' and i.buyNowPrice is not null)
or i.name = 'Bar'
//函数
select c from Category c where size(c.items)>1
select c from Category c where :item member of c.items
select value(img) from Item i join i.images img where key(img) like '%.jpg'
select i from Item i where lower(i.name) like 'ba%'
select i from Item i where function('DATEDIFF','DAY',i.createOn,i.auctionEnd)>1
//排序
select u from User u Order by u.username desc
//投影 Object[]集合
select u.id, u.username, u.homeAddress from User u
select distinct i.name from Item i
select concat(concat(i.name,': ', i.auctionEnd)) from Item i
select i.name, coalesce(i.buyNowPrice,0) from Item i //ifnull
select u.username, case when length(u.homeAddress.zipcode)=5 then 'Germany' when length(u.homeAddress.zipcode)=4 then 'Switzerland' else 'Other' end from User u
//聚合
select count(i) from Item i //sum max min avg
select i.name, avg(b.amout) from Bid b join b.item i group by i.name
select u.lastname, count(u) from User u group by u.lastname having u.lastname like 'D%'
//联结 隐式、join、theta
select b from Bid b where b.item.name like 'Fo%' //隐式总是沿着多对一、一对一
select i from Item i join i.bids b where b.amout>100 //隐式加上 i.ID=b.ITEM_ID
select i, b from Item i left join i.bids b on b.amout>100 //返回 List<Object[]>
select u,log from User u, LogRecord log where u.username = log.username //返回 List<Object[]>
select i ,b from Item i, Bid b where b.item.id =i.id and i.seller = b.bidder
//子查询 JPA支持where子句子查询,不支持from子句子查询,select子查询一般不支持,但可用特殊实现
select u from User u where (select count(i) from Item i where i.seller =u )>1
select b from Bid b where b.amout+1>=(select max(b2.amout) from Bid b2)
select i from Item i where 10>= all (select b.amout from i.bids b)
select i from Item i where 101.00 = any (select b.amout from i.bids b)
select i from Item i where exists (select b from Bid b where b.item=i)
/此外还有多态查询,JPA会自动union all 这里不做讨论